hive 导出json格式 文件_hive存储json格式文件

hive从0.12版本以后就开始自身支持json文件的格式了

1.文件格式

下面是测试用的文件预览格式,我将其存储为jsonTest.json文件,放在了/root目录下

{"_location":"(32.121, 45.262)","_ip":"2.12.1.211","_action":"click button","_uid":"694666ee-8918-48f8-bb92-0a756a3f1f31","_timestamp":"1496999163163"}

{"_location":"(32.121, 45.262)","_ip":"7.12.1.211","_action":"click button","_uid":"868be529-f59e-4f16-8ea5-08c4612ede9a","_timestamp":"1496999164165"}

{"_location":"(32.121, 45.262)","_ip":"2.12.1.211","_action":"click button","_uid":"49b63380-2a5a-453d-aa5b-57e74cd1a8d7","_timestamp":"1496999165170"}

{"_location":"(32.121, 45.262)","_ip":"3.12.1.211","_action":"click button","_uid":"60fd093a-9f6a-4716-81c6-72a5f07d3b4f","_timestamp":"1496999166171"}

{"_location":"(32.121, 45.262)","_ip":"3.12.1.211","_action":"click button","_uid":"056cf558-56b1-4312-aba1-0b0eb71ba78e","_timestamp":"1496999167172"}

{"_location":"(32.121, 45.262)","_ip":"8.12.1.211","_action":"click button","_uid":"a2ecd34c-5458-43a2-b851-89eb6b3985ae","_timestamp":"1496999168174"}

注意: 一行之内必须是完整的一条json文件,否则会解析出错误

2.创建hive表

CREATE TABLE IF NOT EXISTS jsonTest

(

`_location` STRING,

`_ip` STRING,

`_action` STRING,

`_uid` STRING,

`_timestamp` STRING)

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

STORED AS TEXTFILE;

注意: “符号是因为列明里面是下划线开头;并且列明需要与json文件中的key对应,否则也会解析不到数据.

3.加载数据

LOAD DATA LOCAL INPATH "/root/jsonTest.json" OVERWRITE INTO TABLE jsonTest;

4.查询数据进行校验

hive> select * from jsonTest_test;

OK

(32.121, 45.262) 2.12.1.211 click button 54490a2f-376a-43e3-9658-e9a3d6583ffd 1496999162153

(32.121, 45.262) 2.12.1.211 click button 694666ee-8918-48f8-bb92-0a756a3f1f31 1496999163163

(32.121, 45.262) 7.12.1.211 click button 868be529-f59e-4f16-8ea5-08c4612ede9a 1496999164165

(32.121, 45.262) 2.12.1.211 click button 49b63380-2a5a-453d-aa5b-57e74cd1a8d7 1496999165170

(32.121, 45.262) 3.12.1.211 click button 60fd093a-9f6a-4716-81c6-72a5f07d3b4f 1496999166171

(32.121, 45.262) 3.12.1.211 click button 056cf558-56b1-4312-aba1-0b0eb71ba78e 1496999167172

(32.121, 45.262) 8.12.1.211 click button a2ecd34c-5458-43a2-b851-89eb6b3985ae 1496999168174

Time taken: 0.439 seconds, Fetched: 7 row(s)

可以看到数据已经成功解析出来

问题

1.出现下面错误

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Cannot validate serde: org.apache.hive.hcatalog.data.JsonSerDe

需要将支持序列化的hive-hcatalog-core.jar添加进来,执行如下命令即可解决该问题.(找到自己安装的目录即可,我的是在这个目录里)

hive> add jar /opt/cloudera/parcels/CDH-5.11.0-1.cdh5.11.0.p0.34/lib/hive-hcatalog/share/hcatalog/hive-hcatalog-core.jar;