ORC external tables

syntax

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.

Use either the simplified syntax or the full syntax to create an ORC external table.

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

Common parameters

For more information about common parameters, see Basic syntax parameters.

Format-specific parameters

Prerequisites

1. You have created a MaxCompute project.

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

   Ensure your bucket is in the same region as your MaxCompute project.

3. Grant permissions.

   1. You have permission to access OSS. You can access an OSS external table by using an Alibaba Cloud account, a RAM user, or a RAM role. For more information about how to grant permissions, see STS-mode authorization for OSS.

   2. You have the CreateTable permission in the MaxCompute project. For more information about table-related permissions, 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   |
+------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+