MaxCompute:ORC external tables

Schema mismatch behavior

If the ORC file schema and the external table schema do not match, MaxCompute handles the differences as follows:

• Fewer columns in the file than in the table: Missing columns are filled with NULL.

• More columns in the file than in the table: Extra columns are discarded.

• Type mismatch: STRING can read INT data from ORC files, but this is not recommended. INT reading STRING data converts non-numeric values to NULL and accepts numeric values.

Enable predicate pushdown

Predicate pushdown (PPD) improves query performance on ORC external tables by pushing filter conditions down to the data scan layer. PPD requires native mode (odps.ext.oss.orc.native=true).

Add the following statements before your SQL query:

-- Enable the ORC native reader
SET odps.ext.oss.orc.native=true;

-- Enable ORC predicate pushdown
SET odps.storage.orc.use.predicate.pushdown=true;

Prerequisites

Before you begin, make sure that you have:

• A MaxCompute project. See Create a MaxCompute project.

• An OSS bucket and folder in the same region as your MaxCompute project. See Create a bucket and Manage folders.

• OSS access permissions granted via an Alibaba Cloud account, a RAM user, or a RAM role. See STS authorization for OSS.

• The CreateTable permission in your MaxCompute project. See MaxCompute permissions.

Step 1: Prepare the 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.

Step 2: Create the external table

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

Step 3: Add existing partitions

For partitioned external tables, run MSCK REPAIR TABLE to register existing OSS partitions with MaxCompute. For the full syntax, see Adding partitions to an OSS external table.

MSCK REPAIR TABLE orc_data_type_snappy ADD PARTITIONS;

Step 4: Read data

SELECT * FROM orc_data_type_snappy WHERE dt = '20250526' LIMIT 10;

The query returns:

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

Step 5: Write 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');

-- Verify the inserted row
SELECT * FROM orc_data_type_snappy WHERE dt = '20250526' AND recordid = 16;

The query returns:

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