前言

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