Generated columns

生成列

自从 v3.1 版本开始，StarRocks 支持生成列。生成列可用于加速包含复杂表达式的查询。该功能支持预计算和存储表达式的结果和查询重写，从而显著提高了包含相同复杂表达式的查询性能。

您可以在创建表时定义一个或多个生成列来存储表达式的结果。因此，当执行包含您定义的生成列结果的表达式的查询时，CBO 会将查询重写为直接从生成列中读取数据。或者，您可以直接查询生成列中的数据。

由于计算表达式需要一些时间，因此建议评估生成列对加载性能的影响。此外，建议在创建表时**创建生成列，而不是在表创建后添加或修改它们**。因为在表创建后添加或修改生成列是耗时且昂贵的。

基本操作

创建生成列

语法

<col_name> <data_type> [NULL] AS <expr> [COMMENT 'string']

推荐在表创建时创建生成列

创建一个名为 test_tbl1 的表，该表有五个列，其中列 newcol1 和 newcol2 是生成列，其值是通过使用指定的表达式并引用常规列 data_array 和 data_json 的值来计算的。

CREATE TABLE test_tbl1
(
    id INT NOT NULL,
    data_array ARRAY<int> NOT NULL,
    data_json JSON NOT NULL,
    newcol1 DOUBLE AS array_avg(data_array),
    newcol2 String AS json_string(json_query(data_json, "$.a"))
)
PRIMARY KEY (id)
DISTRIBUTED BY HASH(id);

注意：

• 生成列的定义必须在常规列之后。
• 在生成列的表达式中不能使用聚合函数。
• 生成列的表达式不能引用其他生成列或自增列，但可以引用多个常规列。
• 生成列的数据类型必须与生成列表达式生成的结果的数据类型匹配。
• 不能在聚合表上创建生成列。
• 当前，StarRocks 的共享数据模式不支持生成列。

在表创建后添加生成列

注意

该操作消耗时间且资源密集。因此，建议在表创建时添加生成列。如果无法避免使用 ALTER TABLE 添加生成列，则推荐预先评估相关成本和时间。

1. 创建一个名为 test_tbl2 的表，该表有三个常规列 id、data_array 和 data_json。插入一个数据行到该表中。

   -- 创建表。
   CREATE TABLE test_tbl2
   (
       id INT NOT NULL,
       data_array ARRAY<int> NOT NULL,
       data_json JSON NOT NULL
   )
   PRIMARY KEY (id)
   DISTRIBUTED BY HASH(id);
   
   -- 插入一个数据行。
   INSERT INTO test_tbl2 VALUES (1, [1,2], parse_json('{"a" : 1, "b" : 2}'));
   
   -- 查询表。
   MySQL [example_db]> select * from test_tbl2;
   +------+------------+------------------+
   | id   | data_array | data_json        |
   +------+------------+------------------+
   |    1 | [1,2]      | {"a": 1, "b": 2} |
   +------+------------+------------------+
   1 row in set (0.04 sec)

2. 使用 ALTER TABLE ... ADD COLUMN ... 语句添加生成列 newcol1 和 newcol2，这些生成列通过根据常规列 data_array 和 data_json 的值以及指定的表达式进行计算创建。

   ALTER TABLE test_tbl2
   ADD COLUMN newcol1 DOUBLE AS array_avg(data_array);
   
   ALTER TABLE test_tbl2
   ADD COLUMN newcol2 String AS json_string(json_query(data_json, "$.a"));

   注意：

   • 不支持将生成列添加到聚合表中。
   • 在生成列之前需要先定义常规列。当您使用 ALTER TABLE ... ADD COLUMN ... 语句添加一个常规列而没有指定新常规列的位置时，系统会自动将其放置在生成列之前。此外，您不能使用 AFTER 明确将常规列放置在生成列之后。

3. 查询表数据。

   MySQL [example_db]> SELECT * FROM test_tbl2;
   +------+------------+------------------+---------+---------+
   | id   | data_array | data_json        | newcol1 | newcol2 |
   +------+------------+------------------+---------+---------+
   |    1 | [1,2]      | {"a": 1, "b": 2} |     1.5 | 1       |
   +------+------------+------------------+---------+---------+
   1 row in set (0.04 sec)

   结果显示生成列 newcol1 和 newcol2 被添加到表中，并且 StarRocks 自动根据表达式计算它们的值。

将数据加载到生成列中

在数据加载期间，StarRocks 根据表达式自动计算生成列的值。您无法指定生成列的值。以下示例使用 INSERT INTO 语句加载数据：

1. 使用 INSERT INTO 将一条记录插入到 test_tbl1 表中。请注意，在 VALUES () 子句中不能指定生成列的值。

   INSERT INTO test_tbl1 (id, data_array, data_json)
   VALUES (1, [1,2], parse_json('{"a" : 1, "b" : 2}'));

