5.2.2 Loading Data Using Lakehouse External Table

5.2.2.1 External Table Syntax

5.2.2.2 File Name, Pattern and Prefix Examples

Lakehouse uses external tables to load the data from Object Storage.

The external table syntax includes the following enhancements supported as of MySQL 8.4.0, MySQL 9.0.1, MySQL 9.2.0., and MySQL 9.3.1.

• MySQL 9.3.1

  • For HeatWave on OCI, you can load the external table by using a Uniform Resource Identifier (URI).

• MySQL 9.2.0

  • You can load the CSV files without knowing the exact values of the record_delimiter and the field_delimiter dialect parameters in advance.

    Lakehouse Autopilot can detect most of the common delimiters. For supported record delimiters and field delimiters, refer to dialect parameters.

    When record_delimiter and field_delimiter are set to auto, the delimiter is detected upon the first load of the external table (secondary_load).If the delimiter is detected successfully, the inferred delimiter values are substituted for record_delimiter and field_delimiter, and an Alter Table command is issued to set these detected delimiters.

• MySQL 9.0.1

  • timestamp_format as a dialect parameter allows you to customize the format for columns of the DATETIME and TIMESTAMP data types.

  • ENGINE_ATTRIBUTE option for specific columns. Use this option to customize the format for columns of the DATETIME and TIMESTAMP data types. Formats set with this parameter override formats set with the dialect parameter.

• MySQL 8.4.0

  • The JSON file format.

  • Support for primary key and unique key constraint validation.

  • is_strict_mode as a dialect parameter now supports all file formats. It can override the global sql_mode.

  • allow_missing_files is a dialect parameter and a file parameter that can allow missing files or not.