question Re: I need help to create Hive table using datalake point toad? in Support Questions

I need help to create Hive table using datalake point toad?

vijaypatel4624 — Thu, 26 Jan 2017 00:23:14 GMT

Hi I have hive table in one of the database A and want to create table in database B in hive.

sample field and data:-

event_ts - 17/04/2016 2:40:07 am

cust_id - 30122

ban - 001

Detail_str - {"type_cd":"A","head_id":"283680","out_id":"1111","org_type_des":"Koo"}

I want to copy this table and create hive table in database B like this

event_ts

cust_id

ban

Detail_str

type_cd

head_id

out_id

org_type_des

So what kinds of query I need to generate to create Hive table in Database B?

Thanks

Re: I need help to create Hive table using datalake point toad?

ravi1 — Thu, 26 Jan 2017 00:38:56 GMT

Something like below command should work as long as both databases are in same Hive instance.

create table databaseB.testtable as select * from databaseA.testtable

Re: I need help to create Hive table using datalake point toad?

vijaypatel4624 — Thu, 26 Jan 2017 00:44:33 GMT

Detail_str - {"type_cd":"A","head_id":"283680","out_id":"1111","org_type_des":"Koo"}

how Can I separate the fields from this string? key and value

Re: I need help to create Hive table using datalake point toad?

jvaria — Thu, 26 Jan 2017 07:42:50 GMT

@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;