Hive爆炸/横向视图多个阵列 [英] Hive Explode / Lateral View multiple arrays

问题描述

我有一个具有以下模式的配置表：

  COOKIE | PRODUCT_ID | CAT_ID |数量
 1234123 [1,2,3] [r，t，null] [2,1，null] 
  

我如何规范化数组，以便得到以下结果

  COOKIE | PRODUCT_ID | CAT_ID |数量
 
 1234123 [1] [r] [2] 
 
 1234123 [2] [t] [1] 
 
 1234123 [3] null null 
  

我试过以下内容：

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
横向视图爆炸（productid）ptable作为pid 
横向视图爆炸（catalogId）ptable2作为catid 
横向视图爆炸（qty）ptable3作为数量
  

然而结果是以笛卡儿积的形式出现的。 您可以使用来自Brickhouse（ numeric_range 和 array_index UDF klout / brickhouserel =noreferrer> http://github.com/klout/brickhouse ）来解决这个问题。这里有详细的博客文章，详细描述 http://brickhouseconfessions.wordpress.com/2013/03/07/exploding-multiple-arrays-at-the-same-time-with-numeric_range/

使用这些UDF，查询可能类似于

 选择cookie，
 array_index（product_id_arr，n）作为product_id，
 array_index（catalog_id_arr，n）作为catalog_id，
 array_index（qty_id_arr，n）作为数量
从表
横向视图numeric_range（size （product_id_arr））n1 as n; 
  

I have a hive table with the following schema:

COOKIE  | PRODUCT_ID | CAT_ID |    QTY    
1234123   [1,2,3]    [r,t,null]  [2,1,null]

How can I normalize the arrays so I get the following result

COOKIE  | PRODUCT_ID | CAT_ID |    QTY

1234123   [1]          [r]         [2]

1234123   [2]          [t]         [1] 

1234123   [3]          null       null 

I have tried the following:

select concat_ws('|',visid_high,visid_low) as cookie
,pid
,catid 
,qty
from table
lateral view explode(productid) ptable as pid
lateral view explode(catalogId) ptable2 as catid 
lateral view explode(qty) ptable3 as qty

however the result comes out as a Cartesian product.

解决方案

You can use the numeric_range and array_index UDFs from Brickhouse ( http://github.com/klout/brickhouse ) to solve this problem. There is an informative blog posting describing in detail over at http://brickhouseconfessions.wordpress.com/2013/03/07/exploding-multiple-arrays-at-the-same-time-with-numeric_range/

Using those UDFs, the query would be something like

select cookie,
   array_index( product_id_arr, n ) as product_id,
   array_index( catalog_id_arr, n ) as catalog_id,
   array_index( qty_id_arr, n ) as qty
from table
lateral view numeric_range( size( product_id_arr )) n1 as n;

这篇关于Hive爆炸/横向视图多个阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！