hive常用函数及数据结构介绍

系统管理员 2022-04-11 04:13 246阅读 0赞

# [https://blog.csdn.net/xiaolang85/article/details/51330634][https_blog.csdn.net_xiaolang85_article_details_51330634] #

#  #

# Hive 中的复合数据结构简介以及一些函数的用法说明 #

2016年05月06日 12:30:31 [小狼\_百度][Link 1] 阅读数：7329

目录\[-\]

*  [一、map、struct、array 这3种的用法：][map_struct_array _3]
 *  [1、Array的使用][1_Array]
 *  [2、Map 的使用][2_Map]
 *  [3、Struct 的使用][3_Struct]
 *  [4、数据组合 （不支持组合的复杂数据类型）][4_]
 *  [二、hive中的一些不常见函数的用法：][hive]
 *  [1、array\_contains （Collection Functions）][1_array_contains _Collection Functions]
 *  [2、get\_json\_object （Misc. Functions）][2_get_json_object _Misc. Functions]
 *  [3、parse\_url\_tuple][3_parse_url_tuple]
 *  [4、explode][4_explode]
 *  [5、lateral view][5_lateral view]
 *  [三、ref：][ref]

目前 hive 支持的复合数据类型有以下几种：

map  
(key1, value1, key2, value2, ...) Creates a map with the given key/value pairs  
struct  
(val1, val2, val3, ...) Creates a struct with the given field values. Struct field names will be col1, col2, ...  
named\_struct  
(name1, val1, name2, val2, ...) Creates a struct with the given field names and values. (as of Hive 0.8.0)  
array  
(val1, val2, ...) Creates an array with the given elements  
create\_union  
(tag, val1, val2, ...) Creates a union type with the value that is being pointed to by the tag parameter

## 一、map、struct、array 这3种的用法： ##

### 1、Array的使用 ###

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 </td> 
 <td> <code>创建数据库表，以array作为数据类型</code> <code>create</code>&nbsp;<code>table</code>&nbsp;&nbsp;<code>person(</code><code>name</code>&nbsp;<code>string,work_locations&nbsp;array&lt;string&gt;)</code> <code>ROW&nbsp;FORMAT&nbsp;DELIMITED</code> <code>FIELDS&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>'\t'</code> <code>COLLECTION&nbsp;ITEMS&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>','</code><code>;</code> <code>数据</code> <code>biansutao&nbsp;beijing,shanghai,tianjin,hangzhou</code> <code>linan&nbsp;changchu,chengdu,wuhan</code> <code>入库数据</code> <code>LOAD</code>&nbsp;<code>DATA&nbsp;</code><code>LOCAL</code>&nbsp;<code>INPATH&nbsp;</code><code>'/home/hadoop/person.txt'</code>&nbsp;<code>OVERWRITE&nbsp;</code><code>INTO</code>&nbsp;<code>TABLE</code>&nbsp;<code>person;</code> <code>查询</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>person;</code> <code>biansutao&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[</code><code>"beijing"</code><code>,</code><code>"shanghai"</code><code>,</code><code>"tianjin"</code><code>,</code><code>"hangzhou"</code><code>]</code> <code>linan&nbsp;&nbsp;&nbsp;[</code><code>"changchu"</code><code>,</code><code>"chengdu"</code><code>,</code><code>"wuhan"</code><code>]</code> <code>Time</code>&nbsp;<code>taken:&nbsp;0.355&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>name</code>&nbsp;<code>from</code>&nbsp;<code>person;</code> <code>linan</code> <code>biansutao</code> <code>Time</code>&nbsp;<code>taken:&nbsp;12.397&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>work_locations[0]&nbsp;</code><code>from</code>&nbsp;<code>person;</code> <code>changchu</code> <code>beijing</code> <code>Time</code>&nbsp;<code>taken:&nbsp;13.214&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>work_locations&nbsp;</code><code>from</code>&nbsp;<code>person;&nbsp;&nbsp;&nbsp;</code> <code>[</code><code>"changchu"</code><code>,</code><code>"chengdu"</code><code>,</code><code>"wuhan"</code><code>]</code> <code>[</code><code>"beijing"</code><code>,</code><code>"shanghai"</code><code>,</code><code>"tianjin"</code><code>,</code><code>"hangzhou"</code><code>]</code> <code>Time</code>&nbsp;<code>taken:&nbsp;13.755&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>work_locations[3]&nbsp;</code><code>from</code>&nbsp;<code>person;</code> <code>NULL</code> <code>hangzhou</code> <code>Time</code>&nbsp;<code>taken:&nbsp;12.722&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>work_locations[4]&nbsp;</code><code>from</code>&nbsp;<code>person;</code> <code>NULL</code> <code>NULL</code> <code>Time</code>&nbsp;<code>taken:&nbsp;15.958&nbsp;seconds</code> </td> 
 </tr> 
 </tbody> 