2. 查询表数据。

   MySQL [example_db]> SELECT * FROM test_tbl1;
   +------+------------+------------------+---------+---------+
   | id   | data_array | data_json        | newcol1 | newcol2 |
   +------+------------+------------------+---------+---------+
   |    1 | [1,2]      | {"a": 1, "b": 2} |     1.5 | 1       |
   +------+------------+------------------+---------+---------+
   1 row in set (0.01 sec)

   结果显示 StarRocks 自动根据表达式计算生成列 newcol1 和 newcol2 的值。注意：如果在数据加载期间为生成列指定了值，则会返回以下错误：

   MySQL [example_db]> INSERT INTO test_tbl1 (id, data_array, data_json, newcol1, newcol2) 
   VALUES (2, [3,4], parse_json('{"a" : 3, "b" : 4}'), 3.5, "3");
   ERROR 1064 (HY000): 获取分析错误。详细消息：无法指定物化列“newcol1”。
   
   MySQL [example_db]> INSERT INTO test_tbl1 VALUES (2, [3,4], parse_json('{"a" : 3, "b" : 4}'), 3.5, "3");
   ERROR 1064 (HY000): 获取分析错误。详细消息：列数与值的数量不匹配。

修改生成列

注意

该操作消耗时间且资源密集。如果无法避免使用 ALTER TABLE 修改生成列，则推荐预先评估相关成本和时间。

您可以修改生成列的数据类型和表达式。

1. 创建一个名为 test_tbl3 的表，该表有五个列，其中 newcol1 和 newcol2 是生成列，其值通过使用指定的表达式并引用常规列 data_array 和 data_json 的值进行计算。插入一个数据行到表中。

   -- 创建表。
   MySQL [example_db]> CREATE TABLE test_tbl3
   (
       id INT NOT NULL,
       data_array ARRAY<int> NOT NULL,
       data_json JSON NOT NULL,
       -- 生成列的数据类型和表达式指定如下：
       newcol1 DOUBLE AS array_avg(data_array),
       newcol2 String AS json_string(json_query(data_json, "$.a"))
   )
   PRIMARY KEY (id)
   DISTRIBUTED BY HASH(id);
   
   -- 插入数据行。
   INSERT INTO test_tbl3 (id, data_array, data_json)
   VALUES (1, [1,2], parse_json('{"a" : 1, "b" : 2}'));
   
   -- 查询表。
   MySQL [example_db]> select * from test_tbl3;
   +------+------------+------------------+---------+---------+
   | id   | data_array | data_json        | newcol1 | newcol2 |
   +------+------------+------------------+---------+---------+
   |    1 | [1,2]      | {"a": 1, "b": 2} |     1.5 | 1       |
   +------+------------+------------------+---------+---------+
   1 row in set (0.01 sec)

2. 修改生成列 newcol1 和 newcol2：

   • 将生成列 newcol1 的数据类型更改为 ARRAY<INT>，并将其表达式更改为 data_array。

     ALTER TABLE test_tbl3 
     MODIFY COLUMN newcol1 ARRAY<INT> AS data_array;

   • 修改生成列 newcol2 的表达式，从常规列 data_json 中提取字段 b 的值。

     ALTER TABLE test_tbl3
     MODIFY COLUMN newcol2 String AS json_string(json_query(data_json, "$.b"));

3. 查看修改后的模式和表中的数据。

   • 查看修改后的模式。

     MySQL [example_db]> show create table test_tbl3\G
     **** 1. row ****
         Table: test_tbl3
     Create Table: CREATE TABLE test_tbl3 (
     id int(11) NOT NULL COMMENT "",
     data_array array<int(11)> NOT NULL COMMENT "",
     data_json json NOT NULL COMMENT "",
     -- 修改后，生成列的数据类型和表达式如下：
     newcol1 array<int(11)> NULL AS example_db.test_tbl3.data_array COMMENT "",
     newcol2 varchar(65533) NULL AS json_string(json_query(example_db.test_tbl3.data_json, '$.b')) COMMENT ""
     ) ENGINE=OLAP 
     PRIMARY KEY(id)
     DISTRIBUTED BY HASH(id)
     PROPERTIES (...);
     1 row in set (0.00 sec)

   • 查询修改后的表数据。结果显示 StarRocks 根据修改的表达式重新计算了生成列 newcol1 和 newcol2 的值。

     MySQL [example_db]> select * from test_tbl3;
     +------+------------+------------------+---------+---------+
     | id   | data_array | data_json        | newcol1 | newcol2 |
     +------+------------+------------------+---------+---------+
     |    1 | [1,2]      | {"a": 1, "b": 2} | [1,2]   | 2       |
     +------+------------+------------------+---------+---------+
     1 row in set (0.01 sec)

删除生成列

从表 test_tbl3 中删除列 newcol1

ALTER TABLE test_tbl3 DROP COLUMN newcol1;

注意：

如果生成列在表达式中引用了常规列，您将无法直接删除或修改该常规列。相反，您需要首先删除生成列，然后删除或修改常规列。

查询重写

如果查询中的表达式与生成列的表达式匹配，则优化器会自动将查询重写为直接读取生成列的值。

