MaxCompute:RCFILE 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 MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition.

Create an external table

CREATE EXTERNAL TABLE [IF NOT EXISTS] mc_oss_rcfile_extable
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
STORED AS rcfile
LOCATION 'oss_location';

Write data

For more information about the syntax for writing data from MaxCompute to OSS, see Write data to OSS.

Query analysis

• For more information about the SELECT syntax, see Read OSS data.

• For more information about optimizing query plans, see Query optimization.

Examples

This example shows how to create an external table in RCFILE format using the built-in open source data resolver, write data to the table in OSS, and then query the data.

1. Prepare the data.

   Log on to the OSS console and upload the test RCFILE data file to the oss-mc-test/Demo_rcfile+pt/dt=20250521/ directory in your OSS bucket. For more information, see Upload files to OSS.

2. Create an RCFILE external table.

   CREATE EXTERNAL TABLE  mc_oss_rcfile_extable2_pt
   (
     vehicleId STRING ,
     recordId STRING,
     patientId STRING,
     calls STRING,
     locationLatitute STRING,
     locationLongtitue STRING,
     recordTime STRING,
     direction STRING
   )
   PARTITIONED BY (dt STRING)
   ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
   STORED AS rcfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo_rcfile+pt/';

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

4. Query the RCFILE external table.

   SELECT * FROM mc_oss_rcfile_extable2_pt WHERE dt=20250521;

5. Write data to the RCFILE external table.

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

6. Verify that the data is written.

   SELECT * FROM mc_oss_rcfile_extable2_pt WHERE dt=20250521 AND recordId='13';