實作六

Hive (3) : 透過 CTAS 結構建立資料表

接下來,我們要做的步驟有點複雜。稱為 CTAS 結構,也就是 CREAT TABLE … AS SELECT 的縮寫。

之所以需要這麼做,是因為有時候必須要將多個資料表,轉換成一個龐大的表,以方便進行統計分析。

更多關於 Hive 的語法,請參考 官方網站說明 Hive Data Definition Language

資料集:魔球

  1. 我們將沿用 Hive (1) 的資料集。
  2. 接下來,我們再來把 Pitching.csv (Pitching statistics, 勝投率) 轉成另一個資料表。

建立 Hive 資料表

  1. 先瞭解 Pitching.csv 這個檔案的欄位跟資料型態。
  2. 根據第一行的資料,我們可以決定資料表每個欄位的資料型態

    Pitching 的資料型態
    欄位 範例 資料型態
    playerID aardsda01 STRING
    yearID 2004 INT
    stint 1 INT
    teamID SFN STRING
    lgID NL STRING
    W 1 INT
    L 0 INT
    G 11 INT
    GS 0 INT
    CG 0 INT
    SHO 0 INT
    SV 0 INT
    IPouts 32 INT
    H 20 INT
    ER 8 INT
    HR 1 INT
    BB 10 INT
    SO 5 INT
    BAOpp 0.41 FLOAT
    ERA 6.75 FLOAT
    IBB 0 INT
    WP 0 INT
    HBP 2 INT
    BK 0 INT
    BFP 61 INT
    GF 5 INT
    R 8 INT
    SH NULL INT
    SF NULL INT
    GIDP NULL INT
  3. 根據以上瞭解,我們使用 HiveQL 建立資料表的語法來建立 Pitching 與 Fielding 資料表
    [user00@master baseball]$ export DBID=$USER
    [user00@master baseball]$ hive -e "create table $DBID.Pitching
    ( playerID STRING, yearID INT, stint INT, teamID STRING, lgID STRING, W INT,
      L INT, G INT, GS INT, CG INT, SHO INT, SV INT, IPouts INT, H INT, ER INT,
      HR INT, BB INT, SO INT, BAOpp FLOAT, ERA FLOAT, IBB INT, WP INT, HBP INT,
      BK INT, BFP INT, GF INT, R INT, SH INT, SF INT, GIDP INT )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;"
    

匯入 CSV 資料到 Hive 資料表

  1. HiveQL 自 CSV 檔案匯入資料到 Hive 資料表的語法是 `LOAD DATA LOCAL INPATH <檔案路徑> [OVERWRITE] INTO TABLE <資料表名稱>。
    [user00@master baseball]$ hive -e "LOAD DATA LOCAL INPATH \"Pitching.csv\" OVERWRITE INTO TABLE $DBID.Pitching;"
    

檢查匯出結果

  1. SHOW TABLES; 來查詢目前的資料庫有哪幾個資料表。確認是否有正確產生 Pitching 資料表。
    hive> USE userXX; SHOW TABLES;
    OK
    batting
    master
    mysql_data
    pitching
    Time taken: 0.059 seconds
    
  2. 其次,我們可以檢查一下剛剛建立的 userXX.pitching 資料表,內容是否正常。
    hive> SELECT * FROM userXX.pitching LIMIT 10;
    

CTAS 查詢

  1. CTAS 查詢的語法是 CREATE TABLE <資料庫名稱>.<資料表名稱> AS ( SELECT <欄位> FROM <資料表A> JOIN <資料表B> ON <條件1> JOIN <資料表C> ON <條件2>);
    hive> use userXX;
    hive> CREATE TABLE fact_player_stats AS SELECT A.PlayerID, B.teamID, B.AB, B.R, B.H, B.B2, B.B3, B.HR, B.RBI, P.G FROM Master A JOIN BATTING B ON A.playerID = B.playerID JOIN Pitching P ON B.playerID = P.playerID;
    
  2. <備註> 在虛擬機器中執行這個查詢可能會有點吃力,請耐心等候實驗結果。
    Job 0: Map: 3  Reduce: 1   Accumulative CPU: 15.99 sec   HDFS Read: 0 HDFS Write: 0 SUCESS
    Total MapReduce CPU Time Spent: 15 seconds 990 msec
    OK
    Time taken: 128.734 seconds
    
  3. 最後,讓我們檢查所產生的 userXX.fact_player_stats 資料表內容。
    hive> SELECT * FROM userXX.fact_player_stats LIMIT 10;
    OK
    aardsda01 CHA 0 0 0 0 0 0 0 45
    aardsda01 CHA 0 0 0 0 0 0 0 73
    aardsda01 CHA 0 0 0 0 0 0 0 11
    aardsda01 CHA 0 0 0 0 0 0 0 47
    aardsda01 CHA 0 0 0 0 0 0 0 25
    aardsda01 CHA 0 0 0 0 0 0 0 1
    aardsda01 CHA 0 0 0 0 0 0 0 53
    aardsda01 BOS 1 0 0 0 0 0 0 45
    aardsda01 BOS 1 0 0 0 0 0 0 73
    aardsda01 BOS 1 0 0 0 0 0 0 11
    Time taken: 0.177 seconds
    

參考資料

  1. Build a data warehouse with Hive”, By Peter J. Jamack, Big Data Analytics Consultant, 25 June 2013, IBM developerWorks

本文件最後更新於: