使用配置单元SQL获取文件系统目录大小

Question

我每天都会将文件系统信息加载到Hive，我只想获得所有目录大小。

我有一张像这样的桌子

Path                   Size               Date
/                        0             01-07-2021
/tmp                     0             01-07-2021
/tmp/file1               2             01-07-2021
/tmp/file2               2             01-07-2021
/tmp/dir1                0             01-07-2021
/tmp/dir1/file3          3             01-07-2021
/opt/                    0             01-07-2021
/opt/file1               2             01-07-2021
/opt/dir1                0             01-07-2021
/opt/dir1/file2          3             01-07-2021
/opt/dir2/               0             01-07-2021
/opt/dir2/file3          4             01-07-2021
...
...
...
/                        0             02-07-2021
/tmp                     0             02-07-2021
/tmp/file1               2             02-07-2021
/tmp/file2               2             02-07-2021
/tmp/dir1                0             02-07-2021
/tmp/dir1/file3          3             02-07-2021
/opt/                    0             02-07-2021
/opt/file1               2             02-07-2021
/opt/dir1                0             02-07-2021
/opt/dir1/file2          3             02-07-2021
/opt/dir2/               0             02-07-2021
/opt/dir2/file3          4             02-07-2021

我想要一个输出查询或创建一个像这样的新表。

Path                   Size               Date
/                        16            01-07-2021
/tmp                     7             01-07-2021
/tmp/dir1                3             01-07-2021
/opt                     9             01-07-2021
/opt/dir1                3             01-07-2021
/opt/dir2                4             01-07-2021
...
...
...
/                        16            02-07-2021
/tmp                     7             02-07-2021
/tmp/dir1                3             02-07-2021
/opt                     9             02-07-2021
/opt/dir1                3             02-07-2021
/opt/dir2                4             02-07-2021

我是SQL的新手，请帮帮我。谢谢。

Answer 1

MatBaille走在了正确的轨道上。这个想法是将目录中每个级别的每一行相乘，然后进行聚合。我认为更安全的方法是使用substring_index()，它的行为与MySQL中的一样。这确实需要生成一系列数字，使用split(spaces)破解：

select substring_index(path, pe.i) as path,
max(case when path = substring_index(path, i) then date end) as date,
sum(size) as total_size
from (select t.*,
1 + length(path) - length(replace(path, '/', '')) as depth
from t
) t lateral view
posexplode(split(space(t.depth))) pe as i, x
group by substring_index(path, pe.i);

Answer 2

我不是HiveQL专家，但从我简要阅读的内容来看，它不太支持递归查询。所以这里有一个暴力的方法。

它将文件路径拆分为多个部分，然后将它们重新聚合，这样每个父目录的每个文件都是重复的。然后聚合共享父目录的每个文件。

希望有人能给出更好的答案。

select sub_path, sum(size) as size, date
from
(
select path, size, date, concat_ws('/', collect_list(b.element)) as sub_path
from YourTable
lateral view outer posexplode(split(path, '/')) a as pos, element
lateral view outer posexplode(split(path, '/')) b as pos, element
where b.pos <= a.pos
group by path, size, date, a.pos
)
sub_paths
group by sub_path, date

我建议单独测试子查询，以获得一个小的伪数据测试集(几个目录中的几个文件(。这将演示它的作用，并帮助您调试任何拼写错误。

它还假设不存在包含转义斜杠的混乱目录或文件名。这段代码不会识别出斜杠已经转义，并将其视为正常斜杠，表示目录结构中的一个新级别。