按配置单元中的列进行采样

but5z9lq 于 2021-06-02 发布在 Hadoop

关注(0)|答案(1)|浏览(232)

给定一个配置单元表，如下所示：

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

我试着按组随机抽取1000行 (dim1, dim2, dim3) .
一种方法是：


# 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个查询。有没有更有效的方法？

hadoop Hive

来源：https://stackoverflow.com/questions/42711389/sampling-by-columns-in-hive

1条答案

按热度按时间

hmtdttj41#

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
;

赞(0）回复(0）举报 2021-06-02

我来回答

按配置单元中的列进行采样

1条答案

相关问题

热门标签

最新问答