Datax从入门到精通06-Hive分区表同步数据到clickhouse实操

最新推荐文章于 2024-02-19 17:02:04 发布

谭正强

最新推荐文章于 2024-02-19 17:02:04 发布

阅读量5.2k

点赞数 10

分类专栏：数据同步文章标签： spark waterdrop clickhouse

本文链接：https://blog.csdn.net/u010834071/article/details/107185549

版权

数据同步专栏收录该内容

10 篇文章 5 订阅

订阅专栏

一、前置说明

clickhouse 版本：ClickHouse client version 20.3.12.112
waterdrop 环境：waterdrop-1.5.0
集群部署：4台机器（32core,64GB）查询时候使用nginx负载均衡。

二、Hive数据导入ClickHouse方案

应用场景：
鉴于clickhouse优秀的写入和查询性能，我们决定将hive中小时表的分区数据导入到clickhouse进行数据分析。初测：3千万的hive表写入postgresql需要400min+(jdbc方式), 而使用waterdrop同步仅仅需要7min左右，查询我在这里就不展开了，后续有介绍文档。基于这个我们想要探索使用clickhouse来帮助更快的数据分析，因此我们将Hive表的数据每天全量写入clickhouse，但是当前clickhouse不提供如MySQL replace into 或者hive的inset overwrte此类语法，所以只能采用临时表的方案了。

调研方案如下：

全量数据导入解决办法

1、先创建一个临时本地表(由于Bi目前为天级调度，推荐命名：原始表名_时间_tmp)
CREATE TABLE test.t_20200627_tmp  ON CLUSTER report_shards_replicas (`Name` String, `StartDate` Date, `Year` UInt16)
 ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ontime/havail_re_20200627_tmp', '{replica}')
order by StartDate
SETTINGS index_granularity = 8192;
2、再创建一个分布式临时表
CREATE TABLE test.t_20200627_tmp_all
 (`Name` String, `StartDate` Date, `Year` UInt16)
 ENGINE = Distributed('report_shards_replicas', 'test', 't_20200627_tmp', rand())
 
3、往分布式临时表导入数据
 使用waterDrop导入hive数据到临时表：test.t_20200627_tmp_all
4、删除原始本地表
drop table test.t_20200627  ON CLUSTER report_shards_replicas
5、删除分布式临时表
drop table  test.t_20200627_tmp_all
6、更改临时本地表名为原始本地表名,分布式表保持不变
rename table test.t_20200627_tmp_all to test.t_20200627_all ON CLUSTER report_shards_replicas

增量导入解决办法

1.分区表的方式
----- 创建分区表
CREATE TABLE bi.tmp_local_pt ON CLUSTER report_shards_replicas( \
event_date            String, \
app_id                Nullable(Int32),  \ 
event_id              Nullable(String), \ 
act_key               Nullable(String), \ 
event_no              Nullable(String),  \ 
device_id             Nullable(String),  \ 
pt                    String \ 
) ENGINE = ReplicatedMergeTree ('/clickhouse/tables/{layer}-{shard}/bi/tmp_local_pt', '{replica}') PARTITION BY pt order by event_date;
----- 查询分区表
idc-ck04 :) select partition, name, active from system.parts WHERE table = 'tmp_local_pt'
┌─partition──┬─name─────────────────────────────────────┬─active─┐
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_0_0_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_0_5_1   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_0_10_2  │      1 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_1_1_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_2_2_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_3_3_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_4_4_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_5_5_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_6_6_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_7_7_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_8_8_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_9_9_0   │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_10_10_0 │      0 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_11_11_0 │      1 │
│ 2020-07-07 │ a8ce90dc41a7b1d2eb470a73a2f724e1_12_12_0 │      1 │
└────────────┴──────────────────────────────────────────┴────────┘
----- 删除对应分区表
ALTER TABLE tmp_local_pt on cluster report_shards_replicas DROP PARTITION '2020-07-07';


2.直接delete指定where条件即可，如：
alter table xxx  on cluster ck-001  delete where pt='${day}'

在这里使用这两种方式非常方便处理数据全量写入已经处理新增数据如何支持任务重跑的问题，相信在数据处理过程中，此两种肯定用得到。

三、Hive数据导入clickhouse具体实现（脚本方式）

1. 脚本实现（该脚本会用到2，3中的文件）

说明：传参可以根据自己的需要自行改造

#!/bin/bash
param1=$1
dataDate=$2
progress=$3
index=1
for arg in $@
do
    param_key=`echo ${arg} |cut -d'=' -f 1`
    case "$param_key" in
        queuename)
            queuename=`echo "${arg}"|cut -d'=' -f 2-`
            if [[ "$queuename" == "" ]]; then
                 queuename="root.default"
            fi
            ;;
        actionId)
            actionId=`echo "${arg}"|cut -d'=' -f 2-`
            ;;
        actionSid)
            actionSid=`echo "${arg}"|cut -d'=' -f 2-`
            ;;
        executimes)
            executimes=`echo "${arg}"|cut -d'=' -f 2-`
            ;;
    esac
    index=$(($index+1))
done

# 脚本输出日志路径，此乃固定格式
now=`date +%Y-%m-%d`
logPath="/data/job-logs/$now"
mkdir -p $logPath
logPath="$logPath/$actionId"_"$actionSid"_"$executimes.log"

# 如果传入数据时间为$2
day=`date -d @$2 +%Y-%m-%d`
dt=`date -d @$2 +%Y%m%d`
dt_hour=`date -d @$2 +%Y%m%d%H`
hour=`date -d @$2 +%H`
mm=`date -d @$2 +%M`
ym=`date -d @$2 +%Y%m`
current=`date "+%Y-%m-%d %H:%M:%S"`
ts=`date "+%Y%m%d%H%M%S"`
timeStamp=`date -d "$current" +%s` 

conf_path=/xx/scripts/bi/bi_sel_stu_follow_info_hf.conf

#输出任务执行参数
echo "param:" day="$day" dt_hour=$dt_hour progress=$progress queuename=$queuename path=$path  $@>>$logPath
echo "开始运行: $current hive2clickhouse  task is begin running ..." >> $logPath

# 定义function，运行waterdrop作业 使用cluster on yarn模式
function save_to_clickhouse() {
/bin/bash /data/program/waterdrop-1.5.0/bin/start-waterdrop.sh  \
--master yarn \
--queue $queuename \
--deploy-mode cluster  \
--config $conf_path  >> $logPath

}


# 生成临时表
sql_path=/xx/scripts/bi/bi_sel_stu_follow_info_hf.sql
ck_tmp_table="ck_${ts}"
ck_tmp_all_table="ck_${ts}_all"
echo ">>> 创建分布式临时表：${ck_tmp_all_table}" >> $logPath

# SQL变量替换
sed -i "s/__tmp__/${ck_tmp_table}/g" $sql_path
sed -i "s/__all__/${ck_tmp_all_table}/g" $sql_path


# 创建临时表
/usr/bin/clickhouse-client -h xxxx --port 9000 -d bi  -u xxx --password xxx  --multiquery  < $sql_path
if [[ $? -ne 0 ]]; then
       echo 'ERROR: 创建clickhouse临时表失败 !' >> $logPath
       exit 1
fi

# 修改hive导入分区时间&ck目标表
echo ">>> 同步时间时间分区pt=$dt_hour" >> $logPath
sed -i "s/__pt__/${dt_hour}/g" $conf_path
sed -i "s/__all__/${ck_tmp_all_table}/g" $conf_path



# 导数到临时表
echo ">>> 导出数据到clickhouse表：$ck_tmp_all_table " >> $logPath
save_to_clickhouse
if [[ $? -ne 0 ]]; then
       echo '===========save_to_clickhouse is failed=========== ' >> $logPath
       exit 1
fi


# 删除ck 本地表，删除临时分布式表
echo ">>> clickhouse临时表替换为正式表：bi_sel_stu_follow_info_hf" >> $logPath
/usr/bin/clickhouse-client -h xxxx --port 9000 -d bi  -u xxx \
--password xxx  --multiquery  --query "
drop table bi_sel_stu_follow_info_hf  ON CLUSTER report_shards_replicas;
drop table  ${ck_tmp_all_table};
rename table ${ck_tmp_table} to bi_sel_stu_follow_info_hf ON CLUSTER report_shards_replicas;
"
if [[ $? -ne 0 ]]; then
       echo '>>> clickhouse临时表替换失败！' >> $logPath
       exit 1
fi


# 任务完成打印 替换原变量
sed -i "s/$dt_hour/__pt__/g" $conf_path
sed -i "s/${ck_tmp_all_table}/__all__/g" $conf_path

sed -i "s/${ck_tmp_table}/__tmp__/g" $sql_path
sed -i "s/${ck_tmp_all_table}/__all__/g" $sql_path

end=`date "+%Y-%m-%d %H:%M:%S"`
echo "运行结束: $end  hive2clickhouse task is successful =============" >> $logPath

这样使用sed命令替换的方式实在太丑陋了，后面发现waterdrop可以直接变量传参如下：

/bin/bash /app/program/waterdrop-1.5.0/bin/start-waterdrop.sh  \
--master yarn \
--queue $queuename \
--deploy-mode cluster  \
--config  $conf_path -i pt=${day} >> $logPath

链接直达：https://github.com/InterestingLab/waterdrop/issues/267

2. Waterdrop conf 文件

spark {
spark.sql.catalogImplementation = "hive"
spark.app.name = "Waterdrop"
spark.executor.instances = 5
spark.executor.cores = 4
spark.executor.memory = "4g"
}
input {
hive {
pre_sql = "select stu_id,stu_user_id, xx from bi.bi_sel_stu_follow_info_hf  where pt = '__pt__'"
table_name = "bi_sel_stu_follow_info_hf_tmp"
}
}
filter {
}

output {
clickhouse {
host = "xxx:8123"
database = "bi"
table = "__all__"
fields = ["stu_id","stu_user_id","xx"]
username = "xxx"
password = "xxx"
clickhouse.socket_timeout = 50000
bulk_size = 500000
retry_codes = [209, 210]
retry = 3  
}
}

3. clickhouse创建临时表SQL文件

文件名： bi_sel_stu_follow_info_hf.sql

--clickhouse本地临时表
CREATE TABLE bi.__tmp__ ON CLUSTER report_shards_replicas( 
stu_id Int64, 
stu_user_id Nullable(Int64) ,  
stu_name Nullable(String) ,  
stu_mobile Nullable(String) ,  
sel_id Nullable(Int64) ,  
sel_user_id Nullable(Int64) ,  
sel_team_id Nullable(Int64) ,  
sel_team Nullable(String) ,  
sel_district_id Nullable(Int64) ,  
sel_district Nullable(String) ,  
sel_name Nullable(String) ,  
tag_level Nullable(String) ,  
stu_recycle Nullable(String),  
stu_is_referral Nullable(Int32) ,  
created_at String ,  
belong_time Nullable(String) ,  
call_stu_count Nullable(Int64) ,  
connect_stu_count Nullable(Int64) ,  
duration_stu Nullable(Float64) ,  
latest_call_time Nullable(String) ,  
next_callback_time Nullable(String) ,  
call_follow_gap Nullable(Float64) ,  
is_binding_service Nullable(String) ,  
recent_debug_les_time Nullable(String) ,  
recent_test_les_time Nullable(String) ,  
debug_les_arrange_count Nullable(Int64) ,  
debug_les_actual_start_count Nullable(Int64) ,  
test_les_arrange_count Nullable(Int64) ,  
test_les_start_count Nullable(Int64) ,  
test_les_actual_start_count Nullable(Int64) ,  
first_kpi_time Nullable(String),  
first_realtime_kpi Nullable(Float64) ,  
realtime_kpi Nullable(Float64) ,  
drop_time Nullable(String) ,  
is_give_up Nullable(String) ,  
update_datetime Nullable(String),  
belong_date Nullable(Int32),  
sel_district_bu Nullable(Int32) 
) ENGINE = ReplicatedMergeTree ('/clickhouse/tables/{layer}-{shard}/bi/__tmp__', '{replica}') order by created_at;

--分布式临时表
CREATE TABLE bi.__all__ ( 
stu_id Int64, 
stu_user_id Nullable(Int64) ,  
stu_name Nullable(String) ,  
stu_mobile Nullable(String) ,  
sel_id Nullable(Int64) ,  
sel_user_id Nullable(Int64) ,  
sel_team_id Nullable(Int64) ,  
sel_team Nullable(String) ,  
sel_district_id Nullable(Int64) ,  
sel_district Nullable(String) ,  
sel_name Nullable(String) ,  
tag_level Nullable(String) ,  
stu_recycle Nullable(String),  
stu_is_referral Nullable(Int32) ,  
created_at String ,  
belong_time Nullable(String) ,  
call_stu_count Nullable(Int64) ,  
connect_stu_count Nullable(Int64) ,  
duration_stu Nullable(Float64) ,  
latest_call_time Nullable(String) ,  
next_callback_time Nullable(String) ,  
call_follow_gap Nullable(Float64) ,  
is_binding_service Nullable(String) ,  
recent_debug_les_time Nullable(String) ,  
recent_test_les_time Nullable(String) ,  
debug_les_arrange_count Nullable(Int64) ,  
debug_les_actual_start_count Nullable(Int64) ,  
test_les_arrange_count Nullable(Int64) ,  
test_les_start_count Nullable(Int64) ,  
test_les_actual_start_count Nullable(Int64) ,  
first_kpi_time Nullable(String),  
first_realtime_kpi Nullable(Float64) ,  
realtime_kpi Nullable(Float64) ,  
drop_time Nullable(String) ,  
is_give_up Nullable(String) ,  
update_datetime Nullable(String),  
belong_date Nullable(Int32),  
sel_district_bu Nullable(Int32) 
) 
ENGINE = Distributed (report_shards_replicas,bi,__tmp__,rand());

四、DataX 同步至clickhouse

说明：我们是合并了主分支clickhouse sinker组件并改写了部分内容使其支持ck的增量和全量写入，其原理同上，只不过在写入的时候尽可能设置批次大点，比如之前我们设置2000，后来改为100000 性能上有显著的提升，同时ck的后端merge延迟也降了下来。目前我司的数据同步至ck全部采用datax方案，脚本方案废弃，目前来看暂未遇到大的问题。

四、总结

说明：本文主要是记录使用脚本方式从hive同步数据到clickhouse并且每天全量覆盖的实现方式（增量处理模式上面已经有说明暂不给示例）。

不足：建议使用代码方式来改写，因为这里涉及到的sed命令并不能保证百分之百有效，有其当任务失败的时候可能获取不到最新更新的配置信息。

测试结果：1千万+级别数据count(distinct) clickhouse比MySQL快100倍，鉴于clickhouse优秀的查询表现，我司也正逐步开始使用clickhouse来代替postgresql和mysql。由于clickhouse不支持事务，所以需要在数据ETL的上游保证一致性，故在脚本里面无法控制，为此我们还在探索中，下一步将集成到DataSync平台中，逐步完善这种机制更好的支持千万级大表数据分析的场景。

如对你有帮助，不妨点个赞吶，Thanks♪(･ω･)ﾉ

谭正强

关注

10
点赞
踩
23

收藏

觉得还不错? 一键收藏
4
评论
Datax从入门到精通06-Hive分区表同步数据到clickhouse实操

一、前置说明clickhouse 版本：ClickHouse client version 20.3.12.112waterdrop 环境：waterdrop-1.5.0集群部署：4台机器（32core,64GB）查询时候使用nginx负载均衡。二、Hive数据导入ClickHouse方案应用场景：鉴于clickhouse优秀的写入和查询性能，我们决定将hive中小时表的分区数据导入到clickhouse进行数据分析。初测：3千万的hive表写入postgresql需要400min+(jd
复制链接

扫一扫