Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

New Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

New Contributor

@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; 
3 REPLIES 3
Highlighted

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

Guru

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?

New Contributor

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?

New Contributor

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