</table>

### 2、Map 的使用 ###

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 </td> 
 <td> <code>创建数据库表</code> <code>create</code>&nbsp;<code>table</code>&nbsp;<code>score(</code><code>name</code>&nbsp;<code>string,&nbsp;score&nbsp;map&lt;string,</code><code>int</code><code>&gt;)</code> <code>ROW&nbsp;FORMAT&nbsp;DELIMITED</code> <code>FIELDS&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>'\t'</code> <code>COLLECTION&nbsp;ITEMS&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>','</code> <code>MAP&nbsp;KEYS&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>':'</code><code>;</code> <code>要入库的数据</code> <code>biansutao&nbsp;</code><code>'数学'</code><code>:80,</code><code>'语文'</code><code>:89,</code><code>'英语'</code><code>:95</code> <code>jobs&nbsp;</code><code>'语文'</code><code>:60,</code><code>'数学'</code><code>:80,</code><code>'英语'</code><code>:99</code> <code>入库数据</code> <code>LOAD</code>&nbsp;<code>DATA&nbsp;</code><code>LOCAL</code>&nbsp;<code>INPATH&nbsp;</code><code>'/home/hadoop/score.txt'</code>&nbsp;<code>OVERWRITE&nbsp;</code><code>INTO</code>&nbsp;<code>TABLE</code>&nbsp;<code>score;</code> <code>查询</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>score;</code> <code>biansutao&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{ </code><code>"数学"</code><code>:80,</code><code>"语文"</code><code>:89,</code><code>"英语"</code><code>:95}</code> <code>jobs&nbsp;&nbsp;&nbsp;&nbsp;{ </code><code>"语文"</code><code>:60,</code><code>"数学"</code><code>:80,</code><code>"英语"</code><code>:99}</code> <code>Time</code>&nbsp;<code>taken:&nbsp;0.665&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>name</code>&nbsp;<code>from</code>&nbsp;<code>score;</code> <code>jobs</code> <code>biansutao</code> <code>Time</code>&nbsp;<code>taken:&nbsp;19.778&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>t.score&nbsp;</code><code>from</code>&nbsp;<code>score&nbsp;t;</code> <code>{ </code><code>"语文"</code><code>:60,</code><code>"数学"</code><code>:80,</code><code>"英语"</code><code>:99}</code> <code>{ </code><code>"数学"</code><code>:80,</code><code>"语文"</code><code>:89,</code><code>"英语"</code><code>:95}</code> <code>Time</code>&nbsp;<code>taken:&nbsp;19.353&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>t.score[</code><code>'语文'</code><code>]&nbsp;</code><code>from</code>&nbsp;<code>score&nbsp;t;</code> <code>60</code> <code>89</code> <code>Time</code>&nbsp;<code>taken:&nbsp;13.054&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>t.score[</code><code>'英语'</code><code>]&nbsp;</code><code>from</code>&nbsp;<code>score&nbsp;t;</code> <code>99</code> <code>95</code> <code>Time</code>&nbsp;<code>taken:&nbsp;13.769&nbsp;seconds</code> </td> 
 </tr> 
 </tbody> 
</table>

### 3、Struct 的使用 ###

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 </td> 
 <td> <code>创建数据表</code> <code>CREATE</code>&nbsp;<code>TABLE</code>&nbsp;<code>test(id&nbsp;</code><code>int</code><code>,course&nbsp;struct&lt;course:string,score:</code><code>int</code><code>&gt;)</code> <code>ROW&nbsp;FORMAT&nbsp;DELIMITED</code> <code>FIELDS&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>'\t'</code> <code>COLLECTION&nbsp;ITEMS&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>','</code><code>;</code> <code>数据</code> <code>1&nbsp;english,80</code> <code>2&nbsp;math,89</code> <code>3&nbsp;chinese,95</code> <code>入库</code> <code>LOAD</code>&nbsp;<code>DATA&nbsp;</code><code>LOCAL</code>&nbsp;<code>INPATH&nbsp;</code><code>'/home/hadoop/test.txt'</code>&nbsp;<code>OVERWRITE&nbsp;</code><code>INTO</code>&nbsp;<code>TABLE</code>&nbsp;<code>test;</code> <code>查询</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>test;</code> <code>OK</code> <code>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{ </code><code>"course"</code><code>:</code><code>"english"</code><code>,</code><code>"score"</code><code>:80}</code> <code>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{ </code><code>"course"</code><code>:</code><code>"math"</code><code>,</code><code>"score"</code><code>:89}</code> <code>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{ </code><code>"course"</code><code>:</code><code>"chinese"</code><code>,</code><code>"score"</code><code>:95}</code> <code>Time</code>&nbsp;<code>taken:&nbsp;0.275&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>course&nbsp;</code><code>from</code>&nbsp;<code>test;</code> <code>{ </code><code>"course"</code><code>:</code><code>"english"</code><code>,</code><code>"score"</code><code>:80}</code> <code>{ </code><code>"course"</code><code>:</code><code>"math"</code><code>,</code><code>"score"</code><code>:89}</code> <code>{ </code><code>"course"</code><code>:</code><code>"chinese"</code><code>,</code><code>"score"</code><code>:95}</code> <code>Time</code>&nbsp;<code>taken:&nbsp;44.968&nbsp;seconds</code> <code>select</code>&nbsp;<code>t.course.course&nbsp;</code><code>from</code>&nbsp;<code>test&nbsp;t;&nbsp;</code> <code>english</code> <code>math</code> <code>chinese</code> <code>Time</code>&nbsp;<code>taken:&nbsp;15.827&nbsp;seconds</code> <code>hive&gt;&nbsp;</code><code>select</code>&nbsp;<code>t.course.score&nbsp;</code><code>from</code>&nbsp;<code>test&nbsp;t;</code> <code>80</code> <code>89</code> <code>95</code> <code>Time</code>&nbsp;<code>taken:&nbsp;13.235&nbsp;seconds</code> </td> 
 </tr> 
 </tbody> 
</table>

### 4、数据组合 （不支持组合的复杂数据类型） ###

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 </td> 
 <td> <code>LOAD</code>&nbsp;<code>DATA&nbsp;</code><code>LOCAL</code>&nbsp;<code>INPATH&nbsp;</code><code>'/home/hadoop/test.txt'</code>&nbsp;<code>OVERWRITE&nbsp;</code><code>INTO</code>&nbsp;<code>TABLE</code>&nbsp;<code>test;</code> <code>create</code>&nbsp;<code>table</code>&nbsp;<code>test1(id&nbsp;</code><code>int</code><code>,a&nbsp;MAP&lt;STRING,ARRAY&lt;STRING&gt;&gt;)</code> <code>row&nbsp;format&nbsp;delimited&nbsp;fields&nbsp;terminated&nbsp;</code><code>by</code>&nbsp;<code>'\t'</code>&nbsp; <code>collection&nbsp;items&nbsp;terminated&nbsp;</code><code>by</code>&nbsp;<code>','</code> <code>MAP&nbsp;KEYS&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>':'</code><code>;</code> <code>1&nbsp;english:80,90,70</code> <code>2&nbsp;math:89,78,86</code> <code>3&nbsp;chinese:99,100,82</code> <code>LOAD</code>&nbsp;<code>DATA&nbsp;</code><code>LOCAL</code>&nbsp;<code>INPATH&nbsp;</code><code>'/home/hadoop/test1.txt'</code>&nbsp;<code>OVERWRITE&nbsp;</code><code>INTO</code>&nbsp;<code>TABLE</code>&nbsp;<code>test1;</code> </td> 
 </tr> 
 </tbody> 
</table>

## 二、hive中的一些不常见函数的用法： ##

常见的函数就不废话了，和标准sql类似，下面我们要聊到的基本是HQL里面专有的函数，

hive里面的函数大致分为如下几种：Built-in、Misc.、UDF、UDTF、UDAF

我们就挑几个标准SQL里没有，但是在HIVE SQL在做统计分析常用到的来说吧。

### 1、array\_contains （Collection Functions） ###

这是内置的对集合进行操作的函数，用法举例：

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 </td> 
 <td> <code>create</code>&nbsp;<code>EXTERNAL&nbsp;</code><code>table</code>&nbsp;<code>IF&nbsp;</code><code>NOT</code>&nbsp;<code>EXISTS&nbsp;userInfo&nbsp;(id&nbsp;</code><code>int</code><code>,sex&nbsp;string,&nbsp;age&nbsp;</code><code>int</code><code>,&nbsp;</code><code>name</code>&nbsp;<code>string,&nbsp;email&nbsp;string,sd&nbsp;string,&nbsp;ed&nbsp;string)&nbsp;&nbsp;ROW&nbsp;FORMAT&nbsp;DELIMITED&nbsp;FIELDS&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>'\t'</code>&nbsp;<code>location&nbsp;</code><code>'/hive/dw'</code><code>;</code> &nbsp; <code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>userinfo&nbsp;</code><code>where</code>&nbsp;<code>sex=</code><code>'male'</code>&nbsp;<code>and</code>&nbsp;<code>(id!=1&nbsp;</code><code>and</code>&nbsp;<code>id&nbsp;!=2&nbsp;</code><code>and</code>&nbsp;<code>id!=3&nbsp;</code><code>and</code>&nbsp;<code>id!=4&nbsp;</code><code>and</code>&nbsp;<code>id!=5)&nbsp;</code><code>and</code>&nbsp;<code>age&nbsp;&lt;&nbsp;30;</code> <code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>(</code><code>select</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>userinfo&nbsp;</code><code>where</code>&nbsp;<code>sex=</code><code>'male'</code>&nbsp;<code>and</code>&nbsp;<code>!array_contains(split(</code><code>'1,2,3,4,5'</code><code>,</code><code>','</code><code>),</code><code>cast</code><code>(id&nbsp;</code><code>as</code>&nbsp;<code>string)))&nbsp;tb1&nbsp;</code><code>where</code>&nbsp;<code>tb1.age&nbsp;&lt;&nbsp;30;</code> </td> 
 </tr> 
 </tbody> 
</table>

其中建表所用的测试数据你可以用如下链接的脚本自动生成：

