Load the data automatically with Lakehouse Auto Parallel Load by using the HEATWAVE_LOAD command. See how to Load External Data Using Lakehouse Auto Parallel Load with an Existing Table.

Load the data manually with the ALTER TABLE statement and the option to use Guided Load.

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. Learn more about the Vector Data Type.

The following example sets the embedding model for the column to multilingual-e5-small.

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"}]}';

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. Specifies 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.4.1, Lakehouse supports a maximum of 164 file locations. 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 sets is_strict_mode to false in the file parameters.

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

allow_missing_files: Supported as of MySQL 8.4.0. Handles missing files 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 uses 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.

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}]}';

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 specifies a Stream OCID to set up event-based incremental load.

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

match_columns_by: Supported as of MySQL 9.5.0. Allows you to map columns from Parquet files to external tables based on column order, column name using case sensitivity, or column name using case insensitivity.

match_columns_by has the following requirements and specifications:

match_columns_by has the following options:

The following example loads columns from a Parquet file based on name using case sensitivity. Lakehouse maps columns in the Parquet file to the columns in the external table (col_1, col_2, col_3) with the same name and case sensitivity.

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

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 sets check_constraints to false.

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"}]}';

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 sets is_strict_mode to false in the dialect parameters.

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

allow_missing_files: Supported as of MySQL 8.4.0. Handles missing files 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 uses a glob pattern specified with a uniform resource identifier (URI). It 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> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv", "allow_missing_files": true},
                           "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_[1-10].csv"}]}';

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

record_delimiter: Specifies 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. To specify an embedding model for a table, you must use Auto Parallel Load to automatically create the table and load the data. See the example below. If you want to use a CREATE EXTERNAL TABLE statement to create the table manually and specify an embedding model, provide the embedding model as a column definition. Learn more about the Vector Data Type.

The following example uses Auto Parallel Load to set 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"}]
    }
  }]
}]';

field_delimiter: Specifies 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 sets the record_delimiter, and field_delimiter. The backslash character, \, needs to be escaped twice since it is inside an JSON object.

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"}]}';

escape_character: Specifies one or more characters used to escape special characters. The default is "\\". You can add any string as a custom_escape_character. As of MySQL 9.5.0, you have the option to ignore the escape character by setting its value to an empty string.

quotation_marks: Specifies one or more characters used to enclose fields. The default is "\'". You can add any string as custom_quotation_marks. As of MySQL 9.5.0, you have the option to ignore the quote character by setting its value to an empty string.

Consider the following example for setting empty strings for escape and quotation characters. You have the following data in a CSV file: "{""doc1"":{\""doc2\"":22}}".

The following example sets the escape_character, and quotation_marks. The backslash character, \, needs to be escaped twice since it is inside an JSON object.

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"}]}';

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. For versions earlier than MySQL 9.5.0, this format is ignored if you load data using Lakehouse Auto Parallel Load or manually with Guided Load. As of MySQL 9.5.0, the format specified at the dialect level is used to infer the temporal data if you load data using Lakehouse Auto Parallel Load or manually with Guided 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. For versions earlier than MySQL 9.5.0, this format is ignored if you load data using Lakehouse Auto Parallel Load or manually with Guided Load. As of MySQL 9.5.0, the format specified at the dialect level is used to infer the temporal data if you load data using Lakehouse Auto Parallel Load or manually with Guided 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. For versions earlier than MySQL 9.5.0, this format is ignored if you load data using Lakehouse Auto Parallel Load or manually with Guided Load. As of MySQL 9.5.0, the format specified at the dialect level is used to infer the temporal data if you load data using Lakehouse Auto Parallel Load or manually with Guided Load.

As of MySQL 9.5.0, Lakehouse Auto Parallel Load and Guided Load can infer supported formats for temporal data types by setting the appropriate dialect option to auto. The auto setting is the default value if no temporal format is specified. See About Lakehouse Auto Parallel Load Schema Inference to learn more.

The following example sets the date_format, time_format, and timestamp_format.

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": "%d/%m/%Y",
                                       "time_format": "%I:%i %p",
                                       "timestamp_format": "%Y-%m-%d %H:%i:%s"},
                                       "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}';

The following example sets the date_format, time_format, and timestamp_format options to auto, which allows Auto Parallel Load and Guided Load to infer supported formats for temporal data types.

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": "auto",
                                       "time_format": "auto",
                                       "timestamp_format": "auto"},
                                       "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}';

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

The following example sets trim_spaces to true.

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

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 specified, Lakehouse first skips the number of rows, and then uses the next row as the header row.

The following example creates a table with Auto Parallel Load 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> CREATE EXTERNAL TABLE table_1(
        col_1 int,
        col_2 int,
        col_3 int)
        ENGINE_ATTRIBUTE='{"dialect": {"format": "csv", "skip_rows": 1, "has_header": true},
                                       "file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_1.csv"}]}';

Learn about Lakehouse Auto Parallel Load

Learn how to create external tables using Auto Parallel Load or manually.

Learn how to Refresh Data Using Event-Based Incremental Load.