CREATE TABLE语句用于创建表。本文为您介绍在Hologres中建表时语法、索引等相关内容。

建表语法

  • 命令格式
    Hologres的建表语法兼容PostgreSQL,通过CREATE TABLE语句创建表,具体语法如下。
    说明 当前Hologres DDL支持多行事务;DML仅支持单行事务,不支持多行事务,即多个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);
    comment on column <tablename.column> is 'value';
    comment on table <tablename> is 'value';
    commit;
  • 参数说明

合理的建表索引总结

Hologres是兼容PostgreSQL生态,建表语法与PostgreSQL相同,但是索引与PostgreSQL不同,Hologres支持的索引请参见 设置表属性和索引。建表时选择合适的索引,能够使SQL在执行时快速命中数据,减少IO消耗,以更少的计算资源,实现更快的查询性能。下图是一个SQL从发起到获取数据的执行流程,可以通过下图理解每个索引的作用,以方便实际业务中更加方便高效的为表选择合适的索引。 建表索引总结
  1. SQL执行时,如果是分区表,那么会通过分区裁剪,定位到所在分区。
  2. 通过Distribution Key快速定位到数据所在的数据分片(Shard)。
  3. 通过Event Time Column(原Segment Key)快速定位到数据所在的文件。
  4. Clustering Key为数据在文件内的排序,可以通过Clustering Key快速定位到所在的文件块。
  5. 位图索引Bitmap是文件内的索引,可以通过Bitmap快速定位到符合条件的数据所在的行号。
索引适用的场景如下。
索引 适用场景 示例查询语句
Distribution Key 适合于频繁进行Group By的列或者多表Join时的Join字段设置为Distribution Key,能够减少数据Shuffle,实现Local Join的能力。 select * from tbl1 join tbl2 on tbl1.a=tbl2.c;
Clustering Key 将范围查询或Filter查询列作为聚簇索引列。索引过滤具备左匹配原则,建议设置聚簇索引列不要超过两列。 select sum(a) from tb1 where a > 100 and a < 200;
Bitmap 将等值查询列作为Bitmap列。 select * from tb1 where a =100;
Event Time Column(原Segment_Key) 适用于将日志、流量等和时间强相关的列设置为Segment_Key。 select sum(a) from tb1 where ts > '2020-01-01' and a < '2020-03-02';

设置表属性和索引

