Clickhouse分布式数据库列式数据库管理系统

导读一、前言ClickHouse的基础概念和环境部署，可以参考我之前的文章：列式数据库管理系统——ClickHouse（version：22.7.1环境部署）二....一、前言

ClickHouse的基础概念和环境部署，可以参考我之前的文章：列式数据库管理系统——ClickHouse（version：22.7.1 环境部署）

二、SQL语法讲解与实战操作

ClickHouse有2类解析器：完整SQL解析器（递归式解析器），以及数据格式解析器（快速流式解析器） 除了 INSERT 查询，其它情况下仅使用完整SQL解析器。 官方文档：https://clickhouse.com/docs/zh/sql-reference/syntax

1）数据库1、创建数据库

【语法】

`CREATE DATABASE [IF NOT exists] db_name [ON CLUSTER cluster] [ENGINE = engine(...)]`

• IF NOT EXISTS：如果db_name数据库已经存在，则ClickHouse不会创建新数据库并且：如果指定了子句，则不会引发异常。如果未指定子句，则抛出异常。
• ON CLUSTER：ClickHouse在指定集群的所有服务器上创建db_name数据库。
• ENGINE：指定数据库引擎，默认情况下，ClickHouse使用Atomic，还有其它引擎：数据库引擎

【示例1】单点操作

# 登录clickhouse-client -h local-168-182-111 -d default -m -u default --password 123456# 不指定数据库引擎，默认使用Atomic引擎，不指定集群，则只在当前击节点有效，其它节点是不会有这个库的create database if not exists ck_test;

默认的数据库是磁盘上的一个文件目录，执行创建之后可以在数据目录下创建文件：

ls -l /var/lib/clickhouse/data/# 可以看相关的元数据ls -l /var/lib/clickhouse/metadata# 查看数据库引擎cat /var/lib/clickhouse/metadata/ck_test.sql

# 登录clickhouse-client -h local-168-182-111 -d default -m -u default --password 123456# 查看show databases;# 查看创建数据库的语句show create database ck_test\G;# 删除数据库drop database ck_test;# 检查show databases;

【示例2】集群操作

# 登录clickhouse-client -h local-168-182-111 -d default -m -u default --password 123456# 查看集群select * from system.clusters;# 先查看show databases;# 不指定数据库引擎，默认使用Atomic引擎，指定集群create database if not exists ck_test ON CLUSTER ck_cluster_2022;# 检查show databases;

【示例3】集群操作切换MYSQL引擎

【语法】

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]ENGINE = MySQL(host:port, [database | database], user, password)

引擎参数

• host:port — MySQL服务地址
• database — MySQL数据库名称
• user — MySQL用户名
• password — MySQL用户密码

【示例】MySQL操作

mysql -uroot -p -h local-168-182-113密码：123456# 创建数据库create database ck_test001;USE ck_test001;CREATE TABLE `ck_test001`.`mysql_table` (`int_id` INT NOT NULL AUTO_INCREMENT,`float` FLOAT NOT NULL,PRIMARY KEY (`int_id`));show tabels;# 添加数据insert into mysql_table (`int_id`, `float`) VALUES (1,2);# 查看数据select * from mysql_table;

ClickHouse操作

# 登录clickhouse-client -h local-168-182-111 -d default -m -u default --password 123456# 创建数据库使用mysql引擎create database if not exists mysql_db ON CLUSTER ck_cluster_2022 ENGINE = MySQL(local-168-182-113:3306, ck_test001, root, 123456);

2）数据表

ClickHouse默认的表引擎是mergeTree，更多表引擎，可以参考官方文档，这里会讲主要的几种：MergeTree、MYSQL、HDFS、Hive、KAFKA。

1、MergeTree

【语法】

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2], ... INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1, INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2) ENGINE = MergeTree()ORDER BY expr[PARTITION BY expr][PRIMARY KEY expr][SAMPLE BY expr][TTL expr [DELETE|TO DISK xxx|TO VOLUME xxx], ...][SETTINGS name=value, ...]

参数解释

• ENGINE ——引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。
• ORDER BY —— 排序键。
• 可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) 。
• 如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。
• 如果不需要排序，可以使用 ORDER BY tuple(). 参考 选择主键
• PARTITION BY——分区键 ，可选项。

要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。

• PRIMARY KEY——如果要 选择与排序键不同的主键，在这里指定，可选项。

默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。 因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。

• SAMPLE BY - 用于抽样的表达式，可选项。

如果要用抽样表达式，主键中必须包含这个表达式。例如： SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。

• TTL——指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。

# 表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：TTL date INTERVAl 1 DAY

• SETTINGS —— 控制 MergeTree 行为的额外参数，可选项：
• index_granularity ——索引粒度。索引中相邻的『标记』间的数据行数。默认值8192 。参考数据存储。
• index_granularity_bytes ——索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。
• min_index_granularity_bytes ——允许的最小数据粒度，默认值：1024b。该选项用于防止误操作，添加了一个非常低索引粒度的表。
• enable_mixed_granularity_parts——是否启用通过 index_granularity_bytes 控制索引粒度的大小。在19.11版本之前, 只有 index_granularity 配置能够用于限制索引粒度的大小。当从具有很大的行（几十上百兆字节）的表中查询数据时候，index_granularity_bytes 配置能够提升ClickHouse的性能。如果您的表里有很大的行，可以开启这项配置来提升SELECT 查询的性能。
• use_minimalistic_part_header_in_ZooKeeper——ZooKeeper中数据片段存储方式 。如果use_minimalistic_part_header_in_zookeeper=1 ，ZooKeeper 会存储更少的数据。
• min_merge_bytes_to_use_direct_io——使用直接 I/O 来操作磁盘的合并操作时要求的最小数据量。合并数据片段时，ClickHouse 会计算要被合并的所有数据的总存储空间。如果大小超过了 min_merge_bytes_to_use_direct_io 设置的字节数，则 ClickHouse 将使用直接 I/O 接口（O_DIRECT 选项）对磁盘读写。如果设置 min_merge_bytes_to_use_direct_io = 0 ，则会禁用直接 I/O。默认值：10 * 1024 * 1024 * 1024 字节。
• merge_with_ttl_timeout——TTL合并频率的最小间隔时间，单位：秒。默认值: 86400 (1 天)。
• write_final_mark——是否启用在数据片段尾部写入最终索引标记。默认值: 1（不要关闭）。
• merge_max_block_size——在块中进行合并操作时的最大行数限制。默认值：8192
• storage_policy——存储策略
• min_bytes_for_wide_part,min_rows_for_wide_part——在数据片段中可以使用Wide格式进行存储的最小字节数/行数。您可以不设置、只设置一个，或全都设置。
• max_parts_in_total - 所有分区中最大块的数量(意义不明)
• max_compress_block_size——在数据压缩写入表前，未压缩数据块的最大大小。您可以在全局设置中设置该值(参见max_compress_block_size)。建表时指定该值会覆盖全局设置。
• min_compress_block_size——在数据压缩写入表前，未压缩数据块的最小大小。建表时指定该值会覆盖全局设置。
• max_partitions_to_read——一次查询中可访问的分区最大数。。

示例配置

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

同时我们设置了一个按用户 ID 哈希的抽样表达式。这使得您可以对该表中每个 CounterID 和 EventDate 的数据伪随机分布。如果您在查询时指定了 SAMPLE 子句。 ClickHouse会返回对于用户子集的一个均匀的伪随机数据采样。index_granularity 可省略因为 8192 是默认设置 。

【示例1】简单使用

-- 创建本地数据库create database if not exists ck_test002;-- 创建本地表CREATE TABLE ck_test002.example_table( d DateTime, a Int TTL d INTERVAL 1 MONTH, b Int TTL d INTERVAL 1 MONTH, c String)ENGINE = MergeTreePARTITION BY toYYYYMM(d)ORDER BY d;show tables from ck_test002;

【示例2】使用多个块设备进行数据存储

MergeTree 系列表引擎可以将数据存储在多个块设备上。这对某些可以潜在被划分为“冷”“热”的表来说是很有用的。为了应用存储策略，可以在建表时使用storage_policy设置。

【配置】磁盘、卷和存储策略应当在主配置文件 /etc/clickhouse-server/config.xml 或 /etc/clickhouse-server/config.d 目录中的独立文件中的 <storage_configuration> 标签内定义。

示例配置如下：

# 创建存储路径mkdir -p /var/lib/clickhouse/storage001/data{1..3}# 授权chown -R clickhouse:clickhouse /var/lib/clickhouse/storage001

存储配置如下：

<storage_configuration> <disks> <disk1> <!-- disk name --> <path>/var/lib/clickhouse/storage001/data1/</path> </disk1> <disk2> <path>/var/lib/clickhouse/storage001/data2/</path> <keep_free_space_bytes>10485760</keep_free_space_bytes> </disk2> <disk3> <path>/var/lib/clickhouse/storage001/data3/</path> <keep_free_space_bytes>10485760</keep_free_space_bytes> </disk3> </disks></storage_configuration>

• <disk_name_N> — 磁盘名，名称必须与其他磁盘不同.
• path — 服务器将用来存储数据 (data 和 shadow 目录) 的路径, 应当以 ‘/’ 结尾.
• keep_free_space_bytes — 需要保留的剩余磁盘空间。

存储策略配置：

<storage_configuration> <policies> <hdd_in_order> <!-- policy name --> <volumes> <single> <!-- volume name --> <disk>disk1</disk> </single> </volumes> </hdd_in_order> <moving_from_ssd_to_hdd> <!-- policy name --> <volumes> <hot> <!-- volume name --> <disk>disk2</disk> <max_data_part_size_bytes>1073741824</max_data_part_size_bytes> </hot> <cold> <!-- volume name --> <disk>disk3</disk> </cold> </volumes> <move_factor>0.2</move_factor> </moving_from_ssd_to_hdd> </policies></storage_configuration>

标签：

• disk — 卷中的磁盘。
• max_data_part_size_bytes — 卷中的磁盘可以存储的数据片段的最大大小。
• move_factor— 当可用空间少于这个因子时，数据将自动的向下一个卷（如果有的话）移动 (默认值为 0.1)。
• prefer_not_to_merge - 禁止在这个卷中进行数据合并。该选项启用时，对该卷的数据不能进行合并。这个选项主要用于慢速磁盘。

