4.3.9.2 Create Tables with Metadata Columns

As of MySQL 9.6.1, MySQL HeatWave Lakehouse supports the _metadata_filename column, which is a reserved column name that stores file name information for each row of data.

Before You Begin

Review how to Create an External Table Manually.

Overview

• You can use metadata columns for all supported MySQL HeatWave Lakehouse file formats for structured and semi-structured data. See Supported File Formats for Structured and Semi-Structured Data. For unstructured data, the document_name column serves as the metadata filename column. See Section 4.3.4, “Supported File Formats and Data Types”.

• The _metadata_filename metadata column is treated as a normal table column and must be part of the table definition.

• Specifying an invalid data type for a metadata column generates an error if STRICT_LOAD or is_strict_mode is enabled, or a warning message if STRICT_LOAD or is_strict_mode is disabled. We recommend using the data type VARCHAR(1024).

• Guided Load is disabled for tables with metadata columns.

• Specifying a generated column as a metadata column generates an error.

• When you use the match_columns_by option and specify the _metadata_filename column in the schema, the specified files must not contain a _metadata_filename column, as it conflicts with the metadata column. In that case the file column loads and not the metadata column.

For example, you create a table that includes the _metadata_filename column:

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

The command loads data_file_1, data_file_2, and data_file_3 into the external table.

The files load the following values:

• data_file_1: 1, 2

• data_file_2: 2, 3

• data_file_3: 3, 4

The output of the table looks like this:

mysql> SELECT * FROM table_1;
+-------+-------+-----------------------------------------------------------------------------------------------+
| col_1 | col_2 | _metadata_filename                                                                            |
+-------+-------+-----------------------------------------------------------------------------------------------+
| 1     | 2     | https://objectstorage.us-ashburn-1.oraclecloud.com/n/mynamespace/b/mybucket/o/data_file_1.csv |
| 2     | 3     | https://objectstorage.us-ashburn-1.oraclecloud.com/n/mynamespace/b/mybucket/o/data_file_2.csv |
| 3     | 4     | https://objectstorage.us-ashburn-1.oraclecloud.com/n/mynamespace/b/mybucket/o/data_file_3.csv |
+-------+-------+-----------------------------------------------------------------------------------------------+

The table shows the file name associated with each row of data.

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.