HeatWave Release Notes
Refer to the different ways to load data into HeatWave from Object Storage.
Pre-Authenticated Requests (PAR) are unique URLs that anyone can use to access Object Storage resources.
Note
PARs can be used for any Object Storage data stored in any tenancy in the same region.
The following example manually creates a table and uses a PAR to access the data from Object Storage. The second command loads the data and creates an external table.
mysql>CREATE TABLE `CUSTOMER` (`C_CUSTKEY` int NOT NULL PRIMARY KEY, `C_NATIONKEY` int NOT NULL)
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv”},
"file": [{"par": "https://objectstorage.../n/some_bucket/customer.tbl"}]}';
mysql>ALTER TABLE `CUSTOMER` SECONDARY_LOAD;See the following to learn more:
To learn how to create and manage PARs in the OCI console, see Object Storage Pre-Authenticated Requests in the HeatWave on OCI Service Guide.
To learn more about using PARs in HeatWave, see Pre-Authenticated Requests.
You can use Resource Principals to authenticate and access data in Object Storage.
The following example manually creates a table and uses a Resource Principal to access the data in Object Storage.The second command loads the data and creates an external table.
mysql>CREATE TABLE `CUSTOMER` (`C_CUSTKEY` int NOT NULL PRIMARY KEY,`C_NATIONKEY` int NOT NULL)
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv”},
"file": [{"region": "regionName", "namespace": tenancyNamespace",
"bucket": "bucketName", "name": "customer.tbl"}]}';
mysql>ALTER TABLE `CUSTOMER` SECONDARY_LOAD;See the following to learn more:
To learn how to set up your OCI account for Resource Principals, see Resource Principals in the HeatWave on OCI Service Guide.
To learn more about using Resource Principals in HeatWave, see Resource Principals.
Available as of MySQL 9.3.1 for HeatWave on OCI, a Uniform Resource Identifier (URI) is a unique URL that allows you to access Object Storage resources. You can use a URI to specify an OCIFS URL, Native URL, or a PAR URL. For each , the files in Object Storage can be a single file, files that match a prefix, or a pattern that you can specify as a glob.
Refer to the following 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. See
OCIFS
Documentation to learn more about OCIFS and its
specifications.
The following example loads files as a glob pattern and
loads two files, file1 and file2. The
object_path is a pattern because it
uses a [ character.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "oci://mybucket@mynamespace/folder1/file[1-2]"}], "dialect": {"format": "csv"}}';
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 [. All files
in the folder1/ directory are loaded.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "oci://mybucket@mynamespace/folder1/"}], "dialect": {"format": "csv"}}';
The following example loads a single file as a name since
the object_path is not a glob pattern
or prefix. The file file1.csv is
loaded.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "oci://mybucket@mynamespace/folder1/file1.csv"}], "dialect": {"format": "csv"}}';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_pathThe 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.
In the following example, the
object_path is empty, which means that
no additional target is specified. Therefore, all the
files under the PAR are loaded.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/myaccessuri/n/mynamespace/b/mybucket/o/"}], "dialect": {"format": "csv"}}';
The following example loads files as a glob pattern and
loads two files, file2 and file3. The
object_path is a pattern because it
uses a [ character.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/myaccessuri/n/mynamespace/b/mybucket/o/path/file[2-3]"}], "dialect": {"format": "csv"}}';
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 [. Any files
in the folder1/ directory are loaded.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/myaccessuri/n/mynamespace/b/mybucket/o/folder1/"}], "dialect": {"format": "csv"}}';
The following example loads a single file as a name since
the object_path is not a glob pattern
or prefix. The file file2.csv is
loaded.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/p/myaccessuri/n/mynamespace/b/mybucket/o/path/file2.csv"}], "dialect": {"format": "csv"}}';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_pathThe 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 example loads files as a prefix because the
object_path ends with a
/ character and does not contain any of
the following characters: *,
?, or [. Any files
in the folder1/ directory are loaded.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/folder1/"}], "dialect": {"format": "csv"}}';
The following example loads files as a glob pattern and
loads two files, file2 and file3. The
object_path is a pattern because it
uses an unencoded [ character.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/path%2Ffile[2-3]"}], "dialect": {"format": "csv"}}';
In the following example the
object_path is encoded, so it is
treated as a name instead of a prefix.
mysql>CREATE TABLE example (C_TEST varchar(2))
ENGINE=lakehouse
SECONDARY_ENGINE = RAPID
ENGINE_ATTRIBUTE='{"file": [{"uri": "https://mynamespace.objectstorage.myregion.oci.customer-oci.com/n/mynamespace/b/mybucket/o/folder%2F""}], "dialect": {"format": "csv"}}';