版本: 最新版本-3.5

CREATE TABLE AS SELECT

使用 CREATE TABLE AS SELECT (CTAS) 语句同步或异步查询表，并根据查询结果创建新表，然后将查询结果插入到新表中。

您可以使用 SUBMIT TASK 提交异步 CTAS 任务。

语法

同步查询表，并根据查询结果创建新表，然后将查询结果插入到新表中。

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [database.]table_name
[column_name1 [, column_name2, ...]]
[index_definition1 [, index_definition2, ...]]
[key_desc]
[COMMENT "table comment"]
[partition_desc]
[distribution_desc]
[ORDER BY (column_name1 [, column_name2, ...])]
[PROPERTIES ("key"="value", ...)]
AS SELECT query
[ ... ]

异步查询表，并根据查询结果创建新表，然后将查询结果插入到新表中。

SUBMIT [/*+ SET_VAR(key=value) */] TASK [[database.]<task_name>]AS
CREATE TABLE [IF NOT EXISTS] [database.]table_name
[column_name1 [, column_name2, ...]]
[index_definition1 [, index_definition2, ...]]
[key_desc]
[COMMENT "table comment"]
[partition_desc]
[distribution_desc]
[ORDER BY (column_name1 [, column_name2, ...])]
[PROPERTIES ("key"="value", ...)] AS SELECT query
[ ... ]

参数

参数	必需	描述
TEMPORARY	否	创建临时表。从 v3.3.1 版本开始，StarRocks 支持在 Default Catalog 中创建临时表。更多信息，请参见临时表。目前，StarRocks 不支持使用 SUBMIT TASK 通过异步任务创建临时表。
column_name	否	新表中的列名。您无需为列指定数据类型。 StarRocks 会自动为列指定适当的数据类型。 StarRocks 将 FLOAT 和 DOUBLE 数据转换为 DECIMAL(38,9) 数据。 StarRocks 还会将 CHAR、VARCHAR 和 STRING 数据转换为 VARCHAR(65533) 数据。
index_definition	否	自 v3.1.8 版本起，可以为新表创建 Bitmap 索引。语法是 `INDEX index_name (col_name[, col_name, ...]) [USING BITMAP] COMMENT 'xxxxxx'`。有关参数描述和使用注意事项的更多信息，请参见 Bitmap 索引。
key_desc	否	语法是 `key_type ( <col_name1> [, <col_name2> , ...])`。参数: `key_type`: 新表的 Key 类型。有效值：`DUPLICATE KEY` 和 `PRIMARY KEY`。默认值：`DUPLICATE KEY`。 `col_name`: 构成 Key 的列。
COMMENT	否	新表的注释。
partition_desc	否	新表的分区方法。默认情况下，如果您未指定此参数，则新表没有分区。有关分区的更多信息，请参见 CREATE TABLE。
distribution_desc	否	新表的 Bucketing 方法。如果您未指定此参数，则 Bucket 列默认为基于成本优化器 (CBO) 收集的基数最高的列。 Bucket 的数量默认为 10。如果 CBO 未收集有关基数的信息，则 Bucket 列默认为新表中的第一列。有关 Bucketing 的更多信息，请参见 CREATE TABLE。
ORDER BY	否	自 v3.1.8 版本起，如果新表是 Primary Key 表，则可以为新表指定排序键。排序键可以是任意列的组合。 Primary Key 表是在创建表时指定了 `PRIMARY KEY (xxx)` 的表。
Properties	否	新表的属性。
AS SELECT query	是	查询结果。您可以在 `... AS SELECT query` 中指定列，例如，`... AS SELECT a, b, c FROM table_a;`。在此示例中，`a`、`b` 和 `c` 表示被查询表的列名。如果您未指定新表的列名，则新表的列名也是 `a`、`b` 和 `c`。您可以在 `... AS SELECT query` 中指定表达式，例如，`... AS SELECT a+1 AS x, b+2 AS y, cc AS z FROM table_a;`。在此示例中，`a+1`、`b+2` 和 `cc` 表示被查询表的列名，`x`、`y` 和 `z` 表示新表的列名。注意：新表中的列数需要与 SELECT 语句中指定的列数相同。我们建议您使用易于识别的列名。

