This topic describes how to create, read from, and write to AVRO external tables in Object Storage Service (OSS).
Prerequisites
The Alibaba Cloud account or RAM user is granted permissions to access OSS. Alibaba Cloud accounts, RAM users, and RAM roles can access OSS external tables. For more information about authorization, see STS authorization.
A MaxCompute project is created.
MaxCompute is deployed only in specific regions. To prevent a cross-region data connectivity issue, we recommend that you use a bucket in the same region as your MaxCompute project.
The Alibaba Cloud account or RAM user is granted the CreateTable permission on your project. For more information about table operation permissions, see MaxCompute permissions.
(Optional) An OSS bucket, OSS directories, and OSS data files are prepared. For more information, see Create a bucket, Manage directories, and Simple upload.
MaxCompute can automatically create an OSS directory in OSS. For SQL statements that include external tables and UDFs, you can execute operations to read and write external tables and UDFs with a single SQL statement. You can also manually create an OSS directory.
Limits
OSS external tables do not support the clustering.
A single file cannot exceed 3 GB. If one does, we recommend that you split it into multiple files.
Supported data types
For more information about MaxCompute data types, see MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition.
indicates supported and 
indicates not supported.
Data type | Supported | Data type | Supported |
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 an 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 more information, see Basic syntax parameters.
Write data
For more information about the syntax for writing data from MaxCompute to OSS, see Write data to OSS.
Query and analyze data
For more information about the SELECT syntax, see Read OSS data.
For more information about how to optimize query plans, see Query optimization.
Examples
This example shows how to create an AVRO external table using the built-in open-source data parser, write data to OSS, and then query the data.
Prepare the data.
Log on to the OSS console and upload the test data 20250612TableSink to the
external-table-test/avro/dt=20250521/directory in the OSS bucket. For more information, see Simple upload.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/';Import partition data. If the OSS external table is partitioned, you must perform an additional step to add the partition data. For more information, see Syntax for adding partition data to an OSS external table.
-- Import partition data. MSCK REPAIR TABLE mc_oss_extable_name_avro_pt ADD PARTITIONS;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 | +------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+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');Query the written data.
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 | 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 | +------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+