ORC外部表-云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

本文介绍ORC格式的OSS外部表的创建、读取及写入方法。

适用范围

OSS外部表不支持cluster属性。
单个文件大小不能超过2GB，如果文件过大，建议拆分。
MaxCompute需要与OSS部署在同一地域。

支持数据类型

JNI模式（读表时不使用Native ORC Reader）：set odps.ext.oss.orc.native=false;，支持读写。
Native模式（读表时使用Native ORC Reader）：set odps.ext.oss.orc.native=true;，仅支持读。

模式	Java模式（读写）	Native模式（只读）
TINYINT
SMALLINT
INT
BIGINT
BINARY
FLOAT
DOUBLE
DECIMAL(precision,scale)
VARCHAR(n)
CHAR(n)
STRING
DATE
DATETIME
TIMESTAMP
TIMESTAMP_NTZ
BOOLEAN
ARRAY
MAP
STRUCT
JSON

支持压缩格式

当读写压缩属性的OSS文件时，需要在建表语句中添加with serdeproperties属性配置，详情请参见with serdeproperties属性参数。
支持读写的数据文件格式：以SNAPPY、ZLIB方式压缩的ORC。

创建外部表

语法结构

当ORC文件中的Schema与外表Schema不一致时：

列数不一致：如果ORC文件中的列数小于外表DDL的列数，则读取ORC数据时，系统会将缺少的列值补充为NULL。反之（大于时），会丢弃超出的列数据。
列类型不一致：MaxCompute支持使用STRING类型接收ORC文件中的INT类型数据（不推荐），使用INT类型接收STRING类型数据时，会将字符串转换为NULL，数字正常接收。

精简语法结构

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

公共参数

公共参数说明请参见基础语法参数说明。

独有参数

with serdeproperties属性参数

property_name

使用场景

说明

property_value

默认值

mcfed.orc.schema.resolution

当同一张OSS外部表中数据的Schema不一样时，请添加该属性。

用于设置ORC文件解析方式，name表示根据列名解析。

name

默认按列号解析。

等价于： 'mcfed.orc.schema.resolution'='position'。

tblproperties属性参数

property_name	使用场景	说明	property_value	默认值
mcfed.orc.compress	当需要将ORC数据以压缩方式写入OSS时，请添加该属性。	ORC压缩属性。指定ORC数据的压缩方式。	SNAPPY ZLIB	无
io.compression.codecs	当OSS数据文件为Raw-Snappy格式时，请添加该属性。	配置该参数值为True时，MaxCompute才可以正常读取压缩数据，否则MaxCompute无法成功读取数据。	com.aliyun.odps.io.compress.SnappyRawCodec	无
odps.external.data.output.prefix （兼容odps.external.data.prefix）	当需要添加输出文件的自定义前缀名时，请添加该属性。	仅包含数字，字母，下划线(a-z, A-Z, 0-9, _)。长度在1-10之间。	符合条件的字符组合，例如'mc_'。	无
odps.external.data.enable.extension	当需要显示输出文件的扩展名时，请添加该属性。	True表示显示输出文件的扩展名，反之不显示扩展名。	True False	False
odps.external.data.output.suffix	当需要添加输出文件的自定义后缀名时，请添加该属性。	仅包含数字，字母，下划线(a-z, A-Z, 0-9, _)。	符合条件的字符组合，例如'_hangzhou'。	无
odps.external.data.output.explicit.extension	当需要添加输出文件的自定义扩展名时，请添加该属性。	仅包含数字，字母，下划线(a-z, A-Z, 0-9, _) 长度在1-10之间。优先级高于参数`odps.external.data.enable.extension`。	符合条件的字符组合，例如"jsonl"。	无
mcfed.orc.batch.size	控制每次处理的记录数量，影响内存使用和处理效率。	ORC的调优属性。定义ORC默认批次大小，以行为单位。	非负整数	1000

写入数据

MaxCompute写入语法详情，请参见写入语法说明。

查询分析

SELECT语法详情，请参见查询语法说明。
优化查询计划详情，请参见查询优化。
ORC外部表支持通过开启PPD（即Predicate Push Down）实现查询优化，需要在SQL前加如下参数：
PPD参数需在Native模式下使用，即Native开关需为true。
```
-- 开启orc native reader。
SET odps.ext.oss.orc.native-true;

-- 开启orc ppd。
SET odps.storage.orc.use.predicate.pushdown=true; 
```

场景示例

创建以SNAPPY压缩的ORC格式外表，并进行数据读取和写入操作。

前置准备
1. 已创建MaxCompute项目。
2. 已准备好OSS存储空间（Bucket）、OSS目录。具体操作请参见创建存储空间、管理目录。
  由于MaxCompute只在部分地域部署，跨地域的数据连通性可能存在问题，因此建议Bucket与MaxCompute项目所在地域保持一致。
3. 授权
  1. 具备访问OSS的权限。阿里云账号（主账号）、RAM用户或RAMRole身份可以访问OSS外部表，授权信息请参见OSS的STS模式授权。
  2. 已具备在MaxCompute项目中创建表（CreateTable）的权限。表操作的权限信息请参见MaxCompute权限。
准备SNAPPY格式数据文件。
在示例数据的oss-mc-testBucket中创建orc_snappy/dt=20250526目录层级，并将snappy文件存放在分区目录dt=20250526下。

创建SNAPPY压缩格式的ORC外表。

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

引入分区数据。当创建的OSS外部表为分区表时，需要额外执行引入分区数据的操作，详情请参见补全OSS外部表分区数据语法。
```
-- 引入分区数据
MSCK REPAIR TABLE orc_data_type_snappy ADD PARTITIONS;
```

读取ORC外表数据。

SELECT * FROM orc_data_type_snappy WHERE dt=20250526 LIMIT 10;

返回结果如下：

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

写入数据至ORC外表，并查询。

INSERT INTO orc_data_type_snappy PARTITION (dt ='20250526') 
  VALUES (1,16,76,1,46.81006,-92.08174,'9/14/2014 0:10','SW');

-- 查询新写入的数据
SELECT * FROM orc_data_type_snappy WHERE dt = '20250526' AND recordid=16;

返回结果如下：

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