Generated columns

Benefits and use cases

• Automatic field computation for improved data consistency

  Different teams may apply different calculation logic to the same field, leading to inconsistencies. A generated column embeds the calculation logic in the table schema so that the MaxCompute engine applies it uniformly during every write operation, ensuring that derived fields remain consistent across all pipelines.

• Optimized query performance by eliminating redundant computation

  Derived fields such as time truncation, ID normalization, and hash bucketing are frequently used in queries. Because a stored generated column computes and persists these values at write time, queries can read the results directly without re-evaluating complex expressions. This significantly improves performance, especially for large tables, wide tables, and high-concurrency analytics scenarios.

• Simplified SQL and lower barrier to data consumption

  MaxCompute users span development, algorithm, and analytics roles. Generated columns embed common data processing logic in the table schema, freeing consumers from writing repetitive expressions. This reduces SQL complexity, lowers the learning curve, and promotes a more standardized, reusable data model.

• Improved maintainability and readability of data models

  Generated columns make field derivation logic explicit and structural in the table definition. This is well suited for data warehouse modeling, wide-table construction, and feature engineering, and facilitates data lineage analysis, field documentation, and modular governance.

Limits

• Expression restrictions

  The expression of a generated column must be deterministic. The following are not supported:

  • Non-deterministic functions such as RAND(), CURRENT_TIMESTAMP(), and UUID().

  • Aggregate functions such as SUM() and COUNT().

  • Window functions such as ROW_NUMBER().

  • Table-valued functions or cross-row/cross-table references.

  The expression can reference ordinary columns defined before or after the generated column (order does not matter), but cannot reference other generated columns or partition columns.

• Column property and definition constraints

  • The data type of a generated column must be compatible with the result type of the expression. Otherwise, the DDL statement fails.

  • Specifying a DEFAULT value for a generated column is not supported.

  • Modifying the data type of a generated column is not supported.

  • Renaming a generated column is not supported.

  • A generated column cannot serve as a partition column.

• DDL restrictions

  • Dropping a source column that is referenced by a generated column is not supported. Drop the generated column first, then drop the source column.

  • Modifying the data type of a source column that a generated column depends on is not supported.

• Data write restrictions

  Explicitly specifying a value for a generated column in an INSERT or UPDATE statement is not allowed. The system computes the value automatically.

  Writing to a table that contains generated columns through the following methods is not supported and results in an error:

  • MaxCompute Tunnel.

  • Batch data import from DataWorks or Studio.

  • Direct writes from other external data integration tools.

• Metadata and queries

  The DESC table_name; command does not display the expression of a generated column.

  To view the generated column definition, run the SHOW CREATE TABLE table_name; command.

Example 1: Basic arithmetic generated column

-- Create an orders table that automatically computes the total price.
CREATE TABLE orders (
  order_id STRING,
  quantity BIGINT,
  unit_price DECIMAL(10,2),
  total_price DECIMAL(30,2)
    GENERATED ALWAYS AS (quantity * unit_price) STORED COMMENT 'Auto-computed total price'
);

-- Insert data. You do not need to specify total_price.
INSERT INTO orders
VALUES ('ORD001', 5, 19.99);

-- Query results.
SELECT * FROM orders;
-- The following result is returned:
+----------+------------+------------+-------------+
| order_id | quantity   | unit_price | total_price |
+----------+------------+------------+-------------+
| ORD001   | 5          | 19.99      | 99.95       |
+----------+------------+------------+-------------+

Example 2: String concatenation generated column

-- Create a users table that automatically generates the full name.
CREATE TABLE users (
  full_name STRING
    GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED,
  first_name STRING,
  last_name STRING
);

INSERT INTO users
VALUES ('John', 'Doe');

-- Query results.
SELECT * FROM users;
-- The following result is returned:
+-----------+------------+-----------+
| full_name | first_name | last_name |
+-----------+------------+-----------+
| John Doe  | John       | Doe       |
+-----------+------------+-----------+

Example 3: Date processing generated column

-- Create a logs table that automatically generates a year-month identifier.
CREATE TABLE logs (
  event_time DATETIME,
  log_message STRING,
  ym_partition STRING
    GENERATED ALWAYS AS
    (CONCAT(YEAR(event_time), '-', LPAD(MONTH(event_time), 2, '0'))) STORED
);

INSERT INTO logs (event_time, log_message)
VALUES (DATETIME '2023-10-15 14:30:00', 'User login');

-- Query results.
SELECT * FROM logs;
-- The following result is returned:
+---------------------+-------------+--------------+
| event_time          | log_message | ym_partition |
+---------------------+-------------+--------------+
| 2023-10-15 14:30:00 | User login  | 2023-10      |
+---------------------+-------------+--------------+

Example 4: Add a generated column to an existing table

-- Create a products table that automatically generates a price tier.
CREATE TABLE products (
  product_id STRING,
  price DECIMAL(10,2)
);

-- Add a generated column.
ALTER TABLE products ADD COLUMNS (price_tier STRING
    GENERATED ALWAYS AS (
      CASE
        WHEN price < 10 THEN 'Low'
        WHEN price < 50 THEN 'Medium'
        ELSE 'High'
      END
    ) STORED);

INSERT INTO products
VALUES ('P001', 5.99), ('P002', 75.00);

-- Query results.
SELECT product_id, price_tier FROM products;
-- The following result is returned:
+------------+------------+
| product_id | price_tier |
+------------+------------+
| P001       | Low        |
| P002       | High       |
+------------+------------+