Hive 解析 JSON 数据

Question

这里使用 Read - Write JSON SerDe for Apache Hive 来在 hive 里使用 JSON​。

分两个 theme:

• hive
• hadoop

Hive 这块安装：

$ cd $HIVE_HOME/lib
$ wget  http://www.congiu.net/hive-json-serde/1.3.8/cdh5/json-serde-1.3.8-jar-with-dependencies.jar 
$ wget  http://www.congiu.net/hive-json-serde/1.3.8/cdh5/json-udf-1.3.8-jar-with-dependencies.jar 

数据结构是这样的：

{
    "_ip":"39.107.245.223",
    "ua":"Windows NT 6.1; WOW64",
    "timestamp":1536649070251,
    "message":{
        "event":"trace"
    },
    "uid":"abcde"
}

创建 schema

CREATE EXTERNAL TABLE `demo`( 
    `ua` string,
    `timestamp` string,
    `message` map<string, string>,
    `uid` string
)
PARTITIONED BY (`dt` string) 
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ("ignore.malformed.json" = "true")
STORED AS TEXTFILE;

添加 jar 到 hive classpath 中

hive> add jar /opt/hive/apache-hive-2.2.0-bin/lib/json-udf-1.3.8-jar-with-dependencies.jar;
hive > add jar /opt/hive/apache-hive-2.2.0-bin/lib/json-serde-1.3.8-jar-with-dependencies.jar;

添加分区：

alter table demo add partition(dt=20180911) LOCATION '/data/events/demo/dt=20180911'

Hadoop 需要直接把 jar 包放在 $HADOOP_HOME/share/hadoop/mapreduce/ 下即可，否则会出现如下问题，如聚合查询 hive

hive> SELECT count(*) FROM demo WHERE dt='20181010';

Caused by: java.lang.ClassNotFoundException: Class org.openx.data.jsonserde.JsonSerDe not found
        at org.apache.hadoop.conf.Configuration.getClassByName(Configuration.java:2103)
        at org.apache.hadoop.hive.ql.plan.PartitionDesc.getDeserializer(PartitionDesc.java:177)
        at org.apache.hadoop.hive.ql.exec.MapOperator.getConvertedOI(MapOperator.java:295)
        ... 24 more