file_JSON_array is a JSON array containing the JSON objects representing the file location details and file parameters. You can use the following methods to specify these details:

Refer to File Options.

dialect_JSON_object is a JSON object containing the dialect parameters to specify the data format in the files.

model: Supported as of MySQL 9.3.2. Lets you specify the column-level embedding model for VECTOR columns. You must specify a model supported by MySQL HeatWave GenAI. If the vector uses another embedding model, you cannot use it with GenAI features. You can include an unsupported embedding model in the column or table comments for your own reference. You cannot use this to load data using Lakehouse Auto Parallel Load. Learn more about the Vector Data Type.

The following example manually loads a table and sets the embedding model for the column to multilingual-e5-small. If you are on a version earlier than MySQL 9.4.0, you must use CREATE TABLE and set ENGINE to lakehouse, and SECONDARY_ENGINE to rapid. See CREATE TABLE Statement.

mysql> CREATE EXTERNAL TABLE table_1(col_1 VECTOR(5) ENGINE_ATTRIBUTE '{"model": "multilingual-e5-small"}')
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

ENGINE: Set to lakehouse if you are on a version earlier than MySQL 9.4.0. For MySQL 9.4.0 and later, you can use the CREATE EXTERNAL TABLE statement, which automatically sets ENGINE to lakehouse.

SECONDARY_ENGINE: Set to rapid if you are on a version earlier than MySQL 9.4.0. For MySQL 9.4.0 and later, you can use the CREATE EXTERNAL TABLE statement, which automatically sets SECONDARY_ENGINE to rapid.

ENGINE_ATTRIBUTE: JSON object literal. Defines the location of files, the file format, and how the file format is handled.

Use key-value pairs in JSON format to specify options. Lakehouse uses the default setting if there is no defined option. Use NULL to specify no arguments.

file: Defines the Object Storage files. You can define the file locations using resource principals, uniform resource identifier (URI) (as of MySQL 9.3.1 for MySQL HeatWave on OCI), or PAR URL.

As of MySQL 9.3.1, Lakehouse supports a maximum of 170 file locations. Before MySQL 9.3.1, Lakehouse supports a maximum of 256 file locations. To define more than the maximum number of files, store the files under the same bucket or use prefix or pattern.

The following file parameters apply only to PAR and resource principals. Use one of the parameters, unless the target defines a specific file.

compression: Supported as of MySQL 9.3.2. Use this to load compressed files.

Compression is supported for all file formats:

Note the following when loading compressed files:

is_strict_mode: Loads data in strict mode or non-strict mode, based on the selected option. This overrides the is_strict_mode dialect parameter. To learn about the default value, see the is_strict_mode dialect parameter in Common Dialect Parameters. The supported options are:

The following example loads a table using Lakehouse Auto Parallel Load with a uniform resource identifier (URI) and sets is_strict_mode to false in the file parameters.

mysql> SET @input_list = '[{
  "db_name": "data_tables",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv", "is_strict_mode": false}]
    }
  }]
}]';
mysql> CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);

allow_missing_files: Supported as of MySQL 8.4.0. Ignores the missing files and completes the data loading based on the selected option. This overrides the dialect parameter is_strict_mode for missing files. To learn about the default value, see the allow_missing_files dialect parameter in Common Dialect Parameters. The supported options are:

The following example loads a table manually from a glob pattern specified with a uniform resource identifier (URI) and sets allow_missing_files to true in the file parameters. If any of the specified files in the pattern are not available, an error does not occur and data continues loading for the existing files. If all files are missing, an error occurs.

If you are on a version earlier than MySQL 9.4.0, you must use CREATE TABLE and set ENGINE to lakehouse, and SECONDARY_ENGINE to rapid. See CREATE TABLE Statement.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
                           "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_[1-10].csv", 
                           "allow_missing_files": true}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

auto_refresh_event_source: Supported as of MySQL 9.4.1. Specifies a Stream OCID for event-based incremental load. This string value is verified as a valid Stream resource identifier. Learn more about Resource Identifiers from Oracle Cloud Infrastructure Documentation. Learn how to Refresh Data Using Event-Based Incremental Load.