[http://my.oschina.net/leejun2005/blog/76631][http_my.oschina.net_leejun2005_blog_76631]

### 2、get\_json\_object （Misc. Functions） ###

测试数据：

first \{"store":\{"fruit":\[\{"weight":8,"type":"apple"\},\{"weight":9,"type":"pear"\}\],"bicycle":\{"price":19.951,"color":"red1"\}\},"email":"amy@only\_for\_json\_udf\_test.net","owner":"amy1"\} third  
first \{"store":\{"fruit":\[\{"weight":9,"type":"apple"\},\{"weight":91,"type":"pear"\}\],"bicycle":\{"price":19.952,"color":"red2"\}\},"email":"amy@only\_for\_json\_udf\_test.net","owner":"amy2"\} third  
first \{"store":\{"fruit":\[\{"weight":10,"type":"apple"\},\{"weight":911,"type":"pear"\}\],"bicycle":\{"price":19.953,"color":"red3"\}\},"email":"amy@only\_for\_json\_udf\_test.net","owner":"amy3"\} third

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 </td> 
 <td> <code>create</code>&nbsp;<code>external&nbsp;</code><code>table</code>&nbsp;<code>if&nbsp;</code><code>not</code>&nbsp;<code>exists&nbsp;t_json(f1&nbsp;string,&nbsp;f2&nbsp;string,&nbsp;f3&nbsp;string)&nbsp;row&nbsp;format&nbsp;delimited&nbsp;fields&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>'&nbsp;'</code>&nbsp;<code>location&nbsp;</code><code>'/test/json'</code> <code>select</code>&nbsp;<code>get_json_object(t_json.f2,&nbsp;</code><code>'$.owner'</code><code>)&nbsp;</code><code>from</code>&nbsp;<code>t_json;</code> <code>SELECT</code>&nbsp;<code>*&nbsp;</code><code>from</code>&nbsp;<code>t_json&nbsp;</code><code>where</code>&nbsp;<code>get_json_object(t_json.f2,&nbsp;</code><code>'$.store.fruit[0].weight'</code><code>)&nbsp;=&nbsp;9;</code> <code>SELECT</code>&nbsp;<code>get_json_object(t_json.f2,&nbsp;</code><code>'$.non_exist_key'</code><code>)&nbsp;</code><code>FROM</code>&nbsp;<code>t_json;</code> </td> 
 </tr> 
 </tbody> 
</table>

这里尤其要注意UDTF的问题，官方文档有说明：

json\_tuple  
A new json\_tuple() UDTF is introduced in hive 0.7. It takes a set of names (keys) and a JSON string, and returns a tuple of values using one function. This is much more efficient than calling GET\_JSON\_OBJECT to retrieve more than one key from a single JSON string. In any case where a single JSON string would be parsed more than once, your query will be more efficient if you parse it once, which is what JSON\_TUPLE is for. As JSON\_TUPLE is a UDTF, you will need to use the LATERAL VIEW syntax in order to achieve the same goal.  
  
For example,

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> <code>select</code>&nbsp;<code>a.</code><code>timestamp</code><code>,&nbsp;get_json_object(a.appevents,&nbsp;</code><code>'$.eventid'</code><code>),&nbsp;get_json_object(a.appenvets,&nbsp;</code><code>'$.eventname'</code><code>)&nbsp;</code><code>from</code>&nbsp;<code>log&nbsp;a;</code> </td> 
 </tr> 
 </tbody> 
</table>

should be changed to

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>select</code>&nbsp;<code>a.</code><code>timestamp</code><code>,&nbsp;b.*</code> <code>from</code>&nbsp;<code>log&nbsp;a&nbsp;lateral&nbsp;</code><code>view</code>&nbsp;<code>json_tuple(a.appevent,&nbsp;</code><code>'eventid'</code><code>,&nbsp;</code><code>'eventname'</code><code>)&nbsp;b&nbsp;</code><code>as</code>&nbsp;<code>f1,&nbsp;f2;</code> </td> 
 </tr> 
 </tbody> 
</table>

UDTF(User-Defined Table-Generating Functions) 用来解决 输入一行输出多行(On-to-many maping) 的需求。

通过Lateral view可以方便的将UDTF得到的行转列的结果集合在一起提供服务，因为直接在SELECT使用UDTF会存在限制，即仅仅能包含单个字段，不光是多个UDTF，仅仅单个UDTF加上其他字段也是不可以，hive提示在UDTF中仅仅能有单一的表达式。如下：  
hive> select my\_test(“abcef:aa”) as qq,’abcd’ from sunwg01;  
FAILED: Error in semantic analysis: Only a single expression in the SELECT clause is supported with UDTF’s

使用Lateral view可以实现上面的需求，Lateral view语法如下：  
lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)\*  
fromClause: FROM baseTable (lateralView)\*  
hive> create table sunwg ( a array, b array )  
> ROW FORMAT DELIMITED  
> FIELDS TERMINATED BY ‘\\t’  
> COLLECTION ITEMS TERMINATED BY ‘,’;  
OK  
Time taken: 1.145 seconds  
hive> load data local inpath ‘/home/hjl/sunwg/sunwg.txt’ overwrite into table sunwg;  
Copying data from file:/home/hjl/sunwg/sunwg.txt  
Loading data to table sunwg  
OK  
Time taken: 0.162 seconds  
hive> select \* from sunwg;  
OK  
\[10,11\] \["tom","mary"\]  
\[20,21\] \["kate","tim"\]  
Time taken: 0.069 seconds  
hive>  
> SELECT a, name  
> FROM sunwg LATERAL VIEW explode(b) r1 AS name;  
OK  
\[10,11\] tom  
\[10,11\] mary  
\[20,21\] kate  
\[20,21\] tim  
Time taken: 8.497 seconds  
  
hive> SELECT id, name  
> FROM sunwg LATERAL VIEW explode(a) r1 AS id  
> LATERAL VIEW explode(b) r2 AS name;  
OK  
10 tom  
10 mary  
11 tom  
11 mary  
20 kate  
20 tim  
21 kate  
21 tim  
Time taken: 9.687 seconds

### 3、parse\_url\_tuple ###

测试数据：

