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

id="cproIframe_u1728839_3" width="120" height="250" src="http://pos.baidu.com/acom?adn=4&adp=1&at=0&aurl=&c01=1&cad=1&ccd=24&cec=GBK&cfv=17&ch=0&col=zh-CN&conBW=1&conOP=0&cpa=1&dai=3&dis=0&layout_filter=rank%2Cimage&ltr=http%3A%2F%2Fwww.aboutyun.com%2Fthread-11738-1-1.html&ltu=http%3A%2F%2Fwww.aboutyun.com%2Fthread-7450-1-2.html&lunum=6&n=92051019_cpr&pat=6&pcs=1349x623&pih=0&pis=10000x10000&piw=0&ps=604x1133&psr=1366x768&pss=1349x901&ptbg=90&ptp=0&ptt=0&qn=e747046373251862&rad=&rsi0=120&rsi1=250&rsi5=4&rss0=%23FFFFFF&rss1=%23FFFFFF&rss2=%23000000&rss3=%23444444&rss4=%23008000&rss5=&rss6=%23e10900&rss7=&scale=&skin=tabcloud_skin_3&stid=5&td_id=1728839&titFF=%25E5%25BE%25AE%25E8%25BD%25AF%25E9%259B%2585%25E9%25BB%2591&titFS=14&titSU=0&tn=baiduCustNativeAD&tpr=1435393645667&ts=1&version=2.0&xuanting=0&dtm=BAIDU_DUP2_SETJSONADSLOT&dc=2&di=u1728839&ti=hive%E9%9D%A2%E8%AF%95%E9%A2%98%E7%9B%AE%EF%BC%9A%E8%A1%A8%E5%A4%A7%E6%A6%82%E6%9C%892T%E5%B7%A6%E5%8F%B3%EF%BC%8C%E5%AF%B9%E8%A1%A8%E6%95%B0%E6%8D%AE%E8%BD%AC%E6%8D%A2-%E9%9D%A2%E8%AF%95%E9%A2%98-about%E4%BA%91%E5%BC%80%E5%8F%91&tt=1435393645654.584.712.712" align="center,center" marginwidth="0" marginheight="0" scrolling="no" frameborder="0" allowtransparency="true" style="word-wrap: break-word;"> 本帖最后由 阿飞 于 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） allowtransparency="true" frameborder="0" scrolling="no" style="word-wrap: break-word; float: none; display: block; overflow: hidden; z-index: 2147483646; margin: 0px; padding: 0px; border-width: 0px; border-style: none; height: 366px; width: 778px; background: none;">
分享到:   QQ好友和群  QQ空间  腾讯微博  腾讯朋友  微信 收藏7  转播  分享  淘帖  支持  反对 id="cproIframe_u1995025_4" width="750" height="250" src="http://pos.baidu.com/acom?adn=0&adp=1&at=0&aurl=&c01=1&cad=1&ccd=24&cec=GBK&cfv=17&ch=0&col=zh-CN&conBW=1&conOP=1&cpa=1&dai=4&dis=0&ltr=http%3A%2F%2Fwww.aboutyun.com%2Fthread-11738-1-1.html&ltu=http%3A%2F%2Fwww.aboutyun.com%2Fthread-7450-1-2.html&lu_161=0&lunum=6&n=92051019_cpr&pat=6&pcs=1349x623&pih=0&pis=10000x10000&piw=0&ps=2555x396&psr=1366x768&pss=1349x2582&ptbg=90&ptp=0&ptt=0&qn=937ff522596076de&rad=&rsi0=750&rsi1=250&rsi5=4&rss0=%23FFFFFF&rss1=%23FFFFFF&rss2=%23000000&rss3=&rss4=&rss5=&rss6=%23e10900&rss7=&scale=&skin=tabcloud_skin_3&stid=5&td_id=1995025&titFF=%25E5%25BE%25AE%25E8%25BD%25AF%25E9%259B%2585%25E9%25BB%2591&titFS=14&titSU=0&titTA=left&tn=baiduCustNativeAD&tpr=1435393645667&ts=1&version=2.0&xuanting=0&dtm=BAIDU_DUP2_SETJSONADSLOT&dc=2&di=u1995025&ti=hive%E9%9D%A2%E8%AF%95%E9%A2%98%E7%9B%AE%EF%BC%9A%E8%A1%A8%E5%A4%A7%E6%A6%82%E6%9C%892T%E5%B7%A6%E5%8F%B3%EF%BC%8C%E5%AF%B9%E8%A1%A8%E6%95%B0%E6%8D%AE%E8%BD%AC%E6%8D%A2-%E9%9D%A2%E8%AF%95%E9%A2%98-about%E4%BA%91%E5%BC%80%E5%8F%91&tt=1435393645654.753.909.910" align="center,center" marginwidth="0" marginheight="0" scrolling="no" frameborder="0" allowtransparency="true" style="word-wrap: break-word;">
	回复 使用道具 举报

hyj 414 主题 787 帖子 3629 积分 版主 积分 3629 收听TA 发消息	沙发   发表于 2014-4-20 14:53:20  |  只看该作者 捐助：hadoop2.x视频（hadoop系列及机器学习storm、spark视频） 给你个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; 复制代码
	欢迎加入about云群425860289 、432264021 ，云计算爱好者群，关注about云腾讯认证空间
	回复 支持 反对 使用道具 举报

阿飞 39 主题 71 帖子 510 积分 版主 积分 510 收听TA 发消息	板凳   楼主 |  发表于 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 0 主题 124 帖子 426 积分 中级会员 积分 426 收听TA 发消息	地板   发表于 2014-4-27 10:37:45  |  只看该作者 我是来打酱油的
	回复 支持 反对 使用道具 举报

pride1000 0 主题 12 帖子 53 积分 注册会员 积分 53 收听TA 发消息	5#   发表于 2014-5-16 15:12:29  |  只看该作者 能说一下这两种方法有什么优缺点吗？
	回复 支持 反对 使用道具 举报

xin07020220 0 主题 13 帖子 722 积分 中级会员 积分 722 收听TA 发消息	6#   发表于 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
	回复 支持 反对 使用道具 举报

yangq 0 主题 21 帖子 91 积分 注册会员 积分 91 收听TA 发消息

7#   发表于 2015-4-24 11:40:25  |  只看该作者 我想问一下 hive能跟hbase里的表映射   ，那hive能不能跟 mysql映射  ，这个mysql不是hive底层的元数据库