Lab 011 - jazzwang/hive_labs GitHub Wiki

實作十一

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

接下來,我們要做的步驟有點複雜。稱為 CTAS 結構,也就是 CREAT TABLE ... AS SELECT 的縮寫。之所以需要這麼做,是因為有時候必須要將多個資料表,轉換成一個龐大的表,以方便進行統計分析。

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

資料集:魔球

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

建立 Hive 資料表

  1. 先瞭解 .csv 這個檔案的欄位跟資料型態。說明文件中提到

  2. 根據第一行的資料,我們可以決定資料表每個欄位的資料型態

    欄位 範例 資料型態


    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 SF NULL GIDP NULL

    Table: Pitching 的資料型態

  3. 根據以上瞭解,我們使用 HiveQL 建立資料表的語法來建立 Pitching 與 Fielding 資料表

hive> create table baseball.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 <資料表名稱>。
hive> LOAD DATA LOCAL INPATH "Pitching.csv" OVERWRITE INTO TABLE baseball.Pitching;

檢查匯出結果

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

CTAS 查詢

  1. CTAS 查詢的語法是 CREATE TABLE <資料庫名稱>.<資料表名稱> AS ( SELECT <欄位> FROM <資料表A> JOIN <資料表B> ON <條件1> JOIN <資料表C> ON <條件2>);
hive> CREATE DATABASE baseball_stats;
hive> CREATE TABLE baseball_stats.fact_player_stats AS 
 SELECT A.PlayerID STRING, B.teamID INT, B.AB, B.R, B.H, B.2B, B.3B, 
 B.HR, B.RBI, P.W, P.L, P.G, P.GS, P.CG, P.SHO FROM baseball.master A 
 JOIN baseball.batting  B ON A.playerID = B.playerID
 JOIN baseball.pitching P ON B.playerID = P.playerID ;
  1. <備註> 在虛擬機器中執行這個查詢可能會有點吃力,請耐心等候實驗結果。

參考資料

  1. "Build a data warehouse with Hive", By Peter J. Jamack, Big Data Analytics Consultant, 25 June 2013, IBM developerWorks
⚠️ **GitHub.com Fallback** ⚠️