MySQL HeatWave User Guide
To use resource principals for loading data with Lakehouse Auto Parallel Load, review the following examples for different ways to set them up.
This topic contains the following sections:
Review the requirements to Access Object Storage with Resource Principals. Make sure you have the required Resource Principal Privileges.
Review How To Load Data From Object Storage Using Auto Parallel Load.
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 and commands to set up the loading of data with resource principals. Replace the values in the examples with your own.
SET @input_list creates a session
variable that stores all the parameters for the loading
of the file.
db_name identifies the database name
to store the table. MySQL HeatWave automatically creates the
database if it does not exist.
table_name sets the table name to
store the data. MySQL HeatWave automatically creates the table
if it does not exist.
engine_attribute defines the
parameters of the external file.
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.
CALL sys.HEATWAVE_LOAD(CAST(@input_list AS
JSON), NULL); runs Auto Parallel Load to load the external
tables from the parameters set in
@input_list and with no additional
options.
If you are on MySQL 9.1.2 and earlier, you need to update
dialect with the field
delimiter and record
delimiter parameters. As of MySQL 9.2.0, MySQL HeatWave Lakehouse
can automatically detect these values. See
Lakehouse
External Table Syntax to learn more.
To review all syntax options for loading external files, see Auto Parallel Load Syntax and Lakehouse External Table Syntax.
The following example loads a single file.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "name": "data_file_1.csv"}] }}]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
To load multiple external files into one 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 table.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "prefix": "data_files/"}] }}]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example identifies two files to load into one table.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "name": "data_file_1.csv"}, {"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "name": "data_file_2.csv"}] }}]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
To load multiple external files into multiple tables with
one command, you can create the necessary number of
tables items and do one of the following:
Create folders as necessary 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 two files into two tables.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "name": "data_file_1.csv"}]}}, {"table_name": "table_2", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "name": "data_file_2.csv"}] }}]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads all the files in the identified folders into the tables.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "prefix": "data_files_1/"}]}}, {"table_name": "table_2", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "prefix": "data_files_2/"}] }}]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
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:
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>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "pattern": "data_files/data_file_\\\\d+\\\\.csv"}] }}]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
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>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "pattern": "data_files/data_file_[a-z]+\\\\.csv"}] }}]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
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>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"region": "us-ashburn-1", "namespace": "tenant_1", "bucket": "bucket_1", "pattern": "data_files/data_file_\\\\d0\\\\.csv"}] }}]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
To update the previous example to load files with one or
more digits preceding the 0, update
\\\\d0\\\\ with
\\\\d+0\\\\.
After successfully loading data and creating external tables, learn how to do the following: