MaxCompute:SEQUENCEFILE external tables

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 Data types (V1.0) and Data types (V2.0).

Create an external table

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

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 data from OSS.

• For more information about how to optimize query plans, see Query optimization.

Examples

The following examples show how to create a SEQUENCEFILE external table using the built-in open-source data parser, write data to OSS, and then query the data.

1. Prepare data.

   Log on to the OSS console. Upload the test data file 20250611TableSink to the external-table-test/sequence/dt=20250521/ folder in an OSS bucket. For more information, see Upload files.

2. Create a SEQUENCEFILE table.

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

3. Import partition data. If the OSS external table is partitioned, you must perform an additional operation to import 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_sequencefile1_pt ADD PARTITIONS;

4. Read data from the SEQUENCEFILE table.

   SELECT * FROM mc_oss_extable_name_sequencefile1_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   |
   +------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+

5. Write data to the SEQUENCEFILE table.

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

6. View the written data.

   SELECT * FROM mc_oss_extable_name_sequencefile1_pt WHERE dt=20250521 AND recordId='16';

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