hadoop - Apache Hive 不同的查询联合，在同一个表上，每个查询中的条件不同？

Question

我有一个名为“sales”的 Hive 表，结构如下:

id,ptype,amount,time,date
1,a,12,2240,2013-12-25
1,a,4,1830,2013-12-25
1,b,2,1920,2013-12-25
1,b,3,2023,2013-12-25
2,a,5,1220,2013-12-25
2,a,1,1320,2013-12-25

下面是我对不同变量变量的查询:

Q1: select id,sum(amount) as s_amt from sales group by id;
Q2: select id, sum(amount) as s_a_amt from sales where ptype='a' group by id;
Q3: select id, sum(amount) as s_b_amt from sales where ptype='b' group by id;

就我在 Hive 中学到的知识而言，只有当我们具有相同的列名或查询模式时，我们才能应用“联合所有”选项。以下是我希望使用 Hive 查询实现的最终结果:

id,s_amt,s_a_amt,s_b_amt
1,21,16,5
2,6,6,0

下面是我尝试过并成功执行的一个查询。但是当你必须为超过 300 个变量设计同一个查询时，这将是一个非常痛苦的任务。考虑到我们有 300 多个变量，对于同一任务是否有任何有效的方法？感谢您的评论!

    select t.id,max(t.s_amt) as s_amt,max(t.s_a_amt) as s_a_amt, max(t.s_b_amt) as s_b_amt
  from
     (select s1.id,sum(amount) as s_amt,0 as s_a_amt,0 as s_b_amt from sales s1 group by id union all
     select s2.id, 0 as s_amt, sum(amount) as s_a_amt, 0 as s_b_amt from sales s2 where ptype='a' group by id union all
     select s3.id, 0 as s_amt,0 as s_a_amt, sum(amount) as s_b_amt from sales s3 where ptype='b' group by id) t
group by t.id;

Answer 1

理想的解决方案是拥有一个

Materialised Query Table (MQT) as IBM refers.

汇总表是 MQT 的特殊形式，而这正是您所需要的。快速定义 - 顾名思义，MQT 是一个简单的汇总表，具体化在磁盘上。

有了 MQT 支持，您只需执行以下操作

CREATE MATERIALISED QUERY TABLE MQTA AS (
select id, sum(amount) as s_a_amt from sales where ptype='a' group by id;
)
Data initially deferred 
Refresh deferred
Maintained by User

Data initially deferred表示不向汇总表中插入汇总记录。 Refresh deferred 表示可以随时使用 REFRESH TABLE 语句刷新表中的数据。 Maintained by user 表示此表的 Refersh 必须由用户处理 - Maintained by System 是另一个选项，系统负责自动更新汇总表基表看到插入/删除//更新。

您可以像简单的选择查询一样直接查询 MQT，汇总记录的所有繁重工作实际上会在您查询 MQT 之前而不是在查询 MQT 时运行，因此速度会快得多。

但是 AFAIK HIVE 不支持 MQT 或汇总表。

你现在知道这个概念了，你只需要简单地模拟这个。创建一个汇总表并插入汇总记录(REFRESH TABLE 概念)。您必须通过使用某种上次加载日期字段进行控制来定期加载汇总值，这样您将仅在上次刷新后获取记录。您可以使用计划作业 - Hive 脚本来执行此操作。

INSERT INTO PTYPE_AMOUNT_MQT AS (
select *
  from
     (select s1.id,sum(amount) as s_amt,0 as s_a_amt,0 as s_b_amt from sales s1 where record_create_date > last_Refresh_date group by id union all
     select s2.id, 0 as s_amt, sum(amount) as s_a_amt, 0 as s_b_amt from sales s2 where ptype='a' and record_create_date > last_Refresh_date  group by id union all
     select s3.id, 0 as s_amt,0 as s_a_amt, sum(amount) as s_b_amt from sales s3 where ptype='b'  and record_create_date > last_Refresh_date group by id) 
)

拥有像 record_create_date 和 time 这样的审计字段总是好的。last_Refresh_date 是上次你的工作运行时间