Hive技术问题：如何为无数据分区显示计数0？

问题场景 #

我把表按date字段做了分区，HDFS里已经有两个分区文件夹：01-01-2018和02-01-2018，其中02-01-2018分区没有任何数据。执行查询语句：

select date, count(*) from table1 group by date

时，只返回了有数据的分区结果：01-01-2018 168，但我需要输出包含空分区的结果，也就是要显示01-01-2018 168和02-01-2018 0，该怎么实现？

解决方案 #

我给你几个实用的方案，根据你的场景选就行：

方案1：静态日期列表左连接（适合分区数量少的情况）

如果你已经明确知道所有需要统计的分区日期，直接构造一个包含这些日期的临时数据集，再和你的统计结果做左连接，用COALESCE函数把左连接后出现的NULL值转换成0即可：

WITH date_list AS (
    SELECT '01-01-2018' AS date
    UNION ALL
    SELECT '02-01-2018' AS date
)
SELECT dl.date, COALESCE(t.count_num, 0) AS count_num
FROM date_list dl
LEFT JOIN (
    SELECT date, count(*) AS count_num
    FROM table1
    GROUP BY date
) t ON dl.date = t.date;

这个方法简单直接，不需要依赖元数据，适合分区不多的场景。

方案2：从Hive元数据获取所有分区（适合分区数量多的情况）

如果你的分区数量很多，手动列日期太麻烦，可以直接从Hive的元数据表中读取所有已存在的分区，再和统计结果关联：

WITH all_partitions AS (
    SELECT partition_value AS date
    FROM information_schema.partitions
    WHERE table_schema = '你的数据库名' 
      AND table_name = 'table1'
      AND partition_column = 'date'
)
SELECT ap.date, COALESCE(t.count_num, 0) AS count_num
FROM all_partitions ap
LEFT JOIN (
    SELECT date, count(*) AS count_num
    FROM table1
    GROUP BY date
) t ON ap.date = t.date;

注意替换语句中的你的数据库名为实际的数据库名称，这样就能自动获取所有存在的分区（不管有没有数据），最终空分区的统计数会显示为0。

补充说明

Hive默认只会扫描有数据的分区，所以直接group by不会返回空分区的结果。上面的两种左连接方法是最通用的解决方案，相比依赖版本特定的参数配置，稳定性更高。

内容的提问来源于stack exchange，提问作者Steven Armstrong