如何在Debian9上安装和使用ClickHouse
作为 Write for DOnations 计划的一部分,作者选择了 Free and Open Source Fund 来接受捐赠。
介绍
ClickHouse 是一个开源的、面向列的分析数据库,由 Yandex 为 OLAP 和大数据用例创建。 ClickHouse 对实时查询处理的支持使其适用于需要亚秒级分析结果的应用程序。 ClickHouse 的查询语言是一种 SQL 方言,它支持强大的声明式查询功能,同时为最终用户提供熟悉度和更小的学习曲线。
面向列的 数据库将记录存储在按列而不是行分组的块中。 通过不为查询中不存在的列加载数据,面向列的数据库在完成查询时花费更少的时间读取数据。 因此,对于某些工作负载(例如 OLAP),这些数据库可以比传统的基于行的系统更快地计算和返回结果。
在线分析处理 (OLAP) 系统允许组织大量数据并执行复杂的查询。 它们能够管理 PB 级的数据并快速返回查询结果。 通过这种方式,OLAP 可用于数据科学和业务分析等领域的工作。
在本教程中,您将在您的机器上安装 ClickHouse 数据库服务器和客户端。 您将使用 DBMS 执行典型任务,并可选择启用来自另一台服务器的远程访问,以便您能够从另一台机器连接到数据库。 然后,您将通过建模和查询示例网站访问数据来测试 ClickHouse。
先决条件
- 一台带有
sudo
的 Debian 9 服务器启用了非 root 用户和防火墙设置。 您可以按照初始服务器设置教程来创建用户并设置防火墙。 - (可选)具有
sudo
的辅助 Debian 9 服务器启用了非 root 用户和防火墙设置。 您可以按照初始服务器设置教程。
第 1 步 — 安装 ClickHouse
在本节中,您将使用 apt-get
安装 ClickHouse 服务器和客户端程序。
首先,通过运行 SSH 进入您的服务器:
ssh sammy@your_server_ip
dirmngr
是用于管理证书和密钥的服务器。 添加和验证远程存储库密钥需要它,通过运行安装它:
sudo apt-get install -y dirmngr
Yandex 维护一个 APT 存储库,其中包含最新版本的 ClickHouse。 添加存储库的 GPG 密钥,以便您能够安全地下载经过验证的 ClickHouse 包:
sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4
您将看到类似于以下内容的输出:
Output Executing: /tmp/apt-key-gpghome.JkkcKnBAFY/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4 gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <milovidov@yandex-team.ru>" imported gpg: Total number processed: 1 gpg: imported: 1
输出确认它已成功验证并添加了密钥。
通过执行以下命令将存储库添加到您的 APT 存储库列表:
echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list
在这里,您已将 echo
的输出通过管道传输到 sudo tee
,以便此输出可以打印到 root 拥有的文件。
现在,运行 apt-get update
来更新你的包:
sudo apt-get update
clickhouse-server
和 clickhouse-client
软件包现在可供安装。 安装它们:
sudo apt-get install -y clickhouse-server clickhouse-client
您已成功安装 ClickHouse 服务器和客户端。 您现在已准备好启动数据库服务并确保其正常运行。
第 2 步 — 启动服务
您在上一节中安装的 clickhouse-server
包创建了一个 systemd
服务,该服务执行启动、停止和重新启动数据库服务器等操作。 systemd是Linux的init系统,用于初始化和管理服务。 在本节中,您将启动服务并验证它是否成功运行。
通过运行以下命令启动 clickhouse-server
服务:
sudo service clickhouse-server start
前面的命令不会显示任何输出。 要验证服务是否成功运行,请执行:
sudo service clickhouse-server status
您将看到类似于以下内容的输出:
Output ● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data) Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled) Active: active (running) since Sat 2018-12-22 07:23:20 UTC; 1h 9min ago Main PID: 27101 (ClickHouse-serv) Tasks: 34 (limit: 1152) CGroup: /system.slice/ClickHouse-server.service └─27101 /usr/bin/ClickHouse-server --config=/etc/ClickHouse-server/config.xml
输出表示服务器正在运行。
您已成功启动 ClickHouse 服务器,现在可以使用 clickhouse-client
CLI 程序连接到服务器。
第三步——创建数据库和表
在 ClickHouse 中,您可以通过在交互式数据库提示中直接执行 SQL 语句来创建和删除数据库。 语句由遵循特定语法的命令组成,这些命令告诉数据库服务器执行请求的操作以及所需的任何数据。 您可以使用 CREATE DATABASE table_name
语法创建数据库。 要创建数据库,首先通过运行以下命令启动客户端会话:
clickhouse-client
此命令将使您登录到客户端提示符,您可以在其中运行 ClickHouse SQL 语句来执行以下操作:
- 创建、更新和删除数据库、表、索引、分区和视图。
- 执行查询以检索使用各种条件选择性过滤和分组的数据。
在这一步中,随着 ClickHouse 客户端准备好插入数据,您将创建一个数据库和表。 出于本教程的目的,您将创建一个名为 test
的数据库,并在其中创建一个名为 visits
的表,用于跟踪网站访问持续时间。
现在您在 ClickHouse 命令提示符中,通过执行以下命令创建您的 test
数据库:
CREATE DATABASE test;
您将看到以下输出,表明您已创建数据库:
Output CREATE DATABASE test Ok. 0 rows in set. Elapsed: 0.003 sec.
ClickHouse 表类似于其他关系数据库中的表; 它以结构化格式保存相关数据的集合。 您可以指定列及其类型、添加数据行以及对表执行不同类型的查询。
在 ClickHouse 中创建表的语法遵循以下示例结构:
CREATE TABLE table_name ( column_name1 column_type [options], column_name2 column_type [options], ... ) ENGINE = engine
table_name
和 column_name
值可以是任何有效的 ASCII 标识符。 ClickHouse 支持多种列类型; 一些最受欢迎的是:
UInt64
:用于存储 0 到 18446744073709551615 范围内的整数值。Float64
:用于存储2039.23、10.5等浮点数。String
:用于存储变长字符。 它不需要最大长度属性,因为它可以存储任意长度。Date
:用于存储遵循YYYY-MM-DD
格式的日期。DateTime
:用于存储日期和时间,格式为YYYY-MM-DD HH:MM:SS
。
在列定义之后,您指定用于表的引擎。 在 ClickHouse 中,Engines 决定了底层数据的物理结构、表的查询能力、并发访问模式以及对索引的支持。 不同的发动机类型适用于不同的应用要求。 最常用和应用最广的发动机型号是MergeTree
。
现在您已经了解了表的创建,您将创建一个表。 首先确认您将要修改的数据库:
USE test;
您将看到以下输出,表明您已从 default
数据库切换到 test
数据库:
Output USE test Ok. 0 rows in set. Elapsed: 0.001 sec.
本指南的其余部分将假定您正在此数据库的上下文中执行语句。
通过运行以下命令创建您的 visits
表:
CREATE TABLE visits ( id UInt64, duration Float64, url String, created DateTime ) ENGINE = MergeTree() PRIMARY KEY id ORDER BY id;
以下是该命令的作用的细分。 您创建一个名为 visits
的表,它有四列:
id
:主键列。 与其他 RDBMS 系统类似,ClickHouse 中的主键列唯一标识一行; 每一行都应该有该列的唯一值。duration
:浮点列,用于存储每次访问的持续时间(以秒为单位)。float
列可以存储十进制值,例如 12.50。url
:存储访问过的URL的字符串列,如http://example.com
。created
:跟踪访问发生时间的日期和时间列。
在列定义之后,您将 MergeTree
指定为表的存储引擎。 MergeTree 系列 引擎推荐用于生产数据库,因为它优化了对大型实时插入的支持、整体稳健性和查询支持。 此外,MergeTree 引擎支持按主键对行进行排序、对行进行分区以及复制和采样数据。
如果您打算使用 ClickHouse 来归档不经常查询的数据或存储临时数据,您可以使用引擎的 Log 系列 来针对该用例进行优化。
在列定义之后,您将定义其他表级选项。 PRIMARY KEY
子句将 id
设置为主键列,ORDER BY
子句将存储按 id
列排序的值。 主键唯一标识一行,用于高效访问单行和高效的行托管。
在执行 create 语句时,您将看到以下输出:
OutputCREATE TABLE visits ( id UInt64, duration Float64, url String, created DateTime ) ENGINE = MergeTree() PRIMARY KEY id ORDER BY id Ok. 0 rows in set. Elapsed: 0.010 sec.
在本节中,您创建了一个数据库和一个表来跟踪网站访问数据。 在下一步中,您将向表中插入数据、更新现有数据并删除该数据。
第 4 步 — 插入、更新和删除数据和列
在此步骤中,您将使用 visits
表来插入、更新和删除数据。 以下命令是将行插入 ClickHouse 表的语法示例:
INSERT INTO table_name VALUES (column_1_value, column_2_value, ....);
现在,通过运行以下每个语句,将几行示例网站访问数据插入 visits
表:
INSERT INTO visits VALUES (1, 10.5, 'http://example.com', '2019-01-01 00:01:01');
INSERT INTO visits VALUES (2, 40.2, 'http://example1.com', '2019-01-03 10:01:01');
INSERT INTO visits VALUES (3, 13, 'http://example2.com', '2019-01-03 12:01:01');
INSERT INTO visits VALUES (4, 2, 'http://example3.com', '2019-01-04 02:01:01');
您将看到针对每个插入语句重复的以下输出。
Output INSERT INTO visits VALUES Ok. 1 rows in set. Elapsed: 0.004 sec.
每行的输出显示您已成功将其插入 visits
表中。
现在,您将在 visits
表中添加一个附加列。 在现有表中添加或删除列时,ClickHouse 支持 ALTER
语法。
例如,向表中添加列的基本语法如下:
ALTER TABLE table_name ADD COLUMN column_name column_type;
添加一个名为 location
的列,该列将通过运行以下语句存储访问网站的位置:
ALTER TABLE visits ADD COLUMN location String;
您将看到类似于以下内容的输出:
OutputALTER TABLE visits ADD COLUMN location String Ok. 0 rows in set. Elapsed: 0.014 sec.
输出显示您已成功添加 location
列。
从 19.3.6 版本开始,由于实施限制,ClickHouse 不支持更新和删除单个数据行。 但是,ClickHouse 支持批量更新和删除,并且为这些操作提供了独特的 SQL 语法,以突出它们的非标准用法。
以下语法是批量更新行的示例:
ALTER TABLE table_name UPDATE column_1 = value_1, column_2 = value_2 ... WHERE filter_conditions;
您将运行以下语句来更新 duration
小于 15 的所有行的 url
列。 输入到数据库提示符中执行:
ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15;
批量更新语句的输出如下:
Output ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15 Ok. 0 rows in set. Elapsed: 0.003 sec.
输出显示您的更新查询已成功完成。 输出中的 0 rows in set
表示查询没有返回任何行; 这将适用于任何更新和删除查询。
批量删除行的示例语法类似于更新行,并具有以下结构:
ALTER TABLE table_name DELETE WHERE filter_conditions;
要测试删除数据,请运行以下语句以删除所有 duration
小于 5 的行:
ALTER TABLE visits DELETE WHERE duration < 5;
批量删除语句的输出将类似于:
Output ALTER TABLE visits DELETE WHERE duration < 5 Ok. 0 rows in set. Elapsed: 0.003 sec.
输出确认您已删除持续时间少于五秒的行。
要从表中删除列,语法将遵循以下示例结构:
ALTER TABLE table_name DROP COLUMN column_name;
通过运行以下命令删除您之前添加的 location
列:
ALTER TABLE visits DROP COLUMN location;
确认您已删除该列的 DROP COLUMN
输出将如下所示:
OutputALTER TABLE visits DROP COLUMN location String Ok. 0 rows in set. Elapsed: 0.010 sec.
现在您已经成功地在 visits
表中插入、更新和删除了行和列,您将继续在下一步中查询数据。
第 5 步 — 查询数据
ClickHouse 的查询语言是 SQL 的一种自定义方言,具有适用于分析工作负载的扩展和功能。 在此步骤中,您将运行选择和聚合查询以从 visits
表中检索数据和结果。
选择查询允许您检索按您指定的条件过滤的数据行和列,以及要返回的行数等选项。 您可以使用 SELECT
语法选择数据的行和列。 SELECT
查询的基本语法是:
SELECT func_1(column_1), func_2(column_2) FROM table_name WHERE filter_conditions row_options;
执行以下语句以检索 url
为 http://example.com
的行的 url
和 duration
值。
SELECT url, duration FROM visits WHERE url = 'http://example2.com' LIMIT 2;
您将看到以下输出:
OutputSELECT url, duration FROM visits WHERE url = 'http://example2.com' LIMIT 2 ┌─url─────────────────┬─duration─┐ │ http://example2.com │ 10.5 │ └─────────────────────┴──────────┘ ┌─url─────────────────┬─duration─┐ │ http://example2.com │ 13 │ └─────────────────────┴──────────┘ 2 rows in set. Elapsed: 0.013 sec.
输出返回了与您指定的条件匹配的两行。 现在您已经选择了值,您可以开始执行聚合查询。
聚合查询是对一组值进行操作并返回单个输出值的查询。 在分析数据库中,这些查询经常运行,并且由数据库进行了很好的优化。 ClickHouse 支持的一些聚合函数有:
count
:返回符合指定条件的行数。sum
:返回所选列值的总和。avg
:返回所选列值的平均值。
一些 ClickHouse 特定的聚合函数包括:
uniq
:返回匹配的不同行的近似数量。topK
:使用近似算法返回特定列的最频繁值的数组。
为了演示聚合查询的执行,您将通过运行 sum
查询来计算访问的总持续时间:
SELECT SUM(duration) FROM visits;
您将看到类似于以下内容的输出:
Output SELECT SUM(duration) FROM visits ┌─SUM(duration)─┐ │ 63.7 │ └───────────────┘ 1 rows in set. Elapsed: 0.010 sec.
现在,通过执行计算前两个 URL:
SELECT topK(2)(url) FROM visits;
您将看到类似于以下内容的输出:
OutputSELECT topK(2)(url) FROM visits ┌─topK(2)(url)──────────────────────────────────┐ │ ['http://example2.com','http://example1.com'] │ └───────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.010 sec.
现在您已成功查询 visits
表,您将在下一步中删除表和数据库。
第 6 步 — 删除表和数据库
在本节中,您将删除 visits
表和 test
数据库。
删除表的语法如下:
DROP TABLE table_name;
要删除 visits
表,请运行以下语句:
DROP TABLE visits;
您将看到以下输出,表明您已成功删除该表:
outputDROP TABLE visits Ok. 0 rows in set. Elapsed: 0.005 sec.
您可以使用 DROP database table_name
语法删除数据库。 要删除 test
数据库,请执行以下语句:
DROP DATABASE test;
结果输出显示您已成功删除数据库。
Output DROP DATABASE test Ok. 0 rows in set. Elapsed: 0.003 sec.
您已在此步骤中删除了表和数据库。 现在您已经在 ClickHouse 实例中创建、更新和删除了数据库、表和数据,您将在下一部分中启用对数据库服务器的远程访问。
第 7 步 — 设置防火墙规则(可选)
如果您打算仅在本地将 ClickHouse 与在同一服务器上运行的应用程序一起使用,或者您的服务器上未启用防火墙,则无需完成此部分。 相反,如果您将远程连接到 ClickHouse 数据库服务器,则应执行此步骤。
当前,您的服务器启用了防火墙,该防火墙会禁用您的公共 IP 地址访问所有端口。 您将完成以下两个步骤以允许远程访问:
- 修改 ClickHouse 的配置,让它监听所有接口。
- 添加防火墙规则,允许传入连接到端口
8123
,这是 ClickHouse 服务器运行的 HTTP 端口。
如果您在数据库提示符内,请键入 CTRL+D
退出它。
通过执行以下命令编辑配置文件:
sudo nano /etc/clickhouse-server/config.xml
然后取消注释包含 <!-- <listen_host>0.0.0.0</listen_host> -->
的行,如以下文件:
/etc/clickhouse-server/config.xml
... <interserver_http_host>example.yandex.ru</interserver_http_host> --> <!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. --> <!-- <listen_host>::</listen_host> --> <!-- Same for hosts with disabled ipv6: --> <listen_host>0.0.0.0</listen_host> <!-- Default values - try listen localhost on ipv4 and ipv6: --> <!-- <listen_host>::1</listen_host> <listen_host>127.0.0.1</listen_host> --> ...
保存文件并退出nano
。 要应用新配置,请运行以下命令重新启动服务:
sudo service clickhouse-server restart
您不会看到此命令的任何输出。 ClickHouse 的服务器在端口 8123
上侦听 HTTP 连接,在端口 9000
上侦听来自 clickhouse-client
的连接。 使用以下命令允许访问第二个服务器的 IP 地址的两个端口:
sudo ufw allow from second_server_ip/32 to any port 8123
sudo ufw allow from second_server_ip/32 to any port 9000
您将看到两个命令的以下输出,表明您已启用对两个端口的访问:
OutputRule added
现在可以从您添加的 IP 访问 ClickHouse。 如果需要,请随意添加其他 IP,例如本地计算机的地址。
要验证您是否可以从远程计算机连接到 ClickHouse 服务器,请首先在第二台服务器上按照本教程步骤 1 中的步骤操作,并确保在其上安装了 clickhouse-client
。
现在您已经登录到第二台服务器,通过执行以下命令启动客户端会话:
clickhouse-client --host your_server_ip
您将看到以下输出,表明您已成功连接到服务器:
OutputClickHouse client version 19.3.6. Connecting to your_server_ip:9000 as user default. Connected to ClickHouse server version 19.3.6 revision 54415. hostname :)
在此步骤中,您已通过调整防火墙规则启用对 ClickHouse 数据库服务器的远程访问。
结论
您已在服务器上成功设置 ClickHouse 数据库实例并创建数据库和表、添加数据、执行查询和删除数据库。 在 ClickHouse 的文档中,您可以阅读他们针对其他开源和商业分析数据库的 基准测试 和一般参考 文档 。 其他 功能 ClickHouse 提供的包括跨多个服务器的分布式查询处理,通过将数据存储在不同的 分片 上来提高性能并防止数据丢失。