CREATE TABLE语句用于创建表。本文为您介绍在交互式分析Hologres中CREATE TABLE的用法。

使用限制

  • 支持将多个字段设置为primary key(即复合主键)。被设置为primary key的字段是唯一且非空的列或者列组合,同时只能在一个语句里设置多列为表的primary key。目前primary key不支持Float、Double、Numeric、Array、Json、Date及其他复杂数据类型,不支持修改主键,如需修改主键请重新建表。如下示例指导您将idds设置为表的primary key。
    --正确示例
    BEGIN;
    CREATE TABLE public.test (
     "id" text NOT NULL,
     "ds" text NOT NULL,
    PRIMARY KEY (id,ds)
    );
    CALL SET_TABLE_PROPERTY('public.test', 'orientation', 'column');
    COMMIT;
  • 表名和列名均对大小写不敏感,如需定义大写表名、大写列名、特殊字符表名或列名,可使用双引号("")进行转义。示例如下:
    create table "TBL" (a int);
    select relname from pg_class where relname = 'TBL';
    insert into "TBL" values (-1977);
    select * from "TBL";
    ------------------------------------------------------------------
    begin;
    create table tbl ("C1" int not null);
    call set_table_property('tbl', 'clustering_key', '"C1"');
    commit;
    ------------------------------------------------------------------
    begin;
    create table tbl ("C1" int not null, c2 text not null);
    call set_table_property('tbl', 'clustering_key', '"C1,c2:desc"');  -- set_table_property 见下文
    commit;
    ------------------------------------------------------------------
    create table "Tab_$A%*" (a int);
    select relname from pg_class where relname = 'Tab_$A%*';
    insert into "Tab_$A%*" values (-1977);
    select * from "Tab_$A%*";
  • IF NOT EXISTS:在创建表时,如果不存在同名表且语义正确,表创建都会返回成功。如果不指定IF NOT EXISTS选项而存在同名表,则返回异常。如果指定IF NOT EXISTS选项,Hologres会提示信息,跳过表创建步骤,返回成功,直观的规则如下。
    配置项 指定if not exists 不指定if not exists
    存在同名表 NOTICE:relation “xx“already exists,skippingSUCCEED ERROR:relation is already exists.
    不存在同名表 SUCCEED SUCCEED
  • 表名的长度不超过64字节,超过64字节将被截断。
  • 不支持修改数据类型,如果必须修改,请重新建表。
  • 行存表必须设置主键,行列共存表必须设置主键,列存表不要求有主键。
  • orientation、distribution_key、clustering_key、event_time_column属性在建表后不支持更改,如需修改,需要重新建表;bitmap和dictionary属性可以在建表后更改。

建表

  • 命令格式
    目前Hologres语法是PostgreSQL的一个子集,支持的建表语法如下。
    说明 当前Hologres仅DDL支持事务处理,DML不支持事务处理。
    begin;
    create table [if not exists] [schema_name.]table_name ([
      {
       column_name column_type [column_constraints, [...]]
       | table_constraints
       [, ...]
      }
    ]);
    
    call set_table_property('<table_name>', property, value);
    commit;
  • 参数说明
    • column_type:为字段的数据类型,已支持的数据类型可以参见数据类型汇总
    • 列约束column_constraints和表约束table_constraints的支持情况如下。
      参数 column_constraints table_constraints
      primary key 支持 支持
      not null 支持 -
      null 支持 -
      unique 不支持 不支持
      check 不支持 不支持
      default 支持 不支持
    • set_table_property为表设置属性,详请参见设置表属性

设置表属性

