This topic describes how to create, read from, and write to AVRO-formatted foreign tables in OSS.
Scope
OSS external tables do not support the cluster property.
A single file cannot exceed 2 GB. If a file is too large, split it.
MaxCompute and OSS must be in the same region.
Supported data types
For more information about MaxCompute data types, see Data types (V1.0) and Data types (V2.0).
Data type | Support | 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 a foreign 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 analysis
For more information about the SELECT syntax, see Read data from OSS.
For more information about query plan optimization, see Query optimization.
Examples
The following example shows how to use the built-in open-source data parser to create an AVRO-formatted foreign table, write data to OSS, and query the data.
Prepare the data.
Log on to the OSS console and upload the test RCFILE format data file to the specified directory
oss-mc-test/Demo_rcfile+pt/dt=20250521/in your OSS bucket. For more information, see Upload files to OSS.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 OSS bucket. For more information, see Upload files.Create an AVRO-formatted foreign 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 foreign table is a partitioned table, you must import the partition data. For more information, see Syntax for adding partition data to an OSS foreign table.
-- Import partition data. MSCK REPAIR TABLE mc_oss_extable_name_avro_pt ADD PARTITIONS;Read data from the AVRO foreign 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 foreign 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 | +------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+