利用clickhouse的URL引擎做数据推送

时间：2025-01-20 14:59:56

1. URL引擎简介

从远程 HTTP/HTTPS 服务器查询数据。这个引擎类似于文件引擎。（来自官方文档，见引用）

简单来说，就是能从URL读、写数据，就像读写一个数据库一样。读是GET、写是POST。

2. 利用URL引擎推送

基于这个特性，想要解决一个实际问题。就是想要将记录下的数据，通过JSON API同步推送到另一个服务器上。

思路：

  1. 首先测试URL表推送数据；
    1. 创建URL引擎表；
    2. 启动一个服务器；
    3. 插入数据测试推送；
  2. 利用物化视图，将数据同步推送到URL引擎表，从而推送到远程服务器；
    1. 创建原始数据表；
    2. 创建物化视图；
    3. 创建一个URL引擎表；
    4. 插入数据测试推送；

3. 实践步骤

  1. 首先测试URL表推送数据；
    1. 创建URL引擎表； 
      CREATE TABLE url_engine_table (
   `domain` String,
   `url` String,
   `timestamp` Datetime
)
ENGINE=URL('http://127.0.0.1:12345/', JSON);
    2. 启动一个服务器； 
      from flask import Flask, request, jsonify
import json

app = Flask(__name__)

@('/', methods=['POST'])
def post_data():
    raw = 
    print(raw)
    data = (())
    print(request.content_type)
    print(data)
    return jsonify({"received_data": data}), 200

if __name__ == '__main__':
    (debug=True, port=12345)
    3. 插入数据测试推送； 
      INSERT INTO url_engine_table VALUES ( '',  '/',  now());

      输出日志：
       

      root@ubuntu:~# python3 
 * Serving Flask app 'a'
 * Debug mode: on
WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead.
 * Running on http://127.0.0.1:12345
Press CTRL+C to quit
 * Restarting with stat
 * Debugger is active!
 * Debugger PIN: 129-280-379
{'meta': [ {'name': 'domain', 'type': 'String'}, {'name': 'url', 'type': 'String'}, {'name': 'timestamp', 'type': 'DateTime'}], 'data': [{'domain': '','url': '/', 'timestamp': '2024-08-12 17:20:30'}], 'rows': 1, 'statistics': {'elapsed': 0.00077385, 'rows_read': 0, 'bytes_read': 0}}
127.0.0.1 - - [12/Aug/2024 09:20:30] "POST / HTTP/1.1" 200 -

      可以看到日志是带着meta和statistics等数据。而我需要的是只有对应表结构的json数据。于是就对比了一些格式测试，最终选定了一个设置。在下面4会列出对比详情。

  2. 利用物化视图，将数据同步推送到URL引擎表，从而推送到远程服务器；
    1. 创建原始数据表； 
      CREATE TABLE url_log
(
    `domain` LowCardinality(String),
    `url` String,
    `timestamp` DateTime

)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (timestamp, domain);

    2. 创建一个URL引擎表； 
      CREATE TABLE url_engine_table (
    `domain` String,
    `url` String,
    `timestamp` Datetime
)
ENGINE=URL('http://127.0.0.1:12345/', JSONEachRow)
SETTINGS output_format_json_array_of_rows = 1;
      这里使用了JSONEachRow 格式的URL请求，并设置了SETTINGS output_format_json_array_of_rows = 1。
      这会让数据以如下的形式输出到远端URL： 
      [
{ 'domain': '', 'url': '/', 'timestamp': '2024-08-12 18:09:06'},
{ 'domain': '', 'url': '/index', 'timestamp': '2024-08-12 18:09:06'}
]
    3. 创建物化视图； 
      CREATE MATERIALIZED VIEW url_mv
TO url_engine_table
AS
SELECT
    `domain` ,
    `url` ,
    `timestamp`
FROM url_log

    4. 插入数据测试推送； 
      INSERT INTO url_engine_table VALUES (  '',  '/', now()) ;
INSERT INTO url_engine_table VALUES (  '',  '/index', now()) ;

      服务端输出格式：

      [
{ 'domain': '', 'url': '/', 'timestamp': '2024-08-12 18:09:06'},
{ 'domain': '', 'url': '/index', 'timestamp': '2024-08-12 18:09:06'}
]

         通过控制物化视图的查询语句，还可以实现诸如以下功能：

        1. 数值大于某个条件发送告警消息；

        2. 事件触发器，某个数据写入联动其他组件进行处理。
        等等

4. 不同的格式

格式 说明 数据示例

JSON

 带meta和统计

{'meta': [{'name': 'domain_uuid', 'type': 'String'}, {'name': 'domain', 'type': 'String'}, {'name': 'path', 'type': 'String'}, {'name': 'url', 'type': 'String'}, {'name': 'type', 'type': 'String'}, {'name': 'timestamp', 'type': 'DateTime'}], 'data': [{'domain_uuid': '111', 'domain': '', 'path': '', 'url': '', 'type': '', 'timestamp': '2024-08-12 17:20:30'}], 'rows': 1, 'statistics': {'elapsed': 0.00077385, 'rows_read': 0, 'bytes_read': 0}}

JSONCompact

 带meta和统计，不带字段名

{'meta': [{'name': 'domain_uuid', 'type': 'String'}, {'name': 'domain', 'type': 'String'}, {'name': 'path', 'type': 'String'}, {'name': 'url', 'type': 'String'}, {'name': 'type', 'type': 'String'}, {'name': 'timestamp', 'type': 'DateTime'}], 'data': [['111', '', '', '', '', '2024-08-12 17:25:05']], 'rows': 1, 'statistics': {'elapsed': 0.000744253, 'rows_read': 0, 'bytes_read': 0}}

JSONStrings

 带meta和统计

{'meta': [{'name': 'domain_uuid', 'type': 'String'}, {'name': 'domain', 'type': 'String'}, {'name': 'path', 'type': 'String'}, {'name': 'url', 'type': 'String'}, {'name': 'type', 'type': 'String'}, {'name': 'timestamp', 'type': 'DateTime'}], 'data': [{'domain_uuid': '111', 'domain': '', 'path': '', 'url': '', 'type': '', 'timestamp': '2024-08-12 17:28:27'}], 'rows': 1, 'statistics': {'elapsed': 0.000855415, 'rows_read': 0, 'bytes_read': 0}}

JSONEachRow

 只有数据一行一个，格式为

application/x-ndjson

{"domain_uuid":"111",

"domain":"","path":"","url":"","type":"",

"timestamp":"2024-08-12 17:42:15"}

JSONEachRow

+

SETTINGS output_format_json_array_of_rows = 1;

 只有数据，在列表里 [
{ 'domain': '', 'url': '/', 'timestamp': '2024-08-12 18:09:06'},
{ 'domain': '', 'url': '/index', 'timestamp': '2024-08-12 18:09:06'}
]

可以根据需要选择。更多格式见引用。

引用：

URL Table Engine | ClickHouse Docs

Formats for Input and Output Data | ClickHouse Docs

Format settings | ClickHouse Docs

相关文章