MaxCompute:ORC Foreign Tables

Applicability

• OSS external tables do not support the cluster property.

• The size of a single file cannot exceed 2 GB. You must split files that are larger than 2 GB.

• MaxCompute and OSS must be in the same region.

• Supported data types.

• Supported compression formats.

• Schema Evolution support.

Create Foreign Tables

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 orc
LOCATION '<oss_location>';

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
WITH serdeproperties(
    'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
)
STORED AS orc 
LOCATION '<oss_location>' 
tblproperties (
    '<xxx>'='<yyy>'
);

Write data

For more information about the MaxCompute write syntax, see Write syntax.

Query analysis

• For more information about the SELECT syntax, see Query syntax.

• For more information about query plan optimization, see Query optimization.

• You can optimize queries on ORC foreign tables by enabling Predicate Pushdown (PPD). To do this, add the following parameters before your SQL statement:

  The PPD parameters must be used in Native mode. This means the Native switch must be set to true.

  -- Enable the ORC native reader.
  SET odps.ext.oss.orc.native-true;
  
  -- Enable ORC PPD.
  SET odps.storage.orc.use.predicate.pushdown=true; 

Scenario example

This example shows how to create a foreign ORC table that uses SNAPPY compression, and then read data from and write data to the table.

1. Prerequisites

   1. You have created a MaxCompute project.

   2. You have prepared an OSS bucket and OSS directory. For more information, see Create a bucket and Manage folders.

      Because MaxCompute is available only in specific regions, cross-region connectivity issues may occur. We recommend using an OSS bucket in the same region as your MaxCompute project.

   3. Authorization

      1. You have permissions to access OSS. An Alibaba Cloud account (primary account), Resource Access Management (RAM) user, or RAM role can access OSS external tables. For authorization details, see STS-mode authorization for OSS.

      2. You have CreateTable permissions in the MaxCompute project. For details about table operation permissions, see MaxCompute permissions.

2. Prepare a SNAPPY-formatted data file.

   Using the provided sample data, create the folder path orc_snappy/dt=20250526 in the oss-mc-test bucket. Upload the SNAPPY file to the dt=20250526 partition folder.

3. Create a foreign ORC table with SNAPPY compression.

   CREATE EXTERNAL TABLE orc_data_type_snappy
   (
       vehicleId INT,
       recordId INT,
       patientId INT,
       calls INT,
       locationLatitute DOUBLE,
       locationLongitude DOUBLE,
       recordTime STRING,
       direction STRING
   )
   PARTITIONED BY (dt STRING )
   ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
   WITH serdeproperties (
    'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS ORC  
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/orc_snappy/'
   tblproperties (
       'mcfed.orc.compress'='SNAPPY');

4. If your OSS foreign table is partitioned, run the following command to add existing partitions. For more information, see Syntax for adding partitions to an OSS external table.

   -- Add partitions to the external table
   MSCK REPAIR TABLE orc_data_type_snappy ADD PARTITIONS;

5. Read data from the ORC foreign table.

   SELECT * FROM orc_data_type_snappy WHERE dt=20250526 LIMIT 10;

   The query returns the following result:

   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongitude | recordtime     | direction  | dt         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+
   | 1          | 12         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | SW         | 20250526   |
   | 1          | 1          | 51         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | S          | 20250526   |
   | 1          | 2          | 13         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:01 | NE         | 20250526   |
   | 1          | 3          | 48         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:02 | NE         | 20250526   |
   | 1          | 4          | 30         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:03 | W          | 20250526   |
   | 1          | 5          | 47         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:04 | S          | 20250526   |
   | 1          | 6          | 9          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:05 | S          | 20250526   |
   | 1          | 7          | 53         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:06 | N          | 20250526   |
   | 1          | 8          | 63         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:07 | SW         | 20250526   |
   | 1          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:08 | NE         | 20250526   |
   | 1          | 10         | 31         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:09 | N          | 20250526   |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+

6. Write data to the ORC foreign table and query the data.

   INSERT INTO orc_data_type_snappy PARTITION (dt ='20250526') 
     VALUES (1,16,76,1,46.81006,-92.08174,'9/14/2014 0:10','SW');
   
   -- Query the newly inserted data
   SELECT * FROM orc_data_type_snappy WHERE dt = '20250526' AND recordid=16;

   The query returns the following result:

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

Supported data types

For more information about MaxCompute data types, see Data type version 1.0 and Data type version 2.0.

• JNI mode (set odps.ext.oss.orc.native=false;): This mode does not use the native ORC reader when reading tables. It supports both read and write operations.

• Native mode (set odps.ext.oss.orc.native=true;): This mode uses the native ORC reader when reading tables. It supports only read operations.

Supported Compression Formats

• To read or write compressed OSS files, you must add the with serdeproperties configuration to the table creation statement. For more information, see with serdeproperties property parameters.

• MaxCompute supports reading and writing ORC files that are compressed with SNAPPY or ZLIB.

Support Schema Evolution

ORC foreign tables support two methods for mapping the table schema to the columns in the data file: position-based mapping and name-based mapping.

• Position-based mapping: To use this method, set the 'mcfed.orc.schema.resolution'='position' parameter or omit it to use the default setting. The system maps columns based on their position. Therefore, the column order in the table must exactly match the field order in the file.

• Name-based mapping: To use this method, set the 'mcfed.orc.schema.resolution'='name' parameter when you create the foreign table. The system then maps columns by name instead of by position.

In the following table, Data compatibility issues describes whether a foreign table can correctly read data after a Schema Evolution operation. This includes reading new data that conforms to the modified schema and historical data that uses the old schema.