创建读取及写入OSS AVRO外部表-云原生大数据计算服务 MaxCompute-阿里云

本文介绍AVRO格式的OSS外部表的创建、读取及写入方法。

适用范围

OSS外部表不支持cluster属性。
单个文件大小不能超过2GB，如果文件过大，建议拆分。
MaxCompute需要与OSS部署在同一地域。

支持数据类型

数据类型	是否支持	数据类型	是否支持
TINYINT		STRING
SMALLINT		DATE
INT		DATETIME
BIGINT		TIMESTAMP
BINARY		TIMESTAMP_NTZ
FLOAT		BOOLEAN
DOUBLE		ARRAY
DECIMAL(precision,scale)		MAP
VARCHAR(n)		STRUCT
CHAR(n)		JSON

创建外部表

语法结构

CREATE EXTERNAL TABLE <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
STORED AS avro
LOCATION '<oss_location>';

参数说明

详情请参见基础语法参数说明。

写入数据

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

查询分析

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

使用示例

通过内置开源数据解析器创建AVRO格式外部表，将数据写入OSS并查询。

前置准备
- 已创建MaxCompute项目。
- 已准备好OSS存储空间（Bucket）、OSS目录。具体操作请参见创建存储空间、管理目录。
  MaxCompute已支持在OSS侧自动创建目录，对于携带外部表及UDF的SQL语句，可以通过一条SQL语句执行读写外部表及UDF的操作。原手动创建目录方式仍然支持。
  由于MaxCompute只在部分地域部署，跨地域的数据连通性可能存在问题，因此建议Bucket与MaxCompute项目所在地域保持一致。
- 授权
  - 已具备访问OSS的权限。阿里云账号（主账号）、RAM用户或RAMRole身份可以访问OSS外部表，授权信息请参见OSS的STS模式授权。
  - 已具备在MaxCompute项目中创建表（CreateTable）的权限。表操作的权限信息请参见MaxCompute权限。
上传测试数据
登录OSS控制台，上传测试数据20250612TableSink至OSSBucket指定目录external-table-test/avro/dt=20250521/。具体操作请参见OSS文件上传。

创建AVRO格式外部表。

CREATE EXTERNAL TABLE mc_oss_extable_name_avro_pt 
(
  vehicleId STRING,
  recordId STRING,
  patientId STRING,
  calls STRING,
  locationLatitute STRING,
  locationLongtitue STRING,
  recordTime STRING,
  direction STRING
) 
PARTITIONED BY (dt STRING) 
STORED AS avro 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/avro/';

引入分区数据。当创建的OSS外部表为分区表时，需要额外执行引入分区数据的操作，详情请参见补全OSS外部表分区数据语法。
```
-- 引入分区数据。
MSCK REPAIR TABLE mc_oss_extable_name_avro_pt ADD PARTITIONS;
```

读取AVRO外部表。

SELECT * FROM mc_oss_extable_name_avro_pt WHERE dt=20250521;

返回结果如下：

+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+
| vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime | direction  | dt         |
+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+
| 1          | 12         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | SW         | 20250521   |
| 1          | 1          | 51         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | S          | 20250521   |
| 1          | 2          | 13         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:01 | NE         | 20250521   |
| 1          | 3          | 48         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:02 | NE         | 20250521   |
| 1          | 4          | 30         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:03 | W          | 20250521   |
| 1          | 5          | 47         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:04 | S          | 20250521   |
| 1          | 6          | 9          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:05 | S          | 20250521   |
| 1          | 7          | 53         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:06 | N          | 20250521   |
| 1          | 8          | 63         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:07 | SW         | 20250521   |
| 1          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:08 | NE         | 20250521   |
| 1          | 10         | 31         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:09 | N          | 20250521   |
+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+

写入AVRO外部表。

INSERT INTO mc_oss_extable_name_avro_pt PARTITION (dt='20250521') VALUES ('1','16','76','1','46.81006','-92.08174','9/14/2014 0:10','SW');

查询写入数据。

SELECT * FROM mc_oss_extable_name_avro_pt WHERE dt=20250521;

返回结果如下：

+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+
| vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime | direction  | dt         |
+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+
| 1          | 16         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | SW         | 20250521   |
| 1          | 12         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | SW         | 20250521   |
| 1          | 1          | 51         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | S          | 20250521   |
| 1          | 2          | 13         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:01 | NE         | 20250521   |
| 1          | 3          | 48         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:02 | NE         | 20250521   |
| 1          | 4          | 30         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:03 | W          | 20250521   |
| 1          | 5          | 47         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:04 | S          | 20250521   |
| 1          | 6          | 9          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:05 | S          | 20250521   |
| 1          | 7          | 53         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:06 | N          | 20250521   |
| 1          | 8          | 63         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:07 | SW         | 20250521   |
| 1          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:08 | NE         | 20250521   |
| 1          | 10         | 31         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:09 | N          | 20250521   |
+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+