建表

在ClickHouse中创建表

相关信息存储位置

与大多数数据库管理系统一样，ClickHouse 按逻辑将表分组到数据库中。使用CREATE DATABASE命令在ClickHouse中创建新数据库：

CREATE DATABASE IF NOT EXISTS helloworld;
``

```sql
h4 :) show databases;

SHOW DATABASES

Query id: 44d7ceac-a2cc-4bf2-a293-bc7ec75ee93b

┌─name───────────────┐
│ INFORMATION_SCHEMA │
│ default            │
│ helloworld         │
│ information_schema │
│ system             │
└────────────────────┘

对应的存储目录就会生成一个文件夹(/var/lib/clickhouse/data) 是默认的存储路径。

cd /var/lib/clickhouse/data

[root data]# pwd
/var/lib/clickhouse/data
[root data]# ls
default  helloworld  system
[root data]# 

默认的日志存储在/var/log/clickhouse-server/clickhouse-server.log。

修改sudo vim /etc/clickhouse-server/config.xml

cd /var/log/clickhouse-server/

[root clickhouse-server]# cd /var/log/clickhouse-server/
[root clickhouse-server]# ls
clickhouse-server.err.log  clickhouse-server.log

同样，使用CREATE TABLE来定义一个新表。（如果不指定数据库名称，则该表将在 default数据库中。）以下名为的表my_first_table在数据库中helloworld：

CREATE TABLE helloworld.my_first_table
(
    user_id UInt32,
    message String,
    timestamp DateTime,
    metric Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp);

建表以后的结果为

[root data]# ll helloworld/
total 4
lrwxrwxrwx 1 clickhouse clickhouse 67 Aug 10 09:39 my_first_table -> /var/lib/clickhouse/store/d4d/d4d2cd3e-a6ec-4f09-a013-d59da4cb1a75/

在上面的示例中，my_first_table是一个MergeTree包含四列的表：

• user_id: 32 位无符号整数。
• message：一种String数据类型，它替换其他数据库系统中的VARCHAR、BLOB、CLOB等类型。
• timestamp: 一个DateTime值，代表时间的一个瞬间。
• metric：32位浮点数。

表引擎确定：

1. 数据的存储方式和位置。
2. 支持哪些查询。
3. 数据是否被复制。

示例

该MergeTree引擎和该家族的其他引擎（*MergeTree）是最强大的 ClickHouse 表引擎。该系列中的引擎MergeTree设计用于将大量数据插入表中。数据被快速地逐部分写入表中，然后应用规则在后台合并各部分。这种方法比插入时不断重写存储中的数据要高效得多。

主要特点：

• 存储按主键排序的数据。这允许您创建一个小型稀疏索引，帮助更快地查找数据。
• 如果指定了分区键，则可以使用分区。ClickHouse 支持某些带有分区的操作，这些操作比对相同数据和相同结果的常规操作更有效。ClickHouse还会自动截断查询中指定分区键的分区数据。
• 数据采样支持。果需要，您可以在表中设置数据采样方法。

建表语句

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS|EPHEMERAL expr1] [TTL expr1] [CODEC(codec1)] [[NOT] NULL|PRIMARY KEY],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS|EPHEMERAL expr2] [TTL expr2] [CODEC(codec2)] [[NOT] NULL|PRIMARY KEY],
    ...
    INDEX index_name1 expr1 TYPE type1(...) [GRANULARITY value1],
    INDEX index_name2 expr2 TYPE type2(...) [GRANULARITY value2],
    ...
    PROJECTION projection_name_1 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]),
    PROJECTION projection_name_2 (SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY])
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr
    [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx' [, ...] ]
    [WHERE conditions]
    [GROUP BY key_expr [SET v1 = aggr_func(v1) [, v2 = aggr_func(v2) ...]] ] ]
[SETTINGS name=value, ...]

• ORDER BY：列名或任意表达式的元组。示例：ORDER BY (CounterID, EventDate)。如果子句未显式定义主键，ClickHouse 会使用排序键作为主键PRIMARY KEY。ORDER BY tuple()如果不需要排序，请使用语法。请参阅选择主键。下面分别列举了使用ORDER BY 和不使用的情况。

CREATE TABLE helloworld.my_oneo_table
(
    user_id UInt32,
    message String,
    timestamp DateTime,
    metric Float32
)
ENGINE = MergeTree()
ORDER BY (user_id, timestamp)
PRIMARY KEY (user_id);

CREATE TABLE helloworld.my_tow_table
(
    user_id UInt32,
    message String,
    timestamp DateTime,
    metric Float32
)
ENGINE = MergeTree()
PRIMARY KEY (user_id, timestamp);

• PARTITION BY： PARTITION BY—分区键。可选修的。大多数情况下，您不需要分区键，如果确实需要分区，通常不需要比按月更细粒度的分区键。分区不会加速查询（与 ORDER BY 表达式相反）。永远不应该使用太细粒度的分区。不要按客户端标识符或名称对数据进行分区（而是将客户端标识符或名称作为 ORDER BY 表达式中的第一列）。对于按月分区，请使用表达式toYYYYMM(date_column)，其中date_column是日期类型为Date的列。这里的分区名称有格式"YYYYMM"。

上面说到分区不会加速查询，是因为ClickHouse的存储引擎（主要是MergeTree引擎）已经高度优化，能够在大规模数据上进行快速的列式存储和查询，而分区并不是提高查询性能的主要手段。

然而，分区仍然有其优势，尤其在以下情况下：

1. 数据管理和维护： 分区可以帮助你更有效地管理和维护大量数据。例如，删除旧数据、进行数据备份和恢复等操作可以在分区级别更加灵活和高效地执行。

2. 数据加载和删除： 当需要大量数据加载或删除时，分区可以降低操作的复杂性。你可以针对特定分区进行加载或删除，而不需要处理整个表。

3. 数据保留策略： 分区可以用于实现数据保留策略，比如根据日期或其他标志自动删除或归档旧数据。

下面是按指定时间字段按时间分区：

CREATE TABLE helloworld.my_three_table
(
    user_id UInt32,
    message String,
    timestamp DateTime,
    metric Float32
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
PRIMARY KEY (user_id, timestamp);

下面举一个实际情况分库分表合表的情况,假设你有多个MySQL表，每个表存储不同类型的数据，你希望每个MySQL表的数据都写入ClickHouse的对应分区中：

-- 创建ClickHouse分区表
CREATE TABLE partitioned_data
(
    table_name String,
    data_id Int64,
    data_value String
) ENGINE = MergeTree()
PARTITION BY table_name
ORDER BY (table_name, data_id);

-- 插入数据到ClickHouse分区表，分别插入到不同的分区
INSERT INTO partitioned_data (table_name, data_id, data_value)
VALUES ('mysql_table1', 1, 'Data for MySQL Table 1');

INSERT INTO partitioned_data (table_name, data_id, data_value)
VALUES ('mysql_table2', 1, 'Data for MySQL Table 2');

h4 :) select * from partitioned_data;

SELECT *
FROM partitioned_data

Query id: d7138b7f-8d22-4501-a699-9273307745bc

┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table2 │       1 │ Data for MySQL Table 2 │
└──────────────┴─────────┴────────────────────────┘
┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table1 │       1 │ Data for MySQL Table 1 │
└──────────────┴─────────┴─────────��──────────────┘

-- 插入数据到ClickHouse分区表，分别插入到不同的分区
INSERT INTO partitioned_data (table_name, data_id, data_value)
VALUES ('mysql_table1', 2, 'Data for MySQL Table 1');

h4 :) select * from partitioned_data;

SELECT *
FROM partitioned_data

Query id: 449345e4-f0e9-4845-8243-4c255479920e

┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table1 │       1 │ Data for MySQL Table 1 │
└──────────────┴─────────┴────────────────────────┘
┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table2 │       1 │ Data for MySQL Table 2 │
└──────────────┴─────────┴──────────��─────────────┘
┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table1 │       2 │ Data for MySQL Table 1 │
└──────────────┴─────────┴────────────────────────┘

3 rows in set. Elapsed: 0.001 sec. 

为什么会有3个格子呢？带着这个疑问我们看下存储的地方。如下有3个文件

[root@h4 partitioned_data]# pwd
/var/lib/clickhouse/data/helloworld/partitioned_data
[root@h4 partitioned_data]# ll
total 20
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:26 50e99f74d0667129b0094c8a85b72127_1_1_0
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:27 50e99f74d0667129b0094c8a85b72127_3_3_0
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:26 cbe4b6754726233b82d55fc111a8787c_2_2_0
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:25 detached
-rw-r----- 1 clickhouse clickhouse    1 Aug 10 10:25 format_version.txt

手动触发合并

-- 手动触发数据合并操作
OPTIMIZE TABLE partitioned_data;
select * from partitioned_data;

执行结果为如下,可以看到相同分区的数据在一块显示了。

h4 :) OPTIMIZE TABLE partitioned_data;

OPTIMIZE TABLE partitioned_data

Query id: bff04a3f-85a6-496b-9f82-d1417bfab8d2

Ok.

0 rows in set. Elapsed: 0.002 sec. 

h4 :) select * from partitioned_data;

SELECT *
FROM partitioned_data

Query id: ebd9996d-d167-42b4-8a31-7faabfdc09ef

┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table2 │       1 │ Data for MySQL Table 2 │
└──────────────┴─────────┴────────────────────────┘
┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table1 │       1 │ Data for MySQL Table 1 │
│ mysql_table1 │       2 │ Data for MySQL Table 1 │
└──────────────┴─────────┴────────────────────────┘

3 rows in set. Elapsed: 0.002 sec. 

然后看下数据具体存储的情况。多了一个1_3_1。

cd /var/lib/clickhouse/data/helloworld/partitioned_data

drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:26 50e99f74d0667129b0094c8a85b72127_1_1_0
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:27 50e99f74d0667129b0094c8a85b72127_3_3_0
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:26 cbe4b6754726233b82d55fc111a8787c_2_2_0
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:25 detached
-rw-r----- 1 clickhouse clickhouse    1 Aug 10 10:25 format_version.txt
[root@h4 partitioned_data]# cd /var/lib/clickhouse/data/helloworld/partitioned_data
[root@h4 partitioned_data]# ll
total 24
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:26 50e99f74d0667129b0094c8a85b72127_1_1_0
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:50 50e99f74d0667129b0094c8a85b72127_1_3_1
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:27 50e99f74d0667129b0094c8a85b72127_3_3_0
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:26 cbe4b6754726233b82d55fc111a8787c_2_2_0
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 10 10:25 detached
-rw-r----- 1 clickhouse clickhouse    1 Aug 10 10:25 format_version.txt

在ClickHouse中，数据存储的文件夹名称中的 3_3_0 后缀表示分区的版本号。ClickHouse支持多个分区版本，这样可以实现分区的增量更新和删除，而不是完全重写整个分区。

具体来说，分区版本包括三个部分：

1. 第一个数字：表示增量插入的次数。每次执行插入操作，这个数字会增加，指示分区的第几次插入。
2. 第二个数字：表示删除操作的次数。ClickHouse支持合并删除（MergeTree表引擎的特性），这个数字会增加，指示分区的第几次删除。
3. 第三个数字：表示分区合并的次数。当分区达到一定数量后，ClickHouse会自动合并分区，优化查询性能。这个数字指示分区经历了多少次合并。

所以在你提供的示例中，3_3_0 表示这个分区的增量插入次数为 3，删除次数为 3，合并次数为 0。

这种分区版本的设计允许ClickHouse在进行数据插入、删除和合并时能够更加高效地管理数据，并保证了数据的一致性和查询性能。

-- 插入数据到ClickHouse分区表，分别插入到不同的分区
INSERT INTO partitioned_data (table_name, data_id, data_value)
VALUES ('mysql_table1', 3, 'Data for MySQL Table 1');

INSERT INTO partitioned_data (table_name, data_id, data_value)
VALUES ('mysql_table2', 3, 'Data for MySQL Table 2');

h4 :) OPTIMIZE TABLE partitioned_data;

OPTIMIZE TABLE partitioned_data

Query id: 8316289c-51da-4ffe-9213-1bde8a1b6423

Ok.

0 rows in set. Elapsed: 0.002 sec. 

h4 :) select * from partitioned_data;

SELECT *
FROM partitioned_data

Query id: 2510feb8-5e1d-4318-a8ce-38b4f83f7e6e

┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table1 │       1 │ Data for MySQL Table 1 │
│ mysql_table1 │       2 │ Data for MySQL Table 1 │
│ mysql_table1 │       3 │ Data for MySQL Table 1 │
└──────────────┴─────────┴────────────────────────┘
┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table2 │       1 │ Data for MySQL Table 2 │
│ mysql_table2 │       3 │ Data for MySQL Table 2 │
└──────────────┴─────────┴────────────────────────┘

创建一个单分区的看下效果：

-- 创建ClickHouse分区表
CREATE TABLE partitioned_data2
(
    table_name String,
    data_id Int64,
    data_value String
) ENGINE = MergeTree()
PRIMARY KEY (data_id);

-- 插入数据到ClickHouse分区表，分别插入到不同的分区
INSERT INTO partitioned_data2 (table_name, data_id, data_value)
VALUES ('mysql_table1', 3, 'Data for MySQL Table 1');

INSERT INTO partitioned_data2 (table_name, data_id, data_value)
VALUES ('mysql_table2', 3, 'Data for MySQL Table 2');

INSERT INTO partitioned_data2 (table_name, data_id, data_value)
VALUES ('mysql_table2', 3, 'Data for MySQL Table 2');

select * from partitioned_data2;

OPTIMIZE TABLE partitioned_data2;

h4 :) select * from partitioned_data2;

SELECT *
FROM partitioned_data2

Query id: 3573b44f-8c66-420b-ab0d-54ad48c05a10

┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table1 │       3 │ Data for MySQL Table 1 │
└──────────────┴─────────┴────────────────────────┘
┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table2 │       3 │ Data for MySQL Table 2 │
└──────────────┴─────────┴─────────��──────────────┘
┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table2 │       3 │ Data for MySQL Table 2 │
└──────────────┴─────────┴────────────────────────┘

3 rows in set. Elapsed: 0.002 sec. 

h4 :) OPTIMIZE TABLE partitioned_data2;

OPTIMIZE TABLE partitioned_data2

Query id: 9e1d4f3c-4e89-4bc1-9216-00b2a538e3ec

Ok.

0 rows in set. Elapsed: 0.002 sec. 

h4 :) select * from partitioned_data2;

SELECT *
FROM partitioned_data2

Query id: 91fe5291-3343-41ff-9b31-9e0999b74d7e

┌─table_name───┬─data_id─┬─data_value─────────────┐
│ mysql_table1 │       3 │ Data for MySQL Table 1 │
│ mysql_table2 │       3 │ Data for MySQL Table 2 │
│ mysql_table2 │       3 │ Data for MySQL Table 2 │
└──────────────┴─────────┴────────────────────────┘

3 rows in set. Elapsed: 0.001 sec. 

• PRIMARY KEY :

  • ClickHouse 中的主键对于表中的每一行来说不是唯一的。
  • ClickHouse 表的主键决定了数据写入磁盘时的排序方式。每 8,192 行或 10MB 数据（称为索引粒度）在主键索引文件中创建一个条目。这种粒度概念创建了一个可以轻松放入内存的稀疏索引SELECT，并且颗粒表示查询期间处理的最小列数据量的条带。
  • 主键可以使用PRIMARY KEY参数定义。如果定义表时未PRIMARY KEY指定，则键将成为ORDER BY子句中指定的元组。如果您同时指定 PRIMARY KEY 和 ORDER BY，则主键必须是排序顺序的子集。
  • 主键也是排序键，它是 的元组(user_id, timestamp)。因此，每个列文件中存储的数据将按user_id，然后排序timestamp。

• SAMPLE BY: — 采样表达式。可选修的。如果使用采样表达式，主键必须包含它。采样表达式的结果必须是无符号整数。示例：SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。

假设你有一个包含大量用户行为数据的表，但是在某些情况下，你只需要对数据进行采样分析而不需要处理全部数据。当你查询这个表时，ClickHouse会自动应用采样，并只返回采样后的数据子集。这可以加快查询速度，特别是在处理大型数据集时。请注意，采样是一种近似的技术，可以在保持查询速度的同时减少数据量。根据需求和数据分布，你可以选择不同的采样策略，如按比例采样、按行数采样等。

-- 创建一个带有SAMPLE BY的表，对数据进行采样
CREATE TABLE sampled_user_actions (
    user_id UInt32,
    action_type String,
    action_time DateTime
) ENGINE = MergeTree()
ORDER BY (user_id, action_time)
SAMPLE BY user_id;

• TTL： 指定行存储持续时间并定义磁盘和卷之间自动部件删除逻辑的规则列表。

假设你想要创建一个存储日志数据的表，但是你只想保留最近30天的数据。你可以使用TTL来自动删除超过30天的数据。

-- 创建一个带有TTL的表，保留最近30天的数据
CREATE TABLE log_data (
    log_date Date DEFAULT toDate(now()),
    message String
) ENGINE = MergeTree()
ORDER BY log_date
TTL log_date + INTERVAL 30 DAY;

规则的类型DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'|GROUP BY指定如果满足表达式（达到当前时间）则要对该部分执行的操作：删除过期行、将部分移动（如果部分中的所有行都满足表达式）到指定磁盘 ( )TO DISK 'xxx'或到卷 ( TO VOLUME 'xxx')，或聚合过期行中的值。规则的默认类型是删除 ( DELETE)。可以指定多条规则的列表，但不得超过一条DELETE规则。

• SETTINGS： 控制行为的附加参数MergeTree。

例子:

-- 创建带有多个表级别设置的表
CREATE TABLE settings_example
(
    id Int64,
    name String
) ENGINE = MergeTree()
ORDER BY id
SETTINGS write_final_mark = 2, index_granularity = 8192;

ClickHouse消费Kafka

https://clickhouse.com/docs/en/integrations/kafka

1. 这里演示的是json数据。

CREATE TABLE 库名.kafka 对应表名
(
    `msg` String
)
    ENGINE = Kafka
        SETTINGS kafka_broker_list =
                'xxxx:端口', kafka_topic_list = '主题', kafka_group_name = '消费者组', kafka_format = 'JSONAsString',kafka_num_consumers = 3;

2. 创建物化视图的实体表，如果没有它，那么创建出来的物化视图的实体表是一张隐藏的表，自己创建对应的表比较好管理。

CREATE TABLE default.jielong_team_mp_message
(
    `字段` String COMMENT '注解',
    `字段` String COMMENT '注解',
    `字段` String COMMENT '注解',
    `字段` String COMMENT '注解',
    `字段` String COMMENT '注解',
    `字段` String COMMENT '注解',
    `字段` String COMMENT '注解',
    `字段` String COMMENT '注解',
    `字段` String COMMENT '注解',
    `字段` String COMMENT '注解'
)
    ENGINE = MergeTree
        PARTITION BY DATE(字段)
        ORDER BY (字段, 字段)
        SETTINGS index_granularity = 8192, storage_policy = '分区策略';

3. 创建物化视图，并指定存储的实体表。

CREATE MATERIALIZED VIEW 库名.表名
            TO default.对应第二步的实体表
            (
             `字段` UInt64,
             `字段` UInt64,
             `字段` UInt64,
             `字段` UInt64,
             `字段` UInt64,
             `字段` UInt64,
             `字段` UInt64,
             `字段` UInt64,
             `字段` UInt64,
             `字段` UInt64
                )
AS
SELECT JSONExtract(msg, '字段', 'UInt64')                 AS 字段,
       JSONExtract(msg, '字段', 'UInt64')               AS 字段,
       JSONExtract(msg, '字段', 'UInt64')     AS 字段,
       JSONExtract(msg, '字段', 'UInt64')            AS 字段,
       JSONExtract(msg, '字段', 'UInt64')                   AS 字段,
       JSONExtract(msg, '字段', 'UInt64')       AS 字段,
       JSONExtract(msg, '字段', 'UInt64')       AS 字段,
       JSONExtract(msg, '字段', 'UInt64')        AS 字段,
       JSONExtract(msg, '字段', 'UInt64') AS 字段,
       JSONExtract(msg, '字段', 'UInt64')             AS 字段
FROM 库名.对应kafka表名;

4. 直接删除就行，这里的删除表不会影响实际的主题。

drop table