作者选择了自由和开源基金作为Write for DOnations计划的一部分进行捐赠。
介绍
ClickHouse是一个开源的,面向列的分析数据库,由Yandex为OLAP和大数据用例创建。 ClickHouse对实时查询处理的支持使其适用于需要亚秒级分析结果的应用程序。 ClickHouse的查询语言是SQL的一种方言,它支持强大的声明性查询功能,同时为最终用户提供熟悉度和较小的学习曲线。
面向列的数据库将记录存储在按列而不是行分组的块中。 通过不加载查询中不存在的列的数据,面向列的数据库在完成查询时花费的时间更少。 因此,对于某些工作负载(如OLAP),这些数据库可以比传统的基于行的系统更快地计算和返回结果。
在线分析处理 (OLAP)系统允许组织大量数据并执行复杂查询。 它们能够管理数PB的数据并快速返回查询结果。 通过这种方式,OLAP对于数据科学和业务分析等领域的工作非常有用。
在本教程中,您将在计算机上安装ClickHouse数据库服务器和客户端。 您将使用DBMS执行典型任务,并可选择启用另一台服务器的远程访问,以便您能够从另一台计算机连接到数据库。 然后,您将通过建模和查询示例网站访问数据来测试ClickHouse。
先决条件
- 一台带有
sudo
CentOS 7服务器启用了非root用户和防火墙设置。 您可以按照初始服务器设置教程创建用户和本教程来设置防火墙。 - (可选)具有
sudo
启用的非root用户和防火墙设置的辅助CentOS 7服务器。 您可以按照初始服务器设置教程和防火墙的其他安装教程进行操作。
第1步 – 安装ClickHouse
在本节中,您将使用yum
安装ClickHouse服务器和客户端程序。
首先,通过运行SSH进入您的服务器:
ssh sammy@your_server_ip
通过执行以下命令安装基本依赖项:
sudo yum install -y pygpgme yum-utils
pygpgme
包用于添加和验证GPG签名,而yum-utils
允许轻松管理源RPM。
Altinity是一家ClickHouse咨询公司,拥有一个拥有最新版ClickHouse的YUM存储库。 您将通过创建文件来添加存储库的详细信息以安全地下载经过验证的ClickHouse包。 要检查包内容,可以在此Github项目中检查它们的构建源。
通过执行以下命令创建存储库详细信
sudo vi /etc/yum.repos.d/altinity_clickhouse.repo
接下来,将以下内容添加到文件中:
[altinity_clickhouse]name=altinity_clickhousebaseurl=https://packagecloud.io/altinity/clickhouse/el/7/$basearchrepo_gpgcheck=1gpgcheck=0enabled=1gpgkey=https://packagecloud.io/altinity/clickhouse/gpgkeysslverify=1sslcacert=/etc/pki/tls/certs/ca-bundle.crtmetadata_expire=300[altinity_clickhouse-source]name=altinity_clickhouse-sourcebaseurl=https://packagecloud.io/altinity/clickhouse/el/7/SRPMSrepo_gpgcheck=1gpgcheck=0enabled=1gpgkey=https://packagecloud.io/altinity/clickhouse/gpgkeysslverify=1sslcacert=/etc/pki/tls/certs/ca-bundle.crtmetadata_expire=300
现在您已添加了存储库,请使用以下命令启用它们:
sudo yum -q makecache -y --disablerepo='*' --enablerepo='altinity_clickhouse'
-q
标志告诉命令以安静模式运行。 makecache
命令使--enablerepo
标志中指定的包可用。
执行时,您将看到类似于以下内容的输出:
Importing GPG key 0x0F6E36F6: Userid : "https://packagecloud.io/altinity/clickhouse (https://packagecloud.io/docs#gpg_signing) <[email protected]>" Fingerprint: 7001 38a9 6a20 6b22 bf28 3c06 ed26 58f3 0f6e 36f6 From : https://packagecloud.io/altinity/clickhouse/gpgkey
输出确认已成功验证并添加了GPG密钥。
clickhouse-server
和clickhouse-client
软件包现在可以安装了。 安装它们:
sudo yum install -y clickhouse-server clickhouse-client
您已成功安装ClickHouse服务器和客户端。 您现在已准备好启动数据库服务并确保它正常运行。
第2步 – 启动服务
您在上一节中安装的clickhouse-server
软件包创建了一个systemd
服务,该服务执行启动,停止和重新启动数据库服务器等操作。 systemd
是Linux用于初始化和管理服务的init系统。 在本节中,您将启动该服务并验证它是否成功运行。
通过运行以下命令启动clickhouse-server
服务:
sudo service clickhouse-server start
您将看到类似于以下内容的输出:
Start clickhouse-server service: Path to data directory in /etc/clickhouse-server/config.xml: /var/lib/clickhouse/DONE
要验证服务是否成功运行,请执行:
sudo service clickhouse-server status
它将打印类似于以下的输出,表示服务器正常运行:
clickhouse-server service is running
您已成功启动ClickHouse服务器,现在可以使用clickhouse-client
CLI程序连接到服务器。
第3步 – 创建数据库和表
在ClickHouse中,您可以通过直接在交互式数据库提示符中执行SQL语句来创建和删除数据库。 语句由遵循特定语法的命令组成,这些命令告诉数据库服务器执行所请求的操作以及所需的任何数据。 您可以使用CREATE DATABASE table_name
语法创建数据库。 要创建数据库,请首先运行以下命令启动客户端会话:
clickhouse-client --multiline
此命令将使您进入客户端提示符,您可以在其中运行ClickHouse SQL语句以执行以下操作:
创建,更新和删除数据库,表,索引,分区和视图。
执行查询以检索可选地使用各种条件进行过滤和分组的数据。
--multiline
标志告诉CLI允许输入跨越多行的查询。
在此步骤中,当ClickHouse客户端准备好插入数据时,您将创建一个数据库和表。 出于本教程的目的,您将创建一个名为test
的数据库,在其中您将创建一个名为visits
的表来跟踪网站访问持续时间。
现在您已进入ClickHouse命令提示符,通过执行以下命令创建test
数据库:
CREATE DATABASE test;
您将看到以下输出,表明您已创建数据库:
CREATE DATABASE testOk.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
:用于存储可变长度字符。 它不需要max length属性,因为它可以存储任意长度。Date
:用于存储遵循YYYY-MM-DD
格式的日期。DateTime
:用于存储日期和时间,并遵循YYYY-MM-DD HH:MM:SS
格式。
在列定义之后,指定用于表的引擎。 在ClickHouse中, Engines确定底层数据的物理结构,表的查询功能,并发访问模式以及对索引的支持。 不同的发动机类型适合不同的应用要求。 MergeTree
是最常用和最广泛应用的引擎类型。
现在您已经了解了表创建的概述,您将创建一个表。 首先确认您要修改的数据库:
USE test;
您将看到以下输出显示您已从default
数据库切换到test
数据库:
USE testOk.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列,用于存储每次访问的持续时间(以秒为单位)。float
列可以存储十进制值,例如12.50。url
:存储访问过的URL的字符串列,例如http://example.com
。created
:跟踪访问发生时间的日期和时间列。
在列定义之后,您将MergeTree
指定为表的存储引擎。 MergeTree系列引擎因其对大型实时插入的优化支持,整体稳健性和查询支持而被推荐用于生产数据库。 此外,MergeTree引擎支持按主键对行进行排序,对行进行分区以及复制和采样数据。
如果您打算使用ClickHouse存档经常未查询的数据或存储临时数据,您可以使用Log系列引擎来优化该用例。
在列定义之后,您将定义其他表级选项。 PRIMARY KEY
子句将id
设置为主键列, ORDER BY
子句将存储按id
列排序的值。 主键唯一标识行,用于有效访问单行和高效的行主机托管。
在执行create语句时,您将看到以下输出:
CREATE TABLE visits( id UInt64, duration Float64, url String, created DateTime)ENGINE = MergeTree()PRIMARY KEY idORDER BY idOk.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');
您将看到为每个insert语句重复以下输出:
INSERT INTO visits VALUESOk.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;
您将看到类似于以下内容的输出:
ALTER TABLE visits ADD COLUMN location StringOk.0 rows in set. Elapsed: 0.014 sec.
输出显示您已成功添加location
列。
从版本19.4.3开始,由于实现限制,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;
批量更新语句的输出如下:
ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15Ok.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;
批量删除语句的输出类似于:
ALTER TABLE visits DELETE WHERE duration < 5Ok.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
输出如下:
ALTER TABLE visits DROP COLUMN location StringOk.0 rows in set. Elapsed: 0.010 sec.
既然您已经在visits
表中成功插入,更新和删除了行和列,那么您将继续在下一步中查询数据。
第5步 – 查询数据
ClickHouse的查询语言是SQL的自定义方言,具有适用于分析工作负载的扩展和功能。 在此步骤中,您将运行选择和聚合查询以从检索表中检索数据和结果。
通过选择查询,您可以检索按指定条件筛选的数据行和列,以及要返回的行数等选项。 您可以使用SELECT
语法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;
您将看到以下输出:
SELECT url, durationFROM visitsWHERE 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;
您将看到类似于以下内容的输出:
SELECT SUM(duration)FROM visits┌─SUM(duration)─┐│ 63.7 │└───────────────┘1 rows in set. Elapsed: 0.010 sec.
现在,通过执行以下方式计算前两个URL:
SELECT topK(2)(url) FROM visits;
您将看到类似于以下内容的输出:
SELECT 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;
您将看到以下输出声明您已成功删除表:
DROP TABLE visitsOk.0 rows in set. Elapsed: 0.005 sec.
您可以使用DROP database table_name
语法删除数据库。 要删除test
数据库,请执行以下语句:
DROP DATABASE test;
结果输出显示您已成功删除数据库:
DROP DATABASE testOk.0 rows in set. Elapsed: 0.003 sec.
您已在此步骤中删除了表和数据库。 现在,您已在ClickHouse实例中创建,更新和删除了数据库,表和数据,您将在下一部分中启用对数据库服务器的远程访问。
第7步 – 设置防火墙规则(可选)
如果您打算仅在本地使用ClickHouse与在同一服务器上运行的应用程序,或者在服务器上未启用防火墙,则无需完成此部分。 相反,如果您将远程连接到ClickHouse数据库服务器,则应执行此步骤。
目前,您的服务器已启用防火墙,禁用访问所有端口的公共IP地址。 您将完成以下两个步骤以允许远程访问:
- 添加防火墙规则,允许到端口
8123
传入连接,端口8123
是ClickHouse服务器运行的HTTP端口。
如果您在数据库提示符内,请通过键入CTRL+D
退出它。
执行以下命令编辑配置文件:
sudo vi /etc/clickhouse-server/config.xml
然后取消注释包含<!-- <listen_host>0.0.0.0</listen_host> -->
,如下所示:
... <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> -->...
保存文件并退出vi
。 对于要应用的新配置,请运行以下命令重新启动服务:
sudo service clickhouse-server restart
您将看到此命令的以下输出:
Stop clickhouse-server service:DONEStart clickhouse-server service: Path to data directory in /etc/clickhouse-server/config.xml: /var/lib/clickhouse/DONE
将远程服务器的IP添加到名为public的区域:
sudo firewall-cmd --permanent --zone=public --add-source=second_server_ip/32
ClickHouse的服务器在端口8123
上监听HTTP连接,在端口9000
上监听来自clickhouse-client
连接。 使用以下命令允许访问第二个服务器的IP地址的两个端口:
sudo firewall-cmd --permanent --zone=public --add-port=8123/tcpsudo firewall-cmd --permanent --zone=public --add-port=9000/tcp
您将看到两个命令的以下输出,表明您已启用对两个端口的访问:
success
现在您已添加规则,请重新加载防火墙以使更改生效:
sudo firewall-cmd --reload
此命令也将输出success
消息。 现在可以从您添加的IP访问ClickHouse。 如果需要,可以随意添加其他IP,例如本地计算机的地址。
要验证您是否可以从远程计算机连接到ClickHouse服务器,请首先在第二台服务器上按照本教程的第1步中的步骤进行操作,并确保在其上安装了clickhouse-client
。
现在您已登录到第二台服务器,通过执行以下命令启动客户机会话:
clickhouse-client --host your_server_ip --multiline
您将看到以下输出,表明您已成功连接到服务器:
ClickHouse client version 19.4.3.Connecting to your_server_ip:9000 as user default.Connected to ClickHouse server version 19.4.3 revision 54416.hostname :)
在此步骤中,您已通过调整防火墙规则启用了对ClickHouse数据库服务器的远程访问。
结论
您已成功在服务器上设置ClickHouse数据库实例,并创建了数据库和表,添加了数据,执行了查询并删除了数据库。 在ClickHouse的文档中,您可以阅读有关其他开源和商业分析数据库和一般参考文档的基准。 ClickHouse提供的其他功能包括跨多个服务器的分布式查询处理,通过在不同分片上存储数据来提高性能并防止数据丢失。