Clickhouse—数据库引擎

Clickhouse—数据库引擎

默认情况下,ClickHouse使用Atomic数据库引擎。它提供了可配置的表引擎和SQl 方言

您还可以使用以下数据库引擎:

  • MySQL
  • MaterializeMySQL
  • Lazy
  • Atomic
  • PostgreSQL
  • MaterializedPostgreSQL
  • Replicated
  • SQLite

Atomic 数据库引擎

它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询。默认情况下使用​​Atomic​​数据库引擎。

CREATE DATABASE test[ ENGINE = Atomic];

当然你也可以不指定,默认就是Atomic

数据库​​Atomic​​​中的所有表都有唯一的UUID并将数据存储在目录​​/clickhouse_path/store/xxx/xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy/​​​,其中​​xxxyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy​​是该表的UUID。

如下所示

RENAME TABLES

​RENAME​​查询是在不更改UUID和移动表数据的情况下执行的。这些查询不会等待使用表的查询完成,而是会立即执行。

DROP/DETACH TABLES

在​​DROP TABLE​​​上,不除任何数据,数据库​​Atomic​​​只是通过将元数据移动到​​/clickhouse_path/metadata_dropped/​​​将表标记为已删除,并通知后台线程。最终表数据删除前的延迟 ​​database_atomic_delay_before_drop_table_sec ​​参数的设置觉得。

可以使用​​SYNC​​​修饰符指定同步模式。使用​​database_atomic_wait_for_drop_and_detach_synchronously​​​设置执行,然后​​DROP​​​等待运行 ​​SELECT​​​, ​​INSERT​​和其他使用表完成的查询。表在不使用时将被实际删除。

EXCHANGE TABLES

​EXCHANGE​​以原子方式交换表。因此,不是这种非原子操作:

RENAME TABLE new_table TO tmp, old_table TO new_table, tmp TO old_table;

补充一下这个操作在mysql中是原子的

可以使用一个原子查询:

EXCHANGE TABLES new_table AND old_table;

MySQL 数据库引擎

MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中,并允许您对表进行​​INSERT​​​和​​SELECT​​查询,以方便您在ClickHouse与MySQL之间进行数据交换

​MySQL​​数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中,因此您可以执行诸如​​SHOW TABLES​​​或​​SHOW CREATE TABLE​​之类的操作。

但您无法对其执行以下操作:

  • ​RENAME​
  • ​CREATE TABLE​
  • ​ALTER​

创建MySQL 引擎的数据库

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password')

引擎参数

  • ​host:port​​ — MySQL服务地址
  • ​database​​ — MySQL数据库名称
  • ​user​​ — MySQL用户名
  • ​password​​ — MySQL用户密码

第一次看到这个引擎说实话我就很开心,当时就在想这以后同步mysql 的数据可不是非常方便了吗,于是我就开始了我的第一次折腾之旅

CREATE DATABASE db_mblog_mysql ENGINE = MySQL('192.168.1.101:3306','db_mblog', 'root', 'www123456');

开心不过3s,就遇到了下面这个问题

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [501] [07000]: Code: 501. DB::Exception: Cannot create MySQL database, because Poco::Exception. Code: 1000, e.code() = 0, Exception: Connections to all replicas failed: db_mblog@localhost:3306 as user root,. (CANNOT_CREATE_DATABASE) (version 22.1.3.7 (official build))
, server ClickHouseNode(addr=http:localhost/<unresolved>:8123, db=default)@64313861
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:577)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:486)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:172)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:493)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:894)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3645)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:172)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4949)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Code: 501. DB::Exception: Cannot create MySQL database, because Poco::Exception. Code: 1000, e.code() = 0, Exception: Connections to all replicas failed: db_mblog@localhost:3306 as user root,. (CANNOT_CREATE_DATABASE) (version 22.1.3.7 (official build))
, server ClickHouseNode(addr=http:localhost/<unresolved>:8123, db=default)@64313861
at com.clickhouse.jdbc.SqlExceptionUtils.handle(SqlExceptionUtils.java:54)
at com.clickhouse.jdbc.SqlExceptionUtils.handle(SqlExceptionUtils.java:69)
at com.clickhouse.jdbc.internal.ClickHouseStatementImpl.getLastResponse(ClickHouseStatementImpl.java:80)
at com.clickhouse.jdbc.internal.ClickHouseStatementImpl.executeQuery(ClickHouseStatementImpl.java:257)
at com.clickhouse.jdbc.internal.ClickHouseStatementImpl.execute(ClickHouseStatementImpl.java:target="_blank">409)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:94)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
... 12 more