完整配置（/etc/clickhouse-server/config.d/storage001.xml）：

<yandex><storage_configuration> <!-- 存储配置 --> <disks> <disk1> <!-- disk name --> <path>/var/lib/clickhouse/storage001/data1/</path> </disk1> <disk2> <path>/var/lib/clickhouse/storage001/data2/</path> <keep_free_space_bytes>10485760</keep_free_space_bytes> </disk2> <disk3> <path>/var/lib/clickhouse/storage001/data3/</path> <keep_free_space_bytes>10485760</keep_free_space_bytes> </disk3> </disks><!-- 存储策略配置 --><policies> <hdd_in_order> <!-- policy name --> <volumes> <single> <!-- volume name --> <disk>disk1</disk> </single> </volumes> </hdd_in_order> <moving_from_ssd_to_hdd> <!-- policy name --> <volumes> <hot> <!-- volume name --> <disk>disk2</disk> <max_data_part_size_bytes>1073741824</max_data_part_size_bytes> </hot> <cold> <!-- volume name --> <disk>disk3</disk> </cold> </volumes> <move_factor>0.2</move_factor> </moving_from_ssd_to_hdd> </policies></storage_configuration></yandex>

授权

chown -R clickhouse.clickhouse /etc/clickhouse-server/config.d

查看（配置自动加载，不需要重启服务）

# 登录clickhouse-client -u default --password 123456 --port 9000 -h local-168-182-110 --multiquery# 检查SELECT name,path,formatReadableSize(free_space) AS free,formatReadableSize(total_space) AS total,formatReadableSize(keep_free_space) AS reserved from system.disks;SELECT policy_name, volume_name, disks FROM system.storage_policies;

在创建表时，指定存储策略（默认default）：

CREATE TABLE table_with_non_default_policy ( EventDate Date, OrderID UInt64, BannerID UInt64, SearchPhrase String)ENGINE = MergeTreeORDER BY (OrderID, BannerID)PARTITION BY toYYYYMM(EventDate)SETTINGS storage_policy = moving_from_ssd_to_hdd

2、MYSQL

MySQL 引擎可以对存储在远程 MySQL 服务器上的数据执行 SELECT 查询。

【语法】

MySQL(host:port, database, table, user, password[, replace_query, on_duplicate_clause]);

参数详解：

• host:port — MySQL 服务器地址。
• database — 数据库的名称。
• table — 表名称。
• user — 数据库用户。
• password — 用户密码。
• replace_query — 将 INSERT INTO 查询是否替换为 REPLACE INTO 的标志。如果 replace_query=1，则替换查询。【可选】
• on_duplicate_clause — 将 ON DUPLICATE KEY UPDATE - on_duplicate_clause 表达式添加到 INSERT 查询语句中。例如：impression = VALUES(impression) impression。如果需要指定 on_duplicate_clause，则需要设置 replace_query=0。如果同时设置 replace_query = 1 和 on_duplicate_clause，则会抛出异常。【可选】

【示例】MySQL操作

mysql -uroot -p -h local-168-182-113密码：123456# 创建数据库create database if not exists ck_test003;USE ck_test003;CREATE TABLE `ck_test003`.`user` ( `id` bigint NOT NULL AUTO_INCREMENT, `account_no` bigint DEFAULT NULL, `phone` varchar(128) COMMENT 手机号, `username` varchar(255) COMMENT 用户名, `create_time` datetime DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;# 添加数据INSERT INTO `ck_test003`.`user`(`id`, `account_no`, `phone`, `username`, `create_time`) VALUES (1, 35, 00719526, zhangsan, 2022-07-24 16:02:15);# 查看数据select * from `ck_test003`.`user`;

在clickhouse中创建user表，设置引擎为mysql

# 登录clickhouse-client -h local-168-182-111 -d default -m -u default --password 123456create database if not exists ck_mysql_test003;# 创建表，字段得跟mysql表一一对应CREATE TABLE `ck_mysql_test003`.`user`( id UInt32 , account_no UInt32 , username String , phone String , create_time Datetime) ENGINE = MySQL(local-168-182-113:3306,ck_test003,user,root,123456);# 在clickhouse查询mysql数据select * from `ck_mysql_test003`.`user`;

在clickhouse中添加数据

# 登录clickhouse-client -h local-168-182-111 -d default -m -u default --password 123456# 添加数据INSERT INTO `ck_mysql_test003`.`user`(`id`, `account_no`, `phone`, `username`, `create_time`) VALUES (2, 39, 007996, lisi, 2022-07-25 17:02:15);# 查询select * from `ck_mysql_test003`.`user`;

在mysql中查询

mysql -uroot -p -h local-168-182-113密码：123456select * from `ck_test003`.`user`;

3、HDFS（hadoop-3.3.3）

首选需要安装HDFS，可以参考我之前的文章：大数据Hadoop原理介绍 安装 实战操作（HDFS YARN MapReduce）

环境准备