Create and Query AVRO External Tables in OSS via STS Auth - MaxCompute

MaxCompute can read from and write to Avro-formatted files stored in Object Storage Service (OSS) using external tables, without moving the data into MaxCompute internal storage.

With Avro external tables, you can:

Query Avro files in OSS directly using SQL
Write data from MaxCompute back to OSS in Avro format
Partition data for efficient query filtering
Use the built-in open-source data parser—no custom serialization code required

Limitations

Constraint	Detail
Cluster property	OSS external tables do not support the cluster property
Single file size	2 GB maximum. Split files larger than 2 GB before creating the external table
Region	MaxCompute and OSS must be in the same region

Supported data types

The following table lists the MaxCompute data types supported for Avro external tables. For the full type reference, see Data types (V1.0) and Data types (V2.0).

Data type	Supported
STRING	Yes
INT	Yes
FLOAT	Yes
DOUBLE	Yes
BOOLEAN	Yes
ARRAY	Yes
MAP	Yes
TINYINT	No
SMALLINT	No
BIGINT	No
BINARY	No
DECIMAL(precision,scale)	No
VARCHAR(n)	No
CHAR(n)	No
DATE	No
DATETIME	No
TIMESTAMP	No
TIMESTAMP_NTZ	No
STRUCT	No
JSON	No

Create an Avro external table

Syntax

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>';

Parameters

For parameter descriptions, see Basic syntax parameters.

Write data

For the write syntax, see Write data to OSS.

Query and analysis

For the SELECT syntax, see Read data from OSS.
For query plan optimization, see Query optimization.

Example: create, write, and query an Avro external table

This example walks through creating a partitioned Avro external table, importing partition metadata, reading data, and writing a new row.

Prerequisites

Before you begin, ensure that you have:

A MaxCompute project
An OSS bucket in the same region as your MaxCompute project. See Create a bucket and Manage folders
OSS access permissions granted via an Alibaba Cloud account, a Resource Access Management (RAM) user, or a RAM role. See Authorize access in STS mode for OSS
The CreateTable permission in the MaxCompute project. See MaxCompute permissions

MaxCompute supports automatic folder creation in OSS. If an SQL statement involves external tables and user-defined functions (UDFs), a single statement can read from and write to the tables while applying the UDFs.

Step 1: Upload test data

Log on to the OSS console and upload the test data file 20250612TableSink to the external-table-test/avro/dt=20250521/ folder in your bucket. For upload instructions, see Upload files.

Step 2: Create an Avro external table

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/';

Step 3: Import partition metadata

For partitioned external tables, run MSCK REPAIR TABLE to register existing OSS partitions in MaxCompute:

MSCK REPAIR TABLE mc_oss_extable_name_avro_pt ADD PARTITIONS;

Step 4: Read data from the Avro external table

SELECT * FROM mc_oss_extable_name_avro_pt WHERE dt=20250521;

The following result is returned:

+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+
| 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   |
+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+

Step 5: Write data to the Avro external table

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');

Step 6: Verify the written data

SELECT * FROM mc_oss_extable_name_avro_pt WHERE dt=20250521;

The following result is returned, with the newly inserted row (recordId 16) at the top:

+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+
| 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   |
+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+