就是一句话连不上,因为我虽然是我的MySQL和Clickhouse都是在本地的,但是因为我的Clickhouse是部署在Docker 上的,所以我们要允许该用户远程访问,mysql 表的用户权限如下,我们只要修改root 用的Host 允许远程即可,这里我直接修改成​​%​

Clickhouse—数据库引擎

修改后如下

Clickhouse—数据库引擎

这下就可以建成功了,然后我们就可以在Clickhouse中访问MySQL中的数据了

Clickhouse—数据库引擎

Lazy 数据库引擎

在最后一次访问之后,只在RAM中保存​​expiration_time_in_seconds​​秒。只能用于日志表。

它是为存储许多小的Log表而优化的,对于这些表,访问之间有很长的时间间隔。

下面我们创建一个数据库,然后创建一个表

CREATE DATABASE lazy_db ENGINE = Lazy(180);
CREATE TABLE test_tbl_tinylog
(
timestamp DateTime,
message_type String,
message String
)
ENGINE = TinyLog

接下来插入几条数据

INSERT INTO test_tbl_tinylog VALUES (now(),'REGULAR','The first regular message');
INSERT INTO test_tbl_tinylog VALUES (now(),'REGULAR','The second regular message');
INSERT INTO test_tbl_tinylog VALUES (now(),'WARNING','The first warning message');

下面访问一下这个表

SELECT * from test_tbl_tinylog;

Clickhouse—数据库引擎

前面我们说了Lazy 数据库引擎是为日志表引擎服务的,我们尝试建一个非日志引擎的表看看会怎样

CREATE TABLE test_tbl_merge
(
timestamp DateTime,
message_type String,
message String
)
ENGINE = MergeTree()
ORDER BY timestamp

报错如下,也就是不支持​​only with​

Received exception from server (version 22.1.3):
Code: 1. DB::Exception: Received from 192.168.43.41:9000. DB::Exception: Lazy engine can be used only with *Log tables.. (UNSUPPORTED_METHOD)

但是这里有个bug,当你再次执行的时候,它给你报错表已经存在

Received exception from server (version 22.1.3):
Code: 57. DB::Exception: Received from 192.168.43.41:9000. DB::Exception: Directory for table data data/lazy_db/test_tbl_merge/ already exists. (TABLE_ALREADY_EXISTS)

MaterializedMySQL 数据库引擎

clickhouse 20.8将新增 MaterializeMySQL引擎 ,可通过binlog日志实时物化mysql数据,极大提升了数仓的查询性能和数据同步的时效性;原有mysql中承担的数据分析工作 可交由clickhouse去做,这么做可显著降低线上mysql的负载,从此OLTP与OLAP业务实现完美融合

目前 MaterializeMySQL database engine 还不支持表级别的同步操作,需要将整个mysql database映射到clickhouse,映射过来的库表会自动创建为ReplacingMergeTree表engine。

MaterializeMySQL 支持全量和增量同步,首次创建数据库引擎时进行一次全量复制,之后通过监控binlog变化进行增量数据同步;该引擎支持mysql 5.6/5.7/8.0版本数据库,兼容insert,update,delete,alter,create,drop,truncate等大部分DDL操作。

第一步开启mysql 的gtid,可以参考​​全局事务标识符(GTID)​​

[mysqld]
gtid-mode = ON
enforce-gtid-consistency = ON
binlog_format = ROW
log-bin = mysql-bin
default_authentication_plugin=mysql_native_password

第二步开启物化特性,因为改功能还在实验阶段,所以需要手动开启

SET allow_experimental_database_materialized_mysql = 1

第三步创建mysql 库和表

CREATE DATABASE materialize;
CREATE TABLE materialize.test (a INT PRIMARY KEY, b INT);

第四步创建clickhouse库

CREATE DATABASE db_mysql_materialized ENGINE =
MaterializedMySQL('192.168.1.101:3306', 'materialize', 'root', 'www123456')
SETTINGS
allows_query_when_mysql_lost=true,
max_wait_time_when_mysql_unavailable=10000;

可以看到表已经被映射过来了

Clickhouse—数据库引擎

第五步mysql 插入数据

INSERT INTO materialize.test VALUES (1, 11), (2, 22);

第六步 clickhouse 中观察数据

SELECT * from test;

总结

主要介绍了Clickhouse的 数据库引擎

  1. Atomic 是默认的数据库引擎
  2. 其他的引擎我们可以根据具体的业务场景来合理使用