實作五

Hive (2) : JOIN SELECT 跨表查詢

資料集:魔球

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

建立 Hive 資料表

  1. 先瞭解 Batting.csv 這個檔案的欄位跟資料型態。說明文件中提到
    2.2 Batting Table
    playerID       Player ID code
    yearID         Year
    stint          player's stint (order of appearances within a season)
    teamID         Team
    lgID           League
    G              Games
    G_batting      Game as batter
    AB             At Bats
    R              Runs
    H              Hits
    2B             Doubles
    3B             Triples
    HR             Homeruns
    RBI            Runs Batted In
    SB             Stolen Bases
    CS             Caught Stealing
    BB             Base on Balls
    SO             Strikeouts
    IBB            Intentional walks
    HBP            Hit by pitch
    SH             Sacrifice hits
    SF             Sacrifice flies
    GIDP           Grounded into double plays
    G_Old          Old version of games (deprecated)
    
  2. 根據第一行的資料,我們可以決定資料表每個欄位的資料型態

    Batting 的資料型態
    欄位 範例 資料型態
    playerID aardsda01 STRING
    yearID 2004 INT
    stint 1 INT
    teamID SFN STRING
    lgID NL STRING
    G 11 INT
    G_batting 11 INT
    AB 0 INT
    R 0 INT
    H 0 INT
    2B 0 INT
    3B 0 INT
    HR 0 INT
    RBI 0 INT
    SB 0 INT
    CS 0 INT
    BB 0 INT
    SO 0 INT
    IBB 0 INT
    HBP 0 INT
    SH 0 INT
    SF 0 INT
    GIDP 0 INT
    G_old 11 INT
  3. 根據以上瞭解,我們使用 HiveQL 建立資料表的語法來建立 Batting 資料表
    [user00@master baseball]$ export DBID=$USER
    [user00@master baseball]$ hive -e "create table $DBID.Batting
    ( playerID STRING, yearID INT, stint INT, teamID STRING, lgID STRING,
      G INT, G_batting INT, AB INT, R INT, H INT, B2 INT, B3 INT, HR INT,
      RBI INT, SB INT, CS INT, BB INT, SO INT, IBB INT, HBP INT, SH INT,
      SF INT, GIDP INT, G_old 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 \"Batting.csv\" OVERWRITE INTO TABLE $DBID.Batting;"
    

檢查匯出結果

  1. SHOW TABLES; 來查詢目前的資料庫有哪幾個資料表。確認是否有正確產生 Batting 資料表。
    [user00@master baseball]$ hive
    Hive history file=/tmp/user00/hive_job_log_e619c08f-6bb1-46f4-9bd2-45da4a49408a_43118052.txt
    hive> USE userXX; SHOW TABLES;
    OK
    Time taken: 0.031 seconds
    OK
    batting
    master
    Time taken: 0.22 seconds
    
  2. 其次,我們可以檢查一下剛剛建立的 userXX.batting 資料表,內容是否正常。
    hive> SELECT * FROM userXX.batting LIMIT 10;
    

JOIN 跨表查詢

  1. JOIN SELECT 的語法是 SELECT <欄位> FROM <資料表A> JOIN <資料表B> ON <條件>
    hive> use userXX;
    hive> SELECT A.PlayerID, B.teamID, B.AB, B.R, B.H, B.B2, B.B3, B.HR, B.RBI FROM Master A JOIN BATTING B ON A.playerID = B.playerID;
    hive> exit
    
  2. <備註> 在虛擬機器中執行這個查詢可能會有點吃力,請耐心等候實驗結果。
    zuverge01 BAL 17  0 2 0 0 0 2
    zuverge01 BAL 23  1 3 0 0 0 0
    zuverge01 BAL 9 0 2 0 1 0 2
    zuverge01 BAL 0 0 0 0 0 0 0
    zwilldu01 CHA 87  7 16  5 0 0 5
    zwilldu01 CHF 592 91  185 38  8 16  95
    zwilldu01 CHF 548 65  157 32  7 13  94
    zwilldu01 CHN 53  4 6 1 0 1 8
    Time taken: 105.467 seconds
    

參考資料

  1. Build a data library with Hive”, By Peter J. Jamack, Big Data Analytics Consultant, 21 May 2013, IBM developerWorks

本文件最後更新於: