CTAS 使用说明
限制
Amazon Redshift 会按节点类型强制执行每个集群的表数量配额。
表名的最大字符数为 127。
可在单个表中定义的列的最大数目为 1,600。
继承列和表属性
CREATE TABLE AS (CTAS) 表不从其父表继承约束、身份列、默认列值或主键。
您不能为 CTAS 表指定列压缩编码。Amazon Redshift 自动分配压缩编码,如下所示:
-
为定义为排序键的列分配 RAW 压缩。
-
定义为 BOOLEAN、REAL、DOUBLE PRECISION、GEOMETRY 或 GEOGRAPHY 数据类型的列分配了 RAW 压缩。
-
定义为 SMALLINT、INTEGER、BIGINT、DECIMAL、DATE、TIME、TIMETZ、TIMESTAMP 或 TIMESTAMPTZ 的列分配了 AZ64 压缩。
-
定义为 CHAR、VARCHAR 或 VARBYTE 的列分配了 LZO 压缩。
要显式分配列编码,请使用 CREATE TABLE。
CTAS 根据 SELECT 子句的查询计划确定新表的分配样式和排序键。
对于复杂查询,如包含连接、聚合、ORDER BY 子句或 LIMIT 子句,CTAS 会尽最大努力基于查询计划选择最佳分配样式和分类键。
注意
对于使用大型数据集或复杂查询实现最佳性能,我们建议使用典型数据集进行测试。
通常,您可以通过检查查询计划查看查询优化器选择哪些列(如果有)进行数据排序和分配,预测 CTAS 将选择哪个分配键和分类键。如果查询计划的顶端节点为一个表(XN 顺序扫描)的简单顺序扫描,则 CTAS 通常使用源表的分配样式和分类键。如果查询计划的顶部节点是除顺序扫描外的任何情况(如 XN 限制、XN 排序、XN HashAggregate 等),CTAS 会尽最大努力根据查询计划选择最佳分配样式和分类键。
例如,假设您使用 SELECT 子句创建了以下五个表:
-
简单的 SELECT 语句
-
Limit 子句
-
使用 LISTID 的 ORDER BY 子句
-
使用 QTYSOLD 的 ORDER BY 子句
-
使用 GROUP BY 子句的 SUM 聚合函数。
以下示例显示每个 CTAS 语句的查询计划。
explain create table sales1_simple as select listid, dateid, qtysold from sales; QUERY PLAN ---------------------------------------------------------------- XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (1 row) explain create table sales2_limit as select listid, dateid, qtysold from sales limit 100; QUERY PLAN ---------------------------------------------------------------------- XN Limit (cost=0.00..1.00 rows=100 width=8) -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (2 rows) explain create table sales3_orderbylistid as select listid, dateid, qtysold from sales order by listid; QUERY PLAN ------------------------------------------------------------------------ XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=8) Sort Key: listid -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (3 rows) explain create table sales4_orderbyqty as select listid, dateid, qtysold from sales order by qtysold; QUERY PLAN ------------------------------------------------------------------------ XN Sort (cost=1000000016724.67..1000000017155.81 rows=172456 width=8) Sort Key: qtysold -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (3 rows) explain create table sales5_groupby as select listid, dateid, sum(qtysold) from sales group by listid, dateid; QUERY PLAN ---------------------------------------------------------------------- XN HashAggregate (cost=3017.98..3226.75 rows=83509 width=8) -> XN Seq Scan on sales (cost=0.00..1724.56 rows=172456 width=8) (2 rows)
要查看每个表的分配键和分类键,请查询 PG_TABLE_DEF 系统目录表,如下所示。
select * from pg_table_def where tablename like 'sales%'; tablename | column | distkey | sortkey ----------------------+------------+---------+--------- sales | salesid | f | 0 sales | listid | t | 0 sales | sellerid | f | 0 sales | buyerid | f | 0 sales | eventid | f | 0 sales | dateid | f | 1 sales | qtysold | f | 0 sales | pricepaid | f | 0 sales | commission | f | 0 sales | saletime | f | 0 sales1_simple | listid | t | 0 sales1_simple | dateid | f | 1 sales1_simple | qtysold | f | 0 sales2_limit | listid | f | 0 sales2_limit | dateid | f | 0 sales2_limit | qtysold | f | 0 sales3_orderbylistid | listid | t | 1 sales3_orderbylistid | dateid | f | 0 sales3_orderbylistid | qtysold | f | 0 sales4_orderbyqty | listid | t | 0 sales4_orderbyqty | dateid | f | 0 sales4_orderbyqty | qtysold | f | 1 sales5_groupby | listid | f | 0 sales5_groupby | dateid | f | 0 sales5_groupby | sum | f | 0
下表汇总了结果。为简便起见,我们在说明计划中忽略了成本、行和宽度详细信息。
表 |
CTAS SELECT 语句 |
说明计划顶部节点 |
分配键 |
排序键 |
---|---|---|---|---|
S1_SIMPLE |
|
|
LISTID | DATEID |
S2_LIMIT |
|
|
无 (EVEN) | 无 |
S3_ORDER_BY_LISTID |
|
|
LISTID | LISTID |
S4_ORDER_BY_QTY |
|
|
LISTID | QTYSOLD |
S5_GROUP_BY |
|
|
无 (EVEN) | 无 |
您可以在 CTAS 语句中明确指定分配样式和分类键。例如,下面的语句使用 EVEN 分配创建了一个表并指定 SALESID 作为分类键。
create table sales_disteven diststyle even sortkey (salesid) as select eventid, venueid, dateid, eventname from event;
压缩编码
ENCODE AUTO 用作表的默认设置。Amazon Redshift 会自动管理表中所有列的压缩编码。
分配传入数据
如果传入数据的哈希分配方案与目标表的哈希分配方案匹配,则在加载数据时,没有实际必要的数据物理分配。例如,如果为新表设置分配键并从相同键列上分配的另一个表中插入数据,则使用相同的节点和切片就地加载数据。不过,如果源表和目标表都设置为 EVEN 分配,则数据将重新分配到目标表。
自动 ANALYZE 操作
Amazon Redshift 自动分析您使用 CTAS 命令创建的表。在最初创建这些表时,您不需要对这些表运行 ANALYZE 命令。如果修改了这些表,则应通过用来分析其他表的方式来分析这些表。