Query External Tables with Implicit Partitioning

On Autonomous AI 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.

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 AI 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.

In order to optimize query response time for queries of very large data sets using implicit partitioning, you can enable optimization by setting strict_column_order to true. The strict_column_order parameter is a sub-option of the implicit_partition_config format option. This optimization only applies when partition_type is hive. See Query External Implicit Partitioned Data with Hive Format Source File Organization for an example and DBMS_CLOUD Package Format Options for more information on the implicit partitioning format parameters.

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.

See Query External Implicit Partitioned Data with Hive Format Source File Organization and CREATE_EXTERNAL_TABLE Procedure for further information.

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.

1. Store Object Store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL.

   For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => 'MY_CREDENTIAL',
       username => 'adb_user@example.com',
       password => 'password' );
   END;
   /

   Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

   This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name. Note that this step is required only once unless your object store credentials change. Once you store the credentials you can then use the same credential name for creating external tables.

   See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different object storage services.

2. Create an implicit partitioned external table on top of your source files using the procedure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
   1. Case 1: Enable implicit partitioning.
      In this example, since the format option implicit_partition_columns is not provided, partition columns are automatically detected and implicit partitioning is enabled by setting the format option implicit_partition_type to hive.

      However, if a list of columns is given, then those columns are used as partition columns and Autonomous AI Database doesn't try to discover the columns.

      To detect the partition columns Autonomous AI Database starts searching from the beginning of the path, specified by file_uri_list, for '='. When found, the left part of '=' up to the last '/' is taken as column (e.g. "country") and the right part of '=', until the first '/' as value (e.g. "USA"). The search continues for '=' until after the '/' that follows the first partition value, detecting the second '=' in the path, and so on.

      BEGIN
      DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
         table_name        => 'mysales',
         credential_name   => 'MY_CREDENTIAL',
         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"');
      END;
      /

      The parameters are:

      • table_name: is the external table name.

      • credential_name: is the name of the credential created in the previous step.

      • file_uri_list: is a comma-delimited list of source file URIs. There are two options for this list:

        • Specify a comma-delimited list of individual file URIs without wildcarding.

        • Specify a single file URI with wildcards, where the wildcards can only be after the last slash "/". The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

        In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

      • column_list: is a comma delimited list of column names and data types for the external table. The list includes the columns inside the data file and those derived from the object name (from names in the file path specified by file_uri_list).

        The column_list is not required when the data files are structured files (Parquet, Avro, or ORC).

      • format: defines the options you can specify to describe the format of the source file. The implicit_partition_type option specifies the data format type as hive.

        If the data in your source file is encrypted, decrypt the data by specifying the encryption format option. See Decrypt Data While Importing from Object Storage for more information on decrypting data.

        See DBMS_CLOUD Package Format Options for more information.

   1. Case 2: Optimized implicit partitioning
      In this example, implicit partitioning is enabled by specifying the implicit_partition_config format option, and optimized implicit partitioning is enabled by setting strict_column_order to true.

      Since partition_columns specifies a list of partitioned columns, those columns are used as partition columns and Autonomous AI Database doesn't try to discover the columns.

      BEGIN
      dbms_cloud.create_external_table (
         table_name        => 'mysales',
         credential_name   => 'MY_CREDENTIAL',
         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_config":{
                                          "partition_type":"hive",
                                          "strict_column_order":true,
                                          "partition_columns":["country","year","month","day"]
                                }
                                }');
      END;
      /

      The format parameters are:

      • format: defines the options you can specify to describe the format of the source file. The implicit_partition_config option has the following sub-options:

        • partition_type

          The only accepted value is "hive". The default is non-hive filename path. If partition_type is not specified, a client error is returned.

        • partition_columns

          List of partitioned columns.

        • strict_column_order

          Set to true to enable optimized implicit partitioning. Set to true only for cases where you know the Object Store path will not change and there are no missing prefixes. Set to false or omit to disable.

        If the data in your source file is encrypted, decrypt the data by specifying the encryption format option. See Decrypt Data While Importing from Object Storage for more information on decrypting data.

        See DBMS_CLOUD Package Format Options for more information.

   See CREATE_EXTERNAL_TABLE Procedure for detailed information about the parameters. See DBMS_CLOUD URI Formats for more information on the supported cloud object storage services.

3. You can now run queries on the external partitioned table you created in the previous step.

   Your Autonomous AI Database takes advantage of the partitioning information of your external partitioned table, ensuring that the query only accesses the relevant data files in Object Store.

   For example:

   SELECT product, units FROM mysales WHERE year='2024' and month='02'

   This SQL statement only queries data in the partition for month 02 of year 2024.

• Optimize Object Store Query Planning with the strict_column_order option
  When a query targets an object-store folder with a very large number of files and subfolders, the planning and listing phase can become the primary cost before any data is actually scanned. This is common with Hive-style folder layouts where partition values are embedded in the path.

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.

1. Store Object Store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL.

   For example:

   BEGIN
     DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => 'MY_CREDENTIAL',
       username => 'adb_user@example.com',
       password => 'password' );
   END;
   /

   Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

   This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name. Note that this step is required only once unless your object store credentials change. Once you store the credentials you can then use the same credential name for creating external tables.

   See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different object storage services.

2. Create an implicit partitioned external table on top of your source files using the procedure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE.
   In this example, implicit partitioning is enabled by providing the partition columns with the format option implicit_partition_columns. Since the folder name does not include the partition columns, the partition values in the path, specified by file_uri_list, can only be discovered if an explicit list of columns is given with the format option implicit_partition_columns. In order to detect the respective column values in the path the order of columns must be the same as the order of values.

   The partition columns are automatically detected.

   BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE (
      table_name        => 'mysales',
      credential_name   => 'MY_CREDENTIAL',
      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"]}');
   END;
   /

   The parameters are:

   • table_name: is the external table name.

   • credential_name: is the name of the credential created in the previous step.

   • file_uri_list: is a comma-delimited list of source file URIs. There are two options for this list:

     • Specify a comma-delimited list of individual file URIs without wildcarding.

     • Specify a single file URI with wildcards, where the wildcards can only be after the last slash "/". The character "*" can be used as the wildcard for multiple characters, the character "?" can be used as the wildcard for a single character.

   • column_list: is a comma delimited list of column names and data types for the external table. The list includes the columns inside the data file and those derived from the object name (from names in the file path specified by file_uri_list).

     The column_list is not required when the data files are structured files (Parquet, Avro, or ORC).

   • format: defines the options you can specify to describe the format of the source file. The implicit_partition_type option is unset. Since implicit_partition_columns is given, the type is automatically detected as non-hive.

     If the data in your source file is encrypted, decrypt the data by specifying the encryption format option. See Decrypt Data While Importing from Object Storage for more information on decrypting data.

     See DBMS_CLOUD Package Format Options for more information.

   In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

   See CREATE_EXTERNAL_TABLE Procedure for detailed information about the parameters.

   See DBMS_CLOUD URI Formats for more information on the supported cloud object storage services.

3. You can now run queries on the external partitioned table you created in the previous step.

   Your Autonomous AI Database takes advantage of the partitioning information of your external partitioned table, ensuring that the query only accesses the relevant data files in Object Store.

   For example:

   SELECT product, units FROM mysales WHERE year='2024'

   This SQL statement only queries data in the partition for year 2024.