MySQL HeatWave User Guide
The Lakehouse feature of MySQL HeatWave enables query processing on data in Object Storage. MySQL HeatWave Lakehouse reads the source data from Object Storage, transforms it to the memory optimized MySQL HeatWave format, saves it in the MySQL HeatWave persistence storage layer in Object Storage, and then loads the data to MySQL HeatWave Cluster memory. While Lakehouse provides in-memory query processing on data in Object Storage, it does not load data into a DB System table.
To learn how to use Lakehouse with MySQL HeatWave AutoML, see Use MySQL HeatWave AutoML with Lakehouse.
When MySQL HeatWave loads data and creates a table from a file in Object Storage, it is an external Lakehouse table. External tables are tables that do not store any data, but refer to data stored externally in Object Storage. The external table stores the location of the data.
See the following to learn more:
Review Supported File Formats of external files for loading external tables.
Review Access Object Storage to learn about the different ways Lakehouse can access external data.
The lakehouse storage engine allows you to
create tables that point to external data sources.
For MySQL HeatWave Lakehouse, lakehouse is the primary
engine, and rapid is the secondary engine.
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.
For example:
mysql> CREATE EXTERNAL TABLE table_1 (col_1 INT)
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:
mysql> CREATE TABLE table_1 (col_1 INT)
ENGINE=LAKEHOUSE
SECONDARY_ENGINE=RAPID;When using Lakehouse, you do not directly access the source data stored in Object Storage. This memory optimized internal version of the data is retained only as long as MySQL HeatWave Cluster has the Lakehouse option enabled.
The data in external tables is deleted if you do any of the following:
Drop the external table. See DROP TABLE Statement.
Unload the external table. See Unload Tables.
Delete the MySQL HeatWave Cluster. See Deleting a MySQL HeatWave Cluster in the MySQL HeatWave on OCI Service Guide, Deleting a MySQL HeatWave Cluster in the MySQL HeatWave on AWS Service Guide, or Deleting a Database in the MySQL HeatWave for Azure Service Guide.
You have three options to load data into MySQL HeatWave Cluster: uniform resource identifiers (URIs) (as of MySQL 9.3.1), resource principals, and pre-authenticated requests (PAR). Refer to the following workflows and resources showing how to use each method to load data.
The workflow to use URIs:
Prepare the files to load in the proper format. See Supported File Formats.
Upload the files to load into Object Storage. See Uploading an Object Storage Object to a Bucket in Oracle Cloud Infrastructure Documentation.
Load the external files into Lakehouse. See the following:
Learn how to load structured data using Lakehouse Auto Parallel Load. Review URI examples.
Learn how to load data manually. Review URI examples.
To review the syntax for loading structured data using Lakehouse Auto Parallel Load, see Auto Parallel Load Syntax and Lakehouse External Table Syntax.
Once Lakehouse successfully creates the external table from the external files, you can use the data in MySQL HeatWave. See the following:
The workflow to use Resource Principals:
Prepare the files to load in the proper format. See Supported File Formats.
Upload the files to load into Object Storage. See Uploading an Object Storage Object to a Bucket in Oracle Cloud Infrastructure Documentation.
Load the external files into MySQL HeatWave Cluster. See the following:
Learn how to load structured data using Lakehouse Auto Parallel Load. Review resource principals examples.
Learn how to load data manually. Review resource principals examples.
To review the syntax for loading structured data using Lakehouse Auto Parallel Load, see Auto Parallel Load Syntax and Lakehouse External Table Syntax.
Once Lakehouse successfully creates the external table from the external files, you can use the data in MySQL HeatWave. See the following:
The workflow to use PARs:
Prepare the files to load in the proper format. See Supported File Formats.
Upload the files to load into Object Storage. See Uploading an Object Storage Object to a Bucket in Oracle Cloud Infrastructure Documentation.
Create the PARs for the files previously uploaded. You have three options to create PARs: through the OCI console, the command line, and API. See Creating a Pre-Authenticated Request in Object Storage in Oracle Cloud Infrastructure Documentation.
Load the external files into MySQL HeatWave Cluster. See the following:
Learn how to load structured data using Lakehouse Auto Parallel Load. Review PAR examples.
Learn how to load data manually. Review PAR examples.
To review the syntax for loading structured data using Lakehouse Auto Parallel Load, see Auto Parallel Load Syntax and Lakehouse External Table Syntax.
Once Lakehouse successfully creates the external table from the external files, you can use the data in MySQL HeatWave. See the following: