在一个表中插入JSON数据到HIVE中的另一个表 [英] inserting JSON data from one table to another tables in HIVE
问题描述
我想根据数据上的关键字段将JSON数据从一个表格插入到其他表格中。 我的数据看起来像这样
lockquote
{Rtype:{ver:1,os:ms,type:ns,vehicle: MH-3412, MOD:{ 版本:[{ ABC:{ XYZ: 123.dfer, 方正: 3.0, GHT: 佛罗里达,时尚:fg45,cdc:new,dof:yes,ts:2000-04-01T00:00:00.171Z}}]}}}
{Rtype:{ver:1,os:ms,type:ns,vehicle:Mh-3412 MOD :{ 版本 :[{ GAP :{ XVY : 123.dfer FAH: 3.0, GHT: 佛罗里达, 时尚: fg45, cdc:new,dof:yes,ts:2000-04-01T00:00:00.171Z}}]}}}
{ 舍入类型:{ VER: 1, OS: MS, 类型: NS, 车辆: MH-3412, MOD:{ 版本 :[{ BOX:{ VOG: 123.dfer, FAH: 3.0, 传真: 佛罗里达, 时尚: fg45, CDC: 新, dof:是,ts:2000-04-01T00:00:00.171Z}}]}}}
这里基于版本,无论是BOX还是GAP或ABC,我都想填充字段例如,如果版本是GAP,则在一个表中填充特定的行,如果它是BOX,则填充到另一个表中。 。我的意思是所有的行...
我如何使用HIVE来实现这一点。
$ b 注意:我的JSON数据在一个表中作为字符串类型的列
演示
create table src(myjson string);
插入src值
('{Rtype:{ver:1,os:ms,type:ns,vehicle : MH-3412\" , MOD:{ 版本:[{ ABC:{ XYZ: 123.dfer, 方正: 3.0, GHT: 佛罗里达 fashion:fg45,cdc:new,dof:yes,ts:2000-04-01T00:00:00.171Z}}]}}}')
,('{Rtype:{ver:1,os:ms,type:ns,vehicle:Mh-3412,MOD 版本:[{ GAP:{ XVY: 123.dfer, FAH: 3.0, GHT: 佛罗里达, 时尚: fg45, CDC:新,dof:是,ts:2000-04-01T00:00:00.171Z}}]}}}')
,('{Rtype:{ver : 1\" , OS: MS, 类型: NS, 车辆: MH-3412, MOD:{ 版本:[{ BOX:{VOG : 123.dfer FAH: 3.0, 传真: 佛罗里达, 时尚: fg45, CDC: 新, 自由度: 是的, TS :2000-04-01T00:00:00.171Z}}]}}}')
;
create table trg_abc(myjson string);
create table trg_gap(myjson string);
create table trg_box(myjson string);
from src
插入trg_abc select myjson其中get_json_object(myjson,'$。Rtype.MOD.Version [0] .ABC')不为空
插入到trg_gap中select myjson where get_json_object(myjson,'$。 Rtype.MOD.Version [0] .GAP')不为空
插入到trg_box中select myjson其中get_json_object(myjson,'$。Rtype.MOD.Version [0] .BOX')不为空
;
I want to insert JSON data from one table to other tables based on the key fields on the data.
my data looks like this
{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"ABC":{"XYZ":"123.dfer","founder":"3.0","GHT":"Florida","fashion":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}
{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"GAP":{"XVY":"123.dfer","FAH":"3.0","GHT":"Florida","fashion":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}
{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"BOX":{"VOG":"123.dfer","FAH":"3.0","FAX":"Florida","fashion":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}
Here based on Version, whther it is "BOX" or "GAP" or "ABC" i want to populate the fields on particular JSON rows to another table
for ex: if version is "GAP" then populate particular rows in one table if it is "BOX" then populate to another tables... I mean all rows for BOX...
how can I achieve this using HIVE. Please help.
NOTE: My JSON data is in one table as a column with type string
Demo
create table src (myjson string);
insert into src values
('{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"ABC":{"XYZ":"123.dfer","founder":"3.0","GHT":"Florida","fashion":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}')
,('{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"GAP":{"XVY":"123.dfer","FAH":"3.0","GHT":"Florida","fashion":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}')
,('{"Rtype":{"ver":"1","os":"ms","type":"ns","vehicle":"Mh-3412","MOD":{"Version":[{"BOX":{"VOG":"123.dfer","FAH":"3.0","FAX":"Florida","fashion":"fg45","cdc":"new","dof":"yes","ts":"2000-04-01T00:00:00.171Z"}}]}}}')
;
create table trg_abc (myjson string);
create table trg_gap (myjson string);
create table trg_box (myjson string);
from src
insert into trg_abc select myjson where get_json_object(myjson,'$.Rtype.MOD.Version[0].ABC') is not null
insert into trg_gap select myjson where get_json_object(myjson,'$.Rtype.MOD.Version[0].GAP') is not null
insert into trg_box select myjson where get_json_object(myjson,'$.Rtype.MOD.Version[0].BOX') is not null
;
这篇关于在一个表中插入JSON数据到HIVE中的另一个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!