sql - 在 Hive 中创建 View 表

Question

我有两个数据集。第一个 android 集有 10 列，第二个 ios 集有 10 列，其中 6 列相似，但其余 4 列不同。在每个数据集中的这 4 列中，一列与其各自的操作系统相关，我的意思是 android和 ios。现在我需要通过组合这两个表来创建一个配置单元 View ，这样当我运行 View 查询时，最终的聚合表应该如下所示

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

因此，可以通过将名为数据的相同列包含到一个字段中来最小化列长度，并且与其他数据不同的其余数据应该如上所述连接到列。

Answer 1

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

演示

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     |
+-----------+-------------+-------------+-------------+-------------+-------------+-------------+--------------+--------------+--------------+----------+----------+----------+