如何迁移ES数据到CLICKHOUSE中

示例场景：需要迁移某个应用的历史日志数据到CK中，日志数据在ES中是按天索引的，每天一个索引文件，索引规则是"应用名"+"日期"。

一、分析ES数据

ES中数据格式

{ "_index": "appconfig-2023.03.01", "_type": "_doc", "_id": "fGfVm4YB_vTV_M_7xpLS", "_version": 1, "_score": null, "_source": { "m_StringValue": "参数：{\"cacheKey\":6,\"loader\":{\"Delegate\":{},\"method0\":{\"Name\":\"GetAllBankInfo\",\"AssemblyName\":\"AppInsurance.Bussiness, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null\",\"ClassName\":\"AppInsurance.Bussiness.BankInfoBll\",\"Signature\":\"System.Collections.Generic.List`1[AppInsurance.WCF.Entity.BankInfoEntity] GetAllBankInfo()\",\"Signature2\":\"System.Collections.Generic.List`1[[AppInsurance.WCF.Entity.BankInfoEntity, AppInsurance.WCF.Entity, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]] GetAllBankInfo()\",\"MemberType\":8,\"GenericArguments\":null}}}\r\n 结果：\"缓存刷新完成:Fund_AppInsurance_AllBankInfoprod\"", "m_currentThread": 0, "@version": "1", "m_MaxCapacity": 2147483647, "type": "appconfig", "@timestamp": "2023-03-01T06:22:03.883Z", "Capacity": 1108 }, "fields": { "@timestamp": [ "2023-03-01T06:22:03.883Z" ] }, "sort": [ 1677651723883 ] }

其中"_source"字段为真实日志数据，其他字段为ES公共字段，可以忽略，以下提取该字段展示

{ "m_StringValue": "参数：{\"cacheKey\":6,\"loader\":{\"Delegate\":{},\"method0\":{\"Name\":\"GetAllBankInfo\",\"AssemblyName\":\"AppInsurance.Bussiness, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null\",\"ClassName\":\"AppInsurance.Bussiness.BankInfoBll\",\"Signature\":\"System.Collections.Generic.List`1[AppInsurance.WCF.Entity.BankInfoEntity] GetAllBankInfo()\",\"Signature2\":\"System.Collections.Generic.List`1[[AppInsurance.WCF.Entity.BankInfoEntity, AppInsurance.WCF.Entity, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]] GetAllBankInfo()\",\"MemberType\":8,\"GenericArguments\":null}}}\r\n 结果：\"缓存刷新完成:Fund_AppInsurance_AllBankInfoprod\"", "m_currentThread": 0, "@version": "1", "m_MaxCapacity": 2147483647, "type": "appconfig", "@timestamp": "2023-03-01T06:22:03.883Z", "Capacity": 1108 }

一、CK中DDL设计

注意DDL的字段需要与JSON字段顺序保持一致

CREATE TABLE xxxx.elk_his_log_appconfig_local ( `m_StringValue` String COMMENT '日志内容', `m_currentThread` String COMMENT '线程', `@version` String COMMENT '日志版本', `m_MaxCapacity` Int32 COMMENT '最大优先级', `type` String COMMENT '服务类型', `@timestamp` DateTime64(3) COMMENT '时间戳', `Capacity` Int32 COMMENT '优先级', INDEX xxxx_appconfig_message_index m_StringValue TYPE ngrambf_v1(2, 81920, 2, 0) GRANULARITY 8 ) ENGINE = MergeTree PARTITION BY toYYYYMMDD(`@timestamp`) ORDER BY `@timestamp` TTL toDateTime(`@timestamp`) + toIntervalDay(1095) SETTINGS index_granularity = 8192;

三、方案设计

查阅资料了解到，目前想将ES的数据直接导入到clickhouse,好像没有现成的工具可以使用，必须先导出文件，再将文件导入到CK中，CK可支持导入的文件有CSV和JSON格式，当然直接通过SQL导入肯定是可以的。由于数据存储在ES中，导出CSV和JSON文件比SQL更简单。

3.1 ES导出文件

3.1.1ES导出JSON文件

1.想要从ES导出JSON文件，ES有API接口可以直接导出JSON文件，但是导出的JSON文件是包含ES公共部分的字段的，而我们只需要_source字段。这里可以使用jq命令，直接过滤json数据，输出需要的字段到json文件，也可以利用这个命令做json的前置格式转换。

2.如果要导出CSV文件，可以通过先导出JSNO文件，然后转CSV导出即可。这里可以使用Linux命令行工具csvtool进行转换。

3.由于ES索引是按照天创建的，每个应用保存近三个月的，总共这些应用大概有接近400个索引，索引命名比较规律，应用名+日期。这里可以利用shell脚本，从指定日期开始到当天的，遍历一遍，依次下载文件内容。

3.1.2 需要解决的问题

​

编辑切换为居中

添加图片注释，不超过 140 字（可选）

以上问题1-5可以通过jq命令，进行json处理解析

第6点解决方法：

通过查询ES index索引接口

http://127.0.0.1:9200/_cat/indices?v&pretty

通过docs.count列可以看到需要导出的文件的记录数据appconfig应用超过了10000条，所以只需要解决如何导出appconfig的日志文件即可。

3.1.3 导出MAX SIZE记录

3.1.3.1 调整最大查询记录数

curl -XPUT "http://127.0.0.1:9200/$index/_settings" -H "Content-Type: application/json" -d '{"max_result_window":"100000"}'

通过调整索引文件的最大记录数，由于appconfig的最大记录也不差过100000，所以调大查询记录即可

3.1.3.2使用Scroll API（官方不推荐）

第一次查询带上?scroll=1m，之后调用/search/scroll接口，每次带上查询返回的scroll_id进行查询

3.1.3.3使用Search after参数

相当于分页查询，具体参见官方API

由于我这边主要是用于shell脚本导出，为了不增加复杂性，并且一次性使用，而且查询记录不是很多，所以直接调整查询最大记录数比较简单直接，所以采用第一种方式。

3.2文件导入CK

导入到ck里面，可以利用clickhouse-client工具将文件内容导入到ck表中。注意JSON字段顺序必须要和CK表中字段顺序一致，不然可能会出现错位。

1.需要通过clickhouse-client登录用户名密码，进入工具命令

2.将文件流读入，通过sql命令从流中读取json内容，插入到表中

四、实施步骤

由于导出CSV文件需要从JSON文件转换，并且CK可以支持JSON导入，所以这里直接导出JSON文件。CSV文件输出也验证了下。

4.1环境准备

#安装jq 源 yum install epel-release #安装jq yum install jq #安装clickhouse-client yum install clickhouse-client

4.1导出json文件

#!/bin/bash start_date="2023-02-16" end_date="2023-05-16" # 将日期格式化为yyyy.MM.dd格式并添加前缀 prefix="insuranceprod-" date_format="%Y-%m-%d" current_date=$(date -d "$start_date" +"$date_format") end_date=$(date -d "$end_date" +"$date_format") date_format1="%Y.%m.%d" current_date1=$(date -d "$start_date" +"$date_format1") # 遍历日期并打印 while [ "$current_date" != "$end_date" ]; do current_date1=$(date -d "$current_date" +"$date_format1") echo "$current_date1" filename="$prefix$current_date1" echo "$filename" curl -XPUT "http://127.0.0.1:9200/$filename/_settings" -H "Content-Type: application/json" -d '{"max_result_window":"10000000"}' curl -XPOST "http://127.0.0.1:9200/$filename/_search?size=10000" -H "Content-Type: application/json" -d '{ "query": { "match_all": {} } }' | jq '.hits.hits[]._source' | jq -s '.' | jq '.[]."@timestamp" |= (sub("T"; " ") | sub("Z"; ""))' > "./json_file/${filename//./_}.json" current_date=$(date -d "$current_date + 1 day" +"$date_format") done current_date1=$(date -d "$current_date" +"$date_format1") filename="$prefix$current_date1" curl -XPOST "http://127.0.0.1:9200/$filename/_search?size=10000" -H "Content-Type: application/json" -d '{ "query": { "match_all": {} } }' | jq '.hits.hits[]._source' | jq -s '.' | jq '.[]."@timestamp" |= (sub("T"; " ") | sub("Z"; ""))' > "./json_file/${filename//./_}.json" echo "$filename"

指定开始日期、结束日期，转换日期格式后，遍历起止日期，格式化时间，拼接成对应的索引名称，通过ES接口调整查询的最大记录数，然后一次性查询记录，并且通过jq命令处理json数据，输出到json_file目录下面保存。

4.3 导入clickhouse

#!/bin/bash echo "Starting import process..." # 遍历当前目录下所有 JSON 文件 for file in *.json do echo "Importing data from $file..." # 使用 cat 命令读取文件内容并使用 clickhouse-client 命令将数据导入到 ClickHouse 数据库 cat $file | jq '.[]."@version" |= tostring' | jq '.[].m_currentThread |= tostring' | clickhouse-client --host=127.0.0.1 --port 9001 --user=default --password=ck.123 --query="INSERT INTO xxxx.elk_his_log_appconfig_local FORMAT JSONEachRow" done echo "Import process completed."