本小节将会为您介绍在交互式分析(Interactive Analytics)中对表的命令操作。

关于分区表详细命令操作,请见创建和管理分区表

关于外部表详细命令操作,请见创建和管理外部表

创建表

目前交互式分析(Interactive Analytics) 语法是PostgreSQL的一个子集,支持的建表语法有如下三种。

-- 典型的create table语句
create table [if not exists] [schema_name.]table_name ([
  {
   column_name column_type [column_constraints, [...]]
   | table_constraints
   [, ...]
  }
]);

-- 典型的create partition table语句
create table [if not exists] [schema_name.]table_name partition by list (column_name) ([
  {
   column_name column_type [column_constraints, [...]]
   | table_constraints
   [, ...]
  }
]);

-- 典型的create child partition table语句
create table [if not exists] [schema_name.]table_name partition of parent_table
  for values in (string_literal);
说明
  1. column_type支持int,integer,int4,int8,bigint,bool,boolean,float,double precision,float8,text,varchar,timestamp,详见数据类型
  2. 列约束column_constraints和表约束table_constraints的支持情况如下。
    column_constraints table_constraints
    primary key 支持
    not null 支持
    null 支持
    unique 不支持
    check 不支持
    default 不支持
  3. column_onstraints不能有多列为primary key,但tableConstraints允许设置多列为表primary key
  4. 表名和列名均对大小写不敏感,如需定义大写表名、大写列名、特殊字符表名或列名,可使用双引号““进行转义。例如:
    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);
    call set_table_property('tbl', 'clustering_key', '"C1,c2:desc"');  -- set_table_property 见下文
    call set_table_property('tbl', 'segment_key', '"C1",c2:desc');
    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%*";
  5. 在创建表时,如果不存在同名表且语义正确,表创建都会返回成功。如果不指定IF NOT EXISTS选项而存在同名表,则返回异常。如果指定IF NOT EXISTS选项,交互式分析(Interactive Analytics)会提示信息,跳过表创建步骤,返回成功。直观的规则如下。
    指定IF NOT EXISTS 不指定IF NOT EXISTS
    存在同名表 NOTICE:relation “xx“already exists,skippingSUCCEED
    不存在同名表 SUCCEED
  6. 如未做双引号““转义,则表名、列名中不能有特殊字符,只能用英文的a-z、A-Z及数字和下划线(),且必须以字母开头。由于大小写不敏感,A-Z统一会被看成小写。例如。
    begin;
    create table TABLE_one (a int not null);
    call set_table_property('table_one', 'clustering_key', 'a');
    commit;
    insert into table_one values (12);
  7. 表名的长度不超过64字节,超过64字节将被截断。
  8. partition by类型仅支持list,切分partition list只能有一个值,且类型只能为string。

设置表属性

