MaxCompute:SEQUENCEFILE external tables

Applicability

• The cluster property is not supported for OSS external tables.

• A single file cannot exceed 3 GB. If a file is larger than 3 GB, you must split it.

• MaxCompute and OSS must be deployed in the same region.

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 optimizing query plans, see Query optimization.

Examples

This example shows how to use the built-in open-source data parser to create a SEQUENCEFILE external table, write data to OSS, and query the data.

1. 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.

2. Upload test data

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

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

4. Import partition data. If the created OSS external table is a partitioned table, you must run an additional command to import 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;

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

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

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