ORC外部表格 - 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   |
+------------+------------+------------+------------+------------------+-------------------+----------------+------------+------------+