如何在 Ubuntu 18.04 上安装和使用 ClickHouse

作者选择了自由和开源基金作为写给捐款计划的一部分接受捐款。

介绍

ClickHouse是由 Yandex为 OLAP和大数据使用案例创建的开源,面向列的分析数据库。ClickHouse对实时查询处理的支持使其适用于需要次数分析结果的应用程序。

以列为导向的数据库将记录存储在列而不是行组合的块中. 通过不加载查询中不存在的列的数据,以列为导向的数据库在完成查询时花费更少的时间阅读数据。

Online Analytics Processing (OLAP) 系统允许组织大量数据并执行复杂查询. 它们能够快速管理数据,并返回查询结果。

在本教程中,您将安装ClickHouse数据库服务器和客户端在您的机器上. 您将使用DBMS进行典型的任务,并可选地从另一个服务器启用远程访问,以便您能够从另一个机器连接到数据库。

前提条件

您可以遵循初始服务器设置教程(https://andsky.com/tech/tutorials/initial-server-setup-with-ubuntu-18-04)来创建用户并设置防火墙。

(可选) 附属的 Ubuntu 18.04 服务器具有sudo启用的非根用户和防火墙设置。

步骤 1 – 安装 ClickHouse

在本节中,您将使用apt-get安装 ClickHouse 服务器和客户端程序。

首先,通过运行 SSH 到您的服务器:

1[environment local]
2ssh sammy@your_server_ip

Yandex 维护具有 ClickHouse 最新版本的 APT 存储库. 添加存储库的 GPG 密钥,以便您能够安全地下载验证的 ClickHouse 包:

1sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4

您将看到类似于以下的输出:

1[secondary_label Output]
2
3Executing: /tmp/apt-key-gpghome.JkkcKnBAFY/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4
4
5gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <[email protected]>" imported
6gpg: Total number processed: 1
7gpg:               imported: 1

输出证实它已成功验证并添加了密钥。

将存储库添加到您的 APT 存储库列表,执行:

1echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list

在这里,您已将echo的输出管道到sudo tee,以便这个输出可以打印到根所有权的文件。

现在,运行apt-get update来更新您的软件包:

1sudo apt-get update

clickhouse-server 和 clickhouse-client 套件现在可供安装。

1sudo apt-get install -y clickhouse-server clickhouse-client

您已经成功安装了 ClickHouse 服务器和客户端,您现在已经准备好启动数据库服务,并确保它正确运行。

步骤2 - 开始服务

您在上一节安装的clickhouse-server包创建了一个systemd服务,它执行数据库服务器的启动、停止和重新启动等操作。

启动clickhouse-server服务,运行:

1sudo service clickhouse-server start

之前的命令不会显示任何输出. 若要验证该服务是否成功运行,请执行:

1sudo service clickhouse-server status

您将看到类似于以下的输出:

1[secondary_label Output]
2
3● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data)
4   Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled)
5   Active: active (running) since Sat 2018-12-22 07:23:20 UTC; 1h 9min ago
6 Main PID: 27101 (ClickHouse-serv)
7    Tasks: 34 (limit: 1152)
8   CGroup: /system.slice/ClickHouse-server.service
9           └─27101 /usr/bin/ClickHouse-server --config=/etc/ClickHouse-server/config.xml

输出表示服务器正在运行。

您已经成功启动了 ClickHouse 服务器,现在可以使用clickhouse-client CLI 程序连接到服务器。

步骤 3 – 创建数据库和表

在 ClickHouse 中,您可以通过直接在交互式数据库提示程序中执行 SQL 语句来创建和删除数据库. 语句由遵循特定语法的命令组成,该命令告诉数据库服务器执行所要求的操作以及所需的任何数据。

1clickhouse-client

此命令会将您登录到客户端提示,在那里您可以运行 ClickHouse SQL 语句以执行如下操作:

创建、更新和删除数据库、表、索引、分区和视图。
执行查询以获取可选地过滤和组合使用各种条件的数据。

在此步骤中,随着ClickHouse客户端准备输入数据,您将创建一个数据库和表. 为此教程的目的,您将创建一个名为测试的数据库,并在其内部,您将创建一个名为访问的表,该表跟踪网站访问的持续时间。

现在你在ClickHouse命令提示中,通过执行创建你的测试数据库:

1CREATE DATABASE test;

您将看到以下输出显示您已创建数据库:

1[secondary_label Output]
2
3CREATE DATABASE test
4
5Ok.
6
70 rows in set. Elapsed: 0.003 sec.

ClickHouse 表类似于其他关系数据库中的表;它具有结构化格式的相关数据的集合,您可以指定列及其类型,添加数据行,并在表中执行不同类型的查询。

在 ClickHouse 中创建表的语法遵循此示例结构:

1CREATE TABLE table_name
2(
3    column_name1 column_type [options],
4    column_name2 column_type [options],
5    ...
6) ENGINE = engine

table_name 和 column_name 值可以是任何有效的 ASCII 标识符. ClickHouse 支持广泛的列类型,其中一些最受欢迎的类型是:

UInt64:用于存储 0 到 18446744073709551615 范围内的整数值。
Float64:用于存储浮点数,如 2039.23, 10.5 等。
String:用于存储变长字符。它不需要最大长度属性,因为它可以存储任意长度。
Date:用于存储遵循 YYYYY-MM-DD 格式的日期。

在 ClickHouse 中, Engines 决定了底层数据的物理结构、表的查询能力、同时访问模式以及对索引的支持。

现在你有创建表的概述,你将创建一个表. 开始通过确认你正在修改的数据库:

1USE test;

您将看到以下输出显示您已从默认数据库切换到测试数据库:

1[secondary_label Output]
2
3USE test
4
5Ok.
6
70 rows in set. Elapsed: 0.001 sec.

本指南的其余部分将假定您正在在该数据库的背景下执行陈述。

通过运行此命令创建您的访问表:

1CREATE TABLE visits (
2 id UInt64,
3 duration Float64,
4 url String,
5 created DateTime
6) ENGINE = MergeTree() 
7PRIMARY KEY id 
8ORDER BY id;

你创建了一个名为访问的表,它有四个列:

类似于其他 RDBMS 系统,ClickHouse 中的主要密钥列可以独特地识别一个行;每个行都应该为该列具有独特的值。

持续时间:用于存储每次访问的持续时间的浮动列。 float 列可以存储 12.50 等十进制值。

列定义后,您将MergeTree指定为表的存储引擎。 MergeTree 家族引擎被推荐用于生产数据库,因为它对大型实时插入,整体强度和查询支持的优化支持。

如果您打算使用 ClickHouse 来存档不经常查询的数据或存储临时数据,您可以使用日志家族的引擎来优化此用例。

列定义后,您将定义其他表级选项。PRIMARY KEY条款将id设置为主要钥匙列,而ORDER BY条款将存储由id列排序的值。

在执行创建语句时,您将看到以下输出:

 1[secondary_label Output]
 2CREATE TABLE visits
 3(
 4    id UInt64,
 5    duration Float64,
 6    url String,
 7    created DateTime
 8)
 9ENGINE = MergeTree()
10PRIMARY KEY id
11ORDER BY id
12
13Ok.
14
150 rows in set. Elapsed: 0.010 sec.

在本节中,您创建了一个数据库和一个表来跟踪网站访问数据. 在下一步,您将将数据插入表中,更新现有数据,并删除这些数据。

步骤 4 – 插入、更新和删除数据和列

在此步骤中,您将使用访问表来插入、更新和删除数据. 以下命令是插入行到 ClickHouse 表的语法的例子:

1INSERT INTO table_name VALUES (column_1_value, column_2_value, ....);

现在,在您的访问表中插入几个示例网站访问数据行,运行下列每一个陈述:

1INSERT INTO visits VALUES (1, 10.5, 'http://example.com', '2019-01-01 00:01:01');

1INSERT INTO visits VALUES (2, 40.2, 'http://example1.com', '2019-01-03 10:01:01');

1INSERT INTO visits VALUES (3, 13, 'http://example2.com', '2019-01-03 12:01:01');

1INSERT INTO visits VALUES (4, 2, 'http://example3.com', '2019-01-04 02:01:01');

您将看到每一个插入语句重复的下面的输出。

1[secondary_label Output]
2
3INSERT INTO visits VALUES
4
5Ok.
6
71 rows in set. Elapsed: 0.004 sec.

每个行的输出显示您已成功将其插入到访问表中。

现在您将添加一个额外的列到访问表. 当从现有表中添加或删除列时,ClickHouse 支持ALTER语法。

例如,将列添加到表的基本语法如下:

1ALTER TABLE table_name ADD COLUMN column_name column_type;

添加一个名为位置的列,通过运行以下语句来存储访问网站的位置:

1ALTER TABLE visits ADD COLUMN location String;

您将看到类似于以下的输出:

1[secondary_label Output]
2ALTER TABLE visits
3    ADD COLUMN
4    location String
5
6Ok.
7
80 rows in set. Elapsed: 0.014 sec.

输出显示您已成功添加位置列。

从版本 19.3.6 开始,ClickHouse 不会因为实施限制而支持更新和删除单个数据行,但是,ClickHouse 支持大量更新和删除,并为这些操作提供了不同的 SQL 语法,以突出其非标准使用。

以下语法是大量更新行的一个例子:

1ALTER TABLE table_name UPDATE column_1 = value_1, column_2 = value_2 ... WHERE filter_conditions;

您将运行以下语句来更新所有具有持续时间小于 15 的行的url列,将其输入到执行的数据库提示:

1ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15;

大量更新声明的输出将如下:

1[secondary_label Output]
2
3ALTER TABLE visits
4    UPDATE url = 'http://example2.com' WHERE duration < 15
5
6Ok.
7
80 rows in set. Elapsed: 0.003 sec.

输出显示您的更新查询已成功完成. 输出中的0行在集合中表示查询未返回任何行;这将适用于任何更新和删除查询。

大量删除行示例语法类似于更新行,并且具有以下结构:

1ALTER TABLE table_name DELETE WHERE filter_conditions;

若要测试删除数据,请运行以下语句,以删除所有具有持续时间小于 5 的行:

1ALTER TABLE visits DELETE WHERE duration < 5;

大量删除声明的输出将类似于:

1[secondary_label Output]
2
3ALTER TABLE visits
4    DELETE WHERE duration < 5
5
6Ok.
7
80 rows in set. Elapsed: 0.003 sec.

输出确认您已删除持续时间不到五秒的行。

若要从表中删除列,语法将遵循以下示例结构:

1ALTER TABLE table_name DROP COLUMN column_name;

删除您之前添加的位置列,运行以下操作:

1ALTER TABLE visits DROP COLUMN location;

确认您已删除列的DOP COLUMN输出将如下:

1[secondary_label Output]
2ALTER TABLE visits
3    DROP COLUMN
4    location String
5
6Ok.
7
80 rows in set. Elapsed: 0.010 sec.

现在,您已在访问表中成功插入、更新和删除行和列,您将在下一步继续查询数据。

步骤五:查询数据

ClickHouse 的查询语言是 SQL 的自定义方言,具有适用于分析工作负载的扩展和函数. 在此步骤中,您将运行选择和汇总查询,从访问表中获取数据和结果。

选择查询允许您检索由您指定的条件过滤的数据行和列,以及返回的行数等选项,您可以使用SELECT语法选择数据行和列。

1SELECT func_1(column_1), func_2(column_2) FROM table_name WHERE filter_conditions row_options;

运行以下语句以获取url和持续时间值,其中url为http://example.com。

1SELECT url, duration FROM visits WHERE url = 'http://example2.com' LIMIT 2;

您将看到以下结果:

 1[secondary_label Output]
 2SELECT
 3    url,
 4    duration
 5FROM visits
 6WHERE url = 'http://example2.com'
 7LIMIT 2
 8
 9┌─url─────────────────┬─duration─┐
10│ http://example2.com │     10.5 │
11└─────────────────────┴──────────┘
12┌─url─────────────────┬─duration─┐
13│ http://example2.com │       13 │
14└─────────────────────┴──────────┘
15
162 rows in set. Elapsed: 0.013 sec.

输出返回了与您指定的条件相匹配的两个行. 现在您已经选择了值,您可以转到执行聚合查询。

聚合查询是对一组值运作并返回单个输出值的查询。在分析数据库中,这些查询经常运行,并且由数据库优化得很好。

数:返回符合指定的条件的行数。
总:返回所选列值的总和。
avg:返回所选列值的平均值。

一些 ClickHouse 特定的聚合函数包括:

uniq:返回相匹配的差不多不同的行。
topK:使用近似算法返回特定列中最常见值的数组。

要演示汇总查询的执行情况,您将通过运行总查询来计算访问的总持续时间:

1SELECT SUM(duration) FROM visits;

您将看到类似于以下的输出:

 1[secondary_label Output]
 2
 3SELECT SUM(duration)
 4FROM visits
 5
 6┌─SUM(duration)─┐
 7│          63.7 │
 8└───────────────┘
 9
101 rows in set. Elapsed: 0.010 sec.

现在,通过执行计算前两个URL:

1SELECT topK(2)(url) FROM visits;

您将看到类似于以下的输出:

1[secondary_label Output]
2SELECT topK(2)(url)
3FROM visits
4
5┌─topK(2)(url)──────────────────────────────────┐
6│ ['http://example2.com','http://example1.com'] │
7└───────────────────────────────────────────────┘
8
91 rows in set. Elapsed: 0.010 sec.

现在您已成功查询您的访问表,您将在下一步中删除表和数据库。

步骤 6 – 删除表和数据库

在本节中,您将删除您的访问表和测试数据库。

删除表的语法遵循此示例:

1DROP TABLE table_name;

要删除访问表,运行以下语句:

1DROP TABLE visits;

您将看到以下输出声明您已成功删除表:

1[secondary_label output]
2DROP TABLE visits
3
4Ok.
5
60 rows in set. Elapsed: 0.005 sec.

您可以使用「DROP 数据库 table_name」语法删除数据库. 若要删除「test」数据库,请执行以下命令:

1DROP DATABASE test;

结果输出显示您已成功删除数据库。

1[secondary_label Output]
2
3DROP DATABASE test
4
5Ok.
6
70 rows in set. Elapsed: 0.003 sec.

您已在此步骤中删除表和数据库,现在您已在 ClickHouse 实例中创建、更新和删除数据库、表和数据,您将在下一节中启用远程访问数据库服务器。

步骤 7 – 设置防火墙规则(可选)

如果您打算在同一服务器上运行的应用程序仅在本地使用 ClickHouse,或者在服务器上没有启用防火墙,则不需要完成此部分。

目前,您的服务器已启用了防火墙,该防火墙阻止您的公共IP地址访问所有端口,您将完成以下两步以允许远程访问:

更改 ClickHouse 的配置,并允许它在所有接口上聆听。
添加一个防火墙规则,允许输入连接到端口 8123,这是 ClickHouse 服务器运行的 HTTP 端口。

如果您在数据库提示中,请通过键入CTRL+D来退出数据库。

通过执行编辑配置文件:

1sudo nano /etc/clickhouse-server/config.xml

然后删除包含  的行,如下文件:

 1[label /etc/clickhouse-server/config.xml]
 2
 3...
 4 <interserver_http_host>example.yandex.ru</interserver_http_host>
 5    -->
 6
 7    <!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. -->
 8    <!-- <listen_host>::</listen_host> -->
 9    <!-- Same for hosts with disabled ipv6: -->
10    <listen_host>0.0.0.0</listen_host>
11
12    <!-- Default values - try listen localhost on ipv4 and ipv6: -->
13    <!--
14    <listen_host>::1</listen_host>
15    <listen_host>127.0.0.1</listen_host>
16    -->
17...

保存文件并输出nano。若要应用新的配置,请通过运行重新启动服务:

1sudo service clickhouse-server restart

您不会看到此命令的任何输出。ClickHouse 的服务器在端口8123上收听 HTTP 连接和端口9000上收听来自clickhouse-client的连接。

1sudo ufw allow from second_server_ip/32 to any port 8123

1sudo ufw allow from second_server_ip/32 to any port 9000

对于两个命令,您将看到以下输出,表明您已启用对两个端口的访问:

1[secondary_label Output]
2Rule added

ClickHouse 现在可以从您添加的 IP 访问,如果需要,可以添加额外的 IP 地址,例如本地机器的地址。

要验证您可以从远程机器连接到 ClickHouse 服务器,请先按照本教程第 1 步的步骤在第二个服务器上进行连接,并确保您安装了clickhouse-client。

现在您已登录到第二个服务器,请通过执行下列操作开始客户端会话:

1[environment second]
2clickhouse-client --host your_server_ip

您将看到以下输出显示您已成功连接到服务器:

1[environment second]
2[secondary_label Output]
3ClickHouse client version 19.3.6.
4Connecting to your_server_ip:9000 as user default.
5Connected to ClickHouse server version 19.3.6 revision 54415.
6
7hostname :)

在此步骤中,您已通过调整防火墙规则来启用远程访问您的 ClickHouse 数据库服务器。

结论

您已成功在您的服务器上设置了 ClickHouse 数据库实例,创建了数据库和表,添加了数据,执行了查询,并删除了数据库。在 ClickHouse 的文档中,您可以阅读他们的基准与其他开源和商业分析数据库和一般参考文档相比。