Peter 工程日誌

如何透過Greenplum查詢Hadoop HDFS上的資料

peter279k

前言

這篇文章，是我在開發的資料視覺化系統的過程中，所猜測的資料庫結構，因為這個開發系統是這樣的：

本文章中，是在探討我在將系統移入的過程中，如何一步步的發現資料庫的架構

資料庫架構

原本一開始聽到的架構是，我以為是長這樣的

後來，到後面的移入內網的時候，我慢慢從它們講的名詞猜出其實是長下面這樣子的架構。

一開始我還猜資料(HDFS)部份有使用HBase，不過後來廠商說只使用HDFS。

其架構流程如下：

資料庫架構差異

這兩者其實有很大的差異，其差別如下：

Greenplum 資料庫建立外部資料表

本章節，就是在描述如何使用建立外部資料表來載入HDFS上的資料。

首先，先要了解，Greenplum 可以當作是一個查詢的介面，透過它去查詢資料。

並藉由此機制建立readable external table，可以讀取的協定如下：

外部資料表(external table)相關介紹

從官方文件中可以得知，這類的external table有分成下列幾種

在Greenplum官方範例文件中，有如下的敘述：

假設我們有一個資料庫檔案，叫做 sales，分區資料表方式如下圖：

其分區資料表概念就是，有一個資料表是parent table，那依照以月分區的方式建立多個child tables

分區的方式如下：

Example Exchanging a Partition with an External Table

本章節，會以Greenplum官方文件做範例，透過官方提供的sales範例來呈現如何做到建立外部資料表並將它們做成分區，以及如何讀取它們。

從上面解釋外部資料表的不同類型，我們可以知道可寫跟可讀的資料表上的差異，那為了要讓資料表可以寫入資料與查詢，我們要做的建立外部資料表方式步驟如下：

首先，我們需要先建立一個名叫sales的資料表，其SQL語法如下：


CREATE TABLE sales (id int, year int, qtr int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( PARTITION yr START (2010) END (2014) EVERY (1) ) ;

接著，要確定讀取外部資料的來源是存放在哪個地方，在此範例中，假設外部要存取的檔案是放在 gpfdist (Greenplum Parallel File Server)

接著先建立一個可以寫入的外部資料表，相關SQL語法如下：


CREATE WRITABLE EXTERNAL TABLE my_sales_ext ( LIKE sales_1_prt_yr_1 )
LOCATION ( 'gpfdist://gpdb_test/sales_2010' )
FORMAT 'csv'
DISTRIBUTED BY (id) ;

接著在建立一個readable可以讀取的外部資料表


CREATE EXTERNAL TABLE sales_2010_ext ( LIKE sales_1_prt_yr_1)
LOCATION ( 'gpfdist://gpdb_test/sales_2010' )
FORMAT 'csv' ;

接著從leaf child partition上的資料寫進先前步驟建立的external table中


INSERT INTO my_sales_ext SELECT * FROM sales_1_prt_yr_1 ;

接著把先前建立的external table與目前存在的leaf child partition table互相交換，相關SQL語法如下：


ALTER TABLE sales ALTER PARTITION yr_1
EXCHANGE PARTITION yr_1
WITH TABLE sales_2010_ext WITHOUT VALIDATION;

上面步驟做完之後，資料表就會變成：

接著把sales_2010_ext資料表，因為交喚之後，我們不需要external table了，所以可以把它移除。


DROP TABLE sales_2010_ext ;

接著我們把分區名稱，做一個修改，因為本來，分區名稱在建立sales資料表的時候是下列的分區名稱：

那也可以透過更改分區名稱，把 sales_1_prt_yr_1 資料表標註成這是一個external table，那這是一個可選的工作。相關語法如下：


ALTER TABLE sales RENAME PARTITION yr_1 TO yr_1_ext ;

讀取外部資料

從上述的章節，我們可以初步的知道如何建立外部資料表載入外部檔案並成為分區資料表的方式。那如果是要載入其他檔案系統上的檔案呢?

我們可以以廠商使用的HDFS為例：

在官方文件中，Greenplum 5.2.0版本時候，gphdfs已經廢棄了，這是一個Greenplum設計的協定，並專門去讀取在HDFS上的檔案用的。

官方強烈建議使用它們設計的另一個協定，叫做PXF (The Greenplum Platform Extension Framework)，這是一個Greenplum所設計的統一介面協定。

所以建立外部資料表方式就可以改成：


CREATE EXTERNAL TABLE pxf_hdfs_text(location text, month text, num_orders int, total_sales float8)
LOCATION ('pxf://data/pxf_examples/pxf_hdfs_simple.txt?PROFILE=hdfs:text')
FORMAT 'TEXT' (delimiter=E',');

CSV 檔，也可以使用上述的SQL語法去建立相關的資料表

結論

參考資料

Exit mobile version