4.3.9.3 Use Resource Principals to Load Data Manually

To load data manually using resource principals, review the following examples for different ways to set them up.

This topic contains the following sections:

• Before You Begin

• Resource Principals Parameters

• Resource Principals Examples

• CREATE TABLE Statement

• Load a Single File

• Load Multiple Files into One External Table

• Load Files with a Pattern

• What's Next

Before You Begin

• Ensure you have the appropriate MySQL HeatWave Lakehouse Privileges to use resource principals.

• Review the requirements to Access Object Storage with Resource Principals.

• Review How To Load Data From Object Storage Using Auto Parallel Load.

• Review how the Guided Load feature performs a set of checks and validations when loading data.

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 and commands to set up the loading of data with resource principals. Replace the values in the examples with your own.

• dialect defines the format options of the file.

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

CREATE TABLE Statement

Depending on the version of MySQL you are using, use the appropriate CREATE TABLE statement.

• As of MySQL 9.4.0, you can use the CREATE EXTERNAL TABLE statement, which automatically sets ENGINE to lakehouse, and SECONDARY_ENGINE to rapid.

• In versions earlier than MySQL 9.4.0, you must use the CREATE TABLE statement, and manually set ENGINE to lakehouse, and SECONDARY_ENGINE to rapid.

For example, you can use the following command in MySQL 9.4.0:

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"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

For earlier versions, you must use the following command:

mysql> CREATE TABLE table_1(col_1 int, col_2 int, col_3 int)
ENGINE=lakehouse
SECONDARY_ENGINE = rapid
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"}, 
                   "file": [{"region": "regionName", 
                             "namespace": "tenant_1", 
                             "bucket": "bucket_1", 
                             "name": "data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

Load a Single File

The following example loads 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"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

Load Multiple Files into One External Table

To load multiple external files into 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 identify the files to load into the table.

The following example loads all the files uploaded to a folder in the Object Storage bucket into 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/"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following example identifies two files to load into one 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"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

Load Files with a Pattern

You can add a pattern parameter to use regular expression to load specific 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:

• Regular Expression Syntax

• Modified ECMAScript regular expression grammar

The following example loads all files in the bank_data folder that have a numerical suffix of one or more digits. For example, MySQL HeatWave loads the files data_file_1.csv, data_file_2.csv, and data_file_3.csv into table_1.

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"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load all files in the bank_data folder that have an alphabetical suffix of one or more lowercase characters. For example, MySQL HeatWave loads 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"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

To update the previous example to include uppercase and lowercase characters, replace [a-z] with [A-Za-z].

The following example loads all files in the bank_data folder that have a numerical suffix that end in 0 with one preceding digit. For example, MySQL HeatWave loads 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"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

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 loading data and creating external tables, learn how to do the following:

  • Refresh Data Using Event-Based Incremental Load

  • Accelerate Query Processing

  • Train and Use Machine Learning Models

  • Perform AI-Powered Search and Content Generation