The following example loads a table using Lakehouse Auto Parallel Load and specifies a Stream OCID to set up event-based incremental load.

mysql> SET @input_list = '[{
  "db_name": "lakehouse_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv"},
      "auto_refresh_event_source": "ocid1.stream.oc1.iad...",
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]
    }
  }]
}]';
mysql> CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);

format: The file format defined in a table. You can define only one file format per table. The supported file formats are:

check_constraints: Supported as of MySQL 8.4.0. Lakehouse validates primary key and unique key constraints during the initial load based on the selected option. The supported options are:

The following example loads a table manually with a uniform resource identifier (URI) and sets check_constraints to false. If you are on a version earlier than MySQL 9.4.0, you must use CREATE TABLE and set ENGINE to lakehouse, and SECONDARY_ENGINE to rapid. See CREATE TABLE Statement.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv", "check_constraints": false},
                           "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

is_strict_mode: Loads data in strict mode or non-strict mode, based on the selected option. By default, is_strict_mode is set to the sql_mode value. See Strict SQL Mode. The file common parameter is_strict_mode can override this setting. The supported options are:

This setting overrides the global sql_mode setting for handling missing files. The default is the value of sql_mode. See Strict SQL Mode. The file common parameter is_strict_mode can override this setting.

The following example loads a table using Lakehouse Auto Parallel Load with a uniform resource identifier (URI) and sets is_strict_mode to false in the dialect parameters.

mysql> SET @input_list = '[{
  "db_name": "data_tables",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv", "is_strict_mode": false},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]
    }
  }]
}]';
mysql> CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);

allow_missing_files: Supported as of MySQL 8.4.0. Ignores the missing files and completes the data loading based on the selected option. This overrides the dialect parameter is_strict_mode for missing files. The supported options are:

A missing file is defined as:

The following example loads a table from a glob pattern specified with a uniform resource identifier (URI) and sets allow_missing_files to true in the dialect parameters. If any of the specified files in the pattern are not available, an error does not occur and data continues loading for the existing files. If all files are missing, an error occurs. If a file-specific setting is set, this may override the allow_missing_files setting.

mysql> SET @input_list = '[{
  "db_name": "data_tables",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv", "allow_missing_files": true},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_[1-10].csv"}]
    }
  }]
}]';
mysql> CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);

encoding: Defines the character encoding. The default is "utf8mb4".

record_delimiter: Defines one or more characters used to delimit records. The maximum record delimiter length is 64 characters. You can add any string as a custom_record_delimiter.

The default record delimiter for json is "\n". The only alternative for json is "\r\n".

For CSV files, if you set the record_delimiter to auto, Auto Parallel Load automatically detects the record delimiters and composite record delimiters with field delimiters as prefixes. Supported as of MySQL 9.2.0 and set as the default value.

The following record delimiters detected automatically by Auto Parallel Load:

embed_model_id: Supported as of MySQL 9.3.2. Lets you specify the embedding model for VECTOR columns in the table. You must specify a model supported by MySQL HeatWave GenAI. You cannot use this to load data manually. Learn more about the Vector Data Type.

The following example loads a table using Lakehouse Auto Parallel Load and sets the embedding model for all VECTOR columns detected in the table to minilm. Keep in mind that if the table already exists and there are VECTOR columns with an embedding model already set, the existing model is not replaced with the one specified in this example.

mysql> SET @input_list = '[{
  "db_name": "data_db",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv", "embed_model_id": "minilm"},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]
    }
  }]
}]';
mysql> CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);

field_delimiter: Defines one or more characters used to enclose fields. The maximum field delimiter length is 64 characters. You can add any string as a custom_field_delimiter.

When the field_delimiter is set to auto, Auto Parallel Load automatically detects the field delimiters. Supported as of MySQL 9.2.0 and set as the default value.

The following field delimiters are detected automatically by Auto Parallel Load:

