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（默认值）rowrow,columncolumn否，如需修改请重新建表。
    table_groupTable 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，如需修改请重新建表。如下示例指导您将id和ds设置为表的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_constraintstable_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，skippingSUCCEEDERROR：relation is already exists.
    不存在同名表SUCCEEDSUCCEED
  • 表名称的长度不能超过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语句