MySQL :: HeatWave User Guide :: 4.3.11 Load Structured Data Manually

HeatWave User Guide / ... / Load Structured Data Manually

4.3.11 Load Structured Data Manually

After creating an external table manaully and specifying the files to load into the table, you specify the SECONDARY_UNLOAD clause in an ALTER TABLE statement to load the data from the files into the table.

This topic contains the following sections:

Before You Begin

Complete the steps to Create an External Table Manually.

Loading Tables Statement

To load an external table into MySQL HeatWave Cluster with data from files, specify the SECONDARY_LOAD clause in an ALTER TABLE statement and use the following syntax.

ALTER TABLE table_name SECONDARY_LOAD [GUIDED {ON | OFF}];

Specify the following clauses in the ALTER TABLE statement:

table_name: Specify the name of the table to load to MySQL HeatWave.
GUIDED: Available as of MySQL 9.4.1. Allows you to disable and enable Guided Load as needed. Set to OFF to disable Guided Load. By default, Guided Load is enabled.

Guided Load

Available as of MySQL 8.2.0, the Guided Load feature performs a set of checks and validations before loading data.

These checks include the following:

Automatically detect tables and columns that cannot be loaded. If there are tables and columns that are not compatible, stop the load.
Automatically set SECONDARY_ENGINE to rapid.
Detect any errors with ENGINE_ATTRIBUTE and report them.
Infer the table definition and make any necessary adjustments before loading data. These adjustments are similar to those performed by Autopilot during Lakehouse Auto Parallel Load. See: About Lakehouse Auto Parallel Load Schema Inference. If the inferred table definition is not compatible, stop the load.
Predict the amount of memory required for loading data. If the required memory is not available, stop the load.
Infer the record and field delimiters for CSV files and make the necessary adjustments.

As of MySQL 9.4.1, you have the option to disable Guided Load if you want to skip these checks.

The following example manually creates an external table, and then loads the table into MySQL HeatWave with Guided Load disabled:

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 GUIDED OFF;

Load External Data Manually Example

To demonstrate how to load data manually, the following example uses the same file in the Create External Table Manually Example.

Create the external table. For this example, use the following command:

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');

Use the ALTER TABLE clause in the SECONDARY_LOAD command to load the data into the table.

mysql> ALTER TABLE bank_marketing SECONDARY_LOAD;
Warning (code 3877): Command executed during preprocessing: 'ALTER TABLE `bank_data`.`bank_marketing` 
ENGINE_ATTRIBUTE='{"file": [{"par": "https://objectstorage.us-ashburn-1.oraclecloud.com/p/.../n/tenant_1/b/bucket_1/o/bank.csv"}], 
                   "dialect": {"format": "csv", "has_header": true, "field_delimiter": ";", "record_delimiter": "\\n"}}''.

Review the message to confirm the external table is successfully created. For MySQL 9.1.2 and later, you can also review the field_delimiter and record_delimiter values that MySQL HeatWave automatically detected for the table.

Optionally, query five rows of the table to confirm the data is loaded and accessible.

mysql> SELECT * FROM bank_marketing.bank_train LIMIT 5;
+-----+-------------+----------+-----------+----------+---------+---------+------+-----------+-----+-------+----------+----------+-------+----------+----------+----+
| age | job         | marital  | education | default1 | balance | housing | loan | contact   | day | month | duration | campaign | pdays | previous | poutcome | y  |
+-----+-------------+----------+-----------+----------+---------+---------+------+-----------+-----+-------+----------+----------+-------+----------+----------+----+
|  37 | services    | married  | secondary | no       |    4760 | yes     | no   | cellular  |   8 | may   |      182 |        2 |   169 |        2 | failure  | no |
|  32 | technician  | single   | secondary | no       |    2979 | no      | no   | cellular  |  25 | may   |      156 |        1 |    -1 |        0 | unknown  | no |
|  43 | management  | married  | tertiary  | no       |     690 | yes     | no   | cellular  |   6 | aug   |      171 |        3 |    -1 |        0 | unknown  | no |
|  50 | blue-collar | divorced | secondary | no       |     203 | yes     | no   | telephone |  19 | nov   |      265 |        1 |   127 |        4 | other    | no |
|  34 | blue-collar | married  | secondary | no       |     322 | yes     | no   | cellular  |  20 | apr   |       10 |        3 |    -1 |        0 | unknown  | no |
+-----+-------------+----------+-----------+----------+---------+---------+------+-----------+-----+-------+----------+----------+-------+----------+----------+----+

What's Next

After successfully loading the table to MySQL HeatWave Cluster with data from the specified files, learn how to do the following:

PREV HOME UP NEXT