生成列(Generated Column)

更新时间:
复制 MD 格式

生成列(Generated Column)是MaxCompute提供的一种特殊列类型,其值由表中其他列通过确定性表达式自动计算得出,无需手动插入或更新。本文介绍生成列的功能、适用范围、命令格式和使用示例。

功能介绍

生成列(Generated Column)是 MaxCompute 提供的一种特殊列类型,其值由表中其他列通过确定性表达式自动计算得出,无需用户手动插入或更新。生成列可以显著简化数据处理逻辑、保证数据一致性,并提升查询性能。

生成列分为两类:

  • 虚拟生成列(Virtual Generated Column):仅在查询时动态计算,不实际存储数据。

  • 存储生成列(Stored Generated Column):在写入时计算并持久化存储,查询时直接读取。

重要

当前 MaxCompute 仅支持存储生成列。

优势及使用场景

  • 自动完成字段计算,提升数据一致性
    业务侧对同一字段可能存在不同版本的计算方式,容易造成数据口径不一致。生成列将计算逻辑沉淀在表结构中,由 MaxCompute 引擎统一执行,确保所有写入流程生成的派生字段始终保持一致,提升数据的准确性和可信度。

  • 优化查询性能,避免重复计算带来的开销
    在实际查询场景中,许多派生字段(如时间截断、ID 归一化、哈希分桶等)属于高频计算。
    生成列在写入时已完成计算并以物理列存储,查询时无需再次执行复杂表达式,可显著提升查询性能,尤其适合在大表、宽表、高并发分析场景中使用。

  • 简化 SQL 逻辑,降低数据使用门槛
    MaxCompute 用户通常分布在开发、算法、分析等不同角色。生成列将常用的数据处理逻辑内嵌在表结构中,使用者无需重复编写表达式,降低理解成本和 SQL 复杂度,有助于形成更标准化、可复用的数据模型。

  • 提升数据模型可维护性与可读性
    生成列将字段派生逻辑显式化、结构化地记录在表定义中,更适合用于数据仓库建模、大宽表构建、特征工程字段管理等场景,有助于数据血缘分析、字段解释、模块化治理。

适用范围

  • 表达式限制

    • 生成列表达式必须是确定性(Deterministic)的,不支持:

      • 非确定性函数(如 RAND()、CURRENT_TIMESTAMP()、UUID() 等);

      • 聚合函数(如 SUM()、COUNT());

      • 窗口函数(如 ROW_NUMBER());

      • 表值函数或跨行/跨表引用。

    • 表达式可引用当前列之前或之后定义的普通列(顺序无关),不能引用其他生成列或者分区列。

  • 列属性与定义约束

    • 生成列的数据类型必须与表达式计算结果的类型兼容,否则 DDL 将失败。

    • 不支持为生成列指定 DEFAULT 值。

    • 不支持修改生成列的数据类型。

    • 不支持重命名(RENAME)生成列的列名。

    • 生成列不能作为分区字段

  • 表结构变更(DDL)限制

    • 不支持删除被生成列引用的源列(需先删除生成列,再删除源列)。

    • 不支持修改生成列所依赖的源列的数据类型。

  • 数据写入限制

    • 禁止在 INSERT 或 UPDATE 语句中显式指定生成列的值(系统会自动计算)。

    • 不支持通过以下方式写入含生成列的表(会报错):

      • MaxCompute 数据通道(Tunnel);

      • DataWorks / Studio 的批量数据导入;

      • 其他外部数据集成工具的直接写入。

  • 元数据与查询

    • DESC table_name;命令不会显示生成列的表达式。

    • 如需查看生成列定义,请使用SHOW CREATE TABLE table_name;命令。

命令格式

  • 创建表时定义生成列

    CREATE TABLE <table_name> (
      <col_name1> <data_type>,
      <col_name2> <data_type>,
      ...
      <generated_col_name> <data_type> 
        GENERATED ALWAYS AS (<expression>) STORED [COMMENT <comment>],
      ...
      <col_nameN> <data_type>
    )
    [COMMENT <table_comment>]
    [TBLPROPERTIES ('property'='value', ...)]
    ;
  • 修改表添加生成列

    ALTER TABLE <table_name> 
    ADD COLUMNS (
       <col_name1> <data_type>,
      ...
      <generated_col_name> <data_type> 
        GENERATED ALWAYS AS (<expression>) STORED [COMMENT <comment>]
    );

参数说明

参数

是否必填

说明

备注

table_name

是

表名。

表名大小写不敏感,不能有特殊字符,只能包含a~z、A~Z、数字和下划线(_)。建议以字母开头,名称的长度不超过128字节,否则报错。

col_name

是

普通列的列名。

  • 列名大小写不敏感,不能有特殊字符,只能包含a~z、A~Z、数字、下划线(_)或中文。建议以字母开头,名称的长度不超过128字节,否则报错。

  • 执行SET odps.sql.bigquery.compatible=true;命令开启BigQuery兼容模式,列名的长度不超过256字节。否则报错。

data_type

是

列的数据类型。

包含BIGINT、DOUBLE、BOOLEAN、DATETIME、DECIMAL和STRING等多种数据类型,详情请参见数据类型版本说明。

generated_col_name

是

生成列的名称。

规则同 col_name。

expression

是

生成列的计算表达式。

必须为确定性计算表达式,且仅允许引用当前表的普通列。

comment

否

生成列的注释。

注释内容为长度不超过1024字节的有效字符串,否则报错。

table_comment

否

表注释。

注释内容为长度不超过1024字节的有效字符串,否则报错

使用示例

示例1:基础算术生成列

-- 创建订单表,自动计算总价
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 '自动计算总价'
);

-- 插入数据(无需指定 total_price)
INSERT INTO orders 
VALUES ('ORD001', 5, 19.99);

-- 查询结果
SELECT * FROM orders;
-- 返回结果
+----------+------------+------------+-------------+
| order_id | quantity   | unit_price | total_price |
+----------+------------+------------+-------------+
| ORD001   | 5          | 19.99      | 99.95       |
+----------+------------+------------+-------------+

示例2:字符串拼接生成列

-- 创建用户表,自动生成全名
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');

-- 查询结果
SELECT * FROM users; 
-- 返回结果
+-----------+------------+-----------+
| full_name | first_name | last_name |
+-----------+------------+-----------+
| John Doe  | John       | Doe       |
+-----------+------------+-----------+

示例3:日期处理生成列

-- 创建日志表,自动生成年月分区标识
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');

-- 查询结果
SELECT ym_partition FROM logs; 
-- 返回结果
+---------------------+-------------+--------------+
| event_time          | log_message | ym_partition |
+---------------------+-------------+--------------+
| 2023-10-15 14:30:00 | User login  | 2023-10      |
+---------------------+-------------+--------------+

示例4:为已有表添加生成列

-- 创建商品表,自动生成价格等级
CREATE TABLE products (
  product_id STRING,
  price DECIMAL(10,2)
);

-- 添加生成列
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);

-- 查询结果
SELECT product_id, price_tier FROM products;
-- 返回结果
+------------+------------+
| product_id | price_tier |
+------------+------------+
| P001       | Low        |
| P002       | High       |
+------------+------------+