hiveSQL如何查看跑了100天的分区表的全量数据

Question

hiveSQL中，如何查看分区表的全量数据？我写的代码的问题是什么？
加入分区表已经运行了100天，我现在要输出这100天的所有数据，也就是全量。

我写的代码:

set hive.dynamic.partition=true;
set hive.dynamic.partition.mode=nonstrict;

select t.*
from 
(
    select *
    from table_a
  where etl_dt<=date_sub(current_date, 100)
  union all
    select *
    from table_b
  where etl_dt<=date_sub(current_date, 99)
) t

Answer 1

不知道你这个问题是否已经解决, 如果还没有解决的话:

• 你可以看下这个问题的回答https://ask.csdn.net/questions/7778009
• 这篇博客也不错, 你可以看下HiveSql一天一个小技巧：多指标累计去重问题
• 除此之外, 这篇博客: HiveSQL一天一个小技巧：如何精准计算非连续日期累计值【闪电快车面试题】中的 问题补充：如何将缺失日期及缺失值补充完整呢？ 部分也许能够解决你的问题, 你可以仔细阅读以下内容或者直接跳转源博客中阅读:
  

  直接根据相邻日期缺失的时间间隔，利用posexplode()函数将缺失日期及数据展开补齐，具体SQL如下：

  with data as
           (
               select 'AAAA' as contract, '2018-12-21' as value_date, 9439.30 as amount, 12 as term
               union all
               select 'AAAA' as contract, '2019-03-21' as value_date, 9439.30 as amount, 12 as term
               union all
               select 'AAAA' as contract, '2019-06-21' as value_date, 9439.30 as amount, 12 as term
               union all
               select 'AAAA' as contract, '2019-09-21' as value_date, 9439.30 as amount, 12 as term
               union all
               select 'BBBB' as contract, '2018-12-02' as value_date, 9439.30 as amount, 10 as term
               union all
               select 'BBBB' as contract, '2019-02-02' as value_date, 9439.30 as amount, 10 as term
               union all
               select 'BBBB' as contract, '2019-06-02' as value_date, 9439.30 as amount, 10 as term
               union all
               select 'BBBB' as contract, '2019-09-02' as value_date, 9439.30 as amount, 10 as term
           )
  select contract,
         add_months(value_date, pos) value_date,
         amount
  from (
           select contract,
                  value_date,
                  amount,
                  term,
                  lead(value_date, 1, value_date) over (partition by contract order by value_date) next_value_date
           from data) tmp lateral view  posexplode (
          split (space( cast(months_between(next_value_date, value_date) as int)), " (?!$)")
  ) tmp AS pos,val;

  结果如下：

  
• 您还可以看一下 CSDN就业班老师的第六章：Hive数据仓库基础知识课程中的 HiveHQL-Part1小节, 巩固相关知识点
  

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^