Clickhouse创建表

创建本地表

建表语句基本语法如下：

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,c 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。

posted @ 2021-05-27 12:02 jason_wei 阅读(16130) 评论(0) 编辑收藏举报

刷新页面返回顶部

jason_wei

工作内容记录