4.3.9.2 Use URI to Load Data Manually

Before You Begin

To use URIs, you require the same privileges to use pre-authenticated requests and resource prinicipals.
- To use PAR URIs, make sure you have the required PAR Privileges.
- To use Native URIs and OCIFS URIs, make sure you have the required Resource Principal Privileges.
Review How To Load Data From Object Storage Manually.

Types of URIs

There are three types of URIs supported: OCIFS, pre-authenticated request (PAR), and Native URI, and you can specify a name of a file as your source in Object Storage, or a prefix, or a pattern.

When you define the object_path of a URI, the name, prefix, or glob pattern is inferred by the following.

Name: If the object_path is neither a glob pattern nor prefix.
Prefix: If the object_path is not a glob pattern and ends with an unencoded / character, such as a folder path.
Glob pattern: If the object_path contains at least one of the following unencoded characters: ?, *, or [. To use these characters as literals, you need to escape them or encode them as needed depending on the URI syntax. Regex patterns are not supported. See Glob Patterns from the Oracle Cloud Infrastructure Documentation to learn more.

URI Parameters

Depending on the type of URI you use, you need to ensure that the following parameters are present in the URI.

bucket_name: Buckets allow you to store objects in a compartment. This parameter is needed for all three types of URIs. 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. This parameter is needed for all three types of URIs. To learn how to view your namespace, see Object Storage Namespaces in Oracle Cloud Infrastructure Documentation.
region_name: The region identifier for your OCI tenancy. This parameter is needed for PAR URI and Native URI. For OCIFS URI, it is set to the region of the instance running Lakehouse. To view the list of available regions and region identifiers, see Regions and Availability Domains in Oracle Cloud Infrastructure Documentation.

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 Auto Parallel Load Syntax and 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": [{"uri": "oci://mybucket@mynamespace/data_files/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": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

JSON Syntax and SQL Syntax

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 = (URI = 'oci://mybucket@mynamespace/data_files/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": [{"uri": "oci://mybucket@mynamespace/data_files/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.

OCIFS URI Examples

The format of an OCIFS URI is the following:

oci://bucket_name@namespace_name/object_path

The object_path in an OCIFS URI cannot be empty. It also does not have a region parameter. The instance's associated region is used as the default region. See OCIFS Documentation to learn more about OCIFS and its specifications.

The following examples load a single file as a name since the object_path is not a glob pattern or prefix. The example loads the data_file_1.csv 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": [{"uri": "oci://mybucket@mynamespace/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 = (URI = 'oci://mybucket@mynamespace/data_files/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

In the following examples the object_path is encoded, so it is treated as a name instead of a prefix. The original file name is 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": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_%5B1%5D.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 = (URI = 'oci://mybucket@mynamespace/data_files/data_file_%5B1%5D.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a prefix because the object_path ends with a / character and does not contain any of the following characters: *, ?, or [. The examples load all files in the data_files/ Object Storage 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": [{"uri": "oci://mybucket@mynamespace/data_files/"}]}';
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 = (URI = 'oci://mybucket@mynamespace/data_files/');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a glob pattern and loads two files, data_file_1.csv and data_file_2.csv. The object_path is a pattern because it uses a [ character.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"}, 
                           "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_[1-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 = (URI = 'oci://mybucket@mynamespace/data_files/data_file_[1-2].csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a glob pattern because they use an unencoded * character. The examples load all CSV files that start with data_file_. For example, they load the files data_file_1.csv, data_file_2.csv, and data_file_3.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": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_*.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 = (URI = 'oci://mybucket@mynamespace/data_files/data_file_*.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a glob pattern because they use an unencoded ? character. The examples load all CSV files that start with data_file_ and have any single character followed by 0. For example, they 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": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_?0.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 = (URI = 'oci://mybucket@mynamespace/data_files/data_file_?0.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

PAR URI Examples

The format of a PAR URI is the following if using dedicated endpoints:

https://namespace_name.objectstorage.region_name.oci.customer-oci.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path

The format of a PAR URI can also be the following:

https://objectstorage.region_name.oraclecloud.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path

The object_path in a PAR URI can be empty. If the object_path is empty, the PAR URI is treated as a PAR with no additional pattern, prefix, or name target.

For more information about pre-authenticated requests, see Object Storage Pre-Authenticated Requests in Oracle Cloud Infrastructure Documentation.

The following examples load a single file as a name since the object_path is not a glob pattern or prefix. The examples load the file data_file_2.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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_2.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

In the following examples the object_path is encoded, so it is treated as a name instead of a prefix. The original file name is 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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_%5B1%5D.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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_%5B1%5D.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

In the following examples, the object_path is empty, which means that no additional target is specified. Therefore, the examples load all the files under the PAR.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"}, 
                           "file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a prefix because the object_path ends with a / character and does not contain any of the following characters: *, ?, or [. The examples load any files in the data_files/ Object Storage 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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/"}]}';
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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a glob pattern and load two files, data_file_2 and data_file_3.csv. The object_path is a pattern because it uses a [ character.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"}, 
                           "file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_[2-3].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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_[2-3].csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a glob pattern because they use an unencoded * character. The examples load all CSV files that start with data_file_. For example, they load the files data_file_1.csv, data_file_2.csv, and data_file_3.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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_*.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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_*.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a glob pattern because they use an unencoded ? character. The examples load all CSV files that start with data_file_ and have any single character followed by 0. For example, they load 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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_?0.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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_?0.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

Native URI Examples

The format of a Native URI is the following if using dedicated endpoints:

https://namespace_name.objectstorage.region_name.oci.customer-oci.com/n/namespace_name/b/bucket_name/o/object_path

The format of a Native URI can also be the following:

https://objectstorage.region_name.oraclecloud.com/n/namespace_name/b/bucket_name/o/object_path

The object_path cannot be empty.

The following examples load a single file as a name since the object_path is not a glob pattern or prefix. The examples load the data_file_1.csv 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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_file_1.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

In the following examples the object_path is encoded, so it is treated as a name instead of a prefix. The original file name is 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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_file_%5B1%5D.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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_file_%5B1%5D.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a prefix because the object_path ends with a / character and does not contain any of the following characters: *, ?, or [. The examples load any files in the data_files/ Object Storage 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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/"}]}';
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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a glob pattern and loads two files, data_file_1.csv and data_file_2.csv. The object_path is a pattern because it uses a [ character.

JSON syntax example:

mysql> CREATE EXTERNAL TABLE table_1(col_1 int, col_2 int, col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"}, 
                           "file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/data_file_[1-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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/data_file_[1-2].csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a glob pattern because they use an unencoded * character. The examples load all CSV files that start with data_file_. For example, they load the files data_file_1.csv, data_file_2.csv, and data_file_3.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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/data_file_*.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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/data_file_*.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

The following examples load files as a glob pattern because they use an unencoded ? character. The examples load all CSV files that start with data_file_ and have any single character followed by 0. For example, they load 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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/data_file_?0.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 = (URI = 'https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/data_file_?0.csv');
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

What's Next

After successfully loading data and creating external tables, learn how to do the following: