hadoop - 在Hive中按列抽样

给定一个Hive表，如下所示:

> desc T;
dim1 string
dim2 string
dim3 string
value1 int
value2 int

我正在尝试按组(dim1, dim2, dim3)随机采样1,000行。

一种解决方法是:

# bash
for dim1 in dim1_1, dim1_2; do
     for dim2 in dim2_1, dim2_2; do
        for dim3 in dim3_1, dim3_2; do
            hive -e "select * from T where dim1=$dim1 and dim2=$dim2 and dim3=$dim3 limit 1000;"
done done done

然后将连续执行2 ^ 3 = 8个查询。有没有更有效的方法？

最佳答案

with    dim as
        (
            select  struct(d1.v,d2.v,d3.v) as vals

            from    (select 1) x
                    lateral view explode (array(1,2)) d1 as v -- dim1_1 = 1 dim1_2 = 2
                    lateral view explode (array(3,4)) d2 as v -- dim2_1 = 3 dim2_2 = 4
                    lateral view explode (array(5,6)) d3 as v -- dim3_1 = 5 dim3_2 = 6
        )

select  *

from   (select  row_number () over
                (
                    partition by    dim1,dim2,dim3
                    order by        rand()
                ) as rn
               ,*

        from    t

        where   struct(t.dim1,t.dim2,t.dim3) in (select vals from dim)
        ) t

where   rn <= 1000
;

关于hadoop - 在Hive中按列抽样，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42711389/

DIM3

hadoop - 在Hive中按列抽样