从一道hive面试题的解决思路中理解hive应用

最新推荐文章于 2024-06-13 15:45:31 发布

zolalad

最新推荐文章于 2024-06-13 15:45:31 发布

阅读量7.4k

点赞数

分类专栏： Hadoop+Hbase+Hive技术

本文链接：https://blog.csdn.net/zolalad/article/details/10819749

版权

Hadoop+Hbase+Hive技术专栏收录该内容

15 篇文章 0 订阅

订阅专栏

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

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

TRLOG：
CREATE TABLE TRLOG
(PLATFORM string,
USER_ID int,
CLICK_TIME string,
CLICK_URL string)
row format delimited fields terminated by '\t';

数据：

PLATFORM          USER_ID              CLICK_TIME             CLICK_URL
WEB               12332321      2013-03-21 13:48:31.324       /home/
WEB               12332321      2013-03-21 13:48:32.954       /selectcat/er/
WEB               12332321      2013-03-21 13:48:46.365       /er/viewad/12.html
WEB               12332321      2013-03-21 13:48:53.651       /er/viewad/13.html
WEB               12332321      2013-03-21 13:49:13.435       /er/viewad/24.html
WEB               12332321      2013-03-21 13:49:35.876       /selectcat/che/
WEB               12332321      2013-03-21 13:49:56.398       /che/viewad/93.html
WEB               12332321      2013-03-21 13:50:03.143       /che/viewad/10.html
WEB               12332321      2013-03-21 13:50:34.265       /home/
WAP               32483923      2013-03-21 23:58:41.123       /m/home/
WAP               32483923      2013-03-21 23:59:16.123       /m/selectcat/fang/
WAP               32483923      2013-03-21 23:59:45.123       /m/fang/33.html
WAP               32483923      2013-03-22 00:00:23.984       /m/fang/54.html
WAP               32483923      2013-03-22 00:00:54.043       /m/selectcat/er/
WAP               32483923      2013-03-22 00:01:16.576       /m/er/49.html
……                  ……                ……                                  ……

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

CREATE TABLE ALLOG
(PLATFORM string,
 USER_ID int,
 SEQ int,
 FROM_URL string,
 TO_URL   string)
row format delimited fields terminated by '\t';

整理后的数据结构：

PLATFORM          USER_ID     SEQ             FROM_URL                      TO_URL
WEB               12332321     1               NULL                        /home/
WEB               12332321     2               /home/                      /selectcat/er/
WEB               12332321     3               /selectcat/er/              /er/viewad/12.html
WEB               12332321     4               /er/viewad/12.html          /er/viewad/13.html
WEB               12332321     5               /er/viewad/13.html          /er/viewad/24.html
WEB               12332321     6               /er/viewad/24.html          /selectcat/che/
WEB               12332321     7               /selectcat/che/             /che/viewad/93.html
WEB               12332321     8               /che/viewad/93.html         /che/viewad/10.html
WEB               12332321     9               /che/viewad/10.html         /home/
WAP               32483923     1               NULL                        /m/home/
WAP               32483923     2               /m/home/                    /m/selectcat/fang/
WAP               32483923     3               /m/selectcat/fang/          /m/fang/33.html
WAP               32483923     4               /m/fang/33.html             /m/fang/54.html
WAP               32483923     5               /m/fang/54.html             /m/selectcat/er/
WAP               32483923     6               /m/selectcat/er/             /m/er/49.html
……                  ……           ……                 ……                             ……

说明：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字段代表用户按时间排序后的访问顺序），仅供参考。

public class RowNumber extends org.apache.hadoop.hive.ql.exec.UDF {
 
    private static int MAX_VALUE = 50;
    private static String comparedColumn[] = new String[MAX_VALUE];
    private static int rowNum = 1;
 
    public int evaluate(Object... args) {
        String columnValue[] = new String[args.length];
        for (int i = 0; i < args.length; i++)
            columnValue[i] = args[i].toString();
        if (rowNum == 1)
        {
 
            for (int i = 0; i < columnValue.length; i++)
                comparedColumn[i] = columnValue[i];
        }
 
        for (int i = 0; i < columnValue.length; i++)
        {
 
            if (!comparedColumn[i].equals(columnValue[i]))
            {
                for (int j = 0; j < columnValue.length; j++)
                {
                    comparedColumn[j] = columnValue[j];
                }
                rowNum = 1;
                return rowNum++;
            }
        }
        return rowNum++;
    }
}

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

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

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

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

#INSERT OVERWRITE TABLE ALLOG 如果要写入ALLOG表，可以把注释去掉
SELECT t1.platform,t1.user_id,row_number(t1.user_id)seq,t2.click_url FROM_URL,t1.click_url TO_URL FROM
(select *,row_number(user_id)seq from trlog)t1
LEFT OUTER JOIN
(select *,row_number(user_id)seq from trlog)t2 
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。

问题解题思路二：

INSERT OVERWRITE TABLE ALLOG
SELECT t1.platform,t1.user_id,t1.seq,t2.click_url FROM_URL,t1.click_url TO_URL FROM
(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
LEFT OUTER JOIN
(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 
on t1.user_id = t2.user_id and t1.seq = t2.seq + 1;

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

本博文整理自CSDN论坛：http://bbs.csdn.net/topics/390414342

zolalad

关注

0
点赞
踩
14

收藏

觉得还不错? 一键收藏
1
评论
从一道hive面试题的解决思路中理解hive应用

Hive面试题—理清hive应用思路问题：有一张很大的表：TRLOG该表大概有2T左右。TRLOG：CREATE TABLE TRLOG(PLATFORM string,USER_ID int,CLICK_TIME string,CLICK_URL string)row format delimited fields terminated by '\t';数据：
复制链接

扫一扫

专栏目录