实时数仓Hologres：CREATE TABLE

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快速定位到符合条件的数据所在的行号。

索引适用的场景如下。

在Hologres中，可以通过set_table_property或create table with命令为表设置多种属性，合理的表属性设置可以有助于系统高效地组织和查询数据。与数据存储布局有关的参数需要和建表语句同时执行。

• 命令格式

  -- 2.1版本起支持
  create table <table_name> (...)
  with (property = 'value'[, ...]);
  
  -- 所有版本支持
  call set_table_property('<table_name>', property, 'value');

  说明

  set_table_property的调用需要与create table在同一事务中执行。

  Hologres支持的设置表属性如下所示，包括但不限于以下几种表属性。

  • V2.1版本起支持的语法：

    CREATE TABLE <table_name> (...)
    WITH (
        orientation = '[column | row | row,column]',
        table_group = '[tableGroupName]',
        distribution_key = 'columnName[,...]]',
        clustering_key = '[columnName{:asc]} [,...]]',
        event_time_column = '[columnName [,...]]',
        bitmap_columns = '[columnName [,...]]',
        dictionary_encoding_columns = '[columnName [,...]]',
        time_to_live_in_seconds = '<non_negative_literal>'
        [,storage_mode, ...]
    );

  • 所有版本都支持的语法：

    BEGIN;
    CREATE TABLE <table_name> (...);
    call set_table_property('table_name', 'orientation', '[column | row]');
    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>');
    COMMIT;

• 参数说明

  具体参数和相关内容如下表所示：

  参数	说明	列存表	行存表	行列共存表	建议值	建表后是否可修改
  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来管理数据的生命周期，建议使用CREATE PARTITION TABLE。

    call set_table_property('table_name', 'time_to_live_in_seconds', '<non_negative_literal>');

您可以执行如下命令查看TABLE的具体DDL：

create extension hg_toolkit; --该命令是DB级别，一个DB执行一次即可
select hg_dump_script('[<schema_name>.]<table_name>');

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语句。