发布端到端解决方案。将 JSON 转换为 Hive 表的分步过程:
步骤1)安装maven(如果尚未安装)
>$ sudo apt-get install maven
步骤 2)安装 git(如果尚未安装)
>sudo git clone https://github.com/rcongiu/Hive-JSON-Serde.git
步骤3)进入$HOME/HIVE-JSON Serde文件夹
步骤4)构建serde包
>sudo mvn -Pcdh5 clean package
步骤5)serde文件将位于$HOME/Hive-JSON-Serde/json-serde/target/json-serde-1.3.7-SNAPSHOT-jar-with-dependency.jar
步骤6)将serde添加为hive中的依赖jar
hive> ADD JAR $HOME/Hive-JSON-Serde/json-serde/target/json-serde-1.3.7- SNAPSHOT-jar-with-dependencies.jar;
步骤7)在$HOME/books.json中创建json文件(示例)
{"value": [{"id": "1","bookname": "A","properties": {"subscription": "1year","unit": "3"}},{"id": "2","bookname":"B","properties":{"subscription": "2years","unit": "5"}}]}
步骤8)在hive中创建tmp1表
hive>CREATE TABLE tmp1 (
value ARRAY<struct<id:string,bookname:string,properties:struct<subscription:string,unit:string>>>
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'mapping.value' = 'value'
)
STORED AS TEXTFILE;
步骤9)将数据从json加载到tmp1表
>LOAD DATA LOCAL INPATH '$HOME/books.json' INTO TABLE tmp1;
步骤10)创建一个tmp2表来对tmp1进行爆炸操作,这个中间步骤是将多级json结构分解为多行
注意:如果您的 JSON 结构简单且单层,请避免此步骤
hive>create table tmp2 as
SELECT *
FROM tmp1
LATERAL VIEW explode(value) itemTable AS items;
步骤 11) 创建 hive 表并从 tmp2 表加载值
hive>create table books as
select value[0].id as id, value[0].bookname as name, value[0].properties.subscription as subscription, value[0].properties.unit as unit from tmp2;
步骤 12) 删除临时表
hive>drop table tmp1;
hive>drop table tmp2;
步骤13)测试Hive表
hive>select * from books;
output:
id 名称 订阅单位
1 B 1 年 3
2 B 2年5