教程：如何通過DLA實現資料檔案格式轉換

金絡發表於2018-11-22

前言

Data Lake Analytics（後文簡稱 DLA）可以幫助使用者通過標準的SQL語句直接對儲存在OSS、TableStore上的資料進行查詢分析。

對於同一份資料來說，以不同的格式儲存，不僅在儲存空間上有差別，在使用DLA查詢時執行時間也是有差別的。通常來說，同樣大小的資料，以ORC和PARQUET儲存時，效能要優於以普通文字(CSV)的格式儲存。而大部分使用者在OSS上的資料是以CSV的格式儲存的，如果希望得到更好的查詢效率，往往需要藉助第三方工具先對檔案進行格式轉換，再將轉換後的資料檔案匯入OSS，再使用DLA進行查詢，比較麻煩。

本文將介紹如何在DLA中實現不同檔案格式之間的轉換。

轉換方法

簡單來說，就是在DLA中分別依據原始資料檔案的格式和目標資料檔案的格式，建立兩張表；然後通過INSERT INTO  target_table SELECT FROM source_table 語句，將資料以目標表指定的格式寫入OSS中。

詳細示例

下面將以TPC-H中的orders.tbl檔案為例，詳細說明如何將普通文字檔案轉成ORC格式的檔案。

  1. 在DLA中建立表orders_txt，並將LOCATION指向檔案orders.tbl在OSS中的路徑。

    CREATE EXTERNAL TABLE orders_txt (
    O_ORDERKEY INT, 
    O_CUSTKEY INT, 
    O_ORDERSTATUS STRING, 
    O_TOTALPRICE DOUBLE, 
    O_ORDERDATE DATE, 
    O_ORDERPRIORITY STRING, 
    O_CLERK STRING, 
    O_SHIPPRIORITY INT, 
    O_COMMENT STRING
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY `|` 
STORED AS TEXTFILE LOCATION `oss://mybucket/datasets/jinluo/test/convert/orders.tbl`;
  2. 在DLA中建立表orders_orc, 並將LOCATION指向OSS中的目標位置，注意該路徑必須為已經存在的目錄，即以/結尾。
CREATE EXTERNAL TABLE orders_orc (
    O_ORDERKEY INT, 
    O_CUSTKEY INT, 
    O_ORDERSTATUS STRING, 
    O_TOTALPRICE DOUBLE, 
    O_ORDERDATE DATE, 
    O_ORDERPRIORITY STRING, 
    O_CLERK STRING, 
    O_SHIPPRIORITY INT, 
    O_COMMENT STRING
) 
STORED AS ORC LOCATION `oss://mybucket/datasets/jinluo/test/convert/orders_orc/`;
  1. 執行INSERT…SELECT語句，將orders_txt表中滿足要求的資料插入orders_orc中。
INSERT INTO orders_orc SELECT * FROM orders_txt;
  1. INSERT語句執行成功後，在OSS上目標表orders_orc指向的目錄下，會看到生成的資料檔案。
2018-11-22 10:27:15  0.00B Standard oss://mybucket/datasets/jinluo/test/convert/orders_orc/
2018-11-22 10:59:26 1005.62MB Standard oss://mybucket/datasets/jinluo/test/convert/orders_orc/20181122_025537_6_558tf_0eec9b17-dbc3-4ba0-a9df-4024aa6c7d97
2018-11-22 10:59:26 1005.74MB Standard oss://mybucket/datasets/jinluo/test/convert/orders_orc/20181122_025537_6_558tf_74016d12-a570-409d-b444-a216b69a3411

注意事項

  1. 每次執行INSERT語句不會覆蓋表目錄下已有的資料檔案，只會在目錄下新增新檔案；
  2. 由於DLA沒有對使用者BUCKET的刪除許可權，當INSERT語句__執行失敗__時，需要使用者__手動刪除__已經在LOCATION下生成的資料檔案。
  3. 在目標目錄下新生成的檔案個數，與實際執行的叢集環境有關，並不是固定的。
  4. 目前不支援寫入在建立時使用OpenCSVSerde, MultiDelimitSerDe, RegexSerDe和com.esri.hadoop.hive.serde.JsonSerde (Esri ArcGIS地理Json)的目標表。

更多文章

使用Data Lake Analytics從OSS清洗資料到AnalyticDB
基於 DataLakeAnalytics 的資料湖實踐
如何在阿里雲上使用Data Lake Analytics分析Table Store資料
Data Lake Analytics的Geospatial分析函式


相關文章