The procedure DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
supports external
partitioned files in the supported cloud object storage services. The credential is a
table level property; therefore, the external files must be on the same object
store.
For example:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
(
table_name =>'PET1',
credential_name =>'DEF_CRED_NAME',
format => json_object('delimiter' value ',', 'recorddelimiter' value 'newline', 'characterset' value 'us7ascii'),
column_list => 'col1 number, col2 number, col3 number',
partitioning_clause => 'partition by range (col1)
(partition p1 values less than (1000) location
( ''https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/file_11.txt''),
partition p2 values less than (2000) location
( ''https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/file_21.txt''),
partition p3 values less than (3000) location
( ''https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/file_31.txt'') )'
);
END;
/
The parameters are:
-
table_name
: is the external table name.
-
credential_name
: is the name of the credential
created in the previous step.
-
partitioning_clause
: is the complete partitioning
clause, including the location information for individual partitions.
-
format
: defines the options you can specify to describe the format of the source file. format: defines the options you can specify to describe the format of the source file.
If the data in your source files
is encrypted, decrypt the data by specifying the
format
parameter with the
encryption
option. See Decrypt Data
While Importing from Object Storage for more
information on decrypting data.
-
column_list
: is a comma delimited list of the column
definitions in the source files.
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.
You can now run queries on the external partitioned table you created in
the previous step. Your Autonomous Database
takes advantage of the partitioning information of your external partitioned table,
ensuring that the query only accesses the relevant data files in the Object Store. For
example, the following query only reads data files from partition P1:
SELECT * FROM pet1 WHERE col1 < 750;
The external partitioned tables you create with DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
include two invisible
columns file$path
and file$name
. These columns help
identify which file a record is coming from. See External Table Metadata Columns for more information.
If there are any rows in the source files that do not match the format
options you specified, the query reports an error. You can use DBMS_CLOUD
parameters, like
rejectlimit, to suppress these errors. As an alternative, you can also
validate the external partitioned table you created to see the error messages and the
rejected rows so that you can change your format options accordingly. See Validate External Data and Validate External Partitioned Data for more information.
See CREATE_EXTERNAL_PART_TABLE Procedure for detailed information about the parameters.
See DBMS_CLOUD URI Formats for more information on the supported cloud object storage
services.