ClickHouse 指南（一）

ClickHouse release 24.1, 2024-01-30

1、创建表

像大多数数据库一样，ClickHouse在逻辑上将表分组到 databases 中。使用CREATE DATABASE命令在ClickHouse中创建一个新的数据库:

CREATE DATABASE IF NOT EXISTS helloworld

类似地，使用CREATE TABLE定义一个新表。(如果不指定数据库名称，则表将在default 数据库中。)下面这个表的名字是helloworld数据库中的my_first_table:

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

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

• User_id: 32位无符号整数
• message:一个 String数据类型，它取代了其他数据库系统中的VARCHAR、BLOB、CLOB等类型
• timestamp: DateTime值，表示时间中的某个瞬间
• Metric: 32位浮点数

表引擎确定:
如何以及在哪里存储数据
支持哪些查询
数据是否被复制
有许多引擎可供选择，但对于单节点ClickHouse服务器上的简单表，MergeTree是您可能的选择。

主键简介

在你进一步深入之前，了解主键在ClickHouse中的工作原理是很重要的(主键的实现似乎出乎意料!):

• ClickHouse中的主键不是表中每行唯一的

ClickHouse表的主键决定数据在写入磁盘时如何排序。每8,192行或10MB的数据(称为索引粒度,index granularity)在主键索引文件中创建一个条目。这种粒度概念创建了一个可以轻松放入内存的稀疏索引(sparse index )，粒度(granules )表示SELECT查询期间处理的最少量列数据的条带。

主键可以使用PRIMARY KEY参数定义。如果您定义了一个没有指定PRIMARY KEY的表，那么该键将变成ORDER BY子句中指定的元组。如果同时指定了PRIMARY KEY和ORDER BY，则主键必须是排序顺序的子集。

主键也是排序键，它是一个元组(user_id, timestamp)。因此，存储在每个列文件中的数据将按user_id排序，然后按timestamp排序。

2、插入数据到ClickHouse

您可以在ClickHouse中使用熟悉的INSERT INTO TABLE命令，但重要的是要理解，每次插入到MergeTree表中都会导致在存储中创建一个part 。即使是一个简单的例子，让我们一次插入多行:

INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 )

注意，timestamp 列是使用各种Date和DateTime函数填充的。ClickHouse有数百个有用的函数，您可以在函数部分查看。

让我们验证一下它是否有效——您应该看到插入的四行数据。

SELECT * FROM helloworld.my_first_table

批量插入

每批插入大量行——一次插入数万甚至数百万行。批量插入可优化插入性能。别担心- ClickHouse可以轻松处理这种类型的数量!

如果您不能一次插入很多行，并且您使用的是HTTP客户端，那么请使用async_insert设置，它会在将较小的插入插入到表中之前对它们进行批处理。

与ClickHouse整合

无论您的数据位于何处，都可能有表函数，表引擎或其他类型的工具可用于将您的数据插入ClickHouse。查看我们的集成页面了解更多细节。

3、SELECT 查询

ClickHouse是一个SQL数据库，您可以通过编写您已经熟悉的相同类型的SELECT查询来查询数据。例如:

SELECT *
FROM helloworld.my_first_table
ORDER BY timestamp

有关语法和可用子句和选项的更多详细信息，请参阅SQL Reference。

添加一个FORMAT子句来指定ClickHouse的许多支持的输出格式之一:

SELECT *
FROM helloworld.my_first_table
ORDER BY timestamp
FORMAT TabSeparated

ClickHouse支持超过70种输入和输出格式，因此在数千种函数和所有数据格式之间，您可以使用ClickHouse执行一些令人印象深刻的快速类似etl的数据转换。事实上，你甚至不需要一个ClickHouse服务器来转换数据——你可以使用ClickHouse -local工具。有关详细信息，请查看clickhouse-local的文档页面。

4、更新和删除ClickHouse数据

虽然ClickHouse是面向大容量分析工作负载的，但在某些情况下，修改或删除现有数据是可能的。这些操作被标记为“mutations”，并使用ALTER TABLE命令执行。您还可以使用ClickHouse的轻量级删除功能DELETE 一行。

如果您需要执行频繁的更新，请考虑在ClickHouse中使用重复数据删除，它允许您在不生成突变事件的情况下更新和/或删除行。

更新数据

使用ALTER TABLE…UPDATE命令更新表中的行:

ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>

<expression>是满足<filter_expr>的列的新值。<expression>必须与列具有相同的数据类型，或者可以使用CAST操作符转换为相同的数据类型。<filter_expr>应该为每一行数据返回UInt8(零或非零)值。多个UPDATE <column>语句可以组合在一个以逗号分隔的ALTER TABLE命令中。

例子:
1）这样的一个mutation 允许使用字典查找将visitor_ids替换为新的:

ALTER TABLE website.clicks
UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id)
WHERE visit_date < '2022-01-01'

2）在一个命令中修改多个值比多个命令更有效:

ALTER TABLE website.clicks
UPDATE url = substring(url, position(url, '://') + 3), visitor_id = new_visit_id
WHERE visit_date < '2022-01-01'

3）对于分片表，可以在ON CLUSTER上执行mutation :

ALTER TABLE clicks ON CLUSTER main_cluster
UPDATE click_count = click_count / 2
WHERE visitor_id ILIKE '%robot%'

不能更新属于主键或排序键的列。

删除数据

使用ALTER TABLE命令删除行:

ALTER TABLE [<database>.]<table> DELETE WHERE <filter_expr>

<filter_expr>应该为每一行数据返回UInt8值。

例子:
1）删除任何列位于值数组中的记录:

ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277)

2）这个查询改变了什么?

ALTER TABLE clicks ON CLUSTER main_cluster DELETE WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573'

要删除表中的所有数据，使用TRUNCATE TABLE [<database].]<table>命令会更有效。该命令也可以在ON CLUSTER上执行。

查看DELETE语句文档页面了解更多细节。

轻量级的删除

删除行的另一个选择是使用DELETE FROM命令，这被称为轻量级删除。已删除的行被立即标记为删除，并将自动从所有后续查询中过滤出来，因此您不必等待部分合并或使用FINAL关键字。数据的清理在后台异步进行。

DELETE FROM [db.]table [ON CLUSTER cluster] [WHERE expr]

例如，下面的查询将删除hits表中Title列包含hello文本的所有行:

DELETE FROM hits WHERE Title LIKE '%hello%';

关于轻量级删除的一些注意事项:

• 此特性仅适用于MergeTree表引擎系列。
• 默认情况下，轻量级删除是异步的。将mutations_sync设置为1，等待一个副本处理语句，将mutations_sync设置为2，等待所有副本处理语句。
• 这个特性是实验性的，需要你将allow_experimental_lightweight_delete设置为true:

SET allow_experimental_lightweight_delete = true;

5、Joining Tables

ClickHouse完全支持连接，支持所有标准的SQL JOIN类型。语法应该看起来很熟悉，你可以在JOIN的docs页面查看所有细节:

SELECT
   *
FROM imdb.roles
  JOIN imdb.actors_dictionary
  ON imdb.roles.actor_id = imdb.actors_dictionary.id

ClickHouse还提供了额外的非标准SQL JOIN类型，用于分析工作负载和时间序列分析，包括ASOF连接。

ClickHouse有6种不同的连接执行算法，或者允许查询规划器在运行时根据资源可用性和使用情况自适应地选择和动态更改算法。

想要了解ClickHouse中有关join的详细信息，请务必查看我们关于join的系列博客。