1. 假设您创建了一个具有以下模式的表 test_tbl4：

   CREATE TABLE test_tbl4
   (
       id INT NOT NULL,
       data_array ARRAY<int> NOT NULL,
       data_json JSON NOT NULL,
       newcol1 DOUBLE AS array_avg(data_array),
       newcol2 String AS json_string(json_query(data_json, "$.a"))
   )
   PRIMARY KEY (id) DISTRIBUTED BY HASH(id);

2. 如果您使用 SELECT array_avg(data_array), json_string(json_query(data_json, "$.a")) FROM test_tbl4; 语句查询表 test_tbl4 中的数据，则查询仅涉及常规列 data_array 和 data_json。然而，查询中的表达式与生成列 newcol1 和 newcol2 的表达式匹配。在这种情况下，执行计划显示 CBO 自动将查询重写为直接读取生成列 newcol1 和 newcol2 的值。

   MySQL [example_db]> EXPLAIN SELECT array_avg(data_array), json_string(json_query(data_json, "$.a")) FROM test_tbl4;
   +---------------------------------------+
   | Explain String                        |
   +---------------------------------------+
   | PLAN FRAGMENT 0                       |
   |  OUTPUT EXPRS:4: newcol1 | 5: newcol2 | -- 查询被重写为访问生成列 newcol1 和 newcol2 中的数据。
   |   PARTITION: RANDOM                   |
   |                                       |
   |   RESULT SINK                         |
   |                                       |
   |   0:OlapScanNode                      |
   |      TABLE: test_tbl4                 |
   |      PREAGGREGATION: ON               |
   |      partitions=0/1                   |
   |      rollup: test_tbl4                |
   |      tabletRatio=0/0                  |
   |      tabletList=                      |
   |      cardinality=1                    |
   |      avgRowSize=2.0                   |
   +---------------------------------------+
   15 rows in set (0.00 sec)

部分更新和生成列

要对主键表执行部分更新，必须在 columns 参数中指定生成列引用的所有常规列。以下示例使用流式加载执行部分更新。

1. 创建一个具有五个列的表 test_tbl5，其中 newcol1 和 newcol2 是生成列，其值通过使用指定的表达式并引用常规列 data_array 和 data_json 的值进行计算。将数据行插入到表中。

   -- 创建表。
   CREATE TABLE test_tbl5
   (
       id INT NOT NULL,
       data_array ARRAY<int> NOT NULL,
       data_json JSON NULL,
       newcol1 DOUBLE AS array_avg(data_array),
       newcol2 String AS json_string(json_query(data_json, "$.a"))
   )
   PRIMARY KEY (id)
   DISTRIBUTED BY HASH(id);
   
   -- 插入数据行。
   INSERT INTO test_tbl5 (id, data_array, data_json)
   VALUES (1, [1,2], parse_json('{"a" : 1, "b" : 2}'));
   
   -- 查询表。
   MySQL [example_db]> select * from test_tbl5;
   +------+------------+------------------+---------+---------+
   | id   | data_array | data_json        | newcol1 | newcol2 |
   +------+------------+------------------+---------+---------+
   |    1 | [1,2]      | {"a": 1, "b": 2} |     1.5 | 1       |
   +------+------------+------------------+---------+---------+
   1 row in set (0.01 sec)

2. 准备用于更新 test_tbl5 表中某些列的 CSV 文件 my_data1.csv。

   1|[3,4]|{"a": 3, "b": 4}
   2|[3,4]|{"a": 3, "b": 4}

3. 使用带有 my_data1.csv 文件的 Stream Load 来更新 test_tbl5 表的某些列。你需要设置 partial_update:true，并在 columns 参数中指定生成列引用的所有常规列。

   curl --location-trusted -u <username>:<password> -H "label:1" \
       -H "column_separator:|" \
       -H "partial_update:true" \
       -H "columns:id,data_array,data_json" \ 
       -T my_data1.csv -XPUT \
       http://<fe_host>:<fe_http_port>/api/example_db/test_tbl5/_stream_load

4. 查询表数据。

   [example_db]> select * from test_tbl5;
   +------+------------+------------------+---------+---------+
   | id   | data_array | data_json        | newcol1 | newcol2 |
   +------+------------+------------------+---------+---------+
   |    1 | [3,4]      | {"a": 3, "b": 4} |     3.5 | 3       |
   |    2 | [3,4]      | {"a": 3, "b": 4} |     3.5 | 3       |
   +------+------------+------------------+---------+---------+
   2 rows in set (0.01 sec)

如果执行部分更新时没有指定生成列引用的所有常规列，Stream Load 将返回错误。

1. 准备 CSV 文件 my_data2.csv。

   1|[3,4]
   2|[3,4]

2. 使用带有 my_data2.csv 文件的 Stream Load 执行部分列更新时，如果 my_data2.csv 中未提供 data_json 列的值，并且 Stream Load 作业的 columns 参数不包括 data_json 列，即使 data_json 列允许为空值，Stream Load 也会返回错误，因为生成列 newcol2 引用了列 data_json。