hive-列转行和行转列

柔情只为你懂 2021-09-12 04:56 526阅读 0赞

1. 假设我们在[Hive][]中有两张表，其中一张表是存用户基本信息，另一张表是存用户的地址信息等，表数据假设如下：

user\_basic\_info:

user\_address;

<table style="color:rgb(54,46,43); font-family:Arial; font-size:14px"> 
 <tbody> 
  <tr> 
   <td>name</td> 
   <td>address</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>add1</td> 
  </tr> 
  <tr> 
   <td>a</td> 
   <td>add2</td> 
  </tr> 
  <tr> 
   <td>b</td> 
   <td>add3</td> 
  </tr> 
  <tr> 
   <td>c</td> 
   <td>add4</td> 
  </tr> 
  <tr> 
   <td>d</td> 
   <td>add5</td> 
  </tr> 
 </tbody> 
</table>

我们可以看到同一个用户不止一个地址（这里是假设的），我们需要把数据变为如下格式：

<table style="color:rgb(54,46,43); font-family:Arial; font-size:14px"> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>name</td> 
   <td>address</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>a</td> 
   <td>add1,add2</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>b</td> 
   <td>add3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>c</td> 
   <td>add4</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>d</td> 
   <td>add5</td> 
  </tr> 
 </tbody> 
</table>

collect\_set  这就用到了hive中的行转列的知识，需要用到两个内置UDF: collect\_set, concat\_ws,

两个函数解释如下见：[http://www.cnblogs.com/end/archive/2012/06/18/2553682.html][http_www.cnblogs.com_end_archive_2012_06_18_2553682.html]

建表:

**\[sql\]**  [view plain][]   [copy][view plain]

[print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  **create** **table** user\_basic\_info(id string, **name** string);  
2.  **create** **table** user\_address(**name** string, address string);

加载数据：

**\[sql\]**  [view plain][]   [copy][view plain]

[print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  **load** data **local** inpath '/home/jthink/work/workspace/hive/row\_col\_tran/data1' **into** **table** user\_basic\_info;  
2.  **load** data **local** inpath '/home/jthink/work/workspace/hive/row\_col\_tran/data2' **into** **table** user\_address;

执行合并：

**\[sql\]**  [view plain][]   [copy][view plain]

[print][view plain] [?][view plain] [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  **select** **max**(ubi.id), ubi.**name**, concat\_ws(',', collect\_set(ua.address)) **as** address **from** user\_basic\_info ubi join user\_address ua **on** ubi.**name**=ua.**name** **group** **by** ubi.**name**;

运行结果：

1       a       add1,add2  
2       b       add3  
3       c       add4  
4       d       add5

2. 假设我们有一张表：

user\_info:

我们需要拆分address，变为：

<table style="color:rgb(54,46,43); font-family:Arial; font-size:14px"> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>name</td> 
   <td>address</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>a</td> 
   <td>add1</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>a</td> 
   <td>add2</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>b</td> 
   <td>add3</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>c</td> 
   <td>add4</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>d</td> 
   <td>add5</td> 
  </tr> 
 </tbody> 
</table>

我们很容易想到用UDTF，explode()：

select explode(address) as address from user\_info;

这样执行的结果只有address， 但是我们需要完整的信息：

select id, name, explode(address) as address from user\_info;

这样做是不对的， UDTF's are not supported outside the SELECT clause, nor nested in expressions

所以我们需要这样做：

select id, name, add from user\_info ui lateral view explode(ui.address) adtable as add;

结果为：

1       a       add1  
1       a       add2  
2       b       add3  
3       c       add4  
4       d       add5

[Hive]: http://lib.csdn.net/base/hive
[http_www.cnblogs.com_end_archive_2012_06_18_2553682.html]: http://www.cnblogs.com/end/archive/2012/06/18/2553682.html
[view plain]: http://blog.csdn.net/jthink_/article/details/38853573#
[CODE]: https://code.csdn.net/assets/CODE_ico.png
[CODE_CODE]: https://code.csdn.net/snippets/458771
[ico_fork.svg]: https://code.csdn.net/assets/ico_fork.svg
[ico_fork.svg 1]: https://code.csdn.net/snippets/458771/fork