Big Data 金象盃競賽

競賽內容暨範例操作

競賽內容

本次競賽為了讓各位參賽者都能體驗到 Big Data 的相關技術，主辦單位將會提供已經建置好的 Hadoop 競賽環境，參賽者們可直接使用 Pig、Hive 分析工具，並在 75 分鐘內完成主辦單位指定的五道題目。競賽資料下載、競賽資料說明、競賽題目範例，以下將一一說明:

競賽資料下載


壓縮檔名稱  BC3.tar.gz

競賽資料下載及解壓縮命令如下

$ wget http://bigdata2016.twisc.ncku.edu.tw/Download/BC3.tar.gz
$ tar xvfz BC3.tar.gz
$ ls -al BC3

總共3個檔案，2個目錄

  1. hive_example (hive 競賽範例)
  2. pig_example (pig 競賽範例)
  3. timezones.tsv (時區對照表)
  4. userdemand.tsv (使用者個人資料)
  5. userprofile.tsv (使用者點聽紀錄)

注意!! 競賽當天，資料放在 HDFS 根目錄的 dataset 目錄中


競賽資料點我下載

競賽資料說明

情境主題  線上音樂串流網站


userprofile.tsv  (使用者個人資料)

資料格式說明如下，每個欄位之間以 Tab 鍵區隔

欄位名稱 描述 型態
userid 使用者 ID chararray
gender 性別 chararray
age 年齡 int
country 國家 chararray
registered 註冊日期 chararray

userdemand.tsv  (使用者點聽紀錄)

資料格式說明如下，每個欄位之間以 Tab 鍵區隔

欄位名稱 描述 型態
userid 使用者 ID chararray
time 點聽時間 chararray
artname 歌手名稱 chararray
traname 歌曲名稱 chararray

timezones.tsv  (時區對照表)

資料格式說明如下，每個欄位之間以 Tab 鍵區隔

欄位名稱 描述 型態
Standard Offset 標準偏移 chararray
Canonical ID 規範 ID chararray
Aliases 別名 chararray

Pig 範例


注意!!

範例之系統環境為 Linux，以下說明使用的命令，只有執行 Pig 檔案與查看輸出結果，練習時，請先確認 HDFS 系統已啟動，且競賽資料已上傳至 HDFS。範例檔案請至競賽資料下載，每題範例都會有 Pig Latin 的說明，請對照範例檔案。

在 HDFS 上創建目錄，並將資料上傳至所屬目錄 命令如下

$ cd BC3
$ hdfs dfs -mkdir userdemand
$ hdfs dfs -mkdir userprofile	
$ hdfs dfs -put userdemand.tsv userdemand
$ hdfs dfs -put userprofile.tsv userprofile

操作範例前，請先進入 pig_example 目錄

$ cd BC3/pig_example

注意!! 題目輸出如有要求格式，會特別標註，請仔細查看

注意!! 範例八中的星期對照表，在競賽時會放在 HDFS 根目錄的 dataset 目錄中


使用 userprofile.tsv 檔案

Pig Latin 說明

  1. LOAD userprofile.tsv 檔案
  2. 依 gender (性別)的欄位進行 GROUP
  3. 使用 FOREACH、COUNT 產生男性與女性的會員數計數
  4. 從 HDFS 中刪除名為 example01 的目錄
  5. 輸出結果至 HDFS
