通过自定义解析器创建OSS外部表及读写数据-云原生大数据计算服务 MaxCompute-阿里云

本文介绍通过自定义解析器创建OSS外部表以及读取和写入数据的方法。

适用范围

OSS外部表不支持cluster属性。
单个文件大小不能超过2GB，如果文件过大，建议拆分。

创建外部表

语法说明

CREATE EXTERNAL TABLE [IF NOT EXISTS] mc_oss_extable_name
(
  col_name date_type,
  ...
)
[comment table_comment]
[partitioned BY (col_name data_type, ...)] 
stored BY '<YOUR_DEFINED_STORAGEHANDLER>' 
WITH serdeproperties (
  ['property_name'='property_value',...]
) 
location 'oss_location' 
USING 'jar_name';

自定义解析器默认不会对数据分片，以避免解析器出现正确性问题，如果您确认能够处理分片，可通过如下命令启用数据分片功能，即启动多个Mapper。

SET odps.sql.unstructured.data.single.file.split.enabled=true;

公共参数

公共参数说明请参见基础语法参数说明。

独有参数

参数名称	是否必填	说明
your_defined_storagehandler	是	通过编写MaxCompute UDF自定义的解析器，更多编写MaxCompute UDF信息，请参见开发UDF。
jar_name	是	指定自定义解析器代码对应的JAR包，该JAR包需要添加为MaxCompute项目资源。更多添加资源信息，请参见资源操作。
resource_name	否	当您使用自定义的serde class时，需要指定依赖的资源。资源中包含了自定义的serde class。 serde class相关的JAR包需要添加为MaxCompute项目资源。更多添加资源信息，请参见资源操作。

数据写入

MaxCompute写入语法详情，请参见将数据写入OSS。

查询分析

SELECT语法详情，请参见读取OSS数据。
优化查询计划详情，请参见查询优化。

示例：通过自定义解析器创建OSS外部表

与附录：准备示例数据中的SampleData/（自定义解析器）目录建立映射关系。操作流程如下：

前置准备
- 已创建MaxCompute项目。
- 已准备好OSS存储空间（Bucket）、OSS目录。具体操作请参见创建存储空间、管理目录。
  MaxCompute已支持在OSS侧自动创建目录，对于携带外部表及UDF的SQL语句，可以通过一条SQL语句执行读写外部表及UDF的操作。原手动创建目录方式仍然支持。
  由于MaxCompute只在部分地域部署，跨地域的数据连通性可能存在问题，因此建议Bucket与MaxCompute项目所在地域保持一致。
- 授权
  - 已具备访问OSS的权限。阿里云账号（主账号）、RAM用户或RAMRole身份可以访问OSS外部表，授权信息请参见OSS的STS模式授权。
  - 已具备在MaxCompute项目中创建表（CreateTable）的权限。表操作的权限信息请参见MaxCompute权限。
使用MaxCompute Studio创建TextExtractor.java、TextExtractor.java、SplitReader.java和TextStorageHandler.java四个Java类。更多开发Java程序详情，请参见开发UDF。
通过MaxCompute Studio的一键式打包功能，将其中的TextStorageHandler.java打包并上传为MaxCompute资源。
假设资源名称为javatest-1.0-SNAPSHOT.jar。更多打包上传信息，请参见打包、上传及注册。
说明
如果有多个依赖请分别打包后上传为MaxCompute资源。

执行如下命令创建OSS外部表。

CREATE EXTERNAL TABLE ambulance_data_txt_external
(
  vehicleId INT,
  recordId INT,
  patientId INT,
  calls INT,
  locationLatitute DOUBLE,
  locationLongtitue DOUBLE,
  recordTime STRING,
  direction STRING
)
stored BY 'com.aliyun.odps.udf.example.text.TextStorageHandler' 
  WITH serdeproperties (
    'delimiter'='|',  
    'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
  )
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/SampleData/'
USING 'javatest-1.0-SNAPSHOT.jar'; 

-- 您可以执行desc extended ambulance_data_txt_external;命令查看创建好的外部表结构信息。

说明

其中delimiter是用户自定义的、用来决定OSS文件每行数据不同列值之间的分隔符，用户可以选择任意一个合法的字符串。

从OSS读取数据，命令示例如下。

SELECT recordId, patientId, direction FROM ambulance_data_txt_external WHERE patientId > 25;

返回结果如下。

+----------+-----------+-----------+
| recordid | patientid | direction |
+----------+-----------+-----------+
| 1        | 51        | S         |
| 3        | 48        | NE        |
| 4        | 30        | W         |
| 5        | 47        | S         |
| 7        | 53        | N         |
| 8        | 63        | SW        |
| 10       | 31        | N         |
+----------+-----------+-----------+

向OSS外部表写入数据。

INSERT INTO ambulance_data_txt_external VALUES (1,16,76,1,'46.81006','-92.08174','9/14/2014 0:10','SW');

-- 再次查询看是否写入成功；也可看oss目录下是否生成了新文件。
SELECT * FROM ambulance_data_txt_external WHERE recordId='16';

常见问题

自定义Extractor在读取非结构化数据时，如果数据字段存在DATETIME类型，报错ODPS-0123131，如何解决？

问题现象

自定义Extractor在读取非结构化数据时，如果数据字段存在DATETIME类型（例如2019-11-11 06:43:36），会返回如下报错。

FAILED: ODPS-0123131:User defined function exception - Traceback:
java.lang.IllegalArgumentException
    at java.sql.Date.valueOf(Date.java:143)
    at com.aliyun.odps.udf.example.text.TextExtractor.textLineToRecord(TextExtractor.java:194)
    at com.aliyun.odps.udf.example.text.TextExtractor.extract(TextExtractor.java:153)
    at com.aliyun.odps.udf.ExtractorHandler.extract(ExtractorHandler.java:120)

产生原因
查看指定位置的代码Date.valueOf(parts[i])，其中java.sql.Date.valueOf()函数只支持形如"yyyy-[m]m-[d]d"的STRING类型参数，不支持DATETIME时间类型参数。

解决措施

引入Joda-Time依赖并在代码中增加导入信息。

-- 依赖。
<dependency>
  <groupId>joda-time</groupId>
  <artifactId>joda-time</artifactId>
  <version>2.10</version>
</dependency> 
-- 导入信息。
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

引入DateTimeFormat.forPattern()函数，将DATETIME类型的日期格式转化为STRING类型进行读取。

record.setDate(index, new Date(DateTime.parse(parts[i], DateTimeFormat.forPattern("yyyy-MM-dd HH:mi:ss")).getMillis()));

使用示例如下。

通过MaxCompute客户端上传Extractor项目打包生成的JAR包。
```
add jar /Users/gary/big_data/odps/text_extractor/target/text_extractor-1.0-SNAPSHOT.jar      
```
/Users/gary/big_data/odps/text_extractor/target/text_extractor-1.0-SNAPSHOT.jar为生成JAR包的本地保存路径。
通过MaxCompute客户端上传Joda-Time第三方JAR包。
```
add jar /Users/gary/.m2/repository/joda-time/joda-time/2.10/joda-time-2.10.jar                         
```
/Users/gary/.m2/repository/joda-time/joda-time/2.10/joda-time-2.10.jar为Joda-Time第三方JAR包本地存放路径。

上传测试数据至OSS指定的目录下。假设文件名为video_play_log.txt，示例数据如下。

5c661071dba64d5080c91da085ff1073^音乐-点击-快进^26.12.XX.XX^2019-11-11 06:43:36

通过外部表读取数据。

select * from <project_name>.video_play_log;

读取结果如下。

+------+-------+---+----------------+
| uuid  | action  | ip  | time      |
+------+-------+---+----------------+
| 5c661071dba64d5080c91da085ff1073 | 音乐-点击-快进 | 26.12.XX.XX | 2019-11-11 06:43:36 |
+------+-------+---+----------------+