Hologres從V1.3.21版本開始,支援使用CREATE TABLE AS語句建立表,複製表結構的同時也可以選擇複製資料。本文為您介紹在Hologres中CREATE TABLE AS的用法。
背景資訊
CREATE TABLE AS的功能為:建立一個與源表結構或者查詢Query結果相同的新表,同時支援自動同步源表資料,但不會複製表屬性。
CREATE TABLE AS與CREATE TABLE LIKE文法的區別如下,您可以根據實際業務情境選擇合適的文法。
對比項 | CREATE TABLE AS | CREATE TABLE LIKE(函數) |
複製表結構(Schema和資料類型) | 支援 | 支援 |
複製表屬性(可空、預設值、索引、主鍵或注釋) | 不支援 | 有限支援 |
複製源表資料 | 支援 | 不支援 |
複製源表並同時手動設定新屬性(索引、主鍵等) | 有限支援,其中主鍵不支援 | 有限支援,其中主鍵不支援 |
複製分區表結構為非分區表 | 支援 | 支援 |
複製成分區表 | 不支援 | 有限支援(通過 |
使用限制
僅Hologres V1.3.21及以上版本支援
CREATE TABLE AS
語句,若您的執行個體是V1.3.21以下版本,請您使用自助升級或加入HologresDingTalk交流群反饋,詳情請參見如何擷取更多的線上支援?。CREATE TABLE AS
語句支援複製表結構,不能複製表屬性(主鍵,索引等)。CREATE TABLE AS
語句支援建立新表時自動同步源表資料,但不保證資料匯入的原子性。使用
CREATE TABLE AS
語句時,如果源表中包含VARCHR、BPCHAR、NUMERIC(DECIMAL)、BIT、VARBIT類型的列,那麼必須手動在SQL語句中顯式指定精度,否則會提示報錯。使用
CREATE TABLE AS
語句時,如果源表中包含INTERVAL、TIME、TIMETZ、TIMESTAMP、TIMESTAMPTZ的列,那麼不可以對這些列指定精度,否則會提示報錯。CREATE TABLE AS
語句支援從分區主表或分區子表建立新的非分區表,只能複製表結構並同步資料(從分區主表複製,會同步所有子表的資料),不支援複製分區結構(分區鍵約束、繼承關係等)成為一張分區表。
命令文法
Hologres中CREATE TABLE AS
的命令文法如下。
--從一張源表複製一張新表
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS TABLE <src_table_name> [ WITH [ NO ] DATA ]
--從select query複製一張新表
CREATE TABLE [ IF NOT EXISTS ] <new_table_name> AS <select_query> [ WITH [ NO ] DATA ]
參數說明
參數 | 說明 |
new_table_name | 建立表的表名稱(不能建立外部表格),只支援固定字串,不支援字元拼接或函數產生。 |
[ IF NOT EXISTS ] | 判斷新建立的表是否已存在,若存在則跳過。 |
src_table_name | 源表或視圖(VIEW)名稱,即要複製的表或視圖名稱。 說明 Hologres從 V2.1.21版本開始,支援將視圖(VIEW)作為資料來源,即支援複製視圖的結構和資料建立表。 |
select_query | 查詢的SQL語句串,詳情請參見SELECT。 |
[ WITH [ NO ] DATA ] | 指定是否在建立表時自動同步源表資料,含義如下。
如果不指定,預設為 |
使用樣本
將非分區表複製成一張新的非分區表。
準備一張源表和資料。
BEGIN; CREATE TABLE public.src_table ( "a" int8 NOT NULL, "b" text NOT NULL, PRIMARY KEY (a) ); CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column'); CALL SET_TABLE_PROPERTY('public.src_table', 'bitmap_columns', 'b'); CALL SET_TABLE_PROPERTY('public.src_table', 'dictionary_encoding_columns', 'b:auto'); CALL SET_TABLE_PROPERTY('public.src_table', 'time_to_live_in_seconds', '3153600000'); CALL SET_TABLE_PROPERTY('public.src_table', 'distribution_key', 'a'); CALL SET_TABLE_PROPERTY('public.src_table', 'storage_format', 'segment'); COMMIT; INSERT INTO public.src_table VALUES (1,'qaz'),(2,'wsx');
情境1:從源表建立新表,並自動同步資料。
CREATE TABLE public.new_table AS TABLE public.src_table;
執行完成後,查看新表的資料:
SELECT * FROM public.new_table; ------- a | b --|----- 1 | qaz 2 | wsx
查看新表的DDL,可以看到,如果源表有主鍵、not null屬性,複製出的新表將不會繼承:
--新表的DDL如下: select hg_dump_script('public.new_table'); ------------------------------------------- BEGIN; CREATE TABLE public.new_table ( a int, b text ); CALL set_table_property('public.new_table', 'orientation', 'column'); CALL set_table_property('public.new_table', 'storage_format', 'orc'); CALL set_table_property('public.new_table', 'bitmap_columns', 'b'); CALL set_table_property('public.new_table', 'dictionary_encoding_columns', 'b:auto'); CALL set_table_property('public.new_table', 'time_to_live_in_seconds', '3153600000'); COMMENT ON TABLE public.new_table IS NULL; END;
情境2:從源表建立新表,並自動同步資料。若表已經存在則跳過,資料也不會同步。
CREATE TABLE IF NOT EXISTS public.new_table AS TABLE public.src_table; NOTICE: relation "new_table" already exists, skipping
情境3:僅複製表結構,不同步資料。
CREATE TABLE public.new_table AS TABLE public.src_table WITH NO DATA;
情境4:複製新表並同步
select
查詢的資料。CREATE TABLE public.new_table_2 AS SELECT * FROM public.src_table WHERE a = 1 ;
從分區父表或者子表複製出一張新的非分區表,分區表只能複製成非分區表。
準備一張分區表、分區子表以及表資料。
BEGIN; CREATE TABLE public.src_table_partitioned ( "a" int NOT NULL, "b" text , PRIMARY KEY (a) ) PARTITION BY LIST(a); CREATE TABLE public.src_table_child1 PARTITION OF public.src_table_partitioned FOR VALUES IN (1); CREATE TABLE public.src_table_child2 PARTITION OF public.src_table_partitioned FOR VALUES IN (2); CREATE TABLE public.src_table_child3 PARTITION OF public.src_table_partitioned FOR VALUES IN (3); COMMIT; INSERT INTO src_table_child1 VALUES (1,'aaa'); INSERT INTO src_table_child2 VALUES (2,'bbb'); INSERT INTO src_table_child3 VALUES (3,'ccc');
情境1:將分區父表複製成一張非分區表,並同步所有分區的資料。
CREATE TABLE public.new_table_2 AS TABLE public.src_table_partitioned;
查詢新表,將會有所有分區的資料,且新表是非分區表:
SELECT * FROM public.new_table_2; ---------------------------------- a | b --|----- 2 | bbb 1 | aaa 3 | ccc
情境2:複製某個分區子表及其資料到新的非分區表。
--從分區子表建立新表,並自動同步資料,新表只會同步這一張子表的資料 CREATE TABLE public.new_table_3 AS TABLE public.src_table_child1;
從Select Query的結果複製一張新表,同時設定新表屬性。
--建立源表 BEGIN; CREATE TABLE public.src_table ( "a" int8 NOT NULL, "b" text NOT NULL, PRIMARY KEY (a) ); CALL SET_TABLE_PROPERTY('public.src_table', 'orientation', 'column'); COMMIT; --從select query建立新表,並設定新表屬性,自動同步資料 BEGIN; CREATE TABLE public.new_table AS select * from public.src_table; CALL SET_TABLE_PROPERTY('public.new_table', 'bitmap_columns', 'b'); CALL SET_TABLE_PROPERTY('public.new_table', 'dictionary_encoding_columns', 'b:auto'); CALL SET_TABLE_PROPERTY('public.new_table', 'time_to_live_in_seconds', '3153600'); CALL SET_TABLE_PROPERTY('public.new_table', 'distribution_key', 'a'); COMMIT;