ClickHouse 中文文档下载：https://www.modb.pro/doc/37395

什么是ClickHouse？

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。在传统的行式数据库系统中，数据按如下顺序存储：

处于同一行中的数据总是被物理的存储在一起。

常见的行式数据库系统有：MySQL、Postgres和MS SQL Server。在列式数据库系统中，数据按如下的顺序存储：

这些示例只显示了数据的排列顺序。来自不同列的值被单独存储，来自同一列的数据被存储在一起。

常见的列式数据库有： Vertica、 Paraccel (Actian Matrix，Amazon Redshift)、 Sybase IQ、 Exasol、 Infobright、 InfiniDB、 MonetDB (VectorWise， Actian Vector)、 LucidDB、 SAP HANA、 Google Dremel、 Google PowerDrill、 Druid、 kdb+。

不同的数据存储方式适用不同的业务场景，数据访问的场景包括：进行了何种查询、多久查询一次以及各类查询的比例；每种类型的查询(行、列和字节)读取多少数据；读取数据 和更新之间的关系；使用的数据集大小以及如何使用本地的数据集；是否使用事务,以及它们是如何进行隔离的；数据的复制机制与数据的完整性要求；每种类型的查询要求的延 迟与吞吐量等等。

系统负载越高，依据使用场景进行定制化就越重要，并且定制将会变的越精细。没有一个系统能够同时适用所有不同的业务场景。如果系统适用于广泛的场景，在负载高的情况 下，要兼顾所有的场景，那么将不得不做出选择。是要平衡还是要效率？

OLAP场景的关键特征

绝大多数是读请求

数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。已添加到数据库的数据不能修改。

对于读取，从数据库中提取相当多的行，但只提取列的一小部分。 宽表，即每个表包含着大量的列

查询相对较少(通常每台服务器每秒查询数百次或更少) 对于简单查询，允许延迟大约50毫秒

列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)

处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行) 事务不是必须的

对数据一致性要求低

每个查询有一个大表。除了他以外，其他的都很小。

查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

很容易可以看出，OLAP场景与其他通常业务场景(例如,OLTP或K/V)有很大的不同， 因此想要使用OLTP或Key-Value数据库去高效的处理分析查询场景，并不是非常完美的适用方案。例如，使用OLAP数据库去处理分析请求通常要优于使用MongoDB或Redis去处理分析请求。

列式数据库更适合OLAP场景的原因

列式数据库更适合于OLAP场景(对于大多数查询而言，处理速度至少提高了100倍)，下面详细解释了原因(通过图片更有利于直观理解)： 行式

列式

看到差别了么？下面将详细介绍为什么会发生这种情况。 输入/输出

1. 针对分析类查询，通常只需要读取表的一小部分列。在列式数据库中你可以只读取你需要的数据。例如，如果只需要读取100列中的5列，这将帮助你最少减少20倍的I/O消耗。
2. 由于数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储这也更容易压缩。这进一步降低了I/O的体积。
3. 由于I/O的降低，这将帮助更多的数据被系统缓存。

例如，查询«统计每个广告平台的记录数量»需要读取«广告平台ID»这一列，它在未压缩的情况下需要1个字节进行存储。如果大部分流量不是来自广告平台，那么这一列至少可以以十倍的压缩率被压缩。当采用快速压缩算法，它的解压速度最少在十亿字节(未压缩数据)每秒。换句话说，这个查询可以在单个服务器上以每秒大约几十亿行的速度进行处理。 这实际上是当前实现的速度。

CPU

由于执行一个查询需要处理大量的行，因此在整个向量上执行所有操作将比在每一行上执行所有操作更加高效。同时这将有助于实现一个几乎没有调用成本的查询引擎。如果你不 这样做，使用任何一个机械硬盘，查询引擎都不可避免的停止CPU进行等待。所以，在数据按列存储并且按列执行是很有意义的。

有两种方法可以做到这一点：

1. 向量引擎：所有的操作都是为向量而不是为单个值编写的。这意味着多个操作之间的不再需要频繁的调用，并且调用的成本基本可以忽略不计。操作代码包含一个优化的内 部循环。
2. 代码生成：生成一段代码，包含查询中的所有操作。

这是不应该在一个通用数据库中实现的，因为这在运行简单查询时是没有意义的。但是也有例外，例如，MemSQL使用代码生成来减少处理SQL查询的延迟(只是为了比较，分析型数据库通常需要优化的是吞吐而不是延迟)。

请注意，为了提高CPU效率，查询语言必须是声明型的(SQL或MDX)， 或者至少一个向量(J，K)。 查询应该只包含隐式循环，允许进行优化。来源文章

ClickHouse的特性

真正的列式数据库管理系统

在一个真正的列式数据库管理系统中，除了数据本身外不应该存在其他额外的数据。这意味着为了避免在值旁边存储它们的长度«number»，你必须支持固定长度数值类型。例 如，10亿个UInt8类型的数据在未压缩的情况下大约消耗1GB左右的空间，如果不是这样的话，这将对CPU的使用产生强烈影响。即使是在未压缩的情况下，紧凑的存储数据也是非常重要的，因为解压缩的速度主要取决于未压缩数据的大小。

这是非常值得注意的，因为在一些其他系统中也可以将不同的列分别进行存储，但由于对其他场景进行的优化，使其无法有效的处理分析查询。例如：

HBase，BigTable，Cassandra，HyperTable。在这些系统中，你可以得到每秒数十万的吞吐能力，但是无法得到每秒几亿行的吞吐能力。

需要说明的是，ClickHouse不单单是一个数据库， 它是一个数据库管理系统。因为它允许在运行时创建表和数据库、加载数据和运行查询，而无需重新配置或重启服务。

数据压缩

在一些列式数据库管理系统中(例如：InfiniDB CE 和 MonetDB) 并没有使用数据压缩。但是, 若想达到比较优异的性能，数据压缩确实起到了至关重要的作用。

除了在磁盘空间和CPU消耗之间进行不同权衡的高效通用压缩编解码器之外，ClickHouse还提供针对特定类型数据的专用编解码器，这使得ClickHouse能够与更小的数据库(如时间序列数据库)竞争并超越它们。

数据的磁盘存储

许多的列式数据库(如 SAP HANA, Google PowerDrill)只能在内存中工作，这种方式会造成比实际更多的设备预算。

ClickHouse被设计用于工作在传统磁盘上的系统，它提供每GB更低的存储成本，但如果可以使用SSD和内存，它也会合理的利用这些资源。

多核心并行处理

ClickHouse会使用服务器上一切可用的资源，从而以最自然的方式并行处理大型查询。

多服务器分布式处理

上面提到的列式数据库管理系统中，几乎没有一个支持分布式的查询处理。

在ClickHouse中，数据可以保存在不同的shard上，每一个shard都由一组用于容错的replica组成，查询可以并行地在所有shard上进行处理。这些对用户来说是透明的

支持SQL

ClickHouse支持一种基于SQL的声明式查询语言，它在许多情况下与ANSI SQL标准相同。支持的查询GROUP BY, ORDER BY, FROM, JOIN, IN以及非相关子查询。

相关(依赖性)子查询和窗口函数暂不受支持，但将来会被实现。

向量引擎

为了高效的使用CPU，数据不仅仅按列存储，同时还按向量(列的一部分)进行处理，这样可以更加高效地使用CPU。

实时的数据更新

ClickHouse支持在表中定义主键。为了使查询能够快速在主键中进行范围查找，数据总是以增量的方式有序的存储在MergeTree中。因此，数据可以持续不断地高效的写入到表中，并且写入的过程中不会存在任何加锁的行为。

索引

按照主键对数据进行排序，这将帮助ClickHouse在几十毫秒以内完成对数据特定值或范围的查找。

适合在线查询

在线查询意味着在没有对数据做任何预处理的情况下以极低的延迟处理查询并将结果加载到用户的页面中。

支持近似计算

ClickHouse提供各种各样在允许牺牲数据精度的情况下对查询进行加速的方法：

1. 用于近似计算的各类聚合函数，如：distinct values, medians, quantiles
2. 基于数据的部分样本进行近似查询。这时，仅会从磁盘检索少部分比例的数据。
3. 不使用全部的聚合条件，通过随机选择有限个数据聚合条件进行聚合。这在数据聚合条件满足某些分布条件下，在提供相当准确的聚合结果的同时降低了计算资源的使用。

Adaptive Join Algorithm

ClickHouse支持自定义JOIN多个表，它更倾向于散列连接算法，如果有多个大表，则使用合并-连接算法

支持数据复制和数据完整性

ClickHouse使用异步的多主复制技术。当数据被写入任何一个可用副本后，系统会在后台将数据分发给其他副本，以保证系统在不同副本上保持相同的数据。在大多数情况下 ClickHouse能在故障后自动恢复，在一些少数的复杂情况下需要手动恢复。

更多信息，参见 数据复制。

角色的访问控制

ClickHouse使用SQL查询实现用户帐户管理，并允许角色的访问控制，类似于ANSI SQL标准和流行的关系数据库管理系统。

限制

1. 没有完整的事务支持。
2. 缺少高频率，低延迟的修改或删除已存在数据的能力。仅能用于批量删除或修改数据，但这符合 GDPR。
3. 稀疏索引使得ClickHouse不适合通过其键检索单行的点查询。来源文章

性能

根据Yandex的内部测试结果，ClickHouse表现出了比同类可比较产品更优的性能。你可以在 这里 查看具体的测试结果。许多其他的测试也证实这一点。你可以使用互联网搜索到它们，或者你也可以从 我们收集的部分相关连接 中查看。

单个大查询的吞吐量

吞吐量可以使用每秒处理的行数或每秒处理的字节数来衡量。如果数据被放置在page cache中，则一个不太复杂的查询在单个服务器上大约能够以2-10GB／s（未压缩）的速度进行处理（对于简单的查询，速度可以达到30GB／s）。如果数据没有在page cache中的话，那么速度将取决于你的磁盘系统和数据的压缩率。例如，如果一个磁盘允许以400MB／s的速度读取数据，并且数据压缩率是3，则数据的处理速度为1.2GB/s。这意味着，如果你是在提取一个10字节的列，那么它的处理速度大约是1-2亿行每秒。

对于分布式处理，处理速度几乎是线性扩展的，但这受限于聚合或排序的结果不是那么大的情况下。

处理短查询的延迟时间

如果一个查询使用主键并且没有太多行(几十万)进行处理，并且没有查询太多的列，那么在数据被page cache缓存的情况下，它的延迟应该小于50毫秒(在最佳的情况下应该小于10毫秒)。 否则，延迟取决于数据的查找次数。如果你当前使用的是HDD，在数据没有加载的情况下，查询所需要的延迟可以通过以下公式计算得知： 查找时间（10 ms） * 查询的列的数量 * 查询的数据块的数量。

处理大量短查询的吞吐量

在相同的情况下，ClickHouse可以在单个服务器上每秒处理数百个查询（在最佳的情况下最多可以处理数千个）。但是由于这不适用于分析型场景。因此我们建议每秒最多查询

100次。

数据的写入性能

我们建议每次写入不少于1000行的批量写入，或每秒不超过一个写入请求。当使用tab-separated格式将一份数据写入到MergeTree表中时，写入速度大约为50到200MB/s。如果您写入的数据每行为1Kb，那么写入的速度为50，000到200，000行每秒。如果您的行更小，那么写入速度将更高。为了提高写入性能，您可以使用多个INSERT进行并行写入，这将带来线性的性能提升。

来源文章

ClickHouse历史

ClickHouse最初是为 YandexMetrica 世界第二大Web分析平台 而开发的。多年来一直作为该系统的核心组件被该系统持续使用着。目前为止，该系统在ClickHouse中有超过

13万亿条记录，并且每天超过200多亿个事件被处理。它允许直接从原始数据中动态查询并生成报告。本文简要介绍了ClickHouse在其早期发展阶段的目标。

Yandex.Metrica基于用户定义的字段，对实时访问、连接会话，生成实时的统计报表。这种需求往往需要复杂聚合方式，比如对访问用户进行去重。构建报表的数据，是实时接收存储的新数据。

截至2014年4月，Yandex.Metrica每天跟踪大约120亿个事件（用户的点击和浏览）。为了可以创建自定义的报表，我们必须存储全部这些事件。同时，这些查询可能需要在几百毫秒内扫描数百万行的数据，或在几秒内扫描数亿行的数据。

Yandex.Metrica以及其他Yandex服务的使用案例

在Yandex.Metrica中，ClickHouse被用于多个场景中。

它的主要任务是使用原始数据在线的提供各种数据报告。它使用374台服务器的集群，存储了20.3万亿行的数据。在去除重复与副本数据的情况下，压缩后的数据达到了2PB。未压缩前（TSV格式）它大概有17PB。

ClickHouse还被使用在：

存储来自Yandex.Metrica的会话重放数据。处理中间数据

与Analytics一起构建全球报表。

为调试Yandex.Metrica引擎运行查询分析来自API和用户界面的日志数据

ClickHouse在其他Yandex服务中至少有12个安装：search verticals, Market, Direct, business analytics, mobile development, AdFox, personal services等。

聚合与非聚合数据

有一种流行的观点认为，想要有效的计算统计数据，必须要聚合数据，因为聚合将降低数据量。 但是数据聚合是一个有诸多限制的解决方案，例如：

你必须提前知道用户定义的报表的字段列表用户无法自定义报表

当聚合条件过多时，可能不会减少数据，聚合是无用的。 存在大量报表时，有太多的聚合变化（组合爆炸）

当聚合条件有非常大的基数时（如：url），数据量没有太大减少（少于两倍）聚合的数据量可能会增长而不是收缩

用户不会查看我们为他生成的所有报告，大部分计算将是无用的 各种聚合可能违背了数据的逻辑完整性

如果我们直接使用非聚合数据而不进行任何聚合时，我们的计算量可能是减少的。

然而，相对于聚合中很大一部分工作被离线完成，在线计算需要尽快的完成计算，因为用户在等待结果。

Yandex.Metrica 有一个专门用于聚合数据的系统，称为Metrage，它可以用作大部分报表。

从2009年开始，Yandex.Metrica还为非聚合数据使用专门的OLAP数据库，称为OLAPServer，它以前用于报表构建系统。

OLAPServer可以很好的工作在非聚合数据上，但是它有诸多限制，导致无法根据需要将其用于所有报表中。如，缺少对数据类型的支持（只支持数据），无法实时增量的更新数据（只能通过每天重写数据完成）。OLAPServer不是一个数据库管理系统，它只是一个数据库。

为了消除OLAPServer的这些局限性，解决所有报表使用非聚合数据的问题，我们开发了ClickHouse数据库管理系统。来源文章

ClickHouse用户

免责声明

如下使用ClickHouse的公司和他们的成功案例来源于公开资源，因此和实际情况可能有所出入。如果您分享您公司使用ClickHouse的故事，我们将不胜感激 将其添加到列表，但请确保你这样做不会有任何保密协议的问题。也欢迎提供来自其他公司的出版物的更新。

原始文章

入门

如果您是ClickHouse的新手，并希望亲身体验它的性能。首先需要完成 安装与部署.

之后，您可以通过教程与示例数据完成自己的入门第一步：

QuickStart教程 快速了解Clickhouse的操作流程

示例数据集-航班飞行数据 示例数据，提供了常用的SQL查询场景来源文章

安装

系统要求

ClickHouse可以在任何具有x86_64，AArch64或PowerPC64LE CPU架构的Linux，FreeBSD或Mac OS X上运行。

官方预构建的二进制文件通常针对x86_64进行编译，并利用SSE 4.2指令集，因此，除非另有说明，支持它的CPU使用将成为额外的系统需求。下面是检查当前CPU是否支持SSE 4.2的命令:

$ grep -q sse4_2 /proc/cpuinfo && echo "SSE 4.2 supported" || echo "SSE 4.2 not supported"

要在不支持SSE 4.2或AArch64，PowerPC64LE架构的处理器上运行ClickHouse，您应该通过适当的配置调整从源代码构建ClickHouse。

可用安装包

DEB安装包

建议使用Debian或Ubuntu的官方预编译deb软件包。运行以下命令来安装包:

sudo apt-get install apt-transport-https ca-certificates dirmngr

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4

echo "deb https://repo.clickhouse.tech/deb/stable/ main/" | sudo tee \

/etc/apt/sources.list.d/clickhouse.list sudo apt-get update

sudo apt-get install -y clickhouse-server clickhouse-client sudo service clickhouse-server start

clickhouse-client

如果您想使用最新的版本，请用testing替代stable(我们只推荐您用于测试环境)。你也可以从这里手动下载安装包：下载。

安装包列表：

clickhouse-common-static — ClickHouse编译的二进制文件。

clickhouse-server — 创建clickhouse-server软连接，并安装默认配置服务

clickhouse-client — 创建clickhouse-client客户端工具软连接，并安装客户端配置文件。

clickhouse-common-static-dbg — 带有调试信息的ClickHouse二进制文件。

RPM安装包

推荐使用CentOS、RedHat和所有其他基于rpm的Linux发行版的官方预编译rpm包。首先，您需要添加官方存储库：

sudo yum install yum-utils

sudo rpm --import https://repo.clickhouse.tech/CLICKHOUSE-KEY.GPG

sudo yum-config-manager --add-repo https://repo.clickhouse.tech/rpm/stable/x86_64

如果您想使用最新的版本，请用testing替代stable(我们只推荐您用于测试环境)。prestable有时也可用。然后运行命令安装：

sudo yum install clickhouse-server clickhouse-client

你也可以从这里手动下载安装包：下载。

Tgz安装包

如果您的操作系统不支持安装deb或rpm包，建议使用官方预编译的tgz软件包。所需的版本可以通过curl或wget从存储库https://repo.clickhouse.tech/tgz/下载。

下载后解压缩下载资源文件并使用安装脚本进行安装。以下是一个最新版本的安装示例:

export LATEST_VERSION=`curl https://api.github.com/repos/ClickHouse/ClickHouse/tags 2>/dev/null | grep -Eo '[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+' | head -n 1` curl -O https://repo.clickhouse.tech/tgz/clickhouse-common-static-$LATEST_VERSION.tgz

curl -O https://repo.clickhouse.tech/tgz/clickhouse-common-static-dbg-$LATEST_VERSION.tgz curl -O https://repo.clickhouse.tech/tgz/clickhouse-server-$LATEST_VERSION.tgz

curl -O https://repo.clickhouse.tech/tgz/clickhouse-client-$LATEST_VERSION.tgz

tar -xzvf clickhouse-common-static-$LATEST_VERSION.tgz

sudo clickhouse-common-static-$LATEST_VERSION/install/doinst.sh

tar -xzvf clickhouse-common-static-dbg-$LATEST_VERSION.tgz

sudo clickhouse-common-static-dbg-$LATEST_VERSION/install/doinst.sh

tar -xzvf clickhouse-server-$LATEST_VERSION.tgz

sudo clickhouse-server-$LATEST_VERSION/install/doinst.sh sudo /etc/init.d/clickhouse-server start

tar -xzvf clickhouse-client-$LATEST_VERSION.tgz

sudo clickhouse-client-$LATEST_VERSION/install/doinst.sh

对于生产环境，建议使用最新的stable版本。你可以在GitHub页面https://github.com/ClickHouse/ClickHouse/tags找到它，它以后缀-stable标志。

Docker安装包

要在Docker中运行ClickHouse，请遵循Docker Hub上的指南。它是官方的deb安装包。

其他环境安装包

对于非linux操作系统和Arch64 CPU架构，ClickHouse将会以master分支的最新提交的进行编译提供(它将会有几小时的延迟)。

macOS — curl -O 'https://builds.clickhouse.tech/master/macos/clickhouse' && chmod a+x ./clickhouse FreeBSD — curl -O 'https://builds.clickhouse.tech/master/freebsd/clickhouse' && chmod a+x ./clickhouse AArch64 — curl -O 'https://builds.clickhouse.tech/master/aarch64/clickhouse' && chmod a+x ./clickhouse

下载后，您可以使用clickhouse client连接服务，或者使用clickhouse local模式处理数据，不过您必须要额外在GitHub下载server和users配置文件。

不建议在生产环境中使用这些构建版本，因为它们没有经过充分的测试，但是您可以自行承担这样做的风险。它们只是ClickHouse功能的一个部分。使用源码安装

要手动编译ClickHouse, 请遵循Linux或Mac OS X说明。

您可以编译并安装它们，也可以使用不安装包的程序。通过手动构建，您可以禁用SSE 4.2或AArch64 cpu。

Client: programs/clickhouse-client Server: programs/clickhouse-server

您需要创建一个数据和元数据文件夹，并为所需的用户chown授权。它们的路径可以在服务器配置(src/programs/server/config.xml)中改变，默认情况下它们是:

/opt/clickhouse/data/default/

/opt/clickhouse/metadata/default/

在Gentoo上，你可以使用emerge clickhouse从源代码安装ClickHouse。

启动

如果没有service，可以运行如下命令在后台启动服务：

$ sudo /etc/init.d/clickhouse-server start

日志文件将输出在/var/log/clickhouse-server/文件夹。

如果服务器没有启动，检查/etc/clickhouse-server/config.xml中的配置。您也可以手动从控制台启动服务器:

$ clickhouse-server --config-file=/etc/clickhouse-server/config.xml

在这种情况下，日志将被打印到控制台，这在开发过程中很方便。

如果配置文件在当前目录中，则不需要指定——config-file参数。默认情况下，它的路径为./config.xml。

ClickHouse支持访问限制设置。它们位于users.xml文件(与config.xml同级目录)。

默认情况下，允许default用户从任何地方访问，不需要密码。可查看user/default/networks。更多信息，请参见Configuration Files。

启动服务后，您可以使用命令行客户端连接到它:

$ clickhouse-client

默认情况下，使用default用户并不携带密码连接到localhost:9000。还可以使用--host参数连接到指定服务器。

终端必须使用UTF-8编码。

更多信息，请参阅Command-line client。示例：

$ ./clickhouse-client

ClickHouse client version 0.0.18749. Connecting to localhost:9000.

Connected to ClickHouse server version 0.0.18749.

:) SELECT 1

SELECT 1

┌─1─┐

│ 1 │

└───┘

1 rows in set. Elapsed: 0.003 sec.

:)

恭喜，系统已经工作了!

为了继续进行实验，你可以尝试下载测试数据集或查看教程。 原始文章

ClickHouse教程

从本教程中可以获得什么？

通过学习本教程，您将了解如何设置一个简单的ClickHouse集群。它会很小，但是可以容错和扩展。然后，我们将使用其中一个示例数据集来填充数据并执行一些演示查询。

单节点设置

为了延迟演示分布式环境的复杂性，我们将首先在单个服务器或虚拟机上部署ClickHouse。ClickHouse通常是从deb或rpm包安装，但对于不支持它们的操作系统也有其他方法。

例如，您选择deb安装包，执行:

sudo apt-get install apt-transport-https ca-certificates dirmngr

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4

echo "deb https://repo.clickhouse.tech/deb/stable/ main/" | sudo tee \

/etc/apt/sources.list.d/clickhouse.list sudo apt-get update

sudo apt-get install -y clickhouse-server clickhouse-client sudo service clickhouse-server start

clickhouse-client

在我们安装的软件中包含这些包:

clickhouse-client 包，包含clickhouse-client客户端，它是交互式ClickHouse控制台客户端。

clickhouse-common 包，包含一个ClickHouse可执行文件。

clickhouse-server 包，包含要作为服务端运行的ClickHouse配置文件。

服务器配置文件位于/etc/clickhouse-server/。在继续之前，请注意config.xml中的<path>元素。它决定了数据存储的位置，因此它应该位于磁盘容量的卷上;默认值

是/var/lib/clickhouse/。如果你想调整配置，直接编辑config是不方便的。考虑到它可能会在将来的包更新中被重写。建议重写配置元素的方法是在配置中创建config.d文件夹，作为config.xml的重写方式。

你可能已经注意到了,clickhouse-server安装后不会自动启动。 它也不会在更新后自动重新启动。 您启动服务端的方式取决于您的初始系统，通常情况下是这样:

sudo service clickhouse-server start

或

sudo /etc/init.d/clickhouse-server start

服务端日志的默认位置是/var/log/clickhouse-server/。当服务端在日志中记录Ready for connections消息，即表示服务端已准备好处理客户端连接。一旦clickhouse-server启动并运行，我们可以利用clickhouse-client连接到服务端，并运行一些测试查询，如SELECT "Hello, world!";.

Clickhouse-client的快速提示

导入示例数据集

现在是时候用一些示例数据填充我们的ClickHouse服务端。 在本教程中，我们将使用Yandex.Metrica的匿名数据，它是在ClickHouse成为开源之前作为生产环境运行的第一个服务（关于这一点的更多内容请参阅ClickHouse历史)。多种导入Yandex.Metrica数据集方法，为了本教程，我们将使用最现实的一个。

下载并提取表数据

curl https://datasets.clickhouse.tech/hits/tsv/hits_v1.tsv.xz | unxz --threads=`nproc` > hits_v1.tsv

curl https://datasets.clickhouse.tech/visits/tsv/visits_v1.tsv.xz | unxz --threads=`nproc` > visits_v1.tsv

提取的文件大小约为10GB。

创建表

与大多数数据库管理系统一样，ClickHouse在逻辑上将表分组为数据库。包含一个default数据库，但我们将创建一个新的数据库tutorial:

clickhouse-client --query "CREATE DATABASE IF NOT EXISTS tutorial"

与创建数据库相比，创建表的语法要复杂得多（请参阅参考资料. 一般CREATE TABLE声明必须指定三个关键的事情:

1. 要创建的表的名称。
2. 表结构，例如：列名和对应的数据类型。
3. 表引擎及其设置，这决定了对此表的查询操作是如何在物理层面执行的所有细节。

Yandex.Metrica是一个网络分析服务，样本数据集不包括其全部功能，因此只有两个表可以创建:

hits 表包含所有用户在服务所涵盖的所有网站上完成的每个操作。

visits 表包含预先构建的会话，而不是单个操作。让我们看看并执行这些表的实际创建表查询:

CREATE TABLE tutorial.hits_v1 (

`WatchID` UInt64,

`JavaEnable` UInt8,

`Title` String,

`GoodEvent` Int16,

`EventTime` DateTime,

`EventDate` Date,

`CounterID` UInt32,

`ClientIP` UInt32,

`ClientIP6` FixedString(16),

`RegionID` UInt32,

`UserID` UInt64,

`CounterClass` Int8,

`OS` UInt8,

`UserAgent` UInt8,

`URL` String,

`Referer` String,

`URLDomain` String,

`RefererDomain` String,

`Refresh` UInt8,

`IsRobot` UInt8,

`RefererCategories` Array(UInt16),

`URLCategories` Array(UInt16),

`URLRegions` Array(UInt32),

`RefererRegions` Array(UInt32),

`ResolutionWidth` UInt16,

`ResolutionHeight` UInt16,

`ResolutionDepth` UInt8,

`FlashMajor` UInt8,

`FlashMinor` UInt8,

`FlashMinor2` String,

`NetMajor` UInt8,

`NetMinor` UInt8,

`UserAgentMajor` UInt16,

`UserAgentMinor` FixedString(2),

`CookieEnable` UInt8,

`JavascriptEnable` UInt8,

`IsMobile` UInt8,

`MobilePhone` UInt8,

`MobilePhoneModel` String,

`Params` String,

`IPNetworkID` UInt32,

`TraficSourceID` Int8,

`SearchEngineID` UInt16,

`SearchPhrase` String,

`AdvEngineID` UInt8,

`IsArtifical` UInt8,

`WindowClientWidth` UInt16,

`WindowClientHeight` UInt16,

`ClientTimeZone` Int16,

`ClientEventTime` DateTime,

`SilverlightVersion1` UInt8,

`SilverlightVersion2` UInt8,

`SilverlightVersion3` UInt32,

`SilverlightVersion4` UInt16,

`PageCharset` String,

`CodeVersion` UInt32,

`IsLink` UInt8,

`IsDownload` UInt8,

`IsNotBounce` UInt8,

`FUniqID` UInt64,

`HID` UInt32,

`IsOldCounter` UInt8,

`IsEvent` UInt8,

`IsParameter` UInt8,

`DontCountHits` UInt8,

`WithHash` UInt8,

`HitColor` FixedString(1),

`UTCEventTime` DateTime,

`Age` UInt8,

`Sex` UInt8,

`Income` UInt8,

`Interests` UInt16,

`Robotness` UInt8,

`GeneralInterests` Array(UInt16),

`RemoteIP` UInt32,

`RemoteIP6` FixedString(16),

`WindowName` Int32,

`OpenerName` Int32,

`HistoryLength` Int16,

`BrowserLanguage` FixedString(2),

`BrowserCountry` FixedString(2),

`SocialNetwork` String,

`SocialAction` String,

`HTTPError` UInt16,

`SendTiming` Int32,

`DNSTiming` Int32,

CREATE TABLE tutorial.visits_v1 (

`CounterID` UInt32,

`StartDate` Date,

`Sign` Int8,

`IsNew` UInt8,

`VisitID` UInt64,

`UserID` UInt64,

`StartTime` DateTime,

`Duration` UInt32,

`UTCStartTime` DateTime,

`PageViews` Int32,

`Hits` Int32,

`IsBounce` UInt8,

`Referer` String,

`StartURL` String,

`RefererDomain` String,

`StartURLDomain` String,

`EndURL` String,

`LinkURL` String,

`IsDownload` UInt8,

`TraficSourceID` Int8,

`SearchEngineID` UInt16,

`SearchPhrase` String,

`AdvEngineID` UInt8,

`PlaceID` Int32,

`RefererCategories` Array(UInt16),

`URLCategories` Array(UInt16),

`URLRegions` Array(UInt32),

`RefererRegions` Array(UInt32),

`IsYandex` UInt8,

`GoalReachesDepth` Int32,

`GoalReachesURL` Int32,

`GoalReachesAny` Int32,

`SocialSourceNetworkID` UInt8,

`SocialSourcePage` String,

`MobilePhoneModel` String,

`ClientEventTime` DateTime,

`RegionID` UInt32,

`ClientIP` UInt32,

`ClientIP6` FixedString(16),

`RemoteIP` UInt32,

`RemoteIP6` FixedString(16),

`IPNetworkID` UInt32,

`SilverlightVersion3` UInt32,

`CodeVersion` UInt32,

`ResolutionWidth` UInt16,

`ResolutionHeight` UInt16,

`UserAgentMajor` UInt16,

`UserAgentMinor` UInt16,

`WindowClientWidth` UInt16,

`WindowClientHeight` UInt16,

`SilverlightVersion2` UInt8,

`ConnectTiming` Int32,

`ResponseStartTiming` Int32,

`ResponseEndTiming` Int32,

`FetchTiming` Int32,

`RedirectTiming` Int32,

`DOMInteractiveTiming` Int32,

`DOMContentLoadedTiming` Int32,

`DOMCompleteTiming` Int32,

`LoadEventStartTiming` Int32,

`LoadEventEndTiming` Int32,

`NSToDOMContentLoadedTiming` Int32,

`FirstPaintTiming` Int32,

`RedirectCount` Int8,

`SocialSourceNetworkID` UInt8,

`SocialSourcePage` String,

`ParamPrice` Int64,

`ParamOrderID` String,

`ParamCurrency` FixedString(3),

`ParamCurrencyID` UInt16,

`GoalsReached` Array(UInt32),

`OpenstatServiceName` String,

`OpenstatCampaignID` String,

`OpenstatAdID` String,

`OpenstatSourceID` String,

`UTMSource` String,

`UTMMedium` String,

`UTMCampaign` String,

`UTMContent` String,

`UTMTerm` String,

`FromTag` String,

`HasGCLID` UInt8,

`RefererHash` UInt64,

`URLHash` UInt64,

`CLID` UInt32,

`YCLID` UInt64,

`ShareService` String,

`ShareURL` String,

`ShareTitle` String,

`ParsedParams` Nested( Key1 String,

Key2 String, Key3 String, Key4 String, Key5 String,

ValueDouble Float64),

`IslandID` FixedString(16),

`RequestNum` UInt32,

`RequestTry` UInt8

)

ENGINE = MergeTree()

PARTITION BY toYYYYMM(EventDate)

ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID)

`SilverlightVersion4` UInt16,

`FlashVersion3` UInt16,

`FlashVersion4` UInt16,

`ClientTimeZone` Int16,

`OS` UInt8,

`UserAgent` UInt8,

`ResolutionDepth` UInt8,

`FlashMajor` UInt8,

`FlashMinor` UInt8,

`NetMajor` UInt8,

`NetMinor` UInt8,

`MobilePhone` UInt8,

