本文介绍如何建表和视图。

创建本地表

建表语句基本语法如下:

CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER cluster
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = engine_name()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...];
选项描述:
  • db:指定数据库名称,如果当前语句没有包含‘db’,则默认使用当前选择的数据库为‘db’。
  • cluster:指定集群名称,目前固定为default。ON CLUSTER 将在每一个节点上都创建一个本地表。
  • type:该列数据类型,例如 UInt32。
  • DEFAULT:该列缺省值。如果INSERT中不包含指定的列,那么将通过表达式计算它的默认值并填充它。
  • MATERIALIZED:物化列表达式,表示该列不能被INSERT,是被计算出来的; 在INSERT语句中,不需要写入该列;在SELECT *查询语句结果集不包含该列。
  • ALIAS :别名列。这样的列不会存储在表中。 它的值不能够通过INSERT写入,同时使用SELECT查询星号时,这些列也不会被用来替换星号。 但是它们可以用于SELECT中,在这种情况下,在查询分析中别名将被替换。
  • 物化列与别名列的区别: 物化列是会保存数据,查询的时候不需要计算,而表达式列不会保存数据,查询的时候需要计算,查询时候返回表达式的计算结果
以下选项与表引擎相关,只有MergeTree系列表引擎支持:
  • PARTITION BY:指定分区键。通常按照日期分区,也可以用其他字段或字段表达式。
  • ORDER BY:指定 排序键。可以是一组列的元组或任意的表达式。
  • PRIMARY KEY: 指定主键,默认情况下主键跟排序键相同。因此,大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。
  • SAMPLE BY :抽样表达式,如果要用抽样表达式,主键中必须包含这个表达式。
  • SETTINGS:影响 性能的额外参数。
  • GRANULARITY :索引粒度参数。

示例,创建一个本地表:

CREATE TABLE ontime_local ON CLUSTER default
(
    Year UInt16,
    Quarter UInt8,
    Month UInt8,
    DayofMonth UInt8,
    DayOfWeek UInt8,
    FlightDate Date,
    FlightNum String,
    Div5WheelsOff String,
    Div5TailNum String
)ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/ontime_local/{shard}',
    '{replica}')
 PARTITION BY toYYYYMM(FlightDate)
 PRIMARY KEY (intHash32(FlightDate))
 ORDER BY (intHash32(FlightDate),FlightNum)
 SAMPLE BY intHash32(FlightDate)
SETTINGS index_granularity= 8192 ;
说明

高可用集群(双副本),要用ReplicatedMergeTree等Replicated系列引擎,否则副本之间不进行数据复制,导致数据查询结果不一致。

{shard},{replica} 参数不需要赋值。

创建分布式表

基于本地表创建一个分布式表。

创建分布式表基本语法:

CREATE TABLE  [db.]table_name  ON CLUSTER default
 AS db.local_table_name   ENGINE = Distributed(<cluster>, <database>, <shard table> [, sharding_key])
参数说明:
  • db:数据库名。
  • local_table_name:对应的已经创建的本地表表名。
  • shard table:同上,对应的已经创建的本地表表名。
  • sharding_key:分片表达式。可以是一个字段,例如user_id(integer类型),通过对余数值进行取余分片;也可以是一个表达式,例如rand(),通过rand()函数返回值/shards总权重分片;为了分片更均匀,可以加上hash函数,如intHash64(user_id)。

示例:创建一个分布式表:

--建立分布式表
CREATE TABLE ontime_distributed ON CLUSTER default
 AS db_name.ontime_local 
ENGINE = Distributed(default, db_name, ontime_local, rand());
说明 : db_name 是数据库的名字,需要填写。

复制另一个表结构

创建与另一个表相同结构的表,语法如下:

CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER default AS [db.]name2 [ENGINE = engine];

表引擎可以通过ENGINE=engine字句指定,默认与被复制的表“name2”相同。

示例:

create table t2 ON CLUSTER default as db1.t1;

通过SELECT语句创建

使用指定的引擎创建一个与SELECT子句的结果具有相同结构的表,并使用SELECT子句的结果填充它。语法如下:

CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER default ENGINE = engine AS SELECT ...

其中ENGINE是需要明确指定的。

示例:

create table t2 ON CLUSTER default ENGINE =MergeTree() as select * from db1.t1 where id<100;

创建临时表

创建临时语法如下:

CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name ON CLUSTER default 
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
)

通过TEMPORARY 关键字表示临时表。大多数情况下,临时表不是手动创建的,只有在分布式查询处理中使用(GLOBAL) IN时为外部数据创建。

创建视图

创建视图语法如下:

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] ON CLUSTER default [ENGINE = engine] [POPULATE] AS SELECT ...

有MATERIALIZED关键字表示是物化视图,否则为普通视图。

假如用以下语句创建了一个视图。

CREATE VIEW view_1 ON CLUSTER default AS SELECT a,b,c,d FROM db1.t1;

那么下列两个语句完全等价。

SELECT a, b, c FROM view_1 ;
SELECT a, b, c FROM (SELECT a,b,b FROM db1.t1);

物化视图存储的数据是由相应的SELECT查询转换得来的。

在创建物化视图时,您还必须指定表的引擎 - 将会使用这个表引擎存储数据。

目前物化视图的工作原理:当将数据写入到物化视图中SELECT子句所指定的表时,插入的数据会通过SELECT子句查询进行转换并将最终结果插入到视图中。

如果创建物化视图时指定了POPULATE子句,则在创建时将该表的数据插入到物化视图中。就像使用CREATE TABLE ... AS SELECT ...一样。否则,物化视图只会包含在物化视图创建后的新写入的数据。

我们不推荐使用POPULATE,因为在视图创建期间写入的数据将不会写入其中。当一个SELECT子句包含DISTINCT, GROUP BY, ORDER BY, LIMIT时,请注意,这些仅会在插入数据时在每个单独的数据块上执行。例如,如果你在其中包含了GROUP BY,则只会在查询期间进行聚合,但聚合范围仅限于单个批的写入数据。数据不会进一步被聚合。但是当你使用一些其他数据聚合引擎时这是例外的,如:SummingMergeTree。

目前对物化视图执行ALTER是不支持的,因此这可能是不方便的。如果物化视图是使用的TO [db.]name的方式进行构建的,你可以使用DETACH语句现将视图剥离,然后使用ALTER运行在目标表上,然后使用ATTACH将之前剥离的表重新加载进来。视图看起来和普通的表相同。例如,你可以通过SHOW TABLES查看到它们。

没有单独的删除视图的语法。如果要删除视图,请使用DROP TABLE。