@vj pan, you can use lateral view in your CTAS query to separate the fields from Details_str.
You can create table like this in database A create table databaseA.testtable(
event_ts timestamp,
cust_id int,
ban int,
detail_str string);
Then, use following CTAS query with lateral view to create second table in database B create table databaseB.testtable as
select event_ts,
cust_id,
ban,
detail_str,
lv_detail_str.type_cd,
lv_detail_str.head_id,
lv_detail_str.out_id,
lv_detail_str.org_type_des
from databaseA.testtable a
lateral view
json_tuple(a.detail_str,'type_cd','head_id','out_id','org_type_des') lv_detail_str as
type_cd,
head_id,
out_id,
org_type_des;
... View more