$ pig -f example01.pig
$ hdfs dfs -cat pig_example01/*
f	110
m	155


使用 userprofile.tsv 檔案

Pig Latin 說明

  1. LOAD userprofile.tsv 檔案
  2. 依 country (國家)的欄位進行 GROUP
  3. 使用 FOREACH、COUNT 產生每個國家會員數的計數
  4. 同時使用 ORDER BY 進行降冪排序，LIMIT 取前5列
  5. 從 HDFS 中刪除名為 example02 的目錄
  6. 輸出結果至 HDFS
$ pig -f example02.pig
$ hdfs dfs -cat pig_example02/*
United States	60
United Kingdom	35
Poland	19
Turkey	14
Canada	12


使用 userprofile.tsv 檔案

Pig Latin 說明

  1. LOAD userprofile.tsv 檔案
  2. 使用 FILTER 過濾出20~30歲的人
  3. 依所有欄位進行 GROUP
  4. 使用 FOREACH、COUNT 產生20~30歲的人的計數
  5. 從 HDFS 中刪除名為 example03 的目錄
  6. 輸出結果至 HDFS
$ pig -f example03.pig
$ hdfs dfs -cat pig_example03/*
189


使用 userprofile.tsv 檔案

Pig Latin 說明

  1. LOAD userprofile.tsv 檔案
  2. 依 age (年紀)的欄位進行 GROUP
  3. 使用 FOREACH、COUNT 產生每個年紀的人的計數
  4. 同時使用 ORDER BY 進行降冪排序，LIMIT 取前1行
  5. 從 HDFS 中刪除名為 example04 的目錄
  6. 輸出結果至 HDFS
$ pig -f example04.pig
$ hdfs dfs -cat pig_example04/*
75	1


使用 userdemand.tsv 檔案

注意:排行榜順序的依據為個別聽眾總數

注意:排行順序依個別聽眾總數優先，計數一樣者，以英文字 A~Z 順序排序

注意:輸出的第一個欄位為 traname (歌曲)，第二個欄位為 artname (歌手)，第三個欄位為個別聽眾總數，並使用 tab 分隔

Pig Latin 說明

  1. LOAD userdemand.tsv 檔案
  2. 使用 FOREACH 取第一個欄位(userid)、第三個欄位(artname)與第四個欄位(traname)
  3. 使用 DISTINCT 消除重複
  4. 依 traname (歌曲) 與 artname (歌手)，兩個欄位進行 GROUP
  5. 使用 FOREACH、FLATTEN、COUNT 產生每首歌曲的個別聽眾總數
  6. 使用 ORDER BY 進行多欄位排序，先依個別聽眾總數進行降冪排序，再依歌曲名稱進行升冪排序
  7. 使用 LIMIT 取前10行
  8. 從 HDFS 中刪除名為 example05 的目錄
  9. 輸出結果至 HDFS
$ pig -f example05.pig
$ hdfs dfs -cat pig_example05/*
Karma Police    Radiohead       95
Paranoid Android        Radiohead       92
Creep   Radiohead       91
Somebody Told Me        The Killers     91
Wonderwall      Oasis   89
No Surprises    Radiohead       86
Take Me Out     Franz Ferdinand 86
Love Will Tear Us Apart Boy Division    85
Such Great Heights      The Postal Service      85
Smells Like Teen Spirit Nirvana 84


使用 userdemand.tsv 檔案

注意:點播次數的依據為個別聽眾總數

Pig Latin 說明

  1. LOAD userdemand.tsv 檔案
  2. 使用 FOREACH 取第一個欄位(userid)與第三個欄位(artname)
  3. 使用 DISTINCT 消除重複
  4. 依 artname (歌手)的欄位進行 GROUP
  5. 使用 FOREACH、FLATTEN、COUNT 產生每個歌手的個別聽眾總數
  6. 使用 FILTER 過濾出點播次數小於二的歌手
  7. 依所有的欄位進行 GROUP
  8. 使用 FOREACH、COUNT 產生點播次數小於二的歌手總數
  9. 從 HDFS 中刪除名為 example06 的目錄
  10. 輸出結果至 HDFS
$ pig -f example06.pig
$ hdfs dfs -cat pig_example06/*
46922


使用 userprofile.tsv 與 userdemand.tsv 檔案

首先查看土耳其的時區，並把 Canonical ID 記錄下來

$ cat timezones.tsv | grep 'Turkey'
+02:00  Europe/Istanbul Asia/Istanbul, Turkey

Pig Latin 說明

  1. LOAD userdemand.tsv 檔案
  2. LOAD userprofile.tsv 檔案
  3. 使用 FILTER 過濾出國家為土耳其的會員
  4. 使用 JOIN 將土耳其會員與 userdemand.tsv 連接
  5. 使用 FOREACH、ToDate 產生時區校正後的土耳其會員的點播時間
  6. 從 HDFS 中刪除名為 example07 的目錄
  7. 輸出結果至 HDFS

為了方便，我們只看前五列

$ pig -f example07.pig
$ hdfs dfs -cat pig_example07/* | head -n 5
user_000026     2007-11-24T19:19:49.000+02:00   Atrium Musicæ De Madrid Spagnioletta
user_000026     2007-11-24T19:34:58.000+02:00   Antonio Vivaldi Vcd_275_11_Autumn
user_000026     2007-11-24T19:38:05.000+02:00   Antonio Vivaldi Vcd_275_11_Winter
user_000026     2007-11-24T19:39:48.000+02:00   Antonio Vivaldi Vcd_275_3_Spring
user_000026     2007-11-24T20:01:57.000+02:00   Joculatores Upsalienses Falalalan


使用 userdemand.tsv 檔案

注意:輸出格式，使用者 ID,點聽時間,點聽時間在星期幾,點聽時間在第幾周,歌手名稱,歌曲名稱

此題我們需要產生星期對照表，需要使用 cal 命令，首先安裝 cal 套件

$ sudo apt-get update
$ sudo apt-get install gnome-calculator

撰寫一個產生星期對照表的程式，名為 calendar.sh

$ nano calendar.sh

程式內容如下

#!/bin/bash			#宣告使用 bash 的語法
rm -r calendar.csv              #刪除名為 calendar.csv 的檔案
for year in $(seq 2005 2009)    #建立年份迴圈 2005 到 2009
do                       
  for month in $(seq -w 12)     #建立月份迴圈 01 到 12 ( seq -w 自動補 0 )
  do
    for aweek in $(seq 1 7)     #建立星期幾迴圈 1 到 7
    do 
      #使用 ncal 產生某年某月的日曆，並刪除第一行，再依星期幾抓第幾行，最後使用 fmt -u 縮減空格
      cal=$(ncal $month $year | sed '1d' | sed -n "$aweek"p | fmt -u)
      week=$(echo $cal | cut -d' ' -f1)    #取出星期幾
      day=$(echo $cal | cut -d' ' -f2-)    #取出日期
      for d in $day    #建立日期迴圈，使用剛剛取出的日期
      do
        #產生三個欄位，以逗點間隔，格式為:年份-月份-日期,星期幾,週數，並輸出至 calendar.csv 
        echo "$year-$month-$d,$week,`date -d $year-$month-$d +%U`" >> calendar.csv
      done
    done
  done
done

賦予程式執行的權限，並執行它，在檢查是否有成功輸出檔案

$ chmod +x calendar.sh
$ ./calendar.sh
$ ls -al
calendar.csv

確認完畢後，先在 HDFS 上創建專屬目錄，再上傳至該目錄

$ hdfs dfs -mkdir calendar
$ hdfs dfs -put calendar.csv calendar
$ hdfs dfs -ls calendar
Found 1 items
-rw-r--r--   2 bigred biguser      30502 2016-11-14 14:18 calendar/calendar.csv

Pig Latin 說明

  1. LOAD userdemand.tsv 檔案
  2. LOAD calendar.csv 檔案
  3. 使用 FOREACH、SUBSTRING 對 userdemand 額外產生一個欄位只有年月日
  4. 使用 JOIN 將上一個產生的新表單與 calendar.csv 連接
  5. 使用 FOREACH 產生題目要求的格式
  6. 從 HDFS 中刪除名為 example08 的目錄
  7. 輸出結果至 HDFS

為了方便，我們只看前五列，並檢查星期與週數是否正確

$ pig -f example08.pig
$ hdfs dfs -cat pig_example08/* | head -n 5
user_000285     2005-02-14T06:28:31Z    Mo      07      Weezer  Buddy Holly
user_000285     2005-02-14T06:21:27Z    Mo      07      Weezer  No One Else
user_000285     2005-02-14T06:18:16Z    Mo      07      Weezer  My Name Is Jonas
user_000285     2005-02-14T06:15:26Z    Mo      07      Weezer  Photograph
user_000285     2005-02-14T06:09:32Z    Mo      07      Weezer  Falling For You

$ cal 2 2005
   February 2005
Su Mo Tu We Th Fr Sa
       1  2  3  4  5
 6  7  8  9 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28

$ date -d 20050214 +%U
07

使用 userprofile.tsv 與 userdemand.tsv 檔案

注意:排行榜順序的依據為個別聽眾總數

注意:排行順序依個別聽眾總數優先，計數一樣者，以英文字 A~Z 順序排序

注意:國家順序沒有規定

注意:輸出一列一個國家，後面接該國家的熱門歌曲排行榜前三名，格式無固定，請參考解答

Pig Latin 暫無說明，請參考範例檔


國家數量多，為了方便，我們只看美國 ( United States )

$ pig -f example09.pig
$ hdfs dfs -cat pig_example09/* | grep 'United States'
United States   {(Such Great Heights,The Postal Service,37),(The District Sleeps Alone Tonight,The Postal Service,30),(I Will Follow You Into The Dark,Death Cab For Cutie,28)}
United States Minor Outlying Islands    {("En Gallop",Joanna Newsom,1),(#1 Crush,Garbage,1),(#1 Crush (Live),Garbage,1)}

Hive 範例


注意!!

範例之系統環境為 Linux，以下說明使用的命令，只有執行 Hive 檔案與查看輸出結果，練習時，請先確認 HDFS 系統已啟動，且競賽資料已上傳至 HDFS。範例檔案請至競賽資料下載，每題範例都會有 Hive SQL 的說明，請對照範例檔案。

在 HDFS 上創建目錄，並將資料上傳至所屬目錄 命令如下

$ cd BC3
$ hdfs dfs -mkdir userdemand
$ hdfs dfs -mkdir userprofile	
$ hdfs dfs -put userdemand.tsv userdemand
$ hdfs dfs -put userprofile.tsv userprofile

操作範例前，請先進入 hive_example 目錄，並先 Create Table

$ cd BC3/hive_example
$ hive -S -f create_table.hql
$ hive -S -e 'show tables;'
userdemand
userprofile

注意!! 題目輸出如有要求格式，會特別標註，請仔細查看


使用 userprofile.tsv 檔案

HiveQL 說明

依 gender (性別)的欄位進行 GROUP，並使用 COUNT 產生男性與女性的會員數計數

$ hive -S -f example01.hql
$ hdfs dfs -cat hive_example01/*
f       110
m       155


使用 userprofile.tsv 檔案

HiveQL 說明

依 country (國家)的欄位進行 GROUP，並使用 COUNT 產生每個國家會員數的計數，同時使用 ORDER BY 進行降冪排序，LIMIT 取前5列

$ hive -S -f example02.hql
$ hdfs dfs -cat hive_example02/*
United States   60
United Kingdom  35
Poland  19
Turkey  14
Canada  12


使用 userprofile.tsv 檔案

HiveQL 說明

使用 WHERE 過濾出20~30歲的人，並使用 COUNT 產生20~30歲的人的計數總數

$ hive -S -f example03.hql
$ hdfs dfs -cat hive_example03/*
189


使用 userprofile.tsv 檔案

HiveQL 說明

依 age (年紀)的欄位進行 GROUP，並使用COUNT 產生每個年紀的人的計數，同時使用 ORDER BY 進行降冪排序，LIMIT 取前1行

$ hive -S -f example04.hql
$ hdfs dfs -cat hive_example04/*
75      1


使用 userprofile.tsv 檔案

HiveQL 說明

使用 DISTINCT 消除重複的第一個欄位(userid)、第三個欄位(artname)與第四個欄位(traname)，依 traname (歌曲) 與 artname (歌手)，兩個欄位進行 GROUP，並使用 COUNT 產生每首歌曲的個別聽眾總數，再使用 ORDER BY 進行多欄位排序，先依個別聽眾總數進行降冪排序，再依歌曲名稱進行升冪排序，最後使用 LIMIT 取前10行

$ hive -S -f example05.hql
$ hdfs dfs -cat hive_example05/*
Karma Police    Radiohead       95
Paranoid Android        Radiohead       92
Creep   Radiohead       91
Somebody Told Me        The Killers     91
Wonderwall      Oasis   89
No Surprises    Radiohead       86
Take Me Out     Franz Ferdinand 86
Love Will Tear Us Apart Boy Division    85
Such Great Heights      The Postal Service      85
Smells Like Teen Spirit Nirvana 84


使用 userprofile.tsv 檔案

HiveQL 說明

使用 DISTINCT 消除重複的第一個欄位(userid)與第三個欄位(artname)，依 artname (歌手)的欄位進行 GROUP，並使用COUNT 產生每個歌手的個別聽眾總數，再使用 HAVING 過濾出點播次數小於二的歌手，並使用 COUNT 產生點播次數小於二的歌手總數

$ hive -S -f example06.hql
$ hdfs dfs -cat hive_example07/*
46922

參考資料