Data Lake Analytics + OSS数据文件格式处理大全

0. 前言

Data Lake Analytics是Serverless化的云上交互式查询分析服务。用户可以使用标准的SQL语句，对存储在OSS、TableStore上的数据无需移动，直接进行查询分析。

目前该产品已经正式登陆阿里云，欢迎大家申请试用，体验更便捷的数据分析服务。
请参考https://help.aliyun.com/document_detail/70386.html 进行产品开通服务申请。

在上一篇教程中，我们介绍了如何分析CSV格式的TPC-H数据集。除了纯文本文件(例如，CSV，TSV等)，用户存储在OSS上的其他格式的数据文件，也可以使用Data Lake Analytics进行查询分析，包括ORC, PARQUET, JSON, RCFILE, AVRO甚至ESRI规范的地理JSON数据，还可以用正则表达式匹配的文件等。

本文详细介绍如何根据存储在OSS上的文件格式使用Data Lake Analytics （下文简称 DLA）进行分析。DLA内置了各种处理文件数据的SerDe（Serialize/Deserilize的简称,目的是用于序列化和反序列化）实现，用户无需自己编写程序，基本上能选用DLA中的一款或多款SerDe来匹配您OSS上的数据文件格式。如果还不能满足您特殊文件格式的处理需求，请联系我们，尽快为您实现。

1. 存储格式与SerDe

用户可以依据存储在OSS上的数据文件进行建表，通过STORED AS 指定数据文件的格式。
例如，

CREATE EXTERNAL TABLE nation (
    N_NATIONKEY INT, 
    N_NAME STRING, 
    N_REGIONKEY INT, 
    N_COMMENT STRING
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE 
LOCATION 'oss://test-bucket-julian-1/tpch_100m/nation';

建表成功后可以使用SHOW CREATE TABLE语句查看原始建表语句。

mysql> show create table nation;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Result                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE EXTERNAL TABLE `nation`(
  `n_nationkey` int,
  `n_name` string,
  `n_regionkey` int,
  `n_comment` string)
ROW FORMAT DELIMITED
    FIELDS TERMINATED BY '|'
STORED AS `TEXTFILE`
LOCATION
  'oss://test-bucket-julian-1/tpch_100m/nation'|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (1.81 sec)

下表中列出了目前DLA已经支持的文件格式，当针对下列格式的文件建表时，可以直接使用STORED AS，DLA会选择合适的SERDE/INPUTFORMAT/OUTPUTFORMAT。

 

存储格式	描述
STORED AS TEXTFILE	数据文件的存储格式为纯文本文件。默认的文件类型。 文件中的每一行对应表中的一条记录。
STORED AS ORC	数据文件的存储格式为ORC。
STORED AS PARQUET	数据文件的存储格式为PARQUET。
STORED AS RCFILE	数据文件的存储格式为RCFILE。
STORED AS AVRO	数据文件的存储格式为AVRO。
STORED AS JSON	数据文件的存储格式为JSON (Esri ArcGIS的地理JSON数据文件除外)。

 

在指定了STORED AS 的同时，还可以根据具体文件的特点，指定SerDe (用于解析数据文件并映射到DLA表)，特殊的列分隔符等。
后面的部分会做进一步的讲解。

2. 示例

2.1 CSV文件

CSV文件，本质上还是纯文本文件，可以使用STORED AS TEXTFILE。
列与列之间以逗号分隔，可以通过ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 表示。

普通CSV文件

例如，数据文件oss://bucket-for-testing/oss/text/cities/city.csv的内容为

Beijing,China,010
ShangHai,China,021
Tianjin,China,022

建表语句可以为

CREATE EXTERNAL TABLE city (
    city STRING, 
    country STRING, 
    code INT
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE 
LOCATION 'oss://bucket-for-testing/oss/text/cities';

使用OpenCSVSerde__处理引号__引用的字段

OpenCSVSerde在使用时需要注意以下几点：

1. 用户可以为行的字段指定字段分隔符、字段内容引用符号和转义字符，例如：WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\" )；
2. 不支持字段内嵌入的行分割符；
3. 所有字段定义STRING类型；
4. 其他数据类型的处理，可以在SQL中使用函数进行转换。
   例如，

CREATE EXTERNAL TABLE test_csv_opencsvserde (
  id STRING,
  name STRING,
  location STRING,
  create_date STRI