分享

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

阿飞 发表于 2014-4-20 14:47:46 [显示全部楼层] 回帖奖励 阅读模式 关闭右栏 17 70337
本帖最后由 阿飞 于 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)

已有(17)人评论

跳转到指定楼层
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方法
  1. public class RowNumber extends org.apache.hadoop.hive.ql.exec.UDF {
  2.         private static int MAX_VALUE = 50;
  3.         private static String comparedColumn[] = new String[MAX_VALUE];
  4.         private static int rowNum = 1;
  5.         public int evaluate(Object... args) {
  6.                 String columnValue[] = new String[args.length];
  7.                 for (int i = 0; i < args.length; i++)
  8.                         columnValue[i] = args[i].toString();
  9.                 if (rowNum == 1)
  10.                 {
  11.                         for (int i = 0; i < columnValue.length; i++)
  12.                                 comparedColumn[i] = columnValue[i];
  13.                 }
  14.                 for (int i = 0; i < columnValue.length; i++)
  15.                 {
  16.                         if (!comparedColumn[i].equals(columnValue[i]))
  17.                         {
  18.                                 for (int j = 0; j < columnValue.length; j++)
  19.                                 {
  20.                                         comparedColumn[j] = columnValue[j];
  21.                                 }
  22.                                 rowNum = 1;
  23.                                 return rowNum++;
  24.                         }
  25.                 }
  26.                 return rowNum++;
  27.         }
  28. }
复制代码
把这个JAVA打包,编译成JAR包,比如RowNumber.jar。这个你总会吧~~~
然后放到HIVE的机器上
在HIVE SHELL里执行下面两条语句:
  1. add jar /root/RowNumber.jar;
  2. #把RowNumber.jar加载到HIVE的CLASSPATH中
  3. create temporary function row_number as 'RowNumber';
  4. #在HIVE里创建一个新函数,叫row_number ,引用的CLASS 就是JAVA代码里的RowNumber
复制代码

提示成功后,执行下面这条HIVE SQL
  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;
复制代码

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



第二题:
  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;
复制代码






回复

使用道具 举报

阿飞 发表于 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上运行成功。
回复

使用道具 举报

perfri 发表于 2014-4-27 10:37:45
我是来打酱油的
回复

使用道具 举报

pride1000 发表于 2014-5-16 15:12:29
能说一下这两种方法有什么优缺点吗?
回复

使用道具 举报

yangq 发表于 2015-4-24 11:40:25
我想问一下 hive能跟hbase里的表映射   ,那hive能不能跟 mysql映射  ,这个mysql不是hive底层的元数据库
回复

使用道具 举报

吖louis 发表于 2015-4-24 16:32:40
来膜拜一下
回复

使用道具 举报

Lyncos 发表于 2015-5-11 13:58:13
学习了~顶一下~
回复

使用道具 举报

12下一页
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

推荐上一条 /2 下一条