You can create the following types of PARs:

When creating PARs consider the following recommendations:

To set up PARs, you configure the following parameters:

The following examples use these parameters and commands to set up the loading of data with PARs. Replace the values in the examples with your own.

To review all syntax options for loading external files, see Auto Parallel Load Syntax and Lakehouse External Table Syntax.

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

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": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/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": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

As of MySQL 9.4.0, you can specify parameter values when creating external tables using SQL syntax. For earlier versions, you must specify parameter values using JSON syntax.

The following example specifies parameter values using SQL syntax:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenancy_1/b/bucket_1/o/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following example specifies parameter values using JSON syntax:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"}, 
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

To review external table options in JSON syntax, see Lakehouse External Table JSON Syntax.

To review external table options in SQL syntax, see Lakehouse External Table SQL Syntax.

To load a single file with a pre-authenticated request, you can create a pre-authenticated request for that individual file or specify the file in the command.

The following examples load a single file by using a PAR for that file.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

SQL syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenancy_1/b/bucket_1/o/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load a single file by using a PAR for a bucket and specifying the file in the name parameter. The file is in the data_files folder.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/", 
                           "name": "data_files/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

SQL syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_NAME = 'data_files/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

If you want to load multiple files into one external table, you can do one of the following:

To load multiple external files into one external table, you can 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.

If you create a folder with the files to load, create a PAR for that folder and do the following:

The following examples load all the files uploaded to the folder in the Object Storage bucket into one external table.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

SQL syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples use one PAR for a bucket and specify two files to load with the name parameter.

JSON syntax example:

CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/", "name": "data_files/data_file_1.csv"},
                                    {"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/", "name": "data_files/data_file_2.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

SQL syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/' FILE_NAME = 'data_files/data_file_1.csv',
                 URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/' FILE_NAME = 'data_files/data_file_2.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load two separate PARs to load two files.

JSON syntax example:

CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/data_files/data_file_1.csv"},
                                    {"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/data_files/data_file_2.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

SQL syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/data_files/data_file_1.csv',
                 URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/data_files/data_file_2.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

You can add a prefix parameter to a PAR to load specific files. To do this, create a PAR for the folder that stores the files to load.

The following examples load all files in the data_files folder that begin with data_file_. For example, MySQL HeatWave loads the files data_file_1, data_file_2, and data_file_3 into table_1.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/", 
                           "prefix": "data_files/data_file_"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

SQL syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_PREFIX = 'data_files/data_file_');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

You can add a pattern parameter to a PAR 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:

See the following to learn more:

The following examples load all files in the data_files 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.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/", 
                           "pattern": "data_files/data_file_\\\\d+\\\\.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

SQL syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_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.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/", 
                           "pattern": "data_files/data_file_[a-z]+\\\\.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

SQL syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_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.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/", 
                           "pattern": "data_files/data_file_\\\\d0\\\\.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

SQL syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        FILE_FORMAT = (FORMAT csv)
        FILES = (URL = 'https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_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\\\\.