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在使用时需要注意以下几点:
- 用户可以为行的字段指定字段分隔符、字段内容引用符号和转义字符,例如:WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\" );
- 不支持字段内嵌入的行分割符;
- 所有字段定义STRING类型;
- 其他数据类型的处理,可以在SQL中使用函数进行转换。
例如,
CREATE EXTERNAL TABLE test_csv_opencsvserde (
id STRING,
name STRING,
location STRING,
create_date STRI