4.3.10 Create Tables with Stored Generated Columns

As of MySQL 9.5.1, you have the option to include stored generated columns when creating external Lakehouse tables. To learn more about generated columns, see CREATE TABLE and Generated Columns.

Before You Begin

• Prepare to create external tables by reviewing the following:

  • Additional MySQL HeatWave Lakehouse Requirements

  • MySQL HeatWave Lakehouse Privileges

  • Supported File Format

  • Access Object Storage

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

Overview of Stored Generated Columns

The syntax for creating external tables with generated columns is the following:

CREATE EXTERNAL TABLE table_name 
(col_name data_type ['Col_Options_SQL'], ...
col_name data_type [GENERATED ALWAYS] AS (expr)
  STORED [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string'], ...
)Tbl_Options_SQL;

Learn more about Lakehouse External Table SQL Syntax and Lakehouse External Table JSON Syntax.

• You can include stored generated columns for CSV, Parquet, and Avro file formats. Stored generated columns are not supported for the JSON file format, unstructured format, Delta Lake tables, or the VECTOR data type.

• You must use the STORED keyword, otherwise a VIRTUAL generated column is assumed, which is not supported for external tables.

• The expression (expr) for stored generated columns can include any of the following functions:

  • ABS()

  • CEILING()

  • CONCAT()

  • DATEDIFF()

  • DAY()

  • DAYOFMONTH()

  • DAYOFWEEK()

  • DAYOFYEAR()

  • EXTRACT()

  • FLOOR()

  • HOUR()

  • JSON_EXTRACT()

  • MICROSECOND()

  • MINUTE()

  • MOD()

  • MONTH()

  • QUARTER()

  • SECOND()

  • SUBSTR()

  • SUBSTRING_INDEX()

  • TIME_TO_SEC()

  • TO_DAYS()

  • TO_SECONDS()

  • WEEKDAY()

  • YEAR()

  • YEARWEEK()

  Any combination of the previous list of supported functions that is supported by MySQL HeatWave is also supported for stored generated columns. See Supported Functions and Operators to learn more.

• Generated column expressions can be in any of the following forms:

  • A function over an existing column of the table. For example:

    CREATE EXTERNAL TABLE customer (
         date_col DATE, 
         month_col INT GENERATED ALWAYS AS (MONTH(`date_col`)) STORED
    );

  • A function over a constant value. For example:

    CREATE EXTERNAL TABLE customer (
        date_col DATE,
        month_col INT GENERATED ALWAYS AS (MONTH('2008-02-03')) STORED
    );

  • The expression refers directly to another column of the table. For example:

    CREATE EXTERNAL TABLE customer (
        date_col DATE,
        genColDate DATE GENERATED ALWAYS AS (`date_col`) STORED
    );

  • The expression refers to a constant value. For example:

    CREATE EXTERNAL TABLE customer (
        date_col DATE,
        genColDate DATE GENERATED ALWAYS AS ('2008-02-03') STORED
    );

• If you load an external table with generated columns, either with Auto Parallel Load by using HEATWAVE_LOAD or manually with Guided Load, the table must be prevously created. See Load External Data Using Lakehouse Auto Parallel Load with an Existing Table and Guided Load.

• You can include the AS keyword before GENERATED ALWAYS to make the generated nature of the column more explicit.

• Stored generated columns are evaluated when loading data into external tables using the ALTER TABLE table_name SECONDARY_LOAD statement. After data is loaded, subsequent queries on the table display resulting values of the generated columns.

• Due to the fact that the query for stored generated columns runs at the same time that data is loaded to an external table, statistics are not available beforehand, and therefore MySQL HeatWave might overestimate the required size of VARLEN columns. This can lead to predictions that the load exceeds 4MB and an error.

• For additional limitations, see Lakehouse Limitations for all File Formats.

Stored Generated Columns Example

The following example creates an external table with stored generated columns:

CREATE EXTERNAL TABLE customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(200) NOT NULL,
    birth_date DATE,
    registration_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    full_name VARCHAR(201) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED,
    birth_year INT GENERATED ALWAYS AS (YEAR(birth_date)) STORED
);

The first stored generated column, full_name, combines the first and last name into a single string, and the second stored generated column, birth_year, extracts the year portion of the birth_date.

What's Next

• Review how to load data manually or using Auto Parallel Load with an existing table.