MySQL HeatWave User Guide
To use uniform resource identifiers (URI) for loading data with Lakehouse Auto Parallel Load, which is available as of MySQL 9.3.1, review the following examples for different ways to set them up.
This topic contains the following sections:
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 Using Auto Parallel Load.
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.
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.
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 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 example loads 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.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
In the following example 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.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_%5B1%5D.csv"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a prefix because the
object_path ends with a
/ character and does not contain any of
the following characters: *,
?, or [. The example
loads all files in the data_files/ Object
Storage folder.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "oci://mybucket@mynamespace/data_files/"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads 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.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_[1-2].csv"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a glob pattern because
it uses an unencoded * character. The
example loads all CSV files that start with
data_file_. For example, it loads the
files data_file_1.csv,
data_file_2.csv, and
data_file_3.csv.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_files/data_file_*.csv"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a glob pattern because
it uses an unencoded ? character. The
example loads all CSV files that start with
data_file_ and have any single character
followed by 0. For example, it 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": [{"uri": "oci://mybucket@mynamespace/data_files/data_files/data_file_?0.csv"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example creates two tables and loads one file into each table.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}}, {"table_name": "table_2", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_2.csv"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
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 example loads a single file as a name since
the object_path is not a glob pattern or
prefix. The file data_file_2.csv is
loaded.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "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>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
In the following example 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.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "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>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
In the following example, the object_path
is empty, which means that no additional target is
specified. Therefore, the example loads all the files under
the PAR.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a prefix because the
object_path ends with a
/ character and does not contain any of
the following characters: *,
?, or [. The example
loads all files in the data_files/ Object
Storage folder.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a glob pattern and
loads two files, data_file_2.csv and
data_file_3.csv. The
object_path is a pattern because it uses
a [ character.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "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>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a glob pattern because
it uses an unencoded * character. The
example loads all CSV files that start with
data_file_. For example, it loads the
files data_file_1.csv,
data_file_2.csv, and
data_file_3.csv.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "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>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a glob pattern because
it uses an unencoded ? character. The
example loads all CSV files that start with
data_file_ and have any single character
followed by 0. For example, it 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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/.../n/mynamespace/b/mybucket/o/data_files/data_file_?0.csv"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example creates two tables and loads one file into each table.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "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_1.csv"}]}}, {"table_name": "table_2", "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>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
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.
In the following example loads 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.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "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>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
In the following example 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.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "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>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a prefix because the
object_path ends with a
/ character and does not contain any of
the following characters: *,
?, or [. The example
loads all files in the data_files/ Object
Storage folder.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads 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.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "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>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a glob pattern because
it uses an unencoded * character. The
example loads all CSV files that start with
data_file_. For example, it loads the
files data_file_1.csv,
data_file_2.csv, and
data_file_3.csv.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "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>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example loads files as a glob pattern because
it uses an unencoded ? character. The
example loads all CSV files that start with
data_file_ and have any single character
followed by 0. For example, it 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": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_files/data_file_?0.csv"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
The following example creates two tables and loads one file into each table.
mysql>SET @input_list = '[{"db_name": "lakehouse_db", "tables": [{"table_name": "table_1", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_file_1.csv"}]}}, {"table_name": "table_2", "engine_attribute": {"dialect": {"format": "csv"}, "file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/data_file_2.csv"}]} }]}]';mysql>CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);
After successfully loading data and creating external tables, learn how to do the following: