复杂嵌套的Json使用Hive

Question

复杂嵌套的Json使用Hive

4

我有一个原始的输入JSON片段（'/home/user/testsample.json'） -

{"key": "somehashvalue","columns": [["Event:2014-03-26 00\\:29\\:13+0200:json","{\"user\":{\"credType\":\"ADDRESS\",\"credValue\":\"01:AA:A4:G1:HH:UU\",\"cAgent\":null,\"cType\":\"ACE\"},\"timestamp\":1395786553,\"sessionId\":1395785353,\"className\":\"Event\",\"subtype\":\"CURRENTLYACTIVE\",\"vType\":\"TEST\",\"vId\":1235080,\"eType\":\"CURRENTLYACTIVE\",\"eData\":\"1\"}",1395786553381001],["Event:2014-03-26 00\\:29\\:13+0200:","",1395786553381001]]}

我尝试使用Json SerDe解析上述json到我的Hive列。但是，1395786553381001不以SerDe可以映射到Hive列的格式呈现，即此值存在于没有Key的情况下（因为Hive理解在：之后呈现的Json列/值）。

因此，我采用了Array类型方法并创建了一个表 -

CREATE TABLE mytesttable (
  key string, 
  columns array < array< string > >
  )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe';

LOAD DATA LOCAL INPATH '/home/user/testsample.json'
OVERWRITE INTO TABLE mytesttable;

从 mytesttable 选择列 [0] [1]；得到 -

{"user":{"credType":"ADDRESS","credValue":"01:AA:A4:G1:HH:UU","cAgent":null,"cType":"ACE"},"timestamp":1395786553,"sessionId":1395785353,"className":"Event","subtype":"CURRENTLYACTIVE","vType":"TEST","vId":1235080,"eType":"CURRENTLYACTIVE","eData":"1"}

上述内容看起来很清晰，但是我还需要[*][2]列，即在Json hive列中进行进一步转换。

我编写了一个正则表达式hive查询，以清理位于'/home/user/testsample.json'中的原始Json（假设它位于tablewithinputjson表中）

SELECT
REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(ij.columna, '["][{]', '{'),'[}]["]', '}'), '\\\\', '') AS columna
FROM tablewithinputjson ij;

上面的查询返回 -

{"key": "somehashvalue","columns": [["Event:2014-03-26 00:29:13+0200:json",{"user":{"credType":"ADDRESS","credValue":"01:AA:A4:G1:HH:UU","cAgent":null,"cType":"ACE"},"timestamp":1395786553,"sessionId":1395785353,"className":"Event","subtype":"CURRENTLYACTIVE","vType":"TEST","vId":1235080,"eType":"CURRENTLYACTIVE","eData":"1"},1395786553381001],["Event:2014-03-26 00:29:13+0200:","",1395786553381001]]}

但是，1395786553381001不能映射到一个hive列，因为它出现在逗号之后，而不是冒号之后，更具体地说，这个值出现在没有键的情况下。(我可以在1395786553381001之前添加“test”：，但我不想定制输入数据 - 因为a)过多的定制是我不舒服的事情，b)看起来不是一个好的解决方案，c)这将不必要地浪费我的hadoop集群空间和时间)

为了避免进一步混淆，我无法提供一个完全解析并映射原始Json片段中所有字段的Hive表格式。欢迎任何建议。如果看起来太令人困惑，请让我知道。

- gomzy

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Aditya · Accepted Answer

发布端到端解决方案。将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-dependencies.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;

第七步：在$HOME目录下创建名为books.json的json文件（示例）。

{"value": [{"id": "1","bookname": "A","properties": {"subscription": "1year","unit": "3"}},{"id": "2","bookname":"B","properties":{"subscription": "2years","unit": "5"}}]}

第八步) 在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执行explode操作，这个中间步骤是将多级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;

输出：

编号名称订阅时长价格

1 B 1年 3

2 B 2年 5