`SilverlightVersion1` UInt8,

`Age` UInt8,

`Sex` UInt8,

`Income` UInt8,

`JavaEnable` UInt8,

`CookieEnable` UInt8,

`JavascriptEnable` UInt8,

`IsMobile` UInt8,

`BrowserLanguage` UInt16,

`BrowserCountry` UInt16,

`Interests` UInt16,

`Robotness` UInt8,

`GeneralInterests` Array(UInt16),

`Params` Array(String),

`Goals` Nested( ID UInt32,

Serial UInt32, EventTime DateTime, Price Int64,

OrderID String, CurrencyID UInt32),

`WatchIDs` Array(UInt64),

`ParamSumPrice` Int64,

`ParamCurrency` FixedString(3),

`ParamCurrencyID` UInt16,

`ClickLogID` UInt64,

`ClickEventID` Int32,

`ClickGoodEvent` Int32,

`ClickEventTime` DateTime,

`ClickPriorityID` Int32,

`ClickPhraseID` Int32,

`ClickPageID` Int32,

`ClickPlaceID` Int32,

`ClickTypeID` Int32,

`ClickResourceID` Int32,

`ClickCost` UInt32,

`ClickClientIP` UInt32,

`ClickDomainID` UInt32,

`ClickURL` String,

`ClickAttempt` UInt8,

`ClickOrderID` UInt32,

`ClickBannerID` UInt32,

`ClickMarketCategoryID` UInt32,

`ClickMarketPP` UInt32,

`ClickMarketCategoryName` String,

`ClickMarketPPName` String,

`ClickAWAPSCampaignName` String,

`ClickPageName` String,

`ClickTargetType` UInt16,

`ClickTargetPhraseID` UInt64,

`ClickContextType` UInt8,

`ClickSelectType` Int8,

`ClickOptions` String,

`ClickGroupBannerID` Int32,

`OpenstatServiceName` String,

`OpenstatCampaignID` String,

`OpenstatAdID` String,

`OpenstatSourceID` String,

`UTMSource` String,

`UTMMedium` String,

`UTMCampaign` String,

`UTMContent` String,

`UTMTerm` String,

`FromTag` String,

`HasGCLID` UInt8,

`FirstVisit` DateTime,

`PredLastVisit` Date,

`LastVisit` Date,

`TotalVisits` UInt32,

`TraficSource` Nested( ID Int8,

SearchEngineID UInt16, AdvEngineID UInt8, PlaceID UInt16,

SocialSourceNetworkID UInt8, Domain String, SearchPhrase String, SocialSourcePage String),

`Attendance` FixedString(16),

`CLID` UInt32,

`YCLID` UInt64,

`NormalizedRefererHash` UInt64,

`SearchPhraseHash` UInt64,

`RefererDomainHash` UInt64,

`NormalizedStartURLHash` UInt64,

`StartURLDomainHash` UInt64,

`NormalizedEndURLHash` UInt64,

`TopLevelDomain` UInt64,

`URLScheme` UInt64,

`OpenstatServiceNameHash` UInt64,

`OpenstatCampaignIDHash` UInt64,

`OpenstatAdIDHash` UInt64,

`OpenstatSourceIDHash` UInt64,

`UTMSourceHash` UInt64,

`UTMMediumHash` UInt64,

`UTMCampaignHash` UInt64,

`UTMContentHash` UInt64,

`UTMTermHash` UInt64,

`FromHash` UInt64,

`WebVisorEnabled` UInt8,

`WebVisorActivity` UInt32,

`ParsedParams` Nested( Key1 String,

Key2 String, Key3 String, Key4 String, Key5 String,

ValueDouble Float64),

`Market` Nested( Type UInt8, GoalID UInt32, OrderID String, OrderPrice Int64, PP UInt32,

DirectPlaceID UInt32, DirectOrderID UInt32, DirectBannerID UInt32, GoodID String, GoodName String, GoodQuantity Int32, GoodPrice Int64),

`IslandID` FixedString(16)

)

ENGINE = CollapsingMergeTree(Sign) PARTITION BY toYYYYMM(StartDate)

ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) SAMPLE BY intHash32(UserID)

您可以使用clickhouse-client的交互模式执行这些查询（只需在终端中启动它，而不需要提前指定查询）。或者如果你愿意，可以尝试一些替代接口。正如我们所看到的, hits_v1使用 MergeTree引擎，而visits_v1使用 Collapsing引擎。

导入数据

数据导入到ClickHouse是通过INSERT INTO方式完成的，查询类似许多SQL数据库。然而，数据通常是在一个提供支持序列化格式而不是VALUES子句（也支持）。我们之前下载的文件是以制表符分隔的格式，所以这里是如何通过控制台客户端导入它们:

clickhouse-client --query "INSERT INTO tutorial.hits_v1 FORMAT TSV" --max_insert_block_size=100000 < hits_v1.tsv clickhouse-client --query "INSERT INTO tutorial.visits_v1 FORMAT TSV" --max_insert_block_size=100000 < visits_v1.tsv

ClickHouse有很多要调整的设置在控制台客户端中指定它们的一种方法是通过参数，就像我们看到上面语句中的--max_insert_block_size。找出可用的设置、含义及其默认值的最简单方法是查询system.settings 表:

SELECT name, value, changed, description

FROM system.settings

WHERE name LIKE '%max_insert_b%' FORMAT TSV

max_insert_block_size 1048576 0 "The maximum block size for insertion, if we control the creation of blocks for insertion."

您也可以OPTIMIZE导入后的表。使用MergeTree-family引擎配置的表总是在后台合并数据部分以优化数据存储（或至少检查是否有意义）。 这些查询强制表引擎🖂即进行存储优化，而不是稍后一段时间执行:

clickhouse-client --query "OPTIMIZE TABLE tutorial.hits_v1 FINAL" clickhouse-client --query "OPTIMIZE TABLE tutorial.visits_v1 FINAL"

这些查询开始I/O和CPU密集型操作，所以如果表一直接收到新数据，最好不要管它，让合并在后台运行。现在我们可以检查表导入是否成功:

clickhouse-client --query "SELECT COUNT(*) FROM tutorial.hits_v1" clickhouse-client --query "SELECT COUNT(*) FROM tutorial.visits_v1"

查询示例

SELECT

StartURL AS URL,

AVG(Duration) AS AvgDuration

FROM tutorial.visits_v1

WHERE StartDate BETWEEN '2014-03-23' AND '2014-03-30'

GROUP BY URL

ORDER BY AvgDuration DESC LIMIT 10

SELECT

sum(Sign) AS visits,

sumIf(Sign, has(Goals.ID, 1105530)) AS goal_visits, (100. * goal_visits) / visits AS goal_percent

FROM tutorial.visits_v1

WHERE (CounterID = 912887) AND (toYYYYMM(StartDate) = 201403) AND (domain(StartURL) = 'yandex.ru')

集群部署

ClickHouse集群是一个同质集群。 设置步骤:

1. 在群集的所有机器上安装ClickHouse服务端
2. 在配置文件中设置群集配置
3. 在每个实例上创建本地表
4. 创建一个分布式表

分布式表实际上是一种view，映射到ClickHouse集群的本地表。 从分布式表中执行SELECT查询会使用集群所有分片的资源。 您可以为多个集群指定configs，并创建多个分布式表，为不同的集群提供视图。

具有三个分片，每个分片一个副本的集群的示例配置:

<remote_servers>

<perftest_3shards_1replicas>

<shard>

<replica>

<host>example-perftest01j.yandex.ru</host>

<port>9000</port>

</replica>

</shard>

<shard>

<replica>

<host>example-perftest02j.yandex.ru</host>

<port>9000</port>

</replica>

</shard>

<shard>

<replica>

<host>example-perftest03j.yandex.ru</host>

<port>9000</port>

</replica>

</shard>

</perftest_3shards_1replicas>

</remote_servers>

为了进一步演示，让我们使用和创建hits_v1表相同的CREATE TABLE语句创建一个新的本地表，但表名不同:

CREATE TABLE tutorial.hits_local (...) ENGINE = MergeTree() ...

创建提供集群本地表视图的分布式表:

CREATE TABLE tutorial.hits_all AS tutorial.hits_local

ENGINE = Distributed(perftest_3shards_1replicas, tutorial, hits_local, rand());

常见的做法是在集群的所有计算机上创建类似的分布式表。 它允许在群集的任何计算机上运行分布式查询。 还有一个替代选项可以使用以下方法为给定的SELECT查询创建临时分布式表远程表功能。

让我们运行INSERT SELECT将该表传播到多个服务器。

INSERT INTO tutorial.hits_all SELECT * FROM tutorial.hits_v1;

注意:

这种方法不适合大型表的分片。 有一个单独的工具 clickhouse-copier 这可以重新分片任意大表。

正如您所期望的那样，如果计算量大的查询使用3台服务器而不是一个，则运行速度快N倍。在这种情况下，我们使用了具有3个分片的集群，每个分片都包含一个副本。

为了在生产环境中提供弹性，我们建议每个分片应包含分布在多个可用区或数据中心（或至少机架）之间的2-3个副本。 请注意，ClickHouse支持无限数量的副本。包含三个副本的一个分片集群的示例配置:

<remote_servers>

...

<perftest_1shards_3replicas>

<shard>

<replica>

<host>example-perftest01j.yandex.ru</host>

<port>9000</port>

</replica>

<replica>

<host>example-perftest02j.yandex.ru</host>

<port>9000</port>

</replica>

<replica>

<host>example-perftest03j.yandex.ru</host>

<port>9000</port>

</replica>

</shard>

</perftest_1shards_3replicas>

</remote_servers>

启用本机复制Zookeeper是必需的。 ClickHouse负责所有副本的数据一致性，并在失败后自动运行恢复过程。建议将ZooKeeper集群部署在单独的服务器上（其中没有其他进程，包括运行的ClickHouse）。

注意

ZooKeeper不是一个严格的要求：在某些简单的情况下，您可以通过将数据写入应用程序代码中的所有副本来复制数据。 这种方法是不建议的，在这种情况下，ClickHouse将无法保证所有副本上的数据一致性。 因此需要由您的应用来保证这一点。

ZooKeeper位置在配置文件中指定:

<zookeeper>

<node>

<host>zoo01.yandex.ru</host>

<port>2181</port>

</node>

<node>

<host>zoo02.yandex.ru</host>

<port>2181</port>

</node>

<node>

<host>zoo03.yandex.ru</host>

<port>2181</port>

</node>

</zookeeper>

此外，我们需要设置宏来识别每个用于创建表的分片和副本:

<macros>

<shard>01</shard>

<replica>01</replica>

</macros>

如果在创建复制表时没有副本，则会实例化新的第一个副本。 如果已有实时副本，则新副本将克隆现有副本中的数据。 您可以选择首先创建所有复制的表，然后向其中插入数据。 另一种选择是创建一些副本，并在数据插入之后或期间添加其他副本。

CREATE TABLE tutorial.hits_replica (...) ENGINE = ReplcatedMergeTree(

'/clickhouse_perftest/tables/{shard}/hits', '{replica}'

)

...

在这里，我们使用ReplicatedMergeTree表引擎。 在参数中，我们指定包含分片和副本标识符的ZooKeeper路径。

INSERT INTO tutorial.hits_replica SELECT * FROM tutorial.hits_local;

复制在多主机模式下运行。数据可以加载到任何副本中，然后系统自动将其与其他实例同步。复制是异步的，因此在给定时刻，并非所有副本都可能包含最近插入的数据。至少应 该有一个副本允许数据摄入。另一些则会在重新激活后同步数据并修复一致性。请注意，这种方法允许最近插入的数据丢失的可能性很低。

原始文章

示例数据集

本节介绍如何获取示例数据集并将其导入ClickHouse。对于某些数据集，还可以使用示例查询。对于某些数据集示例查询也可用。

Anonymized Yandex.Metrica Dataset Star Schema Benchmark

WikiStat

Terabyte of Click Logs from Criteo AMPLab Big Data Benchmark

New York Taxi Data OnTime

原始文章

Anonymized Yandex.Metrica Data

数据集由两个表组成，包含关于Yandex.Metrica的hits(hits_v1)和visit(visits_v1)的匿名数据。你可以阅读更多关于Yandex的信息。在ClickHouse历史的Metrica部分。

数据集由两个表组成，他们中的任何一个都可以下载作为一个压缩tsv.xz的文件或准备的分区。除此之外,一个扩展版的hits表包含1亿行TSV在 https://datasets.clickhouse.tech/hits/tsv/hits_100m_obfuscated_v1.tsv.xz，准备分区在 https://datasets.clickhouse.tech/hits/partitions/hits_100m_obfuscated_v1.tar.xz。

从准备好的分区获取表

下载和导入hits表:

curl -O https://datasets.clickhouse.tech/hits/partitions/hits_v1.tar

tar xvf hits_v1.tar -C /var/lib/clickhouse # path to ClickHouse data directory ## check permissions on unpacked data, fix if required

sudo service clickhouse-server restart

clickhouse-client --query "SELECT COUNT(*) FROM datasets.hits_v1"

下载和导入visits表:

curl -O https://datasets.clickhouse.tech/visits/partitions/visits_v1.tar

tar xvf visits_v1.tar -C /var/lib/clickhouse # path to ClickHouse data directory ## check permissions on unpacked data, fix if required

sudo service clickhouse-server restart

clickhouse-client --query "SELECT COUNT(*) FROM datasets.visits_v1"

从TSV压缩文件获取表

从TSV压缩文件下载并导入hits:

从压缩tsv文件下载和导入visits:

查询示例

使用教程是以Yandex.Metrica数据集开始教程。

可以在ClickHouse的stateful tests 中找到对这些表的查询的其他示例(它们被命名为test.hists和test.visits)。

Star Schema Benchmark

编译 dbgen:

$ git clone git@github.com:vadimtk/ssb-dbgen.git

$ cd ssb-dbgen

$ make

开始生成数据：

注意

使用-s 100dbgen将生成6亿行数据(67GB), 如果使用-s 1000它会生成60亿行数据(这需要很多时间))

$ ./dbgen -s 1000 -T c

$ ./dbgen -s 1000 -T l

$ ./dbgen -s 1000 -T p

$ ./dbgen -s 1000 -T s

$ ./dbgen -s 1000 -T d

在ClickHouse中创建数据表：

CREATE TABLE customer (

C_CUSTKEY UInt32,

C_NAME String, C_ADDRESS String,

C_CITY LowCardinality(String), C_NATION LowCardinality(String), C_REGION LowCardinality(String), C_PHONE String,

C_MKTSEGMENT LowCardinality(String)

)

ENGINE = MergeTree ORDER BY (C_CUSTKEY);

CREATE TABLE lineorder (

LO_ORDERKEY UInt32,

LO_LINENUMBER UInt8,

LO_CUSTKEY UInt32,

LO_PARTKEY UInt32,

LO_SUPPKEY UInt32,

LO_ORDERDATE Date,

LO_ORDERPRIORITY LowCardinality(String), LO_SHIPPRIORITY UInt8,

LO_QUANTITY UInt8, LO_EXTENDEDPRICE UInt32, LO_ORDTOTALPRICE UInt32, LO_DISCOUNT UInt8,

LO_REVENUE UInt32,

LO_SUPPLYCOST UInt32, LO_TAX UInt8, LO_COMMITDATE Date,

LO_SHIPMODE LowCardinality(String)

)

ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);

CREATE TABLE part (

P_PARTKEY UInt32,

P_NAME String,

P_MFGR LowCardinality(String), P_CATEGORY LowCardinality(String), P_BRAND LowCardinality(String), P_COLOR LowCardinality(String), P_TYPE LowCardinality(String), P_SIZE UInt8,

P_CONTAINER LowCardinality(String)

)

ENGINE = MergeTree ORDER BY P_PARTKEY;

CREATE TABLE supplier (

S_SUPPKEY UInt32,

S_NAME String, S_ADDRESS String,

S_CITY LowCardinality(String), S_NATION LowCardinality(String), S_REGION LowCardinality(String), S_PHONE String

)

ENGINE = MergeTree ORDER BY S_SUPPKEY;

写入数据：

$ clickhouse-client --query "INSERT INTO customer FORMAT CSV" < customer.tbl

$ clickhouse-client --query "INSERT INTO part FORMAT CSV" < part.tbl

$ clickhouse-client --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl

$ clickhouse-client --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl

将star schema转换为flat schema：

SET max_memory_usage = 20000000000, allow_experimental_multiple_joins_emulation = 1;

CREATE TABLE lineorder_flat ENGINE = MergeTree

PARTITION BY toYear(LO_ORDERDATE)

ORDER BY (LO_ORDERDATE, LO_ORDERKEY) AS

SELECT l.*, c.*, s.*, p.*

FROM lineorder l

ANY INNER JOIN customer c ON (c.C_CUSTKEY = l.LO_CUSTKEY) ANY INNER JOIN supplier s ON (s.S_SUPPKEY = l.LO_SUPPKEY) ANY INNER JOIN part p ON (p.P_PARTKEY = l.LO_PARTKEY);

ALTER TABLE lineorder_flat DROP COLUMN C_CUSTKEY, DROP COLUMN S_SUPPKEY, DROP COLUMN P_PARTKEY;

运行查询: Q1.1

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND

LO_QUANTITY < 25;

Q1.2

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toYYYYMM(LO_ORDERDATE) = 199401 AND LO_DISCOUNT BETWEEN 4 AND 6

AND LO_QUANTITY BETWEEN 26 AND 35;

Q1.3

SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM lineorder_flat WHERE toISOWeek(LO_ORDERDATE) = 6 AND toYear(LO_ORDERDATE) = 1994 AND

LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;

Q2.1

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND FROM lineorder_flat WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA' GROUP BY

year, P_BRAND ORDER BY year, P_BRAND;

Q2.2

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND FROM lineorder_flat WHERE P_BRAND BETWEEN 'MFGR#2221' AND 'MFGR#2228' AND

S_REGION = 'ASIA' GROUP BY year, P_BRAND ORDER BY year, P_BRAND;

Q2.3

SELECT sum(LO_REVENUE), toYear(LO_ORDERDATE) AS year, P_BRAND FROM lineorder_flat WHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE' GROUP BY

year, P_BRAND ORDER BY year, P_BRAND;

Q3.1

SELECT C_NATION, S_NATION, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA'

AND year >= 1992 AND year <= 1997 GROUP BY C_NATION, S_NATION, year ORDER BY year asc, revenue desc;

Q3.2

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND year >= 1992 AND year <= 1997 GROUP BY C_CITY, S_CITY, year ORDER BY year asc, revenue desc;

Q3.3

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5')

AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND year >= 1992 AND year <= 1997 GROUP BY C_CITY, S_CITY, year ORDER BY year asc, revenue desc;

Q3.4

SELECT C_CITY, S_CITY, toYear(LO_ORDERDATE) AS year, sum(LO_REVENUE) AS revenue FROM lineorder_flat WHERE (C_CITY = 'UNITED KI1' OR C_CITY = 'UNITED KI5')

AND (S_CITY = 'UNITED KI1' OR S_CITY = 'UNITED KI5') AND toYYYYMM(LO_ORDERDATE) = '199712' GROUP BY C_CITY, S_CITY, year ORDER BY year asc, revenue desc;

Q4.1

SELECT toYear(LO_ORDERDATE) AS year, C_NATION, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2') GROUP BY year, C_NATION ORDER BY year, C_NATION;

Q4.2

SELECT toYear(LO_ORDERDATE) AS year, S_NATION, P_CATEGORY, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND (year = 1997 OR year = 1998) AND (P_MFGR = 'MFGR#1' OR P_MFGR = 'MFGR#2') GROUP BY year, S_NATION, P_CATEGORY ORDER BY year, S_NATION, P_CATEGORY;

Q4.3

SELECT toYear(LO_ORDERDATE) AS year, S_CITY, P_BRAND, sum(LO_REVENUE - LO_SUPPLYCOST) AS profit FROM lineorder_flat WHERE S_NATION = 'UNITED STATES'

AND (year = 1997 OR year = 1998) AND P_CATEGORY = 'MFGR#14' GROUP BY year, S_CITY, P_BRAND ORDER BY year, S_CITY, P_BRAND;

原始文章

WikiStat

参考: http://dumps.wikimedia.org/other/pagecounts-raw/

创建表结构：

CREATE TABLE wikistat (

date Date, time DateTime, project String,

subproject String, path String,

hits UInt64,

size UInt64

) ENGINE = MergeTree(date, (path, time), 8192);

加载数据：

$ for i in {2007..2016}; do for j in {01..12}; do echo $i-$j >&2; curl -sSL "http://dumps.wikimedia.org/other/pagecounts-raw/$i/$i-$j/" | grep -oE 'pagecounts-[0-9]+-[0- 9]+\.gz'; done; done | sort | uniq | tee links.txt

$ cat links.txt | while read link; do wget http://dumps.wikimedia.org/other/pagecounts-raw/$(echo $link | sed -r 's/pagecounts-([0-9]{4})([0-9]{2})[0-9]{2}-[0- 9]+\.gz/\1/')/$(echo $link | sed -r 's/pagecounts-([0-9]{4})([0-9]{2})[0-9]{2}-[0-9]+\.gz/\1-\2/')/$link; done

$ ls -1 /opt/wikistat/ | grep gz | while read i; do echo $i; gzip -cd /opt/wikistat/$i | ./wikistat-loader --time="$(echo -n $i | sed -r 's/pagecounts-([0-9]{4})([0-9]{2})([0-9]{2})- ([0-9]{2})([0-9]{2})([0-9]{2})\.gz/\1-\2-\3 \4-00-00/')" | clickhouse-client --query="INSERT INTO wikistat FORMAT TabSeparated"; done

原始文章

Terabyte of Click Logs from Criteo

可以从 http://labs.criteo.com/downloads/download-terabyte-click-logs/ 上下载数据创建原始数据对应的表结构：

CREATE TABLE criteo_log (date Date, clicked UInt8, int1 Int32, int2 Int32, int3 Int32, int4 Int32, int5 Int32, int6 Int32, int7 Int32, int8 Int32, int9 Int32, int10 Int32, int11 Int32, int12 Int32, int13 Int32, cat1 String, cat2 String, cat3 String, cat4 String, cat5 String, cat6 String, cat7 String, cat8 String, cat9 String, cat10 String, cat11 String, cat12 String, cat13 String, cat14 String, cat15 String, cat16 String, cat17 String, cat18 String, cat19 String, cat20 String, cat21 String, cat22 String, cat23 String, cat24 String, cat25 String, cat26 String) ENGINE = Log

下载数据：

