4.3.9.3 Use Resource Principals to Create External Tables Manually

Before You Begin

Review the requirements to Access Object Storage with Resource Principals.
Review how to Create an External Table Manually.

Resource Principals Parameters

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.

Resource Principals Examples

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.

Specify a Single File

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"}]}';

Specify Multiple Files for One External Table

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"}]}';

Specify Files with a Pattern

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 if NO_BACKSLASH_ESCAPES is enabled.
Use \\\\. to escape a period if NO_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\\\\.

What's Next

After successfully creating external tables manually and specifying the files to load data into the table, learn how to Load Structured Data Manually.