CTAS 示例 - Amazon Redshift

CTAS 示例

以下示例为 EVENT 表创建名为 EVENT_BACKUP 的表:

create table event_backup as select * from event;

生成的表继承 EVENT 表中的分配键和排序键。

select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'event_backup'; column | type | encoding | distkey | sortkey ----------+-----------------------------+----------+---------+-------- catid | smallint | none | false | 0 dateid | smallint | none | false | 1 eventid | integer | none | true | 0 eventname | character varying(200) | none | false | 0 starttime | timestamp without time zone | none | false | 0 venueid | smallint | none | false | 0

以下命令通过选择 EVENT 表中的四个列来创建一个名为 EVENTDISTSORT 的新表。新表按 EVENTID 进行分配并按 EVENTID 和 DATEID 进行排序:

create table eventdistsort distkey (1) sortkey (1,3) as select eventid, venueid, dateid, eventname from event;

结果如下:

select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'eventdistsort'; column | type | encoding | distkey | sortkey ---------+------------------------+----------+---------+------- eventid | integer | none | t | 1 venueid | smallint | none | f | 0 dateid | smallint | none | f | 2 eventname | character varying(200)| none | f | 0

可通过对分配键和排序键使用列名来创建完全相同的表。例如:

create table eventdistsort1 distkey (eventid) sortkey (eventid, dateid) as select eventid, venueid, dateid, eventname from event;

以下语句对表应用 EVEN 分配,但不定义明确的排序键。

create table eventdisteven diststyle even as select eventid, venueid, dateid, eventname from event;

该表不继承 EVENT 表 (EVENTID) 中的排序键,因为已为新表指定 EVEN 分配。新表没有排序键和分配键。

select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'eventdisteven'; column | type | encoding | distkey | sortkey ----------+------------------------+----------+---------+--------- eventid | integer | none | f | 0 venueid | smallint | none | f | 0 dateid | smallint | none | f | 0 eventname | character varying(200) | none | f | 0

以下语句应用 EVEN 分配并定义排序键:

create table eventdistevensort diststyle even sortkey (venueid) as select eventid, venueid, dateid, eventname from event;

生成的表具有排序键,但不具有分配键。

select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'eventdistevensort'; column | type | encoding | distkey | sortkey ----------+------------------------+----------+---------+------- eventid | integer | none | f | 0 venueid | smallint | none | f | 1 dateid | smallint | none | f | 0 eventname | character varying(200) | none | f | 0

以下语句基于来自传入数据(基于 EVENTID 列进行排序)的其他键列重新分配 EVENT 表,但不定义 SORTKEY 列;因此,不会对表进行排序。

create table venuedistevent distkey(venueid) as select * from event;

结果如下:

select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'venuedistevent'; column | type | encoding | distkey | sortkey ----------+-----------------------------+----------+---------+------- eventid | integer | none | f | 0 venueid | smallint | none | t | 0 catid | smallint | none | f | 0 dateid | smallint | none | f | 0 eventname | character varying(200) | none | f | 0 starttime | timestamp without time zone | none | f | 0