在Hologres中,可以通过set_table_property为表设置多种属性,合理的表属性设置可以有助于系统高效地组织和查询数据。与数据存储布局有关的参数需要和建表语句同时执行,其中orientation、distribution_key、clustering_key和event_time_column创建后当前版本不支持修改。
  • 命令格式
    call set_table_property('<table_name>', property, value);
    说明set_table_property的调用需要与create table在同一事务中执行。
    Hologres当前版本支持的设置表属性有以下几种。
    call set_table_property('table_name', 'orientation', '[column | row | row,column]'); 
    call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
    call set_table_property('table_name', 'clustering_key', '[columnName{:[desc|asc]} [,...]]'); 
    call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');
    call set_table_property('table_name', 'bitmap_columns', '[columnName [,...]]');
    call set_table_property('table_name', 'dictionary_encoding_columns', '[columnName [,...]]');
    call set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');
  • 参数说明
    具体参数和相关内容如下表所示:
    参数 列存表 行存表 行列共存表 建表后是否可修改
    orientation column(默认值) row row,column 否,如需修改请重新建表。
    distribution_key 默认为主键,根据业务场景修改。 默认为主键。 默认为主键。 否,如需修改请重新建表。
    clustering_key 默认为空。 默认为主键。 默认为空。 否,如需修改请重新建表。
    event_time_column 默认为第一个非空时间戳字段。 不支持。 默认为第一个非空时间戳字段。 否,如需修改请重新建表。
    bitmap_columns 按需使用。 不支持。 按需使用。 是,详情请参见ALTER TABLE
    dictionary_encoding_columns 按需使用。 不支持。 按需使用。 是,详情请参见ALTER TABLE
    time_to_live_in_seconds 按需使用。 按需使用。 按需使用。 是,详情请参见ALTER TABLE
    • orientation
      call set_table_property('tbl', 'orientation', '[column | row |row,column]');
      • orientation:指定了数据库表在Hologres中的存储模式是列存还是行存,从HologresV1.1版本开始支持行列共存的格式。
      • 在Hologres中表默认为列存(column store)形式,表的存储格式说明如下。
        存储格式 适用场景 使用说明
        列存 适用于OLAP场景,适合各种复杂查询、数据关联、扫描、过滤和统计。 列存会默认创建更多的索引,包括对字符串类型创建bitmap索引,这些索引可以显著加速查询过滤和统计,因此列比较多的表,会占用更多的存储空间,您可以通过修改表属性关闭这些默认创建的索引,释放存储空间。
        行存 适用于 KV(key-value)场景 ,适合基于primary key的点查和扫描。 行存默认仅对主键创建索引,仅支持主键的快速查询,因此使用的存储空间更少,但使用场景也受到限制。
        行列共存 同时适用列存和行存的场景,既支持高效点查也支持OLAP分析,但也带来了更多的存储开销,以及内部数据状态同步的开销。 在行列共存中,必须有主键,其他类型的索引与列存的行为对齐。
      • 使用示例
        --建行存表
        begin;
        create table public.tbl_row (
            a integer NOT NULL,
            b text NOT NULL
            ,PRIMARY KEY (a)
        );
        call set_table_property('public.tbl_row', 'orientation', 'row');
        commit;
        
        
        --建列存表
        begin;
        create table tbl_col (
          a int not null,
          b text not null);
        call set_table_property('tbl_col', 'orientation', 'column');
        commit;
        
        --建行列共存
        begin;
        create table tbl_col_row (
            pk  text  not null,
            col1 text,
            col2 text,
            col3 text,
            PRIMARY KEY (pk));
        call set_table_property('tbl_col_row', 'orientation', 'row,column');
        commit;
    • distribution_key
      call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
      • distribution_key:指定了数据库表分布策略。数据根据distribution_key被分配到各个shard上。系统保证distribution_key相同的记录会被分配到同一个shard上。
      • columnName部分如设置单列,不要有多余空格。如设置多列,则以逗号分隔,同样不要有多余的空格。
      • distribution_key指定的列或列组合不支持Float、Double、Numeric、Date、Timestamp、Timestamptz、Array、Json、Serial、Bytea及其他复杂数据类型。
      • 当表中有primary key时,distribution_key默认为primary key。distribution_key必须为primary key或者primary key中的部分字段(不能为空),同一记录的数据只能属于一个shard。当表中没有primary key时,对distribution_key没有限制,可以为空(不指定任何列)。如果distribution_key为空,即随机shuffle,数据随机分布到不同shard上。当distribution_key对应列的值为空时,当作""(空串)看待。
      • Hologres中,distribution_key是非常重要的分布式概念。合理的设置distribution_key可以达到如下效果:
        • 提高性能。不同的Shard可以进行并行计算,从而提高性能。
        • 提高QPS。当您以distribution_key做过滤条件时,Hologres可以直接筛选出数据相关的Shard进行扫描。否则,Hologres需要让所有的Shard参与计算,会影响QPS。
        • 提高Join性能。当两张表在同一个Table Group内,并且Join key是distribution_key时,那么数据分布已保证表A一个Shard内的数据和表B同一Shard内的数据对应,只需要直接在本节点Join本节点数据(Local Join)即可,可以大大提高执行效率。
      • 使用示例
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'distribution_key', 'a');
        commit;
        
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'distribution_key', 'a,b');
        commit;
        
        begin;
        create table tbl1(a int not null, b text not null);
        call set_table_property('tbl1', 'distribution_key', 'a');
        create table tbl2(c int not null, d text not null);
        call set_table_property('tbl2', 'distribution_key', 'c');
        commit;
        
        select b, count(*) from tbl1 join tbl2 on tbl1.a = tbl2.c group by b;
    • clustering_key
      call set_table_property('tbl', 'clustering_key', '[columnName{:asc} [,...]]');
      • clustering_key:在指定的列上建立聚簇索引。Hologres会在聚簇索引上对数据进行排序,建立聚簇索引能够加速在索引列上的rangefilter查询。
      • 必须为not nullable的列或者列组合,不支持Float、Double、Array、Json及其他复杂数据类型。
      • clustering_key指定列时,可在列名后添加 :asc(升序)来表明构建索引时的排序方式。
      • 行存表的clustering_key默认为主键 (V0.9之前的版本默认不设置)。如果设置为和主键不同的clustering_key,那么Hologres会为这张表生成两个排序(primary_key排序和clustering_key排序),造成数据冗余。
      • 列存表的clustering_key默认为空。
      • 由于clustering_key用于排序,所以clustering_key里的列组合排在前面的优先级更高,clustering_key建议仅保留1~2列。
      • clustering_key可以用于在clustering index最开始几列的rangefilter的加速查询,即查询具备左匹配原则,不匹配则无法利用clustering_key查询加速
        假设表table1的clustering_key设置为col1和col2,那么下面的query可以被加速:
        -- 可加速
        select * from table1 where col1='abc'; 
        -- 可加速
        select * from table1 where col1>'xxx' and col1<'abc';
        -- 可加速
        select * from table1 where col1 in ('abc','def');
        -- 可加速
        select * from table1 where col1='abc' and col2='def'; 
        -- 不可加速
        select col1,col4 from table1 where col2='def';
      • 使用示例
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'clustering_key', 'a,b');
        commit;
        -------------------------------------------------------------
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'clustering_key', 'a,b:asc');
        commit;
    • event_time_column
      call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');
      • event_time_column:指定时间列作为分段键,必须为时间类型强相关的列 (如果数据有更新的话,需要和update time强相关)。当查询条件包含event_time_column时,查询可以通过event_time_column快速找到相应数据对应的存储位置。适用于日志、流量等和时间强相关的数据,合理设置可极大提升性能。
      • event_time_column必须为not nullable的列或者列组合,不支持Float、Double、Array、Json及其他复杂数据类型。
      • 行存表不可设置event_time_column
      • 设置event_time_column要求orienta为column,即列存表。
      • 列存表默认将table schema中的第一个非空的timestamp/timestamptz的字段作为event_time_column,如果不存在这样的字段,则默认将第一个非空的date类型的字段作为event_time_column (V0.9之前的版本默认为空)。
      • event_time_column原名为segment_key,在0.9版本默认改名为event_time_columnsegment_key依旧向下兼容使用。
      • 使用示例
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'event_time_column', 'a,b');
        commit;
    • bitmap_columns
      call set_table_property('tbl', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
      其中,参数说明如下表所示:
      参数 说明
      table_name 表名称。
      on 当前字段打开bitmap_columns。
      off 当前字段关闭bitmap_columns。
      • bitmap_columns:比特编码列。bitmap可以对存储文件内部的数据进行快速过滤,所以建议把filter条件的数据建成比特编码。
      • 设置bitmap_columns要求表的存储形式为column,即列存表。
      • bitmap_columns适合取值不多的列,对于每个取值构造一个二进制串,表示取值所在位置的bitmap。
      • bitmap_columns指定的列可以为空。
      • 当前版本默认所有text列都会被隐式地设置到bitmap_columns中(Hologres V0.8和Hologres V0.9版本行为一致)。
      • 可以在事务之外单独使用,表示修改bitmap_columns列,修改之后非立即生效,比特编码构建和删除在后台异步执行。详请参见ALTER TABLE
      • 使用示例
        --创建tbl并设置bitmap索引
        begin;
        create table tbl (
          a int not null, 
          b text not null);
        call set_table_property('tbl', 'bitmap_columns', 'a:on,b:off');
        commit;
        
        --修改bitmap索引
        call set_table_property('tbl', 'bitmap_columns', 'a:off');//全量修改
        call update_table_property('tbl', 'bitmap_columns', 'b:off');//增量修改
    • dictionary_encoding_columns
      call set_table_property('tbl', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
      其中,参数说明如下表所示:
      参数 说明
      table_name 表名称。
      on 表示当前字段打开dictionary_encoding_columns。
      off 表示当前字段关闭dictionary_encoding_columns。
      auto 表示自动。如果设置了auto,Hologres会根据所在列数值的重复程度自动选择是否进行dictionary_encoding_columns,值的重复度越高,字典编码的收益越大。在Hologres V0.8版本及更早版本中默认所有text列都会被设置为dictionary_encoding_columns,在Hologres V0.9版本及之后版本,会根据数据特征自动选择是否创建字典编码。
      • dictionary_encoding_columns:字典编码列,为指定列的值构建字典映射。字典编码可以将字符串的比较转成数字的比较,加速group by、filter等查询。
      • 设置dictionary_encoding_columns要求表的存储形式为column,即列存表。
      • dictionary_encoding_columns指定的列可以为null。
      • 取值较少的列适合设置dictionary_encoding_columns,可以压缩存储。
      • V0.8及更早版本中默认所有text列都会被隐式地设置到dictionary_encoding_columns中。V0.9及之后的版本会根据数据特征自动选择是否创建字典编码。
      • 可以在事务之外单独使用。表示修改dictionary_encoding_columns列,修改之后非立即生效,字典编码构建和删除在后台异步执行。详请参见ALTER TABLE
      • 使用示例
        --创建表tbl并设置dictionary_encoding_columns索引
        begin;
        create table tbl (
          a int not null, 
          b text not null,
          c text not null
        );
        call set_table_property('tbl', 'dictionary_encoding_columns', 'a:on,b:off,c:auto');
        commit;
        
        --修改dictionary_encoding_columns索引
        call set_table_property('tbl', 'dictionary_encoding_columns', 'a:off');--全量修改
        
        call update_table_property('tbl', 'dictionary_encoding_columns', 'b:off');--增量修改
    • time_to_live_in_seconds
      call set_table_property('tbl', 'time_to_live_in_seconds', '<non_negative_literal>');
      time_to_live_in_seconds:表数据的生存时间TTL,单位为秒,必须是非负数字类型,整数或浮点数均可。
      • 注意事项
        • 若是没有显示指定TTL,则默认数据永久保留(100年)。
        • TTL过期时间以数据第一次写入的时间开始计算,而不是数据最后一次修改的时间;当到达TTL后,表数据会在某一段时间内被清除(没有固定时间段),只是数据被清除,表不会被删除。
        • 可以在事务之外单独使用,表示修改表数据生存时间。
        • TTL功能类似于定期执行delete from tbl where xxx_time + TTL < now();,但是执行该命令比较占资源,并且SQL执行期间会锁表,阻塞其他写入操作,能保证数据一致性。
        • TTL相比于delete from tbl where xxx_time + TTL < now();几乎不占资源,也不会锁表,但是对于过期的数据不能保证数据一致性。这意味着:
          • 读取过期数据,可能能读到,可能读不到,也可能读到某个历史版本。
          • 修改或删除过期数据,可能正常工作,也可能出现PK重复之类的意外结果。
          • 使用TTL功能,最好要保证不会读取或修改过期的数据,或者对过期数据的一致性不关心。
      • 使用示例
        --建表时指定TTL
        begin;
        create table tbl (a int not null, b text not null);
        call set_table_property('tbl', 'time_to_live_in_seconds', '3.14159');
        commit;
        
        --修改TTL
        call set_table_property('tbl', 'time_to_live_in_seconds', '86400');
        说明 表数据的TTL并不是精确的时间,当超过设置的TTL后,系统会在某一个时间自动删除表数据,所以业务逻辑不能强依赖TTL。若是想精确的删除表数据,可以使用DataWorks,进行调度任务配置来删除数据。

增加注释

Hologres支持给表、外表、列等增加注释(comment)的功能。

增加注释的使用示例如下:
  • 新建表时添加注释。
    BEGIN;
    CREATE TABLE public."user" (
     "id" text NOT NULL,
     "name" text NOT NULL
    );
    COMMENT ON TABLE public."user" is '用户属性表';
    COMMENT ON COLUMN public."user".id is '身份证号';
    COMMENT ON COLUMN public."user".name is '姓名';
    COMMIT;
  • 已有表添加注释。
    -- 给表增加注释
    COMMENT ON TABLE table_name IS 'my comments on table table_name.';
    
    -- 给列增加注释
    COMMENT ON COLUMN table_name.col1 IS 'This my first col1';
    
    -- 给外部表增加注释
    COMMENT ON FOREIGN TABLE foreign_table IS ' comments on my foreign table';

更多关于注释的用法请参见PostgreSQL comment

查看表结构

您可以执行如下命令查看TABLE的具体DDL:
create extension hg_toolkit; --该命令是DB级别,一个DB执行一次即可
select hg_dump_script('tablename');
说明 也可以通过HoloWeb,在元数据管理模块进行DDL查看。

使用示例

  • 新建普通列存表并指定Primary Key。
    说明 Distribution Key必须是Primary Key的子集。
    begin;
    CREATE TABLE tbl (
     "id" bigint NOT NULL,
     "name" text NOT NULL,
     "age" bigint,
     "class" text NOT NULL,
     "reg_timestamp" timestamptz NOT NULL,
    PRIMARY KEY (id,age)
    );
    call set_table_property('tbl', 'orientation', 'column');
    call set_table_property('tbl', 'distribution_key', 'id');
    call set_table_property('tbl', 'clustering_key', 'age');
    call set_table_property('tbl', 'event_time_column', 'reg_timestamp');
    call set_table_property('tbl', 'bitmap_columns', 'name,class');
    call set_table_property('tbl', 'dictionary_encoding_columns', 'class:auto');
    commit;
  • 新建分区表并指定Primary Key。
    说明 分区表有主键时,主键里面必须包含分区字段。
    begin;
    CREATE TABLE www (
     name text NOT NULL,
     ds text NOT NULL,
     age text NOT NULL,
    PRIMARY KEY (name,ds)
    )
    PARTITION BY LIST(ds);
    CALL SET_TABLE_PROPERTY('www', 'orientation', 'column');
    commit;
  • 新建普通表并设置默认值。
    --设置当前时间
    CREATE TABLE test(
      id TIMESTAMPTZ DEFAULT now()
    );
    
    --设置数据的默认值
    CREATE TABLE products (
        product_no integer,
        name text,
        price FLOAT  DEFAULT 1.99
    );

HoloWeb可视化新建内部表

HoloWeb提供可视化一键建表功能,无需写SQL命令就能创建表,步骤如下。

  1. 进入HoloWeb页面,详情请参见HoloWeb快速入门
  2. HoloWeb页面顶部菜单栏,单击元数据管理 >

    您也可以在元数据管理界面的已登录实例列表。单击目标数据库,鼠标右击数据库下已创建的目标模式,选择新建内部表

  3. 新建内部表页面,配置各项参数。内部表配置
    类别 参数 描述
    基本属性 实例名 已登录的实例名称。
    数据库 当前已登录实例的数据库名称。
    表名 新建的Hologres内部表名称。
    描述 新建的Hologres内部表描述。
    模式 模式名称。

    您可以选择默认创建的public模式,也可以选择新建的模式名称。

    字段 字段名 表中每一列的标识。
    数据类型 字段取值的类型。
    主键 表中每条数据的唯一标识。
    可空 字段是否可以设置为空。
    数组 有序的元素序列。
    描述 字段的描述信息。
    操作 包括删除上移下移
    属性 存储模式 包括列存行存两种存储模式。

    默认为列存

    生命周期(秒) 如果数据在指定时间内未被修改,则系统将自动删除数据。

    默认生命周期为永久

    Binlog 表是否开启Binlog,详情请参见订阅Hologres Binlog
    Binlog生命周期 Binlog的生命周期,详情请参见订阅Hologres Binlog。默认生命周期为永久
    分布列 distribution_key,使用详情请参见设置表属性
    分段列 event_time_column ,使用详情请参见设置表属性
    聚簇列 clustering_key,使用详情请参见设置表属性
    字典编码列 dictionary_encoding_columns,使用详情请参见设置表属性
    位图列 bitmap_columns,使用详情请参见设置表属性
    分区表 选择分区字段。
  4. 在页面右上角,单击提交表。提交之后,您可以在左侧对应模式下,刷新出新建的内部表。
  5. (可选)表数据预览
    1. 已登录实例列表,双击目标内部表。
    2. 进入表信息页签,单击数据预览,则可以预览表数据。数据预览
  6. (可选)DDL预览
    在目标表信息页签,单击DDL语句,则可以预览DDL语句。DDL语句