url1 http://facebook.com/path1/p.php?k1=v1&k2=v2\#Ref1  
url2 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF\#LanguageManualUDF-getjsonobject  
url3 https://www.google.com.hk/\#hl=zh-CN&newwindow=1&safe=strict&q=hive+translate+example&oq=hive+translate+example&gs\_l=serp.3...10174.11861.6.12051.8.8.0.0.0.0.132.883.0j7.7.0...0.0...1c.1j4.8.serp.0B9C1T\_n0Hs&bav=on.2,or.&bvm=bv.44770516,d.aGc&fp=e13e41a6b9dab3f6&biw=1241&bih=589

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>create</code>&nbsp;<code>external&nbsp;</code><code>table</code>&nbsp;<code>if&nbsp;</code><code>not</code>&nbsp;<code>exists&nbsp;t_url(f1&nbsp;string,&nbsp;f2&nbsp;string)&nbsp;row&nbsp;format&nbsp;delimited&nbsp;fields&nbsp;TERMINATED&nbsp;</code><code>BY</code>&nbsp;<code>'&nbsp;'</code>&nbsp;<code>location&nbsp;</code><code>'/test/url'</code><code>;</code> <code>SELECT</code>&nbsp;<code>f1,&nbsp;b.*&nbsp;</code><code>FROM</code>&nbsp;<code>t_url&nbsp;LATERAL&nbsp;</code><code>VIEW</code>&nbsp;<code>parse_url_tuple(f2,&nbsp;</code><code>'HOST'</code><code>,&nbsp;</code><code>'PATH'</code><code>,&nbsp;</code><code>'QUERY'</code><code>,&nbsp;</code><code>'QUERY:k1'</code><code>)&nbsp;b&nbsp;</code><code>as</code>&nbsp;<code>host,&nbsp;path,&nbsp;query,&nbsp;query_id;</code> </td> 
 </tr> 
 </tbody> 
</table>

结果：

url1 facebook.com /path1/p.php k1=v1&k2=v2 v1  
url2 cwiki.apache.org /confluence/display/Hive/LanguageManual+UDF NULL NULL  
url3 www.google.com.hk / NULL NULL

### 4、explode ###

explode 是一个 hive 内置的表生成函数：Built-in Table-Generating Functions (UDTF)，主要是解决 1 to N 的问题，即它可以把一行输入拆成多行，比如一个 array 的每个元素拆成一行，作为一个虚表输出。它有如下需要注意的地方：

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 </td> 
 <td> <code>Using&nbsp;the&nbsp;syntax&nbsp;</code><code>"SELECT&nbsp;udtf(col)&nbsp;AS&nbsp;colAlias..."</code>&nbsp;<code>has&nbsp;a&nbsp;few&nbsp;limitations:</code> <code>No</code>&nbsp;<code>other&nbsp;expressions&nbsp;are&nbsp;allowed&nbsp;</code><code>in</code>&nbsp;<code>SELECT</code> <code>SELECT</code>&nbsp;<code>pageid,&nbsp;explode(adid_list)&nbsp;</code><code>AS</code>&nbsp;<code>myCol...&nbsp;</code><code>is</code>&nbsp;<code>not</code>&nbsp;<code>supported</code> <code>UDTF</code><code>'s&nbsp;can'</code><code>t&nbsp;be&nbsp;nested</code> <code>SELECT</code>&nbsp;<code>explode(explode(adid_list))&nbsp;</code><code>AS</code>&nbsp;<code>myCol...&nbsp;</code><code>is</code>&nbsp;<code>not</code>&nbsp;<code>supported</code> <code>GROUP</code>&nbsp;<code>BY</code>&nbsp;<code>/&nbsp;CLUSTER&nbsp;</code><code>BY</code>&nbsp;<code>/&nbsp;DISTRIBUTE&nbsp;</code><code>BY</code>&nbsp;<code>/&nbsp;SORT&nbsp;</code><code>BY</code>&nbsp;<code>is</code>&nbsp;<code>not</code>&nbsp;<code>supported</code> <code>SELECT</code>&nbsp;<code>explode(adid_list)&nbsp;</code><code>AS</code>&nbsp;<code>myCol&nbsp;...&nbsp;</code><code>GROUP</code>&nbsp;<code>BY</code>&nbsp;<code>myCol&nbsp;</code><code>is</code>&nbsp;<code>not</code>&nbsp;<code>supported</code> </td> 
 </tr> 
 </tbody> 
</table>

从上面的原理与语法上可知，

