hive面试题目：表大概有2T左右，对表数据转换

本帖最后由阿飞于 2014-4-20 14:48 编辑

有一张很大的表：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表；（结果是一套SQL）

xin07020220 · 发表于 2014-8-28 23:06:48

hyj 发表于 2014-4-20 14:53
给你个JAVA写的RowNumber方法
把这个JAVA打包，编译成JAR包，比如RowNumber.jar。这个你总会吧~~~
然后放 ...

对于第二题，当同一个user_id在不同的platform中出现时，此SQL是会有问题的。
a表与b表的关联条件应为 on a.user_id = b.user_id and a.platform=b.platform

hyj · 发表于 2014-4-20 14:53:20

给你个JAVA写的RowNumber方法


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;
复制代码

第一题中的RN貌似是HIVE转译SQL的BUG，你可以把外层的ROW_NUMBER去掉，用T1的SEQ，就能发现问题了。

第二题：


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;
复制代码

阿飞 · 发表于 2014-4-20 14:54:30

第一个写法在hive-0.7.1上面运行有问题，估计是row_number的bug，用
select platform, user_id, row_number(user_id),click_time,click_url from trlog group by platform, user_id, click_time,click_url;
代替
select *,row_number(user_id)seq from trlog结果正确。
在hive-0.10上运行成功。