Hive面试题—理清hive应用思路

- 日理万妓 2022-08-02 02:14 179阅读 0赞

Hive面试题—理清hive应用思路

问题：有一张很大的表：TRLOG该表大概有2T左右。

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

1.  TRLOG：  
2.  **CREATE** **TABLE** TRLOG  
3.  (PLATFORM string,  
4.  USER\_ID **int**,  
5.  CLICK\_TIME string,  
6.  CLICK\_URL string)  
7.  row format delimited fields terminated **by** '\\t';

数据：

**\[html\]**  [view plain][] [copy][view plain]

1.  PLATFORM          USER\_ID              CLICK\_TIME             CLICK\_URL  
2.  WEB               12332321      2013-03-21 13:48:31.324       /home/  
3.  WEB               12332321      2013-03-21 13:48:32.954       /selectcat/er/  
4.  WEB               12332321      2013-03-21 13:48:46.365       /er/viewad/12.html  
5.  WEB               12332321      2013-03-21 13:48:53.651       /er/viewad/13.html  
6.  WEB               12332321      2013-03-21 13:49:13.435       /er/viewad/24.html  
7.  WEB               12332321      2013-03-21 13:49:35.876       /selectcat/che/  
8.  WEB               12332321      2013-03-21 13:49:56.398       /che/viewad/93.html  
9.  WEB               12332321      2013-03-21 13:50:03.143       /che/viewad/10.html  
10. WEB               12332321      2013-03-21 13:50:34.265       /home/  
11. WAP               32483923      2013-03-21 23:58:41.123       /m/home/  
12. WAP               32483923      2013-03-21 23:59:16.123       /m/selectcat/fang/  
13. WAP               32483923      2013-03-21 23:59:45.123       /m/fang/33.html  
14. WAP               32483923      2013-03-22 00:00:23.984       /m/fang/54.html  
15. WAP               32483923      2013-03-22 00:00:54.043       /m/selectcat/er/  
16. WAP               32483923      2013-03-22 00:01:16.576       /m/er/49.html  
17. ……                  ……                ……                                  ……

现需要把上述数据处理为如下结构的表ALLOG：

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

1.  **CREATE** **TABLE** ALLOG  
2.  (PLATFORM string,  
3.   USER\_ID **int**,  
4.   SEQ **int**,  
5.   FROM\_URL string,  
6.   TO\_URL   string)  
7.  row format delimited fields terminated **by** '\\t';

整理后的数据结构：

**\[html\]**  [view plain][] [copy][view plain]

1.  PLATFORM          USER\_ID     SEQ             FROM\_URL                      TO\_URL  
2.  WEB               12332321     1               NULL                        /home/  
3.  WEB               12332321     2               /home/                      /selectcat/er/  
4.  WEB               12332321     3               /selectcat/er/              /er/viewad/12.html  
5.  WEB               12332321     4               /er/viewad/12.html          /er/viewad/13.html  
6.  WEB               12332321     5               /er/viewad/13.html          /er/viewad/24.html  
7.  WEB               12332321     6               /er/viewad/24.html          /selectcat/che/  
8.  WEB               12332321     7               /selectcat/che/             /che/viewad/93.html  
9.  WEB               12332321     8               /che/viewad/93.html         /che/viewad/10.html  
10. WEB               12332321     9               /che/viewad/10.html         /home/  
11. WAP               32483923     1               NULL                        /m/home/  
12. WAP               32483923     2               /m/home/                    /m/selectcat/fang/  
13. WAP               32483923     3               /m/selectcat/fang/          /m/fang/33.html  
14. WAP               32483923     4               /m/fang/33.html             /m/fang/54.html  
15. WAP               32483923     5               /m/fang/54.html             /m/selectcat/er/  
16. WAP               32483923     6               /m/selectcat/er/             /m/er/49.html  
17. ……                  ……           ……                 ……                             ……

说明：PLATFORM和USER\_ID还是代表平台和用户ID；SEQ字段代表用户按时间排序后的访问顺序，FROM\_URL和TO\_URL分别代表用户从哪一页跳转到哪一页。对于某个平台上某个用户的第一条访问记录，其FROM\_URL是NULL（空值）。

解题要求：需要用两种办法做出来：

1、实现一个能加速上述处理过程的Hive Generic UDF，并给出使用此UDF实现ETL过程的Hive SQL。  
  2、实现基于纯Hive SQL的ETL过程，从TRLOG表生成ALLOG表；（结果是一套hive SQL语句）。  
  说明：第一题效率一定要高，因为表有2TB，第二题无所谓，只要能用HIVE SQL实现就行。

\*\*\*\*\*\*\*\*\*\*解决思路\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

问题一：写UDF简单，只要自定义一个ROWNUMBER方法，加载到HIVE中就能出结果了。  
下面是网友提供的一个JAVA写的RowNumber方法（该方法用于获取“SEQ”列的值(SEQ字段代表用户按时间排序后的访问顺序），仅供参考。

**\[java\]**  [view plain][] [copy][view plain]

1.  **public** **class** RowNumber **extends** org.apache.hadoop.hive.ql.exec.UDF \{  
2.     
3.      **private** **static** **int** MAX\_VALUE = 50;  
4.      **private** **static** String comparedColumn\[\] = **new** String\[MAX\_VALUE\];  
5.      **private** **static** **int** rowNum = 1;  
6.     
7.      **public** **int** evaluate(Object... args) \{  
8.          String columnValue\[\] = **new** String\[args.length\];  
9.          **for** (**int** i = 0; i < args.length; i++)  
10.             columnValue\[i\] = args\[i\].toString();  
11.         **if** (rowNum == 1)  
12.         \{  
13.    
14.             **for** (**int** i = 0; i < columnValue.length; i++)  
15.                 comparedColumn\[i\] = columnValue\[i\];  
16.         \}  
17.    
18.         **for** (**int** i = 0; i < columnValue.length; i++)  
19.         \{  
20.    
21.             **if** (!comparedColumn\[i\].equals(columnValue\[i\]))  
22.             \{  
23.                 **for** (**int** j = 0; j < columnValue.length; j++)  
24.                 \{  
25.                     comparedColumn\[j\] = columnValue\[j\];  
26.                 \}  
27.                 rowNum = 1;  
28.                 **return** rowNum++;  
29.             \}  
30.         \}  
31.         **return** rowNum++;  
32.     \}  
33. \}

把上面这个JAVA类打包，编译成JAR包，比如RowNumber.jar。然后放到Hive的机器上的相应目录下。

然后在HIVE SHELL里执行下面两条语句：

**\[html\]**  [view plain][] [copy][view plain]

1.  add jar /root/RowNumber.jar; \#把RowNumber.jar加载到HIVE的CLASSPATH中  
2.  create temporary function row\_number as 'RowNumber';    
3.  \#在HIVE里创建一个新函数，叫row\_number ，引用的CLASS 就是JAVA代码里的RowNumber类。

提示成功后，再执行下面这条HIVE SQL语句：

**\[html\]**  [view plain][] [copy][view plain]

1.  \#INSERT OVERWRITE TABLE ALLOG 如果要写入ALLOG表，可以把注释去掉  
2.  SELECT t1.platform,t1.user\_id,row\_number(t1.user\_id)seq,t2.click\_url FROM\_URL,t1.click\_url TO\_URL FROM  
3.  (select \*,row\_number(user\_id)seq from trlog)t1  
4.  LEFT OUTER JOIN  
5.  (select \*,row\_number(user\_id)seq from trlog)t2   
6.  on t1.user\_id = t2.user\_id and t1.seq = t2.seq + 1;

成功实现！  
疑        问：第一题解决了，但有一点不明白T1\\T2既然已经有了row\_number(user\_id)seq，为什么在最外层还要套个row\_number(user\_id)seq？  
牛人回答：第一题中的RN貌似是HIVE转译SQL的BUG，你可以把外层的ROW\_NUMBER去掉，用T1的SEQ，就能发现问题了。具体情况还有待分析，有兴趣可以去国外社区查查相关的BUG LIST。

问题解题思路二：

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

1.  **INSERT** OVERWRITE **TABLE** ALLOG  
2.  **SELECT** t1.platform,t1.user\_id,t1.seq,t2.click\_url FROM\_URL,t1.click\_url TO\_URL **FROM**  
3.  (**SELECT** platform,user\_id,click\_time,click\_url,count(1) seq **FROM** (**SELECT** a.\*,b.click\_time click\_time1,b.click\_url click\_url2  **FROM** trlog a left outer join trlog b **on** a.user\_id = b.user\_id)t **WHERE** click\_time>=click\_time1 **GROUP** **BY** platform,user\_id,click\_time,click\_url)t1  
4.  LEFT OUTER JOIN  
5.  (**SELECT** platform,user\_id,click\_time,click\_url,count(1) seq **FROM** (**SELECT** a.\*,b.click\_time click\_time1,b.click\_url click\_url2  **FROM** trlog a left outer join trlog b **on** a.user\_id = b.user\_id)t **WHERE** click\_time>=click\_time1 **GROUP** **BY** platform,user\_id,click\_time,click\_url )t2   
6.  **on** t1.user\_id = t2.user\_id and t1.seq = t2.seq + 1;

分析说明：这个方法完全没有效率可言，MapReduce JOB最少要跑5次，做实验还行，跑生产环境就免了。

转自：http://www.360doc.com/content/14/0107/20/15109633\_343417196.shtml

[view plain]: http://blog.csdn.net/zolalad/article/details/10819749#