全部产品
Search
文档中心

实时数仓Hologres:CREATE TABLE AS

更新时间:Mar 25, 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以下版本,请您使用自助升级或加入Hologres钉钉交流群反馈,详情请参见如何获取更多的在线支持?

  • 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;