Query External Tables with Implicit Partitioning
On Autonomous Database you can create implicit partitioned external tables from Hive style partitioned data or from simple folder partitioned data stored on your Cloud Object Store.
- About External Tables with Implicit Partitioning
On Autonomous Database use theDBMS_CLOUD.CREATE_EXTERNAL_TABLE
procedure to create implicit partitioned external tables from Hive style partitioned data or from simple folder partitioned data stored on your Cloud Object Store. - Query External Implicit Partitioned Data with Hive Format Source File Organization
Use theDBMS_CLOUD.CREATE_EXTERNAL_TABLE
procedure to create an implicit partitioned external table from data in Object Store that was generated from Hive data. - Query External Implicit Partitioned Non-Hive Style Data
Use theDBMS_CLOUD.CREATE_EXTERNAL_TABLE
procedure to create an implicit partitioned external table from data in object storage that has been generated from non-Hive data.
Parent topic: Query External Data with Autonomous Database
About External Tables with Implicit Partitioning
On Autonomous Database use the
DBMS_CLOUD.CREATE_EXTERNAL_TABLE
procedure to create implicit partitioned
external tables from Hive style partitioned data or from simple folder partitioned data stored
on your Cloud Object Store.
Use the DBMS_CLOUD.CREATE_EXTERNAL_TABLE
procedure to create
implicit partitioned external tables. By passing the appropriate options to this procedure,
the partitions are derived from the source data. The partitioned external tables support
runtime discovery of partition columns and their values. Runtime discovery of changes in the
underlying object store structure, such as adding or removing objects, simplifies the
maintenance process by eliminating the need for additional synchronization procedures required
by DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
. This ensures that the data is up to
date at query runtime.
With implicit partitioning, Autonomous Database
automatically determines the columns a table is "partitioned on" based on the object storage
source hierarchical file structure. There is no need to explicitly declare a partitioning
scheme. Implicit partitioning provides partition-table-like performance benefits without the
need to explicitly define a partitioned external table using the
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
procedure.
Implicit partitioned external tables support the following naming styles for partitioned objects in object store:
- Hive Naming Format: Data in object storage that has been generated from Hive
has the following format.
OBJBUCKET/<table>/<partcol1>=<value1>/<partcol2>=<value2>/file.parquet
For an example, see Query External Implicit Partitioned Data with Hive Format Source File Organization
- Non-Hive "Pathtail" Naming Format: A second format that is often found in
data lakes is similar to the hive format, but the folder name does not include the partition
columns.
OBJBUCKET/<table>/<value1>/<value2>/file.parquet
For an example, see Query External Implicit Partitioned Non-Hive Style Data.
For more information on naming formats, see About External Tables with Source File Partitioning.
DBMS_CLOUD.CREATE_EXTERNAL_TABLE
procedure, implicit partitioned external tables can be created in the following ways:
- Set the partition type to hive with a given list of partition columns
In this case,
implicit_partition_type
is set tohive
andimplicit_partition_columns
provides a list of partition columns.For example:
DBMS_CLOUD.CREATE_EXTERNAL_TABLE ( table_name => 'mysales', credential_name => 'mycredential', file_uri_list => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet', column_list => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)', format => '{"type":"parquet", "implicit_partition_type":"hive", "implicit_partition_columns":["country","year","month"]}');
- Set the partition type to hive without providing a list of partition
columns
In this case,
implicit_partition_type
is set tohive
andimplicit_partition_columns
is not provided. The partition columns are automatically detected by searching for '=' in the path specified byfile_uri_list
. Thecolumn_name
is to the left of the '=' and the value is on the right side. If acolumn_name
is not found in the path, an error is thrown.For example:
DBMS_CLOUD.CREATE_EXTERNAL_TABLE ( table_name => 'mysales', credential_name => 'mycredential', file_uri_list => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet', column_list => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)', format => '{"type":"parquet", "implicit_partition_type":"hive"');
- Provide a list of partition columns without specifying the type
In this case,
implicit_partition_type
is not set andimplicit_partition_columns
provides a list of columns.For example:
DBMS_CLOUD.CREATE_EXTERNAL_TABLE ( table_name => 'mysales', credential_name => 'mycredential', file_uri_list => 'https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/*.parquet', column_list => 'product varchar2(100), units number, country varchar2(100),year (number), month varchar2(2)', format => '{"type":"parquet", "implicit_partition_columns":["country","year","month"]}');
See CREATE_EXTERNAL_TABLE Procedure for further information.
Parent topic: Query External Tables with Implicit Partitioning
Query External Implicit Partitioned Data with Hive Format Source File Organization
Use the DBMS_CLOUD.CREATE_EXTERNAL_TABLE
procedure to
create an implicit partitioned external table from data in Object Store that was
generated from Hive data.
The sample source files in the follow example, use this naming format:
OBJBUCKET/<table>/<partcol1>=<value1>/<partcol2>=<value2>/file.parquet
Consider the following sample source files:
OBJBUCKET/sales/country=USA/year=2024/month=01/sales-2024-01.parquet
OBJBUCKET/sales/country=USA/year=2024/month=02/sales-2024-02.parquet
OBJBUCKET/sales/country=USA/year=2024/month=02/sales-2024-03.parquet
To create an implicit partitioned external table with data stored in this sample Hive format, do the following:
Parent topic: Query External Tables with Implicit Partitioning
Query External Implicit Partitioned Non-Hive Style Data
Use the DBMS_CLOUD.CREATE_EXTERNAL_TABLE
procedure
to create an implicit partitioned external table from data in object storage that
has been generated from non-Hive data.
The sample source files in the follow example, use this naming format:
OBJBUCKET/<table>/<value1>/<value2>/file.parquet
Consider the following sample source files:
OBJBUCKET/sales/USA/2024/01/sales-2024-01.parquet
OBJBUCKET/sales/USA/2024/01/sales-2024-02.parquet
OBJBUCKET/sales/USA/2024/01/sales-2024-03.parquet
To create an implicit partitioned external table with data stored in this sample Hive format, do the following:
Parent topic: Query External Tables with Implicit Partitioning