[大数据]Clickhouse表引擎—集成系列引擎

Clickhouse表引擎—集成系列引擎

前面我们介绍了表引擎

1. Clickhouse表引擎—MergeTree系列
2. Clickhouse表引擎—日志系列引擎

以及数据库引擎

1. Clickhouse引擎—数据库引擎

今天我们介绍关于引擎系列的最后一节，表引擎—集成系列引擎，集成系列引擎主要解决了Clickhouse和其它外部数据进行交互的问题，或者是将其它数据系统里的数据同步到Clickhouse里来，进行分析处理，但是又不想通过传统的数据同步方式。

从用户的角度来看，配置的集成看起来像查询一个正常的表，但对它的查询是代理给外部系统的。这种透明的查询是这种方法相对于其他集成方法的主要优势之一，比如外部字典或表函数，它们需要在每次使用时使用自定义查询方法。

由于Clickhouse的集成系列引擎是比较多的，所以我们这里只介绍最常用的几种，其它的参考官网，目前主要支持的集成引擎有

• ODBC

• JDBC

• MySQL

• MongoDB

• HDFS

• S3

• Kafka

• EmbeddedRocksD

• RabbitMQ

• PostgreSQL

• SQLite

• Hive

  我们看到支持的这么多，大致可以分为三类，第一类 数据库系统 第二类 文件系统 第三类 消息队列

Hive

首先Hive作为我们最常用的数仓建设工具，我们看看Clickhouse如何和Hive进行集成，我们在hive的ods有一张表log_text,下面我们在Clickhouse 中创建这张表，然后让它指向hive的ods表

CREATE TABLE `log_text`(
  id int, 
  `name` Nullable(String), 
  `city` Nullable(String), 
  `phone` Nullable(String), 
  `acctime` date
)
ENGINE = Hive('thrift://192.168.1.103:9083', 'ods', 'log_text')
PARTITION BY toYYYYMM(acctime) 

这里的端口不是Hiveserver2的端口，而是MetaSotore 的端口，要注意一下,表建成功后，尝试着查询一下就遇到下面这个错误了，这个主要是因为我们的Clickhouse允许在docker 上，

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [210] [08000]: Code: 210. DB::Exception: Unable to connect to HDFS: Hdfs::HdfsRpcException: HdfsFailoverException: Failed to invoke RPC call "getFsStats" on server "kingcall:9000"	Caused by: HdfsNetworkConnectException: Failed to resolve address "kingcall:9000" Name or service not known. (NETWORK_ERROR) (version 22.1.3.7 (official build))
, server ClickHouseNode(addr=http:localhost/<unresolved>:8123, db=default)@-685193826
	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: 210. DB::Exception: Unable to connect to HDFS: Hdfs::HdfsRpcException: HdfsFailoverException: Failed to invoke RPC call "getFsStats" on server "kingcall:9000"	Caused by: HdfsNetworkConnectException: Failed to resolve address "kingcall:9000" Name or service not known. (NETWORK_ERROR) (version 22.1.3.7 (official build))

我们进入容器，安装上ping 发现都不通，所以我们可以配置一下主机的hosts

apt-get update
apt-get install inetutils-ping -y

还有一个要注意的地方，就是如果你的Clickhouse 部署在大数据集群上，那Clickhouse的端口是和HDFS 的RPC 端口是冲突的，这个时候可以去改一下Clickhouse的端口

HDFS

Clickhouse集成的集成引擎除了支持数据库外，还支持文件系统 ，这里我们以HDFS 作为例子,用法如下

ENGINE = HDFS(URI, format)

URI 参数是 HDFS 中整个文件的 URI。 format 参数指定一种可用的文件格式。 执行 SELECT 查询时，格式必须支持输入，以及执行 INSERT 查询时，格式必须支持输出. 路径部分 URI 可能包含 glob 通配符。 在这种情况下，表将是只读的。

路径中的通配符

多个路径组件可以具有 globs。 对于正在处理的文件应该存在并匹配到整个路径模式。 文件列表的确定是在 SELECT 的时候进行（而不是在 CREATE 的时候）。

• * — 替代任何数量的任何字符，除了 / 以及空字符串。
• ? — 代替任何单个字符.
• {some_string,another_string,yet_another_one} — 替代任何字符串 'some_string', 'another_string', 'yet_another_one'.
• {N..M} — 替换 N 到 M 范围内的任何数字，包括两个边界的值。

