ClickHouse 十亿 1秒 内的实践
ClickHouse 安装
Ubuntu安装
sudo apt-get install -y apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/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
Centos 安装:
sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
sudo yum install -y clickhouse-server clickhouse-client
配置ClickHouse
配置文件地址:
root@MS-RRQXLHMKMOGE:/etc/clickhouse-server
上面地址为clickHouse 的配置文件地址。
下面是配置文件说明
<!--重新加载内置词典的时间间隔(以秒为单位),默认3600。可以在不重新启动服务器的情况下“即时”修改词典-->
<builtin_dictionaries_reload_interval>3600</builtin_dictionaries_reload_interval>
<!--自定义设置的前缀列表 前缀必须用逗号分隔。-->
<custom_settings_prefixes>custom_</custom_settings_prefixes>
<!--核心转储文件大小的软限制,默认情况下为1 GB。-->
<core_dump>
<size_limit>1073741824</size_limit>
</core_dump>
<!--数据的目录路径。-->
<path>/var/lib/clickhouse/</path>
<graphite>
<!--Graphite服务器-->
<host>localhost</host>
<!--Graphite服务器上的端口-->
<port>42000</port>
<!--发送超时时间,以秒为单位-->
<timeout>0.1</timeout>
<!--发送间隔,以秒为单位-->
<interval>60</interval>
<!--密钥的前缀-->
<root_path>one_min</root_path>
<!--从system.metrics表发送数据-->
<metrics>true</metrics>
<!--从system.events表发送在该时间段内累积的增量数据-->
<events>true</events>
<!--从system.events表发送累积数据-->
<events_cumulative>false</events_cumulative>
<!--从system.asynchronous_metrics表发送数据-->
<asynchronous_metrics>true</asynchronous_metrics>
</graphite>
<https_port>9999</https_port>
<!--在ClickHouse服务器之间交换数据的端口。-->
<interserver_http_port>9009</interserver_http_port>
<!--其他服务器可以用来访问该服务器的主机名。如果省略,则其定义方法与hostname -f命令相同-->
<interserver_http_host>example.yandex.ru</interserver_http_host>
<!--ClickHouse在关闭连接之前等待传入请求的秒数。默认为3秒。-->
<keep_alive_timeout>3</keep_alive_timeout>
<!--限制来源主机的请求, 如果要服务器回答所有请求,请指定“::” :-->
<listen_host>::1</listen_host>
<listen_host>127.0.0.1</listen_host>
<logger>
<!--日志记录级别。可接受的值: trace, debug, information, warning, error-->
<level>trace</level>
<!--日志文件,根据级别包含所有条目-->
<log>/var/log/clickhouse-server/clickhouse-server.log</log>
<!--错误日志文件-->
<errorlog>/var/log/clickhouse-server/clickhouse-server.err.log</errorlog>
<!--文件的大小。适用于loganderrorlog,文件达到大小后,ClickHouse将对其进行存档并重命名,并在其位置创建一个新的日志文件-->
<size>1000M</size>
<!--ClickHouse存储的已归档日志文件的数量-->
<count>10</count>
</logger>
<send_crash_reports>
<enabled>true</enabled>
</send_crash_reports>
<!--标记缓存的大小,用于MergeTree系列的表中。 以字节为单位,共享服务器的缓存,并根据需要分配内存。缓存大小必须至少为5368709120(5G)-->
<mark_cache_size>5368709120</mark_cache_size>
<!--同时处理的最大请求数。-->
<max_concurrent_queries>100</max_concurrent_queries>
<!--最大连接数-->
<max_connections>4096</max_connections>
<!--全局线程池中的最大线程数。预设值:10000。-->
<max_thread_pool_size>12000</max_thread_pool_size>
<openSSL>
<server>
<!-- openssl req -subj "/CN=localhost" -new -newkey rsa:2048 -days 365 -nodes -x509 -keyout /etc/clickhouse-server/server.key -out /etc/clickhouse-server/server.crt -->
<!----PEM格式的客户端/服务器证书文件的路径。如果privateKeyFile包含证书,则可以忽略它。-->
<certificateFile>/etc/clickhouse-server/server.crt</certificateFile>
<!--具有PEM证书的秘密密钥的文件的路径。该文件可能同时包含密钥和证书。-->
<privateKeyFile>/etc/clickhouse-server/server.key</privateKeyFile>
<!-- openssl dhparam -out /etc/clickhouse-server/dhparam.pem 4096 -->
<dhParamsFile>/etc/clickhouse-server/dhparam.pem</dhParamsFile>
<!--检查节点证书的方法。详细信息在Context类的描述中。可能的值:none, relaxed, strict, once.-->
<verificationMode>none</verificationMode>
<!--指示将使用OpenSSL的内置CA证书。可接受的值:true,false-->
<loadDefaultCAFile>true</loadDefaultCAFile>
<!--启用或禁用缓存会话。必须与sessionIdContext结合使用。可接受的值:true,false。-->
<cacheSessions>true</cacheSessions>
<!--不允许使用的协议。-->
<disableProtocols>sslv2,sslv3</disableProtocols>
<!---首选服务器密码-->
<preferServerCiphers>true</preferServerCiphers>
</server>
<client>
<!--指示将使用OpenSSL的内置CA证书。可接受的值:true,false-->
<loadDefaultCAFile>true</loadDefaultCAFile>
<!--启用或禁用缓存会话。必须与sessionIdContext结合使用。可接受的值:true,false。-->
<cacheSessions>true</cacheSessions>
<!--不允许使用的协议。-->
<disableProtocols>sslv2,sslv3</disableProtocols>
<!--首选服务器密码-->
<preferServerCiphers>true</preferServerCiphers>
<!-- Use for self-signed: <verificationMode>none</verificationMode> -->
<!--用于验证无效证书的类-->
<invalidCertificateHandler>
<!-- Use for self-signed: <name>AcceptCertificateHandler</name> -->
<name>RejectCertificateHandler</name>
</invalidCertificateHandler>
</client>
</openSSL>
<!--tcp_port 通过TCP协议与客户端进行通信的端口。即ClickHouse端口。-->
<tcp_port>9000</tcp_port>
<!--TCP端口,用于与客户端进行安全通信。与OpenSSL设置一起使用。-->
<tcp_port_secure>9440</tcp_port_secure>
配置用户
<users>
<!-- If user name was not specified, 'default' user is used. -->
<user_name>
<!--以明文形式分配密码 (不推荐),把它放在一个 password 配置段中。-->
<password></password>
<!--要使用SHA256加密后的密码,请将其放置在 password_sha256_hex 配置段。-->
<password_sha256_hex></password_sha256_hex>
<!--此设置可为用户启用或禁用 SQL-driven 访问控制和帐户管理 。
0 — Disabled.
1 — Enabled.
默认值为 0
-->
<access_management>0|1</access_management>
<networks incl="networks" replace="replace">
</networks>
<profile>profile_name</profile>
<quota>default</quota>
<!-- <databases>
<database_name>
<table_name>
<filter>expression</filter>
<table_name>
</database_name>
</databases>
-->
</user_name>
<!-- Other users settings -->
</users>
启动ClickHouse
Ubuntu 启动
sudo service clickhouse-server start
Centos 启动
sudo /etc/init.d/clickhouse-server start
进入到客户端
#Ubuntu 与 Centos 进入ClickHouse 方式相同
clickhouse-client # or "clickhouse-client --password" if you set up a password.
创建数据库
单机版
CREATE DATABASE [IF NOT EXISTS ] db_name
集群版
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')
创建数据表
create table deviceTestDemo4
(
id Int64,
device_id String,
mac String,
create_time DateTime64(3),
record_time DateTime64(3),
status_params String,
device_type String,
device_model String,
customer_id String,
sn String,
)
engine = MergeTree PARTITION BY toYYYYMM(DATE(create_time)) -- 分区 这里需要进行一次转换 PARTITION BY
PRIMARY KEY device_id --主键
ORDER BY (device_id, create_time)--排序
SETTINGS index_granularity = 8192;
创建物化视图
CREATE MATERIALIZED VIEW device_test_time ENGINE=MergeTree ORDER BY device_id POPULATE AS SELECT create_time ,device_id from deviceTestDemo4 ;
特别说明
- device_test_time 为物化视图的名称
- MergeTree 物化视图的引擎。这里需要特别注意的是:创建物化视图的表引擎一定要与物化视图的引擎相同。
- ORDER BY 物化视图的排序方式,后面跟具体的排序列以及排序方式
- AS SELECT 物化视图显示的列,
- deviceTestDeme4 物化视图的目标表名
- !!! important “重要” ClickHouse 中的物化视图更像是插入触发器。 如果视图查询中有一些聚合,则它仅应用于一批新插入的数据。 对源表现有数据的任何更改(如更新、删除、删除分区等)都不会更改物化视图。
- 如果指定
POPULATE
,则在创建视图时将现有表数据插入到视图中,就像创建一个CREATE TABLE ... AS SELECT ...
一样。 否则,查询仅包含创建视图后插入表中的数据。 我们不建议使用POPULATE,因为在创建视图期间插入表中的数据不会插入其中。 SELECT
查询可以包含DISTINCT
、GROUP BY
、ORDER BY
、LIMIT
……请注意,相应的转换是在每个插入数据块上独立执行的。 例如,如果设置了GROUP BY
,则在插入期间聚合数据,但仅在插入数据的单个数据包内。 数据不会被进一步聚合。 例外情况是使用独立执行数据聚合的ENGINE
,例如SummingMergeTree
。- 在物化视图上执行ALTER查询有局限性,因此可能不方便。 如果物化视图使用构造
TO [db.]name
,你可以DETACH
视图,为目标表运行ALTER
,然后ATTACH
先前分离的(DETACH
)视图。
查询
select * from deviceTestDemo4 where device_id in (SELECT t.device_id FROM test.device_test_time AS t LIMIT 500000000,10 )