CREATE TABLE
功能描述
在当前数据库中创建一个新的空白时序表,该表由命令执行者所有。
IoT数仓提供创建时序表DDL语句。创建时序表DDL需要提供时序表基于Key Value存储所需的维度属性(tstag)、指标属性(tsfield)以及时间(tstime)属性的相关信息。同时作为时序数据库,允许指明数据的生命周期TTL(Time To Live)以及分区创建时间周期(Period)来提供自动分区创建和自动分区删除的能力。创建时序表需要将orientation属性设置为timeseries。
注意事项
- 创建时序表的用户需要有schema cstore的USAGE权限。
- 时序表的所有属性除时间属性必须指明是维度(TSTAG)还是指标(TSFIELD)。
- 如果显式地指定partition by分区键,只允许使用时间列作为分区键。
- drop column包含索引列时,会使用剩余的索引列重建索引。如果索引列都被剔除,则会使用前10列tag列重建索引。
- 时序表不支持:update,upsert,主键,pck。
- 每一个时序表绑定一张tag表,tag表的oid和index oid分别记录在pg_class中reltoastrelid和reltoastidxid字段。
- tag表默认使用前10列tag列创建索引。
- tag表不允许在CN查询,查询表大小的时候会包含tag表。
- 非时序表建表语句设置列kvtype不生效。
语法格式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE [ IF NOT EXISTS ] table_name ({ column_name data_type [ kv_type ] | LIKE source_table [like_option [...] ] } } [, ... ]) [ WITH ( {storage_parameter = value} [, ... ] ) ] [ TABLESPACE tablespace_name ] [ DISTRIBUTE BY HASH ( column_name [,...])] [ TO { GROUP groupname | NODE ( nodename [, ... ] ) } ] [ PARTITION BY { {RANGE (partition_key) ( partition_less_than_item [, ... ] )} } [ { ENABLE | DISABLE } ROW MOVEMENT ] ]; 其中like选项like_option为: { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | DISTRIBUTION | ALL } |
参数说明
- IF NOT EXISTS
- table_name
- column_name
- data_type
- kv_type
列的kv_type属性:维度属性(TSTAG),指标属性(TSFIELD),时间属性(TSTIME);
时序表必须指定一个时间属性(TSTIME),且只能指定一个,tstime类型的列不能被删除。至少存在一个TSTAG和TSFIELD列,否则建表报错。
TSTAG列支持类型:text、char、boo、int、big int。
TSTIME列支持类型:timestamp with time zone、timestamp without time zone。在兼容Oracle语法的数据库中,也支持date类型。涉及到时区相关操作时,请选择带时区的时间类型。
TSFIELD列支持类型见TSFIELD支持的数据类型。
- LIKE source_table [like_option...]
LIKE子句声明一个表,新表自动从这个表中继承所有字段名及其数据类型。
新表与原表之间在创建动作完毕之后是完全无关的。在原表做的任何修改都不会传播到新表中,并且也不可能在扫描原表的时候包含新表的数据。
被复制的列并不使用相同的名字进行融合。如果明确的指定了相同的名字或者在另外一个LIKE子句中,将会报错。
时序表只能从时序表中进行继承。
- WITH( { storage_parameter = value } [, ...] )
- ORIENTATION
指定表数据的存储方式,即时序方式、行存方式、列存方式,该参数设置成功后就不再支持修改。
取值范围:- TIMESERIES,表示表的数据将以时序方式存储。
- COLUMN,表示表的数据将以列存方式存储。
- ROW,表示表的数据将以行方式存储。
默认值:ROW。
- COMPRESSION
指定表数据的压缩级别,它决定了表数据的压缩比以及压缩时间。一般来讲,压缩级别越高,压缩比越大,压缩时间也越长;反之亦然。实际压缩比取决于加载的表数据的分布特征。
取值范围:- 时序表和列存表的有效值为YES/NO和LOW/MIDDLE/HIGH,默认值为LOW。
- 行存表的有效值为YES/NO,默认值为NO。
- COMPRESSLEVEL
指定表数据同一压缩级别下的不同压缩水平,它决定了同一压缩级别下表数据的压缩比以及压缩时间。对同一压缩级别进行了更加详细的划分,为用户选择压缩比和压缩时间提供了更多的空间。总体来讲,此值越大,表示同一压缩级别下压缩比越大,压缩时间越长;反之亦然。该参数只对时序表和列存表有效。
取值范围:0~3,默认值为0。
- MAX_BATCHROW
指定了在数据加载过程中一个存储单元可以容纳记录的最大数目。该参数只对时序表和列存表有效。
取值范围:10000~60000
默认值60000
- PARTIAL_CLUSTER_ROWS
指定了在数据加载过程中进行将局部聚簇存储的记录数目。该参数只对时序表和列存表有效。
取值范围:600000~2147483647
- ENABLE_DELTA
指定了在时序表是否开启delta表。该参数只对时序表和列存表有效。
默认值:on
- SUB_PARTITION_COUNT
指定时序表二级分区的个数。该参数用于设置在导入阶段二级分区个数。在建表时进行设置,建表后不支持修改。不建议用户随意设置该默认值,可能会影响导入和查询的性能。
取值范围:1~1024,默认值为32
- DELTAROW_THRESHOLD
指定时序表导入时小于多少行(SUB_PARTITION_COUNT * DELTAROW_THRESHOLD)的数据进入delta表,enable_delta开启时生效。该参数只对时序表和列存表有效。
取值范围:0~60000
默认值:10000
- COLVERSION
指定时存储格式的版本,仅时序表和列存表支持该参数,时序表不支持不同存储格式版本之间的切换。时序表只支持2.0版本。
取值范围:
1.0:列存表的每列以一个单独的文件进行存储,文件名以relfilenode.C1.0、relfilenode.C2.0、relfilenode.C3.0等命名。
2.0:时序表/列存表的每列合并存储在一个文件中,文件名以relfilenode.C1.0命名。
默认值:2.0
- TTL
设置时序表定时删除分区task任务。默认不创建删除分区task任务。
取值范围:
1 hour ~ 100 years
- PERIOD
设置时序表定时创建分区task任务。如果设置TTL,PERIOD不能大于TTL。
取值范围:
1 hour ~ 100 years, 默认值:1 day
- ORIENTATION
- TABLESPACE tablespace_name
- DISTRIBUTE BY
取值范围:
HASH (column_name ) :对指定的列进行Hash,通过映射,把数据分布到指定DN。
时序表当前默认按照所有TAG列进行分布。
- TO { GROUP groupname | NODE ( nodename [, ... ] ) }
TO GROUP指定创建表所在的Node Group,目前不支持hdfs表使用。TO NODE主要供内部扩容工具使用,一般用户不应该使用。
- PARTITION BY
- TTL(Time To Live)指明该表的数据保存周期,超过TTL周期的数据将被清理。Period指明按照时间划分的周期对数据进行分区,分区的大小可能对查询性能有影响,同时每隔周期时间会创建一个新的周期大小的分区。TTL和Period值为Interval类型,例如:“1 hour”, “1 day”, “1 week”, “1 month” ,“1 year”, “1 month 2 day 3 hour”...
- Storage_parameter存储参数中的orientation指明是否为时序存储方式,只有当orientation为timeseries时存储方式才支持Key Value存储。
- 时序表不需要手动指定DISTRIBUTE BY和PARTITION BY,默认按照所有tag列分布,同时以TSTIME列为分区键,创建具有自动分区管理功能的分区表。
示例
创建简单的时序表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE IF NOT EXISTS CPU( scope_name text TSTag, server_ip text TSTag, group_path text TSTag, time timestamptz TSTime, idle numeric TSField, system numeric TSField, util numeric TSField, vcpu_num numeric TSField, guest numeric TSField, iowait numeric TSField, users numeric TSField) with (orientation=TIMESERIES) distribute by hash(scope_name); CREATE TABLE CPU1( idle numeric TSField, IO numeric TSField, scope text TSTag, IP text TSTag, time timestamp TSTime ) with (TTL='7 days', PERIOD='1 day', orientation=TIMESERIES); CREATE TABLE CPU2 (LIKE CPU INCLUDING ALL); |