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.

Select the method to specify the files for the external table: uniform resource identifier (URI), resource principal, or PAR. To learn more about each method, see the following:

Connect to your MySQL HeatWave Database System.

In the terminal window, create and use the database to store the table.

mysql> CREATE DATABASE bank_data;
mysql> USE DATABASE bank_data;

Set up the CREATE EXTERNAL TABLE statement and the ENGINE_ATTRIBUTE options to specify the parameters needed to process the external files. In versions earlier than MySQL 9.4.0, you must use the CREATE TABLE statement and set ENGINE to lakehouse, and SECONDARY_ENGINE to rapid.

Ensure that the table has the correct data type for each column. For this example, columns are defined according to the data in the bank.csv file. See the following to learn more:

As of MySQL 9.4.0, you can use SQL syntax to set options for external tables. For earlier versions, you must set options using JSON syntax. To learn more, see Lakehouse External Table JSON Syntax and Lakehouse External Table SQL Syntax.

The following example uses SQL syntax:

mysql> CREATE EXTERNAL TABLE bank_marketing(
        age int,
        job varchar(255),
        marital varchar(255),
        education varchar(255),
        default1 varchar(255),
        balance float,
        housing varchar(255),
        loan varchar(255),
        contact varchar(255),
        day int,
        month varchar(255),
        duration float,
        campaign int,
        pdays float,
        previous float,
        poutcome varchar(255),
        y varchar(255)
    )
        FILE_FORMAT = (FORMAT csv HEADER ON)
        FILES = (URI = 'oci://mybucket@mynamespace/bucket_1/bank.csv');

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.

Where:

If you are on a version earlier than MySQL 9.4.0, you must use the following JSON syntax:

mysql> CREATE EXTERNAL TABLE bank_marketing(
        age int,
        job varchar(255),
        marital varchar(255),
        education varchar(255),
        default1 varchar(255),
        balance float,
        housing varchar(255),
        loan varchar(255),
        contact varchar(255),
        day int,
        month varchar(255),
        duration float,
        campaign int,
        pdays float,
        previous float,
        poutcome varchar(255),
        y varchar(255)
    )
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv", "has_header": true},
                           "file": [{ "uri": "oci://mybucket@mynamespace/bucket_1/bank.csv"}]}';

When you are ready to load the external table with data from the specified files, use the SECONDARY_LOAD clause in an ALTER TABLE statement. See Load Structured Data Manually.