MaxCompute:ORC external tables

Usage notes

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

• Supported data types.

• Supported compression formats.

• Schema Evolution support.

Create external 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 external 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; 

Example

This example shows how to create an external 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. Prepare an OSS bucket and folder. For more information, see Create a bucket and Manage folders.

      MaxCompute is deployed only in some regions. To prevent data connectivity issues, use an OSS bucket that is in the same region as your MaxCompute project.

   3. Authorization

      1. You must have permissions to access OSS. You can use an Alibaba Cloud account, a RAM user, or a RAM role to access OSS external tables. For more information about authorization, see STS authorization for OSS.

      2. You must have the `CreateTable` permission in the MaxCompute project. For more information 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 an external 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 external 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 external 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 external 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 external 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 external 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.