Clickhouse—数据库引擎

Clickhouse—数据库引擎

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

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

MySQLMaterializeMySQLLazyAtomicPostgreSQLMaterializedPostgreSQLReplicatedSQLiteAtomic 数据库引擎

它支持非阻塞的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: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中访问MySQL中的数据了

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;

前面我们说了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;

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

第五步mysql 插入数据

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

第六步 clickhouse 中观察数据

SELECT * from test;

总结

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

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