Hive 数据同步ClickHouse

在开发过程中，需要从HDFS中导入数据到clickhouse中。

把所有数据分析相关的日志数据存储至ClickHouse这个优秀的数据仓库之中，当前日数据量达到了300亿。

需求：按条件筛选Hive表中的数据同步到ClickHouse中

方法一：使用Waterdrop

按照ClickHouse 官方文档提供的方法，在ClickHouse 中创建HDFS引擎表，读取Hive的数据，将读取的数据插入到ClickHouse本地表中。

缺点：
1.需要在clickhouse中创建两张表
2.如果Hadoop集群压力比较大，在读取HDFS数据的时候会出现超时的情况方法一： Waterdrop

使用Waterdrop将HDFS中的Nginx日志文件导入ClickHouse中。仅通过一个配置文件便可快速完成数据的导入，无需编写任何代码。除了支持HDFS数据源之外，Waterdrop同样支持将数据从Kafka中实时读取处理写入ClickHouse中。

Waterdrop是一个非常易用，高性能，能够应对海量数据的实时数据处理产品，它构建在Spark之上。Waterdrop拥有着非常丰富的插件，支持从Kafka、HDFS、Kudu中读取数据，进行各种各样的数据处理，并将结果写入ClickHouse、Elasticsearch或者Kafka中。

Log Sample

我们在HDFS中存储的日志格式如下， 是很常见的Nginx日志

10.41.1.28 github.com 114.250.140.241 0.001s "127.0.0.1:80" [26/Oct/2018:03:09:32 +0800] "GET /InterestingLab/waterdrop HTTP/1.1" 200 0 "-" - "Dalvik/2.1.0 (Linux; U; Android 7.1.1; OPPO R11 Build/NMF26X)" "196" "-" "mainpage" "443" "-" "172.16.181.129"

ClickHouse Schema

我们的ClickHouse建表语句如下，我们的表按日进行分区

CREATE TABLE cms.cms_msg
(
    date Date, 
    datetime DateTime, 
    url String, 
    request_time Float32, 
    status String, 
    hostname String, 
    domain String, 
    remote_addr String, 
    data_size Int32, 
    pool String
) ENGINE = MergeTree PARTITION BY date ORDER BY date SETTINGS index_granularity = 16384

首先我们需要安装Waterdrop，安装十分简单，无需配置系统环境变量

1. 准备Spark环境
2. 安装Waterdrop
3. 配置Waterdrop

cd /usr/local
wget https://archive.apache.org/dist/spark/spark-2.2.0/spark-2.2.0-bin-hadoop2.7.tgz
tar -xvf https://archive.apache.org/dist/spark/spark-2.2.0/spark-2.2.0-bin-hadoop2.7.tgz
wget https://github.com/InterestingLab/waterdrop/releases/download/v1.1.1/waterdrop-1.1.1.zip
unzip waterdrop-1.1.1.zip
cd waterdrop-1.1.1

vim config/waterdrop-env.sh
# 指定Spark安装路径
SPARK_HOME=${SPARK_HOME:-/usr/local/spark-2.2.0-bin-hadoop2.7}

Waterdrop Pipeline

我们仅需要编写一个Waterdrop Pipeline的配置文件即可完成数据的导入。

配置文件包括四个部分，分别是Spark、Input、filter和Output。

vim config/batch.conf

#这一部分是Spark的相关配置，主要配置Spark执行时所需的资源大小
spark {
  spark.app.name = "Waterdrop"
  spark.executor.instances = 2
  spark.executor.cores = 1
  spark.executor.memory = "1g"
}

#这一部分定义数据源，如下是从HDFS文件中读取text格式数据的配置案例
input {
    hdfs {
        path = "hdfs://nomanode:8020/rowlog/accesslog"
        table_name = "access_log"
        format = "text"
    }
    # hive {
    #     pre_sql = "select A,B,C from nginx_msg_detail"  //hive的查询语句，一般可以为select *
    #     table_name = "nginx_msg_detail" 
    # }
}

#在Filter部分，这里我们配置一系列的转化，包括正则解析将日志进行拆分、时间转换将HTTPDATE转化为ClickHouse支持的日期格式、对Number类型的字段进行类型转换以及通过SQL进行字段筛减等
filter {
    # 使用正则解析原始日志
    grok {
        source_field = "raw_message"
        pattern = '%{IP:ha_ip}\\s%{NOTSPACE:domain}\\s%{IP:remote_addr}\\s%{NUMBER:request_time}s\\s\"%{DATA:upstream_ip}\"\\s\\[%{HTTPDATE:timestamp}\\]\\s\"%{NOTSPACE:method}\\s%{DATA:url}\\s%{NOTSPACE:http_ver}\"\\s%{NUMBER:status}\\s%{NUMBER:body_bytes_send}\\s%{DATA:referer}\\s%{NOTSPACE:cookie_info}\\s\"%{DATA:user_agent}\"\\s%{DATA:uid}\\s%{DATA:session_id}\\s\"%{DATA:pool}\"\\s\"%{DATA:tag2}\"\\s%{DATA:tag3}\\s%{DATA:tag4}'
    }
    # 将"dd/MMM/yyyy:HH:mm:ss Z"格式的数据转换为
    # "yyyy/MM/dd HH:mm:ss"格式的数据
    date {
        source_field = "timestamp"
        target_field = "datetime"
        source_time_format = "dd/MMM/yyyy:HH:mm:ss Z"
        target_time_format = "yyyy/MM/dd HH:mm:ss"
    }
    # 使用SQL筛选关注的字段，并对字段进行处理
    # 甚至可以通过过滤条件过滤掉不关心的数据
    sql {
        table_name = "access"
        sql = "select substring(date, 1, 10) as date, datetime, hostname, url, http_code, float(request_time), int(data_size), domain from access"
    }
}

#最后我们将处理好的结构化数据写入ClickHouse
output {
    clickhouse {
        host = "your.clickhouse.host:8123"
        database = "waterdrop"
        table = "access_log"
        fields = ["date", "datetime", "hostname", "uri", "http_code", "request_time", "data_size", "domain"]
        username = "username"
        password = "password"
    }
}

执行命令，指定配置文件，运行Waterdrop，即可将数据写入ClickHouse。这里我们以本地模式为例。

./bin/start-waterdrop.sh --config config/batch.conf -e client -m 'local[2]'

方法二：将Hive中的数据导出为csv格式后，csv 文件导入到clickhouse当中

缺点
1.数据量大的时候导出时间比较长

第一步 生成csv文件

选择合适的分隔符；大部分情况下，会选择逗号或者hive的默认分隔符\001 ,可以参考文件 详解hive的列分隔符和行分隔符的使用

情况一：由于存储中可能含有分割符，导致分割列的时候，会多出几列的情况；比如说本来是5列的情况，但是却分割出来6列的情况，就是因为数据中含有分隔符的情况，所有如果出现该情况，需要对于可能出现情况的列进行特殊处理

regexp_replace(t.column_name,'\t|\n|\001','') as column_name,

特别注意 列中的换行符以及tab符号,需要处理掉，否则也会出现一些问题；

情况二 ：clickhouse中不能出现null需要对所有可能的列，进行如下处理

 ifnull(tm.column_name,0) as column_name,

​ 情况三：因为hive导出之后，原来的bigint，会出现10.0的情况，在有些情况下，需要进行截断

​ 情况四： hive中的列的属性，和clickhouse中列的属性不一致会出现问题；对于clickhouse多列的情况下，一定要细致，一个个列的属性进行校验

​ Float-->bigInt出现错误

​ int8--->Uint8之间转换错误；

第二步 csv导入到clickhouse当中；

csv导入clickhouse的方式，常见的有两种方式；通过clickhouse的jdbc执行sql和使用clickhouse的clickhouse-client客户端的方式，数据量大的情况下，切记不要用第一种方式，一定要切记；切记

方式一： 写一个Python脚本，pandas，以及整合了csv的写入的实现，数据量不大的时候，小于500百万，列在50列左右的时候，可以使用

方式二：

//需要加入列名，推荐使用
cat ~/csv_fileName.csv| clickhouse-client  --host=ip --port=19000 --user=username --password=pwd --format_csv_delimiter=$'\001' --query="INSERT INTO table_name FORMAT CSVWithNames"
//真正的数据
cat ~/csv_fileName.csv| clickhouse-client  --host=ip --port=19000 --user=username --password=pwd --format_csv_delimiter=$'\001' --query="INSERT INTO table_name FORMAT CSV"

常见错误：

Code: 27. DB::Exception: Cannot parse input: expected , before:

• clickhouse列的属性和hive不匹配，包括类型和列的个数
• clickhouse 时间类型

方法三：clickhouse-client

在能直接读取HDFS数据的主机上安装clickhouse-client，读取HDFS数据后直接插入ClickHouse。

#!/bin/bash
#######################################
#  功能：同步hdfs数据到clickhouse
#  usage：sh  shell_name  table_name
#######################################

current_dir=$(cd `dirname $0`; pwd)

# 获取Hive表在HDFS上的存储路径
table_name=$1
input_file=`hadoop fs -ls  /warehouse/tablespace/hive/db01/${table_name}/*/*|awk '{print $8}'>${current_dir}/input_file.txt`

# 定义Clickhouse 数据源连接
ch="clickhouse-client -h 172.18.xxx.xxx  --port 9090 -u xxx --password xxx --query "

# 将Hive存储在Hdfs上的数据同步到clickhouse
for i in `cat ${current_dir}/${input_file}`
do
   echo `date +"%Y-%m-%d %H:%m:%S"`  '开始执行 .....' $i
   hadoop fs -text $i|${ch} "insert into xxx.xxx  format CSV" 
   echo `date +"%Y-%m-%d %H:%m:%S"`  '结束执行 .....' $i
done