Review the following examples to see the different ways to use resource principals to specify files when creating external tables manually.
This topic contains the following sections:
Review the requirements to Access Object Storage with Resource Principals.
Review how to Create an External Table Manually.
To set up resource principals, you configure the following parameters:
bucket_name
: Buckets allow you to storage objects in a compartment. To learn more about buckets, see Object Storage Buckets in Oracle Cloud Infrastructure Documentation.namespace_name
: This is the top-level container for all buckets and objects. To learn how to view your namespace, see Object Storage Namespaces in Oracle Cloud Infrastructure Documentation.-
region_name
: You need to define the region identifier for your OCI tenancy. To view the list of available regions and region identifiers, see Regions and Availability Domains in Oracle Cloud Infrastructure Documentation.As of MySQL 9.2.1, if the region is not specified, the instance's associated region is used as the default region. Otherwise, the specified region is used.
name
: Use this to specify an Object Storage file name.pattern
: Use this to set a regular expression that defines a set of Object Storage files. The pattern follows the modified Modified ECMAScript regular expression grammar.prefix
: Use this to define a set of Object Storage folders and files.
The following examples use these parameters to create the external tables and specify the files to load the tables with. Replace the values in the examples with your own.
dialect
defines the format options of the specified files.-
The
file
parameters define the resource principal details.region
: The region the tenant resides in.namespace
: The name of the tenancy.bucket
: The name of the Object Storage bucket.name
: The name of the external file to load.prefix
: The prefix for a set of Object Storage folders and files.pattern:
The regular expression that defines a set of Object Storage files.
To review all syntax options for loading external files, see Lakehouse External Table Syntax.
The following example specifies a single file.
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
"file": [{"region": "regionName",
"namespace": "tenant_1",
"bucket": "bucket_1",
"name": "data_file_1.csv"}]}';
To specify multiple external files for one external table, you can do one of the following:
Create a folder in the Object Storage bucket and upload the required files into that folder. See Managing Folders in an Object Storage Bucket in Oracle Cloud Infrastructure Documentation.
Create
file
items as needed to specify the files to load into the table.
The following example specifies all the files uploaded to a folder in the Object Storage bucket for one external table.
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
"file": [{"region": "regionName",
"namespace": "tenant_1",
"bucket": "bucket_1",
"prefix": "data_files/"}]}';
The following example specifies two files for one external table.
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
"file": [{"region": "regionName,
"namespace": "tenant_1",
"bucket": "bucket_1",
"name": "data_files/data_file_1.csv"},
{"region": "regionName",
"namespace": "tenant_1",
"bucket": "bucket_1",
"name": "data_files/data_file_2.csv"}]}';
You can add a pattern
parameter to use
regular expression to specify files.
The regular expression syntax requires certain characters to have an escape character.
The escape character is the backslash character, and it is a
reserved character in both JSON and MySQL. Therefore, it is
necessary to escape the backslash character twice, and
specify \\
for both JSON and MySQL.
However, the regular expression escape sequence depends upon
the NO_BACKSLASH_ESCAPES
SQL mode:
Use
\\.
to escape a period ifNO_BACKSLASH_ESCAPES
is enabled.Use
\\\\.
to escape a period ifNO_BACKSLASH_ESCAPES
is not enabled. The following examples use this sequence because it is the default mode.
See the following to learn more:
The following example specifies all files in the
data_files
folder that have a numerical
suffix of one or more digits to load into the external
table. For example, it uses the files
data_file_1.csv
,
data_file_2.csv
, and
data_file_3.csv
.
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
"file": [{"region": "regionName",
"namespace": "tenant_1",
"bucket": "bucket_1",
"pattern": "data_files/data_file_\\\\d+\\\\.csv"}]}';
The following examples specifies all files in the
data_files
folder that have an
alphabetical suffix of one or more lowercase characters to
load into the external table. For example, it uses the files
data_file_a.csv
,
data_file_b.csv
, and
data_file_c.csv
.
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
"file": [{"region": "regionName",
"namespace": "tenant_1",
"bucket": "bucket_1",
"pattern": "data_files/data_file_[a-z]+\\\\.csv"}]}';
To update the previous example to include uppercase and
lowercase characters, replace [a-z]
with
[A-Za-z]
.
The following example specifies all files in the
data_files
folder that have a numerical
suffix that end in 0
with one preceding
digit. For example, it uses the files
data_file_10.csv
,
data_file_20.csv
, and
data_file_30.csv
.
mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
"file": [{"region": "regionName",
"namespace": "tenant_1",
"bucket": "bucket_1",
"pattern": "data_files/data_file_\\\\d0\\\\.csv"}]}';
To update the previous example to load files with one or
more digits preceding the 0, update
\\\\d0\\\\
with
\\\\d+0\\\\
.
After successfully creating external tables manually and specifying the files to load data into the table, learn how to Load Structured Data Manually.