$ for i in {00..23}; do echo $i; zcat datasets/criteo/day_${i#0}.gz | sed -r 's/^/2000-01-'${i/00/24}'\t/' | clickhouse-client --host=example-perftest01j --query="INSERT INTO criteo_log FORMAT TabSeparated"; done

创建转换后的数据对应的表结构：

CREATE TABLE criteo (

date Date, clicked UInt8, int1 Int32, int2 Int32, int3 Int32, int4 Int32, int5 Int32, int6 Int32, int7 Int32, int8 Int32, int9 Int32, int10 Int32, int11 Int32, int12 Int32, int13 Int32, icat1 UInt32, icat2 UInt32, icat3 UInt32, icat4 UInt32, icat5 UInt32, icat6 UInt32, icat7 UInt32, icat8 UInt32, icat9 UInt32, icat10 UInt32, icat11 UInt32, icat12 UInt32, icat13 UInt32, icat14 UInt32, icat15 UInt32, icat16 UInt32, icat17 UInt32, icat18 UInt32, icat19 UInt32, icat20 UInt32, icat21 UInt32, icat22 UInt32, icat23 UInt32, icat24 UInt32, icat25 UInt32, icat26 UInt32

) ENGINE = MergeTree(date, intHash32(icat1), (date, intHash32(icat1)), 8192)

将第一张表中的原始数据转化写入到第二张表中去：

INSERT INTO criteo SELECT date, clicked, int1, int2, int3, int4, int5, int6, int7, int8, int9, int10, int11, int12, int13, reinterpretAsUInt32(unhex(cat1)) AS icat1, reinterpretAsUInt32(unhex(cat2)) AS icat2, reinterpretAsUInt32(unhex(cat3)) AS icat3, reinterpretAsUInt32(unhex(cat4)) AS icat4, reinterpretAsUInt32(unhex(cat5)) AS icat5, reinterpretAsUInt32(unhex(cat6)) AS icat6, reinterpretAsUInt32(unhex(cat7)) AS icat7, reinterpretAsUInt32(unhex(cat8)) AS icat8, reinterpretAsUInt32(unhex(cat9)) AS icat9, reinterpretAsUInt32(unhex(cat10)) AS icat10, reinterpretAsUInt32(unhex(cat11)) AS icat11, reinterpretAsUInt32(unhex(cat12)) AS icat12, reinterpretAsUInt32(unhex(cat13)) AS icat13, reinterpretAsUInt32(unhex(cat14)) AS icat14, reinterpretAsUInt32(unhex(cat15)) AS icat15, reinterpretAsUInt32(unhex(cat16)) AS icat16, reinterpretAsUInt32(unhex(cat17)) AS icat17, reinterpretAsUInt32(unhex(cat18)) AS icat18, reinterpretAsUInt32(unhex(cat19)) AS icat19, reinterpretAsUInt32(unhex(cat20)) AS icat20, reinterpretAsUInt32(unhex(cat21)) AS icat21, reinterpretAsUInt32(unhex(cat22)) AS icat22, reinterpretAsUInt32(unhex(cat23)) AS icat23, reinterpretAsUInt32(unhex(cat24)) AS icat24, reinterpretAsUInt32(unhex(cat25)) AS icat25, reinterpretAsUInt32(unhex(cat26)) AS icat26 FROM criteo_log;

DROP TABLE criteo_log;

原始文章

AMPLab Big Data Benchmark

参考 https://amplab.cs.berkeley.edu/benchmark/

需要您在Amazon注册一个免费的账号。注册时需要您提供信用卡、邮箱、电话等信息。之后可以在Amazon AWS Console获取新的访问密钥在控制台运行以下命令：

$ sudo apt-get install s3cmd

$ mkdir tiny; cd tiny;

$ s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/tiny/ .

$ cd ..

$ mkdir 1node; cd 1node;

$ s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/1node/ .

$ cd ..

$ mkdir 5nodes; cd 5nodes;

$ s3cmd sync s3://big-data-benchmark/pavlo/text-deflate/5nodes/ .

$ cd ..

在ClickHouse运行如下查询：

CREATE TABLE rankings_tiny (

pageURL String, pageRank UInt32, avgDuration UInt32

) ENGINE = Log;

CREATE TABLE uservisits_tiny (

sourceIP String, destinationURL String, visitDate Date, adRevenue Float32, UserAgent String, cCode FixedString(3), lCode FixedString(6), searchWord String, duration UInt32

) ENGINE = MergeTree(visitDate, visitDate, 8192);

CREATE TABLE rankings_1node (

pageURL String, pageRank UInt32, avgDuration UInt32

) ENGINE = Log;

CREATE TABLE uservisits_1node (

sourceIP String, destinationURL String, visitDate Date, adRevenue Float32, UserAgent String, cCode FixedString(3), lCode FixedString(6), searchWord String, duration UInt32

) ENGINE = MergeTree(visitDate, visitDate, 8192);

CREATE TABLE rankings_5nodes_on_single (

pageURL String, pageRank UInt32, avgDuration UInt32

) ENGINE = Log;

CREATE TABLE uservisits_5nodes_on_single (

sourceIP String, destinationURL String, visitDate Date, adRevenue Float32, UserAgent String, cCode FixedString(3), lCode FixedString(6), searchWord String, duration UInt32

) ENGINE = MergeTree(visitDate, visitDate, 8192);

回到控制台运行如下命令：

简单的查询示例：

SELECT pageURL, pageRank FROM rankings_1node WHERE pageRank > 1000

SELECT substring(sourceIP, 1, 8), sum(adRevenue) FROM uservisits_1node GROUP BY substring(sourceIP, 1, 8)

SELECT

sourceIP,

sum(adRevenue) AS totalRevenue,

avg(pageRank) AS pageRank

FROM rankings_1node ALL INNER JOIN

(

SELECT

sourceIP,

destinationURL AS pageURL, adRevenue

FROM uservisits_1node

WHERE (visitDate > '1980-01-01') AND (visitDate < '1980-04-01')

) USING pageURL

GROUP BY sourceIP

ORDER BY totalRevenue DESC LIMIT 1

原始文章

纽约出租车数据

纽约市出租车数据有以下两个方式获取：

从原始数据导入 下载处理好的数据

怎样导入原始数据

可以参考 https://github.com/toddwschneider/nyc-taxi-data 和 http://tech.marksblogg.com/billion-nyc-taxi-rides-redshift.html 中的关于数据集结构描述与数据下载指令说明。

数据集包含227GB的CSV文件。在1Gbig的带宽下，下载大约需要一个小时这大约需要一个小时的下载时间(从s3.amazonaws.com并行下载时间至少可以缩减一半)。下载时注意损坏的文件。可以检查文件大小并重新下载损坏的文件。

有些文件中包含一些无效的行，您可以使用如下语句修复他们：

sed -E '/(.*,){18,}/d' data/yellow_tripdata_2010-02.csv > data/yellow_tripdata_2010-02.csv_ sed -E '/(.*,){18,}/d' data/yellow_tripdata_2010-03.csv > data/yellow_tripdata_2010-03.csv_ mv data/yellow_tripdata_2010-02.csv_ data/yellow_tripdata_2010-02.csv

mv data/yellow_tripdata_2010-03.csv_ data/yellow_tripdata_2010-03.csv

然后必须在PostgreSQL中对数据进行预处理。这将创建多边形中选择的点(将地图上的点与纽约市的行政区相匹配)，并使用连接将所有数据合并到一个非规范化的平面表中。为此，您需要安装支持PostGIS的PostgreSQL。

运行initialize_database.sh时要小心，并手动重新检查是否正确创建了所有表。

在PostgreSQL中处理每个月的数据大约需要20-30分钟，总共大约需要48小时。您可以按如下方式检查下载的行数：

$ time psql nyc-taxi-data -c "SELECT count(*) FROM trips;" ### Count

1298979494

(1 row)

real 7m9.164s

(根据Mark Litwintschik的系列博客报道数据略多余11亿行)

PostgreSQL处理这些数据大概需要370GB的磁盘空间。从PostgreSQL中导出数据：

COPY

(

SELECT trips.id, trips.vendor_id, trips.pickup_datetime, trips.dropoff_datetime, trips.store_and_fwd_flag, trips.rate_code_id, trips.pickup_longitude, trips.pickup_latitude, trips.dropoff_longitude, trips.dropoff_latitude, trips.passenger_count, trips.trip_distance, trips.fare_amount, trips.extra, trips.mta_tax, trips.tip_amount, trips.tolls_amount, trips.ehail_fee,

trips.improvement_surcharge, trips.total_amount, trips.payment_type, trips.trip_type,

trips.pickup, trips.dropoff,

cab_types.type cab_type,

weather.precipitation_tenths_of_mm rain, weather.snow_depth_mm, weather.snowfall_mm,

weather.max_temperature_tenths_degrees_celsius max_temp, weather.min_temperature_tenths_degrees_celsius min_temp, weather.average_wind_speed_tenths_of_meters_per_second wind,

pick_up.gid pickup_nyct2010_gid, pick_up.ctlabel pickup_ctlabel, pick_up.borocode pickup_borocode, pick_up.boroname pickup_boroname, pick_up.ct2010 pickup_ct2010, pick_up.boroct2010 pickup_boroct2010, pick_up.cdeligibil pickup_cdeligibil, pick_up.ntacode pickup_ntacode, pick_up.ntaname pickup_ntaname, pick_up.puma pickup_puma,

drop_off.gid dropoff_nyct2010_gid, drop_off.ctlabel dropoff_ctlabel, drop_off.borocode dropoff_borocode, drop_off.boroname dropoff_boroname, drop_off.ct2010 dropoff_ct2010, drop_off.boroct2010 dropoff_boroct2010, drop_off.cdeligibil dropoff_cdeligibil, drop_off.ntacode dropoff_ntacode, drop_off.ntaname dropoff_ntaname, drop_off.puma dropoff_puma

FROM trips

LEFT JOIN cab_types

ON trips.cab_type_id = cab_types.id

LEFT JOIN central_park_weather_observations_raw weather

ON weather.date = trips.pickup_datetime::date

LEFT JOIN nyct2010 pick_up

ON pick_up.gid = trips.pickup_nyct2010_gid

LEFT JOIN nyct2010 drop_off

ON drop_off.gid = trips.dropoff_nyct2010_gid

) TO '/opt/milovidov/nyc-taxi-data/trips.tsv';

数据快照的创建速度约为每秒50MB。 在创建快照时，PostgreSQL以每秒约28MB的速度从磁盘读取数据。这大约需要5个小时。 最终生成的TSV文件为590612904969 bytes。

在ClickHouse中创建临时表：

CREATE TABLE trips (

trip_id UInt32,

vendor_id String, pickup_datetime DateTime,

dropoff_datetime Nullable(DateTime), store_and_fwd_flag Nullable(FixedString(1)), rate_code_id Nullable(UInt8), pickup_longitude Nullable(Float64), pickup_latitude Nullable(Float64), dropoff_longitude Nullable(Float64), dropoff_latitude Nullable(Float64), passenger_count Nullable(UInt8), trip_distance Nullable(Float64), fare_amount Nullable(Float32),

extra Nullable(Float32),

mta_tax Nullable(Float32),

tip_amount Nullable(Float32), tolls_amount Nullable(Float32), ehail_fee Nullable(Float32), improvement_surcharge Nullable(Float32), total_amount Nullable(Float32), payment_type Nullable(String), trip_type Nullable(UInt8),

pickup Nullable(String),

dropoff Nullable(String),

cab_type Nullable(String), precipitation Nullable(UInt8), snow_depth Nullable(UInt8),

snowfall Nullable(UInt8), max_temperature Nullable(UInt8), min_temperature Nullable(UInt8), average_wind_speed Nullable(UInt8), pickup_nyct2010_gid Nullable(UInt8), pickup_ctlabel Nullable(String), pickup_borocode Nullable(UInt8), pickup_boroname Nullable(String), pickup_ct2010 Nullable(String), pickup_boroct2010 Nullable(String), pickup_cdeligibil Nullable(FixedString(1)), pickup_ntacode Nullable(String), pickup_ntaname Nullable(String), pickup_puma Nullable(String), dropoff_nyct2010_gid Nullable(UInt8), dropoff_ctlabel Nullable(String), dropoff_borocode Nullable(UInt8), dropoff_boroname Nullable(String), dropoff_ct2010 Nullable(String), dropoff_boroct2010 Nullable(String), dropoff_cdeligibil Nullable(String), dropoff_ntacode Nullable(String), dropoff_ntaname Nullable(String), dropoff_puma Nullable(String)

) ENGINE = Log;

接下来,需要将字段转换为更正确的数据类型，并且在可能的情况下，消除NULL。

$ time clickhouse-client --query="INSERT INTO trips FORMAT TabSeparated" < trips.tsv real 75m56.214s

数据的读取速度为112-140 Mb/秒。

通过这种方式将数据加载到Log表中需要76分钟。这个表中的数据需要使用142GB的磁盘空间.

（也可以直接使用COPY ... TO PROGRAM从Postgres中导入数据）

数据中所有与天气相关的字段(precipitation……average_wind_speed)都填充了NULL。 所以，我们将从最终数据集中删除它们首先，我们使用单台服务器创建表，后面我们将在多台节点上创建这些表。

创建表结构并写入数据：

CREATE TABLE trips_mergetree

ENGINE = MergeTree(pickup_date, pickup_datetime, 8192)

AS SELECT

trip_id,

CAST(vendor_id AS Enum8('1' = 1, '2' = 2, 'CMT' = 3, 'VTS' = 4, 'DDS' = 5, 'B02512' = 10, 'B02598' = 11, 'B02617' = 12, 'B02682' = 13, 'B02764' = 14)) AS vendor_id,

toDate(pickup_datetime) AS pickup_date, ifNull(pickup_datetime, toDateTime(0)) AS pickup_datetime, toDate(dropoff_datetime) AS dropoff_date, ifNull(dropoff_datetime, toDateTime(0)) AS dropoff_datetime,

assumeNotNull(store_and_fwd_flag) IN ('Y', '1', '2') AS store_and_fwd_flag, assumeNotNull(rate_code_id) AS rate_code_id, assumeNotNull(pickup_longitude) AS pickup_longitude, assumeNotNull(pickup_latitude) AS pickup_latitude, assumeNotNull(dropoff_longitude) AS dropoff_longitude, assumeNotNull(dropoff_latitude) AS dropoff_latitude, assumeNotNull(passenger_count) AS passenger_count, assumeNotNull(trip_distance) AS trip_distance, assumeNotNull(fare_amount) AS fare_amount,

assumeNotNull(extra) AS extra, assumeNotNull(mta_tax) AS mta_tax, assumeNotNull(tip_amount) AS tip_amount, assumeNotNull(tolls_amount) AS tolls_amount, assumeNotNull(ehail_fee) AS ehail_fee,

assumeNotNull(improvement_surcharge) AS improvement_surcharge, assumeNotNull(total_amount) AS total_amount,

CAST((assumeNotNull(payment_type) AS pt) IN ('CSH', 'CASH', 'Cash', 'CAS', 'Cas', '1') ? 'CSH' : (pt IN ('CRD', 'Credit', 'Cre', 'CRE', 'CREDIT', '2') ? 'CRE' : (pt IN ('NOC', 'No

Charge', 'No', '3') ? 'NOC' : (pt IN ('DIS', 'Dispute', 'Dis', '4') ? 'DIS' : 'UNK'))) AS Enum8('CSH' = 1, 'CRE' = 2, 'UNK' = 0, 'NOC' = 3, 'DIS' = 4)) AS payment_type_,

assumeNotNull(trip_type) AS trip_type, ifNull(toFixedString(unhex(pickup), 25), toFixedString('', 25)) AS pickup,

ifNull(toFixedString(unhex(dropoff), 25), toFixedString('', 25)) AS dropoff,

CAST(assumeNotNull(cab_type) AS Enum8('yellow' = 1, 'green' = 2, 'uber' = 3)) AS cab_type,

assumeNotNull(pickup_nyct2010_gid) AS pickup_nyct2010_gid, toFloat32(ifNull(pickup_ctlabel, '0')) AS pickup_ctlabel, assumeNotNull(pickup_borocode) AS pickup_borocode,

CAST(assumeNotNull(pickup_boroname) AS Enum8('Manhattan' = 1, 'Queens' = 4, 'Brooklyn' = 3, '' = 0, 'Bronx' = 2, 'Staten Island' = 5)) AS pickup_boroname, toFixedString(ifNull(pickup_ct2010, '000000'), 6) AS pickup_ct2010,

toFixedString(ifNull(pickup_boroct2010, '0000000'), 7) AS pickup_boroct2010, CAST(assumeNotNull(ifNull(pickup_cdeligibil, ' ')) AS Enum8(' ' = 0, 'E' = 1, 'I' = 2)) AS pickup_cdeligibil, toFixedString(ifNull(pickup_ntacode, '0000'), 4) AS pickup_ntacode,

CAST(assumeNotNull(pickup_ntaname) AS Enum16('' = 0, 'Airport' = 1, 'Allerton-Pelham Gardens' = 2, 'Annadale-Huguenot-Prince\'s Bay-Eltingville' = 3, 'Arden Heights' = 4, 'Astoria' = 5, 'Auburndale' = 6, 'Baisley Park' = 7, 'Bath Beach' = 8, 'Battery Park City-Lower Manhattan' = 9, 'Bay Ridge' = 10, 'Bayside-Bayside Hills' = 11, 'Bedford' = 12, 'Bedford Park-Fordham North' = 13, 'Bellerose' = 14, 'Belmont' = 15, 'Bensonhurst East' = 16, 'Bensonhurst West' = 17, 'Borough Park' = 18, 'Breezy Point-Belle

Harbor-Rockaway Park-Broad Channel' = 19, 'Briarwood-Jamaica Hills' = 20, 'Brighton Beach' = 21, 'Bronxdale' = 22, 'Brooklyn Heights-Cobble Hill' = 23, 'Brownsville' = 24, 'Bushwick North' = 25, 'Bushwick South' = 26, 'Cambria Heights' = 27, 'Canarsie' = 28, 'Carroll Gardens-Columbia Street-Red Hook' = 29, 'Central Harlem North-Polo Grounds' = 30, 'Central Harlem South' = 31, 'Charleston-Richmond Valley-Tottenville' = 32, 'Chinatown' = 33, 'Claremont-Bathgate' = 34, 'Clinton' = 35, 'Clinton Hill' = 36, 'Co-op City' = 37, 'College Point' = 38, 'Corona' = 39, 'Crotona Park East' = 40, 'Crown Heights North' = 41, 'Crown Heights South' = 42, 'Cypress Hills-City Line' = 43, 'DUMBO-Vinegar Hill-Downtown Brooklyn-Boerum Hill' = 44, 'Douglas Manor-Douglaston-Little Neck' = 45, 'Dyker Heights' = 46, 'East Concourse-Concourse Village' = 47, 'East Elmhurst' = 48, 'East Flatbush-Farragut' = 49, 'East Flushing' = 50, 'East Harlem North' = 51, 'East Harlem South' = 52, 'East New York' = 53, 'East New York (Pennsylvania Ave)' = 54, 'East Tremont' = 55, 'East Village' = 56, 'East Williamsburg' = 57, 'Eastchester-Edenwald-Baychester' = 58, 'Elmhurst' = 59, 'Elmhurst-Maspeth' = 60, 'Erasmus' = 61, 'Far Rockaway-Bayswater' = 62, 'Flatbush' = 63, 'Flatlands' = 64, 'Flushing' = 65, 'Fordham South' = 66, 'Forest Hills' = 67, 'Fort Greene' = 68, 'Fresh Meadows-Utopia' = 69, 'Ft. Totten-Bay Terrace-Clearview' = 70, 'Georgetown-Marine Park-Bergen Beach-Mill Basin' = 71, 'Glen Oaks-Floral Park-New Hyde Park' = 72, 'Glendale' = 73, 'Gramercy' = 74, 'Grasmere-Arrochar-Ft. Wadsworth' = 75, 'Gravesend' = 76, 'Great Kills' = 77, 'Greenpoint' = 78, 'Grymes Hill-Clifton-Fox Hills' = 79, 'Hamilton Heights' = 80, 'Hammels-Arverne-Edgemere' = 81, 'Highbridge' = 82, 'Hollis' = 83, 'Homecrest' = 84, 'Hudson Yards-Chelsea-Flatiron-Union Square' = 85, 'Hunters Point-Sunnyside-West Maspeth' = 86, 'Hunts Point' = 87, 'Jackson Heights' = 88, 'Jamaica' = 89, 'Jamaica Estates-Holliswood' = 90, 'Kensington-Ocean Parkway' = 91, 'Kew Gardens' = 92, 'Kew Gardens Hills' = 93, 'Kingsbridge Heights' = 94, 'Laurelton' = 95, 'Lenox Hill-Roosevelt Island' = 96, 'Lincoln Square' = 97,

'Lindenwood-Howard Beach' = 98, 'Longwood' = 99, 'Lower East Side' = 100, 'Madison' = 101, 'Manhattanville' = 102, 'Marble Hill-Inwood' = 103, 'Mariner\'s Harbor- Arlington-Port Ivory-Graniteville' = 104, 'Maspeth' = 105, 'Melrose South-Mott Haven North' = 106, 'Middle Village' = 107, 'Midtown-Midtown South' = 108, 'Midwood' = 109, 'Morningside Heights' = 110, 'Morrisania-Melrose' = 111, 'Mott Haven-Port Morris' = 112, 'Mount Hope' = 113, 'Murray Hill' = 114, 'Murray Hill-Kips Bay' = 115, 'New Brighton-Silver Lake' = 116, 'New Dorp-Midland Beach' = 117, 'New Springville-Bloomfield-Travis' = 118, 'North Corona' = 119, 'North Riverdale-Fieldston-Riverdale' = 120, 'North Side-South Side' = 121, 'Norwood' = 122, 'Oakland Gardens' = 123, 'Oakwood-Oakwood Beach' = 124, 'Ocean Hill' = 125, 'Ocean Parkway South' = 126, 'Old Astoria'

= 127, 'Old Town-Dongan Hills-South Beach' = 128, 'Ozone Park' = 129, 'Park Slope-Gowanus' = 130, 'Parkchester' = 131, 'Pelham Bay-Country Club-City Island' = 132, 'Pelham Parkway' = 133, 'Pomonok-Flushing Heights-Hillcrest' = 134, 'Port Richmond' = 135, 'Prospect Heights' = 136, 'Prospect Lefferts Gardens-Wingate' = 137, 'Queens Village' = 138, 'Queensboro Hill' = 139, 'Queensbridge-Ravenswood-Long Island City' = 140, 'Rego Park' = 141, 'Richmond Hill' = 142, 'Ridgewood' = 143, 'Rikers Island' = 144, 'Rosedale' = 145, 'Rossville-Woodrow' = 146, 'Rugby-Remsen Village' = 147, 'Schuylerville-Throgs Neck-Edgewater Park' = 148, 'Seagate-Coney Island' = 149, 'Sheepshead Bay-Gerritsen Beach-Manhattan Beach' = 150, 'SoHo-TriBeCa-Civic Center-Little Italy' = 151, 'Soundview-Bruckner' = 152, 'Soundview-Castle Hill-Clason Point- Harding Park' = 153, 'South Jamaica' = 154, 'South Ozone Park' = 155, 'Springfield Gardens North' = 156, 'Springfield Gardens South-Brookville' = 157, 'Spuyten Duyvil- Kingsbridge' = 158, 'St. Albans' = 159, 'Stapleton-Rosebank' = 160, 'Starrett City' = 161, 'Steinway' = 162, 'Stuyvesant Heights' = 163, 'Stuyvesant Town-Cooper Village' = 164, 'Sunset Park East' = 165, 'Sunset Park West' = 166, 'Todt Hill-Emerson Hill-Heartland Village-Lighthouse Hill' = 167, 'Turtle Bay-East Midtown' = 168, 'University Heights-Morris Heights' = 169, 'Upper East Side-Carnegie Hill' = 170, 'Upper West Side' = 171, 'Van Cortlandt Village' = 172, 'Van Nest-Morris Park-Westchester Square' = 173, 'Washington Heights North' = 174, 'Washington Heights South' = 175, 'West Brighton' = 176, 'West Concourse' = 177, 'West Farms-Bronx River' = 178, 'West New Brighton-New Brighton-St. George' = 179, 'West Village' = 180, 'Westchester-Unionport' = 181, 'Westerleigh' = 182, 'Whitestone' = 183, 'Williamsbridge-Olinville' = 184, 'Williamsburg' = 185, 'Windsor Terrace' = 186, 'Woodhaven' = 187, 'Woodlawn-Wakefield' = 188, 'Woodside' = 189, 'Yorkville' = 190, 'park-cemetery-etc-Bronx' = 191, 'park-cemetery-etc-Brooklyn' = 192, 'park-cemetery-etc-Manhattan' = 193, 'park-cemetery-etc-Queens' = 194, 'park-cemetery-etc-Staten Island' = 195)) AS pickup_ntaname,

toUInt16(ifNull(pickup_puma, '0')) AS pickup_puma,

assumeNotNull(dropoff_nyct2010_gid) AS dropoff_nyct2010_gid, toFloat32(ifNull(dropoff_ctlabel, '0')) AS dropoff_ctlabel, assumeNotNull(dropoff_borocode) AS dropoff_borocode,

CAST(assumeNotNull(dropoff_boroname) AS Enum8('Manhattan' = 1, 'Queens' = 4, 'Brooklyn' = 3, '' = 0, 'Bronx' = 2, 'Staten Island' = 5)) AS dropoff_boroname, toFixedString(ifNull(dropoff_ct2010, '000000'), 6) AS dropoff_ct2010,

toFixedString(ifNull(dropoff_boroct2010, '0000000'), 7) AS dropoff_boroct2010, CAST(assumeNotNull(ifNull(dropoff_cdeligibil, ' ')) AS Enum8(' ' = 0, 'E' = 1, 'I' = 2)) AS dropoff_cdeligibil, toFixedString(ifNull(dropoff_ntacode, '0000'), 4) AS dropoff_ntacode,

CAST(assumeNotNull(dropoff_ntaname) AS Enum16('' = 0, 'Airport' = 1, 'Allerton-Pelham Gardens' = 2, 'Annadale-Huguenot-Prince\'s Bay-Eltingville' = 3, 'Arden Heights' = 4, 'Astoria' = 5, 'Auburndale' = 6, 'Baisley Park' = 7, 'Bath Beach' = 8, 'Battery Park City-Lower Manhattan' = 9, 'Bay Ridge' = 10, 'Bayside-Bayside Hills' = 11, 'Bedford' = 12, 'Bedford Park-Fordham North' = 13, 'Bellerose' = 14, 'Belmont' = 15, 'Bensonhurst East' = 16, 'Bensonhurst West' = 17, 'Borough Park' = 18, 'Breezy Point-Belle

Harbor-Rockaway Park-Broad Channel' = 19, 'Briarwood-Jamaica Hills' = 20, 'Brighton Beach' = 21, 'Bronxdale' = 22, 'Brooklyn Heights-Cobble Hill' = 23, 'Brownsville' = 24, 'Bushwick North' = 25, 'Bushwick South' = 26, 'Cambria Heights' = 27, 'Canarsie' = 28, 'Carroll Gardens-Columbia Street-Red Hook' = 29, 'Central Harlem North-Polo Grounds' = 30, 'Central Harlem South' = 31, 'Charleston-Richmond Valley-Tottenville' = 32, 'Chinatown' = 33, 'Claremont-Bathgate' = 34, 'Clinton' = 35, 'Clinton Hill' = 36, 'Co-op City' = 37, 'College Point' = 38, 'Corona' = 39, 'Crotona Park East' = 40, 'Crown Heights North' = 41, 'Crown Heights South' = 42, 'Cypress Hills-City Line' = 43, 'DUMBO-Vinegar Hill-Downtown Brooklyn-Boerum Hill' = 44, 'Douglas Manor-Douglaston-Little Neck' = 45, 'Dyker Heights' = 46, 'East Concourse-Concourse Village' = 47, 'East Elmhurst' = 48, 'East Flatbush-Farragut' = 49, 'East Flushing' = 50, 'East Harlem North' = 51, 'East Harlem South' = 52, 'East New York' = 53, 'East New York (Pennsylvania Ave)' = 54, 'East Tremont' = 55, 'East Village' = 56, 'East Williamsburg' = 57, 'Eastchester-Edenwald-Baychester' = 58, 'Elmhurst' = 59, 'Elmhurst-Maspeth' = 60, 'Erasmus' = 61, 'Far Rockaway-Bayswater' = 62, 'Flatbush' = 63, 'Flatlands' = 64, 'Flushing' = 65, 'Fordham South' = 66, 'Forest Hills' = 67, 'Fort Greene' = 68, 'Fresh Meadows-Utopia' = 69, 'Ft. Totten-Bay Terrace-Clearview' = 70, 'Georgetown-Marine Park-Bergen Beach-Mill Basin' = 71, 'Glen Oaks-Floral Park-New Hyde Park' = 72, 'Glendale' = 73, 'Gramercy' = 74, 'Grasmere-Arrochar-Ft. Wadsworth' = 75, 'Gravesend' = 76, 'Great Kills' = 77, 'Greenpoint' = 78, 'Grymes Hill-Clifton-Fox Hills' = 79, 'Hamilton Heights' = 80, 'Hammels-Arverne-Edgemere' = 81, 'Highbridge' = 82, 'Hollis' = 83, 'Homecrest' = 84, 'Hudson Yards-Chelsea-Flatiron-Union Square' = 85, 'Hunters Point-Sunnyside-West Maspeth' = 86, 'Hunts Point' = 87, 'Jackson Heights' = 88, 'Jamaica' = 89, 'Jamaica Estates-Holliswood' = 90, 'Kensington-Ocean Parkway' = 91, 'Kew Gardens' = 92, 'Kew Gardens Hills' = 93, 'Kingsbridge Heights' = 94, 'Laurelton' = 95, 'Lenox Hill-Roosevelt Island' = 96, 'Lincoln Square' = 97,

'Lindenwood-Howard Beach' = 98, 'Longwood' = 99, 'Lower East Side' = 100, 'Madison' = 101, 'Manhattanville' = 102, 'Marble Hill-Inwood' = 103, 'Mariner\'s Harbor- Arlington-Port Ivory-Graniteville' = 104, 'Maspeth' = 105, 'Melrose South-Mott Haven North' = 106, 'Middle Village' = 107, 'Midtown-Midtown South' = 108, 'Midwood' = 109, 'Morningside Heights' = 110, 'Morrisania-Melrose' = 111, 'Mott Haven-Port Morris' = 112, 'Mount Hope' = 113, 'Murray Hill' = 114, 'Murray Hill-Kips Bay' = 115, 'New Brighton-Silver Lake' = 116, 'New Dorp-Midland Beach' = 117, 'New Springville-Bloomfield-Travis' = 118, 'North Corona' = 119, 'North Riverdale-Fieldston-Riverdale' = 120, 'North Side-South Side' = 121, 'Norwood' = 122, 'Oakland Gardens' = 123, 'Oakwood-Oakwood Beach' = 124, 'Ocean Hill' = 125, 'Ocean Parkway South' = 126, 'Old Astoria'

= 127, 'Old Town-Dongan Hills-South Beach' = 128, 'Ozone Park' = 129, 'Park Slope-Gowanus' = 130, 'Parkchester' = 131, 'Pelham Bay-Country Club-City Island' = 132, 'Pelham Parkway' = 133, 'Pomonok-Flushing Heights-Hillcrest' = 134, 'Port Richmond' = 135, 'Prospect Heights' = 136, 'Prospect Lefferts Gardens-Wingate' = 137, 'Queens Village' = 138, 'Queensboro Hill' = 139, 'Queensbridge-Ravenswood-Long Island City' = 140, 'Rego Park' = 141, 'Richmond Hill' = 142, 'Ridgewood' = 143, 'Rikers Island' = 144, 'Rosedale' = 145, 'Rossville-Woodrow' = 146, 'Rugby-Remsen Village' = 147, 'Schuylerville-Throgs Neck-Edgewater Park' = 148, 'Seagate-Coney Island' = 149, 'Sheepshead Bay-Gerritsen Beach-Manhattan Beach' = 150, 'SoHo-TriBeCa-Civic Center-Little Italy' = 151, 'Soundview-Bruckner' = 152, 'Soundview-Castle Hill-Clason Point- Harding Park' = 153, 'South Jamaica' = 154, 'South Ozone Park' = 155, 'Springfield Gardens North' = 156, 'Springfield Gardens South-Brookville' = 157, 'Spuyten Duyvil- Kingsbridge' = 158, 'St. Albans' = 159, 'Stapleton-Rosebank' = 160, 'Starrett City' = 161, 'Steinway' = 162, 'Stuyvesant Heights' = 163, 'Stuyvesant Town-Cooper Village' = 164, 'Sunset Park East' = 165, 'Sunset Park West' = 166, 'Todt Hill-Emerson Hill-Heartland Village-Lighthouse Hill' = 167, 'Turtle Bay-East Midtown' = 168, 'University Heights-Morris Heights' = 169, 'Upper East Side-Carnegie Hill' = 170, 'Upper West Side' = 171, 'Van Cortlandt Village' = 172, 'Van Nest-Morris Park-Westchester Square' = 173, 'Washington Heights North' = 174, 'Washington Heights South' = 175, 'West Brighton' = 176, 'West Concourse' = 177, 'West Farms-Bronx River' = 178, 'West New Brighton-New Brighton-St. George' = 179, 'West Village' = 180, 'Westchester-Unionport' = 181, 'Westerleigh' = 182, 'Whitestone' = 183, 'Williamsbridge-Olinville' = 184, 'Williamsburg' = 185, 'Windsor Terrace' = 186, 'Woodhaven' = 187, 'Woodlawn-Wakefield' = 188, 'Woodside' = 189, 'Yorkville' = 190, 'park-cemetery-etc-Bronx' = 191, 'park-cemetery-etc-Brooklyn' = 192, 'park-cemetery-etc-Manhattan' = 193, 'park-cemetery-etc-Queens' = 194, 'park-cemetery-etc-Staten Island' = 195)) AS dropoff_ntaname,

toUInt16(ifNull(dropoff_puma, '0')) AS dropoff_puma

FROM trips

这需要3030秒，速度约为每秒428,000行。

要加快速度，可以使用Log引擎替换MergeTree引擎来创建表。 在这种情况下，下载速度超过200秒。这个表需要使用126GB的磁盘空间。

SELECT formatReadableSize(sum(bytes)) FROM system.parts WHERE table = 'trips_mergetree' AND active

┌─formatReadableSize(sum(bytes))─┐

│ 126.18 GiB │

└────────────────────────────────┘

除此之外，你还可以在MergeTree上运行OPTIMIZE查询来进行优化。但这不是必须的，因为即使在没有进行优化的情况下它的表现依然是很好的。

下载预处理好的分区数据

$ curl -O https://datasets.clickhouse.tech/trips_mergetree/partitions/trips_mergetree.tar

$ tar xvf trips_mergetree.tar -C /var/lib/clickhouse # path to ClickHouse data directory

$ # check permissions of unpacked data, fix if required

$ sudo service clickhouse-server restart

$ clickhouse-client --query "select count(*) from datasets.trips_mergetree"

信息

如果要运行下面的SQL查询，必须使用完整的表名，datasets.trips_mergetree。

单台服务器运行结果

Q1:

SELECT cab_type, count(*) FROM trips_mergetree GROUP BY cab_type

0.490秒 Q2:

SELECT passenger_count, avg(total_amount) FROM trips_mergetree GROUP BY passenger_count

1.224秒 Q3:

SELECT passenger_count, toYear(pickup_date) AS year, count(*) FROM trips_mergetree GROUP BY passenger_count, year

2.104秒 Q4:

SELECT passenger_count, toYear(pickup_date) AS year, round(trip_distance) AS distance, count(*) FROM trips_mergetree

GROUP BY passenger_count, year, distance

ORDER BY year, count(*) DESC

3.593秒

我们使用的是如下配置的服务器：

两个Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz，总共有16个物理内核，128GiB RAM，8X6TB HD，RAID-5

执行时间是取三次运行中最好的值，但是从第二次查询开始，查询就将从文件系统的缓存中读取数据。同时在每次读取和处理后不在进行缓存。 在三台服务器中创建表结构：

在每台服务器中运行：

在之前的服务器中运行：

CREATE TABLE trips_mergetree_x3 AS trips_mergetree_third ENGINE = Distributed(perftest, default, trips_mergetree_third, rand())

运行如下查询重新分布数据：

INSERT INTO trips_mergetree_x3 SELECT * FROM trips_mergetree

这个查询需要运行2454秒。

在三台服务器集群中运行的结果：

Q1: 0.212秒. Q2：0.438秒。 Q3：0.733秒。 Q4: 1.241秒.

这并不奇怪，因为查询是线性扩展的。

我们同时在140台服务器的集群中运行的结果：

Q1：0.028秒。 Q2：0.043秒。 Q3：0.051秒。 Q4：0.072秒。

在这种情况下，查询处理时间首先由网络延迟确定。

我们使用位于芬兰Yandex数据中心的客户机在俄罗斯的一个集群上运行查询，这增加了大约20毫秒的延迟。

总结

原始文章

OnTime

航班飞行数据有以下两个方式获取：

从原始数据导入

下载预处理好的数据

从原始数据导入

下载数据：

for s in `seq 1987 2018`

do

for m in `seq 1 12`

do

wget https://transtats.bts.gov/PREZIP/On_Time_Reporting_Carrier_On_Time_Performance_1987_present_${s}_${m}.zip

done done

(参考 https://github.com/Percona-Lab/ontime-airline-performance/blob/master/download.sh )

创建表结构：

CREATE TABLE `ontime` (

`Year` UInt16,

`Quarter` UInt8,

`Month` UInt8,

`DayofMonth` UInt8,

`DayOfWeek` UInt8,

`FlightDate` Date,

`Reporting_Airline` String,

`DOT_ID_Reporting_Airline` Int32,

`IATA_CODE_Reporting_Airline` String,

`Tail_Number` Int32,

`Flight_Number_Reporting_Airline` String,

`OriginAirportID` Int32,

`OriginAirportSeqID` Int32,

`OriginCityMarketID` Int32,

`Origin` FixedString(5),

`OriginCityName` String,

`OriginState` FixedString(2),

`OriginStateFips` String,

`OriginStateName` String,

`OriginWac` Int32,

`DestAirportID` Int32,

`DestAirportSeqID` Int32,

`DestCityMarketID` Int32,

`Dest` FixedString(5),

`DestCityName` String,

`DestState` FixedString(2),

`DestStateFips` String,

`DestStateName` String,

`DestWac` Int32,

`CRSDepTime` Int32,

`DepTime` Int32,

`DepDelay` Int32,

`DepDelayMinutes` Int32,

`DepDel15` Int32,

`DepartureDelayGroups` String,

`DepTimeBlk` String,

`TaxiOut` Int32,

`WheelsOff` Int32,

`WheelsOn` Int32,

`TaxiIn` Int32,

`CRSArrTime` Int32,

`ArrTime` Int32,

`ArrDelay` Int32,

`ArrDelayMinutes` Int32,

`ArrDel15` Int32,

`ArrivalDelayGroups` Int32,

`ArrTimeBlk` String,

`Cancelled` UInt8,

`CancellationCode` FixedString(1),

`Diverted` UInt8,

`CRSElapsedTime` Int32,

`ActualElapsedTime` Int32,

`AirTime` Nullable(Int32),

`Flights` Int32,

`Distance` Int32,

`DistanceGroup` UInt8,

`CarrierDelay` Int32,

`WeatherDelay` Int32,

`NASDelay` Int32,

`SecurityDelay` Int32,

`LateAircraftDelay` Int32,

`FirstDepTime` String,

`TotalAddGTime` String,

`LongestAddGTime` String,

`DivAirportLandings` String,

`DivReachedDest` String,

`DivActualElapsedTime` String,

`DivArrDelay` String,

`DivDistance` String,

`Div1Airport` String,

`Div1AirportID` Int32,

`Div1AirportSeqID` Int32,

`Div1WheelsOn` String,

`Div1TotalGTime` String,

`Div1LongestGTime` String,

`Div1WheelsOff` String,

`Div1TailNum` String,

`Div2Airport` String,

`Div2AirportID` Int32,

`Div2AirportSeqID` Int32,

`Div2WheelsOn` String,

`Div2TotalGTime` String,

`Div2LongestGTime` String,

`Div2WheelsOff` String,

`Div2TailNum` String,

`Div3Airport` String,

`Div3AirportID` Int32,

`Div3AirportSeqID` Int32,

`Div3WheelsOn` String,

`Div3TotalGTime` String,

`Div3LongestGTime` String,

`Div3WheelsOff` String,

`Div3TailNum` String,

`Div4Airport` String,

`Div4AirportID` Int32,

`Div4AirportSeqID` Int32,

`Div4WheelsOn` String,

`Div4TotalGTime` String,

`Div4LongestGTime` String,

`Div4WheelsOff` String,

`Div4TailNum` String,

`Div5Airport` String,

`Div5AirportID` Int32,

`Div5AirportSeqID` Int32,

`Div5WheelsOn` String,

`Div5TotalGTime` String,

`Div5LongestGTime` String,

`Div5WheelsOff` String,

`Div5TailNum` String

) ENGINE = MergeTree PARTITION BY Year

ORDER BY (IATA_CODE_Reporting_Airline, FlightDate) SETTINGS index_granularity = 8192;

加载数据：

ls -1 *.zip | xargs -I{} -P $(nproc) bash -c "echo {}; unzip -cq {} '*.csv' | sed 's/\.00//g' | clickhouse-client --input_format_with_names_use_header=0 --query='INSERT INTO ontime FORMAT CSVWithNames'"

下载预处理好的分区数据

$ curl -O https://datasets.clickhouse.tech/ontime/partitions/ontime.tar

$ tar xvf ontime.tar -C /var/lib/clickhouse # path to ClickHouse data directory

$ # check permissions of unpacked data, fix if required

$ sudo service clickhouse-server restart

$ clickhouse-client --query "select count(*) from datasets.ontime"

信息

如果要运行下面的SQL查询，必须使用完整的表名，datasets.ontime。

查询：

Q0.

SELECT avg(c1) FROM

(

SELECT Year, Month, count(*) AS c1

FROM ontime

GROUP BY Year, Month

);

Q1. 查询从2000年到2008年每天的航班数

SELECT DayOfWeek, count(*) AS c FROM ontime

WHERE Year>=2000 AND Year<=2008

GROUP BY DayOfWeek

ORDER BY c DESC;

Q2. 查询从2000年到2008年每周延误超过10分钟的航班数。

SELECT DayOfWeek, count(*) AS c FROM ontime

WHERE DepDelay>10 AND Year>=2000 AND Year<=2008

GROUP BY DayOfWeek

ORDER BY c DESC;

Q3. 查询2000年到2008年每个机场延误超过10分钟以上的次数

SELECT Origin, count(*) AS c FROM ontime

WHERE DepDelay>10 AND Year>=2000 AND Year<=2008

GROUP BY Origin ORDER BY c DESC LIMIT 10;

Q4. 查询2007年各航空公司延误超过10分钟以上的次数

SELECT IATA_CODE_Reporting_Airline AS Carrier, count(*) FROM ontime

WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier

ORDER BY count(*) DESC;

Q5. 查询2007年各航空公司延误超过10分钟以上的百分比

SELECT Carrier, c, c2, c*100/c2 as c3

FROM

(

SELECT

IATA_CODE_Reporting_Airline AS Carrier,

count(*) AS c FROM ontime WHERE DepDelay>10

AND Year=2007

GROUP BY Carrier

) q JOIN (

SELECT

IATA_CODE_Reporting_Airline AS Carrier,

count(*) AS c2 FROM ontime WHERE Year=2007

GROUP BY Carrier

) qq USING Carrier

ORDER BY c3 DESC;

更好的查询版本：

SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(DepDelay>10)*100 AS c3

FROM ontime

WHERE Year=2007

GROUP BY Carrier

ORDER BY c3 DESC

Q6. 同上一个查询一致,只是查询范围扩大到2000年到2008年

SELECT Carrier, c, c2, c*100/c2 as c3

FROM

(

SELECT

IATA_CODE_Reporting_Airline AS Carrier,

count(*) AS c FROM ontime WHERE DepDelay>10

AND Year>=2000 AND Year<=2008

GROUP BY Carrier

) q JOIN (

SELECT

IATA_CODE_Reporting_Airline AS Carrier,

count(*) AS c2

FROM ontime

WHERE Year>=2000 AND Year<=2008

GROUP BY Carrier

) qq USING Carrier

ORDER BY c3 DESC;

更好的查询版本：

SELECT IATA_CODE_Reporting_Airline AS Carrier, avg(DepDelay>10)*100 AS c3

FROM ontime

WHERE Year>=2000 AND Year<=2008

GROUP BY Carrier

ORDER BY c3 DESC;

Q7. 每年航班延误超过10分钟的百分比

SELECT Year, c1/c2

FROM

(

select

Year, count(*)*100 as c1

from ontime

WHERE DepDelay>10

GROUP BY Year

) q JOIN (

select

Year, count(*) as c2

from ontime

GROUP BY Year

) qq USING (Year)

ORDER BY Year;

更好的查询版本：

SELECT Year, avg(DepDelay>10)*100 FROM ontime

GROUP BY Year ORDER BY Year;

Q8. 每年更受人们喜爱的目的地

SELECT DestCityName, uniqExact(OriginCityName) AS u

FROM ontime

WHERE Year >= 2000 and Year <= 2010

GROUP BY DestCityName

ORDER BY u DESC LIMIT 10;

Q9.

SELECT Year, count(*) AS c1

FROM ontime

GROUP BY Year;

Q10.

SELECT

min(Year), max(Year), IATA_CODE_Reporting_Airline AS Carrier, count(*) AS cnt, sum(ArrDelayMinutes>30) AS flights_delayed, round(sum(ArrDelayMinutes>30)/count(*),2) AS rate

FROM ontime

WHERE

DayOfWeek NOT IN (6,7) AND OriginState NOT IN ('AK', 'HI', 'PR', 'VI')

AND DestState NOT IN ('AK', 'HI', 'PR', 'VI')

AND FlightDate < '2010-01-01'

GROUP by Carrier

HAVING cnt>100000 and max(Year)>1990 ORDER by rate DESC

LIMIT 1000;

Bonus:

SELECT avg(cnt) FROM

(

SELECT Year,Month,count(*) AS cnt

FROM ontime

WHERE DepDel15=1

GROUP BY Year,Month

);

SELECT avg(c1) FROM

(

SELECT Year,Month,count(*) AS c1

FROM ontime

GROUP BY Year,Month

);

SELECT DestCityName, uniqExact(OriginCityName) AS u

FROM ontime

GROUP BY DestCityName ORDER BY u DESC LIMIT 10;

SELECT OriginCityName, DestCityName, count() AS c FROM ontime

GROUP BY OriginCityName, DestCityName

ORDER BY c DESC LIMIT 10;

SELECT OriginCityName, count() AS c FROM ontime

GROUP BY OriginCityName

ORDER BY c DESC LIMIT 10;

这个性能测试由Vadim Tkachenko提供。参考：

https://www.percona.com/blog/2009/10/02/analyzing-air-traffic-performance-with-infobright-and-monetdb/ https://www.percona.com/blog/2009/10/26/air-traffic-queries-in-luciddb/ https://www.percona.com/blog/2009/11/02/air-traffic-queries-in-infinidb-early-alpha/ https://www.percona.com/blog/2014/04/21/using-apache-hadoop-and-impala-together-with-mysql-for-data-analysis/ https://www.percona.com/blog/2016/01/07/apache-spark-with-air-ontime-performance-data/ http://nickmakos.blogspot.ru/2012/08/analyzing-air-traffic-performance-with.html

原始文章

ClickHouse体验平台

ClickHouse体验平台 允许人们通过即时运行查询来尝试ClickHouse，而无需设置他们的服务器或集群。

体验平台中提供几个示例数据集以及显示ClickHouse特性的示例查询。还有一些ClickHouse LTS版本可供尝试。

ClickHouse体验平台提供了小型集群Managed Service for ClickHouse实例配置(4 vCPU, 32 GB RAM)它们托管在Yandex.Cloud. 更多信息查询cloud providers.

您可以使用任何HTTP客户端对ClickHouse体验平台进行查询，例如curl或者wget,或使用JDBC或者ODBC驱动连接。关于支持ClickHouse的软件产品的更多信息详见here.

Credentials

还有一些带有特定ClickHouse版本的附加信息来试验它们之间的差异(端口和用户/密码与上面相同):

20.3 LTS: play-api-v20-3.clickhouse.tech

19.14 LTS: play-api-v19-14.clickhouse.tech

注意

所有这些端点都需要安全的TLS连接。

查询限制

查询以只读用户身份执行。 这意味着一些局限性:

不允许DDL查询不允许插入查询

还强制执行以下设置:

• max_result_bytes=10485760
• max_result_rows=2000
• result_overflow_mode=break
• max_execution_time=60000

ClickHouse体验还有如下： ClickHouse管理服务

实例托管 Yandex云。更多信息 云提供商。

示例

使用curl连接Https服务：

curl "https://play-api.clickhouse.tech:8443/?query=SELECT+'Play+ClickHouse\!';&user=playground&password=clickhouse&database=datasets"

TCP连接示例CLI:

clickhouse client --secure -h play-api.clickhouse.tech --port 9440 -u playground --password clickhouse -q "SELECT 'Play ClickHouse\!'"

Implementation Details

ClickHouse体验平台界面实际上是通过ClickHouse HTTP API接口实现的。

ClickHouse体验平台是一个ClickHouse集群，没有任何附加的服务器端应用程序。如上所述，ClickHouse的HTTPS和TCP/TLS端点也可以作为体验平台的一部分公开使用, 代理通过Cloudflare Spectrum增加一层额外的保护和改善连接。

注意

强烈不推荐在任何其他情况下将ClickHouse服务器暴露给公共互联网。确保它只在私有网络上侦听，并由正确配置的防火墙监控。

客户端

ClickHouse提供了两个网络接口(两个都可以选择包装在TLS中以增加安全性):

HTTP, 包含文档，易于使用。

Native TCP，简单，方便使用。

在大多数情况下，建议使用适当的工具或库，而不是直接与它们交互。Yandex官方支持的项目有:

命令行客户端JDBC驱动 ODBC驱动C++客户端

还有一些广泛的第三方库可供ClickHouse使用:

客户端库

第三方集成库可视化UI

来源文章

命令行客户端

ClickHouse提供了一个原生命令行客户端clickhouse-client客户端支持命令行支持的更多信息详见Configuring。安装部署后，系统默认会安装clickhouse-client(同时它属于clickhouse-client安装包中)。

$ clickhouse-client

ClickHouse client version 19.17.1.1579 (official build). Connecting to localhost:9000 as user default.

Connected to ClickHouse server version 19.17.1 revision 54428.

:)

不同的客户端和服务器版本彼此兼容，但是一些特性可能在旧客户机中不可用。我们建议使用与服务器应用相同版本的客户端。当你尝试使用旧版本的客户端时，服务器上 的clickhouse-client会显示如下信息:

ClickHouse client version is older than ClickHouse server. It may lack support for new features.

使用方式

客户端可以在交互和非交互(批处理)模式下使用。要使用批处理模式，请指定query参数，或将数据发送到stdin(它会验证stdin是否是终端)，或两者同时进行。与HTTP接口类似，当使用query参数并向stdin发送数据时，客户端请求就是一行一行的stdin输入作为query的参数。这种方式在大规模的插入请求中非常方便。

使用客户端插入数据的示例：

$ echo -ne "1, 'some text', '2016-08-14 00:00:00'\n2, 'some more text', '2016-08-14 00:00:01'" | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV";

$ cat <<_EOF | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV"; 3, 'some text', '2016-08-14 00:00:00'

4, 'some more text', '2016-08-14 00:00:01'

_EOF

$ cat file.csv | clickhouse-client --database=test --query="INSERT INTO test FORMAT CSV";

在批量模式中，默认的数据格式是TabSeparated分隔的。您可以根据查询来灵活设置FORMAT格式。

默认情况下，在批量模式中只能执行单个查询。为了从一个Script中执行多个查询，可以使用--multiquery参数。除了INSERT请求外，这种方式在任何地方都有用。查询的结果会连续且不含分隔符地输出。

同样的，为了执行大规模的查询，您可以为每个查询执行一次clickhouse-client。但注意到每次启动clickhouse-client程序都需要消耗几十毫秒时间。在交互模式下，每条查询过后，你可以直接输入下一条查询命令。

如果multiline没有指定（默认没指定）：为了执行查询，按下Enter即可。查询语句不是必须使用分号结尾。如果需要写一个多行的查询语句，可以在换行之前输入一个反斜杠\，然后在您按下Enter键后，您就可以输入当前语句的下一行查询了。

如果指定了multiline：为了执行查询，需要以分号结尾并且按下Enter键。如果行末没有分号，将认为当前语句并没有输入完而要求继续输入下一行。若只运行单个查询，分号后面的所有内容都会被忽略。

您可以指定\G来替代分号或者在分号后面，这表示使用Vertical的格式。在这种格式下，每一个值都会打印在不同的行中，这种方式对于宽表来说很方便。这个不常见的特性是为了兼容MySQL命令而加的。

命令行客户端是基于replxx(类似于readline)。换句话说，它可以使用我们熟悉的快捷键方式来操作以及保留历史命令。历史命令会写入在~/.clickhouse-client-history中。

默认情况下，输出的格式是PrettyCompact。您可以通过FORMAT设置根据不同查询来修改格式，或者通过在查询末尾指定\G字符，或通过在命令行中使用--format或--vertical参数，或使用客户端的配置文件。

若要退出客户端，使用Ctrl+D（或Ctrl+C），或者输入以下其中一个命令：exit, quit, logout, учше, йгше, дщпщге, exit;, quit;, logout;, q, Q, :q

当执行一个查询的时候，客户端会显示：

1. 进度, 进度会每秒更新十次（默认情况下）。对于很快的查询，进度可能没有时间显示。
2. 为了调试会显示解析且格式化后的查询语句。
3. 指定格式的输出结果。
4. 输出结果的行数的行数，经过的时间，以及查询处理的速度。

您可以通过Ctrl+C来取消一个长时间的查询。然而，您依然需要等待服务端来中止请求。在某个阶段去取消查询是不可能的。如果您不等待并再次按下Ctrl + C,客户端将会退出。

命令行客户端允许通过外部数据（外部临时表）来查询。更多相关信息，请参考 «外部数据查询处理».

查询参数

您可以创建带有参数的查询，并将值从客户端传递给服务器。这允许避免在客户端使用特定的动态值格式化查询。例如:

$ clickhouse-client --param_parName="[1, 2]" -q "SELECT * FROM table WHERE a = {parName:Array(UInt16)}"

查询语法

像平常一样格式化一个查询，然后把你想要从app参数传递到查询的值用大括号格式化，格式如下:

{<name>:<data type>}

name — 占位符标识符。在控制台客户端，使用--param_<name> = value来指定

data type — 数据类型参数值。例如，一个数据结构(integer, ('string', integer))拥有Tuple(UInt8, Tuple(String, UInt8))数据类型(你也可以用另一个integer类型)。

示例

$ clickhouse-client --param_tuple_in_tuple="(10, ('dt', 10))" -q "SELECT * FROM table WHERE val = {tuple_in_tuple:Tuple(UInt8, Tuple(String, UInt8))}"

配置

您可以通过以下方式传入参数到clickhouse-client中（所有的参数都有默认值）：

通过命令行

命令行参数会覆盖默认值和配置文件的配置。

配置文件

配置文件的配置会覆盖默认值命令行参数

--host, -h -– 服务端的host名称, 默认是localhost。您可以选择使用host名称或者IPv4或IPv6地址。

--port – 连接的端口，默认值：9000。注意HTTP接口以及TCP原生接口使用的是不同端口。

--user, -u – 用户名。 默认值：default。

--password – 密码。 默认值：空字符串。

--query, -q – 使用非交互模式查询。

--database, -d – 默认当前操作的数据库. 默认值：服务端默认的配置（默认是default）。

--multiline, -m – 如果指定，允许多行语句查询（Enter仅代表换行，不代表查询语句完结）。

--multiquery, -n – 如果指定, 允许处理用;号分隔的多个查询，只在非交互模式下生效。

--format, -f – 使用指定的默认格式输出结果。

--vertical, -E – 如果指定，默认情况下使用垂直格式输出结果。这与–format=Vertical相同。在这种格式中，每个值都在单独的行上打印，这种方式对显示宽表很有帮助。

--time, -t – 如果指定，非交互模式下会打印查询执行的时间到stderr中。

--stacktrace – 如果指定，如果出现异常，会打印堆栈跟踪信息。

--config-file – 配置文件的名称。

--secure – 如果指定，将通过安全连接连接到服务器。

--history_file — 存放命令历史的文件的路径。

--param_<name> — 查询参数配置查询参数.

配置文件

clickhouse-client使用以下第一个配置文件：

通过--config-file参数指定。

./clickhouse-client.xml

~/.clickhouse-client/config.xml

/etc/clickhouse-client/config.xml

配置文件示例:

<config>

<user>username</user>

<password>password</password>

<secure>False</secure>

</config>

来源文章

原生接口（TCP）{#native-interface-tcp}

原生接口用于命令行客户端，用于分布式查询处理期间的服务器间通信，以及其他C++程序。可惜的是，原生的ClickHouse协议还没有正式的规范，但它可以从ClickHouse源代码通过拦截和分析TCP流量进行反向工程。

来源文章

HTTP客户端

HTTP接口允许您在任何编程语言的任何平台上使用ClickHouse。我们使用它在Java和Perl以及shell脚本中工作。在其他部门中，HTTP接口用于Perl、Python和Go。HTTP接口比原生接口受到更多的限制，但它具有更好的兼容性。

默认情况下，clickhouse-server会在8123端口上监控HTTP请求（这可以在配置中修改）。

如果你发送了一个未携带任何参数的GET /请求，它会返回一个字符串 «Ok.»（结尾有换行）。可以将它用在健康检查脚本中。

如果你发送了一个未携带任何参数的GET /请求，它返回响应码200和OK字符串定义，可在Http服务响应配置定义(在末尾添加换行)

$ curl 'http://localhost:8123/' Ok.

通过URL中的 query 参数来发送请求，或者发送POST请求，或者将查询的开头部分放在URL的query参数中，其他部分放在POST中（我们会在后面解释为什么这样做是有必要的）。URL的大小会限制在16KB，所以发送大型查询时要时刻记住这点。

如果请求成功，将会收到200的响应状态码和响应主体中的结果。

如果发生了某个异常，将会收到500的响应状态码和响应主体中的异常描述信息。

当使用GET方法请求时，readonly会被设置。换句话说，若要作修改数据的查询，只能发送POST方法的请求。可以将查询通过POST主体发送，也可以通过URL参数发送。示例:

$ curl 'http://localhost:8123/?query=SELECT%201' 1

$ wget -nv -O- 'http://localhost:8123/?query=SELECT 1' 1

$ echo -ne 'GET /?query=SELECT%201 HTTP/1.0\r\n\r\n' | nc localhost 8123 HTTP/1.0 200 OK

Date: Wed, 27 Nov 2019 10:30:18 GMT

Connection: Close

Content-Type: text/tab-separated-values; charset=UTF-8

X-ClickHouse-Server-Display-Name: clickhouse.ru-central1.internal X-ClickHouse-Query-Id: 5abe861c-239c-467f-b955-8a201abb8b7f

X-ClickHouse-Summary: {"read_rows":"0","read_bytes":"0","written_rows":"0","written_bytes":"0","total_rows_to_read":"0"}

1

可以看到，curl 命令由于空格需要 URL 转义，所以不是很方便。尽管 wget 命令对url做了 URL 转义，但我们并不推荐使用他，因为在 HTTP 1.1 协议下使用 keep-alive 和

Transfer-Encoding: chunked 头部设置它并不能很好的工作。

$ echo 'SELECT 1' | curl 'http://localhost:8123/' --data-binary @- 1

$ echo 'SELECT 1' | curl 'http://localhost:8123/?query=' --data-binary @- 1

$ echo '1' | curl 'http://localhost:8123/?query=SELECT' --data-binary @- 1

如您所见，curl有些不方便，因为空格必须进行URL转义。

尽管wget本身会对所有内容进行转义，但我们不推荐使用它，因为在使用keepalive和传输编码chunked时，它在HTTP 1.1上不能很好地工作。

$ echo 'SELECT 1' | curl 'http://localhost:8123/' --data-binary @- 1

$ echo 'SELECT 1' | curl 'http://localhost:8123/?query=' --data-binary @- 1

$ echo '1' | curl 'http://localhost:8123/?query=SELECT' --data-binary @- 1

如果部分查询是在参数中发送的，部分是在POST中发送的，则在这两个数据部分之间插入换行。错误示例：

$ echo 'ECT 1' | curl 'http://localhost:8123/?query=SEL' --data-binary @-

Code: 59, e.displayText() = DB::Exception: Syntax error: failed at position 0: SEL ECT 1

, expected One of: SHOW TABLES, SHOW DATABASES, SELECT, INSERT, CREATE, ATTACH, RENAME, DROP, DETACH, USE, SET, OPTIMIZE., e.what() = DB::Exception

默认情况下，返回的数据是TabSeparated格式的，更多信息，见Formats部分。您可以使用查询的FORMAT子句来设置其他格式。

另外，还可以使用default_formatURL参数或X-ClickHouse-Format头来指定TabSeparated之外的默认格式。

$ echo 'SELECT 1 FORMAT Pretty' | curl 'http://localhost:8123/?' --data-binary @-

┏━━━┓

┃ 1 ┃

┡━━━┩

│ 1 │

└───┘

INSERT必须通过POST方法来插入数据。在这种情况下，您可以在URL参数中编写查询的开始部分，并使用POST传递要插入的数据。例如，要插入的数据可以是来自MySQL的一个以tab分隔的存储。通过这种方式，INSERT查询替换了从MySQL查询的LOAD DATA LOCAL INFILE。

示例: 创建一个表:

$ echo 'CREATE TABLE t (a UInt8) ENGINE = Memory' | curl 'http://localhost:8123/' --data-binary @-

使用类似INSERT的查询来插入数据：

$ echo 'INSERT INTO t VALUES (1),(2),(3)' | curl 'http://localhost:8123/' --data-binary @-

数据可以从查询中单独发送：

$ echo '(4),(5),(6)' | curl 'http://localhost:8123/?query=INSERT%20INTO%20t%20VALUES' --data-binary @-

您可以指定任何数据格式。Values格式与将INSERT写入t值时使用的格式相同:

$ echo '(7),(8),(9)' | curl 'http://localhost:8123/?query=INSERT%20INTO%20t%20FORMAT%20Values' --data-binary @-

若要插入tab分割的数据，需要指定对应的格式：

$ echo -ne '10\n11\n12\n' | curl 'http://localhost:8123/?query=INSERT%20INTO%20t%20FORMAT%20TabSeparated' --data-binary @-

从表中读取内容。由于查询处理是并行的，数据以随机顺序输出。

$ curl 'http://localhost:8123/?query=SELECT%20a%20FROM%20t' 7

8

9

10

11

12

1

2

3

4

5

6

删除表：

$ echo 'DROP TABLE t' | curl 'http://localhost:8123/' --data-binary @-

成功请求后并不会返回数据，返回一个空的响应体。

在传输数据时，可以使用ClickHouse内部压缩格式。压缩的数据具有非标准格式，您需要使用特殊的clickhouse-compressor程序来处理它(它是与clickhouse-client包一起安装的)。为了提高数据插入的效率，您可以通过使用http_native_compression_disable_checksumming_on_decompress设置禁用服务器端校验。

如果在URL中指定了compress=1，服务会返回压缩的数据。

如果在URL中指定了decompress=1，服务会解压通过POST方法发送的数据。

您也可以选择使用HTTP compression。发送一个压缩的POST请求，附加请求头Content-Encoding: compression_method。为了使ClickHouse响应，您必须附加Accept- Encoding: compression_method。ClickHouse支持gzip，br和deflate compression methods。要启用HTTP压缩，必须使用ClickHouse启用Http压缩配置。您可以在Http zlib压缩级别设置中为所有压缩方法配置数据压缩级别。

您可以使用它在传输大量数据时减少网络流量，或者创建🖂即压缩的转储。 通过压缩发送数据的例子:

##Sending data to the server:

$ curl -vsS "http://localhost:8123/?enable_http_compression=1" -d 'SELECT number FROM system.numbers LIMIT 10' -H 'Accept-Encoding: gzip'

##Sending data to the client:

$ echo "SELECT 1" | gzip -c | curl -sS --data-binary @- -H 'Content-Encoding: gzip' 'http://localhost:8123/'

警告

一些HTTP客户端可能会在默认情况下从服务器解压数据(使用gzip和deflate)，即使您未正确地使用了压缩设置，您也可能会得到解压数据。

您可以使用databaseURL参数或X-ClickHouse-Database头来指定默认数据库。

$ echo 'SELECT number FROM numbers LIMIT 10' | curl 'http://localhost:8123/?database=system' --data-binary @- 0

1

2

3

4

5

6

7

8

9

默认情况下，在服务器设置中注册的数据库被用作默认数据库。默认情况下，它是名为default的数据库。或者，您可以始终在表名之前使用点来指定数据库。用户名和密码可以通过以下三种方式指定：

1. 通过HTTP Basic Authentication。示例：

$ echo 'SELECT 1' | curl 'http://user:password@localhost:8123/' -d @-

1. 通过URL参数中的user和password。示例：

$ echo 'SELECT 1' | curl 'http://localhost:8123/?user=user&password=password' -d @-

1. 使用X-ClickHouse-User或X-ClickHouse-Key头指定，示例:

$ echo 'SELECT 1' | curl -H 'X-ClickHouse-User: user' -H 'X-ClickHouse-Key: password' 'http://localhost:8123/' -d @-

如果未指定用户名，则使用default。如果未指定密码，则使用空密码。

您还可以使用URL参数来指定处理单个查询或整个设置配置文件的任何设置。例子:http://localhost:8123/?profile=web&max_rows_to_read=1000000000&query=SELECT+1更多信息，详见设置部分。

$ echo 'SELECT number FROM system.numbers LIMIT 10' | curl 'http://localhost:8123/?' --data-binary @- 0

1

2

3

4

5

6

7

8

9

有关其他参数的信息，请参考SET一节。

类似地，您可以在HTTP协议中使用ClickHouse会话。为此，需要向请求添加session_idGET参数。您可以使用任何字符串作为会话ID。默认情况下，会话在60秒不活动后终止。要更改此超时配置，请修改服务器配置中的default_session_timeout设置，或向请求添加session_timeoutGET参数。要检查会话状态，使用session_check=1参数。一次只能在单个会话中执行一个查询。

您可以在X-ClickHouse-Progress响应头中收到查询进度的信息。为此，启用Http Header携带进度。示例：

X-ClickHouse-Progress: {"read_rows":"2752512","read_bytes":"240570816","total_rows_to_read":"8880128"} X-ClickHouse-Progress: {"read_rows":"5439488","read_bytes":"482285394","total_rows_to_read":"8880128"} X-ClickHouse-Progress: {"read_rows":"8783786","read_bytes":"819092887","total_rows_to_read":"8880128"}

显示字段信息:

read_rows — 读取的行数。

read_bytes — 读取的数据字节数。total_rows_to_read — 读取的数据总行数。 written_rows — 写入数据行数。

written_bytes — 写入数据字节数。

如果HTTP连接丢失，运行的请求不会自动停止。解析和数据格式化是在服务器端执行的，使用Http连接可能无效。可选的query_id参数可能当做query ID传入（或者任何字符串）。更多信息，详见replace_running_query部分。可选的quota_key参数可能当做quota key传入（或者任何字符串）。更多信息，详见Quotas部分。

HTTP接口允许传入额外的数据（外部临时表）来查询。更多信息，详见外部数据查询处理部分。

响应缓冲

可以在服务器端启用响应缓冲。提供了buffer_size和wait_end_of_query两个URL参数来达此目的。

buffer_size决定了查询结果要在服务内存中缓冲多少个字节数据. 如果响应体比这个阈值大，缓冲区会写入到HTTP管道，剩下的数据也直接发到HTTP管道中。为了确保整个响应体被缓冲，可以设置wait_end_of_query=1。这种情况下，存入内存的数据会被缓冲到服务端的一个临时文件中。

示例:

$ curl -sS 'http://localhost:8123/?max_result_bytes=4000000&buffer_size=3000000&wait_end_of_query=1' -d 'SELECT toUInt8(number) FROM system.numbers LIMIT 9000000 FORMAT RowBinary'

查询请求响应状态码和HTTP头被发送到客户端后，若发生查询处理出错，使用缓冲区可以避免这种情况的发生。在这种情况下，响应主体的结尾会写入一条错误消息，而在客户端，只能在解析阶段检测到该错误。

查询参数

您可以使用参数创建查询，并通过相应的HTTP请求参数为它们传递值。有关更多信息，请参见CLI查询参数。示例

$ curl -sS "<address>?param_id=2&param_phrase=test" -d "SELECT * FROM table WHERE int_column = {id:UInt8} and string_column = {phrase:String}"

特定的HTTP接口

ClickHouse通过HTTP接口支持特定的查询。例如，您可以如下所示向表写入数据:

$ echo '(4),(5),(6)' | curl 'http://localhost:8123/?query=INSERT%20INTO%20t%20VALUES' --data-binary @-

ClickHouse还支持预定义的HTTP接口，可以帮助您更容易与第三方工具集成，如Prometheus Exporter.

示例:

首先，将此部分添加到服务器配置文件中:

<http_handlers>

<rule>

<url>/predefined_query</url>

<methods>POST,GET</methods>

<handler>

<type>predefined_query_handler</type>

<query>SELECT * FROM system.metrics LIMIT 5 FORMAT Template SETTINGS format_template_resultset = 'prometheus_template_output_format_resultset', format_template_row = 'prometheus_template_output_format_row', format_template_rows_between_delimiter = '\n'</query>

</handler>

</rule>

<rule>...</rule>

<rule>...</rule>

</http_handlers>

请求Prometheus格式的URL以获取数据:

$ curl -v 'http://localhost:8123/predefined_query'

• Trying ::1...
• Connected to localhost (::1) port 8123 (#0)
• GET /predefined_query HTTP/1.1
• Host: localhost:8123
• User-Agent: curl/7.47.0
• Accept: */*

>

< HTTP/1.1 200 OK

< Date: Tue, 28 Apr 2020 08:52:56 GMT

< Connection: Keep-Alive

< Content-Type: text/plain; charset=UTF-8

< X-ClickHouse-Server-Display-Name: i-mloy5trc

< Transfer-Encoding: chunked

< X-ClickHouse-Query-Id: 96fe0052-01e6-43ce-b12a-6b7370de6e8a

< X-ClickHouse-Format: Template

< X-ClickHouse-Timezone: Asia/Shanghai

< Keep-Alive: timeout=3

< X-ClickHouse-Summary: {"read_rows":"0","read_bytes":"0","written_rows":"0","written_bytes":"0","total_rows_to_read":"0"}

<

## HELP "Query" "Number of executing queries" ## TYPE "Query" counter

"Query" 1

## HELP "Merge" "Number of executing background merges" ## TYPE "Merge" counter

"Merge" 0

## HELP "PartMutation" "Number of mutations (ALTER DELETE/UPDATE)" ## TYPE "PartMutation" counter

"PartMutation" 0

## HELP "ReplicatedFetch" "Number of data parts being fetched from replica" ## TYPE "ReplicatedFetch" counter

"ReplicatedFetch" 0

## HELP "ReplicatedSend" "Number of data parts being sent to replicas" ## TYPE "ReplicatedSend" counter

"ReplicatedSend" 0

• Connection #0 to host localhost left intact
• Connection #0 to host localhost left intact

正如您从示例中看到的，如果在config.xml文件中配置了http_handlers，并且http_handlers可以包含许多规则。ClickHouse将把接收到的HTTP请求与rule中的预定义类型进行匹配，第一个匹配的将运行处理程序。如果匹配成功，ClickHouse将执行相应的预定义查询。

现在rule可以配置method， header， url， handler:

• method 负责匹配HTTP请求的方法部分。 method完全符合HTTP协议中method的定义。这是一个可选的配置。如果它没有在配置文件中定义，那么它与HTTP请求的方法部分不匹配。

url 负责匹配HTTP请求的URL部分。它匹配RE2正则表达式。这是一个可选的配置。如果配置文件中没有定义它，则它与HTTP请求的URL部分不匹配。

headers 负责匹配HTTP请求的头部分。它与RE2的正则表达式兼容。这是一个可选的配置。如果它没有在配置文件中定义，那么它与HTTP请求的头部分不匹配。

handler 包含主要的处理部分。现在handler可以配置type, status, content_type, response_content, query, query_param_name。 type 目前支持三种类型:特定查询, 动态查询, static.

query — 使用predefined_query_handler类型，在调用处理程序时执行查询。

query_param_name — 与dynamic_query_handler类型一起使用，提取并执行HTTP请求参数中与query_param_name值对应的值。

status — 与static类型一起使用，响应状态代码。

content_type — 与static类型一起使用，响应信息content-type。

response_content — 与static类型一起使用，响应发送给客户端的内容，当使用前缀file://或config://时，从发送给客户端的文件或配置中查找内容。接下来是不同type的配置方法。

特定查询

predefined_query_handler 支持设置Settings和query_params参数。您可以将query配置为predefined_query_handler类型。

query 是一个预定义的predefined_query_handler查询，它由ClickHouse在匹配HTTP请求并返回查询结果时执行。这是一个必须的配置。以下是定义的max_threads和max_alter_threads设置， 然后查询系统表以检查这些设置是否设置成功。

示例:

<http_handlers>

<rule>

<url><![CDATA[/query_param_with_url/\w+/(?P<name_1>[^/]+)(/(?P<name_2>[^/]+))?]]></url>

<method>GET</method>

<headers>

<XXX>TEST_HEADER_VALUE</XXX>

<PARAMS_XXX><![CDATA[(?P<name_1>[^/]+)(/(?P<name_2>[^/]+))?]]></PARAMS_XXX>

</headers>

<handler>

<type>predefined_query_handler</type>

<query>SELECT value FROM system.settings WHERE name = {name_1:String}</query>

<query>SELECT name, value FROM system.settings WHERE name = {name_2:String}</query>

</handler>

</rule>

</http_handlers>

$ curl -H 'XXX:TEST_HEADER_VALUE' -H 'PARAMS_XXX:max_threads' 'http://localhost:8123/query_param_with_url/1/max_threads/max_alter_threads? max_threads=1&max_alter_threads=2'

1

max_alter_threads 2

警告

在一个predefined_query_handler中，只支持insert类型的一个查询。

动态查询

dynamic_query_handler时，查询以HTTP请求参数的形式编写。区别在于，在predefined_query_handler中，查询是在配置文件中编写的。您可以在dynamic_query_handler中配置query_param_name。

ClickHouse提取并执行与HTTP请求URL中的query_param_name值对应的值。query_param_name的默认值是/query。这是一个可选的配置。如果配置文件中没有定义，则不会传入参数。

为了试验这个功能，示例定义了max_threads和max_alter_threads，queries设置是否成功的值。示例:

<http_handlers>

<rule>

<headers>

<XXX>TEST_HEADER_VALUE_DYNAMIC</XXX> </headers>

<handler>

<type>dynamic_query_handler</type>

<query_param_name>query_param</query_param_name>

</handler>

</rule>

</http_handlers>

$ curl -H 'XXX:TEST_HEADER_VALUE_DYNAMIC' 'http://localhost:8123/own? max_threads=1&max_alter_threads=2&param_name_1=max_threads&param_name_2=max_alter_threads&query_param=SELECT%20name,value%20FROM%20system.set tings%20where%20name%20=%20%7Bname_1:String%7D%20OR%20name%20=%20%7Bname_2:String%7D'

max_threads 1

max_alter_threads 2

static

static可以返回content_type, status和response_content。response_content可以返回指定的内容。示例:

返回信息.

<http_handlers>

<rule>

<methods>GET</methods>

<headers><XXX>xxx</XXX></headers>

<url>/hi</url>

<handler>

<type>static</type>

<status>402</status>

<content_type>text/html; charset=UTF-8</content_type>

<response_content>Say Hi!</response_content>

</handler>

</rule>

<http_handlers>

$ curl -vv -H 'XXX:xxx' 'http://localhost:8123/hi'

• Trying ::1...
• Connected to localhost (::1) port 8123 (#0)
• GET /hi HTTP/1.1
• Host: localhost:8123
• User-Agent: curl/7.47.0
• Accept: */*
• XXX:xxx

>

< HTTP/1.1 402 Payment Required

< Date: Wed, 29 Apr 2020 03:51:26 GMT

< Connection: Keep-Alive

< Content-Type: text/html; charset=UTF-8

< Transfer-Encoding: chunked

< Keep-Alive: timeout=3

< X-ClickHouse-Summary: {"read_rows":"0","read_bytes":"0","written_rows":"0","written_bytes":"0","total_rows_to_read":"0"}

<

* Connection #0 to host localhost left intact Say Hi!%

从配置中查找发送到客户端的内容。

<get_config_static_handler><![CDATA[<html ng-app="SMI2"><head><base href="http://ui.tabix.io/"></head><body><div ui-view="" class="content-ui"></div><script src="http://loader.tabix.io/master.js"></script></body></html>]]></get_config_static_handler>

<http_handlers>

<rule>

<methods>GET</methods>

<headers><XXX>xxx</XXX></headers>

<url>/get_config_static_handler</url>

<handler>

<type>static</type>

<response_content>config://get_config_static_handler</response_content>

</handler>

</rule>

</http_handlers>

$ curl -v -H 'XXX:xxx' 'http://localhost:8123/get_config_static_handler'

• Trying ::1...
• Connected to localhost (::1) port 8123 (#0)
• GET /get_config_static_handler HTTP/1.1
• Host: localhost:8123
• User-Agent: curl/7.47.0
• Accept: */*
• XXX:xxx

>

< HTTP/1.1 200 OK

< Date: Wed, 29 Apr 2020 04:01:24 GMT

< Connection: Keep-Alive

< Content-Type: text/plain; charset=UTF-8

< Transfer-Encoding: chunked

< Keep-Alive: timeout=3

< X-ClickHouse-Summary: {"read_rows":"0","read_bytes":"0","written_rows":"0","written_bytes":"0","total_rows_to_read":"0"}

<

* Connection #0 to host localhost left intact

<html ng-app="SMI2"><head><base href="http://ui.tabix.io/"></head><body><div ui-view="" class="content-ui"></div><script src="http://loader.tabix.io/master.js">

</script></body></html>%

从发送到客户端的文件中查找内容。

<http_handlers>

<rule>

<methods>GET</methods>

<headers><XXX>xxx</XXX></headers>

<url>/get_absolute_path_static_handler</url>

<handler>

<type>static</type>

<content_type>text/html; charset=UTF-8</content_type>

<response_content>file:///absolute_path_file.html</response_content>

</handler>

</rule>

<rule>

<methods>GET</methods>

<headers><XXX>xxx</XXX></headers>

<url>/get_relative_path_static_handler</url>

<handler>

<type>static</type>

<content_type>text/html; charset=UTF-8</content_type>

<response_content>file://./relative_path_file.html</response_content>

</handler>

</rule>

</http_handlers>

$ user_files_path='/var/lib/clickhouse/user_files'

$ sudo echo "<html><body>Relative Path File</body></html>" > $user_files_path/relative_path_file.html

$ sudo echo "<html><body>Absolute Path File</body></html>" > $user_files_path/absolute_path_file.html

$ curl -vv -H 'XXX:xxx' 'http://localhost:8123/get_absolute_path_static_handler'

• Trying ::1...
• Connected to localhost (::1) port 8123 (#0)
• GET /get_absolute_path_static_handler HTTP/1.1
• Host: localhost:8123
• User-Agent: curl/7.47.0
• Accept: */*
• XXX:xxx

>

< HTTP/1.1 200 OK

< Date: Wed, 29 Apr 2020 04:18:16 GMT

< Connection: Keep-Alive

< Content-Type: text/html; charset=UTF-8

< Transfer-Encoding: chunked

< Keep-Alive: timeout=3

< X-ClickHouse-Summary: {"read_rows":"0","read_bytes":"0","written_rows":"0","written_bytes":"0","total_rows_to_read":"0"}

<

<html><body>Absolute Path File</body></html>

• Connection #0 to host localhost left intact

$ curl -vv -H 'XXX:xxx' 'http://localhost:8123/get_relative_path_static_handler'

• Trying ::1...
• Connected to localhost (::1) port 8123 (#0)
• GET /get_relative_path_static_handler HTTP/1.1
• Host: localhost:8123
• User-Agent: curl/7.47.0
• Accept: */*
• XXX:xxx

>

< HTTP/1.1 200 OK

< Date: Wed, 29 Apr 2020 04:18:31 GMT

< Connection: Keep-Alive

< Content-Type: text/html; charset=UTF-8

< Transfer-Encoding: chunked

< Keep-Alive: timeout=3

< X-ClickHouse-Summary: {"read_rows":"0","read_bytes":"0","written_rows":"0","written_bytes":"0","total_rows_to_read":"0"}

<

<html><body>Relative Path File</body></html>

* Connection #0 to host localhost left intact

来源文章

MySQL接口

ClickHouse支持MySQL wire通讯协议。可以通过在配置文件中设置 mysql_port 来启用它:

<mysql_port>9004</mysql_port>

使用命令行工具 mysql 进行连接的示例:

$ mysql --protocol tcp -u default -P 9004

如果连接成功，则输出:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4

Server version: 20.2.1.1-ClickHouse

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

为了与所有MySQL客户端兼容，建议在配置文件中使用 double SHA1 来指定用户密码。

如果使用 SHA256 指定用户密码，一些客户端将无法进行身份验证（比如mysqljs和旧版本的命令行工具mysql）。限制:

不支持prepared queries

某些数据类型以字符串形式发送原始文章

输入/输出格式

ClickHouse可以接受和返回各种格式的数据。输入支持的格式可以用来解析提供给INSERT的数据，可以从文件备份表(如File, URL或HDFS)执行SELECT，或者读取外部字典。输出支持的格式可用于获取SELECT的结果，并支持执行INSERT文件的表中。

以下是支持的格式:

您可以使用ClickHouse设置控制一些格式处理参数。更多详情设置请参考设置

TabSeparated

在TabSeparated分隔格式中，数据按行写入。每行包含由制表符分隔的值。每个值后跟一个制表符，除了行中最后一个值后跟换行。在任何地方都采用严格的Unix换行。最后一行还必须在末尾包含换行。值以文本格式编写，不包含引号，并使用转义的特殊字符。

这种格式也可以用TSV来表示。

TabSeparated格式便于使用自定义程序和脚本处理数据。默认情况下，它在HTTP接口和命令行客户端的批处理模式中使用。这种格式还允许在不同dbms之间传输数据。例如， 您可以从MySQL获取转储并将其上传到ClickHouse，反之亦然。

TabSeparated格式支持输出total值(与TOTALS一起使用时)和extreme值(当extreme被设置为1时)。在这种情况下，total值和extreme值会在主数据后输出。主要结果、总值和极值之间用空行分隔。示例:

SELECT EventDate, count() AS c FROM test.hits GROUP BY EventDate WITH TOTALS ORDER BY EventDate FORMAT TabSeparated``

数据格式化

整数是用十进制形式写的。数字可以在开头包含一个额外的+字符(解析时忽略，格式化时不记录)。非负数不能包含负号。在读取时，允许将空字符串解析为零，或者(对于有符号 类型)将仅由一个负号组成的字符串解析为零。不符合相应数据类型的数字可以被解析为不同的数字，而不会出现错误消息。

浮点数以十进制形式书写。.号用作十进制分隔符。支持指数符号，如inf、+inf、-inf和nan。浮点数的条目可以以小数点开始或结束。在格式化期间，浮点数可能会丢失准确性。

在解析期间，并不严格要求读取与机器可以表示的最接近的数值。

日期以YYYY-MM-DD格式编写，并以相同的格式解析，但使用任何字符作为分隔符。

日期和时间以YYYY-MM-DD hh:mm:ss的格式书写，并以相同的格式解析，但使用任何字符作为分隔符。

这一切都发生在客户端或服务器启动时的系统时区(取决于它们对数据的格式)。对于带有时间的日期，夏时制时间未指定。因此，如果转储在夏令时有时间，则转储不会明确地与 数据匹配，解析将选择这两次中的一次。

在读取操作期间，不正确的日期和具有时间的日期可以使用自然溢出或null日期和时间进行分析，而不会出现错误消息。

有个例外情况，Unix时间戳格式也支持用时间解析日期(如果它恰好由10个十进制数字组成)。其结果与时间区域无关。格式YYYY-MM-DD hh:mm:ss和NNNNNNNNNN是自动区分的。

字符串以反斜杠转义的特殊字符输出。下面的转义序列用于输出:\b, \f, \r, \n, \t, \0, \', \\。解析还支持\a、\v和\xHH(十六进制转义字符)和任何\c字符，其中c是任何字符(这些序列被转换为c)。因此，读取数据支持这样一种格式，即可以将换行符写成\n或\，或者写成换行符。例如，字符串Hello world在单词之间有换行符，而不是空格，可以用以下语法进行解析:

Hello\nworld Hello\

world

第二种形式是支持的，因为MySQL读取tab-separated格式数据集的时候也会使用它。在TabSeparated分隔格式传递数据时需要转义的最小字符集:Tab、换行符(LF)和反斜杠。

只有一小部分符号被转义。您可以很容易地找到一个字符串值，而您的终端将在输出中不显示它。

数组写在方括号内的逗号分隔值列表中。数组中的数字项按正常格式进行格式化。Date和DateTime类型用单引号表示。字符串使用与上面相同的转义规则在单引号中编写。

NULL将输出为\N。

Nested结构的每个元素都表示为数组。示例：

CREATE TABLE nestedt (

`id` UInt8,

`aux` Nested( a UInt8,

b String

)

)

ENGINE = TinyLog

INSERT INTO nestedt Values ( 1, [1], ['a'])

SELECT * FROM nestedt FORMAT TSV

1 [1] ['a']

TabSeparatedRaw

与TabSeparated格式的不同之处在于，写入的行没有转义。

使用这种格式解析时，每个字段中不允许使用制表符或换行符。 这种格式也可以使用名称TSVRaw来表示。

TabSeparatedWithNames

与TabSeparated格式不同的是列名写在第一行。

在解析过程中，第一行被完全忽略。不能使用列名来确定它们的位置或检查它们的正确性。

(将来可能会添加对头行解析的支持。)

这种格式也可以使用名称TSVWithNames来表示。

TabSeparatedWithNamesAndTypes

与TabSeparated格式不同的是列名写在第一行，而列类型写在第二行。在解析过程中，将完全忽略第一行和第二行。

这种格式也可以使用名称TSVWithNamesAndTypes来表示。

Template

此格式允许指定带有占位符的自定义格式字符串，这些占位符用于指定转义规则。

它使用设置format_schema, format_schema_rows, format_schema_rows_between_delimiter以及其他格式的一些设置(例如转义JSON时使用output_format_json_quote_64bit_integers)

设置format_template_row指定文件的路径，该文件包含以下语法的行格式字符串:

delimiter_1${column_1:serializeAs_1}delimiter_2${column_2:serializeAs_2} ... delimiter_N,

其中，delimiter_i是值之间的分隔符($符号可以转义为$$)，

column_i是要选择或插入其值的列的名称或索引(如果为空，则跳过该列)， serializeAs_i是列值的转义规则。支持以下转义规则:

CSV, JSON, XML (类似于相同名称的格式)

Escaped (类似于TSV) Quoted (类似于Values) Raw (类似于TSVRaw)

None

如果省略了转义规则，那么将使用None。XML和Raw只适用于输出。 对于下面的格式字符串:

`Search phrase: ${SearchPhrase:Quoted}, count: ${c:Escaped}, ad price: $$${price:JSON};`

SearchPhrase、c和price列的值被转义为quotation、Escaped和JSON将分别在Search phrase:， ， count:， ， ad price: $和;分隔符之间打印(用于选择)或expected(用于插入)。例如:

Search phrase: 'bathroom interior design', count: 2166, ad price: $3;

format_template_rows_between_delimiter设置指定行之间的分隔符，它将打印(或expected)在每一行之后，最后一行除外(默认为\n)。

设置format_template_resultset指定文件路径，该文件包含resultset的格式字符串。resultset的格式字符串与row的格式字符串具有相同的语法，允许指定前缀、后缀和打印一些附加信息的方法。它包含以下占位符而不是列名:

data format_template_row格式的数据行，由format_template_rows_between_delimiter分隔。此占位符必须是格式字符串中的第一个占位符。 totals format_template_row格式的总值(和WITH TOTALS一起使用)

min format_template_row格式的最小值(当极值设置为1时) max format_template_row格式的最大值(当极值设置为1时) rows 输出行的总数

rows_before_limit 没有LIMIT的最小行数。仅当查询包含LIMIT时输出。如果查询包含GROUP BY，那么rows_before_limit_at_least就是没有LIMIT的确切行数。

time 请求执行时间（秒）

rows_read 已读取的行数

bytes_read 已读取（未压缩）的字节数

占位符data、totals、min和max必须没有指定转义规则(或者必须显式指定None)。其余占位符可以指定任何转义规则。如果format_template_resultset设置为空字符串，则使用${data}作为默认值。

对于insert查询，格式允许跳过某些列或某些字段的前缀或后缀(参见示例)。

Select示例:

SELECT SearchPhrase, count() AS c FROM test.hits GROUP BY SearchPhrase ORDER BY c DESC LIMIT 5 FORMAT Template SETTINGS format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format', format_template_rows_between_delimiter = '\n '

/some/path/resultset.format:

<!DOCTYPE HTML>

<html> <head> <title>Search phrases</title> </head>

<body>

<table border="1"> <caption>Search phrases</caption>

<tr> <th>Search phrase</th> <th>Count</th> </tr>

${data}

</table>

<table border="1"> <caption>Max</caption>

${max}

</table>

<b>Processed ${rows_read:XML} rows in ${time:XML} sec</b>

</body>

</html>

/some/path/row.format:

<tr> <td>${0:XML}</td> <td>${1:XML}</td> </tr>

结果：

<!DOCTYPE HTML>

<html> <head> <title>Search phrases</title> </head>

<body>

<table border="1"> <caption>Search phrases</caption>

<tr> <th>Search phrase</th> <th>Count</th> </tr>

<tr> <td></td> <td>8267016</td> </tr>

<tr> <td>bathroom interior design</td> <td>2166</td> </tr>

<tr> <td>yandex</td> <td>1655</td> </tr>

<tr> <td>spring 2014 fashion</td> <td>1549</td> </tr>

<tr> <td>freeform photos</td> <td>1480</td> </tr>

</table>

<table border="1"> <caption>Max</caption>

<tr> <td></td> <td>8873898</td> </tr>

</table>

<b>Processed 3095973 rows in 0.1569913 sec</b>

</body>

</html>

Insert示例：

Some header

Page views: 5, User id: 4324182021466249494, Useless field: hello, Duration: 146, Sign: -1

Page views: 6, User id: 4324182021466249494, Useless field: world, Duration: 185, Sign: 1

Total rows: 2

INSERT INTO UserActivity FORMAT Template SETTINGS

format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format'

/some/path/resultset.format:

Some header\n${data}\nTotal rows: ${:CSV}\n

/some/path/row.format:

Page views: ${PageViews:CSV}, User id: ${UserID:CSV}, Useless field: ${:CSV}, Duration: ${Duration:CSV}, Sign: ${Sign:CSV}

PageViews, UserID, Duration和Sign 内部占位符是表中列的名称。将忽略行中Useless field后面和后缀中\nTotal rows:之后的值。输入数据中的所有分隔符必须严格等于指定格式字符串中的分隔符。

TemplateIgnoreSpaces

这种格式只适用于输入。

类似于Template，但跳过输入流中分隔符和值之间的空白字符。但是，如果格式字符串包含空格字符，这些字符将会出现在输入流中。还允许指定空占位符(${}或${:None})来将一些分隔符分割为单独的部分，以忽略它们之间的空格。这种占位符仅用于跳过空白字符。

如果列的值在所有行的顺序相同，那么可以使用这种格式读取JSON。可以使用以下请求从格式为JSON的输出示例中插入数据：

INSERT INTO table_name FORMAT TemplateIgnoreSpaces SETTINGS

format_template_resultset = '/some/path/resultset.format', format_template_row = '/some/path/row.format', format_template_rows_between_delimiter = ','

/some/path/resultset.format:

{${}"meta"${}:${:JSON},${}"data"${}:${} [${data}]${},${}"totals"${}:${:JSON},${}"extremes"${}:${:JSON},${}"rows"${}:${:JSON},${}"rows_before_limit_at_least"${}:${:JSON}${}}

/some/path/row.format:

{${}"SearchPhrase"${}:${}${phrase:JSON}${},${}"c"${}:${}${cnt:JSON}${}}

TSKV

类似于TabSeparated，但是输出的值是name=value格式。名称的转义方式与TabSeparated格式相同，=符号也是转义的。

SearchPhrase= count()=8267016 SearchPhrase=bathroom interior design count()=2166 SearchPhrase=yandex count()=1655 SearchPhrase=2014 spring fashion count()=1549 SearchPhrase=freeform photos count()=1480 SearchPhrase=angelina jolie count()=1245 SearchPhrase=omsk count()=1112 SearchPhrase=photos of dog breeds count()=1091 SearchPhrase=curtain designs count()=1064 SearchPhrase=baku count()=1000

NULL格式为\N。

SELECT * FROM t_null FORMAT TSKV

x=1 y=\N

当有大量的小列时，这种格式是无效的，并且通常没有理由使用它。不过，就效率而言，它并不比JSONEachRow差。

这种格式支持数据输出和解析。对于解析，不同列的值支持任何顺序。省略某些值是可以接受的——它们被视为与其默认值相等。在这种情况下，0和空白行被用作默认值。不支 持在表中指定的复杂值作为缺省值。

解析允许存在不带等号或值的附加字段tskv。此字段被忽略。

CSV

按,分隔的数据格式(RFC)。

格式化时，行是用双引号括起来的。字符串中的双引号会以两个双引号输出，除此之外没有其他规则来做字符转义了。日期和时间也会以双引号包括。数字的输出不带引号。值由 一个单独的字符隔开，这个字符默认是,。行使用Unix换行符（LF）分隔。数组序列化成CSV规则如下：首先将数组序列化为TabSeparated格式的字符串，然后将结果字符串用双引号包括输出到CSV。CSV格式的元组被序列化为单独的列（即它们在元组中的嵌套关系会丢失）。

$ clickhouse-client --format_csv_delimiter="|" --query="INSERT INTO test.csv FORMAT CSV" < data.csv

* 默认情况下间隔符是, ，在format_csv_delimiter中可以了解更多分隔符配置。

解析的时候，可以使用或不使用引号来解析所有值。支持双引号和单引号。行也可以不用引号排列。在这种情况下，它们被解析为逗号或换行符（CR或LF）。在解析不带引号的行 时，若违反RFC`规则，会忽略前缀和结尾的空格和制表符。对于换行，全部支持Unix（LF），Windows（CR LF）和Mac OS Classic（CR LF）。

如果启用input_format_defaults_for_omitted_fields，空的末尾加引号的输入值将替换为相应列的默认值。

NULL被格式化为\N或NULL或一个空的非引号字符串(详见配置input_format_csv_unquoted_null_literal_as_null或input_format_defaults_for_omitted_fields)。

CSV格式支持输出总数和极值的方式与TabSeparated相同。

CSVWithNames

会输出带头部的信息(字段列表)，和TabSeparatedWithNames一样。

CustomSeparated

类似于Template， 但它打印或读取所有列和使用转义规则在设置format_custom_escaping_rule和分隔符设

置format_custom_field_delimiter,format_custom_row_before_delimiter,format_custom_row_after_delimiter,format_custom_row_between_delimiter,format_custom_result_before_d elimiter,format_custom_result_after_delimiter中,而不是从格式字符串。

也有CustomSeparatedIgnoreSpaces格式，这是类似于TemplateIgnoreSpaces。

JSON

以JSON格式输出数据。除了数据表之外，它还输出列名和类型，以及一些附加信息: 输出行的总数，以及如果没有LIMIT的话可输出的行数。示例:

SELECT SearchPhrase, count() AS c FROM test.hits GROUP BY SearchPhrase WITH TOTALS ORDER BY c DESC LIMIT 5 FORMAT JSON

{

"meta":

[

{

"name": "'hello'", "type": "String"

},

{

"name": "multiply(42, number)", "type": "UInt64"

},

{

"name": "range(5)",

"type": "Array(UInt8)"

}

],

"data":

[

{

"'hello'": "hello",

"multiply(42, number)": "0",

"range(5)": [0,1,2,3,4]

},

{

"'hello'": "hello",

"multiply(42, number)": "42",

"range(5)": [0,1,2,3,4]

},

{

"'hello'": "hello",

"multiply(42, number)": "84",

"range(5)": [0,1,2,3,4]

}

],

"rows": 3,

"rows_before_limit_at_least": 3

}

JSON与JavaScript兼容。为了确保这一点，一些字符被另外转义：斜线/被转义为\/; 替代的换行符U+2028和U+2029会打断一些浏览器解析，它们会被转义为\uXXXX。 ASCII控 制字符被转义：退格，换页，换行，回车和水平制表符被替换为\b，\f，\n，\r，\t 作为使用\uXXXX序列的00-1F范围内的剩余字节。 无效的UTF-8序列更改为替换字符，因此输出文本将包含有效的UTF-8序列。 为了与JavaScript兼容，默认情况下，Int64和UInt64整数用双引号引起来。要除去引号，可以将配置参

数output_format_json_quote_64bit_integers设置为0。

rows – 结果输出的行数。

rows_before_limit_at_least去掉 LIMIT过滤后的最小行总数。 只会在查询包含LIMIT条件时输出。若查询包含 GROUP BY，rows_before_limit_at_least就是去掉LIMIT后过滤后的准确行数。

totals – 总值 （当使用TOTALS条件时）。

extremes – 极值（当extremes设置为1时）。

该格式仅适用于输出查询结果，但不适用于解析输入（将数据插入到表中）。

ClickHouse支持NULL, 在JSON输出中显示为null。若要在输出中启用+nan、-nan、+inf、-inf值，请设置output_format_json_quote_denormals为1。参考

JSONEachRow格式

output_format_json_array_of_rows设置

JSONString

与JSON的不同之处在于数据字段以字符串输出，而不是以类型化JSON值输出。示例：

{

"meta":

[

{

"name": "'hello'", "type": "String"

},

{

"name": "multiply(42, number)", "type": "UInt64"

},

{

"name": "range(5)",

"type": "Array(UInt8)"

}

],

"data":

[

{

"'hello'": "hello",

"multiply(42, number)": "0",

"range(5)": "[0,1,2,3,4]"

},

{

"'hello'": "hello",

"multiply(42, number)": "42",

"range(5)": "[0,1,2,3,4]"

},

{

"'hello'": "hello",

"multiply(42, number)": "84",

"range(5)": "[0,1,2,3,4]"

}

],

"rows": 3,

"rows_before_limit_at_least": 3

}

JSONAsString

在这种格式中，一个JSON对象被解释为一个值。如果输入有几个JSON对象(逗号分隔)，它们将被解释为独🖂的行。

这种格式只能对具有单个字段类型的表进行解析String。其余的列必须设置为DEFAULT或MATERIALIZED，或者忽略。一旦将整个JSON对象收集为字符串，就可以使用JSON函数运行它。

示例 查询：

DROP TABLE IF EXISTS json_as_string;

CREATE TABLE json_as_string (json String) ENGINE = Memory;

INSERT INTO json_as_string FORMAT JSONAsString {"foo":{"bar":{"x":"y"},"baz":1}},{},{"any json stucture":1}

SELECT * FROM json_as_string;

结果：

┌─json──────────────────────────────┐

│ {"foo":{"bar":{"x":"y"},"baz":1}} │

│ {} │

│ {"any json stucture":1} │

└───────────────────────────────────┘

JSONCompact JSONCompactString

与JSON格式不同的是它以数组的方式输出结果，而不是以结构体。示例：

// JSONCompact

{

"meta":

[

{

"name": "'hello'", "type": "String"

},

{

"name": "multiply(42, number)", "type": "UInt64"

},

{

"name": "range(5)",

"type": "Array(UInt8)"

}

],

"data":

[

["hello", "0", [0,1,2,3,4]],

["hello", "42", [0,1,2,3,4]],

["hello", "84", [0,1,2,3,4]]

],

"rows": 3,

"rows_before_limit_at_least": 3

}

// JSONCompactString

{

"meta":

[

{

"name": "'hello'", "type": "String"

},

{

"name": "multiply(42, number)", "type": "UInt64"

},

{

"name": "range(5)",

"type": "Array(UInt8)"

}

],

"data":

[

["hello", "0", "[0,1,2,3,4]"],

["hello", "42", "[0,1,2,3,4]"],

["hello", "84", "[0,1,2,3,4]"]

],

"rows": 3,

"rows_before_limit_at_least": 3

}

JSONEachRow JSONStringEachRow JSONCompactEachRow JSONCompactStringEachRow

使用这些格式时，ClickHouse会将行输出为分隔的、换行分隔的JSON值，但数据作为一个整体不是有效的JSON。

{"some_int":42,"some_str":"hello","some_tuple":[1,"a"]} // JSONEachRow [42,"hello",[1,"a"]] // JSONCompactEachRow

["42","hello","(2,'a')"] // JSONCompactStringsEachRow

在插入数据时，应该为每一行提供一个单独的JSON值。

JSONEachRowWithProgress JSONStringEachRowWithProgress

与JSONEachRow/JSONStringEachRow不同的是，ClickHouse还将生成作为JSON值的进度信息。

{"row":{"'hello'":"hello","multiply(42, number)":"0","range(5)":[0,1,2,3,4]}}

{"row":{"'hello'":"hello","multiply(42, number)":"42","range(5)":[0,1,2,3,4]}}

{"row":{"'hello'":"hello","multiply(42, number)":"84","range(5)":[0,1,2,3,4]}}

{"progress":{"read_rows":"3","read_bytes":"24","written_rows":"0","written_bytes":"0","total_rows_to_read":"3"}}

JSONCompactEachRowWithNamesAndTypes JSONCompactStringEachRowWithNamesAndTypes

与JSONCompactEachRow/JSONCompactStringEachRow不同的是，其中列名和类型被写入前两行。

["'hello'", "multiply(42, number)", "range(5)"]

["String", "UInt64", "Array(UInt8)"]

["hello", "0", [0,1,2,3,4]]

["hello", "42", [0,1,2,3,4]]

["hello", "84", [0,1,2,3,4]]

Inserting Data

INSERT INTO UserActivity FORMAT JSONEachRow {"PageViews":5, "UserID":"4324182021466249494", "Duration":146,"Sign":-1}

{"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

ClickHouse允许:

对象中key-value的任何顺序。省略一些值。

ClickHouse忽略元素之间的空格和对象后面的逗号。您可以在一行中传递所有对象。你不需要用换行符把它们分开。

省略值处理

ClickHouse将省略的值替换为对应的data types默认值。

如果指定了DEFAULT expr，则ClickHouse根据属性使用不同的替换规则，详看input_format_defaults_for_omitted_fields设置。参考下表：

CREATE TABLE IF NOT EXISTS example_table (

x UInt32,

a DEFAULT x * 2

) ENGINE = Memory;

如果input_format_defaults_for_omitted_fields = 0, 那么x和a的默认值等于0(作为UInt32数据类型的默认值)。如果input_format_defaults_for_omitted_fields = 1, 那么x的默认值为0，但a的默认值为x * 2。

注意

当使用insert_sample_with_metadata = 1插入数据时，与使用insert_sample_with_metadata = 0插入数据相比，ClickHouse消耗更多的计算资源。

Selecting Data

以UserActivity表为例:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │ 5 │ 146 │ -1 │

│ 4324182021466249494 │ 6 │ 185 │ 1 │

└─────────────────────┴───────────┴──────────┴──────┘

当查询SELECT * FROM UserActivity FORMAT JSONEachRow返回:

{"UserID":"4324182021466249494","PageViews":5,"Duration":146,"Sign":-1}

{"UserID":"4324182021466249494","PageViews":6,"Duration":185,"Sign":1}

与JSON格式不同，没有替换无效的UTF-8序列。值以与JSON相同的方式转义。

提示

字符串中可以输出任意一组字节。如果您确信表中的数据可以被格式化为JSON而不会丢失任何信息，那么就使用JSONEachRow格式。

Nested Structures

如果您有一个包含Nested数据类型列的表，您可以插入具有相同结构的JSON数据。使用input_format_import_nested_json设置启用该特性。例如，请参考下表:

CREATE TABLE json_each_row_nested (n Nested (s String, i Int32) ) ENGINE = Memory

正如您在Nested数据类型描述中看到的，ClickHouse将嵌套结构的每个组件作为一个单独的列(n.s和n.i是我们的表)。您可以通过以下方式插入数据:

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n.s": ["abc", "def"], "n.i": [1, 23]}

将数据作为分层JSON对象集插入input_format_import_nested_json=1。

{

"n": {

"s": ["abc", "def"], "i": [1, 23]

}

}

如果没有此设置，ClickHouse将引发异常。

SELECT name, value FROM system.settings WHERE name = 'input_format_import_nested_json'

┌─name────────────────────────────┬─value─┐

│ input_format_import_nested_json │ 0 │

└─────────────────────────────────┴───────┘

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}

Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: n: (at row 1)

SET input_format_import_nested_json=1

INSERT INTO json_each_row_nested FORMAT JSONEachRow {"n": {"s": ["abc", "def"], "i": [1, 23]}}

SELECT * FROM json_each_row_nested

┌─n.s───────────┬─n.i────┐

│ ['abc','def'] │ [1,23] │

└───────────────┴────────┘

Native

最高性能的格式。通过二进制格式的块进行写入和读取。对于每个块，该中的行数，列数，列名称和类型以及列的部分将被相继记录。 换句话说，这种格式是columnar的 - 它不会将列转换为行。这是用于在服务器之间进行交互的本地界面中使用的格式，用于使用命令行客户端和C++客户端。

您可以使用此格式快速生成只能由ClickHouse DBMS读取的格式。但自己处理这种格式是没有意义的。

Null

没有输出。但是，查询已处理完毕，并且在使用命令行客户端时，数据将传输到客户端。这仅用于测试，包括性能测试。 显然，这种格式只适用于输出，不适用于解析。

Pretty

将数据以表格形式输出，也可以使用ANSI转义字符在终端中设置颜色。它会绘制一个完整的表格，每行数据在终端中占用两行。

每个结果块作为一个单独的表输出。这是必要的，以便在输出块时不需要缓冲结果(为了预先计算所有值的可见宽度，缓冲是必要的)。

NULL输出为ᴺᵁᴸᴸ。

示例(显示PrettyCompact格式)

SELECT * FROM t_null

┌─x─┬────y─┐

│ 1 │ ᴺᵁᴸᴸ │

└───┴──────┘

行没有转义为Pretty* 格式。示例显示了PrettyCompact格式:

SELECT 'String with \'quotes\' and \t character' AS Escaping_test

┌─Escaping_test────────────────────────┐

│ String with 'quotes' and character │

└──────────────────────────────────────┘

为避免将太多数据传输到终端，只打印前10,000行。 如果行数大于或等于10,000，则会显示消息Showed first 10 000。该格式仅适用于输出查询结果，但不适用于解析输入（将数据插入到表中）。

Pretty格式支持输出合计值(当使用WITH TOTALS时)和极值(当extremes设置为1时)。在这些情况下，合计值和极值将输出在主要数据之后，在单独的表中。示例(显示为PrettyCompact格式):

SELECT EventDate, count() AS c FROM test.hits GROUP BY EventDate WITH TOTALS ORDER BY EventDate FORMAT PrettyCompact

┌──EventDate─┬───────c─┐

│ 2014-03-17 │ 1406958 │

│ 2014-03-18 │ 1383658 │

│ 2014-03-19 │ 1405797 │

│ 2014-03-20 │ 1353623 │

│ 2014-03-21 │ 1245779 │

│ 2014-03-22 │ 1031592 │

│ 2014-03-23 │ 1046491 │

└────────────┴─────────┘

Totals:

┌──EventDate─┬───────c─┐

│ 1970-01-01 │ 8873898 │

└────────────┴─────────┘

Extremes:

┌──EventDate─┬───────c─┐

│ 2014-03-17 │ 1031592 │

│ 2014-03-23 │ 1406958 │

└────────────┴─────────┘

PrettyCompact

与Pretty格式不一样的是PrettyCompact去掉了行之间的表格分割线，这样使得结果更加紧凑。这种格式会在交互命令行客户端下默认使用。

PrettyCompactMonoBlock

与PrettyCompact格式不一样的是，它支持10,000行数据缓冲，然后输出在一个表格中，不会按照块来区分。

PrettyNoEscapes

与Pretty格式不一样的是，它不使用ANSI字符转义，这在浏览器显示数据以及在使用watch命令行工具是有必要的。示例：

watch -n1 "clickhouse-client --query='SELECT event, value FROM system.events FORMAT PrettyCompactNoEscapes'"

您可以使用HTTP接口来获取数据，显示在浏览器中。

PrettyCompactNoEscapes

用法类似上述。

PrettySpaceNoEscapes

用法类似上述。

PrettyCompactNoEscapes

与前面的设置相同。

PrettySpaceNoEscapes

与前面的设置相同。

PrettySpace

与PrettyCompact格式不一样的是，它使用空格来代替网格来显示数据。

RowBinary

以二进制格式逐行格式化和解析数据。行和值连续列出，没有分隔符。 这种格式比 Native 格式效率低，因为它是基于行的。

整数使用固定长度的小端表示法。 例如，UInt64 使用8个字节。

DateTime 被表示为 UInt32 类型的Unix 时间戳值。

Date 被表示为 UInt16 对象，它的值为 1970-01-01以来的天数。

字符串表示为 varint 长度（无符号 LEB128），后跟字符串的字节数。 FixedString 被简单地表示为一个字节序列。

数组表示为 varint 长度（无符号 LEB128），后跟有序的数组元素。

对于 NULL 的支持， 一个为 1 或 0 的字节会加在每个 可为空 值前面。如果为 1, 那么该值就是 NULL。 如果为 0，则不为 NULL。

RowBinaryWithNamesAndTypes

类似于 RowBinary，但添加了标题:

LEB128-编码列数（N) N Strings指定列名

N Strings指定列类型

值

在括号中打印每一行。行由逗号分隔。最后一行之后没有逗号。括号内的值也用逗号分隔。数字以十进制格式输出，不含引号。 数组以方括号输出。带有时间的字符串，日期和时间用引号包围输出。转义字符的解析规则与 TabSeparated 格式类似。 在格式化过程中，不插入额外的空格，但在解析过程中，空格是被允许并跳过的（除了数组值之外的空格，这是不允许的）。NULL 为 NULL。

以 Values 格式传递数据时需要转义的最小字符集是：单引号和反斜线。

这是 INSERT INTO t VALUES ... 中可以使用的格式，但您也可以将其用于查询结果。

垂直

使用指定的列名在单独的行上打印每个值。如果每行都包含大量列，则此格式便于打印一行或几行。

NULL 输出为 ᴺᵁᴸᴸ。示例:

SELECT * FROM t_null FORMAT Vertical

Row 1:

────── x: 1

y: ᴺᵁᴸᴸ

该格式仅适用于输出查询结果，但不适用于解析输入（将数据插入到表中）。

VerticalRaw

和 Vertical 格式不同点在于，行是不会被转义的。

这种格式仅仅适用于输出，但不适用于解析输入（将数据插入到表中）。 示例:

:) SHOW CREATE TABLE geonames FORMAT VerticalRaw; Row 1:

──────

statement: CREATE TABLE default.geonames ( geonameid UInt32, date Date DEFAULT CAST('2017-12-08' AS Date)) ENGINE = MergeTree(date, geonameid, 8192)

:) SELECT 'string with \'quotes\' and \t with some special \n characters' AS test FORMAT VerticalRaw; Row 1:

──────

test: string with 'quotes' and with some special characters

和 Vertical 格式相比：

:) SELECT 'string with \'quotes\' and \t with some special \n characters' AS test FORMAT Vertical; Row 1:

──────

test: string with \'quotes\' and \t with some special \n characters

XML

该格式仅适用于输出查询结果，但不适用于解析输入，示例：

<?xml version='1.0' encoding='UTF-8' ?>

<result>

<meta>

<columns>

<column>

<name>SearchPhrase</name>

<type>String</type>

</column>

<column>

<name>count()</name>

<type>UInt64</type>

</column>

</columns>

</meta>

<data>

<row>

<SearchPhrase></SearchPhrase>

<field>8267016</field>

</row>

<row>

<SearchPhrase>bathroom interior design</SearchPhrase>

<field>2166</field>

</row>

<row>

<SearchPhrase>yandex</SearchPhrase>

<field>1655</field>

</row>

<row>

<SearchPhrase>2014 spring fashion</SearchPhrase>

<field>1549</field>

</row>

<row>

<SearchPhrase>freeform photos</SearchPhrase>

<field>1480</field>

</row>

<row>

<SearchPhrase>angelina jolie</SearchPhrase>

<field>1245</field>

</row>

<row>

<SearchPhrase>omsk</SearchPhrase>

<field>1112</field>

</row>

<row>

<SearchPhrase>photos of dog breeds</SearchPhrase>

<field>1091</field>

</row>

<row>

<SearchPhrase>curtain designs</SearchPhrase>

<field>1064</field>

</row>

<row>

<SearchPhrase>baku</SearchPhrase>

<field>1000</field>

</row>

</data>

<rows>10</rows>

<rows_before_limit_at_least>141137</rows_before_limit_at_least>

</result>

如果列名称没有可接受的格式，则仅使用 field 作为元素名称。 通常，XML 结构遵循 JSON 结构。就像JSON一样，将无效的 UTF-8 字符都作替换，以便输出文本将包含有效的 UTF-8 字符序列。

在字符串值中，字符 < 和 ＆ 被转义为 < 和 ＆。

数组输出为 <array> <elem> Hello </ elem> <elem> World </ elem> ... </ array>，元组输出为 <tuple> <elem> Hello </ elem> <elem> World </ ELEM> ... </tuple>。

CapnProto

Cap’n Proto 是一种二进制消息格式，类似 Protocol Buffers 和 Thriftis，但与 JSON 或 MessagePack 格式不一样。

Cap’n Proto 消息格式是严格类型的，而不是自我描述，这意味着它们不需要外部的描述。这种格式可以实时地应用，并针对每个查询进行缓存。

SELECT SearchPhrase, count() AS c FROM test.hits

GROUP BY SearchPhrase FORMAT CapnProto SETTINGS schema = 'schema:Message'

其中 schema.capnp 描述如下：

struct Message { SearchPhrase @0 :Text; c @1 :Uint64;

}

格式文件存储的目录可以在服务配置中的 format_schema_path 指定。

Cap’n Proto 反序列化是很高效的，通常不会增加系统的负载。

Protobuf

Protobuf-是一个 协议缓冲区 格式。

此格式需要外部格式架构。 在查询之间缓存架构。

ClickHouse支持 proto2 和 proto3 语法 支持重复/可选/必填字段。使用示例:

SELECT * FROM test.table FORMAT Protobuf SETTINGS format_schema = 'schemafile:MessageType'

cat protobuf_messages.bin | clickhouse-client --query "INSERT INTO test.table FORMAT Protobuf SETTINGS format_schema='schemafile:MessageType'"

哪里的文件 schemafile.proto 看起来像这样:

syntax = "proto3";

message MessageType { string name = 1;

string surname = 2; uint32 birthDate = 3;

repeated string phoneNumbers = 4;

};

要查找协议缓冲区的消息类型的表列和字段之间的对应关系，ClickHouse比较它们的名称。这种比较是不区分大小写和字符 _ (下划线)和 . （点）被认为是相等的。

如果协议缓冲区消息的列和字段的类型不同，则应用必要的转换。 支持嵌套消息。 例如，对于字段 z 在下面的消息类型

message MessageType { message XType { message YType {

int32 z;

};

repeated YType y;

};

XType x;

};

ClickHouse尝试找到一个名为 x.y.z （或 x_y_z 或 X.y_Z 等）。嵌套消息适用于输入或输出一个 嵌套数据结构.

在protobuf模式中定义的默认值，如下所示

syntax = "proto2"; message MessageType {

optional int32 result_per_page = 3 [default = 10];

}

不应用;该 表默认值 用来代替它们。

ClickHouse在输入和输出protobuf消息 length-delimited 格式。这意味着每个消息之前，应该写它的长度作为一个 varint.

另请参阅 如何在流行语言中读取/写入长度分隔的protobuf消息.

Avro

Apache Avro 是在Apache Hadoop项目中开发的面向行的数据序列化框架。ClickHouse Avro格式支持读取和写入 Avro数据文件.

数据类型匹配{#sql_reference/data_types-matching}

下表显示了支持的数据类型以及它们如何匹配ClickHouse 数据类型 在 INSERT 和 SELECT 查询。

* Avro逻辑类型

不支持的Avro数据类型: record （非根), map

不支持的Avro逻辑数据类型: uuid, time-millis, time-micros, duration

插入数据

将Avro文件中的数据插入ClickHouse表:

$ cat file.avro | clickhouse-client --query="INSERT INTO {some_table} FORMAT Avro"

输入Avro文件的根模式必须是 record 类型。

要查找Avro schema的表列和字段之间的对应关系，ClickHouse比较它们的名称。 此比较区分大小写。跳过未使用的字段。

ClickHouse表列的数据类型可能与插入的Avro数据的相应字段不同。 插入数据时，ClickHouse根据上表解释数据类型，然后 投 将数据转换为相应的列类型。选择数据

从ClickHouse表中选择数据到Avro文件:

$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT Avro" > file.avro

列名必须:

名,名,名,名 [A-Za-z_]

随后只包含 [A-Za-z0-9_]

输出Avro文件压缩和同步间隔可以配置 output_format_avro_codec 和 output_format_avro_sync_interval 分别。

AvroConfluent

AvroConfluent支持解码单对象Avro消息常用于 卡夫卡 和 汇合的模式注册表.

每个Avro消息都嵌入了一个架构id，该架构id可以在架构注册表的帮助下解析为实际架构。模式解析后会进行缓存。

架构注册表URL配置为 format_avro_schema_registry_url

数据类型匹配{#sql_reference/data_types-matching-1}和 Avro

用途

要快速验证架构解析，您可以使用 kafkacat 与 ﾂ环板-ｮﾂ嘉ｯﾂ偲:

$ kafkacat -b kafka-broker -C -t topic1 -o beginning -f '%s' -c 3 | clickhouse-local --input-format AvroConfluent --format_avro_schema_registry_url 'http://schema-registry' -S "field1 Int64, field2 String" -q 'select * from table'

1. a
2. b
3. c

使用 AvroConfluent 与 卡夫卡:

CREATE TABLE topic1_stream (

field1 String, field2 String

)

ENGINE = Kafka() SETTINGS

kafka_broker_list = 'kafka-broker', kafka_topic_list = 'topic1', kafka_group_name = 'group1', kafka_format = 'AvroConfluent';

SET format_avro_schema_registry_url = 'http://schema-registry';

SELECT * FROM topic1_stream;

警告

设置 format_avro_schema_registry_url 需要在配置 users.xml restart动后保持它的价值。

Parquet

Apache Parquet 是Hadoop生态系统中普遍存在的列式存储格式。 ClickHouse支持此格式的读写操作。

数据类型匹配{#sql_reference/data_types-matching-2}

下表显示了支持的数据类型以及它们如何匹配ClickHouse 数据类型 在 INSERT 和 SELECT 查询。

ClickHouse支持可配置的精度 Decimal 类型。 该 INSERT 查询对待 Parquet DECIMAL 键入为ClickHouse Decimal128 类型。不支持的Parquet数据类型: DATE32, TIME32, FIXED_SIZE_BINARY, JSON, UUID, ENUM.

ClickHouse表列的数据类型可能与插入的Parquet数据的相应字段不同。 插入数据时，ClickHouse根据上表解释数据类型，然后 投 为ClickHouse表列设置的数据类型的数据。

插入和选择数据

您可以通过以下命令将Parquet数据从文件插入到ClickHouse表中:

$ cat {filename} | clickhouse-client --query="INSERT INTO {some_table} FORMAT Parquet"

您可以从ClickHouse表中选择数据，并通过以下命令将它们保存到Parquet格式的某个文件中:

$ clickhouse-client --query="SELECT * FROM {some_table} FORMAT Parquet" > {some_file.pq}

要与Hadoop交换数据，您可以使用 HDFS表引擎.

ORC

阿帕奇兽人 是Hadoop生态系统中普遍存在的列式存储格式。 您只能将此格式的数据插入ClickHouse。

数据类型匹配{#sql_reference/data_types-matching-3}

下表显示了支持的数据类型以及它们如何匹配ClickHouse 数据类型 在 INSERT 查询。

ClickHouse支持的可配置精度 Decimal 类型。 该 INSERT 查询对待兽人 DECIMAL 键入为ClickHouse Decimal128 类型。不支持的ORC数据类型: DATE32, TIME32, FIXED_SIZE_BINARY, JSON, UUID, ENUM.

ClickHouse表列的数据类型不必匹配相应的ORC数据字段。 插入数据时，ClickHouse根据上表解释数据类型，然后 投 将数据转换为ClickHouse表列的数据类型集。

插入数据

您可以通过以下命令将文件中的ORC数据插入到ClickHouse表中:

$ cat filename.orc | clickhouse-client --query="INSERT INTO some_table FORMAT ORC"

要与Hadoop交换数据，您可以使用 HDFS表引擎.

格式架构

包含格式架构的文件名由该设置设置 format_schema.

当使用其中一种格式时，需要设置此设置 Cap'n Proto 和 Protobuf.

格式架构是文件名和此文件中消息类型的名称的组合，用冒号分隔,

e.g. schemafile.proto:MessageType.

如果文件具有格式的标准扩展名（例如, .proto 为 Protobuf),

它可以被省略，在这种情况下，格式模式如下所示 schemafile:MessageType.

如果您通过输入或输出数据 客户 在交互模式下，格式架构中指定的文件名可以包含绝对路径或相对于客户端上当前目录的路径。

如果在批处理模式下使用客户端，则由于安全原因，架构的路径必须是相对的。

如果您通过输入或输出数据 HTTP接口 格式架构中指定的文件名应该位于指定的目录中 format_schema_path

在服务器配置中。原始文章

跳过错误

一些格式，如 CSV, TabSeparated, TSKV, JSONEachRow, Template, CustomSeparated 和 Protobuf 如果发生解析错误，可以跳过断开的行，并从下一行开始继续解析。 看

input_format_allow_errors_num 和 input_format_allow_errors_ratio 设置。限制:

-在解析错误的情况下 JSONEachRow 跳过所有数据，直到新行（或EOF），所以行必须由 \n 正确计算错误。

• Template 和 CustomSeparated 在最后一列之后使用分隔符，并在行之间使用分隔符来查找下一行的开头，所以跳过错误只有在其中至少有一个不为空时才有效。来源文章

JDBC驱动

官方驱动

第三方驱动:

ClickHouse-Native-JDBC clickhouse4j

来源文章

ODBC驱动

官方驱动。来源文章

C++客户端库

请参考仓库的描述文件clickhouse-cpp。原始文章

第三方工具

这是第三方工具的链接集合，它们提供了一些ClickHouse的接口。它可以是可视化界面、命令行界面或API:

Client libraries Integrations GUI

Proxies

注意

支持通用API的通用工具ODBC或JDBC，通常也适用于ClickHouse，但这里没有列出，因为它们实在太多了。

第三方开发库

声明

Yandex没有维护下面列出的库，也没有做过任何广泛的测试来确保它们的质量。

Python

infi.clickhouse_orm clickhouse-driver clickhouse-client aiochclient

asynch

PHP

Go

Swift

smi2/phpclickhouse 8bitov/clickhouse-php-client bozerkins/clickhouse-client simpod/clickhouse-client

seva-code/php-click-house-client SeasClick C++ client

one-ck

glushkovds/phpclickhouse-laravel

clickhouse

go-clickhouse mailrugo-clickhouse golang-clickhouse

ClickHouseNIO ClickHouseVapor ORM

NodeJs

clickhouse (NodeJs) node-clickhouse

Perl

perl-DBD-ClickHouse HTTP-ClickHouse AnyEvent-ClickHouse

Ruby

ClickHouse (Ruby) clickhouse-activerecord

R

Java

clickhouse-r RClickHouse

clickhouse-client-java clickhouse-client

Scala

clickhouse-scala-client Kotlin

AORM

C#

Elixir

Nim

Octonica.ClickHouseClient ClickHouse.Ado ClickHouse.Client ClickHouse.Net

clickhousex pillar

nim-clickhouse

Haskell

hdbc-clickhouse

来源文章

第三方集成库

声明

Yandex没有维护下面列出的库，也没有做过任何广泛的测试来确保它们的质量。

基础设施

关系数据库

MySQL

mysql2ch ProxySQL

clickhouse-mysql-data-reader horgh-replicator

PostgreSQL

clickhousedb_fdw

infi.clickhouse_fdw (uses infi.clickhouse_orm) pg2ch

clickhouse_fdw MSSQL

ClickHouseMigrator

消息队列

Kafka

clickhouse_sinker (uses Go client) stream-loader-clickhouse

流处理

Flink

对象存储

S3

容器编排

flink-clickhouse-sink

clickhouse-backup

Kubernetes

clickhouse-operator

配置管理

puppet

innogames/clickhouse mfedotov/clickhouse

Monitoring

Graphite

graphouse

carbon-clickhouse + graphite-clickhouse

graphite-ch-optimizer - optimizes staled partitions in *GraphiteMergeTree if rules from rollup configuration could be applied Grafana

clickhouse-grafana Prometheus

clickhouse_exporter PromHouse

clickhouse_exporter (uses Go client)

Nagios

check_clickhouse check_clickhouse.py

Zabbix

clickhouse-zabbix-template Sematext

clickhouse integration

Logging

rsyslog

omclickhouse fluentd

loghouse (for Kubernetes) logagent

logagent output-plugin-clickhouse

Geo

MaxMind

clickhouse-maxmind-geoip

编程语言

Python

SQLAlchemy

sqlalchemy-clickhouse (uses infi.clickhouse_orm) pandas

pandahouse

PHP

R

Java

Doctrine

dbal-clickhouse

dplyr

RClickHouse (uses clickhouse-cpp)

Hadoop

clickhouse-hdfs-loader (uses JDBC)

Scala

Akka

clickhouse-scala-client

C#

Elixir

ADO.NET

ClickHouse.Ado ClickHouse.Client ClickHouse.Net ClickHouse.Net.Migrations

Ecto

Ruby

clickhouse_ecto

Ruby on Rails

activecube ActiveRecord

GraphQL

activecube-graphql

源文章

第三方代理

chproxy

chproxy, 是一个用于ClickHouse数据库的HTTP代理和负载均衡器。特性:

用户路由和响应缓存。灵活的限制。

自动SSL证书续订。使用go语言实现。

KittenHouse

KittenHouse被设计为ClickHouse和应用服务器之间的本地代理，以防不可能或不方便在应用程序端缓冲插入数据。特性:

内存和磁盘上的数据缓冲。表路由。

负载平衡和运行状况检查。使用go语言实现。

ClickHouse-Bulk

ClickHouse-Bulk是一个简单的ClickHouse收集器。特性:

按阈值或间隔对请求进行分组并发送。多个远程服务器。

基本身份验证。

使用go语言实现。

Original article

第三方开发的可视化界面

开源

Tabix

ClickHouse Web 界面 Tabix.

主要功能：

浏览器直接连接 ClickHouse，不需要安装其他软件。高亮语法的编辑器。

自动命令补全。

查询命令执行的图形分析工具。配色方案选项。

Tabix 文档. HouseOps

HouseOps 是一个交互式 UI/IDE 工具，可以运行在 OSX, Linux and Windows 平台中。主要功能：

查询高亮语法提示，可以以表格或 JSON 格式查看数据。支持导出 CSV 或 JSON 格式数据。

支持查看查询执行的详情，支持 KILL 查询。

图形化显示，支持显示数据库中所有的表和列的详细信息。 快速查看列占用的空间。

服务配置。

以下功能正在计划开发：

• 数据库管理
• 用户管理
• 实时数据分析
• 集群监控
• 集群管理
• 监控副本情况以及 Kafka 引擎表

灯塔

灯塔 是ClickHouse的轻量级Web界面。特征：

包含过滤和元数据的表列表。带有过滤和排序的表格预览。只读查询执行。

DBeaver

DBeaver 具有ClickHouse支持的通用桌面数据库客户端。特征：

使用语法高亮显示查询开发。表格预览。

自动完成。

ﾂ环板-ｮﾂ嘉ｯﾂ偲

ﾂ环板-ｮﾂ嘉ｯﾂ偲 是ClickHouse的替代命令行客户端，用Python 3编写。特征：

自动完成。

查询和数据输出的语法高亮显示。寻呼机支持数据输出。

自定义PostgreSQL类命令。

ﾂ暗ｪﾂ氾环催ﾂ団ﾂ法ﾂ人

[clickhouse-flamegraph](https://github.com/Slach/clickhouse-flamegraph) 是一个可视化的专业工具`system.trace_log`如[flamegraph](http://www.brendangregg.com/flamegraphs.html).

商业

ﾂ环板Softwareｮﾂ嘉ｯ

整体学 在2019年被Gartner FrontRunners列为可用性最高排名第二的商业智能工具之一。 Holistics是一个基于SQL的全栈数据平台和商业智能工具，用于设置您的分析流程。

特征：

-自动化的电子邮件，Slack和Google表格报告时间表。

-强大的SQL编辑器，具有版本控制，自动完成，可重用的查询组件和动态过滤器。

-通过iframe在自己的网站或页面中嵌入仪表板。

-数据准备和ETL功能。

-SQL数据建模支持数据的关系映射。

DataGrip

DataGrip 是JetBrains的数据库IDE，专门支持ClickHouse。 它还嵌入到其他基于IntelliJ的工具中：PyCharm，IntelliJ IDEA，GoLand，PhpStorm等。特征：

非常快速的代码完成。

ClickHouse语法高亮显示。

支持ClickHouse特有的功能，例如嵌套列，表引擎。数据编辑器。

重构。

搜索和导航。来源文章

延时引擎Lazy

在距最近一次访问间隔expiration_time_in_seconds时间段内，将表保存在内存中，仅适用于 *Log引擎表由于针对这类表的访问间隔较长，对保存大量小的 *Log引擎表进行了优化，

创建数据库

CREATE DATABASE testlazy ENGINE = Lazy(expiration_time_in_seconds);

原始文章

Atomic

It is supports non-blocking DROP and RENAME TABLE queries and atomic EXCHANGE TABLES t1 AND t2 queries. Atomic database engine is used by default.

Creating a Database

CREATE DATABASE test ENGINE = Atomic;

Original article

MySQL

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中，并允许您对表进行INSERT和SELECT查询，以方便您在ClickHouse与MySQL之间进行数据交换。

MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中，因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。但您无法对其执行以下操作：

RENAME CREATE TABLE ALTER

CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]

ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

MySQL数据库引擎参数

host:port — 链接的MySQL地址。database — 链接的MySQL数据库。user — 链接的MySQL用户。password — 链接的MySQL用户密码。

支持的类型对应

其他的MySQL数据类型将全部都转换为字符串。同时以上的所有类型都支持可为空。

使用示例

在MySQL中创建表:

mysql> USE test;

Database changed

mysql> CREATE TABLE `mysql_table` (

-> `int_id` INT NOT NULL AUTO_INCREMENT,

-> `float` FLOAT NOT NULL,

-> PRIMARY KEY (`int_id`));

Query OK, 0 rows affected (0,09 sec)

mysql> insert into mysql_table (`int_id`, `float`) VALUES (1,2);

Query OK, 1 row affected (0,00 sec)

mysql> select * from mysql_table;

+ + +

| int_id | value |

+ + +

| 1 | 2 |

+ + +

1 row in set (0,00 sec)

在ClickHouse中创建MySQL类型的数据库，同时与MySQL服务器交换数据：

CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password')

SHOW DATABASES

┌─name─────┐

│ default │

│ mysql_db │

│ system │

└──────────┘

SHOW TABLES FROM mysql_db

┌─name─────────┐

│ mysql_table │

└──────────────┘

SELECT * FROM mysql_db.mysql_table

┌─int_id─┬─value─┐

│ 1 │ 2 │

└────────┴───────┘

INSERT INTO mysql_db.mysql_table VALUES (3,4)

SELECT * FROM mysql_db.mysql_table

┌─int_id─┬─value─┐

│ 1 │ 2 │

│ 3 │ 4 │

└────────┴───────┘

来源文章

数据库引擎

您使用的所有表都是由数据库引擎所提供的

默认情况下，ClickHouse使用自己的数据库引擎，该引擎提供可配置的表引擎和所有支持的SQL语法.除此之外，您还可以选择使用以下的数据库引擎：

MySQL

来源文章

版本折叠MergeTree

这个引擎:

允许快速写入不断变化的对象状态。

删除后台中的旧对象状态。 这显着降低了存储体积。请参阅部分 崩溃 有关详细信息。

引擎继承自 MergeTree 并将折叠行的逻辑添加到合并数据部分的算法中。 VersionedCollapsingMergeTree 用于相同的目的 折叠树 但使用不同的折叠算法，允许以多个线程的任何顺序插入数据。 特别是， Version 列有助于正确折叠行，即使它们以错误的顺序插入。 相比之下, CollapsingMergeTree 只允许严格连续插入。

创建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (

name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],

...

) ENGINE = VersionedCollapsingMergeTree(sign, version) [PARTITION BY expr]

[ORDER BY expr] [SAMPLE BY expr]

[SETTINGS name=value, ...]

有关查询参数的说明，请参阅 查询说明. 引擎参数

sign — 指定行类型的列名: 1 是一个 “state” 行, -1 是一个 “cancel” 行列数据类型应为 Int8.

VersionedCollapsingMergeTree(sign, version)

version — 指定对象状态版本的列名。列数据类型应为 UInt*.

查询 Clauses

当创建一个 VersionedCollapsingMergeTree 表时，跟创建一个 MergeTree表的时候需要相同 Clause

不推荐使用的创建表的方法

折叠

数据

考虑一种情况，您需要为某个对象保存不断变化的数据。 对于一个对象有一行，并在发生更改时更新该行是合理的。 但是，对于数据库管理系统来说，更新操作非常昂贵且速度很慢，因为它需要重写存储中的数据。 如果需要快速写入数据，则不能接受更新，但可以按如下顺序将更改写入对象。

使用 Sign 列写入行时。 如果 Sign = 1 这意味着该行是一个对象的状态（让我们把它称为 “state” 行）。 如果 Sign = -1 它指示具有相同属性的对象的状态的取消（让我们称之为

“cancel” 行）。 还可以使用 Version 列，它应该用单独的数字标识对象的每个状态。

例如，我们要计算用户在某个网站上访问了多少页面以及他们在那里的时间。 在某个时间点，我们用用户活动的状态写下面的行:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐

│ 4324182021466249494 │ 5 │ 146 │ 1 │ 1 |

└─────────────────────┴───────────┴──────────┴──────┴─────────┘

在稍后的某个时候，我们注册用户活动的变化，并用以下两行写入它。

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐

│ 4324182021466249494 │ 5 │ 146 │ -1 │ 1 |

│ 4324182021466249494 │ 6 │ 185 │ 1 │ 2 |

└─────────────────────┴───────────┴──────────┴──────┴─────────┘

第一行取消对象（用户）的先前状态。 它应该复制已取消状态的所有字段，除了 Sign.

第二行包含当前状态。

因为我们只需要用户活动的最后一个状态，行

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐

│ 4324182021466249494 │ 5 │ 146 │ 1 │ 1 |

│ 4324182021466249494 │ 5 │ 146 │ -1 │ 1 |

└─────────────────────┴───────────┴──────────┴──────┴─────────┘

可以删除，折叠对象的无效（旧）状态。 VersionedCollapsingMergeTree 在合并数据部分时执行此操作。要了解为什么每次更改都需要两行，请参阅 算法.

使用注意事项

• 1. 写入数据的程序应该记住对象的状态以取消它。 该 “cancel” 字符串应该是 “state” 与相反的字符串 Sign. 这增加了存储的初始大小，但允许快速写入数据。
  2. 列中长时间增长的数组由于写入负载而降低了引擎的效率。 数据越简单，效率就越高。
  3. SELECT 结果很大程度上取决于对象变化历史的一致性。 准备插入数据时要准确。 不一致的数据将导致不可预测的结果，例如会话深度等非负指标的负值。

算法

当ClickHouse合并数据部分时，它会删除具有相同主键和版本但 Sign值不同的一对行. 行的顺序并不重要。

当ClickHouse插入数据时，它会按主键对行进行排序。 如果 Version 列不在主键中，ClickHouse将其隐式添加到主键作为最后一个字段并使用它进行排序。

选择数据

ClickHouse不保证具有相同主键的所有行都将位于相同的结果数据部分中，甚至位于相同的物理服务器上。 对于写入数据和随后合并数据部分都是如此。 此外，ClickHouse流程 SELECT 具有多个线程的查询，并且无法预测结果中的行顺序。 这意味着，如果有必要从VersionedCollapsingMergeTree 表中得到完全 “collapsed” 的数据，聚合是必需的。

要完成折叠，请使用 GROUP BY 考虑符号的子句和聚合函数。 例如，要计算数量，请使用 sum(Sign) 而不是 count(). 要计算的东西的总和，使用 sum(Sign * x) 而不是 sum(x)，并添加 HAVING sum(Sign) > 0.

聚合 count, sum 和 avg 可以这样计算。 聚合 uniq 如果对象至少具有一个非折叠状态，则可以计算。 聚合 min 和 max 无法计算是因为 VersionedCollapsingMergeTree 不保存折叠状态值的历史记录。

如果您需要提取数据 “collapsing” 但是，如果没有聚合（例如，要检查是否存在其最新值与某些条件匹配的行），则可以使用 FINAL 修饰 FROM 条件这种方法效率低下，不应与大型表一起使用。

使用示例

示例数据:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐

│ 4324182021466249494 │ 5 │ 146 │ 1 │ 1 |

│ 4324182021466249494 │ 5 │ 146 │ -1 │ 1 |

│ 4324182021466249494 │ 6 │ 185 │ 1 │ 2 |

└─────────────────────┴───────────┴──────────┴──────┴─────────┘

创建表:

CREATE TABLE UAct (

UserID UInt64, PageViews UInt8, Duration UInt8, Sign Int8, Version UInt8

)

ENGINE = VersionedCollapsingMergeTree(Sign, Version) ORDER BY UserID

插入数据:

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1, 1)

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1, 1),(4324182021466249494, 6, 185, 1, 2)

我们用两个 INSERT 查询以创建两个不同的数据部分。 如果我们使用单个查询插入数据，ClickHouse将创建一个数据部分，并且永远不会执行任何合并。获取数据:

SELECT * FROM UAct

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐

│ 4324182021466249494 │ 5 │ 146 │ 1 │ 1 │

└─────────────────────┴───────────┴──────────┴──────┴─────────┘

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐

│ 4324182021466249494 │ 5 │ 146 │ -1 │ 1 │

│ 4324182021466249494 │ 6 │ 185 │ 1 │ 2 │

└─────────────────────┴───────────┴──────────┴──────┴─────────┘

我们在这里看到了什么，折叠的部分在哪里？

我们使用两个创建了两个数据部分 INSERT 查询。 该 SELECT 查询是在两个线程中执行的，结果是行的随机顺序。由于数据部分尚未合并，因此未发生折叠。 ClickHouse在我们无法预测的未知时间点合并数据部分。

这就是为什么我们需要聚合:

SELECT

UserID,

sum(PageViews * Sign) AS PageViews, sum(Duration * Sign) AS Duration, Version

FROM UAct

GROUP BY UserID, Version HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Version─┐

│ 4324182021466249494 │ 6 │ 185 │ 2 │

└─────────────────────┴───────────┴──────────┴─────────┘

如果我们不需要聚合，并希望强制折叠，我们可以使用 FINAL 修饰符 FROM 条款

SELECT * FROM UAct FINAL

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐

│ 4324182021466249494 │ 6 │ 185 │ 1 │ 2 │

└─────────────────────┴───────────┴──────────┴──────┴─────────┘

这是一个非常低效的方式来选择数据。 不要把它用于数据量大的表。原始文章

GraphiteMergeTree

该引擎用来对 Graphite数据进行瘦身及汇总。对于想使用CH来存储Graphite数据的开发者来说可能有用。

如果不需要对Graphite数据做汇总，那么可以使用任意的CH表引擎；但若需要，那就采用 GraphiteMergeTree 引擎。它能减少存储空间，同时能提高Graphite数据的查询效率。

该引擎继承自 MergeTree.

创建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (

Path String,

Time DateTime,

Value <Numeric_type>,

Version <Numeric_type>

...

) ENGINE = GraphiteMergeTree(config_section) [PARTITION BY expr]

[ORDER BY expr] [SAMPLE BY expr]

[SETTINGS name=value, ...]

建表语句的详细说明请参见 创建表

含有Graphite数据集的表应该包含以下的数据列：

• 指标名称(Graphite sensor)，数据类型：String
• 指标的时间度量，数据类型： DateTime
• 指标的值，数据类型：任意数值类型
• 指标的版本号，数据类型： 任意数值类型

CH以最大的版本号保存行记录，若版本号相同，保留最后写入的数据。

以上列必须设置在汇总参数配置中。

GraphiteMergeTree 参数

• config_section - 配置文件中标识汇总规则的节点名称

建表语句

在创建 GraphiteMergeTree 表时，需要采用和 clauses 相同的语句，就像创建 MergeTree 一样。

已废弃的建表语句

汇总配置的参数

汇总的配置参数由服务器配置的 graphite_rollup 参数定义。参数名称可以是任意的。允许为多个不同表创建多组配置并使用。

汇总配置的结构如下： 所需的列

模式Patterns

所需的列

path_column_name — 保存指标名称的列名 (Graphite sensor). 默认值: Path. time_column_name — 保存指标时间度量的列名. Default value: Time.

value_column_name — The name of the column storing the value of the metric at the time set in time_column_name.默认值: Value. version_column_name - 保存指标的版本号列. 默认值: Timestamp.

模式Patterns

patterns 的结构：

pattern

regexp function

pattern

regexp

age + precision

...

pattern

regexp function

age + precision

...

pattern

...

default

function

age + precision

...

Attention

模式必须严格按顺序配置：

1. 不含function or retention的Patterns

1. 同时含有function and retention的Patterns

1. default的Patterns.

CH在处理行记录时，会检查 pattern节点的规则。每个 pattern（含default）节点可以包含 function 用于聚合操作，或retention参数，或者两者都有。如果指标名称和 regexp相匹配，相应 pattern的规则会生效；否则，使用 default 节点的规则。

pattern 和 default 节点的字段设置:

regexp– 指标名的pattern.

age – 数据的最小存活时间(按秒算).

precision– 按秒来衡量数据存活时间时的精确程度. 必须能被86400整除 (一天的秒数).

function – 对于存活时间在 [age, age + precision]之内的数据，需要使用的聚合函数配置示例

<graphite_rollup>

<version_column_name>Version</version_column_name>

<pattern>

<regexp>click_cost</regexp>

<function>any</function>

<retention>

<age>0</age>

<precision>5</precision>

</retention>

<retention>

<age>86400</age>

<precision>60</precision>

</retention>

</pattern>

<default>

<function>max</function>

<retention>

<age>0</age>

<precision>60</precision>

</retention>

<retention>

<age>3600</age>

<precision>300</precision>

</retention>

<retention>

<age>86400</age>

<precision>3600</precision>

</retention>

</default>

</graphite_rollup>

原始文档

AggregatingMergeTree

该引擎继承自 MergeTree，并改变了数据片段的合并逻辑。 ClickHouse 会将一个数据片段内所有具有相同主键（准确的说是 排序键）的行替换成一行，这一行会存储一系列聚合函数的状态。

可以使用 AggregatingMergeTree 表来做增量数据的聚合统计，包括物化视图的数据聚合。引擎使用以下类型来处理所有列：

AggregateFunction SimpleAggregateFunction

AggregatingMergeTree 适用于能够按照一定的规则缩减行数的情况。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (

name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],

...

) ENGINE = AggregatingMergeTree() [PARTITION BY expr]

[ORDER BY expr] [SAMPLE BY expr]

[TTL expr]

[SETTINGS name=value, ...]

语句参数的说明，请参阅 建表语句描述。

子句

创建 AggregatingMergeTree 表时，需用跟创建 MergeTree 表一样的子句。

已弃用的建表方法

SELECT 和 INSERT

要插入数据，需使用带有 -State- 聚合函数的 INSERT SELECT 语句。

从 AggregatingMergeTree 表中查询数据时，需使用 GROUP BY 子句并且要使用与插入时相同的聚合函数，但后缀要改为 -Merge 。

对于 SELECT 查询的结果， AggregateFunction 类型的值对 ClickHouse 的所有输出格式都实现了特定的二进制表示法。在进行数据转储时，例如使用 TabSeparated 格式进行

SELECT 查询，那么这些转储数据也能直接用 INSERT 语句导回。

聚合物化视图的示例

创建一个跟踪 test.visits 表的 AggregatingMergeTree 物化视图：

CREATE MATERIALIZED VIEW test.basic

ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)

AS SELECT

CounterID, StartDate,

sumState(Sign) AS Visits, uniqState(UserID) AS Users

FROM test.visits

GROUP BY CounterID, StartDate;

向 test.visits 表中插入数据。

INSERT INTO test.visits ...

数据会同时插入到表和视图中，并且视图 test.basic 会将里面的数据聚合。

要获取聚合数据，我们需要在 test.basic 视图上执行类似 SELECT ... GROUP BY ... 这样的查询 ：

SELECT

StartDate, sumMerge(Visits) AS Visits, uniqMerge(Users) AS Users

FROM test.basic GROUP BY StartDate ORDER BY StartDate;

来源文章

MergeTree

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。

MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

主要特点:

存储的数据按主键排序。

这使得你能够创建一个小型的稀疏索引来加快数据检索。

支持数据分区，如果指定了 分区键 的话。

在相同数据集和相同结果集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。

支持数据副本。

ReplicatedMergeTree 系列的表提供了数据副本功能。更多信息，请参阅 数据副本 一节。

支持数据采样。

需要的话，你可以给表设置一个采样方法。

注意

合并 引擎并不属于 *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, ...]

对于以上参数的描述，可参考 CREATE 语句 的描述 。子句

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

规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或 卷（TO VOLUME 'xxx'）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。

更多细节，请查看 表和列的 TTL

SETTINGS — 控制 MergeTree 行为的额外参数：

index_granularity — 索引粒度。索引中相邻的『标记』间的数据行数。默认值，8192 。参考数据存储。

index_granularity_bytes — 索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。

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格式进行存储的最小字节数/行数。你可以不设置、只设置一个，或全都设置。参考：数据存储

示例配置

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 是默认设置 。

已弃用的建表方法

数据存储

表由按主键排序的数据片段（DATA PART）组成。

当数据被插入到表中时，会创建多个数据片段并按主键的字典序排序。例如，主键是 (CounterID, Date) 时，片段中数据首先按 CounterID 排序，具有相同 CounterID 的部分按

Date 排序。

不同分区的数据会被分成不同的片段，ClickHouse 在后台合并数据片段以便更高效存储。不同分区的数据片段不会进行合并。合并机制并不保证具有相同主键的行全都合并到同一个数据片段中。

数据片段可以以 Wide 或 Compact 格式存储。在 Wide 格式下，每一列都会在文件系统中存储为单独的文件，在 Compact 格式下所有列都存储在一个文件中。Compact 格式可以提高插入量少插入频率频繁时的性能。

数据存储格式由 min_bytes_for_wide_part 和 min_rows_for_wide_part 表引擎参数控制。如果数据片段中的字节数或行数少于相应的设置值，数据片段会以 Compact 格式存储，否则会以 Wide 格式存储。

每个数据片段被逻辑的分割成颗粒（granules）。颗粒是 ClickHouse 中进行数据查询时的最小不可分割数据集。ClickHouse 不会对行或值进行拆分，所以每个颗粒总是包含整数个行。每个颗粒的第一行通过该行的主键值进行标记，

ClickHouse 会为每个数据片段创建一个索引文件来存储这些标记。对于每列，无论它是否包含在主键当中，ClickHouse 都会存储类似标记。这些标记让你可以在列文件中直接找到数据。

颗粒的大小通过表引擎参数 index_granularity 和 index_granularity_bytes 控制。取决于行的大小，颗粒的行数的在 [1, index_granularity] 范围中。如果单行的大小超过了

index_granularity_bytes 设置的值，那么一个颗粒的大小会超过 index_granularity_bytes。在这种情况下，颗粒的大小等于该行的大小。

主键和索引在查询中的表现

我们以 (CounterID, Date) 以主键。排序好的索引的图示会是下面这样：

全部数据 : [ ]

CounterID: [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]

Date: [1111111222222233331233211111222222333211111112122222223111112223311122333]

标记: | | | | | | | | | | |

a,1 a,2 a,3 b,3 e,2 e,3 g,1 h,2 i,1 i,3 l,3

标记号: 0 1 2 3 4 5 6 7 8 9 10

如果指定查询如下：

CounterID in ('a', 'h')，服务器会读取标记号在 [0, 3) 和 [6, 8) 区间中的数据。

CounterID IN ('a', 'h') AND Date = 3，服务器会读取标记号在 [1, 3) 和 [7, 8) 区间中的数据。

Date = 3，服务器会读取标记号在 [1, 10] 区间中的数据。上面例子可以看出使用索引通常会比全表描述要高效。

稀疏索引会引起额外的数据读取。当读取主键单个区间范围的数据时，每个数据块中最多会多读 index_granularity * 2 行额外的数据。稀疏索引使得你可以处理极大量的行，因为大多数情况下，这些索引常驻与内存（RAM）中。

ClickHouse 不要求主键惟一，所以你可以插入多条具有相同主键的行。

主键的选择

主键中列的数量并没有明确的限制。依据数据结构，你可以在主键包含多些或少些列。这样可以：

改善索引的性能。

如果当前主键是 (a, b) ，在下列情况下添加另一个 c 列会提升性能：

查询会使用 c 列作为条件

很长的数据范围（ index_granularity 的数倍）里 (a, b) 都是相同的值，并且这样的情况很普遍。换言之，就是加入另一列后，可以让你的查询略过很长的数据范围。改善数据压缩。

ClickHouse 以主键排序片段数据，所以，数据的一致性越高，压缩越好。

在CollapsingMergeTree 和 SummingMergeTree 引擎里进行数据合并时会提供额外的处理逻辑。在这种情况下，指定与主键不同的 排序键 也是有意义的。

长的主键会对插入性能和内存消耗有负面影响，但主键中额外的列并不影响 SELECT 查询的性能。

可以使用 ORDER BY tuple() 语法创建没有主键的表。在这种情况下 ClickHouse 根据数据插入的顺序存储。如果在使用 INSERT ... SELECT 时希望保持数据的排序，请设置

max_insert_threads = 1。

想要根据初始顺序进行数据查询，使用 单线程查询选择与排序键不同主键

指定一个跟排序键不一样的主键是可以的，此时排序键用于在数据片段中进行排序，主键用于在索引文件中进行标记的写入。这种情况下，主键表达式元组必须是排序键表达式元 组的前缀。

当使用 SummingMergeTree 和 AggregatingMergeTree 引擎时，这个特性非常有用。通常在使用这类引擎时，表里的列分两种：维度 和 度量 。典型的查询会通过任意的 GROUP BY 对度量列进行聚合并通过维度列进行过滤。由于 SummingMergeTree 和 AggregatingMergeTree 会对排序键相同的行进行聚合，所以把所有的维度放进排序键是很自然的做法。但这将导致排序键中包含大量的列，并且排序键会伴随着新添加的维度不断的更新。

在这种情况下合理的做法是，只保留少量的列在主键当中用于提升扫描效率，将维度列添加到排序键中。

对排序键进行 ALTER 是轻量级的操作，因为当一个新列同时被加入到表里和排序键里时，已存在的数据片段并不需要修改。由于旧的排序键是新排序键的前缀，并且新添加的列中没有数据，因此在表修改时的数据对于新旧的排序键来说都是有序的。

索引和分区在查询中的应用

对于 SELECT 查询，ClickHouse 分析是否可以使用索引。如果 WHERE/PREWHERE 子句具有下面这些表达式（作为谓词链接一子项或整个）则可以使用索引：包含一个表示与主键/分区键中的部分字段或全部字段相等/不等的比较表达式；基于主键/分区键的字段上的 IN 或 固定前缀的LIKE 表达式；基于主键/分区键的字段上的某些函数；基于主键/分区键的表达式的逻辑表达式。

因此，在索引键的一个或多个区间上快速地执行查询都是可能的。下面例子中，指定标签；指定标签和日期范围；指定标签和日期；指定多个标签和日期范围等执行查询，都会非 常快。

当引擎配置如下时：

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

这种情况下，这些查询：

SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34

SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)

SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND

CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

ClickHouse 会依据主键索引剪掉不符合的数据，依据按月分区的分区键剪掉那些不包含符合数据的分区。 上文的查询显示，即使索引用于复杂表达式。因为读表操作是组织好的，所以，使用索引不会比完整扫描慢。 下面这个例子中，不会使用索引。

SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'

要检查 ClickHouse 执行一个查询时能否使用索引，可设置 force_index_by_date 和 force_primary_key 。

按月分区的分区键是只能读取包含适当范围日期的数据块。这种情况下，数据块会包含很多天（最多整月）的数据。在块中，数据按主键排序，主键第一列可能不包含日期。因 此，仅使用日期而没有带主键前几个字段作为条件的查询将会导致需要读取超过这个指定日期以外的数据。

部分单调主键的使用

考虑这样的场景，比如一个月中的几天。它们在一个月的范围内形成一个单调序列 ，但如果扩展到更大的时间范围它们就不再单调了。这就是一个部分单调序列。如果用户使用部分单调的主键创建表，ClickHouse同样会创建一个稀疏索引。当用户从这类表中查询数据时，ClickHouse 会对查询条件进行分析。如果用户希望获取两个索引标记之间的数据并且这两个标记在一个月以内，ClickHouse 可以在这种特殊情况下使用到索引，因为它可以计算出查询参数与索引标记之间的距离。

如果查询参数范围内的主键不是单调序列，那么 ClickHouse 无法使用索引。在这种情况下，ClickHouse 会进行全表扫描。

ClickHouse 在任何主键代表一个部分单调序列的情况下都会使用这个逻辑。跳数索引

此索引在 CREATE 语句的列部分里定义。

INDEX index_name expr TYPE type(...) GRANULARITY granularity_value

*MergeTree 系列的表可以指定跳数索引。

这些索引是由数据块按粒度分割后的每部分在指定表达式上汇总信息 granularity_value 组成（粒度大小用表引擎里 index_granularity 的指定）。这些汇总信息有助于用 where 语句跳过大片不满足的数据，从而减少 SELECT 查询从磁盘读取的数据量，

这些索引会在数据块上聚合指定表达式的信息，这些信息以 granularity_value 指定的粒度组成 （粒度的大小通过在表引擎中定义 index_granularity 定义）。这些汇总信息有助于跳过大片不满足 where 条件的数据，从而减少 SELECT 查询从磁盘读取的数据量。

示例

CREATE TABLE table_name

(

u64 UInt64, i32 Int32,

s String,

...

INDEX a (u64 * i32, s) TYPE minmax GRANULARITY 3,

INDEX b (u64 * length(s)) TYPE set(1000) GRANULARITY 4

) ENGINE = MergeTree()

...

上例中的索引能让 ClickHouse 执行下面这些查询时减少读取数据量。

SELECT count() FROM table WHERE s < 'z'

SELECT count() FROM table WHERE u64 * i32 == 10 AND u64 * length(s) >= 1234

索引的可用类型

minmax

存储指定表达式的极值（如果表达式是 tuple ，则存储 tuple 中每个元素的极值），这些信息用于跳过数据块，类似主键。

set(max_rows)

存储指定表达式的不重复值（不超过 max_rows 个，max_rows=0 则表示『无限制』）。这些信息可用于检查 数据块是否满足 WHERE 条件。

ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)存储一个包含数据块中所有 n元短语（ngram） 的 布隆过滤器 。只可用在字符串上。可用于优化 equals ， like 和 in 表达式的性能。

n – 短语长度。

size_of_bloom_filter_in_bytes – 布隆过滤器大小，单位字节。（因为压缩得好，可以指定比较大的值，如 256 或 512）。

number_of_hash_functions – 布隆过滤器中使用的哈希函数的个数。

random_seed – 哈希函数的随机种子。

tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)

跟 ngrambf_v1 类似，不同于 ngrams 存储字符串指定长度的所有片段。它只存储被非字母数字字符分割的片段。

bloom_filter(bloom_filter([false_positive]) – 为指定的列存储布隆过滤器

可选的参数 false_positive 用来指定从布隆过滤器收到错误响应的几率。取值范围是 (0,1)，默认值：0.025

支持的数据类型：Int*, UInt*, Float*, Enum, Date, DateTime, String, FixedString, Array, LowCardinality, Nullable。以下函数会用到这个索引： equals, notEquals, in, notIn, has

INDEX sample_index (u64 * length(s)) TYPE minmax GRANULARITY 4

INDEX sample_index2 (u64 * length(str), i32 + f64 * 100, date, str) TYPE set(100) GRANULARITY 4

INDEX sample_index3 (lower(str), str) TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4

函数支持

WHERE 子句中的条件包含对列的函数调用，如果列是索引的一部分，ClickHouse 会在执行函数时尝试使用索引。不同的函数对索引的支持是不同的。

set 索引会对所有函数生效，其他索引对函数的生效情况见下表

常量参数小于 ngram 大小的函数不能使用 ngrambf_v1 进行查询优化。

注意

布隆过滤器可能会包含不符合条件的匹配，所以 ngrambf_v1, tokenbf_v1 和 bloom_filter 索引不能用于负向的函数，例如：

可以用来优化的场景

s LIKE '%test%'

NOT s NOT LIKE '%test%'

s = 1

NOT s != 1

startsWith(s, 'test')

不能用来优化的场景NOT s LIKE '%test%' s NOT LIKE '%test%'

NOT s = 1 s != 1

NOT startsWith(s, 'test')

并发数据访问

应对表的并发访问，我们使用多版本机制。换言之，当同时读和更新表时，数据从当前查询到的一组片段中读取。没有冗长的的锁。插入不会阻碍读取。 对表的读操作是自动并行的。

列和表的 TTL

TTL 可以设置值的生命周期，它既可以为整张表设置，也可以为每个列字段单独设置。表级别的 TTL 还会指定数据在磁盘和卷上自动转移的逻辑。

TTL 表达式的计算结果必须是 日期 或 日期时间 类型的字段。示例：

TTL time_column

TTL time_column + interval

要定义interval, 需要使用 时间间隔 操作符。

TTL date_time + INTERVAL 1 MONTH

TTL date_time + INTERVAL 15 HOUR

列 TTL

当列中的值过期时, ClickHouse会将它们替换成该列数据类型的默认值。如果数据片段中列的所有值均已过期，则ClickHouse 会从文件系统中的数据片段中删除此列。

TTL子句不能被用于主键字段。示例:

创建表时指定 TTL

CREATE TABLE example_table (

d DateTime,

a Int TTL d + INTERVAL 1 MONTH, b Int TTL d + INTERVAL 1 MONTH,

c String

)

ENGINE = MergeTree PARTITION BY toYYYYMM(d) ORDER BY d;

为表中已存在的列字段添加 TTL

ALTER TABLE example_table

MODIFY COLUMN

c String TTL d + INTERVAL 1 DAY;

修改列字段的 TTL

ALTER TABLE example_table

MODIFY COLUMN

c String TTL d + INTERVAL 1 MONTH;

表 TTL

表可以设置一个用于移除过期行的表达式，以及多个用于在磁盘或卷上自动转移数据片段的表达式。当表中的行过期时，ClickHouse 会删除所有对应的行。对于数据片段的转移特性，必须所有的行都满足转移条件。

TTL expr [DELETE|TO DISK 'aaa'|TO VOLUME 'bbb'], ...

TTL 规则的类型紧跟在每个 TTL 表达式后面，它会影响满足表达式时（到达指定时间时）应当执行的操作：

DELETE - 删除过期的行（默认操作）;

TO DISK 'aaa' - 将数据片段移动到磁盘 aaa; TO VOLUME 'bbb' - 将数据片段移动到卷 bbb.

示例:

创建时指定 TTL

CREATE TABLE example_table (

d DateTime, a Int

)

ENGINE = MergeTree PARTITION BY toYYYYMM(d) ORDER BY d

TTL d + INTERVAL 1 MONTH [DELETE],

d + INTERVAL 1 WEEK TO VOLUME 'aaa', d + INTERVAL 2 WEEK TO DISK 'bbb';

修改表的 TTL

ALTER TABLE example_table

MODIFY TTL d + INTERVAL 1 DAY;

删除数据

ClickHouse 在数据片段合并时会删除掉过期的数据。

当ClickHouse发现数据过期时, 它将会执行一个计划外的合并。要控制这类合并的频率, 你可以设置 merge_with_ttl_timeout。如果该值被设置的太低, 它将引发大量计划外的合并，这可能会消耗大量资源。

如果在合并的过程中执行 SELECT 查询, 则可能会得到过期的数据。为了避免这种情况，可以在 SELECT 之前使用 OPTIMIZE 查询。

使用具有多个块的设备进行数据存储

介绍

MergeTree 系列表引擎可以将数据存储在多块设备上。这对某些可以潜在被划分为“冷”“热”的表来说是很有用的。近期数据被定期的查询但只需要很小的空间。相反，详尽的历史数据很少被用到。如果有多块磁盘可用，那么“热”的数据可以放置在快速的磁盘上（比如 NVMe 固态硬盘或内存），“冷”的数据可以放在相对较慢的磁盘上（比如机械硬

盘）。

数据片段是 MergeTree 引擎表的最小可移动单元。属于同一个数据片段的数据被存储在同一块磁盘上。数据片段会在后台自动的在磁盘间移动，也可以通过 ALTER 查询来移动。

术语

磁盘 — 挂载到文件系统的块设备

默认磁盘 — 在服务器设置中通过 path 参数指定的数据存储卷 — 磁盘的等效有序集合 （类似于 JBOD）

存储策略 — 卷的集合及他们之间的数据移动规则

配置

磁盘、卷和存储策略应当在主文件 config.xml 或 config.d 目录中的独🖂文件中的 <storage_configuration> 标签内定义。配置结构：

<storage_configuration>

<disks>

<disk_name_1> <!-- disk name -->

<path>/mnt/fast_ssd/clickhouse/</path>

</disk_name_1>

<disk_name_2>

<path>/mnt/hdd1/clickhouse/</path>

<keep_free_space_bytes>10485760</keep_free_space_bytes>

</disk_name_2>

<disk_name_3>

<path>/mnt/hdd2/clickhouse/</path>

<keep_free_space_bytes>10485760</keep_free_space_bytes>

</disk_name_3>

...

</disks>

...

</storage_configuration>

标签：

<disk_name_N> — 磁盘名，名称必须与其他磁盘不同.

path — 服务器将用来存储数据 (data 和 shadow 目录) 的路径, 应当以 ‘/’ 结尾.

keep_free_space_bytes — 需要保留的剩余磁盘空间. 磁盘定义的顺序无关紧要。

存储策略配置：

<storage_configuration>

...

<policies>

<policy_name_1>

<volumes>

<volume_name_1>

<disk>disk_name_from_disks_configuration</disk>

<max_data_part_size_bytes>1073741824</max_data_part_size_bytes>

</volume_name_1>

<volume_name_2>

<!-- configuration -->

</volume_name_2>

<!-- more volumes -->

</volumes>

<move_factor>0.2</move_factor>

</policy_name_1>

<policy_name_2>

<!-- configuration -->

</policy_name_2>

<!-- more policies -->

</policies>

...

</storage_configuration>

标签：

policy_name_N — 策略名称，不能重复。 volume_name_N — 卷名称，不能重复。 disk — 卷中的磁盘。

max_data_part_size_bytes — 任意卷上的磁盘可以存储的数据片段的最大大小。

move_factor — 当可用空间少于这个因子时，数据将自动的向下一个卷（如果有的话）移动 (默认值为 0.1)。配置示例：

<storage_configuration>

...

<policies>

<hdd_in_order> <!-- policy name -->

<volumes>

<single> <!-- volume name -->

<disk>disk1</disk>

<disk>disk2</disk>

</single>

</volumes>

</hdd_in_order>

<moving_from_ssd_to_hdd>

<volumes>

<hot>

<disk>fast_ssd</disk>

<max_data_part_size_bytes>1073741824</max_data_part_size_bytes>

</hot>

<cold>

<disk>disk1</disk>

</cold>

</volumes>

<move_factor>0.2</move_factor>

</moving_from_ssd_to_hdd>

</policies>

...

</storage_configuration>

在给出的例子中， hdd_in_order 策略实现了 循环制 方法。因此这个策略只定义了一个卷（single），数据片段会以循环的顺序全部存储到它的磁盘上。当有多个类似的磁盘挂载到系统上，但没有配置 RAID 时，这种策略非常有用。请注意一个每个独🖂的磁盘驱动都并不可靠，你可能需要用 3 或更大的复制因此来补偿它。

如果在系统中有不同类型的磁盘可用，可以使用 moving_from_ssd_to_hdd。hot 卷由 SSD 磁盘（fast_ssd）组成，这个卷上可以存储的数据片段的最大大小为 1GB。所有大于 1GB 的数据片段都会被直接存储到 cold 卷上，cold 卷包含一个名为 disk1 的 HDD 磁盘。

同样，一旦 fast_ssd 被填充超过 80%，数据会通过后台进程向 disk1 进行转移。

存储策略中卷的枚举顺序是很重要的。因为当一个卷被充满时，数据会向下一个卷转移。磁盘的枚举顺序同样重要，因为数据是依次存储在磁盘上的。 在创建表时，可以将一个配置好的策略应用到表：

CREATE TABLE table_with_non_default_policy ( EventDate Date,

OrderID UInt64, BannerID UInt64, SearchPhrase String

) ENGINE = MergeTree

ORDER BY (OrderID, BannerID) PARTITION BY toYYYYMM(EventDate)

SETTINGS storage_policy = 'moving_from_ssd_to_hdd'

default 存储策略意味着只使用一个卷，这个卷只包含一个在 <path> 中定义的磁盘。表创建后，它的存储策略就不能改变了。

可以通过 background_move_pool_size 设置调整执行后台任务的线程数。

详细说明

对于 MergeTree 表，数据通过以下不同的方式写入到磁盘当中：

作为插入（INSERT查询）的结果在后台合并和数据变异期间

当从另一个副本下载时

作为 ALTER TABLE … FREEZE PARTITION 冻结分区的结果

除了数据变异和冻结分区以外的情况下，数据按照以下逻辑存储到卷或磁盘上：

1. 首个卷（按定义顺序）拥有足够的磁盘空间存储数据片段（unreserved_space > current_part_size）并且允许存储给定数据片段的大小（max_data_part_size_bytes > current_part_size）
2. 在这个数据卷内，紧挨着先前存储数据的那块磁盘之后的磁盘，拥有比数据片段大的剩余空间。（unreserved_space - keep_free_space_bytes > current_part_size）更进一步，数据变异和分区冻结使用的是 硬链接。不同磁盘之间的硬链接是不支持的，所以在这种情况下数据片段都会被存储到初始化的那一块磁盘上。

在后台，数据片段基于剩余空间（move_factor参数）根据卷在配置文件中定义的顺序进行转移。数据永远不会从最后一个移出也不会从第一个移入。可以通过系统表

system.part_log (字段 type = MOVE_PART) 和 system.parts (字段 path 和 disk) 来监控后台的移动情况。同时，具体细节可以通过服务器日志查看。

用户可以通过 ALTER TABLE … MOVE PART|PARTITION … TO VOLUME|DISK … 强制移动一个数据片段或分区到另外一个卷，所有后台移动的限制都会被考虑在内。这个查询会自行启动，无需等待后台操作完成。如果没有足够的可用空间或任何必须条件没有被满足，用户会收到报错信息。

数据移动不会妨碍到数据复制。也就是说，同一张表的不同副本可以指定不同的存储策略。

在后台合并和数据变异之后，就的数据片段会在一定时间后被移除 (old_parts_lifetime)。在这期间，他们不能被移动到其他的卷或磁盘。也就是说，直到数据片段被完全移除，它们仍然会被磁盘占用空间计算在内。

原始文章

ReplacingMergeTree

该引擎和 MergeTree 的不同之处在于它会删除排序键值相同的重复项。

数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要依靠它，因为 OPTIMIZE 语句会引发对数据的大量读写。

因此，ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (

name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],

...

) ENGINE = ReplacingMergeTree([ver]) [PARTITION BY expr]

[ORDER BY expr] [SAMPLE BY expr]

[SETTINGS name=value, ...]

有关建表参数的描述，可参考 创建表。

ReplacingMergeTree 的参数

ver — 版本列。类型为 UInt*, Date 或 DateTime。可选参数。

在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：

如果 ver 列未指定，保留最后一条。

如果 ver 列已指定，保留 ver 值最大的版本。

子句

创建 ReplacingMergeTree 表时，需要使用与创建 MergeTree 表时相同的 子句。

已弃用的建表方法

来源文章

SummingMergeTree

该引擎继承自 MergeTree。区别在于，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。

我们推荐将该引擎和 MergeTree 一起使用。例如，在准备做报告的时候，将完整的数据存储在 MergeTree 表中，并且使用 SummingMergeTree 来存储聚合数据。这种方法可以使你避免因为使用不正确的主键组合方式而丢失有价值的数据。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (

name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],

...

) ENGINE = SummingMergeTree([columns]) [PARTITION BY expr]

[ORDER BY expr] [SAMPLE BY expr]

[SETTINGS name=value, ...]

请求参数的描述，参考 请求描述。

SummingMergeTree 的参数

columns - 包含了将要被汇总的列的列名的元组。可选参数。所选的列必须是数值类型，并且不可位于主键中。

如果没有指定 `columns`，ClickHouse 会把所有不在主键中的数值类型的列都进行汇总。

子句

创建 SummingMergeTree 表时，需要与创建 MergeTree 表时相同的子句。

已弃用的建表方法

用法示例

考虑如下的表：

CREATE TABLE summtt (

key UInt32, value UInt32

)

ENGINE = SummingMergeTree()

ORDER BY key

向其中插入数据：

:) INSERT INTO summtt Values(1,1),(1,2),(2,1)

ClickHouse可能不会完整的汇总所有行（见下文）,因此我们在查询中使用了聚合函数 sum 和 GROUP BY 子句。

SELECT key, sum(value) FROM summtt GROUP BY key

┌─key─┬─sum(value)─┐

│ 2 │ 1 │

│ 1 │ 3 │

└─────┴────────────┘

数据处理

当数据被插入到表中时，他们将被原样保存。ClickHouse 定期合并插入的数据片段，并在这个时候对所有具有相同主键的行中的列进行汇总，将这些行替换为包含汇总数据的一行记录。

ClickHouse 会按片段合并数据，以至于不同的数据片段中会包含具有相同主键的行，即单个汇总片段将会是不完整的。因此，聚合函数 sum() 和 GROUP BY 子句应该在

（SELECT）查询语句中被使用，如上文中的例子所述。

汇总的通用规则

列中数值类型的值会被汇总。这些列的集合在参数 columns 中被定义。如果用于汇总的所有列中的值均为0，则该行会被删除。

如果列不在主键中且无法被汇总，则会在现有的值中任选一个。 主键所在的列中的值不会被汇总。

AggregateFunction 列中的汇总

对于 AggregateFunction 类型的列，ClickHouse 根据对应函数表现为 AggregatingMergeTree 引擎的聚合。

嵌套结构

表中可以具有以特殊方式处理的嵌套数据结构。

如果嵌套表的名称以 Map 结尾，并且包含至少两个符合以下条件的列：

第一列是数值类型 (*Int*, Date, DateTime)，我们称之为 key,

其他的列是可计算的 (*Int*, Float32/64)，我们称之为 (values...),

然后这个嵌套表会被解释为一个 key => (values...) 的映射，当合并它们的行时，两个数据集中的元素会被根据 key 合并为相应的 (values...) 的汇总值。示例：

[(1, 100)] + [(2, 150)] -> [(1, 100), (2, 150)]

[(1, 100)] + [(1, 150)] -> [(1, 250)]

[(1, 100)] + [(1, 150), (2, 150)] -> [(1, 250), (2, 150)]

[(1, 100), (2, 150)] + [(1, -100)] -> [(2, 150)]

请求数据时，使用 sumMap(key,value) 函数来对 Map 进行聚合。对于嵌套数据结构，你无需在列的元组中指定列以进行汇总。

来源文章

折叠树

该引擎继承于 MergeTree，并在数据块合并算法中添加了折叠行的逻辑。

CollapsingMergeTree 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。更多的细节请看本文的折叠部分。

因此，该引擎可以显著的降低存储量并提高 SELECT 查询效率。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (

name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],

...

) ENGINE = CollapsingMergeTree(sign) [PARTITION BY expr]

[ORDER BY expr] [SAMPLE BY expr]

[SETTINGS name=value, ...]

请求参数的描述，参考请求参数。

CollapsingMergeTree 参数

sign — 类型列的名称： 1 是«状态»行，-1 是«取消»行。列数据类型 — Int8。

子句

创建 CollapsingMergeTree 表时，需要与创建 MergeTree 表时相同的子句。

已弃用的建表方法

折叠

数据

考虑你需要为某个对象保存不断变化的数据的情景。似乎为一个对象保存一行记录并在其发生任何变化时更新记录是合乎逻辑的，但是更新操作对 DBMS 来说是昂贵且缓慢的， 因为它需要重写存储中的数据。如果你需要快速的写入数据，则更新操作是不可接受的，但是你可以按下面的描述顺序地更新一个对象的变化。

在写入行的时候使用特定的列 Sign。如果 Sign = 1 则表示这一行是对象的状态，我们称之为«状态»行。如果 Sign = -1 则表示是对具有相同属性的状态行的取消，我们称之为«取消»行。

例如，我们想要计算用户在某个站点访问的页面页面数以及他们在那里停留的时间。在某个时候，我们将用户的活动状态写入下面这样的行。

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │ 5 │ 146 │ 1 │

└─────────────────────┴───────────┴──────────┴──────┘

一段时间后，我们写入下面的两行来记录用户活动的变化。

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │ 5 │ 146 │ -1 │

│ 4324182021466249494 │ 6 │ 185 │ 1 │

└─────────────────────┴───────────┴──────────┴──────┘

第一行取消了这个对象（用户）的状态。它需要复制被取消的状态行的所有除了 Sign 的属性。第二行包含了当前的状态。

因为我们只需要用户活动的最后状态，这些行

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │ 5 │ 146 │ 1 │

│ 4324182021466249494 │ 5 │ 146 │ -1 │

└─────────────────────┴───────────┴──────────┴──────┘

可以在折叠对象的失效（老的）状态的时候被删除。CollapsingMergeTree 会在合并数据片段的时候做这件事。为什么我们每次改变需要 2 行可以阅读算法段。

这种方法的特殊属性

1. 写入的程序应该记住对象的状态从而可以取消它。«取消»字符串应该是«状态»字符串的复制，除了相反的 Sign。它增加了存储的初始数据的大小，但使得写入数据更快速。
2. 由于写入的负载，列中长的增长阵列会降低引擎的效率。数据越简单，效率越高。
3. SELECT 的结果很大程度取决于对象变更历史的一致性。在准备插入数据时要准确。在不一致的数据中会得到不可预料的结果，例如，像会话深度这种非负指标的负值。

算法

当 ClickHouse 合并数据片段时，每组具有相同主键的连续行被减少到不超过两行，一行 Sign = 1（«状态»行），另一行 Sign = -1 （«取消»行），换句话说，数据项被折叠了。对每个结果的数据部分 ClickHouse 保存：

1. 第一个«取消»和最后一个«状态»行，如果«状态»和«取消»行的数量匹配和最后一个行是«状态»行
2. 最后一个«状态»行，如果«状态»行比«取消»行多一个或一个以上。
3. 第一个«取消»行，如果«取消»行比«状态»行多一个或一个以上。
4. 没有行，在其他所有情况下。

合并会继续，但是 ClickHouse 会把此情况视为逻辑错误并将其记录在服务日志中。这个错误会在相同的数据被插入超过一次时出现。

因此，折叠不应该改变统计数据的结果。

变化逐渐地被折叠，因此最终几乎每个对象都只剩下了最后的状态。

Sign 是必须的因为合并算法不保证所有有相同主键的行都会在同一个结果数据片段中，甚至是在同一台物理服务器上。ClickHouse 用多线程来处理 SELECT 请求，所以它不能预测结果中行的顺序。如果要从 CollapsingMergeTree 表中获取完全«折叠»后的数据，则需要聚合。

要完成折叠，请使用 GROUP BY 子句和用于处理符号的聚合函数编写请求。例如，要计算数量，使用 sum(Sign) 而不是 count()。要计算某物的总和，使用 sum(Sign * x) 而不是

sum(x)，并添加 HAVING sum(Sign) > 0 子句。

聚合体 count,sum 和 avg 可以用这种方式计算。如果一个对象至少有一个未被折叠的状态，则可以计算 uniq 聚合。min 和 max 聚合无法计算，因为 CollaspingMergeTree 不会保存折叠状态的值的历史记录。

如果你需要在不进行聚合的情况下获取数据（例如，要检查是否存在最新值与特定条件匹配的行），你可以在 FROM 从句中使用 FINAL 修饰符。这种方法显然是更低效的。

示例

示例数据:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │ 5 │ 146 │ 1 │

│ 4324182021466249494 │ 5 │ 146 │ -1 │

│ 4324182021466249494 │ 6 │ 185 │ 1 │

└─────────────────────┴───────────┴──────────┴──────┘

建表:

CREATE TABLE UAct (

UserID UInt64, PageViews UInt8, Duration UInt8, Sign Int8

)

ENGINE = CollapsingMergeTree(Sign)

ORDER BY UserID

插入数据:

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1)

我们使用两次 INSERT 请求来创建两个不同的数据片段。如果我们使用一个请求插入数据，ClickHouse 只会创建一个数据片段且不会执行任何合并操作。获取数据：

SELECT * FROM UAct

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │ 5 │ 146 │ -1 │

│ 4324182021466249494 │ 6 │ 185 │ 1 │

└─────────────────────┴───────────┴──────────┴──────┘

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │ 5 │ 146 │ 1 │

└─────────────────────┴───────────┴──────────┴──────┘

我们看到了什么，哪里有折叠？

通过两个 INSERT 请求，我们创建了两个数据片段。SELECT 请求在两个线程中被执行，我们得到了随机顺序的行。没有发生折叠是因为还没有合并数据片段。ClickHouse 在一个我们无法预料的未知时刻合并数据片段。

因此我们需要聚合：

SELECT

UserID,

sum(PageViews * Sign) AS PageViews,

sum(Duration * Sign) AS Duration

FROM UAct

GROUP BY UserID

HAVING sum(Sign) > 0

┌──────────────UserID─┬─PageViews─┬─Duration─┐

│ 4324182021466249494 │ 6 │ 185 │

└─────────────────────┴───────────┴──────────┘

如果我们不需要聚合并想要强制进行折叠，我们可以在 FROM 从句中使用 FINAL 修饰语。

SELECT * FROM UAct FINAL

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐

│ 4324182021466249494 │ 6 │ 185 │ 1 │

└─────────────────────┴───────────┴──────────┴──────┘

这种查询数据的方法是非常低效的。不要在大表中使用它。 来源文章

数据副本

只有 MergeTree 系列里的表可支持副本：

ReplicatedMergeTree ReplicatedSummingMergeTree ReplicatedReplacingMergeTree ReplicatedAggregatingMergeTree ReplicatedCollapsingMergeTree ReplicatedVersionedCollapsingMergetree ReplicatedGraphiteMergeTree

副本是表级别的，不是整个服务器级的。所以，服务器里可以同时有复制表和非复制表。 副本不依赖分片。每个分片有它自己的独🖂副本。

对于 INSERT 和 ALTER 语句操作数据的会在压缩的情况下被复制（更多信息，看 ALTER ）。

而 CREATE，DROP，ATTACH，DETACH 和 RENAME 语句只会在单个服务器上执行，不会被复制。

The CREATE TABLE 在运行此语句的服务器上创建一个新的可复制表。如果此表已存在其他服务器上，则给该表添加新副本。The DROP TABLE 删除运行此查询的服务器上的副本。

The RENAME 重命名一个副本。换句话说，可复制表不同的副本可以有不同的名称。要使用副本，需在配置文件中设置 ZooKeeper 集群的地址。例如：

<zookeeper>

<node index="1">

<host>example1</host>

<port>2181</port>

</node>

<node index="2">

<host>example2</host>

<port>2181</port>

</node>

<node index="3">

<host>example3</host>

<port>2181</port>

</node>

</zookeeper>

需要 ZooKeeper 3.4.5 或更高版本。

你可以配置任何现有的 ZooKeeper 集群，系统会使用里面的目录来存取元数据（该目录在创建可复制表时指定）。如果配置文件中没有设置 ZooKeeper ，则无法创建复制表，并且任何现有的复制表都将变为只读。

SELECT 查询并不需要借助 ZooKeeper ，副本并不影响 SELECT 的性能，查询复制表与非复制表速度是一样的。查询分布式表时，ClickHouse的处理方式可通过设置

max_replica_delay_for_distributed_queries 和 fallback_to_stale_replicas_for_distributed_queries 修改。

对于每个 INSERT 语句，会通过几个事务将十来个记录添加到 ZooKeeper。（确切地说，这是针对每个插入的数据块; 每个 INSERT 语句的每 max_insert_block_size = 1048576行和最后剩余的都各算作一个块。）相比非复制表，写 zk 会导致 INSERT 的延迟略长一些。但只要你按照建议每秒不超过一个 INSERT 地批量插入数据，不会有任何问题。一个 ZooKeeper 集群能给整个 ClickHouse 集群支撑协调每秒几百个 INSERT。数据插入的吞吐量（每秒的行数）可以跟不用复制的数据一样高。

对于非常大的集群，你可以把不同的 ZooKeeper 集群用于不同的分片。然而，即使 Yandex.Metrica 集群（大约300台服务器）也证明还不需要这么做。

复制是多主异步。 INSERT 语句（以及 ALTER ）可以发给任意可用的服务器。数据会先插入到执行该语句的服务器上，然后被复制到其他服务器。由于它是异步的，在其他副本上最近插入的数据会有一些延迟。如果部分副本不可用，则数据在其可用时再写入。副本可用的情况下，则延迟时长是通过网络传输压缩数据块所需的时间。

默认情况下，INSERT 语句仅等待一个副本写入成功后返回。如果数据只成功写入一个副本后该副本所在的服务器不再存在，则存储的数据会丢失。要启用数据写入多个副本才确认返回，使用 insert_quorum 选项。

单个数据块写入是原子的。 INSERT 的数据按每块最多 max_insert_block_size = 1048576 行进行分块，换句话说，如果 INSERT 插入的行少于 1048576，则该 INSERT 是原子的。

数据块会去重。对于被多次写的相同数据块（大小相同且具有相同顺序的相同行的数据块），该块仅会写入一次。这样设计的原因是万一在网络故障时客户端应用程序不知道数据 是否成功写入DB，此时可以简单地重复 INSERT 。把相同的数据发送给多个副本 INSERT 并不会有问题。因为这些 INSERT 是完全相同的（会被去重）。去重参数参看服务器设置 merge_tree 。（注意：Replicated*MergeTree 才会去重，不需要 zookeeper 的不带 MergeTree 不会去重）

在复制期间，只有要插入的源数据通过网络传输。进一步的数据转换（合并）会在所有副本上以相同的方式进行处理执行。这样可以最大限度地减少网络使用，这意味着即使副本 在不同的数据中心，数据同步也能工作良好。（能在不同数据中心中的同步数据是副本机制的主要目标。）

你可以给数据做任意多的副本。Yandex.Metrica 在生产中使用双副本。某一些情况下，给每台服务器都使用 RAID-5 或 RAID-6 和 RAID-10。是一种相对可靠和方便的解决方案。

系统会监视副本数据同步情况，并能在发生故障后恢复。故障转移是自动的（对于小的数据差异）或半自动的（当数据差异很大时，这可能意味是有配置错误）。

创建复制表

在表引擎名称上加上 Replicated 前缀。例如：ReplicatedMergeTree。

Replicated*MergeTree 参数

zoo_path — ZooKeeper 中该表的路径。

replica_name — ZooKeeper 中的该表的副本名称。

示例:

CREATE TABLE table_name

(

EventDate DateTime, CounterID UInt32, UserID UInt32

) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}') PARTITION BY toYYYYMM(EventDate)

ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID)

已弃用的建表语法示例：

CREATE TABLE table_name

(

EventDate DateTime, CounterID UInt32, UserID UInt32

) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}', EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID), EventTime), 8192)

如上例所示，这些参数可以包含宏替换的占位符，即大括号的部分。它们会被替换为配置文件里 ‘macros’ 那部分配置的值。示例：

<macros>

<layer>05</layer>

<shard>02</shard>

<replica>example05-02-1.yandex.ru</replica>

</macros>

«ZooKeeper 中该表的路径»对每个可复制表都要是唯一的。不同分片上的表要有不同的路径。这种情况下，路径包含下面这些部分：

/clickhouse/tables/ 是公共前缀，我们推荐使用这个。

{layer}-{shard} 是分片标识部分。在此示例中，由于 Yandex.Metrica 集群使用了两级分片，所以它是由两部分组成的。但对于大多数情况来说，你只需保留 {shard} 占位符即可，它会替换展开为分片标识。

table_name 是该表在 ZooKeeper 中的名称。使其与 ClickHouse 中的表名相同比较好。 这里它被明确定义，跟 ClickHouse 表名不一样，它并不会被 RENAME 语句修改。

HINT：你可以在前面添加一个数据库名称 table_name 也是 例如。 db_name.table_name

副本名称用于标识同一个表分片的不同副本。你可以使用服务器名称，如上例所示。同个分片中不同副本的副本名称要唯一。

你也可以显式指定这些参数，而不是使用宏替换。对于测试和配置小型集群这可能会很方便。但是，这种情况下，则不能使用分布式 DDL 语句（ON CLUSTER）。使用大型集群时，我们建议使用宏替换，因为它可以降低出错的可能性。

在每个副本服务器上运行 CREATE TABLE 查询。将创建新的复制表，或给现有表添加新副本。

如果其他副本上已包含了某些数据，在表上添加新副本，则在运行语句后，数据会从其他副本复制到新副本。换句话说，新副本会与其他副本同步。 要删除副本，使用 DROP TABLE。但它只删除那个 – 位于运行该语句的服务器上的副本。

故障恢复

如果服务器启动时 ZooKeeper 不可用，则复制表会切换为只读模式。系统会定期尝试去连接 ZooKeeper。如果在 INSERT 期间 ZooKeeper 不可用，或者在与 ZooKeeper 交互时发生错误，则抛出异常。

连接到 ZooKeeper 后，系统会检查本地文件系统中的数据集是否与预期的数据集（ ZooKeeper 存储此信息）一致。如果存在轻微的不一致，系统会通过与副本同步数据来解决。

如果系统检测到损坏的数据片段（文件大小错误）或无法识别的片段（写入文件系统但未记录在 ZooKeeper 中的部分），则会把它们移动到 ‘detached’ 子目录（不会删除）。而副本中其他任何缺少的但正常数据片段都会被复制同步。

注意，ClickHouse 不会执行任何破坏性操作，例如自动删除大量数据。

当服务器启动（或与 ZooKeeper 建🖂新会话）时，它只检查所有文件的数量和大小。 如果文件大小一致但中间某处已有字节被修改过，不会🖂即被检测到，只有在尝试读取

SELECT 查询的数据时才会检测到。该查询会引发校验和不匹配或压缩块大小不一致的异常。这种情况下，数据片段会添加到验证队列中，并在必要时从其他副本中复制。

如果本地数据集与预期数据的差异太大，则会触发安全机制。服务器在日志中记录此内容并拒绝启动。这种情况很可能是配置错误，例如，一个分片上的副本意外配置为别的分片 上的副本。然而，此机制的阈值设置得相当低，在正常故障恢复期间可能会出现这种情况。在这种情况下，数据恢复则是半自动模式，通过用户主动操作触发。

要触发启动恢复，可在 ZooKeeper 中创建节点 /path_to_table/replica_name/flags/force_restore_data，节点值可以是任何内容，或运行命令来恢复所有的可复制表：

sudo -u clickhouse touch /var/lib/clickhouse/flags/force_restore_data

然后重启服务器。启动时，服务器会删除这些标志并开始恢复。

在数据完全丢失后的恢复

如果其中一个服务器的所有数据和元数据都消失了，请按照以下步骤进行恢复：

1. 在服务器上安装 ClickHouse。在包含分片标识符和副本的配置文件中正确定义宏配置，如果有用到的话，
2. 如果服务器上有非复制表则必须手动复制，可以从副本服务器上（在 /var/lib/clickhouse/data/db_name/table_name/ 目录中）复制它们的数据。
3. 从副本服务器上中复制位于 /var/lib/clickhouse/metadata/ 中的表定义信息。如果在表定义信息中显式指定了分片或副本标识符，请更正它以使其对应于该副本。（另外，启动服务器，然后会在 /var/lib/clickhouse/metadata/ 中的.sql文件中生成所有的 ATTACH TABLE 语句。）
4. 要开始恢复，ZooKeeper 中创建节点 /path_to_table/replica_name/flags/force_restore_data，节点内容不限，或运行命令来恢复所有复制的表：sudo -u clickhouse touch

/var/lib/clickhouse/flags/force_restore_data

然后启动服务器（如果它已运行则重启）。数据会从副本中下载。

另一种恢复方式是从 ZooKeeper（/path_to_table/replica_name）中删除有数据丢的副本的所有元信息，然后再按照«创建可复制表»中的描述重新创建副本。恢复期间的网络带宽没有限制。特别注意这一点，尤其是要一次恢复很多副本。

MergeTree 转换为 ReplicatedMergeTree

我们使用 MergeTree 来表示 MergeTree系列 中的所有表引擎，ReplicatedMergeTree 同理。

如果你有一个手动同步的 MergeTree 表，您可以将其转换为可复制表。如果你已经在 MergeTree 表中收集了大量数据，并且现在要启用复制，则可以执行这些操作。如果各个副本上的数据不一致，则首先对其进行同步，或者除保留的一个副本外，删除其他所有副本上的数据。

重命名现有的 MergeTree 表，然后使用旧名称创建 ReplicatedMergeTree 表。

将数据从旧表移动到新表（/var/lib/clickhouse/data/db_name/table_name/）目录内的 ‘detached’ 目录中。然后在其中一个副本上运行ALTER TABLE ATTACH PARTITION，将这些数据片段添加到工作集中。

ReplicatedMergeTree 转换为 MergeTree

使用其他名称创建 MergeTree 表。将具有ReplicatedMergeTree表数据的目录中的所有数据移动到新表的数据目录中。然后删除ReplicatedMergeTree表并重新启动服务器。如果你想在不启动服务器的情况下清除 ReplicatedMergeTree 表：

删除元数据目录中的相应 .sql 文件（/var/lib/clickhouse/metadata/）。删除 ZooKeeper 中的相应路径（/path_to_table/replica_name）。

之后，你可以启动服务器，创建一个 MergeTree 表，将数据移动到其目录，然后重新启动服务器。

当 ZooKeeper 集群中的元数据丢失或损坏时恢复方法

如果 ZooKeeper 中的数据丢失或损坏，如上所述，你可以通过将数据转移到非复制表来保存数据。来源文章

自定义分区键

MergeTree 系列的表（包括 可复制表 ）可以使用分区。基于 MergeTree 表的 物化视图 也支持分区。

分区是在一个表中通过指定的规则划分而成的逻辑数据集。可以按任意标准进行分区，如按月，按日或按事件类型。为了减少需要操作的数据，每个分区都是分开存储的。访问数 据时，ClickHouse 尽量使用这些分区的最小子集。

分区是在 建表 时通过 PARTITION BY expr 子句指定的。分区键可以是表中列的任意表达式。例如，指定按月分区，表达式为 toYYYYMM(date_column)：

CREATE TABLE visits (

VisitDate Date, Hour UInt8, ClientID UUID

)

ENGINE = MergeTree()

PARTITION BY toYYYYMM(VisitDate)

ORDER BY Hour;

分区键也可以是表达式元组（类似 主键 ）。例如：

ENGINE = ReplicatedCollapsingMergeTree('/clickhouse/tables/name', 'replica1', Sign) PARTITION BY (toMonday(StartDate), EventType)

ORDER BY (CounterID, StartDate, intHash32(UserID));

上例中，我们设置按一周内的事件类型分区。

新数据插入到表中时，这些数据会存储为按主键排序的新片段（块）。插入后 10-15 分钟，同一分区的各个片段会合并为一整个片段。

注意

那些有相同分区表达式值的数据片段才会合并。这意味着 你不应该用太精细的分区方案（超过一千个分区）。否则，会因为文件系统中的文件数量过多和需要打开的文件描 述符过多，导致 SELECT 查询效率不佳。

可以通过 system.parts 表查看表片段和分区信息。例如，假设我们有一个 visits 表，按月分区。对 system.parts 表执行 SELECT：

SELECT

partition, name, active

FROM system.parts WHERE table = 'visits'

┌─partition─┬─name───────────┬─active─┐

│ 201901 │ 201901_1_3_1 │ 0 │

│ 201901 │ 201901_1_9_2 │ 1 │

│ 201901 │ 201901_8_8_0 │ 0 │

│ 201901 │ 201901_9_9_0 │ 0 │

│ 201902 │ 201902_4_6_1 │ 1 │

│ 201902 │ 201902_10_10_0 │ 1 │

│ 201902 │ 201902_11_11_0 │ 1 │

└───────────┴────────────────┴────────┘

partition 列存储分区的名称。此示例中有两个分区：201901 和 201902。在 ALTER … PARTITION 语句中你可以使用该列值来指定分区名称。

name 列为分区中数据片段的名称。在 ALTER ATTACH PART 语句中你可以使用此列值中来指定片段名称。这里我们拆解下第一个数据片段的名称：201901_1_3_1：

201901 是分区名称。

1 是数据块的最小编号。

3 是数据块的最大编号。

1 是块级别（即在由块组成的合并树中，该块在树中的深度）。

注意

旧类型表的片段名称为：20190117_20190123_2_2_0（最小日期 - 最大日期 - 最小块编号 - 最大块编号 - 块级别）。

active 列为片段状态。1 代表激活状态；0 代表非激活状态。非激活片段是那些在合并到较大片段之后剩余的源数据片段。损坏的数据片段也表示为非活动状态。

正如在示例中所看到的，同一分区中有几个独🖂的片段（例如，201901_1_3_1和201901_1_9_2）。这意味着这些片段尚未合并。ClickHouse 会定期的对插入的数据片段进行合并，大约是在插入后15分钟左右。此外，你也可以使用 OPTIMIZE 语句发起一个计划外的合并。例如：

OPTIMIZE TABLE visits PARTITION 201902;

┌─partition─┬─name───────────┬─active─┐

│ 201901 │ 201901_1_3_1 │ 0 │

│ 201901 │ 201901_1_9_2 │ 1 │

│ 201901 │ 201901_8_8_0 │ 0 │

│ 201901 │ 201901_9_9_0 │ 0 │

│ 201902 │ 201902_4_6_1 │ 0 │

│ 201902 │ 201902_4_11_2 │ 1 │

│ 201902 │ 201902_10_10_0 │ 0 │

│ 201902 │ 201902_11_11_0 │ 0 │

└───────────┴────────────────┴────────┘

非激活片段会在合并后的10分钟左右被删除。

查看片段和分区信息的另一种方法是进入表的目录：/var/lib/clickhouse/data/<database>/<table>/。例如：

/var/lib/clickhouse/data/default/visits$ ls -l total 40

drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 1 16:48 201901_1_3_1

drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 16:17 201901_1_9_2

drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 15:52 201901_8_8_0

drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 15:52 201901_9_9_0

drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 16:17 201902_10_10_0

drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 16:17 201902_11_11_0

drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 16:19 201902_4_11_2

drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 5 12:09 201902_4_6_1

drwxr-xr-x 2 clickhouse clickhouse 4096 Feb 1 16:48 detached

‘201901_1_1_0’，‘201901_1_7_1’ 等文件夹是数据片段的目录。每个片段都与一个对应的分区相关，并且只包含这个月的数据（本例中的表按月分区）。

detached 目录存放着使用 DETACH 语句从表中卸载的片段。损坏的片段不会被删除而是也会移到该目录下。服务器不会去使用detached目录中的数据片段。因此你可以随时添加，删除或修改此目录中的数据 – 在运行 ATTACH 语句前，服务器不会感知到。

注意，在操作服务器时，你不能手动更改文件系统上的片段集或其数据，因为服务器不会感知到这些修改。对于非复制表，可以在服务器停止时执行这些操作，但不建议这样做。 对于复制表，在任何情况下都不要更改片段文件。

ClickHouse 支持对分区执行这些操作：删除分区，将分区从一个表复制到另一个表，或创建备份。了解分区的所有操作，请参阅 分区和片段的操作 一节。来源文章

日志引擎系列

这些引擎是为了需要写入许多小数据量（少于一百万行）的表的场景而开发的。 这系列的引擎有：

StripeLog日志TinyLog

共同属性

引擎：

数据存储在磁盘上。

写入时将数据追加在文件末尾。

不支持突变操作。

不支持索引。

这意味着 `SELECT` 在范围查询时效率不高。

非原子地写入数据。

如果某些事情破坏了写操作，例如服务器的异常关闭，你将会得到一张包含了损坏数据的表。

差异

Log 和 StripeLog 引擎支持：

并发访问数据的锁。

`INSERT` 请求执行过程中表会被锁定，并且其他的读写数据的请求都会等待直到锁定被解除。如果没有写数据的请求，任意数量的读请求都可以并发执行。

并行读取数据。

在读取数据时，ClickHouse 使用多线程。 每个线程处理不同的数据块。

Log 引擎为表中的每一列使用不同的文件。StripeLog 将所有的数据存储在一个文件中。因此 StripeLog 引擎在操作系统中使用更少的描述符，但是 Log 引擎提供更高的读性能。

TinyLog 引擎是该系列中最简单的引擎并且提供了最少的功能和最低的性能。TinyLog 引擎不支持并行读取和并发数据访问，并将每一列存储在不同的文件中。它比其余两种支持并行读取的引擎的读取速度更慢，并且使用了和 Log 引擎同样多的描述符。你可以在简单的低负载的情景下使用它。

来源文章

Log

Log 与 TinyLog 的不同之处在于，«标记» 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行 数。这使得可以在多个线程中读取表数据。对于并发数据访问，可以同时执行读取操作，而写入操作则阻塞读取和其它写入。Log引擎不支持索引。同样，如果写入表失败，则该 表将被破坏，并且从该表读取将返回错误。Log引擎适用于临时数据，write-once 表以及测试或演示目的。

原始文章

StripeLog

该引擎属于日志引擎系列。请在日志引擎系列文章中查看引擎的共同属性和差异。 在你需要写入许多小数据量（小于一百万行）的表的场景下使用这个引擎。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (

column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],

...

) ENGINE = StripeLog

查看建表请求的详细说明。

写数据

StripeLog 引擎将所有列存储在一个文件中。对每一次 Insert 请求，ClickHouse 将数据块追加在表文件的末尾，逐列写入。

ClickHouse 为每张表写入以下文件：

data.bin — 数据文件。

index.mrk — 带标记的文件。标记包含了已插入的每个数据块中每列的偏移量。

StripeLog 引擎不支持 ALTER UPDATE 和 ALTER DELETE 操作。

读数据

带标记的文件使得 ClickHouse 可以并行的读取数据。这意味着 SELECT 请求返回行的顺序是不可预测的。使用 ORDER BY 子句对行进行排序。

使用示例

建表：

CREATE TABLE stripe_log_table (

timestamp DateTime, message_type String, message String

)

ENGINE = StripeLog

插入数据：

INSERT INTO stripe_log_table VALUES (now(),'REGULAR','The first regular message')

INSERT INTO stripe_log_table VALUES (now(),'REGULAR','The second regular message'),(now(),'WARNING','The first warning message')

我们使用两次 INSERT 请求从而在 data.bin 文件中创建两个数据块。

ClickHouse 在查询数据时使用多线程。每个线程读取单独的数据块并在完成后独🖂的返回结果行。这样的结果是，大多数情况下，输出中块的顺序和输入时相应块的顺序是不同的。例如：

SELECT * FROM stripe_log_table

┌───────────timestamp─┬─message_type─┬─message────────────────────┐

│ 2019-01-18 14:27:32 │ REGULAR │ The second regular message │

│ 2019-01-18 14:34:53 │ WARNING │ The first warning message │

└─────────────────────┴──────────────┴────────────────────────────┘

┌───────────timestamp─┬─message_type─┬─message───────────────────┐

│ 2019-01-18 14:23:43 │ REGULAR │ The first regular message │

└─────────────────────┴──────────────┴───────────────────────────┘

对结果排序（默认增序）：

SELECT * FROM stripe_log_table ORDER BY timestamp

┌───────────timestamp─┬─message_type─┬─message────────────────────┐

│ 2019-01-18 14:23:43 │ REGULAR │ The first regular message │

│ 2019-01-18 14:27:32 │ REGULAR │ The second regular message │

│ 2019-01-18 14:34:53 │ WARNING │ The first warning message │

└─────────────────────┴──────────────┴────────────────────────────┘

来源文章

TinyLog

最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中。写入时，数据将附加到文件末尾。

并发数据访问不受任何限制：

• 如果同时从表中读取并在不同的查询中写入，则读取操作将抛出异常
• 如果同时写入多个查询中的表，则数据将被破坏。

这种表引擎的典型用法是 write-once：首先只写入一次数据，然后根据需要多次读取。查询在单个流中执行。换句话说，此引擎适用于相对较小的表（建议最多1,000,000行）。如果您有许多小表，则使用此表引擎是适合的，因为它比Log引擎更简单（需要打开的文件更少）。当您拥有大量小表时，可能会导致性能低下，但在可能已经在其它 DBMS 时使用过，则您可能会发现切换使用 TinyLog 类型的表更容易。不支持索引。

在 Yandex.Metrica 中，TinyLog 表用于小批量处理的中间数据。原始文章

JDBC

允许CH通过 JDBC 连接到外部数据库。

要实现JDBC连接，CH需要使用以后台进程运行的程序 clickhouse-jdbc-bridge。该引擎支持 Nullable 数据类型。

建表

CREATE TABLE [IF NOT EXISTS] [db.]table_name (

columns list...

)

ENGINE = JDBC(dbms_uri, external_database, external_table)

引擎参数

dbms_uri — 外部DBMS的uri.

格式: jdbc:<driver_name>://<host_name>:<port>/?user=<username>&password=<password>. MySQL示例: jdbc:mysql://localhost:3306/?user=root&password=root.

external_database — 外部DBMS的数据库名.

external_table — external_database中的外部表名.

用法示例

通过mysql控制台客户端来创建表

Creating a table in MySQL server by connecting directly with it’s console client:

mysql> CREATE TABLE `test`.`test` (

-> `int_id` INT NOT NULL AUTO_INCREMENT,

-> `int_nullable` INT NULL DEFAULT NULL,

-> `float` FLOAT NOT NULL,

-> `float_nullable` FLOAT NULL DEFAULT NULL,

-> PRIMARY KEY (`int_id`)); Query OK, 0 rows affected (0,09 sec)

mysql> insert into test (`int_id`, `float`) VALUES (1,2); Query OK, 1 row affected (0,00 sec)

mysql> select * from test;