hive面試題目：表大概有2T左右，對錶資料轉換__商務智能(Pentaho)

http://www.aboutyun.com/thread-7450-1-1.html

有一張很大的表：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）

給你個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;
複製代碼