在Hologres中,可以通过 set_table_property命令为表设置多种属性,合理的表属性设置可以有助于系统高效地组织和查询数据。与数据存储布局有关的参数需要和建表语句同时执行。
  • 命令格式
    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', 'table_group', '[tableGroupName]'); 
    call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
    call set_table_property('table_name', 'clustering_key', '[columnName{:[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 column 否,如需修改请重新建表。
    table_group Table Group。 默认为default table group 默认为default table group 默认为default table group 默认即可。 否,如需修改请重新建表或者Resharding。
    distribution_key 分布键。 默认为主键,根据业务场景修改。 默认为主键。 默认为主键。 主键的子集,建议只选择一列。 否,如需修改请重新建表。
    clustering_key 聚簇索引。 默认为空。 默认为主键。 默认为空。 建议最多选择一列,且仅支持asc序。 否,如需修改请重新建表。
    event_time_column 分段键。 默认为第一个非空时间戳字段。 不支持。 默认为第一个非空时间戳字段。 建议时间戳字段。 否,如需修改请重新建表。
    bitmap_columns 位图索引。 按需使用。 不支持。 按需使用。 建议用于等值比较的列,一般10列以下。 是,详情请参见ALTER TABLE
    dictionary_encoding_columns 比特编码。 按需使用。 不支持。 按需使用。 建议低基数列,一般10列以下。 是,详情请参见ALTER TABLE
    time_to_live_in_seconds 表数据生命周期。 按需使用。 按需使用。 按需使用。 默认即可,无需设置。 是,详情请参见ALTER TABLE
    • orientation
      指定了数据库表在Hologres中的存储模式是列存还是行存,从V1.1版本开始支持行列共存的格式,命令语法如下所示。不同的存储格式适用于不同的查询场景,建表时默认为列存,其余存储模式需要建表时显式指定,详情请参见 表存储格式:列存、行存、行列共存
      call set_table_property('table_name', 'orientation', '[column | row |row,column]');
    • table_group
      在Hologres中,Shard是指数据分片,Table Group是Hologres中独有的逻辑存储概念,用于管理Shard数,一个Table Group唯一对应一组Shard。新建数据库后,如果没有创建新的Table Group,那么创建第一个表时,会自动建立一个名称为 <db>_tg_default的默认Table Group,后续表创建时没有指定Table Group将会被指定至默认Table Group中。一般情况下无需设置Table Group,使用默认Table Group即可,当实例规格较大(大于256 Core时),建议根据业务情况划分不同的Table Group和Shard数,带来更好的性能,使用详情请参见 Table Group与Shard Count操作指南
      call set_table_property('table_name', 'table_group', '[tableGroupName]');
    • distribution_key
      设置分布键Distribution Key。Distribution Key指定了表的分布策略,数据根据Distribution Key被分配到各个Shard上,保证Distribution Key相同的记录会被分配到同一个Shard上。Distribution Key是非常重要的分布式概念,合理的设置Distribution Key可以提高查询性能和QPS等,详情请参见 分布键Distribution Key
      call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
    • clustering_key
      设置聚簇索引Clustering Key,命令语法如下所示。Hologres会根据Clustering Key在文件内对数据进行排序,默认为升序(acs)排序。合理地设置Clustering Key能够加速在索引列上的Range和Filter查询,提升查询性能,详情请参见 聚簇索引Clustering Key
      call set_table_property('table_name', 'clustering_key', '[columnName{:asc} [,...]]');
    • event_time_column
      设置分段键Event_time_column (原Segment Key),命令语法如下所示。文件会根据Event_time_column划分,当命中Event_time_column时,可以快速定位到数据所在的文件,Event_time_column适用于数据为单调递增或单调递减的有序字段,例如时间戳字段,非常适用于日志、流量等和时间强相关的数据,合理设置可极大提升查询性能,详情请参见 Event Time Column(Segment Key)
      call set_table_property('table_name', 'event_time_column', '[columnName [,...]]');
    • bitmap_columns
      设置位图索引Bitmap,命令语法如下所示。Bitmap能够快速定位到符合条件数据所在的行号,适合将等值查询条件的数据设置为位图索引列。默认列存表所有TEXT数据类型的字段都会被隐式地设置为位图索引列,详情请参见 位图索引Bitmap
      call set_table_property('table_name', 'bitmap_columns', '[columnName{:[on|off]}[,...]]');
    • dictionary_encoding_columns
      设置字典编码Dictionary Encoding,命令语法如下所示。Dictionary Encoding指定列的值构建字典映射。字典编码可以将字符串的比较转成数字的比较,加速Group By、Filter等查询。默认列存表所有TEXT数据类型的字段都会被设置为Dictionary Encoding列 ,在Hologres V0.9及之后版本,会根据数据特征自动选择是否创建字典编码。
      call set_table_property('table_name', 'dictionary_encoding_columns', '[columnName{:[on|off|auto]}[,...]]');
    • time_to_live_in_seconds(不建议使用)
      设置表的数据生命周期(TTL),单位为秒,命令语法如下所示。
      • TTL过期时间是按照数据写入的时间开始计算,不是按照数据更新时间计算。不设置TTL的时候,默认为100年,Hologres从 V1.3.24版本开始,TTL允许的最小值是一天,即86400秒。TTL的详细使用说明请参见其他SQL语句
      • TTL不是精确的时间,即到期了之后数据会在某一段时间删除(不是固定时间),因此可能会出现PK重复的问题。生产业务不建议使用TTL来管理数据的生命周期,建议使用动态分区管理
      call set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');

使用限制

  • 支持将多个字段设置为Primary Key(即复合主键),被设置为Primary Key的字段是唯一且非空,同时只能在一个语句里设置多列为表的Primary Key。Primary Key必须为not nullable的列或者列组合,不支持将Float、Double、Numeric、Array、Json、Date及其他复杂数据类型的字段设为Primary Key。不支持修改Primary Key,如需修改请重新建表。如下示例指导您将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;
  • 列约束column_constraints和表约束table_constraints的支持情况如下。
    参数 column_constraints table_constraints
    primary key 支持 支持
    not null 支持 -
    null 支持 -
    unique 不支持 不支持
    check 不支持 不支持
    default 支持 不支持
  • 关键词、特殊字符、大小写需使用双引号("")进行区分。
    • Hologres对关键词的限制包括:列名称不能以hg_开头、Schema名称不能以holo_hg_pg_开头。同时还需要遵循PostgreSQL本身的关键词规范,包括关键词和系统列等,详情请参见关键词列表系统列列表,这些PostgreSQL关键词作为列名时需要加双引号("")。
    • 表名和列名均对大小写不敏感,如需定义大写表名、大写列名、特殊字符表名或列名、以数字开头的表名或列名时,可使用双引号("")进行转义。
    示例如下:
    说明 在查询列名称以数字开头的列时,同样需要使用双引号("")进行转义,否则会出现列名解析错误的情况。
    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"');  
    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%*";
    ------------------------------------------------------------------
    create table tbl ("2c" int not null);
    insert into tbl values (3), (4);
    select "2c" from tbl;
  • 在创建表时,如果不存在同名表且语义正确,表创建都会返回成功。如果不指定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属性不影响数据存储布局,可以在建表后按需更改。
  • 不支持将已有表的非空(not null)字段改成空(nullable)字段,同时不支持将nullable的字段改为not null的字段,如需更改请重新建表。

查看表结构

您可以执行如下命令查看TABLE的具体DDL:
create extension hg_toolkit; --该命令是DB级别,一个DB执行一次即可
select hg_dump_script('[<schema_name>.]<table_name>');
说明 也可以通过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 PARTITION TABLE
  • 新建普通表并设置默认值。
    begin;
    CREATE TABLE tbl_default (    
      smallint_col smallint DEFAULT 0,    
      int_col int DEFAULT 0,    
      bigint_col bigint DEFAULT 0,    
      boolean_col boolean DEFAULT FALSE,    
      float_col real DEFAULT 0.0,    
      double_col double precision DEFAULT 0.0,    
      decimal_col decimal(2, 1) DEFAULT 0.0,    
      text_col text DEFAULT 'N',    
      char_col char(2) DEFAULT 'N',    
      varchar_col varchar(200) DEFAULT 'N',    
      timestamptz_col timestamptz DEFAULT now(),    
      date_col date DEFAULT now(),    
      timestamp_col timestamp DEFAULT now()
    );
    commit;

HoloWeb可视化新建内部表

HoloWeb提供可视化一键建表功能,无需写SQL命令就能创建表,步骤如下。
  1. 进入HoloWeb页面,详情请参见连接HoloWeb
  2. HoloWeb页面顶部菜单栏,单击元数据管理 >

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

  3. 新建内部表页面,配置各项参数。
    类别 参数 描述
    基本属性 模式 模式名称。

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

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

    默认为列存

    表数据生命周期(秒) 数据第一次写入的时间开始计算,当到达生命周期后,表数据会在某一段时间内被清除(没有固定时间段)。

    默认生命周期为永久

    Binlog 表是否开启Binlog,详情请参见订阅Hologres Binlog
    Binlog生命周期 Binlog的生命周期,详情请参见订阅Hologres Binlog。默认生命周期为永久
    分布列 distribution_key,使用详情请参见分布键Distribution Key
    分段列 event_time_column ,使用详情请参见Event Time Column(Segment Key)
    聚簇列 clustering_key,使用详情请参见聚簇索引Clustering Key
    字典编码列 dictionary_encoding_columns,使用详情请参见字典编码Dictionary Encoding
    位图列 bitmap_columns,使用详情请参见位图索引Bitmap
    分区表 选择分区字段。
  4. 在页面右上角,单击提交。提交之后,您可以在左侧对应模式下,刷新出新建的内部表。
其他相关操作:
  • 编辑内部表
    1. 元数据管理界面的已登录实例列表,双击目标内部表。
    2. 在目标内部表信息页,单击编辑表,可以添加字段、更改表数据周期等部分表属性。
    3. 单击提交
  • 删除内部表
    1. 元数据管理界面的已登录实例列表,鼠标右击目标内部表,选择删除表删除内部表
    2. 删除表对话框,单击确认
  • 表数据预览
    1. 已登录实例列表,双击目标内部表。
    2. 进入表信息页签,单击数据预览,则可以预览表数据。数据预览
  • DDL预览
    在目标表信息页签,单击 DDL语句,则可以预览DDL语句。 DDL语句