---
title: 2020 金象盃 練習試題 競賽內容暨範例操作
tags: 2020金象盃
description: 2020 金象盃練習賽
lang: zh
---
# 2020 金象盃 練習試題 競賽內容暨範例操作

----------
## 競賽資料下載

壓縮檔名稱 BC3.rar

[**連結資料點我下載-1**](https://drive.google.com/file/d/1hKTyz2xfU2YQiW2KSp8AQIbVEhNY7q6U/view?usp=sharing)

[**連結資料點我下載-2**](https://drive.google.com/drive/folders/1rn8okKblHdjyx5OE5_GwG2X4FL0616EH?usp=sharing)

[**連結資料點我下載-3**](https://drive.google.com/file/d/1erymo2ac9Hj0XbEi4j6Gkd0AscknzUb7/view?usp=sharing) (zip 檔案下載)

----------
## 競賽資料說明

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

### 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 範例

:::danger
注意!!
範例之系統環境為 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

:::warning
注意!! 題目輸出如有要求格式，會特別標註，請仔細查看
注意!! 範例八中的星期對照表，在競賽時會放在 HDFS 根目錄的 dataset 目錄中
:::

### 範例一 男性與女性的會員數分別是多少

:::spoiler

**使用 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

:::

### 範例二 會員數量排行 顯示前五名國家

:::spoiler
**使用 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

:::
### 範例三 年紀在20~30歲的人有多少位

:::spoiler
**使用 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

:::

### 範例四 年紀最長者有幾人

:::spoiler
**使用 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

:::

### 範例五 熱門歌曲排行榜前十名

:::spoiler
**使用 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

:::

### 範例六 點播次數小於二的歌手總數

:::spoiler
**使用 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

:::

### 範例七 土耳其會員點播時間時區校正

:::spoiler
**使用 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

:::

### 範例八 日期加上星期

:::spoiler
**使用 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

:::

### 範例九 各國的熱門歌曲前三名

:::spoiler
**使用 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 範例

:::danger
注意!!
範例之系統環境為 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

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

### 範例一 男性與女性的會員數分別是多少

:::spoiler

**使用 userprofile.tsv 檔案**

HiveQL 說明

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

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

:::

### 範例二 會員數量排行 顯示前五名國家

:::spoiler

**使用 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

:::

### 範例三 年紀在20~30歲的人有多少位

:::spoiler

**使用 userprofile.tsv 檔案**

HiveQL 說明

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

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

:::

### 範例四 年紀最長者有幾人

:::spoiler

**使用 userprofile.tsv 檔案**

HiveQL 說明

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

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

:::

### 範例五 熱門歌曲排行榜前十名

:::spoiler

**使用 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

:::

### 範例六 點播次數小於二的歌手總數

:::spoiler

**使用 userprofile.tsv 檔案**

HiveQL 說明

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

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

:::

----------

## 參考資料
> [Apache Pig - Pig Latin Basics (點我)](http://pig.apache.org/docs/r0.16.0/basic.html)
> [Apache Hive - Hive SQL Operations (點我)](https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-SQLOperations)
> [Microsoft Azure - Pig 與 HDInsight 上的 Hadoop (點我)](https://azure.microsoft.com/zh-tw/documentation/articles/hdinsight-use-pig/)
> [Microsoft Azure - 搭配 HDInsight 中的 Hadoop 使用 Hive 和 HiveQL 來分析範例 Apache Log4j 檔案 (點我)](https://azure.microsoft.com/zh-tw/documentation/articles/hdinsight-use-hive/)
----------