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 all be on the same cloud object
store.
For example:
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
(
TABLE_NAME => 'sales_sample',
CREDENTIAL_NAME => 'DEF_CRED_NAME',
FILE_URI_LIST => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/*.parquet',
FORMAT => '{"type":"parquet", "schema": "first","partition_columns":[{"name":"month","type":"varchar2(100)"}]}');
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).
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 partition_columns
format
parameter specifies the names of the partition columns.
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.
The DBMS_CLOUD.CREATE_EXTERNAL_PART_TABLE
call would result in the following table definition:
CREATE TABLE "ADMIN"."SALES_SAMPLE"
( "DAY_ID" TIMESTAMP (6),
"GENRE_ID" NUMBER(19,0),
"MOVIE_ID" NUMBER(19,0),
"CUST_ID" NUMBER(19,0),
"APP" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
"DEVICE" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
"OS" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
"PAYMENT_METHOD" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
"LIST_PRICE" BINARY_DOUBLE,
"DISCOUNT_TYPE" VARCHAR2(4000 BYTE) COLLATE "USING_NLS_COMP",
"DISCOUNT_PERCENT" BINARY_DOUBLE,
"ACTUAL_PRICE" BINARY_DOUBLE,
"MONTH" VARCHAR2(100 BYTE) COLLATE "USING_NLS_COMP"
) DEFAULT COLLATION "USING_NLS_COMP"
ORGANIZATION EXTERNAL
( TYPE ORACLE_BIGDATA
DEFAULT DIRECTORY "DATA_PUMP_DIR"
ACCESS PARAMETERS
( com.oracle.bigdata.fileformat=parquet
com.oracle.bigdata.filename.columns=["month"]
com.oracle.bigdata.file_uri_list="https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/*.parquet"
com.oracle.bigdata.credential.schema="ADMIN"
com.oracle.bigdata.credential.name=CRED_OCI
com.oracle.bigdata.trimspaces=notrim
)
)
REJECT LIMIT 0
PARTITION BY LIST ("MONTH")
(PARTITION "P1" VALUES (('2019-01'))
LOCATION
( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/month=2019-01/*.parquet'
),
PARTITION "P2" VALUES (('2019-02'))
LOCATION
( 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/moviestream_landing/o/sales_sample/month=2019-02/*.parquet'
))
PARALLEL ;
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.