Hive lateral view语句(翻译自Hive wiki)

Love The Way You Lie 2022-08-05 05:24 148阅读 0赞

[hive lateral view语句(翻译自Hive wiki)][hive lateral view_Hive wiki]

http://www.cnblogs.com/ggjucheng/archive/2013/01/03/2842938.html

## Lateral View语法 ##

lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
    fromClause: FROM baseTable (lateralView)*

## **描述** ##

lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

## **例子** ##

假设我们有一张表pageAds，它有两列数据，第一列是pageid string，第二列是adid\_list，即用逗号分隔的广告ID集合：

<table> 
 <tbody> 
  <tr> 
   <td>string pageid</td> 
   <td>Array&lt;int&gt; adid_list</td> 
  </tr> 
  <tr> 
   <td>"front_page"</td> 
   <td>[1, 2, 3]</td> 
  </tr> 
  <tr> 
   <td>"contact_page"</td> 
   <td>[3, 4, 5]</td> 
  </tr> 
 </tbody> 
</table>

要统计所有广告ID在所有页面中出现的次数。

首先分拆广告ID：

SELECT pageid, adid 
        FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;

执行结果如下：

<table> 
 <tbody> 
  <tr> 
   <td>string pageid</td> 
   <td>int adid</td> 
  </tr> 
  <tr> 
   <td>"front_page"</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>"front_page"</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>"front_page"</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>"contact_page"</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>"contact_page"</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>"contact_page"</td> 
   <td>5</td> 
  </tr> 
 </tbody> 
</table>

接下来就是一个聚合的统计：

SELECT adid, count(1) 
        FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid
    GROUP BY adid;

执行结果如下：

<table> 
 <tbody> 
  <tr> 
   <td>int adid</td> 
   <td>count(1)</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

## **多个lateral view语句** ##

一个FROM语句后可以跟多个lateral view语句，后面的lateral view语句能够引用它前面的所有表和列名。 以下面的表为例：

<table> 
 <tbody> 
  <tr> 
   <td>Array&lt;int&gt; col1</td> 
   <td>Array&lt;string&gt; col2</td> 
  </tr> 
  <tr> 
   <td>[1, 2]</td> 
   <td>[a", "b", "c"]</td> 
  </tr> 
  <tr> 
   <td>[3, 4]</td> 
   <td>[d", "e", "f"]</td> 
  </tr> 
 </tbody> 
</table>

</pre><p class="code-java"> </p><p class="code-java"> </p><p class="code-java"> </p><div class="cnblogs_code"><pre>SELECT myCol1, col2 FROM baseTable
        LATERAL VIEW explode(col1) myTable1 AS myCol1;

执行结果为：

<table> 
 <tbody> 
  <tr> 
   <td>int mycol1</td> 
   <td>Array&lt;string&gt; col2</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>[a", "b", "c"]</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>[a", "b", "c"]</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>[d", "e", "f"]</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>[d", "e", "f"]</td> 
  </tr> 
 </tbody> 
</table>

加上一个lateral view：

SELECT myCol1, myCol2 FROM baseTable
        LATERAL VIEW explode(col1) myTable1 AS myCol1
        LATERAL VIEW explode(col2) myTable2 AS myCol2;

它的执行结果为：

<table> 
 <tbody> 
  <tr> 
   <td>int myCol1</td> 
   <td>string myCol2</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>"a"</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>"b"</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>"c"</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>"a"</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>"b"</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>"c"</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>"d"</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>"e"</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>"f"</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>"d"</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>"e"</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>"f"</td> 
  </tr> 
 </tbody> 
</table>

注意上面语句中，两个lateral view按照出现的次序被执行。

转自 [https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView\#][https_cwiki.apache.org_confluence_display_Hive_LanguageManual_LateralView]

[http://blog.csdn.net/inte\_sleeper/article/details/7196114][http_blog.csdn.net_inte_sleeper_article_details_7196114]

[hive lateral view_Hive wiki]: http://www.cnblogs.com/ggjucheng/archive/2013/01/03/2842938.html
[https_cwiki.apache.org_confluence_display_Hive_LanguageManual_LateralView]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView
[http_blog.csdn.net_inte_sleeper_article_details_7196114]: http://blog.csdn.net/inte_sleeper/article/details/7196114