使用说明

CTAS 语句只能创建满足以下要求的新表
- ENGINE 是 OLAP。
- 该表默认是 Duplicate Key 表。您也可以在 key_desc 中将其指定为 Primary Key 表。
- 排序键是前三列，并且这三列的数据类型存储空间不超过 36 字节。
如果 CTAS 语句由于 FE 重启等原因而执行失败，则可能会出现以下问题之一
- 新表已成功创建，但不包含数据。
- 新表创建失败。
创建新表后，如果使用多种方法（例如 INSERT INTO）将数据插入到新表中，则首先完成 INSERT 操作的方法将将其数据插入到新表中。
创建新表后，您需要手动授予用户对此表的权限。
如果您在异步查询表并根据查询结果创建新表时未指定任务名称，StarRocks 会自动为任务生成名称。

示例

示例 1：同步查询表 order，并根据查询结果创建新表 order_new，然后将查询结果插入到新表中。

CREATE TABLE order_new
AS SELECT * FROM order;

示例 2：同步查询表 order 中的 k1、k2 和 k3 列，并根据查询结果创建新表 order_new，然后将查询结果插入到新表中。此外，将新表的列名设置为 a、b 和 c。

CREATE TABLE order_new (a, b, c)
AS SELECT k1, k2, k3 FROM order;

或

CREATE TABLE order_new
AS SELECT k1 AS a, k2 AS b, k3 AS c FROM order;

示例 3：同步查询表 employee 中 salary 列的最大值，并根据查询结果创建新表 employee_new，然后将查询结果插入到新表中。此外，将新表的列名设置为 salary_max。

CREATE TABLE employee_new
AS SELECT MAX(salary) AS salary_max FROM employee;

插入数据后，查询新表。

SELECT * FROM employee_new;

+------------+
| salary_max |
+------------+
|   10000    |
+------------+

示例 4：同步查询表 customers 中的 customer_id 和 first_name 列，并根据查询结果创建新表 customers_new，然后将查询结果插入到新表中。此外，将新表的列名设置为 customer_id_new 和 first_name_new。此外，为新表中的 customer_id_new 列构建 Bitmap 索引。

CREATE TABLE customers_new 
(   customer_id_new,
    first_name_new,
    INDEX idx_bitmap_customer_id (customer_id_new) USING BITMAP
) 
AS SELECT customer_id,first_name FROM customers;

示例 5：同步查询表 customers，并根据查询结果创建新表 customers_new，然后将查询结果插入到新表中。此外，将新表指定为 Primary Key 表，并将其排序键指定为 first_name 和 last_name。

CREATE TABLE customers_pk
PRIMARY KEY (customer_id)
ORDER BY (first_name,last_name)
AS SELECT  * FROM customers;

示例 6：使用 CTAS 创建 Primary Key 表。请注意，Primary Key 表中的数据行数可能少于查询结果中的数据行数。这是因为 Primary Key 表仅存储具有相同主键的一组行中最近的数据行。

CREATE TABLE employee_new
PRIMARY KEY(order_id)
AS SELECT
    order_list.order_id,
    sum(goods.price) as total
FROM order_list INNER JOIN goods ON goods.item_id1 = order_list.item_id2
GROUP BY order_id;

示例 7：同步查询四个表，包括 lineorder、customer、supplier 和 part，并根据查询结果创建新表 lineorder_flat，然后将查询结果插入到新表中。此外，为新表指定分区方法和 Bucketing 方法。

CREATE TABLE lineorder_flat
PARTITION BY RANGE(`LO_ORDERDATE`)
(
    START ("1993-01-01") END ("1999-01-01") EVERY (INTERVAL 1 YEAR)
)
DISTRIBUTED BY HASH(`LO_ORDERKEY`) AS SELECT
    l.LO_ORDERKEY AS LO_ORDERKEY,
    l.LO_LINENUMBER AS LO_LINENUMBER,
    l.LO_CUSTKEY AS LO_CUSTKEY,
    l.LO_PARTKEY AS LO_PARTKEY,
    l.LO_SUPPKEY AS LO_SUPPKEY,
    l.LO_ORDERDATE AS LO_ORDERDATE,
    l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
    l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
    l.LO_QUANTITY AS LO_QUANTITY,
    l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
    l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
    l.LO_DISCOUNT AS LO_DISCOUNT,
    l.LO_REVENUE AS LO_REVENUE,
    l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
    l.LO_TAX AS LO_TAX,
    l.LO_COMMITDATE AS LO_COMMITDATE,
    l.LO_SHIPMODE AS LO_SHIPMODE,
    c.C_NAME AS C_NAME,
    c.C_ADDRESS AS C_ADDRESS,
    c.C_CITY AS C_CITY,
    c.C_NATION AS C_NATION,
    c.C_REGION AS C_REGION,
    c.C_PHONE AS C_PHONE,
    c.C_MKTSEGMENT AS C_MKTSEGMENT,
    s.S_NAME AS S_NAME,
    s.S_ADDRESS AS S_ADDRESS,
    s.S_CITY AS S_CITY,
    s.S_NATION AS S_NATION,
    s.S_REGION AS S_REGION,
    s.S_PHONE AS S_PHONE,
    p.P_NAME AS P_NAME,
    p.P_MFGR AS P_MFGR,
    p.P_CATEGORY AS P_CATEGORY,
    p.P_BRAND AS P_BRAND,
    p.P_COLOR AS P_COLOR,
    p.P_TYPE AS P_TYPE,
    p.P_SIZE AS P_SIZE,
    p.P_CONTAINER AS P_CONTAINER FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

示例 8：异步查询表 order_detail，并根据查询结果创建新表 order_statistics，然后将查询结果插入到新表中。

SUBMIT TASK AS CREATE TABLE order_statistics AS SELECT COUNT(*) as count FROM order_detail;

+-------------------------------------------+-----------+
| TaskName                                  | Status    |
+-------------------------------------------+-----------+
| ctas-df6f7930-e7c9-11ec-abac-bab8ee315bf2 | SUBMITTED |
+-------------------------------------------+-----------+

检查任务信息。

SELECT * FROM INFORMATION_SCHEMA.tasks;

-- Information of the Task

TASK_NAME: ctas-df6f7930-e7c9-11ec-abac-bab8ee315bf2
CREATE_TIME: 2022-06-14 14:07:06
SCHEDULE: MANUAL
DATABASE: default_cluster:test
DEFINITION: CREATE TABLE order_statistics AS SELECT COUNT(*) as cnt FROM order_detail
EXPIRE_TIME: 2022-06-17 14:07:06

检查 TaskRun 的状态。

SELECT * FROM INFORMATION_SCHEMA.task_runs;

-- State of the TaskRun

QUERY_ID: 37bd2b63-eba8-11ec-8d41-bab8ee315bf2
TASK_NAME: ctas-df6f7930-e7c9-11ec-abac-bab8ee315bf2
CREATE_TIME: 2022-06-14 14:07:06
FINISH_TIME: 2022-06-14 14:07:07
STATE: SUCCESS
DATABASE: 
DEFINITION: CREATE TABLE order_statistics AS SELECT COUNT(*) as cnt FROM order_detail
EXPIRE_TIME: 2022-06-17 14:07:06
ERROR_CODE: 0
ERROR_MESSAGE: NULL

当 TaskRun 的状态为 SUCCESS 时，查询新表。

SELECT * FROM order_statistics;

语法​

参数​

使用说明​

示例​

您觉得这篇文档怎么样？

语法

参数

使用说明

示例