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

前言

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分析函數