GenericUDF使用流程记载(转载+自己整理)

太过爱你忘了你带给我的痛 2023-01-01 09:52 140阅读 0赞

### 概述 ###

本文是对\[1\]的整理和复现。

### 环境 ###

<table style="width:279px;"> 
 <tbody> 
  <tr> 
   <td style="width:100px;"><strong>组件</strong></td> 
   <td style="width:176px;"><strong>版本</strong></td> 
  </tr> 
  <tr> 
   <td style="width:100px;"><strong>Hadoop</strong></td> 
   <td style="width:176px;">3.1.2</td> 
  </tr> 
  <tr> 
   <td style="width:100px;"><strong>Zookeeper</strong></td> 
   <td style="width:176px;">3.6.0</td> 
  </tr> 
  <tr> 
   <td style="width:100px;"><strong>Mysql</strong></td> 
   <td style="width:176px;">8.0.22-0ubuntu0.20.04.2</td> 
  </tr> 
  <tr> 
   <td style="width:100px;"><strong>Hive</strong></td> 
   <td style="width:176px;">2.3.7</td> 
  </tr> 
 </tbody> 
</table>

### Hive准备工作 ###

**本实验最大的难处是需要在hive中使用dummy table\[5\]**

如果没有专门学习过，还真想不到有这种技巧。

create table tb_test2 (name string,score_list array<map<string,int>>);
    insert into tb_test2 select "A", array(map("math",100,"english",90,"history",85)) from (select 1) x;
    insert into tb_test2 select "A", array(map("math",95,"english",80,"history",100)) from (select 1) x;
    insert into tb_test2 select "A", array(map("math",80,"english",90,"history",100)) from (select 1) x;

插入后效果如下:

0: jdbc:hive2://Desktop:10000> select * from tb_test2;
    +----------------+-------------------------------------------+
    | tb_test2.name  |            tb_test2.score_list            |
    +----------------+-------------------------------------------+
    | A              | [{"history":85,"english":90,"math":100}]  |
    | A              | [{"history":100,"english":80,"math":95}]  |
    | A              | [{"history":100,"english":90,"math":80}]  |
    +----------------+-------------------------------------------+

### 注册GenericUDF流程 ###

<table> 
 <tbody> 
  <tr> 
   <td><strong>注册命令(hive/beeline中进行)</strong></td> 
   <td><strong>备注</strong></td> 
  </tr> 
  <tr> 
   <td>add jar /home/appleyuchi/桌面/Flink_Code/FLINK读写各种数据源/Java/target/table_api-1.0-SNAPSHOT.jar;</td> 
   <td>指明自定义依赖包</td> 
  </tr> 
  <tr> 
   <td>create temporary function hellonew as 'helloGenericUDFNew';</td> 
   <td>依赖包中的类helloGenericUDFNew注册为hellonew</td> 
  </tr> 
 </tbody> 
</table>

### 使用GenericUDF流程 ###

<table> 
 <tbody> 
  <tr> 
   <td style="width:348px;"><strong>hive命令</strong></td> 
   <td><strong>实验结果</strong></td> 
   <td style="width:192px;"><strong>备注</strong></td> 
  </tr> 
  <tr> 
   <td style="width:348px;"> <p>use db1;</p> <p>select * from tb_test2;</p> </td> 
   <td>+--------------------+-----------------------------------------------------+<br> | tb_test2.name &nbsp;| &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;tb_test2.score_list&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|<br> +--------------------+-----------------------------------------------------+<br> | A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| [{"history":85,"english":90,"math":100}] &nbsp;|<br> | A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| [{"history":100,"english":80,"math":95}] &nbsp;|<br> | A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| [{"history":100,"english":90,"math":80}] &nbsp;|<br> +------------------- -+-----------------------------------------------------+</td> 
   <td style="width:192px;">检查hive功能是否ok</td> 
  </tr> 
  <tr> 
   <td style="width:348px;"> <p>use db1;</p> <p>select&nbsp;hellonew(tb_test2.name,tb_test2.score_list)&nbsp;from&nbsp;tb_test2;</p> </td> 
   <td>+---------------------------------------+<br> | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;_c0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|<br> +---------------------------------------+<br> | {"name":"A","totalscore":275} &nbsp;|<br> | {"name":"A","totalscore":275} &nbsp;|<br> | {"name":"A","totalscore":270} &nbsp;|<br> +----------------------------------------+<br> &nbsp;</td> 
   <td style="width:192px;">使用写好的udtf</td> 
  </tr> 
 </tbody> 
</table>

从实验结果来看这个GenericUDF的效果也很明显:

统计每个人的总成绩

**完整代码:**

[https://gitee.com/appleyuchi/Flink\_Code/blob/master/FLINK读写各种数据源/Java/src/main/java/helloGenericUDFNew.java][https_gitee.com_appleyuchi_Flink_Code_blob_master_FLINK_Java_src_main_java_helloGenericUDFNew.java]

hive的官方文档中指出collection指的是***arrays**** or maps\[4\]*

**Reference:**

\[1\][Hive GenericUDF2][]

\[2\][GenericUDF的示例, 根据字符串生成词向量][GenericUDF_]

\[3\][How can I insert a key-value pair into a hive map?][How can I insert a key-value pair into a hive map]

\[4\][Hive Tutorial][]

\[5\][How to insert array<map<string,int>> into hive table?][How to insert array_map_string_int_ into hive table]

[https_gitee.com_appleyuchi_Flink_Code_blob_master_FLINK_Java_src_main_java_helloGenericUDFNew.java]: https://gitee.com/appleyuchi/Flink_Code/blob/master/FLINK%E8%AF%BB%E5%86%99%E5%90%84%E7%A7%8D%E6%95%B0%E6%8D%AE%E6%BA%90/Java/src/main/java/helloGenericUDFNew.java
[Hive GenericUDF2]: https://www.cnblogs.com/cxhfuujust/p/7553123.html
[GenericUDF_]: https://blog.csdn.net/rav009/article/details/80423965
[How can I insert a key-value pair into a hive map]: https://stackoverflow.com/questions/18344501/how-can-i-insert-a-key-value-pair-into-a-hive-map
[Hive Tutorial]: https://cwiki.apache.org/confluence/display/Hive/Tutorial
[How to insert array_map_string_int_ into hive table]: https://stackoverflow.com/questions/65537116/how-to-insert-arraymapstring-int-into-hive-table/65537127