从 Hive 数组中选择特定值

jimiclapton

2016-10-08

16993

我在 Hive 中有一个表格，结构如下：

timestamp   UserID  OtherId    
2016-09-01  123     "101","222","321","987","393.1","090","467","863"
2016-09-01  124     "188","389","673","972","193","100","143","210"
2016-09-01  125     "888","120","482","594","393.2"
2016-09-01  126     "441","501","322","671","008","899"
2016-09-01  127     "004","700","393.4","761","467","356","643","578"
2016-09-01  128     "322","582","348"
2016-09-01  129     "029","393.8","126","187"

其中 OtherID 是一个数组。

我需要解析 OtherID 以便生成的数据集如下，因为我只对包含 '393%' 的值感兴趣

timestamp   UserID  OtherId    
2016-09-01  123     393.1
2016-09-01  125     393.2
2016-09-01  127     393.4
2016-09-01  129     393.8

我研究了大量的解析函数，但似乎它们都是为了返回值的位置，或者您需要指定值的位置才能返回它。这两个选项在这里都不起作用，因为 '3309%' 可以出现在任何给定行的数组中的任何点。还有一个事实是我需要合并通配符以允许我想要的值的变化。

另一种选择是爆炸，但我的桌子对于该选项来说太大了。

我认为 UDF 可能是唯一的出路，但会欢迎那里提供一些指导。

感谢任何帮助。

Select specific value from Hive array

原文答案

答案:

ozw1z5rd

2016-10-09 0 原文

使用 hive 中提供的横向视图选项可以轻松完成您需要的操作。

0: jdbc:hive2://quickstart:10000/default> select * from test_5; 
+-----------+------------+----------------------------------------------+
| test_5.t  | test_5.id  |                  test_5.oid                  |
+-----------+------------+----------------------------------------------+
| 123       | 123        | "222","321","987","393.1","090","467","863"  |
+-----------+------------+----------------------------------------------+

这就是诀窍：

SELECT id, ooid
FROM test_5 
LATERAL VIEW EXPLODE(SPLIT(oid,",")) temp AS ooid;

+------+----------+
|  id  |   ooid   |
+------+----------+
| 123  | "222"    |
| 123  | "321"    |
| 123  | "987"    |
| 123  | "393.1"  |
| 123  | "090"    |
| 123  | "467"    |
| 123  | "863"    |
+------+----------+

尔格：

SELECT id, regexp_replace(ooid,'"','')
FROM test_5 
LATERAL VIEW EXPLODE(SPLIT(oid,",")) temp AS ooid;
WHERE ooid LIKE '"393%';

+------+----------+
|  id  |   ooid   |
+------+----------+
| 123  |  393.1   |
+------+----------+

Suman K

2016-10-15 0 原文

也许你可以尝试如下：

hive> select timestamp1, userid, otherids from userdet1 LATERAL VIEW explode(otherid) testTable as otherids where otherids LIKE concat('393','%');

好的

2016-09-01  123 393.1

2016-09-01  125 393.2

2016-09-01  127 393.4

2016-09-01  129 393.8

Time taken:  0.297  seconds,  Fetched: 4 row(s)

从 Hive 数组中选择特定值

答案:

相关问题

标签