MaxCompute:RCFile external tables

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.

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 and analysis

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

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

Example scenario

This example shows how to create an RCFile external table using the built-in open source data parser, write data to OSS, and query the data.

1. Prerequisites

   • A MaxCompute project is created.

   • An OSS bucket and folder are available. For more information, see Create a bucket and Manage folders.

     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 folders manually.

     MaxCompute is deployed only in specific regions. To avoid potential issues with cross-region data connections, ensure that your OSS bucket is in the same region as your MaxCompute project.

   • Authorization

     • You must have permissions to access OSS. You can use an Alibaba Cloud account, a Resource Access Management (RAM) user, or a RAM role to access OSS external tables. For more information about authorization, see Authorize access in STS mode for OSS.

     • You must have the CreateTable permission in the MaxCompute project. For more information about table permissions, see MaxCompute permissions.

2. Upload test data

   Log on to the OSS console and upload the RCFile data file to the oss-mc-test/Rcfile/dt=20241223/ folder in the OSS bucket. For more information, see Upload files to OSS.

3. Create an RCFile external table

   CREATE EXTERNAL TABLE  mc_oss_rcfile_extable
   (
     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/Rcfile/';

4. Import partition data. If the OSS external table is a partitioned table, you must also 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_extable ADD PARTITIONS;

5. Query the RCFile external table.

   SELECT * FROM mc_oss_rcfile_extable2_pt WHERE dt=20241223;
   
   -- The following result is returned:
   +------------+------------+------------+------------+------------------+-------------------+------------------+------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime       | direction  | dt         | 
   +------------+------------+------------+------------+------------------+-------------------+------------------+------------+------------+
   | 1          | 1          | 7          | 1          | 39.995353        | 116.410101        | 2025/12/23 10:01 | NW         | 20241223   | 
   | 1          | 2          | 15         | 1          | 39.996123        | 116.412345        | 2025/12/24 10:01 | NE         | 20241223   | 
   | 2          | 3          | 22         | 2          | 40.001234        | 116.425678        | 2025/12/25 10:01 | SE         | 20241223   | 
   | 1          | 4          | 71         | 1          | 39.998888        | 106.419999        | 2025/12/26 10:01 | NW         | 20241223   | 
   | 3          | 5          | 50         | 1          | 29.989999        | 116.401111        | 2025/12/27 10:01 | NE         | 20241223   | 
   | 2          | 6          | 28         | 3          | 40.003456        | 110.42891         | 2025/12/28 10:01 | NW         | 20241223   | 
   | 4          | 7          | 20         | 2          | 39.991234        | 116.435555        | 2025/12/29 10:01 | NE         | 20241223   | 
   | 1          | 8          | 13         | 1          | 39.994567        | 116.41789         | 2025/12/30 10:01 | SE         | 20241223   | 
   +------------+------------+------------+------------+------------------+-------------------+------------------+------------+------------+

6. Write data to the RCFile external table.

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

7. Check if the data was written successfully.

   SELECT * FROM mc_oss_rcfile_extable2_pt WHERE dt=20250521 AND recordId='13';
   
   -- The following result is returned:
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime     | direction  | dt         | 
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+
   | 1          | 13         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | SW         | 20251223   | 
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+