clickhouse笔记
1 clickhouse特点
1.1 完备的DBMS功能
-
DDL（数据定义语言）：可以动态地创建、修改或删除数据库、表和视图，而无须重启服务。
-
DML（数据操作语言）：可以动态查询、插入、修改或删除数据。
-
权限控制：可以按照用户粒度设置数据库或者表的操作权限，保障数据的安全性。
-
分布式管理：提供集群模式，能够自动管理多个数据库节点。
-
数据备份与恢复：提供了数据备份导出与导入恢复机制，满足生产环境的要求。
1.2 列式存储与数据压缩
-
按列存储与按行存储相比，前者可以有效减少查询时所需扫描的数据量
-
能更好的压缩数据,压缩前：abcdefghi_bcdefghi 压缩后：abcdefghi_(9,8)。数据库的瓶颈通常在IO，很高的压缩比，可以大大减轻数据读取的压力，提高响应速度
-
除去字符串类型，其他类型的字段通常是固定长度的，而且在磁盘和内存的字节顺序通常是一致的，可以直接映射，省去了解析的过程
-
列式存储可以向量化的处理一个字段。可以将一个列的一整块连续数据读入CPU cache，效率非常高
'B|C|D', 'B|D', 'A|C', 'B|D' ,'B|C', 'B|C|D' A,B,C,D [0,1,1,1] [0,1,0,1] [1,0,1,0] [0,1,0,1] [0,1,1,0] [0,1,1,1]
1.3 关系模型与SQL查询
ClickHouse使用关系模型描述数据并提供了传统数据库的概念（数据库、表、视图和函数等）。与此同时，ClickHouse完全使用SQL作为查询语言（支持GROUP BY、ORDER BY、JOIN、IN等大部分标准SQL）
1.4 多线程与分布式
ClickHouse在数据存取方面，既支持分区（纵向扩展，利用多线程原理），也支持分片（横向扩展，利用分布式原理），可以说是将多线程和分布式的技术应用到了极致。
1.5 多主架构
ClickHouse则采用多主架构，集群中的每个节点角色对等，客户端访问任意一个节点都能得到相同的效果。它天然规避了单点故障的问题，非常适合用于多数据中心、异地多活的场景
1.6 数据分片与分布式查询
数据分片是将数据进行横向切分，这是一种在面对海量数据的场景下，解决存储和查询瓶颈的有效手段，是一种分治思想的体现
ClickHouse提供了本地表（Local Table）与分布式表（Distributed Table）的概念。一张本地表等同于一份数据的分片。而分布式表本身不存储任何数据，它是本地表的访问代理，其作用类似分库中间件。借助分布式表，能够代理访问多个数据分片，从而实现分布式查询。
第2章 安装与部署
2.1 ClickHouse的安装过程
1.centos7 取消打开文件限制
ulimit -n # 查看打开文件下载
ulimit -a # 查看详情
vim /etc/security/limits.conf
* soft nofile 1024000
* hard nofile 1024000
hive - nofile 1024000
hive - nproc 1024000
vim /etc/security/limits.d/20-nproc.conf
#加大普通用户限制 也可以改为unlimited
* soft nproc 409600
root soft nproc unlimited
shutdown -r now 重启服务
2.centos7取消selinux
getenforce # 查看
/usr/sbin/sestatus -v # 查看
vim /etc/selinux/config # 关闭selinux服务
将SELINUX=enforcing改为SELINUX=disabled
shutdown -r now 重启服务
3.关闭防火墙
firewall-cmd --state # 查看防火墙状态
systemctl stop firewalld # 禁用防火墙
systemctl start firewalld # 启动防火墙
4.安装依赖
yum install -y curl pygpgme yum-utils coreutils epel-release libtool *unixODBC*
5.下载安装包
http://repo.yandex.ru/clickhouse/rpm/stable/x86_64/
clickhouse-client-20.6.6.7-2.noarch.rpm 11-Sep-2020 09:55 102K
clickhouse-client-20.6.7.4-2.noarch.rpm 18-Sep-2020 13:16 102K
clickhouse-client-20.7.2.30-2.noarch.rpm 31-Aug-2020 16:20 102K
clickhouse-client-20.7.3.7-2.noarch.rpm 18-Sep-2020 12:43 102K
clickhouse-client-20.8.2.3-2.noarch.rpm 08-Sep-2020 12:46 115K
clickhouse-client-20.8.3.18-2.noarch.rpm 18-Sep-2020 12:23 115K
clickhouse-common-static-20.6.6.7-2.x86_64.rpm 11-Sep-2020 09:55 120M
clickhouse-common-static-20.6.7.4-2.x86_64.rpm 18-Sep-2020 13:16 120M
clickhouse-common-static-20.7.2.30-2.x86_64.rpm 31-Aug-2020 16:20 120M
clickhouse-common-static-20.7.3.7-2.x86_64.rpm 18-Sep-2020 12:44 120M
clickhouse-common-static-20.8.2.3-2.x86_64.rpm 08-Sep-2020 12:46 137M
clickhouse-common-static-20.8.3.18-2.x86_64.rpm 18-Sep-2020 12:23 137M
clickhouse-server-20.6.6.7-2.noarch.rpm 11-Sep-2020 09:56 125K
clickhouse-server-20.6.7.4-2.noarch.rpm 18-Sep-2020 13:17 125K
clickhouse-server-20.7.2.30-2.noarch.rpm 31-Aug-2020 16:21 126K
clickhouse-server-20.7.3.7-2.noarch.rpm 18-Sep-2020 12:44 126K
clickhouse-server-20.8.2.3-2.noarch.rpm 08-Sep-2020 12:46 139K
clickhouse-server-20.8.3.18-2.noarch.rpm 18-Sep-2020 12:24 139K
6.创建安装目录
mkdir -p /usr/local/clickhouse
[root@localhost clickhouse]# ll
总用量 141008
-rw-r--r--. 1 root root 117705 9月 22 14:02 clickhouse-client-20.8.3.18-2.noarch.rpm
-rw-r--r--. 1 root root 144128776 9月 22 14:04 clickhouse-common-static-20.8.3.18-2.x86_64.rpm
-rw-r--r--. 1 root root 142403 9月 22 14:01 clickhouse-server-20.8.3.18-2.noarch.rpm
rpm -ivh *.rpm # 安装clickhouse
rpm -Uvh *.rpm # 在线升级clickhouse版本 在升级的过程中 原来的配置将会保存下来
rpm -e clickhouse-client-20.3.8.53-2.noarch # 卸载
rpm -e clickhouse-common-static-20.3.8.53-2.x86_64 # 卸载
rpm -e clickhouse-server-20.3.8.53-2.noarch # 卸载
7.配置文件
[root@localhost clickhouse-server]# pwd
/etc/clickhouse-server
[root@localhost clickhouse-server]# ll
总用量 76
drwxr-xr-x. 2 root root 6 8月 26 09:52 config.d
-rw-r--r--. 1 root root 22601 8月 26 09:54 config.xml # clickhouse配置文件
-rw-r--r--. 1 root root 33738 9月 18 06:29 config.xml.rpmnew
lrwxrwxrwx. 1 root root 41 8月 26 09:52 preprocessed -> /var/lib/clickhouse//preprocessed_configs
drwxr-xr-x. 2 root root 6 8月 26 09:52 users.d
-rw-r--r--. 1 root root 5522 8月 26 10:06 users.xml # 用户的配置文件
-rw-r--r--. 1 root root 5587 9月 18 06:29 users.xml.rpmnew
8.config.xml配置文件详解
<?xml version="1.0"?>
<yandex>
<logger>
<level>trace</level>
<log>/var/log/clickhouse-server/clickhouse-server.log</log> <!-- 日志存放目录 -->
<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog> <!-- 错误日志存放目录 -->
<size>1000M</size> <!-- 文件的大小。文件达到大小后，ClickHouse将对其进行存档并重命名，并在其位置创建一个新的日志文件 -->
<count>10</count> <!-- ClickHouse存储的已归档日志文件的数量 -->
</logger>
<http_port>8123</http_port> <!-- 通过HTTP连接到服务器的端口 -->
<tcp_port>9000</tcp_port> <!--通过TCP协议与客户端进行通信的端口，即ClickHouse端口。-->
<mysql_port>9004</mysql_port> <!--通过MySQL协议与客户端通信的端口。-->
<https_port>8443</https_port> <!-- 通过HTTP连接到服务器的端口 -->
<tcp_port_secure>9440</tcp_port_secure> <!--通过TCP协议与客户端进行通信的端口，即ClickHouse端口。 与OpenSSL设置一起使用。-->
<openSSL>
<server>
<certificateFile>/etc/clickhouse-server/server.crt</certificateFile> <!--PEM格式的客户端/服务器证书文件的路径。如果privateKeyFile包含证书，则可以忽略它-->
<privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile> <!--具有PEM证书的秘密密钥的文件的路径。该文件可能同时包含密钥和证书-->
<dhParamsFile>/etc/clickhouse-server/dhparam.pem</dhParamsFile> <!--dh文件路径-->
<verificationMode>none</verificationMode> <!--检查节点证书的方法-->
<loadDefaultCAFile>true</loadDefaultCAFile> <!--指示将使用OpenSSL的内置CA证书。可接受的值：true，false-->
<cacheSessions>true</cacheSessions> <!--启用或禁用缓存会话。必须与sessionIdContext结合使用。可接受的值：true，false。-->
<disableProtocols>sslv2,sslv3</disableProtocols> <!--不允许使用的协议-->
<preferServerCiphers>true</preferServerCiphers> <!--首选服务器密码-->
</server>
<client>
<loadDefaultCAFile>true</loadDefaultCAFile> <!--指示将使用OpenSSL的内置CA证书。可接受的值：true，false-->
<cacheSessions>true</cacheSessions> <!--启用或禁用缓存会话。必须与sessionIdContext结合使用。可接受的值：true，false。-->
<disableProtocols>sslv2,sslv3</disableProtocols> <!--不允许使用的协议-->
<preferServerCiphers>true</preferServerCiphers> <!--首选服务器密码-->
<invalidCertificateHandler> <!--用于验证无效证书的类-->
<name>RejectCertificateHandler</name>
</invalidCertificateHandler>
</client>
</openSSL>
<interserver_http_port>9009</interserver_http_port> <!--于在ClickHouse服务器之间交换数据的端口。-->
<listen_host>0.0.0.0</listen_host> <!--限制来源主机的请求 允许所有-->
<listen_host>127.0.0.1</listen_host> <!--限制来源主机的请求 只允许本机访问-->
<max_connections>4096</max_connections> <!--最大连接数-->
<keep_alive_timeout>3</keep_alive_timeout> <!--ClickHouse在关闭连接之前等待传入请求的秒数。 默认为3秒。-->
<max_concurrent_queries>100</max_concurrent_queries> <!--同时处理的最大请求数。-->
<max_open_files>262144</max_open_files> <!--打开最大的文件数，默认最大值-->
<uncompressed_cache_size>8589934592</uncompressed_cache_size> <!--表引擎从MergeTree使用的未压缩数据的缓存大小（以字节为单位，8G）-->
<mark_cache_size>5368709120</mark_cache_size> <!--标记缓存的大小，用于MergeTree系列的表中（以字节为单位，5G）-->
<path>/var/lib/clickhouse/</path> <!--数据的目录路径-->
<storage_configuration> <!--多个存储路径-->
<disks>
<disk_name_a> <!--自定义磁盘名称 -->
<path>/var/lib/clickhouse1/</path> <!--必填项，用于指定磁盘路径 -->
<keep_free_space_bytes>1073741824</keep_free_space_bytes> <!--选填项，以字节为单位，用于定义磁盘的预留空间。-->
</disk_name_a>
<disk_name_b>
<path>/var/lib/clickhouse2/</path> <!--必填项，用于指定磁盘路径 -->
<keep_free_space_bytes>1073741824</keep_free_space_bytes> <!--选填项，以字节为单位，用于定义磁盘的预留空间。-->
</disk_name_b>
</disks>
</storage_configuration>
<tmp_path>/var/lib/clickhouse/tmp/</tmp_path> <!--用于处理大型查询的临时数据的路径。-->
<tmp_policy>tmp</tmp_policy> <!--用于存储临时文件。如果未设置，则使用tmp_path，否则将忽略它。-->
<user_files_path>/var/lib/clickhouse/user_files/</user_files_path> <!--存储用户文件的目录-->
<users_config>users.xml</users_config> <!--用户配置文件，可以配置用户访问、profiles、quota、查询等级等-->
<default_profile>default</default_profile> <!--默认设置配置文件-->
<default_database>default</default_database> <!--默认数据库-->
<timezone>Europe/Moscow</timezone> <!--服务器的时区，定为UTC时区或地理位置 莫斯科时间-->
<!-- <umask>022</umask> -->
<mlock_executable>false</mlock_executable>
<include_from>/etc/metrica.xml</include_from> <!--带替换文件的路径-->
<zookeeper incl="zookeeper-servers" optional="true" /> <!--ClickHouse与ZooKeeper群集进行交互的设置-->
<macros incl="macros" optional="true" /> <!--复制表的参数替换，如果不使用复制表，则可以省略-->
<builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval> <!--重新加载内置词典的时间间隔（以秒为单位），默认3600。可以在不重新启动服务器的情况下“即时”修改词典-->
<max_session_timeout>3600</max_session_timeout> <!-- 最大会话超时时间 单位秒-->
<default_session_timeout>60</default_session_timeout> <!--默认会话超时时间 单位秒-->
<graphite> <!--将数据发送到Graphite，它是一款企业级监控。-->
<host>localhost</host> <!--Graphite服务器-->
<port>42000</port> <!--Graphite服务器上的端口-->
<timeout>0.1</timeout> <!--发送超时时间，以秒为单位-->
<interval>60</interval> <!--发送间隔，以秒为单位-->
<root_path>one_min</root_path> <!--密钥的前缀-->
<hostname_in_path>true</hostname_in_path> <!--讲机名追加到根路径(默认= true)-->
<metrics>true</metrics> <!--从system.metrics表发送数据-->
<events>true</events> <!--从system.events表发送在该时间段内累积的增量数据-->
<events_cumulative>false</events_cumulative> <!--从system.events表发送累积数据-->
<asynchronous_metrics>true</asynchronous_metrics> <!--从system.asynchronous_metrics表发送数据-->
</graphite>
<query_log> <!--通过log_queries = 1 在用户配置表中设置，记录了ClickHouse服务中所有已经执行的查询记录。查询记录在system.query_log表中-->
<database>system</database> <!--库名-->
<table>query_log</table> <!--表名-->
<partition_by>toYYYYMM(event_date)</partition_by> <!--自定义分区键-->
<flush_interval_milliseconds>7500</flush_interval_milliseconds> <!--将数据从内存中的缓冲区刷新到表的时间间隔-->
</query_log>
<trace_log> <!--trace_log系统表操作的设置。 采样查询探查器收集的堆栈跟踪-->
<database>system</database> <!--库名-->
<table>trace_log</table> <!--表名-->
<partition_by>toYYYYMM(event_date)</partition_by> <!--自定义分区键-->
<flush_interval_milliseconds>7500</flush_interval_milliseconds> <!--将数据从内存中的缓冲区刷新到表的时间间隔-->
</trace_log>
<query_thread_log> <!--使用log_query_threads = 1设置，在用户配置表中设置 记录了所有线程的执行查询的信息-->
<database>system</database> <!--库名-->
<table>query_thread_log</table> <!--表名-->
<partition_by>toYYYYMM(event_date)</partition_by> <!--自定义分区键-->
<flush_interval_milliseconds>7500</flush_interval_milliseconds> <!--将数据从内存中的缓冲区刷新到表的时间间隔-->
</query_thread_log>
<part_log> <!--记录了MergeTree系列表引擎的分区操作日志。如添加或合并数据-->
<database>system</database> <!--库名-->
<table>part_log</table> <!--表名-->
<flush_interval_milliseconds>7500</flush_interval_milliseconds> <!--将数据从内存中的缓冲区刷新到表的时间间隔-->
</part_log>
<text_log> <!--log日志记录了ClickHouse运行过程中产生的一系列打印日志，包括INFO、DEBUG和Trace-->
<database>system</database> <!--库名-->
<table>text_log</table> <!--表名-->
<flush_interval_milliseconds>7500</flush_interval_milliseconds> <!--将数据从内存中的缓冲区刷新到表的时间间隔-->
<level></level>
</text_log>
<metric_log> <!--用于将system.metrics和system.events中的数据汇聚到一起-->
<database>system</database> <!--库名-->
<table>metric_log</table> <!--表名-->
<flush_interval_milliseconds>7500</flush_interval_milliseconds> <!--将数据从内存中的缓冲区刷新到表的时间间隔-->
<collect_interval_milliseconds>1000</collect_interval_milliseconds> <!--收集间隔-->
</metric_log>
<dictionaries_config>*_dictionary.xml</dictionaries_config> <!--外部词典的配置文件的路径，在config配置文件中指定。路径可以包含通配符*和？的绝对或则相对路径-->
<compression incl="clickhouse_compression"> <!--MergeTree引擎表的数据压缩设置。配置模板如-->
<case>
<min_part_size>10000000000</min_part_size> <!-- 数据部分的最小大小 -->
<min_part_size_ratio>0.01</min_part_size_ratio> <!-- 数据部分大小与表大小的比率 -->
<method>LZ4</method> <!--压缩算法，zstd和lz4-->
</case>
</compression>
<distributed_ddl> <!--分布式ddl配置 create drop alter -->
<path>/clickhouse/task_queue/ddl</path> <!--分布式DDL在ZooKeeper内使用的根路径为-->
</distributed_ddl>
<max_table_size_to_drop>0</max_table_size_to_drop> <!--删除表的限制，默认50G，0表示不限制。如果MergeTree表的大小超过max_table_size_to_drop（以字节为单位），则无法使用DROP查询将其删除-->
<max_partition_size_to_drop>0</max_partition_size_to_drop> <!--删除分区限制 默认50G，0表示不限制-->
<format_schema_path>/var/lib/clickhouse/format_schemas/</format_schema_path> <!--包含输入格式文件的目录路径-->
<query_masking_rules> <!--基于Regexp的规则，应用于查询以及所有日志消息。再其存储在服务器日志中，system.query_log，system.text_log，system.processes表以及以日志形式发送给客户端。这样可以防止SQL查询中的敏感数据泄漏记录到日志中-->
<rule>
<name>hide SSN</name> <!--规则名称-->
<regexp>\b\d{3}-\d{2}-\d{4}\b</regexp> <!--正则表达式-->
<replace>000-00-0000</replace> <!--替换，敏感数据的替换字符串（默认为可选-六个星号）-->
</rule>
</query_masking_rules>
<disable_internal_dns_cache>1</disable_internal_dns_cache> <!--禁用内部DNS缓存，默认0-->
</yandex>
9.users.xml配置详解
<?xml version="1.0"?>
<yandex>
<profiles>
<default>
<max_memory_usage>10000000000</max_memory_usage> <!--单个查询最大内存使用量-->
<max_memory_usage_for_user>0</max_memory_usage_for_user> <!--在单个ClickHouse服务进程中，以用户为单位进行统计，单个用户在运行查询时，限制使用的最大内存用量，默认值为0，即不做限制-->
<max_memory_usage_for_all_queries>0</max_memory_usage_for_all_queries><!--在单个ClickHouse服务进程中，所有运行的查询累加在一起，限制使用的最大内存用量，默认为0不做限制-->
<max_partitions_per_insert_block>100</max_partitions_per_insert_block><!--在单次INSERT写入的时候，限制创建的最大分区个数，默认值为100个。如果超出这个阈值数目，将会得到异常-->
<max_rows_to_group_by>0</max_rows_to_group_by> <!--在执行GROUP BY聚合查询的时候，限制去重后的聚合KEY的最大个数，默认值为0，即不做限制-->
<max_bytes_before_external_group_by>0</max_bytes_before_external_group_by> <!--在执行GROUP BY聚合查询的时候，限制使用的最大内存用量，默认值为0，即不做限制-->
<use_uncompressed_cache>0</use_uncompressed_cache> <!--是否使用未压缩块的缓存。接受0或1。默认情况下，0（禁用）-->
<load_balancing>random</load_balancing> <!--指定用于分布式查询处理的副本选择算法 默认：Random
random是默认的负载均衡算法，在ClickHouse的服务节点中，拥有一个全局计数器errors_count，当服务发生任何异常时，该计数累积加1。而random算法会选择errors_count错误数量最少的replica，如果多个replica的errors_count计数相同，则在它们之中随机选择一个
nearest_hostname可以看作random算法的变种，首先它会选择errors_count错误数量最少的replica，如果多个replica的errors_count计数相同，则选择集群配置中host名称与当前host最相似的一个
in_order同样可以看作random算法的变种，首先它会选择errors_count错误数量最少的replica，如果多个replica的errors_count计数相同，则按照集群配置中replica的定义顺序逐个选择。
first_or_random可以看作in_order算法的变种，首先它会选择errors_count错误数量最少的replica，如果多个replica的errors_count计数相同，它首先会选择集群配置中第一个定义的replica，如果该replica不可用，则进一步随机选择一个其他的replica。
-->
<log_queries> 1</log_queries> <!--用户配置开启query_log查询日志，记录了ClickHouse服务中所有已经执行的查询记录-->
<log_query_threads> 1</log_query_threads> <!--用户配置开启query_thread_log日志 记录了所有线程的执行查询的信息-->
<part_log>1</part_log> <!--用户配置开启part_log日志,记录了MergeTree系列表引擎的分区操作日志-->
<text_log>1</text_log> <!--用户配置开启text_log日志 记录了ClickHouse运行过程中产生的一系列打印日志，包括INFO、DEBUG和Trace-->
<metric_log>1</metric_log> <!--用户配置开启metric_log日志 用于将system.metrics和system.events中的数据汇聚到一起-->
</default>
<readonly> <!--读权限、写权限和设置权限 readonly:用户名-->
<readonly>0</readonly> <!--不进行任何限制（默认值）-->
<readonly>1</readonly> <!--只拥有读权限（只能执行SELECT、EXISTS、SHOW和DESCRIBE）-->
<readonly>2</readonly> <!--拥有读权限和设置权限（在读权限基础上，增加了SET查询）-->
<allow_ddl>0</allow_ddl> <!--·当取值为0时，不允许DDL查询 create drop alter-->
<allow_ddl>1</allow_ddl> <!--·当取值为1时，允许DDL查询（默认值）-->
</readonly>
</profiles>
<users> <!--用户配置-->
<default> <!--用户名-->
<password></password> <!--置登录密码 免密码登入，支持明文、SHA256加密和double_sha1加密三种形式-->
<password>123</password> <!--明文密码-->
<password_sha256_hex>a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3</password_sha256_hex> <!--SHA256加密 # echo -n 123 | openssl dgst -sha256-->
<password_double_sha1_hex>23ae809ddacaf96af0fd78ed04b6a265e05aa257</password_double_sha1_hex> <!--double_sha1加密 # echo -n 123 | openssl dgst -sha1 -binary | openssl dgst -sha1-->
<networks incl="networks" replace="replace"> <!--限制IP访问-->
<ip>::/0</ip> --允许任何地址访问
<ip>127.0.0.1</ip> --允许本地访问
<ip>192.168.107.216</ip> --允许该IP访问
</networks>
<allow_databases> <!--限制访问数据库 不指定表示不限制-->
<database>default</database> <!--库名称-->
<database>test_dictionaries</database>
</allow_databases>
<databases> <!--限制访问表的数据 不指定表示不限制-->
<database_name><!--数据库名称-->
<table_name><!--表名称-->
<filter> id < 10</filter><!--数据过滤条件-->
</table_name>
</database_name>
</databases>
<profile>default</profile>
<quota>default</quota>
</default>
</users>
<quotas> <!--限制资源被过度使用，当使用的资源数量达到阈值时，那么正在进行的操作会被自动中断-->
<default> <!--自定义名称，全局唯一-->
<interval> <!--配置时间间隔，每个时间内的资源消耗限制-->
<duration>3600</duration> <!--表示累积的时间周期，单位是秒-->
<queries>0</queries> <!--表示在周期内允许执行的查询次数，0表示不限制-->
<errors>0</errors> <!--表示在周期内允许发生异常的次数，0表示不限制-->
<result_rows>0</result_rows> <!--表示在周期内允许查询返回的结果行数，0表示不限制-->
<read_rows>0</read_rows> <!--表示在周期内在分布式查询中，允许远端节点读取的数据行数，0表示不限制。-->
<execution_time>0</execution_time> <!--表示周期内允许执行的查询时间，单位是秒，0表示不限制。-->
</interval>
</default>
</quotas>
</yandex>
10.metrika.xml详解
vim /etc/metrika.xml
<yandex>
<clickhouse_remote_servers> <!-- 集群设置 -->
<shunwang> <!-- clickhouse显示名称 可以自己修改 -->
<shard> <!-- 一分片 -->
<weight>1</weight> <!--分片权重-->
<internal_replication>false</internal_replication> <!-- 是否开启自动复制 -->
<replica> <!-- 副本 -->
<host>192.168.104.91</host> <!--指定部署了ClickHouse节点的服务器地址-->
<port>9000</port> <!--指定ClickHouse服务的TCP端口-->
<user>default</user> <!--ClickHouse用户，默认为default 选填-->
<password></password> <!--ClickHouse的用户密码，默认为空字符串 选填-->
<secure>9440</secure> <!--SSL连接的端口，默认为9440 选填-->
<compression>true</compression> <!--是否开启数据压缩功能，默认为true 选填-->
</replica>
<replica> <!-- 副本 -->
<host>192.168.104.92</host> <!--指定部署了ClickHouse节点的服务器地址-->
<port>9000</port> <!--指定ClickHouse服务的TCP端口-->
<user>default</user> <!--ClickHouse用户，默认为default 选填-->
<password></password> <!--ClickHouse的用户密码，默认为空字符串 选填-->
<secure>9440</secure> <!--SSL连接的端口，默认为9440 选填-->
<compression>true</compression> <!--是否开启数据压缩功能，默认为true 选填-->
</replica>
</shard>
<shard>
<weight>1</weight> <!--分片权重-->
<internal_replication>false</internal_replication> <!-- 是否开启自动复制 -->
<replica> <!-- 副本 -->
<host>192.168.104.93</host> <!--指定部署了ClickHouse节点的服务器地址-->
<port>9000</port> <!--指定ClickHouse服务的TCP端口-->
<user>default</user> <!--ClickHouse用户，默认为default 选填-->
<password></password> <!--ClickHouse的用户密码，默认为空字符串 选填-->
<secure>9440</secure> <!--SSL连接的端口，默认为9440 选填-->
<compression>true</compression> <!--是否开启数据压缩功能，默认为true 选填-->
</replica>
<replica> <!-- 副本 -->
<host>192.168.104.94</host> <!--指定部署了ClickHouse节点的服务器地址-->
<port>9000</port> <!--指定ClickHouse服务的TCP端口-->
<user>default</user> <!--ClickHouse用户，默认为default 选填-->
<password></password> <!--ClickHouse的用户密码，默认为空字符串 选填-->
<secure>9440</secure> <!--SSL连接的端口，默认为9440 选填-->
<compression>true</compression> <!--是否开启数据压缩功能，默认为true 选填-->
</replica>
</shard>
</shunwang>
</clickhouse_remote_servers>
<macros>
<shard>01</shard> <!--分片编号不能重复 如果是副本要相同-->
<replica>192.168.104.91</replica> <!--副本名称，创建复制表时有用，每个节点不同，整个集群唯一，建议使用主机名-->
</macros>
<networks>
<ip>::/0</ip>
</networks>
<!-- zookeeper 配置 集群高可用 -->
<zookeeper-servers>
<node index="1">
<host>192.168.104.91</host>
<port>2181</port>
</node>
<node index="2">
<host>192.168.104.92</host>
<port>2181</port>
</node>
<node index="3">
<host>192.168.104.93</host>
<port>2181</port>
</node>
<session_timeout_ms>30000</session_timeout_ms> <!--客户端会话的最大超时（以毫秒为单位）-->
<operation_timeout_ms>10000</operation_timeout_ms>
<root>/path/to/zookeeper/node</root> <!--用作ClickHouse服务器使用的znode的根的znode-->
<identity>user:password</identity> <!--用户和密码，ZooKeeper可能需要这些用户和密码才能访问请求的znode-->
</zookeeper-servers>
<clickhouse_compression> <!--MergeTree引擎表的数据压缩设置。配置模板如-->
<case>
<min_part_size>10000000000</min_part_size> <!-- 数据部分的最小大小 -->
<min_part_size_ratio>0.01</min_part_size_ratio> <!-- 数据部分大小与表大小的比率 -->
<method>LZ4</method> <!--压缩算法，zstd和lz4-->
</case>
</clickhouse_compression>
</yandex>
11.其他配置文件
（1）/etc/security/limits.d/clickhouse.conf：文件句柄数量的配置，默认值如下所示。该配置也可以通过config.xml的max_open_files修改。
# cat /etc/security/limits.d/clickhouse.conf
clickhouse soft nofile 262144
clickhouse hard nofile 262144
（2）/etc/cron.d/clickhouse-server：cron定时任务配置，用于恢复因异常原因中断的ClickHouse服务进程，其默认的配置如下。
# cat /etc/cron.d/clickhouse-server
# */10 * * * * root (which service > /dev/null 2>&1 && (service clickhouse-server condstart ||:)) || /etc/init.d/clickhouse-server condstart > /dev/null 2>&1
12.启动clickhouse
service clickhouse-server start # 启动server端clickhouse-server 单机版clickhouse部署完成
service clickhouse-server stop # 停止server端clickhouse-server
service clickhouse-server restart # 重启server端clickhouse-server
service clickhouse-server status # 查看server端服务开启/关闭状态
clickhouse-client --multiline -h 192.168.104.91 --port 9000 -u ck --password shunwang
# multiline:可以同时执行多条语句默认 ; 结束
# -h:ip
# --port:端口
# -u:账号
# --password:密码
# --database/-d：登录的数据库，默认值为default
# --time/-t：在非交互式执行时，会打印每条SQL的执行时间
# --query/-q：只能在非交互式查询时使用，用于指定SQL语句 --query="SELECT 1;SELECT 2;"
select * from system.clusters # 查看是否成功 显示下面信息 集群搭建成功
第3章 数据定义
3.1 ClickHouse的数据类型
3.1.1 基础类型
基础类型只有数值、字符串和时间三种类型，没有Boolean类型，但可以使用整型的0或1替代。
1.数值类型
数值类型分为整数、浮点数和定点数三类
1）Int
Int8、Int16、Int32和Int64指代4种大小的Int类型，其末尾的数字正好表明了占用字节的大小（8位=1字节）
ClickHouse支持无符号的整数，使用前缀U表示
2）Float
ClickHouse直接使用Float32和Float64代表单精度浮点数以及双精度浮点数
:) SELECT toFloat32('0.12345678901234567890') as a , toTypeName(a)
┌──────a─┬─toTypeName(toFloat32('0.12345678901234567890'))─┐
│ 0.12345679 │ Float32 │
└────────┴───────────────────────────────┘
:) SELECT toFloat64('0.12345678901234567890') as a , toTypeName(a)
┌────────────a─┬─toTypeName(toFloat64('0.12345678901234567890'))─┐
│ 0.12345678901234568 │ Float64 │
└─────────────┴──────────────────────────────┘
3）Decimal
如果要求更高精度的数值运算，则需要使用定点数。ClickHouse提供了Decimal32、Decimal64和Decimal128三种精度的定点数。可以通过两种形式声明定点：简写方式有Decimal32(S)、Decimal64(S)、Decimal128(S)三种，原生方式为Decimal(P,S)，其中：
·P代表精度，决定总位数（整数部分+小数部分），取值范围是1～38；
·S代表规模，决定小数位数，取值范围是0～P。
在进行加法运算时，S取最大值。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相加后S=4：
:) SELECT toDecimal64(2,4) + toDecimal32(2,2)
┌─plus(toDecimal64(2, 4), toDecimal32(2, 2))─┐
│ 4.0000 │
└───────────────────────────┘
在进行减法运算时，其规则与加法运算相同，S同样会取最大值。例如toDecimal32(4,4)与toDecimal64(2,2)相减后S=4：
:) SELECT toDecimal32(4,4) - toDecimal64(2,2)
┌─minus(toDecimal32(4, 4), toDecimal64(2, 2))┐
│ 2.0000 │
└────────────────────────────┘
在进行乘法运算时，S取两者S之和。例如下面的查询，toDecimal64(2,4)与toDecimal32(2,2)相乘后S=4+2=6：
:) SELECT toDecimal64(2,4) * toDecimal32(2,2)
┌─multiply(toDecimal64(2, 4), toDecimal32(2, 2))┐
│ 4.000000 │
└─────────────────────────────┘
在进行除法运算时，S取被除数的值，此时要求被除数S必须大于除数S，否则会报错。例如toDecimal64(2,4)与toDecimal32(2,2)相除后S=4：
:) SELECT toDecimal64(2,4) / toDecimal32(2,2)
┌─divide(toDecimal64(2, 4), toDecimal32(2, 2))┐
│ 1.0000 │
└───────────────────────────┘
2.字符串类型
1）String
字符串由String定义，长度不限。因此在使用String的时候无须声明大小。
2）FixedString
FixedString类型和传统意义上的Char类型有些类似，对于一些字符有明确长度的场合，可以使用固定长度的字符串。定长字符串通过FixedString(N)声明，其中N表示字符串长度。FixedString使用null字节填充末尾字符
:) SELECT toFixedString('abc',5) , LENGTH(toFixedString('abc',5)) AS LENGTH
┌─toFixedString('abc', 5)─┬─LENGTH─┐
│ abc │ 5 │
└────────────────┴──────┘
3）UUID
UUID是一种数据库常见的主键类型，在ClickHouse中直接把它作为一种数据类型。UUID共有32位，它的格式为8-4-4-4-12。如果一个UUID类型的字段在写入数据时没有被赋值，则会依照格式使用0填充，例如：
CREATE TABLE UUID_TEST (
c1 UUID,
c2 String
) ENGINE = Memory;
--第一行UUID有值
INSERT INTO UUID_TEST SELECT generateUUIDv4(),'t1'
--第二行UUID没有值
INSERT INTO UUID_TEST(c2) VALUES('t2')
:) SELECT * FROM UUID_TEST
┌─────────────────────c1─┬─c2─┐
│ f36c709e-1b73-4370-a703-f486bdd22749 │ t1 │
└───────────────────────┴────┘
┌─────────────────────c1─┬─c2─┐
│ 00000000-0000-0000-0000-000000000000 │ t2 │
└───────────────────────┴────┘
3.时间类型
时间类型分为DateTime、DateTime64和Date三类。ClickHouse目前没有时间戳类型。时间类型最高的精度是秒，也就是说，如果需要处理毫秒、微秒等大于秒分辨率的时间，则只能借助UInt类型实现。
1）DateTime
DateTime类型包含时、分、秒信息，精确到秒，支持使用字符串形式写入：
CREATE TABLE Datetime_TEST (
c1 Datetime
) ENGINE = Memory
--以字符串形式写入
INSERT INTO Datetime_TEST VALUES('2019-06-22 00:00:00')
SELECT c1, toTypeName(c1) FROM Datetime_TEST
┌──────────c1─┬─toTypeName(c1)─┐
│ 2019-06-22 00:00:00 │ DateTime │
└─────────────┴───────────┘
2）DateTime64
DateTime64可以记录亚秒，它在DateTime之上增加了精度的设置，例如：
CREATE TABLE Datetime64_TEST (
c1 Datetime64(2)
) ENGINE = Memory
--以字符串形式写入
INSERT INTO Datetime64_TEST VALUES('2019-06-22 00:00:00')
SELECT c1, toTypeName(c1) FROM Datetime64_TEST
┌─────────────c1─┬─toTypeName(c1)─┐
│ 2019-06-22 00:00:00.00 │ DateTime │
└───────────────┴──────────┘
3）Date
Date类型不包含具体的时间信息，只精确到天，它同样也支持字符串形式写入：
CREATE TABLE Date_TEST (
c1 Date
) ENGINE = Memory
--以字符串形式写入
INSERT INTO Date_TEST VALUES('2019-06-22')
SELECT c1, toTypeName(c1) FROM Date_TEST
┌─────────c1─┬─toTypeName(c1)─┐
│ 2019-06-22 │ Date │
└───────────┴──────────┘
3.1.2 复合类型
除了基础数据类型之外，ClickHouse还提供了数组、元组、枚举和嵌套四类复合类型。这些类型通常是其他数据库原生不具备的特性。拥有了复合类型之后，ClickHouse的数据模型表达能力更强了。
1.Array
数组有两种定义形式，常规方式array(T)：
SELECT array(1, 2) as a , toTypeName(a)
┌─a───┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8) │
└─────┴────────────────┘
或者简写方式[T]：
SELECT [1, 2]
在定义表字段时，数组需要指定明确的元素类型
CREATE TABLE Array_TEST (
c1 Array(String)
) engine = Memory
2.Tuple
元组类型由1～n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。
SELECT tuple(1,'a',now()) AS x, toTypeName(x)
┌─x─────────────────┬─toTypeName(tuple(1, 'a', now()))─┐
│ (1,'a','2019-08-28 21:36:32') │ Tuple(UInt8, String, DateTime) │
└───────────────────┴─────────────────────┘
或者简写方式（T）：
SELECT (1,2.0,null) AS x, toTypeName(x)
┌─x──────┬─toTypeName(tuple(1, 2., NULL))───────┐
│ (1,2,NULL) │ Tuple(UInt8, Float64, Nullable(Nothing)) │
└───────┴──────────────────────────┘
在定义表字段时，元组也需要指定明确的元素类型：
CREATE TABLE Tuple_TEST (
c1 Tuple(String,Int8)
) ENGINE = Memory;
3.Enum
ClickHouse支持枚举类型，这是一种在定义常量时经常会使用的数据类型。ClickHouse提供了Enum8和Enum16两种枚举类型，它们除了取值范围不同之外，别无二致。枚举固定使用(String:Int)Key/Value键值对的形式定义数据，所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16)
CREATE TABLE Enum_TEST (
c1 Enum8('ready' = 1, 'start' = 2, 'success' = 3, 'error' = 4)
) ENGINE = Memory;
在定义枚举集合的时候，有几点需要注意。首先，Key和Value是不允许重复的，要保证唯一性。其次，Key和Value的值都不能为Null，但Key允许是空字符串。在写入枚举数据的时候，只会用到Key字符串部分，但是在后续对枚举的所有操作中（包括排序、分组、去重、过滤等），会使用Int类型的Value值。
INSERT INTO Enum_TEST VALUES('ready');
INSERT INTO Enum_TEST VALUES('start');
4.Nested
嵌套类型，顾名思义是一种嵌套表结构。一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型
CREATE TABLE nested_test (
name String,
age UInt8 ,
dept Nested(
id UInt8,
name String
)
) ENGINE = Memory;
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001,10002], ['研发部','技术支持中心','测试部']);
--行与行之间,数组长度无须对齐
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心']);
在访问嵌套类型的数据时需要使用点符号，例如：
SELECT name, dept.id, dept.name FROM nested_test
┌─name─┬─dept.id──┬─dept.name─────────────┐
│ bruce │ [16,17,18] │ ['研发部','技术支持中心','测试部'] │
└────┴───────┴────────────────────┘
4.1.3 特殊类型
1.Nullable
准确来说，Nullable并不能算是一种独立的数据类型，它更像是一种辅助的修饰符，需要与基础数据类型一起搭配使用。Nullable类型与Java8的Optional对象有些相似，它表示某个基础数据类型可以是Null值。其具体用法如下所示：
CREATE TABLE Null_TEST (
c1 String,
c2 Nullable(UInt8)
) ENGINE = TinyLog;
通过Nullable修饰后c2字段可以被写入Null值：
INSERT INTO Null_TEST VALUES ('nauu',null)
INSERT INTO Null_TEST VALUES ('bruce',20)
SELECT c1 , c2 ,toTypeName(c2) FROM Null_TEST
┌─c1───┬───c2─┬─toTypeName(c2)─┐
│ nauu │ NULL │ Nullable(UInt8) │
│ bruce │ 20 │ Nullable(UInt8) │
└─────┴──────┴───────────┘
在使用Nullable类型的时候还有两点值得注意：首先，它只能和基础类型搭配使用，不能用于数组和元组这些复合类型，也不能作为索引字段；其次，应该慎用Nullable类型，包括Nullable的数据表，不然会使查询和写入性能变慢。因为在正常情况下，每个列字段的数据会被存储在对应的[Column].bin文件中。如果一个列字段被Nullable类型修饰后，会额外生成一个[Column].null.bin文件专门保存它的Null值。这意味着在读取和写入数据时，需要一倍的额外文件操作。
2.Domain
域名类型分为IPv4和IPv6两类，本质上它们是对整型和字符串的进一步封装。IPv4类型是基于UInt32封装的，它的具体用法如下所示：
CREATE TABLE IP4_TEST (
url String,
ip IPv4
) ENGINE = Memory;
INSERT INTO IP4_TEST VALUES ('www.nauu.com','192.0.0.0')
SELECT url , ip ,toTypeName(ip) FROM IP4_TEST
┌─url──────┬─────ip─┬─toTypeName(ip)─┐
│ www.nauu.com │ 192.0.0.0 │ IPv4 │
└────────┴───────┴──────────┘
（1）出于便捷性的考量，例如IPv4类型支持格式检查，格式错误的IP数据是无法被写入的，例如：
INSERT INTO IP4_TEST VALUES ('www.nauu.com','192.0.0')
Code: 441. DB::Exception: Invalid IPv4 value.
（2）出于性能的考量，同样以IPv4为例，IPv4使用UInt32存储，相比String更加紧凑，占用的空间更小，查询性能更快。IPv6类型是基于FixedString(16)封装的，它的使用方法与IPv4别无二致
3.2 如何定义数据表
3.2.1 数据库
创建数据库的完整语法如下所示：
CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]
IF NOT EXISTS表示如果已经存在一个同名的数据库，则会忽略后续的创建过程；
[ENGINE=engine]表示数据库所使用的引擎类型（是的，你没看错，数据库也支持设置引擎）。
数据库目前一共支持5种引擎，如下所示。
·Ordinary：默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。
·Dictionary：字典引擎，此类数据库会自动为所有数据字典创建它们的数据表
·Memory：内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除。
·Lazy：日志引擎，此类数据库下只能使用Log系列的表引擎
·MySQL：MySQL引擎，此类数据库下会自动拉取远端MySQL中的数据，并为它们创建MySQL表引擎的数据表
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', 'database' , 'user', 'password')
--host:port — 链接的MySQL地址。
--database — 链接的MySQL数据库。
--user — 链接的MySQL用户。
--password — 链接的MySQL用户密码。
SHOW databases; -- 显示数据库
use cloud_joy_yun; -- 进入数据库
SHOW tables; -- 显示表
DROP DATABASE [IF EXISTS] db_name -- 删除数据库
3.2.2 数据表
第一种是常规定义方法，它的完整语法如下所示：
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT] [COMMENT],
name2 [type] [DEFAULT] [COMMENT],
省略…
) ENGINE = engine
CREATE TABLE hits_v1 (
Title String,
URL String ,
EventTime DateTime
) ENGINE = Memory;
第二种定义方法是复制其他表的结构，具体语法如下所示：
CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name AS [db_name2.] table_name2 [ENGINE = engine]
--创建新的数据库
CREATE DATABASE IF NOT EXISTS new_db
--将default.hits_v1的结构复制到new_db.hits_v1
CREATE TABLE IF NOT EXISTS new_db.hits_v1 AS default.hits_v1 ENGINE = TinyLog
上述语句将会把default.hits_v1的表结构原样复制到new_db.hits_v1，并且ENGINE表引擎可以与原表不同。
第三种定义方法是通过SELECT子句的形式创建，它的完整语法如下：
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ENGINE = engine AS SELECT …
CREATE TABLE IF NOT EXISTS hits_v1_1 ENGINE = Memory AS SELECT * FROM hits_v1 --表结构和数据都会在
DROP TABLE [IF EXISTS] [db_name.]table_name
3.2.3 临时表
ClickHouse也有临时表的概念，创建临时表的方法是在普通表的基础之上添加temporary关键字，它的完整语法如下所示：
CREATE temporary TABLE [IF NOT EXISTS] table_name (
name1 [type] [DEFAULT] [COMMENT],
name2 [type] [DEFAULT] [COMMENT],
)
相比普通表而言，临时表有如下两点特殊之处：
- 它的生命周期是会话绑定的，所以它只支持Memory表引擎，如果会话结束，数据表就会被销毁；
- 临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数。
- 临时表和普通可以同时存在,优先级临时表大于普通表
3.2.4 视图
ClickHouse拥有普通和物化两种视图，其中物化视图拥有独立的存储，而普通视图只是一层简单的查询代理。创建普通视图的完整语法如下所示：
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...
物化视图支持表引擎，数据保存形式由它的表引擎决定，创建物化视图的完整语法如下所示：
CREATE [materialized] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...
物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。物化视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据仍会保留。
3.2.5 追加新字段
假如需要对一张数据表追加新的字段，可以使用如下语法：
ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after]
例如，在数据表的末尾增加新字段：
ALTER TABLE testcol_v1 ADD COLUMN OS String DEFAULT 'mac'
或是通过AFTER修饰符，在指定字段的后面增加新字段：
ALTER TABLE testcol_v1 ADD COLUMN IP String AFTER ID
对于数据表中已经存在的旧数据而言，新追加的字段会使用默认值补全。
3.2.6 修改数据类型
如果需要改变表字段的数据类型或者默认值，需要使用下面的语法：
ALTER TABLE tb_name modify COLUMN [IF EXISTS] name [type] [default_expr]
ALTER TABLE testcol_v1 modify COLUMN IP IPv4 --将String类型的IP字段修改为IPv4类型是可行的
3.2.7 修改备注
做好信息备注是保持良好编程习惯的美德之一，所以如果你还没有为列字段添加备注信息，那么就赶紧行动吧。追加备注的语法如下所示：
ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name 'some comment'
ALTER TABLE testcol_v1 COMMENT COLUMN ID '主键ID' --为ID字段增加备注：
DESC testcol_v1 --使用DESC查询可以看到上述增加备注的操作已经生效：
┌─name─────┬─type──┬─comment─┐
│ ID │ String │ 主键ID │
└─────────┴─────┴──────┘
3.2.8 删除已有字段
假如要删除某个字段，可以使用下面的语句：
ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name
ALTER TABLE testcol_v1 DROP COLUMN URL --执行下面的语句删除URL字段
3.2.9 移动数据表
在Linux系统中，mv命令的本意是将一个文件从原始位置A移动到目标位置B，但是如果位置A与位置B相同，则可以变相实现重命名的作用。ClickHouse的RENAME查询就与之有着异曲同工之妙，RENAME语句的完整语法如下所示：
RENAME TABLE [db_name11.]tb_name11 TO [db_name12.]tb_name12, [db_name21.]tb_name21 TO [db_name22.]tb_name22, ...
RENAME TABLE default.testcol_v1 TO db_test.testcol_v2 --testcol_v1从default默认数据库被移动到了db_test数据库，同时数据表被重命名为testcol_v2：
3.2.10 清空数据表
假设需要将表内的数据全部清空，而不是直接删除这张表，则可以使用TRUNCATE语句，它的完整语法如下所示：
TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name
TRUNCATE TABLE db_test.testcol_v2 --将db_test.testcol_v2的数据一次性清空
3.3 数据分区的基本操作
目前只有MergeTree系列的表引擎支持数据分区。
3.3.1 查询分区信息
SELECT partition_id,name,table,database FROM system.parts WHERE table = 'partition_v2'
┌─partition_id─┬─name───────┬─table─────┬─database┐
│ 201905 │ 201905_1_1_0_6 │ partition_v2 │ default │
│ 201910 │ 201910_3_3_0_6 │ partition_v2 │ default │
│ 201911 │ 201911_4_4_0_6 │ partition_v2 │ default │
│ 201912 │ 201912_5_5_0_6 │ partition_v2 │ default │
└──────────┴──────────┴─────────┴──────┘
3.3.2 删除指定分区
ALTER TABLE tb_name DROP PARTITION partition_expr --删除一个指定分区
ALTER TABLE partition_v2 DROP PARTITION 201907 --假如现在需要更新partition_v2数据表整个7月份的数据，则可以先将7月份的分区删除：
INSERT INTO partition_v2 VALUES ('A004-update','www.bruce.com', '2019-07-02'),… --然后将整个7月份的新数据重新写入，就可以达到更新的目的：
3.3.3 复制分区数据
ClickHouse支持将A表的分区数据复制到B表，这项特性可以用于快速数据写入、多表间数据同步和备份等场景，它的完整语法如下：
ALTER TABLE B REPLACE PARTITION partition_expr FROM A
不过需要注意的是，并不是任意数据表之间都能够相互复制，它们还需要满足两个前提条件：
- ·两张表需要拥有相同的分区键；
- ·它们的表结构完全相同。
假设数据表partition_v2与先前的partition_v1分区键和表结构完全相同，那么应先在partition_v1中写入一批8月份的新数据：
INSERT INTO partition_v1 VALUES ('A006-v1','www.v1.com', '2019-08-05'),('A007-v1','www.v1.com', '2019-08-20')
-- 再执行下面的语句：
ALTER TABLE partition_v2 REPLACE PARTITION 201908 FROM partition_v1
--即能够将partition_v1的整个201908分区中的数据复制到partition_v2：
SELECT * from partition_v2 ORDER BY EventTime
┌─ID───────┬─URL──────┬─EventTime─┐
│ A000 │ www.nauu.com │ 2019-05-01 │
│ A001 │ www.nauu.com │ 2019-05-02 │
省略…
│ A004-update │ www.bruce.com │ 2019-07-02 │
│ A006-v1 │ www.v1.com │ 2019-08-05 │
│ A007-v1 │ www.v1.com │ 2019-08-20 │
└─────────┴─────────┴───────┘
3.3.4 重置分区数据
如果数据表某一列的数据有误，需要将其重置为初始值，此时可以使用下面的语句实现：
ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr
对于默认值的含义，笔者遵循如下原则：如果声明了默认值表达式，则以表达式为准；否则以相应数据类型的默认值为准。例如，执行下面的语句会重置partition_v2表内201908分区的URL数据重置。
ALTER TABLE partition_v2 CLEAR COLUMN URL in PARTITION 201908
查验数据后会发现，URL字段已成功被全部重置为空字符串了（String类型的默认值）。
SELECT * from partition_v2
┌─ID────┬─URL─┬──EventTime┐
│ A006-v1 │ │ 2019-08-05 │
│ A007-v1 │ │ 2019-08-20 │
└──────┴────┴────────┘
3.3.5 卸载与装载分区
表分区可以通过DETACH语句卸载，分区被卸载后，它的物理数据并没有删除，而是被转移到了当前数据表目录的子目录下。而装载分区则是反向操作，它能够将子目录下的某个分区重新装载回去。卸载与装载这一对伴生的操作，常用于分区数据的迁移和备份场景。卸载某个分区的语法如下所示：
ALTER TABLE tb_name DETACH PARTITION partition_expr
ALTER TABLE partition_v2 DETACH PARTITION 201908 --例如，执行下面的语句能够将partition_v2表内整个8月份的分区卸载：
此时再次查询这张表，会发现其中2019年8月份的数据已经没有了。而进入partition_v2的磁盘目录，则可以看到被卸载的分区目录已经被移动到了detached目录中：
# pwd
/chbase/data/data/default/partition_v2/detached
# ll
total 4
drwxr-x---. 2 clickhouse clickhouse 4096 Aug 31 23:16 201908_4_4_0
记住，一旦分区被移动到了detached子目录，就代表它已经脱离了ClickHouse的管理，ClickHouse并不会主动清理这些文件。这些分区文件会一直存在，除非我们主动删除或者使用ATTACH语句重新装载它们。装载某个分区的完整语法如下所示：
ALTER TABLE tb_name attach PARTITION partition_expr
ALTER TABLE partition_v2 attach PARTITION 201908 --再次执行下面的语句，就可以将刚才已被卸载的201908分区重新装载回去：
3.4 分布式DDL执行
将一条普通的DDL语句转换成分布式执行十分简单，只需加上ON CLUSTER cluster_name声明即可。例如，执行下面的语句后将会对ch_cluster集群内的所有节点广播这条DDL语句：
CREATE TABLE partition_v3 ON cluster ch_cluster(
ID String,
URL String,
EventTime Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID
3.5 数据的写入
第一种是使用VALUES格式的常规语法：第二种是使用指定格式的语法：
INSERT INTO [db.]table [(c1, c2, c3…)] FORMAT format_name data_set
INSERT INTO partition_v2 FORMAT CSV \
'A0017','www.nauu.com', '2019-10-01' \
'A0018','www.nauu.com', '2019-10-01' -- CSV格式写入为例：
第三种是使用SELECT子句形式的语法：
INSERT INTO [db.]table [(c1, c2, c3…)] SELECT ...
INSERT INTO partition_v2 SELECT * FROM partition_v1 --通过SELECT子句可将查询结果写入数据表，假设需要将partition_v1的数据写入partition_v2，则可以使用下面的语句：
3.6 数据的删除与修改
DELETE语句的完整语法如下所示：
ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr
ALTER TABLE partition_v2 DELETE WHERE ID = 'A003' --删除partition_v2表内所有ID等于A003的数据：
数据修改除了需要指定具体的列字段之外，整个逻辑与数据删除别无二致，它的完整语法如下所示：
ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr
ALTER TABLE partition_v2 UPDATE URL = 'www.wayne.com',OS = 'mac' WHERE ID IN (SELECT ID FROM partition_v2 WHERE EventTime = '2019-06-01') --根据WHERE条件同时修改partition_v2内的URL和OS字段：
3.7 表的查询
SELECT WatchID FROM hits_v1 --从数据表中取数
SELECT MAX_WatchID FROM (SELECT MAX(WatchID) AS MAX_WatchID FROM hits_v1) --从子查询中取数
第4章 MergeTree原理解析
属合并树（MergeTree）表引擎及其家族系列（*MergeTree）最为强大，在生产环境的绝大部分场景中，都会使用此系列的表引擎。只有合并树系列的表引擎才支持主键索引、数据分区、数据副本和数据采样这些特性，同时也只有此系列的表引擎支持ALTER相关操作。
合并树家族自身也拥有多种表引擎的变种。其中MergeTree作为家族中最基础的表引擎，提供了主键索引、数据分区、数据副本和数据采样等基本能力，而家族中其他的表引擎则在MergeTree的基础之上各有所长。例如ReplacingMergeTree表引擎具有删除重复数据的特性，而SummingMergeTree表引擎则会按照排序键自动聚合数据。如果给合并树系列的表引擎加上Replicated前缀，又会得到一组支持数据副本的表引擎，例如ReplicatedMergeTree、ReplicatedReplacingMergeTree、ReplicatedSummingMergeTree等。合并树表引擎家族如图6-1所示。
4.1 MergeTree的创建方式与存储结构
MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改。为了避免片段过多，ClickHouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合成一个新的片段。这种数据片段往复合并的特点，也正是合并树名称的由来。
4.1.1 MergeTree的创建方式
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
省略...
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, 省略...]
-
（1）PARTITION BY [选填]：分区键，用于指定表数据以何种标准进行分区。分区键既可以是单个列字段，也可以通过元组的形式使用多个列字段，同时它也支持使用列表达式。如果不声明分区键，则ClickHouse会生成一个名为all的分区
-
（2）ORDER BY [必填]：排序键，用于指定在一个数据片段内，数据以何种标准排序。默认情况下主键（PRIMARY KEY）与排序键相同。排序键既可以是单个列字段，例如ORDER BY CounterID，也可以通过元组的形式使用多个列字段，例如ORDER BY（CounterID,EventDate）。当使用多个列字段排序时，以ORDER BY（CounterID,EventDate）为例，在单个数据片段内，数据首先会以CounterID排序，相同CounterID的数据再按EventDate排序。
-
（3）PRIMARY KEY [选填]：主键，顾名思义，声明后会依照主键字段生成一级索引，用于加速表查询。默认情况下，主键与排序键(ORDER BY)相同，所以通常直接使用ORDER BY代为指定主键，无须刻意通过PRIMARY KEY声明。所以在一般情况下，在单个数据片段内，数据与一级索引以相同的规则升序排列。与其他数据库不同，MergeTree主键允许存在重复数据（ReplacingMergeTree可以去重）。
-
（4）sample BY [选填]：抽样表达式，用于声明数据以何种标准进行采样。如果使用了此配置项，那么在主键的配置中也需要声明同样的表达式，例如：
-
省略... ) ENGINE = MergeTree() ORDER BY (CounterID, EventDate, intHash32(UserID) SAMPLE BY intHash32(UserID) SELECT CounterID FROM hits_v1 SAMPLE 0.1 --随机返回10%的数据 SELECT count() FROM hits_v1 SAMPLE 10000 --随机返回1000条数据 SELECT CounterID FROM hits_v1 SAMPLE 0.4 OFFSET 0.5 --偏移50%数据开始采样40%数据 -
-
-
（5）SETTINGS：index_granularity [选填]：index_granularity对于MergeTree而言是一项非常重要的参数，它表示索引的粒度，默认值为8192。也就是说，MergeTree的索引在默认情况下，每间隔8192行数据才生成一条索引，其具体声明方式如下所示：
-
省略... ) ENGINE = MergeTree() 省略... SETTINGS index_granularity = 8192; -
（6）SETTINGS：enable_mixed_granularity_parts [选填]：设置是否开启自适应索引间隔的功能，默认开启。
-
（7）SETTINGS：index_granularity_bytes [选填]根据每一批次写入数据的体量大小，动态划分间隔大小。而数据的体量大小，正是由index_granularity_bytes参数控制的，默认为10M(10×1024×1024)，设置为0表示不启动自适应功能
-
（8）SETTINGS：merge_with_ttl_timeout [选填]：TTL默认的合并频率，默认86400秒，即1天
4.1.2 MergeTree的存储结构
MergeTree表引擎中的数据是拥有物理存储的，数据会按照分区目录的形式保存到磁盘之上，其完整的存储结构如图6-2所示。
从图6-2中可以看出，一张数据表的完整物理结构分为3个层级，依次是数据表目录、分区目录及各分区下具体的数据文件。接下来就逐一介绍它们的作用。
（1）partition：分区目录，余下各类数据文件（primary.idx、[Column].mrk、[Column].bin等）都是以分区目录的形式被组织存放的，属于相同分区的数据，最终会被合并到同一个分区目录
（2）checksums.txt：校验文件，使用二进制格式存储。它保存了余下各类文件(primary.idx、count.txt等)的size大小及size的哈希值，用于快速校验文件的完整性和正确性。
（3）columns.txt：列信息文件，使用明文格式存储。用于保存此数据分区下的列字段信息，例如：
$ cat columns.txt
columns format version: 1
4 columns:
'ID' String
'URL' String
'Code' String
'EventTime' Date
（4）count.txt：计数文件，使用明文格式存储。用于记录当前数据分区目录下数据的总行数，例如：
$ cat count.txt
8
（5）primary.idx：一级索引文件，使用二进制格式存储。用于存放稀疏索引，一张MergeTree表只能声明一次一级索引（通过ORDER BY或者PRIMARY KEY）。
（6）[Column].bin：数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据
（7）[Column].mrk：列字段标记文件，使用二进制格式存储。标记文件中保存了.bin文件中数据的偏移量信息。标记文件与稀疏索引对齐，又与.bin文件一一对应，所以MergeTree通过标记文件建立了primary.idx稀疏索引与.bin数据文件之间的映射关系
（8）[Column].mrk2：如果使用了自适应大小的索引间隔，则标记文件会以.mrk2命名。它的工作原理和作用与.mrk标记文件相同。
（9）partition.dat与minmax_[Column].idx：如果使用了分区键，例如PARTITION BY EventTime，则会额外生成partition.dat与minmax索引文件，它们均使用二进制格式存储。partition.dat用于保存当前分区下分区表达式最终生成的值；而minmax索引用于记录当前分区下分区字段对应原始数据的最小和最大值
（10）skp_idx_[Column].idx与skp_idx_[Column].mrk：如果在建表语句中声明了二级索引，则会额外生成相应的二级索引与标记文件，它们同样也使用二进制存储。二级索引在ClickHouse中又称跳数索引
4.2 数据分区
4.2.1 数据的分区规则
- （1）不指定分区键：如果不使用分区键，即不使用PARTITION BY声明任何分区表达式，则分区ID默认取名为all，所有的数据都会被写入这个all分区。
- （2）使用整型：如果分区键取值属于整型（兼容UInt64，包括有符号整型和无符号整型），且无法转换为日期类型YYYYMMDD格式，则直接按照该整型的字符形式输出，作为分区ID的取值。
- （3）使用日期类型：如果分区键取值属于日期类型，或者是能够转换为YYYYMMDD格式的整型，则使用按照YYYYMMDD进行格式化后的字符形式输出，并作为分区ID的取值。
- （4）使用其他类型：如果分区键取值既不属于整型，也不属于日期类型，例如String、Float等，则通过128位Hash算法取其Hash值作为分区ID的取值。
如果通过元组的方式使用多个分区字段，则分区ID依旧是根据上述规则生成的，只是多个ID之间通过“-”符号依次拼接。例如按照上述表格中的例子，使用两个字段分区：
PARTITION BY (length(Code),EventTime)
-- 则最终的分区ID会是下面的模样：
2-20190501
2-20190611
4.2.2 分区目录的命名规则
一个完整分区目录的命名公式如下所示：
PartitionID_MinBlockNum_MaxBlockNum_Level
上图中，201905表示分区目录的ID；1_1分别表示最小的数据块编号与最大的数据块编号；而最后的_0则表示目前合并的层级。接下来开始分别解释它们的含义：
- （1）PartitionID：分区ID，无须多说，关于分区ID的规则在上一小节中已经做过详细阐述了。
- （2）MinBlockNum和MaxBlockNum：顾名思义，最小数据块编号与最大数据块编号。ClickHouse在这里的命名似乎有些歧义，很容易让人与稍后会介绍到的数据压缩块混淆。但是本质上它们毫无关系，这里的BlockNum是一个整型的自增长编号。如果将其设为n的话，那么计数n在单张MergeTree数据表内全局累加，n从1开始，每当新创建一个分区目录时，计数n就会累积加1。对于一个新的分区目录而言，MinBlockNum与MaxBlockNum取值一样，同等于n，例如201905_1_1_0、201906_2_2_0以此类推。但是也有例外，当分区目录发生合并时，对于新产生的合并目录MinBlockNum与MaxBlockNum有着另外的取值规则
- （3）Level：合并的层级，可以理解为某个分区被合并过的次数，或者这个分区的年龄。数值越高表示年龄越大
4.2.3 分区目录的合并过程
MergeTree完全不同，伴随着每一批数据的写入（一次INSERT语句），MergeTree都会生成一批新的分区目录。即便不同批次写入的数据属于相同分区，也会生成不同的分区目录。也就是说，对于同一个分区而言，也会存在多个分区目录的情况。在之后的某个时刻（写入后的10～15分钟，也可以手动执行optimize TABLE table_name语句），ClickHouse会通过后台任务再将属于相同分区的多个目录合并成一个新的目录。已经存在的旧分区目录并不会立即被删除，而是在之后的某个时刻通过后台任务被删除（默认8分钟）。
属于同一个分区的多个目录，在合并之后会生成一个全新的目录，目录中的索引和数据文件也会相应地进行合并。新目录名称的合并方式遵循以下规则，其中：
- ·MinBlockNum：取同一分区内所有目录中最小的MinBlockNum值。
- ·MaxBlockNum：取同一分区内所有目录中最大的MaxBlockNum值。
- ·Level：取同一分区内最大Level值并加1。
4.3 一级索引
4.3.1 稀疏索引
primary.idx文件内的一级索引采用稀疏索引实现。
稠密索引中每一行索引标记都会对应到一行具体的数据记录。而在稀疏索引中，每一行索引标记对应的是一段数据，而不是一行
稀疏索引的优势是显而易见的，它仅需使用少量的索引标记就能够记录大量数据的区间位置信息，且数据量越大优势越为明显。以默认的索引粒度（8192）为例，MergeTree只需要12208行索引标记就能为1亿行数据记录提供索引。由于稀疏索引占用空间小，所以primary.idx内的索引数据常驻内存，取用速度自然极快。
4.3.2 索引粒度
MergeTree按照索引粒度
4.3.3 索引数据的生成规则
例如第0(81920)行CounterID取值57，第8192(81921)行CounterID取值1635，而第16384(8192*2)行CounterID取值3266，最终索引数据将会是5716353266。
如果使用多个主键，例如ORDER BY(CounterID,EventDate)，则每间隔8192行可以同时取CounterID与EventDate两列的值作为索引值，具体如图6-9所示。
4.3.4 索引的查询过程
现在有一份测试数据，共192行记录。其中，主键ID为String类型，ID的取值从A000开始，后面依次为A001、A002……直至A192为止。MergeTree的索引粒度index_granularity=3，根据索引的生成规则，primary.idx文件内的索引数据。
根据索引数据，MergeTree会将此数据片段划分成192/3=64个小的MarkRange，两个相邻MarkRange相距的步长为1。其中，所有MarkRange（整个数据片段）的最大数值区间为[A000,+inf)。
在引出了数值区间的概念之后，对于索引的查询过程就很好解释了。索引查询其实就是两个数值区间的交集判断。其中，一个区间是由基于主键的查询条件转换而来的条件区间；而另一个区间是刚才所讲述的与MarkRange对应的数值区间。
整个索引查询过程可以大致分为3个步骤。
（1）生成查询条件区间：首先，将查询条件转换为条件区间。即便是单个值的查询条件，也会被转换成区间的形式，例如下面的例子。
WHERE ID = 'A003'
['A003', 'A003']
WHERE ID > 'A000'
('A000', +inf)
WHERE ID < 'A188'
(-inf, 'A188')
WHERE ID LIKE 'A006%'
['A006', 'A007')
（2）递归交集判断：以递归的形式，依次对MarkRange的数值区间与条件区间做交集判断。从最大的区间[A000,+inf)开始：
- ·如果不存在交集，数据不存在。
- ·如果存在交集，且MarkRange步长大于8(end-start)，则将此区间进一步拆分成8个子区间，并重复此规则，继续做递归交集判断。
- ·如果存在交集，且MarkRange不可再分解（步长小于8），则记录MarkRange并返回。
（3）合并MarkRange区间：将最终匹配的MarkRange聚在一起，合并它们的范围。
4.4 二级索引
除了一级索引之外，MergeTree同样支持二级索引。二级索引又称跳数索引，由数据的聚合信息构建而成。根据索引类型的不同，其聚合信息的内容也不同。跳数索引的目的与一级索引一样，也是帮助查询时减少数据扫描的范围。
SET allow_experimental_data_skipping_indices = 1 --跳数索引在默认情况下是关闭的，需要设置allow_experimental_data_skipping_indices才能使用：
跳数索引需要在CREATE语句内定义，它支持使用元组和表达式的形式声明，其完整的定义语法如下所示：
INDEX index_name expr TYPE index_type(...) GRANULARITY granularity
与一级索引一样，如果在建表语句中声明了跳数索引，则会额外生成相应的索引与标记文件（skp_idx_[Column].idx与skp_idx_[Column].mrk）。
4.4.1 跳数索引的类型
目前，MergeTree共支持4种跳数索引，分别是minmax、set、ngrambf_v1和tokenbf_v1。一张数据表支持同时声明多个跳数索引，例如：
CREATE TABLE skip_test (
ID String,
URL String,
Code String,
EventTime Date,
INDEX a ID TYPE minmax GRANULARITY 5,
INDEX b（length(ID) * 8） TYPE set(2) GRANULARITY 5,
INDEX c（ID，Code） TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 5,
INDEX d ID TYPE tokenbf_v1(256, 2, 0) GRANULARITY 5
) ENGINE = MergeTree()
省略...
（1）minmax：minmax索引记录了一段数据内的最小和最大极值，其索引的作用类似分区目录的minmax索引，能够快速跳过无用的数据区间，示例如下所示：
INDEX a ID TYPE minmax GRANULARITY 5 --上述示例中minmax索引会记录这段数据区间内ID字段的极值。极值的计算涉及每5个index_granularity区间中的数据。
（2）set：set索引直接记录了声明字段或表达式的取值（唯一值，无重复），其完整形式为set(max_rows)，其中max_rows是一个阈值，表示在一个index_granularity内，索引最多记录的数据行数。如果max_rows=0，则表示无限制，例如：
INDEX b（length(ID) * 8） TYPE set(100) GRANULARITY 5 --set索引会记录数据中ID的长度*8后的取值。其中，每个index_granularity内最多记录100条。
（3）ngrambf_v1：ngrambf_v1索引记录的是数据短语的布隆表过滤器，只支持String和FixedString数据类型。这些参数是一个布隆过滤器的标准输入，如果你接触过布隆过滤器，应该会对此十分熟悉。它们具体的含义如下：
- n：token长度，依据n的长度将数据切割为token短语。
- size_of_bloom_filter_in_bytes：布隆过滤器的大小。
- number_of_hash_functions：布隆过滤器中使用Hash函数的个数。
- random_seed：Hash函数的随机种子。
INDEX c（ID，Code） TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 5 --ngrambf_v1索引会依照3的粒度将数据切割成短语token，token会经过2个Hash函数映射后再被写入，布隆过滤器大小为256字节。
（4）tokenbf_v1：tokenbf_v1索引是ngrambf_v1的变种，同样也是一种布隆过滤器索引。tokenbf_v1去除了短语token的处理方法外，其他与ngrambf_v1是完全一样的。tokenbf_v1会自动按照非字符的、数字的字符串分割token，具体用法如下所示：
INDEX d ID TYPE tokenbf_v1(256, 2, 0) GRANULARITY 5
4.5 数据存储
4.5.1 各列独立存储
在MergeTree中，数据按列存储。而具体到每个列字段，数据也是独立存储的，每个列字段都拥有一个与之对应的.bin数据文件。也正是这些.bin文件，最终承载着数据的物理存储。数据文件以分区目录的形式被组织存放，所以在.bin文件中只会保存当前分区片段内的这一部分数据。按列独立存储的设计优势显而易见：一是可以更好地进行数据压缩（相同类型的数据放在一起，对压缩更加友好），二是能够最小化数据扫描的范围。
4.5.2 压缩数据块
一个压缩数据块由头信息和压缩数据两部分组成。头信息固定使用9位字节表示，具体由1个UInt8（1字节）整型和2个UInt32（4字节）整型组成，分别代表使用的压缩算法类型、压缩后的数据大小和压缩前的数据大小，具体如图6-14所示。
通过ClickHouse提供的clickhouse-compressor工具，能够查询某个.bin文件中压缩数据的统计信息
clickhouse-compressor --stat < /var/lib/clickhouse/data/cloud_joy_yun/cloud_friend/202007_1_1_0/cloud_id.bin
--执行后，会看到如下信息：
65536 12000
65536 14661
65536 4936
65536 7506
省略…
其中每一行数据代表着一个压缩数据块的头信息，其分别表示该压缩块中未压缩数据大小和压缩后数据大小。
每个压缩数据块的体积，按照其压缩前的数据字节大小，都被严格控制在64KB～1MB，其上下限分别由min_compress_block_size（默认65536）与max_compress_block_size（默认1048576）参数指定。而一个压缩数据块最终的大小，则和一个间隔（index_granularity）内数据的实际大小相关。
MergeTree在数据具体的写入过程中，会依照索引粒度（默认情况下，每次取8192行），按批次获取数据并进行处理。如果把一批数据的未压缩大小设为size，则整个写入过程遵循以下规则：
（1）单个批次数据size<64KB：如果单个批次数据小于64KB，则继续获取下一批数据，直至累积到size>=64KB时，生成下一个压缩数据块。
（2）单个批次数据64KB<=size<=1MB：如果单个批次数据大小恰好在64KB与1MB之间，则直接生成下一个压缩数据块。
（3）单个批次数据size>1MB：如果单个批次数据直接超过1MB，则首先按照1MB大小截断并生成下一个压缩数据块。剩余数据继续依照上述规则执行。此时，会出现一个批次数据生成多个压缩数据块的情况。
经过上述的介绍后我们知道，一个.bin文件是由1至多个压缩数据块组成的，每个压缩块大小在64KB～1MB之间。多个压缩数据块之间，按照写入顺序首尾相接，紧密地排列在一起。
在.bin文件中引入压缩数据块的目的至少有以下两个：其一，虽然数据被压缩后能够有效减少数据大小，降低存储空间并加速数据传输效率。其二，通过压缩数据块，可以在不读取整个.bin文件的情况下将读取粒度降低到压缩数据块级别，从而进一步缩小数据读取的范围。
4.6 数据标记
4.6.1 数据标记的生成规则
为了能够与数据衔接，数据标记文件也与.bin文件一一对应。即每一个列字段[Column].bin文件都有一个与之对应的[Column].mrk数据标记文件，用于记录数据在.bin文件中的偏移量信息。
一行标记数据使用一个元组表示，元组内包含两个整型数值的偏移量信息。它们分别表示在此段数据区间内，在对应的.bin压缩文件中，压缩数据块的起始偏移量；以及将该数据压缩块解压后，其未压缩数据的起始偏移量。.mrk文件内标记数据的示意。
4.6.2 数据标记的工作方式
MergeTree在读取数据时，必须通过标记数据的位置信息才能够找到所需要的数据。整个查找过程大致可以分为读取压缩数据块和读取数据两个步骤。为了便于解释，这里继续使用测试表hits_v1中的真实数据进行说明。
首先，左侧的标记数据做一番解释说明。JavaEnable字段的数据类型为UInt8，所以每行数值占用1字节。而hits_v1数据表的index_granularity粒度为8192，所以一个索引片段的数据大小恰好是8192B。压缩数据块的生成规则，如果单个批次数据小于64KB，则继续获取下一批数据，直至累积到size>=64KB时，生成下一个压缩数据块。因此在JavaEnable的标记文件中，每8行标记数据对应1个压缩数据块（1B*8192=8192B,64KB=65536B,65536/8192=8）。其左侧的标记数据中，8行数据的压缩文件偏移量都是相同的，因为这8行标记都指向了同一个压缩数据块。而在这8行的标记数据中，它们的解压缩数据块中的偏移量，则依次按照8192B（每行数据1B，每一个批次8192行数据）累加，当累加达到65536(64KB)时则置0。因为根据规则，此时会生成下一个压缩数据块。
理解了上述标记数据之后，接下来就开始介绍MergeTree具体是如何定位压缩数据块并读取数据的。
（1）读取压缩数据块：在查询某一列数据时，MergeTree无须一次性加载整个.bin文件，而是可以根据需要，只加载特定的压缩数据块。而这项特性需要借助标记文件中所保存的压缩文件中的偏移量。
（2）读取数据：在读取解压后的数据时，MergeTree并不需要一次性扫描整段解压数据，它可以根据需要，以index_granularity的粒度加载特定的一小段。为了实现这项特性，需要借助标记文件中保存的解压数据块中的偏移量。
4.7 对于分区、索引、标记和压缩数据的协同总结
4.7.1 写入过程
数据写入的第一步是生成分区目录，伴随着每一批数据的写入，都会生成一个新的分区目录。在后续的某一时刻，属于相同分区的目录会依照规则合并到一起；接着，按照index_granularity索引粒度，会分别生成primary.idx一级索引（如果声明了二级索引，还会创建二级索引文件）、每一个列字段的.mrk数据标记和.bin压缩数据文件。
4.7.2 查询过程
数据查询的本质，可以看作一个不断减小数据范围的过程。在最理想的情况下，MergeTree首先可以依次借助分区索引、一级索引和二级索引，将数据扫描范围缩至最小。然后再借助数据标记，将需要解压与计算的数据范围缩至最小
4.7.3 数据标记与压缩数据块的对应关系
1.多对一
多个数据标记对应一个压缩数据块，当一个间隔（index_granularity）内的数据未压缩大小size小于64KB时，会出现这种对应关系。
以hits_v1测试表的JavaEnable字段为例。JavaEnable数据类型为UInt8，大小为1B，则一个间隔内数据大小为8192B。所以在此种情形下，每8个数据标记会对应同一个压缩数据块，如图6-22所示。
2.一对一
一个数据标记对应一个压缩数据块，当一个间隔（index_granularity）内的数据未压缩大小size大于等于64KB且小于等于1MB时，会出现这种对应关系。
3.一对多
以hits_v1测试表的URL字段为例。URL数据类型为String，大小根据实际内容而定。如图6-24所示，编号45的标记对应了2个压缩数据块。
一个数据标记对应多个压缩数据块，当一个间隔（index_granularity）内的数据未压缩大小size直接大于1MB时，会出现这种对应关系。
第5章 MergeTree系列表引擎
除了基础表引擎MergeTree之外，常用的表引擎还有ReplacingMergeTree、SummingMergeTree、AggregatingMergeTree、CollapsingMergeTree和VersionedCollapsingMergeTree。每一种合并树的变种，在继承了基础MergeTree的能力之后，又增加了独有的特性。其名称中的“合并”二字奠定了所有类型MergeTree的基因，它们的所有特殊逻辑，都是在触发合并的过程中被激活的。在本章后续的内容中，会逐一介绍它们的特点以及使用方法。
5.1 MergeTree
5.1.1 数据TTL
TTL即Time To Live，顾名思义，它表示数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；如果是表级别的TTL，则会删除整张表的数据；如果同时设置了列级别和表级别的TTL，则会以先到期的那个为主。
1.列级别TTL
如果想要设置列级别的TTL，则需要在定义表字段的时候，为它们声明TTL表达式，主键字段不能被声明TTL。以下面的语句为例：
CREATE TABLE ttl_table_v1(
id String,
create_time DateTime,
code String TTL create_time + interval 10 SECOND,
type UInt8 TTL create_time + interval 10 SECOND
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id
其中，create_time是日期类型，列字段code与type均被设置了TTL，它们的存活时间是在create_time的取值基础之上向后延续10秒。
INSERT INTO TABLE ttl_table_v1 VALUES('A000',now(),'C1',1),
('A000',now() + INTERVAL 10 MINUTE,'C1',1)
SELECT * FROM ttl_table_v1
┌─id───┬─────create_time──┬─code─┬─type─┐
│ A000 │ 2019-06-12 22:49:00 │ C1 │ 1 │
│ A000 │ 2019-06-12 22:59:00 │ C1 │ 1 │
└────┴───────────────┴────┴─────┘
optimize TABLE ttl_table_v1 FINAL --接着心中默数10秒，然后执行optimize命令强制触发TTL清理：
再次查询ttl_table_v1则能够看到，由于第一行数据满足TTL过期条件（当前系统时间>=create_time+10秒），它们的code和type列会被还原为数据类型的默认值：
┌─id───┬───────create_time─┬─code─┬─type─┐
│ A000 │ 2019-06-12 22:49:00 │ │ 0 │
│ A000 │ 2019-06-12 22:59:00 │ C1 │ 1 │
└─────┴───────────────┴─────┴─────┘
如果想要修改列字段的TTL，或是为已有字段添加TTL，则可以使用ALTER语句，示例如下：
ALTER TABLE ttl_table_v1 MODIFY COLUMN code String TTL create_time + INTERVAL 1 DAY
列级别TTL目前也没有取消的方法。
2.表级别TTL
如果想要为整张数据表设置TTL，需要在MergeTree的表参数中增加TTL表达式，例如下面的语句：
CREATE TABLE ttl_table_v2(
id String,
create_time DateTime,
code String TTL create_time + INTERVAL 1 MINUTE,
type UInt8
)ENGINE = MergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY create_time
TTL create_time + INTERVAL 1 DAY
ttl_table_v2整张表被设置了TTL，当触发TTL清理时，那些满足过期时间的数据行将会被整行删除。同样，表级别的TTL也支持修改，修改的方法如下：
ALTER TABLE ttl_table_v2 MODIFY TTL create_time + INTERVAL 3 DAY
表级别TTL目前也没有取消的方法。
5.2 ReplacingMergeTree
在一定程度上解决了重复数据的问题
创建一张ReplacingMergeTree表的方法与创建普通MergeTree表无异，只需要替换Engine：
ENGINE = ReplacingMergeTree(ver)
其中，ver是选填参数，会指定一个UInt*、Date或者DateTime类型的字段作为版本号。这个参数决定了数据去重时所使用的算法。键ORDER BY所声明的表达式是后续作为判断数据是否重复的依据
CREATE TABLE replace_table(
id String,
code String,
create_time DateTime
)ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY (id,code)
PRIMARY KEY id
┌─id───┬─code─┬───────create_time─┐
│ A001 │ C1 │ 2019-05-10 17:00:00 │
│ A001 │ C1 │ 2019-05-11 17:00:00 │
│ A001 │ C100 │ 2019-05-12 17:00:00 │
│ A001 │ C200 │ 2019-05-13 17:00:00 │
│ A002 │ C2 │ 2019-05-14 17:00:00 │
│ A003 │ C3 │ 2019-05-15 17:00:00 │
└─────┴─────┴───────────────┘
optimize TABLE replace_table FINAL --optimize强制触发合并后 ，保留分组内的最后一条
┌─id───┬─code─┬──────create_time─┐ --将其余重复的数据删除：
│ A001 │ C1 │ 2019-05-11 17:00:00 │
│ A001 │ C100 │ 2019-05-12 17:00:00 │
│ A001 │ C200 │ 2019-05-13 17:00:00 │
│ A002 │ C2 │ 2019-05-14 17:00:00 │
│ A003 │ C3 │ 2019-05-15 17:00:00 │
└────┴────┴──────────────┘
INSERT INTO TABLE replace_table VALUES('A001','C1','2019-08-10 17:00:00')
┌─id───┬─code─┬─────────create_time─┐
│ A001 │ C1 │ 2019-08-22 17:00:00 │
└─────┴────┴─────────────────┘
┌─id──┬─code─┬─────────create_time─┐
│ A001 │ C1 │ 2019-05-11 17:00:00 │
│ A001 │ C100 │ 2019-05-12 17:00:00 │
│ A001 │ C200 │ 2019-05-13 17:00:00 │
│ A002 │ C2 │ 2019-05-14 17:00:00 │
│ A003 │ C3 │ 2019-05-15 17:00:00 │
└────┴──────┴─────────────────┘
可以看到A001:C1依然出现了重复, 是因为ReplacingMergeTree是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。这就是上面说ReplacingMergeTree只是在一定程度上解决了重复数据问题的原因
现在接着说明ReplacingMergeTree版本号的用法。以下面的语句为例
CREATE TABLE replace_table_v(
id String,
code String,
create_time DateTime
)ENGINE = ReplacingMergeTree(create_time)
PARTITION BY toYYYYMM(create_time)
ORDER BY id
┌─id──┬─code──┬───────────create_time─┐ --replace_table_v基于id字段去重，并且使用create_time字段作为版本号
│ A001 │ C1 │ 2019-05-10 17:00:00 │
│ A001 │ C1 │ 2019-05-25 17:00:00 │
│ A001 │ C1 │ 2019-05-13 17:00:00 │
└────┴─────┴───────────────────┘
┌─id────┬─code─┬──────────create_time─┐ --会保留同一组数据内create_time时间最长的那一行
│ A001 │ C1 │ 2019-05-25 17:00:00 │
└──────┴────┴──────────────────┘
- （1）使用ORBER BY排序键作为判断重复数据的唯一键。
- （2）只有在合并分区的时候才会触发删除重复数据的逻辑。
- （3）以数据分区为单位删除重复数据。当分区合并时，同一分区内的重复数据会被删除；不同分区之间的重复数据不会被删除。
- （4）在进行数据去重时，因为分区内的数据已经基于ORBER BY进行了排序，所以能够找到那些相邻的重复数据。
- （5）数据去重策略有两种：
·如果没有设置ver版本号，则保留同一组重复数据中的最后一行。
·如果设置了ver版本号，则保留同一组重复数据中ver字段取值最大的那一行。
5.3 SummingMergeTree
假设有这样一种查询需求：终端用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的（GROUP BY条件明确，且不会随意改变）。
SummingMergeTree就是为了应对这类查询场景而生的。顾名思义，它能够在合并分区的时候按照预先定义的条件聚合汇总数据，将同一分组下的多行数据汇总合并成一行，这样既减少了数据行，又降低了后续汇总查询的开销。
ENGINE = SummingMergeTree((col1,col2,…)) --ENGINE = SummingMergeTree((col1,col2,…))
其中，col1、col2为columns参数值，这是一个选填参数，用于设置除主键外的其他数值类型字段，以指定被SUM汇总的列字段。如若不填写此参数，则会将所有非主键的数值类型字段进行SUM汇总。接来下用一组示例说明它的使用方法：
CREATE TABLE summing_table(
id String,
city String,
v1 UInt32,
v2 Float64,
create_time DateTime
)ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(create_time)
ORDER BY (id, city)
PRIMARY KEY id
┌─id──┬─city───┬─v1─┬─v2─┬────────create_time─┐
│ A001 │ wuhan │ 10 │ 20 │ 2019-08-10 17:00:00 │
│ A001 │ wuhan │ 20 │ 30 │ 2019-08-20 17:00:00 │
│ A001 │ zhuhai │ 20 │ 30 │ 2019-08-10 17:00:00 │
└─────┴───────┴───┴───┴────────────────┘
┌─id──┬─city───┬─v1─┬─v2─┬────────create_time─┐
│ A001 │ wuhan │ 10 │ 20 │ 2019-02-10 09:00:00 │
└─────┴───────┴───┴───┴───────────────┘
┌─id──┬─city───┬─v1─┬─v2─┬────────create_time─┐
│ A002 │ wuhan │ 60 │ 50 │ 2019-10-10 17:00:00 │
└────┴──────┴───┴───┴───────────────┘
optimize TABLE summing_table FINAL --执行optimize强制进行触发和合并操作：
┌─id──┬─city───┬─v1─┬─v2─┬─────────create_time─┐
│ A001 │ wuhan │ 30 │ 50 │ 2019-08-10 17:00:00 │
│ A001 │ zhuhai │ 20 │ 30 │ 2019-08-10 17:00:00 │
└─────┴──────┴────┴────┴─────────────────┘
┌─id──┬─city───┬─v1─┬─v2─┬─────────create_time─┐
│ A001 │ wuhan │ 10 │ 20 │ 2019-02-10 09:00:00 │
└────┴──────┴────┴────┴─────────────────┘
┌─id──┬─city───┬─v1─┬─v2─┬─────────create_time─┐
│ A002 │ wuhan │ 60 │ 50 │ 2019-10-10 17:00:00 │
└────┴──────┴────┴────┴─────────────────┘
至此能够看到，在第一个分区内，同为A001:wuhan的两条数据汇总成了一行。其中，v1和v2被SUM汇总，不在汇总字段之列的create_time则选取了同组内第一行数据的取值。而不同分区之间，数据没有被汇总合并
- （1）用ORBER BY排序键作为聚合数据的条件Key。
- （2）只有在合并分区的时候才会触发汇总的逻辑。
- （3）以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。
- （4）如果在定义引擎时指定了columns汇总列（非主键的数值类型字段），则SUM汇总这些列字段；如果未指定，则聚合所有非主键的数值类型字段。
- （5）在进行数据汇总时，因为分区内的数据已经基于ORBER BY排序，所以能够找到相邻且拥有相同聚合Key的数据。
- （6）在汇总数据时，同一分区内，相同聚合Key的多行数据会合并成一行。其中，汇总字段会进行SUM计算；对于那些非汇总字段，则会使用第一行数据的取值。
5.4 CollapsingMergeTree
CollapsingMergeTree就是一种通过以增代删的思路，支持行级数据修改和删除的表引擎。它通过定义一个sign标记位字段，记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。这种1和-1相互抵消的操作，犹如将一张瓦楞纸折叠了一般。这种直观的比喻，想必也正是折叠合并树（CollapsingMergeTree）名称的由来
声明CollapsingMergeTree的方式如下：
ENGINE = CollapsingMergeTree(sign)
其中，sign用于指定一个Int8类型的标志位字段。一个完整的使用示例如下所示：
CREATE TABLE collpase_table(
id String,
code Int32,
create_time DateTime,
sign Int8
)ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(create_time)
ORDER BY id
与其他的MergeTree变种引擎一样，CollapsingMergeTree同样是以ORDER BY排序键作为后续判断数据唯一性的依据。按照之前的介绍，对于上述collpase_table数据表而言，除了常规的新增数据操作之外，还能够支持两种操作。
其一，修改一行数据：
--修改前的源数据, 它需要被修改
INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',1)
--镜像数据, ORDER BY字段与源数据相同(其他字段可以不同),sign取反为-1,它会和源数据折叠
INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',-1)
--修改后的数据 ,sign为1
INSERT INTO TABLE collpase_table VALUES('A000',120,'2019-02-20 00:00:00',1)
其二，删除一行数据：
--修改前的源数据, 它需要被删除
INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',1)
--镜像数据, ORDER BY字段与源数据相同, sign取反为-1, 它会和源数据折叠
INSERT INTO TABLE collpase_table VALUES('A000',100,'2019-02-20 00:00:00',-1)
CollapsingMergeTree在折叠数据时，遵循以下规则。
- ·如果sign=1比sign=-1的数据多一行，则保留最后一行sign=1的数据。
- ·如果sign=-1比sign=1的数据多一行，则保留第一行sign=-1的数据。
- ·如果sign=1和sign=-1的数据行一样多，并且最后一行是sign=1，则保留第一行sign=-1和最后一行sign=1的数据。
- ·如果sign=1和sign=-1的数据行一样多，并且最后一行是sign=-1，则什么也不保留。
在使用CollapsingMergeTree的时候，还有几点需要注意。
（1）折叠数据并不是实时触发的，和所有其他的MergeTree变种表引擎一样，这项特性也只有在分区合并的时候才会体现。所以在分区合并之前，用户还是会看到旧的数据。解决这个问题的方式有两种。
-
·在查询数据之前，使用optimize TABLE table_name FINAL命令强制分区合并，但是这种方法效率极低，在实际生产环境中慎用。
-
·需要改变我们的查询方式。以collpase_table举例，如果原始的SQL如下所示：
SELECT id,SUM(code * sign),COUNT(code * sign),AVG(code * sign),uniq(code * sign) FROM collpase_table GROUP BY id HAVING SUM(sign) > 0
--先写入sign=1 先写入sign=1，再写入sign=-1，则能够正常折叠：
INSERT INTO TABLE collpase_table VALUES('A000',102,'2019-02-20 00:00:00',1)
--再写入sign=-1
INSERT INTO TABLE collpase_table VALUES('A000',101,'2019-02-20 00:00:00',-1)
--先写入sign=-1 先写入sign=-1，再写入sign=1，则不能够折叠：
INSERT INTO TABLE collpase_table VALUES('A000',101,'2019-02-20 00:00:00',-1)
--再写入sign=1
INSERT INTO TABLE collpase_table VALUES('A000',102,'2019-02-20 00:00:00',1)
5.5 VersionedCollapsingMergeTree
VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同，它们的不同之处在于，VersionedCollapsingMergeTree对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作
在定义VersionedCollapsingMergeTree的时候，除了需要指定sign标记字段以外，还需要指定一个UInt8类型的ver版本号字段：
ENGINE = VersionedCollapsingMergeTree(sign,ver)
CREATE TABLE ver_collpase_table(
id String,
code Int32,
create_time DateTime,
sign Int8,
ver UInt8
)ENGINE = VersionedCollapsingMergeTree(sign,ver)
PARTITION BY toYYYYMM(create_time)
ORDER BY id
VersionedCollapsingMergeTree是如何使用版本号字段的呢？其实很简单，在定义ver字段之后，VersionedCollapsingMergeTree会自动将ver作为排序条件并增加到ORDER BY的末端。以上面的ver_collpase_table表为例，在每个数据分区内，数据会按照ORDER BY id，ver DESC排序。所以无论写入时数据的顺序如何，在折叠处理时，都能回到正确的顺序。
第6章 其他常见类型表引擎
6.1 外部存储类型
6.1.1 HDFS
首先需要关闭HDFS的Kerberos认证（因为HDFS表引擎目前还不支持Kerberos）；接着在HDFS上创建用于存放文件的目录：
hadoop fs -mkdir /clickhouse
最后，在HDFS上给ClickHouse用户授权。例如，为默认用户clickhouse授权的方法如下：
hadoop fs -chown -R clickhouse:clickhouse /clickhouse
HDFS表引擎的定义方法如下：
ENGINE = HDFS(hdfs_uri,format)
- ·hdfs_uri表示HDFS的文件存储路径；
- ·format表示文件格式（指ClickHouse支持的文件格式，常见的有CSV、TSV和JSON等）。
CREATE TABLE hdfs_table1(
id UInt32,
code String,
name String
)ENGINE = HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table1','CSV')
INSERT INTO hdfs_table1 SELECT number,concat('code',toString(number)),
concat('n',toString(number)) FROM numbers(5)
SELECT * FROM hdfs_table1
┌─id─┬─code─┬─name─┐
│ 0 │ code0 │ n0 │
│ 1 │ code1 │ n1 │
│ 2 │ code2 │ n2 │
│ 3 │ code3 │ n3 │
│ 4 │ code4 │ n4 │
└───┴─────┴────┘
接着再看看在HDFS上发生了什么变化。执行hadoop fs -cat查看文件：
$ hadoop fs -cat /clickhouse/hdfs_table1
0,"code0","n0"
1,"code1","n1"
2,"code2","n2"
3,"code3","n3"
4,"code4","n4"
可以发现，通过HDFS表引擎，ClickHouse在HDFS的指定目录下创建了一个名为hdfs_table1的文件，并且按照CSV格式写入了数据。不过目前ClickHouse并没有提供删除HDFS文件的方法，即便将数据表hdfs_table1删除：
DROP Table hdfs_table1
在HDFS上文件依然存在：
$ hadoop fs -ls /clickhouse
Found 1 items
-rwxrwxrwx 3 clickhouse clickhouse /clickhouse/hdfs_table1
接下来，介绍第二种形式的使用方法，这种形式类似Hive的外挂表，由其他系统直接将文件写入HDFS。通过HDFS表引擎的hdfs_uri和format参数分别与HDFS的文件路径、文件格式建立映射。其中，hdfs_uri支持以下几种常见的配置方法：
- ·绝对路径：会读取指定路径的单个文件，例如/clickhouse/hdfs_table1。
- ·通配符：匹配所有字符，例如路径为/clickhouse/hdfs_table/，则会读取/click-house/hdfs_table路径下的所有文件。
- ·?通配符：匹配单个字符，例如路径为/clickhouse/hdfs_table/organization_?.csv，则会读取/clickhouse/hdfs_table路径下与organization_?.csv匹配的文件，其中?代表任意一个合法字符。
- ·{M..N}数字区间：匹配指定数字的文件，例如路径为/clickhouse/hdfs_table/organization_{1..3}.csv，则会读取/clickhouse/hdfs_table/路径下的文件organization_1.csv、organization_2.csv和organization_3.csv。
现在用一个具体示例验证表引擎的效果。首先，将事先准备好的3个CSV测试文件上传至HDFS的/clickhouse/hdfs_table2路径（用于测试的CSV文件，可以在本书的github仓库获取）：
--上传文件至HDFS
$ hadoop fs -put /chbase/demo-data/ /clickhouse/hdfs_table2
--查询路径
$ hadoop fs -ls /clickhouse/hdfs_table2
Found 3 items
-rw-r--r-- 3 hdfs clickhouse /clickhouse/hdfs_table2/organization_1.csv
-rw-r--r-- 3 hdfs clickhouse /clickhouse/hdfs_table2/organization_2.csv
-rw-r--r-- 3 hdfs clickhouse /clickhouse/hdfs_table2/organization_3.csv
接着，创建HDFS测试表：
CREATE TABLE hdfs_table2(
id UInt32,
code String,
name String
) ENGINE = HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/*','CSV')
HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/*','CSV') --*通配符：
HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/organization_?.csv','CSV') --?通配符：
HDFS('hdfs://hdp1.nauu.com:8020/clickhouse/hdfs_table2/organization_{1..3}.csv','CSV') --{M..N}数字区间：
6.1.2 MySQL
MySQL表引擎可以与MySQL数据库中的数据表建立映射，并通过SQL向其发起远程查询，包括SELECT和INSERT，它的声明方式如下：
ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause'])
其中各参数的含义分别如下：
- ·host:port表示MySQL的地址和端口。
- ·database表示数据库的名称。
- ·table表示需要映射的表名称。
- ·user表示MySQL的用户名。
- ·password表示MySQL的密码。
- ·replace_query默认为0，对应MySQL的REPLACE INTO语法。如果将它设置为1，则会用REPLACE INTO代替INSERT INTO。
- ·on_duplicate_clause默认为0，对应MySQL的ON DUPLICATE KEY语法。如果需要使用该设置，则必须将replace_query设置成0。
现在用一个具体的示例说明MySQL表引擎的用法。假设MySQL数据库已准备就绪，则使用MySQL表引擎与其建立映射：
CREATE TABLE dolphin_scheduler_table(
id UInt32,
name String
)ENGINE = MySQL('10.37.129.2:3306', 'escheduler', 't_escheduler_process_definition', 'root', '')
创建成功之后，就可以通过这张数据表代为查询MySQL中的数据了，例如：
SELECT * FROM dolphin_scheduler_table
┌─id─┬─name───┐
│ 1 │ 流程1 │
│ 2 │ 流程2 │
│ 3 │ 流程3 │
└──┴────────┘
INSERT INTO TABLE dolphin_scheduler_table VALUES (4,'流程4') --接着，尝试写入数据：
不过比较遗憾的是，目前MySQL表引擎不支持任何UPDATE和DELETE操作，如果有数据更新方面的诉求，可以考虑使用CollapsingMergeTree作为视图的表引擎。
6.1.4 Kafka
Kafka是大数据领域非常流行的一款分布式消息系统。Kafka表引擎能够直接与Kafka系统对接，进而订阅Kafka中的主题并实时接收消息数据。
Kafka表引擎的声明方式如下所示：
ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'host:port,... ',
kafka_topic_list = 'topic1,topic2,...',
kafka_group_name = 'group_name',
kafka_format = 'data_format'[,]
[kafka_row_delimiter = 'delimiter_symbol']
[kafka_schema = '']
[kafka_num_consumers = N]
[kafka_skip_broken_messages = N]
[kafka_commit_every_batch = N]
其中，带有方括号的参数表示选填项，现在依次介绍这些参数的作用。首先是必填参数：
- ·kafka_broker_list：表示Broker服务的地址列表，多个地址之间使用逗号分隔，例如'hdp1.nauu.com:6667，hdp2.nauu.com:6667'。
- ·kafka_topic_list：表示订阅消息主题的名称列表，多个主题之间使用逗号分隔，例如'topic1,topic2'。多个主题中的数据均会被消费。
- ·kafka_group_name：表示消费组的名称，表引擎会依据此名称创建Kafka的消费组。
- ·kafka_format：表示用于解析消息的数据格式，在消息的发送端，必须按照此格式发送消息。数据格式必须是ClickHouse提供的格式之一，例如TSV、JSONEachRow和CSV等。
- ·kafka_row_delimiter：表示判定一行数据的结束符，默认值为'\0'。
- ·kafka_schema：对应Kafka的schema参数。
- ·kafka_num_consumers：表示消费者的数量，默认值为1。表引擎会依据此参数在消费组中开启相应数量的消费者线程。在Kafka的主题中，一个Partition分区只能使用一个消费者。
- ·kafka_skip_broken_messages：当表引擎按照预定格式解析数据出现错误时，允许跳过失败的数据行数，默认值为0，即不允许任何格式错误的情形发生。在此种情形下，只要Kafka主题中存在无法解析的数据，数据表都将不会接收任何数据。如果将其设置为非0正整数，例如kafka_skip_broken_messages=10，表示只要Kafka主题中存在无法解析的数据的总数小于10，数据表就能正常接收消息数据，而解析错误的数据会被自动跳过。
- ·kafka_commit_every_batch：表示执行Kafka commit的频率，默认值为0，即当一整个Block数据块完全写入数据表后才执行Kafka commit。如果将其设置为1，则每写完一个Batch批次的数据就会执行一次Kafka commit（一次Block写入操作，由多次Batch写入操作组成）。
除此之外，还有一些配置参数可以调整表引擎的行为。在默认情况下，Kafka表引擎每间隔500毫秒会拉取一次数据，时间由stream_poll_timeout_ms参数控制（默认500毫秒）。数据首先会被放入缓存，在时机成熟的时候，缓存数据会被刷新到数据表。
触发Kafka表引擎刷新缓存的条件有两个，当满足其中的任意一个时，便会触发刷新动作：
- ·当一个数据块完成写入的时候（一个数据块的大小由kafka_max_block_size参数控制，默认情况下kafka_max_block_size=max_block_size=65536）。
- ·等待间隔超过7500毫秒，由stream_flush_interval_ms参数控制（默认7500 ms）。
6.1.5 File
File表引擎的声明方式如下所示：
ENGINE = File(format)
其中，format表示文件中的数据格式，其类型必须是ClickHouse支持的数据格式，例如TSV、CSV和JSONEachRow等。可以发现，在File表引擎的定义参数中，并没有包含文件路径这一项。所以，File表引擎的数据文件只能保存在config.xml配置中由path指定的路径下。
每张File数据表均由目录和文件组成，其中目录以表的名称命名，而数据文件则固定以data.format命名，例如：
<ch-path>/data/default/test_file_table/data.CSV
创建File表目录和文件的方式有自动和手动两种。首先介绍自动创建的方式，即由File表引擎全权负责表目录和数据文件的创建：
CREATE TABLE file_table (
name String,
value UInt32
) ENGINE = File("CSV")
当执行完上面的语句后，在
INSERT INTO file_table VALUES ('one', 1), ('two', 2), ('three', 3)
在数据写入之后，file_table目录下便会生成一个名为data.CSV的数据文件：
# pwd
/chbase/data/default/file_table
# cat ./data.CSV
"one",1
"two",2
"three",3
接下来介绍手动创建的形式，即表目录和数据文件由ClickHouse之外的其他系统创建，例如使用shell创建：
//切换到clickhouse用户，以确保ClickHouse有权限读取目录和文件
# su clickhouse
//创建表目录
# mkdir /chbase/data/default/file_table1
//创建数据文件
# mv /chbase/data/default/file_table/data.CSV /chbase/data/default/file_table1
在表目录和数据文件准备妥当之后，挂载这张数据表：
ATTACH TABLE file_table1(
name String,
value UInt32
)ENGINE = File(CSV)
查询file_table1内的数据：
SELECT * FROM file_table1
┌─name──┬─value─┐
│ one │ 1 │
│ two │ 2 │
│ three │ 3 │
└─────┴─────┘
INSERT INTO file_table1 VALUES ('four', 4), ('five', 5) --即便是手动创建的表目录和数据文件，仍然可以对数据表插入数据，例如：
6.2 内存类型
6.2.1 Memory
Memory表的创建方法如下所示：
CREATE TABLE memory_1 (
id UInt64
)ENGINE = Memory()
6.2.2 Set
Set表引擎是拥有物理存储的，数据首先会被写至内存，然后被同步到磁盘文件中。所以当服务重启时，它的数据不会丢失，当数据表被重新装载时，文件数据会再次被全量加载至内存。众所周知，在Set数据结构中，所有元素都是唯一的。Set表引擎具有去重的能力，在数据写入的过程中，重复的数据会被自动忽略。然而Set表引擎的使用场景既特殊又有限，它虽然支持正常的INSERT写入，但并不能直接使用SELECT对其进行查询，Set表引擎只能间接作为IN查询的右侧条件被查询使用。
CREATE TABLE set_1 (
id UInt8
)ENGINE = Set()
6.2.3 Join
Join表引擎可以说是为JOIN查询而生的，它等同于将JOIN查询进行了一层简单封装。在Join表引擎的底层实现中，它与Set表引擎共用了大部分的处理逻辑，所以Join和Set表引擎拥有许多相似之处。例如，Join表引擎的存储也由[num].bin数据文件和tmp临时目录两部分组成；数据首先会被写至内存，然后被同步到磁盘文件。但是相比Set表引擎，Join表引擎有着更加广泛的应用场景，它既能够作为JOIN查询的连接表，也能够被直接查询使用。
ENGINE = Join(join_strictness, join_type, key1[, key2, ...])
其中，各参数的含义分别如下：
- ·join_strictness：连接精度，它决定了JOIN查询在连接数据时所使用的策略，目前支持ALL、ANY和ASOF三种类型。
- ·join_type：连接类型，它决定了JOIN查询组合左右两个数据集合的策略，它们所形成的结果是交集、并集、笛卡儿积或其他形式，目前支持INNER、OUTER和CROSS三种类型。当join_type被设置为ANY时，在数据写入时，join_key重复的数据会被自动忽略。
- ·join_key：连接键，它决定了使用哪个列字段进行关联。
6.2.4 Buffer
Buffer表引擎完全使用内存装载数据，不支持文件的持久化存储，所以当服务重启之后，表内的数据会被清空。Buffer表引擎不是为了面向查询场景而设计的，它的作用是充当缓冲区的角色。假设有这样一种场景，我们需要将数据写入目标MergeTree表A，由于写入的并发数很高，这可能会导致MergeTree表A的合并速度慢于写入速度（因为每一次INSERT都会生成一个新的分区目录）。此时，可以引入Buffer表来缓解这类问题，将Buffer表作为数据写入的缓冲区。数据首先被写入Buffer表，当满足预设条件时，Buffer表会自动将数据刷新到目标表。
Buffer表引擎的声明方式如下所示：
ENGINE = Buffer(database, table, num_layers, min_time, max_time, min_rows, max_rows, min_bytes, max_bytes)
其中，参数可以分成基础参数和条件参数两类，首先说明基础参数的作用：
- database：目标表的数据库。
- ·table：目标表的名称，Buffer表内的数据会自动刷新到目标表。
- ·num_layers：可以理解成线程数，Buffer表会按照num_layers的数量开启线程，以并行的方式将数据刷新到目标表，官方建议设为16。
- ·min_time和max_time：时间条件的最小和最大值，单位为秒，从第一次向表内写入数据的时候开始计算；
- 假设一张Buffer表的max_bytes=100000000（约100 MB），num_layers=16，那么这张Buffer表能够同时处理的最大数据量约是1.6 GB。
6.3 接口类型
有这么一类表引擎，它们自身并不存储任何数据，而是像黏合剂一样可以整合其他的数据表。在使用这类表引擎的时候，不用担心底层的复杂性，它们就像接口一样，为用户提供了统一的访问界面，所以我将它们归为接口类表引擎。
6.3.1 Merge
Merge表引擎就如同一层使用了门面模式的代理，它本身不存储任何数据，也不支持数据写入。它的作用就如其名，即负责合并多个查询的结果集。Merge表引擎可以代理查询任意数量的数据表，这些查询会异步且并行执行，并最终合成一个结果集返回。
ENGINE = Merge(database, table_name) --其中：database表示数据库名称；table_name表示数据表的名称，它支持使用正则表达式，例如^test表示合并查询所有以test为前缀的数据表。
CREATE TABLE test_table_all as test_table_2018
ENGINE = Merge(currentDatabase(), '^test_table_')
6.3.2 Dictionary
Dictionary表引擎是数据字典的一层代理封装，它可以取代字典函数，让用户通过数据表查询字典。字典内的数据被加载后，会全部保存到内存中，所以使用Dictionary表对字典性能不会有任何影响。声明Dictionary表的方式如下所示：
ENGINE = Dictionary(dict_name)
CREATE TABLE tb_test_flat_dict ( --其中，dict_name对应一个已被加载的字典名称，例如下面的例子：
id UInt64,
code String,
name String
)Engine = Dictionary(test_flat_dict);
SELECT * FROM tb_test_flat_dict --tb_test_flat_dict等同于数据字典test_flat_dict的代理表，现在对它使用SELECT语句进行查询：
┌─id─┬─code──┬─name─┐
│ 1 │ a0001 │ 研发部 │
│ 2 │ a0002 │ 产品部 │
│ 3 │ a0003 │ 数据部 │
│ 4 │ a0004 │ 测试部 │
└───┴─────┴────┘
第7章 副本与分片
10.4 数据分片
10.4.1 集群的配置方式
1.不包含副本的分片
如果直接使用node标签定义分片节点，那么该集群将只包含分片，不包含副本。以下面的配置为例：
<yandex>
<!--自定义配置名，与config.xml配置的incl属性对应即可 -->
<clickhouse_remote_servers>
<shard_2><!--自定义集群名称-->
<node><!--定义ClickHouse节点-->
<host>ch5.nauu.com</host>
<port>9000</port>
<!--选填参数
<weight>1</weight>
<user></user>
<password></password>
<secure></secure>
<compression></compression>
-->
</node>
<node>
<host>ch6.nauu.com</host>
<port>9000</port>
</node>
</shard_2>
……
</clickhouse_remote_servers>
该配置定义了一个名为shard_2的集群，其包含了2个分片节点，它们分别指向了是CH5和CH6服务器。现在分别对配置项进行说明：
- ·shard_2表示自定义的集群名称，全局唯一，是后续引用集群配置的唯一标识。在一个配置文件内，可以定义任意组集群。
- ·node用于定义分片节点，不包含副本。
- ·host指定部署了ClickHouse节点的服务器地址。
- ·port指定ClickHouse服务的TCP端口。
- ·weight分片权重默认为1，在后续小节中会对其详细介绍。
- ·user为ClickHouse用户，默认为default。
- ·password为ClickHouse的用户密码，默认为空字符串。
- ·secure为SSL连接的端口，默认为9440。
- ·compression表示是否开启数据压缩功能，默认为true。
2.自定义分片与副本
1）不包含副本的分片
<!-- 2个分片、0个副本 -->
<sharding_simple> <!-- 自定义集群名称 -->
<shard> <!-- 分片 -->
<replica> <!-- 副本 -->
<host>ch5.nauu.com</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>ch6.nauu.com</host>
<port>9000</port>
</replica>
</shard>
</sharding_simple>
2）N个分片和N个副本
<!-- 1个分片 1个副本-->
<sharding_simple_1>
<shard>
<replica>
<host>ch5.nauu.com</host>
<port>9000</port>
</replica>
<replica>
<host>ch6.nauu.com</host>
<port>9000</port>
</replica>
</shard>
</sharding_simple_1>
3）下面所示集群sharding_ha拥有2个分片，而每个分片拥有1个副本：
<sharding_ha>
<shard>
<replica>
<host>ch5.nauu.com</host>
<port>9000</port>
</replica>
<replica>
<host>ch6.nauu.com</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>ch7.nauu.com</host>
<port>9000</port>
</replica>
<replica>
<host>ch8.nauu.com</host>
<port>9000</port>
</replica>
</shard>
</sharding_ha>
在完成上述配置之后，可以查询系统表验证集群配置是否已被加载：
SELECT cluster, host_name FROM system.clusters
┌─cluster────────┬─host_name──┐
│ shard_2 │ ch5.nauu.com │
│ shard_2 │ ch6.nauu.com │
│ sharding_simple │ ch5.nauu.com │
│ sharding_simple │ ch6.nauu.com │
│ sharding_simple_1 │ ch5.nauu.com │
│ sharding_simple_1 │ ch6.nauu.com │
└─────────────┴─────────┘
10.4.2 基于集群实现分布式DDL
在加入集群配置后，就可以使用新的语法实现分布式DDL执行了，其语法形式如下：
CREATE/DROP/RENAME/ALTER TABLE ON CLUSTER cluster_name
其中，cluster_name对应了配置文件中的集群名称，ClickHouse会根据集群的配置信息顺藤摸瓜，分别去各个节点执行DDL语句。
CREATE TABLE test_1_local ON CLUSTER shard_2(
id UInt64
--这里可以使用任意其他表引擎，
)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/test_1', '{replica}')
ORDER BY id
┌─host──────┬─port─┬─status─┬─error─┬─num_hosts_active─┐
│ ch6.nauu.com │ 9000 │ 0 │ │ 0 │
│ ch5.nauu.com │ 9000 │ 0 │ │ 0 │
└─────────┴────┴──────┴─────┴───────────┘
如果要删除test_1_local，则执行下面的分布式DROP：
DROP TABLE test_1_local ON CLUSTER shard_2
┌─host──────┬─port─┬─status─┬─error─┬─num_hosts_active─┐
│ ch6.nauu.com │ 9000 │ 0 │ │ 0 │
│ ch5.nauu.com │ 9000 │ 0 │ │ 0 │
└─────────┴────┴──────┴─────┴───────────┘
在CH5节点的config.xml配置中预先定义了分区01的宏变量：
<macros>
<shard>01</shard>
<replica>ch5.nauu.com</replica>
</macros>
在CH6节点的config.xml配置中预先定义了分区02的宏变量：
<macros>
<shard>02</shard>
<replica>ch6.nauu.com</replica>
</macros>
1.数据结构
1）ZooKeeper内的节点结构
在默认情况下，分布式DDL在ZooKeeper内使用的根路径为：
/clickhouse/task_queue/ddl
该路径由config.xml内的distributed_ddl配置指定：
<distributed_ddl>
<!-- Path in ZooKeeper to queue with DDL queries -->
<path>/clickhouse/task_queue/ddl</path>
</distributed_ddl>
2）DDLLogEntry日志对象的数据结构
在/query-[seq]下记录的日志信息由DDLLogEntry承载，它拥有如下几个核心属性：
（1）query记录了DDL查询的执行语句，例如：
query: DROP TABLE default.test_1_local ON CLUSTER shard_2
（2）hosts记录了指定集群的hosts主机列表，集群由分布式DDL语句中的ON CLUSTER指定，例如：
hosts: ['ch5.nauu.com:9000','ch6.nauu.com:9000']
（3）initiator记录初始化host主机的名称，hosts主机列表的取值来自于初始化host节点上的集群，例如：
initiator: ch5.nauu.com:9000
2.分布式DDL的核心执行流程
10.5 Distributed原理解析
Distributed表引擎是分布式表的代名词，它自身不存储任何数据，而是作为数据分片的透明代理，能够自动路由数据至集群中的各个节点，所以Distributed表引擎需要和其他数据表引擎一起协同工作。
从实体表层面来看，一张分片表由两部分组成：
- ·本地表：通常以_local为后缀进行命名。本地表是承接数据的载体，可以使用非Distributed的任意表引擎，一张本地表对应了一个数据分片。
- ·分布式表：通常以_all为后缀进行命名。分布式表只能使用Distributed表引擎，它与本地表形成一对多的映射关系，日后将通过分布式表代理操作多张本地表。
10.5.1 定义形式
Distributed表引擎的定义形式如下所示：
ENGINE = Distributed(cluster, database, table [,sharding_key])
CREATE TABLE test_shard_2_all ON CLUSTER sharding_simple (
id UInt64
)ENGINE = Distributed(sharding_simple, default, test_shard_2_local,rand())
其中，各个参数的含义分别如下：
- ·cluster：集群名称，与集群配置中的自定义名称相对应。在对分布式表执行写入和查询的过程中，它会使用集群的配置信息来找到相应的host节点。
- ·database和table：分别对应数据库和表的名称，分布式表使用这组配置映射到本地表。
- ·sharding_key：分片键，选填参数。在数据写入的过程中，分布式表会依据分片键的规则，将数据分布到各个host节点的本地表。
10.5.3 分片规则
关于分片的规则这里将做进一步的展开说明。分片键要求返回一个整型类型的取值，包括Int系列和UInt系列。例如分片键可以是一个具体的整型列字段：
--按照用户id的余数划分
Distributed(cluster, database, table ,userid)
--按照随机数划分
Distributed(cluster, database, table ,rand())
--按照用户id的散列值划分
Distributed(cluster, database, table , intHash64(userid))
10.5.4 分布式写入的核心流程
在向集群内的分片写入数据时，通常有两种思路：一种是借助外部计算系统，事先将数据均匀分片，再借由计算系统直接将数据写入ClickHouse集群的各个本地表，如图10-15所示。
第二种思路是通过Distributed表引擎代理写入分片数据的，接下来开始介绍数据写入的核心流程。
为了便于理解整个过程，这里会将分片写入、副本复制拆分成两个部分进行讲解。在讲解过程中，会使用两个特殊的集群分别进行演示：第一个集群拥有2个分片和0个副本，通过这个示例向大家讲解分片写入的核心流程；第二个集群拥有1个分片和1个副本，通过这个示例向大家讲解副本复制的核心流程。
1.将数据写入分片的核心流程
10.5.5 分布式查询的核心流程
1.多副本的路由规则
在查询数据的时候，如果集群中的一个shard，拥有多个replica，那么Distributed表引擎需要面临副本选择的问题。它会使用负载均衡算法从众多replica中选择一个，而具体使用何种负载均衡算法，则由load_balancing参数控制：
load_balancing = random/nearest_hostname/in_order/first_or_random
1）random
random是默认的负载均衡算法，正如前文所述，在ClickHouse的服务节点中，拥有一个全局计数器errors_count，当服务发生任何异常时，该计数累积加1。而random算法会选择errors_count错误数量最少的replica，如果多个replica的errors_count计数相同，则在它们之中随机选择一个。
2）nearest_hostname
nearest_hostname可以看作random算法的变种，首先它会选择errors_count错误数量最少的replica，如果多个replica的errors_count计数相同，则选择集群配置中host名称与当前host最相似的一个。而相似的规则是以当前host名称为基准按字节逐位比较，找出不同字节数最少的一个，例如CH5-1-1和CH5-1-2.nauu.com有一个字节不同：
3）in_order
in_order同样可以看作random算法的变种，首先它会选择errors_count错误数量最少的replica，如果多个replica的errors_count计数相同，则按照集群配置中replica的定义顺序逐个选择。
4）first_or_random
first_or_random可以看作in_order算法的变种，首先它会选择errors_count错误数量最少的replica，如果多个replica的errors_count计数相同，它首先会选择集群配置中第一个定义的replica，如果该replica不可用，则进一步随机选择一个其他的replica。
第11章 管理与运维
11.1 用户配置
11.1.3 用户定义
1.username
username用于指定登录用户名，这是全局唯一属性。该属性比较简单，这里就不展开介绍了。
2.password
（1）明文密码：在使用明文密码的时候，直接通过password标签定义，例如下面的代码。
<password>123</password>
<password></password> !--如果password为空，则表示免密码登录--
（2）SHA256加密：在使用SHA256加密算法的时候，需要通过password_sha256_hex标签定义密码，例如下面的代码。
<password_sha256_hex>a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3</password_sha256_hex>
可以执行下面的命令获得密码的加密串，例如对明文密码123进行加密：
# echo -n 123 | openssl dgst -sha256
(stdin)= a665a45920422f9d417e4867efdc4fb8a04a1f3fff1fa07e998e86f7f7a27ae3
（3）double_sha1加密：在使用double_sha1加密算法的时候，则需要通过password_double_sha1_hex标签定义密码，例如下面的代码。
<password_double_sha1_hex>23ae809ddacaf96af0fd78ed04b6a265e05aa257</password_double_sha1_hex>
可以执行下面的命令获得密码的加密串，例如对明文密码123进行加密：
# echo -n 123 | openssl dgst -sha1 -binary | openssl dgst -sha1
(stdin)= 23ae809ddacaf96af0fd78ed04b6a265e05aa257
3.networks
4.profile
用户所使用的profile配置，直接引用相应的名称即可，例如：
<default>
<profile>default</profile>
</default>
5.quota
quota用于设置该用户能够使用的资源限额，可以理解成一种熔断机制。关于这方面的介绍将会在11.3节展开。
<yandex>
<profiles>
……
</profiles>
<users>
<default><!—默认用户 -->
……
</default>
<user_plaintext>
<password>123</password>
<networks>
<ip>::/0</ip>
</networks>
<profile>normal_1</profile>
<quota>default</quota>
</user_plaintext>
由于配置了密码，所以在登录的时候需要附带密码参数：
# clickhouse-client -h 10.37.129.10 -u user_plaintext --password 123
Connecting to 10.37.129.10:9000 as user user_plaintext.
11.2 权限管理
11.2.1 访问权限
1.网络访问权限
（1）IP地址：直接使用IP地址进行设置。
<ip>127.0.0.1</ip>
（2）host主机名称：通过host主机名称设置。
<host>ch5.nauu.com</host>
（3）正则匹配：通过表达式来匹配host名称。
<host>^ch\d.nauu.com$</host>
2.数据库与字典访问权限
在客户端连入服务之后，可以进一步限制某个用户数据库和字典的访问权限，它们分别通过allow_databases和allow_dictionaries标签进行设置。如果不进行任何定义，则表示不进行限制。现在继续在用户user_normal的定义中增加权限配置：
<user_normal>
……
<allow_databases>
<database>default</database>
<database>test_dictionaries</database>
</allow_databases>
<allow_dictionaries>
<dictionary>test_flat_dict</dictionary>
</allow_dictionaries>
</user_normal>
11.2.2 查询权限
查询权限是整个权限体系的第二层防护，它决定了一个用户能够执行的查询语句。查询权限可以分成以下四类：
- ·读权限：包括SELECT、EXISTS、SHOW和DESCRIBE查询。
- ·写权限：包括INSERT和OPTIMIZE查询。
- ·设置权限：包括SET查询。
- ·DDL权限：包括CREATE、DROP、ALTER、RENAME、ATTACH、DETACH和TRUNCATE查询。
上述这四类权限，通过以下两项配置标签控制：
（1）readonly：读权限、写权限和设置权限均由此标签控制，它有三种取值。
- ·当取值为0时，不进行任何限制（默认值）。
- ·当取值为1时，只拥有读权限（只能执行SELECT、EXISTS、SHOW和DESCRIBE）。
- ·当取值为2时，拥有读权限和设置权限（在读权限基础上，增加了SET查询）。
（2）allow_ddl：DDL权限由此标签控制，它有两种取值。
- ·当取值为0时，不允许DDL查询。
- ·当取值为1时，允许DDL查询（默认值）。
现在继续用一个示例说明。与刚才的配置项不同，readonly和allow_ddl需要定义在用户profiles中，例如：
<profiles>
<normal> <!-- 只有read读权限-->
<readonly>1</readonly>
<allow_ddl>0</allow_ddl>
</normal>
<normal_1> <!-- 有读和设置参数权限-->
<readonly>2</readonly>
<allow_ddl>0</allow_ddl>
</normal_1>
11.2.3 数据行级权限
数据权限是整个权限体系中的第三层防护，它决定了一个用户能够看到什么数据。数据权限使用databases标签定义，它是用户定义中的一项选填设置。database通过定义用户级别的查询过滤器来实现数据的行级粒度权限，它的定义规则如下所示：
<databases>
<database_name><!--数据库名称-->
<table_name><!--表名称-->
<filter> id < 10</filter><!--数据过滤条件-->
</table_name>
</database_name>
其中，database_name表示数据库名称；table_name表示表名称；而filter则是权限过滤的关键所在，它等同于定义了一条WHERE条件子句，与WHERE子句类似，它支持组合条件。现在用一个示例说明。这里还是用user_normal，为它追加databases定义：
<user_normal>
……
<databases>
<default><!--默认数据库-->
<test_row_level><!—表名称-->
<filter>id < 10</filter>
</test_row_level>
<!—支持组合条件
<test_query_all>
<filter>id <= 100 or repo >= 100</filter>
</test_query_all> -->
</default>
</databases>
11.3 熔断机制
1.根据时间周期的累积用量熔断
在这种方式下，系统资源的用量是按照时间周期累积统计的，当累积量达到阈值，则直到下个计算周期开始之前，该用户将无法继续进行操作。这种方式通过users.xml内的quotas标签来定义资源配额。以下面的配置为例：
<quotas>
<default> <!-- 自定义名称 -->
<interval>
<duration>3600</duration><!-- 时间周期 单位：秒 -->
<queries>0</queries>
<errors>0</errors>
<result_rows>0</result_rows>
<read_rows>0</read_rows>
<execution_time>0</execution_time>
</interval>
</default>
</quotas>
其中，各配置项的含义如下：
- ·default：表示自定义名称，全局唯一。
- ·duration：表示累积的时间周期，单位是秒。
- ·queries：表示在周期内允许执行的查询次数，0表示不限制。
- ·errors：表示在周期内允许发生异常的次数，0表示不限制。
- ·result_row：表示在周期内允许查询返回的结果行数，0表示不限制。
- ·read_rows：表示在周期内在分布式查询中，允许远端节点读取的数据行数，0表示不限制。
- ·execution_time：表示周期内允许执行的查询时间，单位是秒，0表示不限制。
2.根据单次查询的用量熔断
首先介绍一组针对普通查询的熔断配置。
（1）max_memory_usage：在单个ClickHouse服务进程中，运行一次查询限制使用的最大内存量，默认值为10 GB，其配置形式如下。
<max_memory_usage>10000000000</max_memory_usage>
（2）max_memory_usage_for_user：在单个ClickHouse服务进程中，以用户为单位进行统计，单个用户在运行查询时限制使用的最大内存量，默认值为0，即不做限制。
（3）max_memory_usage_for_all_queries：在单个ClickHouse服务进程中，所有运行的查询累加在一起所限制使用的最大内存量，默认为0，即不做限制。
（4）max_partitions_per_insert_block：在单次INSERT写入的时候，限制创建的最大分区个数，默认值为100个。如果超出这个阈值，将会出现如下异常：
（5）max_rows_to_group_by：在执行GROUP BY聚合查询的时候，限制去重后聚合
（6）group_by_overflow_mode：当max_rows_to_group_by熔断规则触发时，group_by_overflow_mode将会提供三种处理方式。
（7）max_bytes_before_external_group_by：在执行GROUP BY聚合查询的时候，限制使用的最大内存量，默认值为0，即不做限制。当超过阈值时，聚合查询将会进一步借用本地磁盘。
11.4 数据备份
11.4.1 导出文件备份
如果数据的体量较小，可以通过dump的形式将数据导出为本地文件。例如执行下面的语句将test_backup的数据导出：
#clickhouse-client --query="SELECT * FROM test_backup" > /chbase/test_backup.tsv
# cat /chbase/test_backup.tsv | clickhouse-client --query "INSERT INTO test_backup FORMAT TSV" --将备份数据再次导入，则可以执行下面的语句：
上述这种dump形式的优势在于，可以利用SELECT查询并筛选数据，然后按需备份。如果是备份整个表的数据，也可以直接复制它的整个目录文件，例如：
# mkdir -p /chbase/backup/default/ & cp -r /chbase/data/default/test_backup /chbase/backup/default/
11.4.2 通过快照表备份
快照表实质上就是普通的数据表，它通常按照业务规定的备份频率创建，例如按天或者按周创建。所以首先需要建立一张与原表结构相同的数据表，然后再使用INSERT INTO SELECT句式，点对点地将数据从原表写入备份表。假设数据表test_backup需要按日进行备份，现在为它创建当天的备份表：
CREATE TABLE test_backup_0206 AS test_backup
有了备份表之后，就可以点对点地备份数据了，例如：
INSERT INTO TABLE test_backup_0206 SELECT * FROM test_backup
如果考虑到容灾问题，也可以将备份表放置在不同的ClickHouse节点上，此时需要将上述SQL语句改成远程查询的形式：
INSERT INTO TABLE test_backup_0206 SELECT * FROM remote('ch5.nauu.com:9000', 'default', 'test_backup', 'default')
11.4.3 按分区备份
1.使用FREEZE备份
FREEZE的完整语法如下所示：
ALTER TABLE tb_name FREEZE PARTITION partition_expr
分区在被备份之后，会被统一保存到ClickHouse根路径/shadow/N子目录下。其中，N是一个自增长的整数，它的含义是备份的次数（FREEZE执行过多少次），具体次数由shadow子目录下的increment.txt文件记录。而分区备份实质上是对原始目录文件进行硬链接操作，所以并不会导致额外的存储空间。整个备份的目录会一直向上追溯至data根路径的整个链路：
/data/[database]/[table]/[partition_folder]
例如执行下面的语句，会对数据表partition_v2的201908分区进行备份：
:) ALTER TABLE partition_v2 FREEZE PARTITION 201908
进入shadow子目录，即能够看到刚才备份的分区目录：
# pwd
/chbase/data/shadow/1/data/default/partition_v2
# ll
total 4
drwxr-x---. 2 clickhouse clickhouse 4096 Sep 1 00:22 201908_5_5_0
对于备份分区的还原操作，则需要借助ATTACH装载分区的方式来实现。这意味着如果要还原数据，首先需要主动将shadow子目录下的分区文件复制到相应数据表的detached目录下，然后再使用ATTACH语句装载。
11.5 服务监控
11.5.1 系统表
1.metrics
metrics表用于统计ClickHouse服务在运行时，当前正在执行的高层次的概要信息，包括正在执行的查询总次数、正在发生的合并操作总次数等。该系统表的查询方法如下所示：
SELECT * FROM system.metrics LIMIT 5
┌─metric──────┬─value─┬─description─────────────────────┐
│ Query │ 1 │ Number of executing queries │
│ Merge │ 0 │ Number of executing background merges │
│ PartMutation │ 0 │ Number of mutations (ALTER DELETE/UPDATE) │
│ ReplicatedFetch │ 0 │ Number of data parts being fetched from replica │
│ ReplicatedSend │ 0 │ Number of data parts being sent to replicas │
└──────────┴─────┴─────────────────────────────┘
2.events
events用于统计ClickHouse服务在运行过程中已经执行过的高层次的累积概要信息，包括总的查询次数、总的SELECT查询次数等，该系统表的查询方法如下所示：
SELECT event, value FROM system.events LIMIT 5
┌─event─────────────────────┬─value─┐
│ Query │ 165 │
│ SelectQuery │ 92 │
│ InsertQuery │ 14 │
│ FileOpen │ 3525 │
│ ReadBufferFromFileDescriptorRead │ 6311 │
└─────────────────────────┴─────┘
3.asynchronous_metrics
asynchronous_metrics用于统计ClickHouse服务运行过程时，当前正在后台异步运行的高层次的概要信息，包括当前分配的内存、执行队列中的任务数量等。该系统表的查询方法如下所示：
SELECT * FROM system.asynchronous_metrics LIMIT 5
┌─metric───────────────────────┬─────value─┐
│ jemalloc.background_thread.run_interval │ 0 │
│ jemalloc.background_thread.num_runs │ 0 │
│ jemalloc.background_thread.num_threads │ 0 │
│ jemalloc.retained │ 79454208 │
│ jemalloc.mapped │ 531341312 │
└────────────────────────────┴──────────┘
11.5.2 查询日志
查询日志目前主要有6种类型，它们分别从不同角度记录了ClickHouse的操作行为。所有查询日志在默认配置下都是关闭状态，需要在config.xml配置中进行更改，接下来分别介绍它们的开启方法。在配置被开启之后，ClickHouse会为每种类型的查询日志自动生成相应的系统表以供查询。
1.query_log
query_log是最常用的查询日志，它记录了ClickHouse服务中所有已经执行的查询记录，它的全局定义方式如下所示：
<query_log>
<database>system</database>
<table>query_log</table>
<partition_by>toYYYYMM(event_date)</partition_by>
<!—刷新周期-->
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_log>
如果只需要为某些用户单独开启query_log，也可以在user.xml的profile配置中按照下面的方式定义：
<log_queries> 1</log_queries>
query_log开启后，即可以通过相应的系统表对记录进行查询：
SELECT type,concat(substr(query,1,20),'...')query,read_rows,
query_duration_ms AS duration FROM system.query_log LIMIT 6
┌─type──────────┬─query───────────┬─read_rows─┬─duration─┐
│ QueryStart │ SELECT DISTINCT arra... │ 0 │ 0 │
│ QueryFinish │ SELECT DISTINCT arra... │ 2432 │ 11 │
│ QueryStart │ SHOW DATABASES... │ 0 │ 0 │
│ QueryFinish │ SHOW DATABASES... │ 3 │ 1 │
│ ExceptionBeforeStart │ SELECT * FROM test_f... │ 0 │ 0 │
│ ExceptionBeforeStart │ SELECT * FROM test_f... │ 0 │ 0 │
└─────────────┴───────────────┴───────┴───────┘
2.query_thread_log
query_thread_log记录了所有线程的执行查询的信息，它的全局定义方式如下所示：
<query_thread_log>
<database>system</database>
<table>query_thread_log</table>
<partition_by>toYYYYMM(event_date)</partition_by>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</query_thread_log>
同样，如果只需要为某些用户单独开启该功能，可以在user.xml的profile配置中按照下面的方式定义：
<log_query_threads> 1</log_query_threads>
query_thread_log开启后，即可以通过相应的系统表对记录进行查询：
SELECT thread_name,concat(substr(query,1,20),'...')query,query_duration_ms AS duration,memory_usage AS memory FROM system.query_thread_log LIMIT 6
┌─thread_name───┬─query───────────┬─duration─┬─memory─┐
│ ParalInputsProc │ SELECT DISTINCT arra... │ 2 │ 210888 │
│ ParalInputsProc │ SELECT DISTINCT arra... │ 3 │ 252648 │
│ AsyncBlockInput │ SELECT DISTINCT arra... │ 3 │ 449544 │
│ TCPHandler │ SELECT DISTINCT arra... │ 11 │ 0 │
│ TCPHandler │ SHOW DATABASES... │ 2 │ 0 │
└──────────┴───────────────┴───────┴──────┘
3.part_log
part_log日志记录了MergeTree系列表引擎的分区操作日志，其全局定义方式如下所示：
<part_log>
<database>system</database>
<table>part_log</table>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</part_log>
part_log开启后，即可以通过相应的系统表对记录进行查询：
SELECT event_type AS type,table,partition_id,event_date FROM system.part_log
┌─type────┬─table─────────────┬─partition_id─┬─event_date─┐
│ NewPart │ summing_table_nested_v1 │ 201908 │ 2020-01-29 │
│ NewPart │ summing_table_nested_v1 │ 201908 │ 2020-01-29 │
│ MergeParts │ summing_table_nested_v1 │ 201908 │ 2020-01-29 │
│ RemovePart │ ttl_table_v1 │ 201505 │ 2020-01-29 │
│ RemovePart │ summing_table_nested_v1 │ 201908 │ 2020-01-29 │
│ RemovePart │ summing_table_nested_v1 │ 201908 │ 2020-01-29 │
└───────┴─────────────────┴─────────┴────────┘
4.text_log
text_log日志记录了ClickHouse运行过程中产生的一系列打印日志，包括INFO、DEBUG和Trace，它的全局定义方式如下所示：
<text_log>
<database>system</database>
<table>text_log</table>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
</text_log>
text_log开启后，即可以通过相应的系统表对记录进行查询：
SELECT thread_name,
concat(substr(logger_name,1,20),'...')logger_name,
concat(substr(message,1,20),'...')message
FROM system.text_log LIMIT 5
┌─thread_name──┬─logger_name───────┬─message────────────┐
│ SystemLogFlush │ SystemLog (system.me... │ Flushing system log... │
│ SystemLogFlush │ SystemLog (system.te... │ Flushing system log... │
│ SystemLogFlush │ SystemLog (system.te... │ Creating new table s... │
│ SystemLogFlush │ system.text_log... │ Loading data parts... │
│ SystemLogFlush │ system.text_log... │ Loaded data parts (0... │
└──────────┴───────────────┴─────────────────┘
5.metric_log
metric_log日志用于将system.metrics和system.events中的数据汇聚到一起，它的全局定义方式如下所示：
<metric_log>
<database>system</database>
<table>metric_log</table>
<flush_interval_milliseconds>7500</flush_interval_milliseconds>
<collect_interval_milliseconds>1000</collect_interval_milliseconds>
</metric_log>
其中，collect_interval_milliseconds表示收集metrics和events数据的时间周期。metric_log开启后，即可以通过相应的系统表对记录进行查询。