4.3.9.3 Create Tables with Key Partitioning

As of MySQL 9.6.1, you can specify the PARTITION BY KEY option in the CREATE EXTERNAL TABLE statement.

Learn more about KEY Partitioning from the MySQL Reference Manual.

Before You Begin

Review how to Create an External Table Manually.

Overview

• For structured and semi-structured data, the column specified for key partitioning must be a metadata column, or a generated column over a metadata column. For unstructured data, the column specified must be the document_id column. See Metadata Columns.

• You can use key partitioning for all supported MySQL HeatWave Lakehouse file formats. See Supported File Formats for Structured and Semi-Structured Data and Supported File Formats for Unstructured Data.

• The paritioning type must be KEY, otherwise an error is generated.

• Once MySQL HeatWave Lakehouse loads a table, you cannot modify the partitioning.

Examples with SQL Syntax

The following example specifies key partitioning. The required metadata column _metadata_filename is specified.

mysql> CREATE EXTERNAL TABLE table_1 (
 col_1 int,
 col_2 int,
 _metadata_filename varchar(1024)
)
FILE_FORMAT = (FORMAT csv)
FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_[1-3].csv')
PARTITION BY KEY (_metadata_filename);

The following example uses a generated column over the metadata column. It extracts only the name of the files from the specified URI.

mysql> CREATE EXTERNAL TABLE table_1 (
 col_1 int,
 col_2 int,
 _metadata_filename varchar(1024),
 partition_col VARCHAR(128) generated always as (substring_index(_metadata_filename, '/', -1)) stored
)
FILE_FORMAT = (FORMAT csv)
FILES = (URI = 'oci://mybucket@mynamespace/data_files/data_file_[1-3].csv')
PARTITION BY KEY (partition_col);

To learn more, see Create Tables with Stored Generated Columns.

Examples with JSON Syntax

The following example specifies key partitioning. The required metadata column _metadata_filename is specified.

mysql> CREATE EXTERNAL TABLE table_1 (
 col_1 int,
 col_2 int,
 _metadata_filename varchar(1024)
)
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"}, 
"file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_[1-3].csv"}]}'
PARTITION BY KEY (_metadata_filename);

The following example uses a generated column over the metadata column. It extracts only the name of the files from the specified URI.

mysql> CREATE EXTERNAL TABLE table_1 (
 col_1 int,
 col_2 int,
 _metadata_filename varchar(1024),
 partition_col VARCHAR(128) generated always as (substring_index(_metadata_filename, '/', -1)) stored
)
ENGINE_ATTRIBUTE='{"dialect": {"format": "csv"},
"file": [{"uri": "oci://mybucket@mynamespace/data_files/data_file_[1-3].csv"}]}'
PARTITION BY KEY (partition_col);

What's Next

• Review different ways to specify files when creating external tables manually:

  • Using Uniform Resource Identifiers

  • Using Resource Principals

  • Using Pre-Authenticated Requests

• Review how to Load Structured Data Manually.