CODEWITHSUNDEEP
sqlhadoophivehiveql
Vizram
Vizram

Reputation: 25

creating view table in Hive

I have two data sets. 1 st android set is having 10 columns and the second ios set is having 10 columns among those columns 6 are similar but rest of 4 are different.Among those 4 columns in each data set, one column is related to their respective OS I mean android and ios.Now I need to create a hive view by combining those two tables such that when I run view query the final aggregation table should look like this

View_table:

os_7|1|2|3|4|5|6|8_android_different|9_and_dif|10_and_dif|8_ios_dif|9_ios_dif|10_ios_dif|

android All the android data from 1-6 should come here
ios     All the ios data from 1-6 should come here
ios
android
android

So that the column length can be minimized by including the same column named data into one field and the rest of the data which is different from one other should join to the columns as mentioned above.

Upvotes: 0

Views: 3257

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44961

create view os
as
select  os
       ,common_1
       ,common_2
       ,common_3
       ,common_4
       ,common_5
       ,common_6
       ,android_1
       ,android_2
       ,android_3
       ,null    as ios_1
       ,null    as ios_2
       ,null    as ios_3 

from    os_android

union all

select  os
       ,common_1
       ,common_2
       ,common_3
       ,common_4
       ,common_5
       ,common_6
       ,null    as android_1
       ,null    as android_2
       ,null    as android_3
       ,ios_1
       ,ios_2
       ,ios_3 

from    os_ios

Demo

create table os_android
(
    os          string
   ,common_1    string
   ,common_2    string
   ,common_3    string
   ,common_4    string
   ,common_5    string
   ,common_6    string
   ,android_1   string
   ,android_2   string
   ,android_3   string
);

create table os_ios
(
    os          string
   ,common_1    string
   ,common_2    string
   ,common_3    string
   ,common_4    string
   ,common_5    string
   ,common_6    string
   ,ios_1       string
   ,ios_2       string
   ,ios_3       string
)
;

create view os
as
select  os
       ,common_1
       ,common_2
       ,common_3
       ,common_4
       ,common_5
       ,common_6
       ,android_1
       ,android_2
       ,android_3
       ,null    as ios_1
       ,null    as ios_2
       ,null    as ios_3 

from    os_android

union all

select  os
       ,common_1
       ,common_2
       ,common_3
       ,common_4
       ,common_5
       ,common_6
       ,null    as android_1
       ,null    as android_2
       ,null    as android_3
       ,ios_1
       ,ios_2
       ,ios_3 

from    os_ios
;

insert into os_android values 
    ('ANDROID_1','C1_1','C2_1','C3_1','C4_1','C5_1','C6_1','A1_1','A2_1','A3_1')
   ,('ANDROID_2','C1_2','C2_2','C3_2','C4_2','C5_2','C6_2','A1_2','A2_2','A3_2')
;

insert into os_ios values 
    ('IOS_3','C1_3','C2_3','C3_3','C4_3','C5_3','C6_3','I1_3','I2_3','I3_3')
   ,('IOS_4','C1_4','C2_4','C3_4','C4_4','C5_4','C6_4','I1_4','I2_4','I3_4')
;

select * from os_android
;

+---------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+----------------------+----------------------+----------------------+
| os_android.os | os_android.common_1 | os_android.common_2 | os_android.common_3 | os_android.common_4 | os_android.common_5 | os_android.common_6 | os_android.android_1 | os_android.android_2 | os_android.android_3 |
+---------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+----------------------+----------------------+----------------------+
| ANDROID_1     | C1_1                | C2_1                | C3_1                | C4_1                | C5_1                | C6_1                | A1_1                 | A2_1                 | A3_1                 |
| ANDROID_2     | C1_2                | C2_2                | C3_2                | C4_2                | C5_2                | C6_2                | A1_2                 | A2_2                 | A3_2                 |
+---------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+----------------------+----------------------+----------------------+

select * from os_ios
;

+-----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+--------------+--------------+--------------+
| os_ios.os | os_ios.common_1 | os_ios.common_2 | os_ios.common_3 | os_ios.common_4 | os_ios.common_5 | os_ios.common_6 | os_ios.ios_1 | os_ios.ios_2 | os_ios.ios_3 |
+-----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+--------------+--------------+--------------+
| IOS_3     | C1_3            | C2_3            | C3_3            | C4_3            | C5_3            | C6_3            | I1_3         | I2_3         | I3_3         |
| IOS_4     | C1_4            | C2_4            | C3_4            | C4_4            | C5_4            | C6_4            | I1_4         | I2_4         | I3_4         |
+-----------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+--------------+--------------+--------------+

select * from os
;

+-----------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+--------------+----------+----------+----------+
|   os.os   | os.common_1 | os.common_2 | os.common_3 | os.common_4 | os.common_5 | os.common_6 | os.android_1 | os.android_2 | os.android_3 | os.ios_1 | os.ios_2 | os.ios_3 |
+-----------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+--------------+----------+----------+----------+
| ANDROID_1 | C1_1        | C2_1        | C3_1        | C4_1        | C5_1        | C6_1        | A1_1         | A2_1         | A3_1         | NULL     | NULL     | NULL     |
| ANDROID_2 | C1_2        | C2_2        | C3_2        | C4_2        | C5_2        | C6_2        | A1_2         | A2_2         | A3_2         | NULL     | NULL     | NULL     |
| IOS_3     | C1_3        | C2_3        | C3_3        | C4_3        | C5_3        | C6_3        | NULL         | NULL         | NULL         | I1_3     | I2_3     | I3_3     |
| IOS_4     | C1_4        | C2_4        | C3_4        | C4_4        | C5_4        | C6_4        | NULL         | NULL         | NULL         | I1_4     | I2_4     | I3_4     |
+-----------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+--------------+----------+----------+----------+

Upvotes: 1

Related Questions