在交互式分析(Interactive Analytics)中,可以通过set_table_property为表设置多种属性,合理的表属性设置可以有助于系统高效地组织和查询数据。执行命令如下。
call set_table_property('tbl', property, value)
说明set_table_property的调用需要与create table在同一事务中执行。
主要语法支持且仅支持以下几种。
call set_table_property('table_name', 'orientation', '[column | row]'); 
call set_table_property('table_name', 'clustering_key', '[columnName{:[desc|asc]} [,...]]'); 
call set_table_property('table_name', 'segment_key', '[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>');
call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
下面分别对每种做详细定义。
  1. orientation
    call set_table_property('tbl', 'orientation', '[column | row]');
    • orientation属性指定了数据库表在交互式分析(Interactive Analytics)中是列存还是行存。
    • 在交互式分析(Interactive Analytics)中,数据库表默认为列存(column store)形式。列存对于OLAP场景较为友好,适合各种复杂查询,行存对于kv场景比较友好,适合基于primary key的点查和扫描scan。
    • 举例
      begin;
      create table tbl (a int not null, b text not null);
      call set_table_property('tbl', 'orientation', 'row');
      commit;
  2. clustering key
    call set_table_property('tbl', 'clustering_key', '[columnName{:[desc|asc]} [,...]]');
    • clustering_key指定一些列作为聚簇索引:交互式分析(Interactive Analytics)在指定的列上将建立聚簇索引。交互式分析(Interactive Analytics)会在聚簇索引上对数据进行排序,建立聚簇索引能够加速用户在索引列上的range和filter查询。
    • clustering_key指定的列必须满足非空约束(not null)。
    • clustering_key指定列时,可在列名后添加 :desc或者asc来表明构建索引时的排序方式。排序方式默认为asc,即升序。
    • 数据类型为float/double的列,不能设置为clustering_key
    • 举例
      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:desc,b:asc');
      commit;
  3. bitmap columns
    call set_table_property('tbl', 'bitmap_columns', '[columnName [,...]]');
    • bitmap_columns指定比特编码列,交互式分析(Interactive Analytics)在这些列上构建比特编码。bitmap可以对segment内部的数据进行快速过滤,所以建议用户把filter条件的数据建成比特编码。
    • 设置bitmap_columns要求orientation为 column,即列存表。
    • bitmap_columns适合无序且取值不多的列,对于每个取值构造一个二进制串,表示取值所在位置的bitmap。
    • bitmap_columns指定的列可以为null。
    • 默认所有text列都会被隐式地设置到bitmap_columns中。
    • 举例
      begin;
      create table tbl (a int not null, b text not null);
      call set_table_property('tbl', 'bitmap_columns', 'a,b');
      commit;
  4. dictionary encoding columns
    call set_table_property('tbl', 'dictionary_encoding_columns', '[columnName [,...]]');
    • dictionary_encoding_columns指定字典编码列,交互式分析(Interactive Analytics)为指定列的值构建字典映射。字典编码可以将字符串的比较转成数字的比较,加速group by、filter等查询。
    • 设置dictionary_encoding_columns要求orientation为column,即列存表。
    • dictionary_encoding_columns指定的列可以为null。
    • 无序但取值较少的列适合设置dictionary_encoding_columns,可以压缩存储。
    • 默认所有text列都会被隐式地设置到dictionary_encoding_columns中。
    • 举例
      begin;
      create table tbl (a int not null, b text not null);
      call set_table_property('tbl', 'dictionary_encoding_columns', 'a,b');
      commit;
  5. time to live in seconds
    call set_table_property('tbl', 'time_to_live_in_seconds', '<non_negative_literal>');
    • time_to_live_in_seconds指定了表的生存时间,单位为秒,必须是非负数字类型,整数或浮点数均可。
    • 举例
      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;
      -------------------------------------------------------------
      begin;
      create table tbl (a int not null, b time not null);
      call set_table_property('tbl', 'time_to_live_in_seconds', '86400');
      commit;
  6. distribution_key
    call set_table_property('table_name', 'distribution_key', '[columnName[,...]]');
    • distribution_key属性指定了数据库表分布策略。
    • columnName部分如设置单值,不要有多余空格。如设置多值,则以逗号分隔,同样不要有多余的空格。
    • distribution_key指定的列可以为null。
    • 交互式分析中,数据库表默认为随机分布形式。数据将被随机分配到各个shard上。如果制定了分布列,数据将按照指定列,将数据shuffle到各个shard,同样的数值肯定会在同样的shard中。当以分布列做过滤条件时,交互式分析可以直接筛选出数据相关的shard进行扫描。当以分布列做join条件时,交互式分析不需要再次将数据shuffle到其他计算节点,直接在本节点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;

删除表

交互式分析(Interactive Analytics)的删除表(DROP TABLE)语法如下。
DROP TABLE [ IF EXISTS ] table_name [, ...];
说明
  1. DROP TABLE支持一次DROP多个表。
  2. 如果指定IF EXISTS, 无论表存在与否,都会返回成功。如果不指定IF EXISTS选项而表不存在,则返回异常:ERROR:table “non_exist_table“does not exist

修改表

交互式分析当前版本对表的修改(ALTER TABLE)仅支持重命名表(RENAME TABLE)和增加列(ADD COLUMN)。对于外部表(foreign table)没有限制。

针对交互式分析的分区表,还支持ATTACH PARTITIONDETACH PARTITION两种修改。详见创建和管理分区表一节。

  • 重命名表
    交互式分析的重命名普通表语法如下:
    ALTER TABLE table_name RENAME to new_table_name;
    ALTER FOREIGN TABLE my_foreign_table_name to my_new_foreign_table_name;
    说明 :如果RENAME不存在的表,或者将表重命名为已存在的表名,都会返回异常。
  • 增加列
    交互式分析给普通表增加列的语法如下:
    ALTER TABLE IF EXISTS table_name ADD COLUMN col_add_1, ADD COLUMN IF NOT EXISTS col_add_2; 

增加注释

交互式分析支持给表、外表、列等增加注释的功能, 使用方法可以参见PostgreSQL

示例建表语句如下:
-- 给表增加注释
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';