下面我们创建Clickhouse表

CREATE TABLE `log_text2`(
  id int, 
  `name` Nullable(String), 
  `city` Nullable(String), 
  `phone` Nullable(String), 
  `acctime` date
)
ENGINE = HDFS('hdfs://hdfs1:9000//user/hive/warehouse/ods.db/log_text/*','CSV')
PARTITION BY toYYYYMM(acctime) 

接下来查看一下数据

Kafka

clickhouse批量写入的性能比较好，我们的业务场景下会大批量的产生数据，如果使用clickhouse-jdbc去写的，写入时机和每批次写入的数量不好把控，最终选择了先将消息写入kafka，然后由clickhouse从kafka消费数据，clickhouse server消费到数据之后写入真正的数据表。

CREATE TABLE test_tbl_kafka_json_msg(
		ts DateTime, 
    msg String
) ENGINE = Kafka
SETTINGS
  kafka_broker_list = 'localhost:9092',
  kafka_topic_list = 'topic',
  kafka_group_name = 'group',
  kafka_format = 'JSONAsString'

必要参数：

• kafka_broker_list – 以逗号分隔的 brokers 列表 (localhost:9092)。
• kafka_topic_list – topic 列表 (my_topic)。
• kafka_group_name – Kafka 消费组名称 (group1)。如果不希望消息在集群中重复，请在每个分片中使用相同的组名。
• kafka_format – 消息体格式。使用与 SQL 部分的 FORMAT 函数相同表示方法，例如 JSONEachRow。了解详细信息，请参考 Formats 部分。

可选参数：

• kafka_row_delimiter - 每个消息体（记录）之间的分隔符。
• kafka_schema – 如果解析格式需要一个 schema 时，此参数必填。
• kafka_num_consumers – 单个表的消费者数量。默认值是：1，如果一个消费者的吞吐量不足，则指定更多的消费者。消费者的总数不应该超过 topic 中分区的数量，因为每个分区只能分配一个消费者。

需要注意的是

SELECT 查询对于读取消息并不是很有用（调试除外），因为每条消息只能被读取一次。使用物化视图创建实时线程更实用。

所以我们正确的将Kafka中数据导入ClickHouse的标准流程是：

• 在ClickHouse中建立Kafka Engine 外表，作为Kafka数据源的一个接口
• 在ClickHouse中创建普通表(通常是MergeTree系列）存储Kafka中的数据
• 在ClickHouse中创建Materialized View, 监听Kafka中的数据，并将数据写入ClickHouse存储表中；

第一步创建source表

CREATE TABLE test_tbl_kafka_json_source(
		ts DateTime, 
    msg String
) ENGINE = Kafka
SETTINGS
  kafka_broker_list = 'localhost:9092',
  kafka_topic_list = 'topic',
  kafka_group_name = 'group',
  kafka_format = 'JSONAsString'

第二步创建target表

CREATE TABLE test_tbl_kafka_json_target
(
   ts DateTime, 
   msg String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)

第三步创建物化视图

CREATE MATERIALIZED VIEW test_tbl_kafka_json_view TO test_tbl_kafka_json_target AS
SELECT 
    ts, 
    msg
FROM 
	test_tbl_kafka_json_source

在上述数据导入流程中，Materialized View 起到了一个中间管道作用，将Kafka Engine代表的数据流，写入到目标表中。

实际上，一个数据流可以关联多个Materialized View, 将Kafka中的数据同时导入到多个不同目的的表中。

也可以通过DETACH/ATTACH 来取消关联，或者重新关联到某个目标表

总结

1. Clickhouse 的集成引擎其实方便了我们在Clickhouse中操作其它数据，但是本质上只是一个代理或者是分发工具而已，但是我们可以利用这个特点做数据同步也就是从集成的引擎里面读取消息，然后写到真正的数据存储表里。有点类似Hive 的外部表和内部表，我们可以把集成表的数据插入到Clickhouse的表中。
2. 其实这样做还有一个好处，那就是Clickhouse虽然支持实时写入，但是我们还是建议批量操作，这样的集成方式就很方便的做到批量写入。
3. 集成引擎Kafka 的使用有一些技巧在里面，否则直接使用可能获取不到数据