全部產品
Search
文件中心

Hologres:CREATE TABLE AS

更新時間:Jun 30, 2024

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和資料類型)

支援

支援

複製表屬性(可空、預設值、索引、主鍵或注釋)

不支援

有限支援

複製源表資料

支援

不支援

複製源表並同時手動設定新屬性(索引、主鍵等)

有限支援,其中主鍵不支援

有限支援,其中主鍵不支援

複製分區表結構為非分區表

支援

支援

複製成分區表

不支援

有限支援(通過partition_clause手動建立)

使用限制

  • 僅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 ]

指定是否在建立表時自動同步源表資料,含義如下。

  • WITH DATA:自動同步資料

  • WITH NO DATA:不自動同步資料。

如果不指定,預設為WITH 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;