如何让Hive表支持json数据

 

关键信息

根据hive官方文档：

hive支持json数据需要hive内置JsonSerDe类

JsonSerDe 适用版本：  Hive 0.12+
JsonSerDe类所在jar包：hive-hcatalog-core.jar

操作

添加jar

要使用.JsonSerDe，需要添加相应的jar包

ADD JAR /usr/lib/hive-hcatalog/lib/hive-hcatalog-core.jar;

这是官方给的路径，可能服务器在该路径下不存在这个jar，比如我安装的是CDH版本的hive，该路径下就找不到这个jar

我的jar包路径

ADD JAR /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/jars/hive-hcatalog-core-1.1.0-cdh5.16.2.jar
寻找方法在博客末尾

 

指定SerDe类
 

创建hive表时，如果希望该表的数据为json格式，就需要在创建的时候指定SerDe类

CREATE TABLE my_table(a string, b bigint, ...)

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'

STORED AS TEXTFILE;

注意：

从hive3.x 开始 , JsonSerDe 类的全名就不是上面那样了，而是变成了 org.apache.hadoop.hive.serde2.JsonSerDe

具体详见 HIVE-19211

CREATE TABLE my_table(a string, b bigint, ...)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'

 

附录：

1 查找 hive-hcatalog-core.jar包

find /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/ -name hive-hcatalog-core.jar 
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hbase-solr/lib/hive-hcatalog-core.jar
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/impala/lib/hive-hcatalog-core.jar
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core.jar
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/sentry/lib/hive-hcatalog-core.jar
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/oozie/libserver/hive-hcatalog-core.jar
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/oozie/libtools/hive-hcatalog-core.jar
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/oozie/oozie-sharelib-mr1/lib/hcatalog/hive-hcatalog-core.jar
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/oozie/oozie-sharelib-mr1/lib/sqoop/hive-hcatalog-core.jar
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/oozie/oozie-sharelib-yarn/lib/hcatalog/hive-hcatalog-core.jar
/opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/oozie/oozie-sharelib-yarn/lib/sqoop/hive-hcatalog-core.jar

# 有很多jar包是软连接，需要找到真正的hcatalog jar
[root@cdh03 ~]# ll -h /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hbase-solr/lib/hive-hcatalog-core.jar
lrwxrwxrwx 1 root root 64 Jun  3  2019 /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hbase-solr/lib/hive-hcatalog-core.jar -> ../../../lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core.jar
[root@cdh03 ~]# ll -h /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/impala/lib/hive-hcatalog-core.jar 
lrwxrwxrwx 1 root root 64 Jun  3  2019 /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/impala/lib/hive-hcatalog-core.jar -> ../../../lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core.jar
[root@cdh03 ~]# ll /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hive
hive/          hive-hcatalog/ 
[root@cdh03 ~]# ll /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hive
hive/          hive-hcatalog/ 
[root@cdh03 ~]# ll /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core
hive-hcatalog-core-1.1.0-cdh5.16.2.jar  hive-hcatalog-core.jar                  
[root@cdh03 ~]# ll /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core.jar 
lrwxrwxrwx 1 root root 38 Jun  3  2019 /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core.jar -> hive-hcatalog-core-1.1.0-cdh5.16.2.jar
[root@cdh03 ~]# ll /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core-1.1.0-cdh5.16.2.jar 
lrwxrwxrwx 1 root root 55 Jun  3  2019 /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core-1.1.0-cdh5.16.2.jar -> ../../../../jars/hive-hcatalog-core-1.1.0-cdh5.16.2.jar
[root@cdh03 ~]# ll /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/jars/hive-hcatalog-core-1.1.0-cdh5.16.2.jar
-rw-r--r-- 1 root root 255524 Jun  3  2019 /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/jars/hive-hcatalog-core-1.1.0-cdh5.16.2.jar
[root@cdh03 ~]# ll -h /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/jars/hive-hcatalog-core-1.1.0-cdh5.16.2.jar
-rw-r--r-- 1 root root 250K Jun  3  2019 /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/jars/hive-hcatalog-core-1.1.0-cdh5.16.2.jar

2 查找JsonSerDe类

# 查找JsonSerDe类
[root@cdh03 ~]# jar tvf /opt/cloudera/parcels/CDH-5.16.2-1.cdh5.16.2.p0.8/jars/hive-hcatalog-core-1.1.0-cdh5.16.2.jar > hcatalog.log 
[root@cdh03 ~]# ll
total 28
-rw-------. 1 root   root    1826 Mar  8 11:45 anaconda-ks.cfg
-rw-r--r--. 1 hadoop hadoop    19 May  3 11:33 a.txt
-rw-r--r--. 1 root   root     517 May  2 18:14 firewall.rules
-rw-r--r--  1 root   root   13065 May 30 11:31 hcatalog.log
[root@cdh03 ~]# grep JsonSerDe hcatalog.log
  3782 Mon Jun 03 03:49:22 CST 2019 org/apache/hive/hcatalog/data/JsonSerDe$1.class
 23139 Mon Jun 03 03:49:22 CST 2019 org/apache/hive/hcatalog/data/JsonSerDe.class