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;

以下陳述式會將均勻分佈套用至資料表,但不會定義明確的排序索引鍵。

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

以下陳述式會套用均勻分佈,並定義排序索引鍵:

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