*  select 列中不能 udtf 和其它非 udtf 列混用，
 *  udtf 不能嵌套，
 *  不支持 GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY
 *  还有 select 中出现的 udtf 一定需要列别名，否则会报错：

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> <code>SELECT</code>&nbsp;<code>explode(myCol)&nbsp;</code><code>AS</code>&nbsp;<code>myNewCol&nbsp;</code><code>FROM</code>&nbsp;<code>myTable;</code> <code>SELECT</code>&nbsp;<code>explode(myMap)&nbsp;</code><code>AS</code>&nbsp;<code>(myMapKey,&nbsp;myMapValue)&nbsp;</code><code>FROM</code>&nbsp;<code>myMapTable;</code> <code>SELECT</code>&nbsp;<code>posexplode(myCol)&nbsp;</code><code>AS</code>&nbsp;<code>pos,&nbsp;myNewCol&nbsp;</code><code>FROM</code>&nbsp;<code>myTable;</code> </td> 
 </tr> 
 </tbody> 
</table>

### 5、lateral view ###

lateral view 是Hive中提供给UDTF的conjunction，它可以解决UDTF不能添加额外的select列的问题。当我们想对hive表中某一列进行split之后，想对其转换成1 to N的模式，即一行转多列。hive不允许我们在UDTF函数之外，再添加其它select语句。

如下，我们想将登录某个游戏的用户id放在一个字段user\_ids里，对每一行数据用UDTF后输出多行。

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>select</code>&nbsp;<code>game_id,&nbsp;explode(split(user_ids,</code><code>'\\[\\[\\['</code><code>))&nbsp;</code><code>as</code>&nbsp;<code>user_id&nbsp;&nbsp;&nbsp;</code><code>from</code>&nbsp;<code>login_game_log&nbsp;&nbsp;</code><code>where</code>&nbsp;<code>dt=</code><code>'2014-05-15'</code>&nbsp;<code>;</code> <code>FAILED:&nbsp;Error&nbsp;</code><code>in</code>&nbsp;<code>semantic&nbsp;analysis:&nbsp;UDTF's&nbsp;are&nbsp;</code><code>not</code>&nbsp;<code>supported&nbsp;outside&nbsp;the&nbsp;</code><code>SELECT</code>&nbsp;<code>clause,&nbsp;nor&nbsp;nested&nbsp;</code><code>in</code>&nbsp;<code>expressions。</code> </td> 
 </tr> 
 </tbody> 
</table>

提示语法分析错误，UDTF不支持函数之外的select 语句，如果我们想支持怎么办呢？接下来就是Lateral View 登场的时候了。

Lateral view 其实就是用来和像类似explode这种UDTF函数联用的。lateral view 会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表（1 to N）会和输入行即每个game\_id进行join 来达到连接UDTF外的select字段的目的（源表和拆分的虚表按行做行内 1 join N 的直接连接），这也是为什么 LATERAL VIEW udtf(expression) 后面需要表别名和列别名的原因。

Lateral View Syntax

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)\*

fromClause: FROM baseTable (lateralView)\*

可以看出，可以在2个地方用Lateral view：

*  在udtf前面用
 *  在from baseTable后面用

例如：

pageid adid\_list

front\_page \[1, 2, 3\]

contact\_page \[3, 4, 5\]

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 </td> 
 <td> <code>SELECT</code>&nbsp;<code>pageid,&nbsp;adid</code> <code>FROM</code>&nbsp;<code>pageAds&nbsp;LATERAL&nbsp;</code><code>VIEW</code>&nbsp;<code>explode(adid_list)&nbsp;adTable&nbsp;</code><code>AS</code>&nbsp;<code>adid;</code> </td> 
 </tr> 
 </tbody> 
</table>

##  ##

pageid adid

front\_page 1

front\_page 2

front\_page 3

contact\_page 3

contact\_page 4

contact\_page 5

From语句后可以跟多个Lateral View。

A FROM clause can have multiple LATERAL VIEW clauses. Subsequent LATERAL VIEWS can reference columns from any of the tables appearing to the left of the LATERAL VIEW.

给定数据：

Array<int> col1 Array<string> col2

\[1, 2\] \[a", "b", "c"\]

\[3, 4\] \[d", "e", "f"\]

转换目标：

想同时把第一列和第二列拆开，类似做笛卡尔乘积。

[![175428_1RKs_568818.png][]][175428_1RKs_568818.png]

我们可以这样写：

[?][Link 2]

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 </td> 
 <td> <code>SELECT</code>&nbsp;<code>myCol1,&nbsp;myCol2&nbsp;</code><code>FROM</code>&nbsp;<code>baseTable</code> <code>LATERAL&nbsp;</code><code>VIEW</code>&nbsp;<code>explode(col1)&nbsp;myTable1&nbsp;</code><code>AS</code>&nbsp;<code>myCol1</code> <code>LATERAL&nbsp;</code><code>VIEW</code>&nbsp;<code>explode(col2)&nbsp;myTable2&nbsp;</code><code>AS</code>&nbsp;<code>myCol2;</code> </td> 
 </tr> 
 </tbody> 
</table>

还有一种情况，如果UDTF转换的Array是空的怎么办呢？

在Hive0.12里面会支持outer关键字，如果UDTF的结果是空，默认会被忽略输出。

如果加上outer关键字，则会像left outer join 一样，还是会输出select出的列，而UDTF的输出结果是NULL。

总结：

*  Lateral View通常和UDTF一起出现，为了解决UDTF不允许在select字段的问题。
 *  Multiple Lateral View可以实现类似笛卡尔乘积。
 *  Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。

## 三、ref： ##

http://blog.csdn.net/wf1982/article/details/7474601  
http://www.cnblogs.com/ggjucheng/archive/2013/01/08/2850797.html  
http://www.oratea.net/?p=650  
[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF\#LanguageManualUDF-parseurltuple][https_cwiki.apache.org_confluence_display_Hive_LanguageManual_UDF_LanguageManualUDF-parseurltuple]  
[https://cwiki.apache.org/confluence/display/Hive/Tutorial][https_cwiki.apache.org_confluence_display_Hive_Tutorial]

[http://blog.csdn.net/inte\_sleeper/article/details/7196114][http_blog.csdn.net_inte_sleeper_article_details_7196114] hive lateral view语句：列拆分成行

[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF\#LanguageManualUDF-explode][https_cwiki.apache.org_confluence_display_Hive_LanguageManual_UDF_LanguageManualUDF-explode]

[http://blog.csdn.net/oopsoom/article/details/26001307][http_blog.csdn.net_oopsoom_article_details_26001307] Lateral View用法 与 Hive UDTF explode

[https_blog.csdn.net_xiaolang85_article_details_51330634]: https://blog.csdn.net/xiaolang85/article/details/51330634
[Link 1]: https://me.csdn.net/xiaolang85
[map_struct_array _3]: http://my.oschina.net/leejun2005/blog/120463#OSC_h2_1
[1_Array]: http://my.oschina.net/leejun2005/blog/120463#OSC_h3_2
[2_Map]: http://my.oschina.net/leejun2005/blog/120463#OSC_h3_3
[3_Struct]: http://my.oschina.net/leejun2005/blog/120463#OSC_h3_4
[4_]: http://my.oschina.net/leejun2005/blog/120463#OSC_h3_5
[hive]: http://my.oschina.net/leejun2005/blog/120463#OSC_h2_6
[1_array_contains _Collection Functions]: http://my.oschina.net/leejun2005/blog/120463#OSC_h3_7
[2_get_json_object _Misc. Functions]: http://my.oschina.net/leejun2005/blog/120463#OSC_h3_8
[3_parse_url_tuple]: http://my.oschina.net/leejun2005/blog/120463#OSC_h3_9
[4_explode]: http://my.oschina.net/leejun2005/blog/120463#OSC_h3_10
[5_lateral view]: http://my.oschina.net/leejun2005/blog/120463#OSC_h3_11
[ref]: http://my.oschina.net/leejun2005/blog/120463#OSC_h2_12
[Link 2]: http://my.oschina.net/leejun2005/blog/120463#
[http_my.oschina.net_leejun2005_blog_76631]: http://my.oschina.net/leejun2005/blog/76631
[175428_1RKs_568818.png]: /images/20220411/0ede4440f800482999a1089dcfb710d2.png
[https_cwiki.apache.org_confluence_display_Hive_LanguageManual_UDF_LanguageManualUDF-parseurltuple]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-parseurltuple
[https_cwiki.apache.org_confluence_display_Hive_Tutorial]: https://cwiki.apache.org/confluence/display/Hive/Tutorial
[http_blog.csdn.net_inte_sleeper_article_details_7196114]: http://blog.csdn.net/inte_sleeper/article/details/7196114
[https_cwiki.apache.org_confluence_display_Hive_LanguageManual_UDF_LanguageManualUDF-explode]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-explode
[http_blog.csdn.net_oopsoom_article_details_26001307]: http://blog.csdn.net/oopsoom/article/details/26001307