The following example loads a table manually with a uniform resource identifier (URI) and sets the record_delimiter, and field_delimiter. The backslash character, \, needs to be escaped twice since it is inside an JSON object. If you are on a version earlier than MySQL 9.4.0, you must use CREATE TABLE and set ENGINE to lakehouse, and SECONDARY_ENGINE to rapid. See CREATE TABLE Statement.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv", 
                                       "record_delimiter": ";",
                                       "field_delimiter": "\\r\\n"},
                                       "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

escape_character: Defines one or more characters used to escape special characters. The default is "\\". You can add any string as a custom_escape_character.

quotation_marks: Defines one or more characters used to enclose fields. The default is "\'". You can add any string as a custom_quotation_marks.

The following example loads a table manually with a uniform resource identifier (URI) and sets the escape_character, and quotation_marks. The backslash character, \, needs to be escaped twice since it is inside an JSON object. If you are on a version earlier than MySQL 9.4.0, you must use CREATE TABLE and set ENGINE to lakehouse, and SECONDARY_ENGINE to rapid. See CREATE TABLE Statement.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv", 
                                       "escape_character": "\\\\",
                                       "quotation_marks": "\\""},
                                       "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

date_format: The date format, see: date_format. You can also set date formats for each column by using the column ENGINE_ATTRIBUTE option, which overrides the format in the dialect parameter. This format is ignored if you load data using Lakehouse Auto Parallel Load.

time_format: The time format, see: String and Numeric Literals in Date and Time Context. You can also set time formats for each column by using the column ENGINE_ATTRIBUTE option, which overrides the format in the dialect parameter. This format is ignored if you load data using Lakehouse Auto Parallel Load.

timestamp_format: Supported as of MySQL 9.0.1. The timestamp format, see: date_format. You can also set timestamp formats for each column by using the column ENGINE_ATTRIBUTE option, which overrides the format in the dialect parameter. This format is ignored if you load data using Lakehouse Auto Parallel Load.

The following example loads a table manually with a uniform resource identifier (URI) and sets the date_format, time_format, and timestamp_format. If you are on a version earlier than MySQL 9.4.0, you must use CREATE TABLE and set ENGINE to lakehouse, and SECONDARY_ENGINE to rapid. See CREATE TABLE Statement.

mysql> CREATE EXTERNAL TABLE table_1(
        col_1 date,
        col_2 time,
        col_3 timestamp,
        col_4 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv", 
                                       "date_format": "%W %M %Y",
                                       "time_format": "hh:mm:ss",
                                       "timestamp_format": "%Y-%m-%d %H:%i:%s"},
                                       "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}';
mysql> ALTER TABLE table_1 SECONDARY_LOAD;

trim_spaces: Removes/retains the leading and trailing spaces, based on the set option. The default value is false.

The following example loads a table using Lakehouse Auto Parallel Load with a uniform resource identifier (URI) and sets trim_spaces to true.

mysql> SET @input_list = '[{
  "db_name": "data_tables",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv", "trim_spaces": true},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]
    }
  }]
}]';
mysql> CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);

skip_rows: The number of rows to skip at the start of the file. The default value is 0 and the maximum value is 20.

has_header: Adds a header row to the CSV file, based on the selected option. The default is false.

If has_header and skip_rows are both defined, Lakehouse first skips the number of rows, and then uses the next row as the header row.

The following example loads a table using Lakehouse Auto Parallel Load with a uniform resource identifier (URI) and sets skip_rows and has_header. Since both parameters are set, the first row is skipped and the second row is set as the header row.

mysql> SET @input_list = '[{
  "db_name": "data_tables",
  "tables": [{
    "table_name": "table_1",
    "engine_attribute": {
      "dialect": {"format": "csv", "skip_rows": 1, "has_header": true},
      "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]
    }
  }]
}]';
mysql> CALL sys.HEATWAVE_LOAD(CAST(@input_list AS JSON), NULL);

Learn about Lakehouse Auto Parallel Load

Learn how to Load Structured Data Using Lakehouse Auto Parallel Load

Learn how to Load Structured Data Manually

Learn how to Refresh Data Using Event-Based Incremental Load