MaxCompute:SEQUENCEFILE external tables

Syntax

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

Parameters

For a full parameter reference, see Basic syntax parameters.

Prerequisites

Before you begin, make sure you have:

• A MaxCompute project. See Create a project.

• An OSS bucket and folder in the same region as your MaxCompute project. See Create a bucket and Manage folders.

• OSS access permissions through 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 your MaxCompute project. See MaxCompute permissions.

MaxCompute supports automatic folder creation in OSS. If an SQL statement involves external tables and user-defined functions (UDFs), you can use a single statement to read from and write to the tables and use the UDFs. You can also create the folder manually in the OSS console before running the statements below.

Step 1: Upload test data

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

Step 2: Create the external table

Create a partitioned SequenceFile external table that points to the OSS path where the test data is stored.

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

Step 3: Import partition data

Because the table is partitioned, run MSCK REPAIR TABLE to detect and register the partitions that already exist in OSS.

MSCK REPAIR TABLE mc_oss_extable_name_sequencefile1_pt ADD PARTITIONS;

Step 4: Query the data

SELECT * FROM mc_oss_extable_name_sequencefile1_pt WHERE dt=20250521;

The query returns 11 rows from the test dataset:

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

Insert a new row into the partition.

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

Step 6: Verify the write

Query the row you just inserted to confirm it was written correctly.

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

Expected output:

+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+
| vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime | direction  | dt         |
+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+
| 1          | 16         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | SW         | 20250521   |
+------------+------------+------------+------------+------------------+-------------------+------------+------------+------------+