特色功能：Schemaless Query - MaxCompute

MaxCompute支援免定義查詢（Schemaless Query）OSS中Parquet外部表格資料，解析後的資料集結果可以匯出至OSS或寫入內部表，同時也可以作為子查詢嵌入SQL運算，從而便捷地操作湖上資料。

背景資訊

使用Spark處理結構化資料的使用者在進行臨時資料探查時，並不需要依賴固定的數倉模型。如果輸入和輸出都需要定義表結構，並將OSS上的檔案欄位對應到外表欄位，才能進行讀寫操作，那麼後續維護分區的過程將顯得相對繁瑣。

若可以在使用Load指令讀取Parquet外部表格時，自動解析檔案格式，並將資料讀取為一個帶有Schema的資料集（Dataset）。使用者就可以直接在結果上選擇部分列進行類似基於表的資料處理，產生的結果可通過Unload命令直接匯出至OSS，或者通過CREATE TABLE AS匯入到內部表中。也可以將該Dataset作為其他SQL的子查詢，從而在SQL運算中靈活使用MaxCompute操作湖上的資料。

使用限制

Schemaless Query暫不支援把OSS Bucket中的子目錄當作分區處理。

文法結構

SELECT *, <col_name>, <table_alias>.<col_name> 
FROM 
 LOCATION '<location_path>'  
 ('key'='value' [, 'key1'='value1', ...]) 
 [AS <table_alias>];

參數說明

參數	是否必填	說明
*	是	查詢Parquet檔案中的所有欄位。
col_name	是	查詢Parquet檔案中已知列名的欄位。
table_alias.col_name	是	查詢Parquet檔案中已知列名的欄位，以表別名和欄位名完整路徑方式表達。
table_alias	否	自訂表格別名。
location_path	是	Parquet檔案的location，必須是一個OSS目錄，結構為oss://oss_endpoint/bucket_name/path/。 path的下一級支援partition_name=partition_value格式的分區目錄。說明 Schemaless Query暫不支援把location中的子目錄當作分區處理，因此不支援分區裁剪。
key&value	是	查詢語句的參數和參數值。詳情如下表。

key&value選擇性參數：

key	value	是否必填	說明	預設值
file_format	parquet	是	指定Location檔案的格式僅支援Parquet，其他格式將報錯並提示不支援。	parquet
rolearn	acs:ram::xxxxxx:role/aliyunodpsdefaultrole	否	指定訪問Location所需Rolearn。您可以通過RAM控制台中的角色頁面搜尋AliyunODPSDefaultRole進行擷取。說明在SQL語句中，如果未指定Rolearn，系統將預設使用`AliyunODPSDefaultRole`角色的ARN資訊。	acs:ram::1234****:role/aliyunodpsdefaultrole
file_pattern_blacklist	Regex。如： `"._SUCCESS$,.\\.hive_staging.*"`	否	指定需要讀取的檔案的黑名單。若掃描的檔案名稱字匹配黑名單，則不讀取該檔案。	無
file_pattern_whitelist	Regex。如： `"._20250124_..parquet"`	否	指定需要讀取的檔案的白名單。只有掃描的檔案名稱字匹配白名單，才讀取該檔案。	`.*`

使用樣本

樣本1：通過設定黑白名單參數讀取OSS資料

準備資料。
登入OSS控制台，上傳測試資料至OSS Bucket指定目錄object-table-test/schema/。具體操作請參見OSS檔案上傳。
- 準備一個Parquet檔案用來讀取以及驗證白名單參數：。
- 準備一個CSV檔案用來驗證黑名單參數：

讀取Parquet檔案。

登入MaxCompute用戶端（odpscmd），執行如下SQL命令。

添加test_oss.csv為黑名單參數，讀取OSS的Parquet檔案。

SELECT tinyint_name, int_name, binary_name, float_name,varchar_name 
FROM location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/'
(
'file_format'='parquet',
'rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole',
'file_pattern_blacklist'='.*test_oss.*'
);

添加20250610TableSink為白名單參數，讀取OSS的Parquet檔案。

SELECT tinyint_name, int_name, binary_name, float_name, varchar_name 
FROM location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/'
(
'file_format'='parquet',
'rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole',
'file_pattern_whitelist'='.*20250610TableSink.*'
);

以上參數配置情形，均在讀取20250610TableSink檔案，返回結果如下：

+--------------+------------+-------------+------------+--------------+
| tinyint_name | int_name   | binary_name | float_name | varchar_name |
+--------------+------------+-------------+------------+--------------+
| 1            | 100        | abc         | 3.14       | N            |
+--------------+------------+-------------+------------+--------------+

樣本2：讀取Spark寫入的資料

基於Serverless Spark產生Parquet資料。更多操作請參見建立SQL任務。

說明

如果您的OSS目錄中已有通過Spark寫入產生的Parquet檔案，可忽略此步驟。
更多Schemaless Query操作請參見使用Schemaless Query方式讀取湖上Parquet資料。

登入E-MapReduce控制台，建立EMR Serverless-Spark工作空間。

在EMR Serverless > Spark頁面，單擊進入建立的工作空間，在資料開發頁面執行如下SQL。

CREATE TABLE example_table_parquet04 (
    id STRING,
    name STRING,
    age STRING,
    salary DOUBLE,
    is_active BOOLEAN,
    created_at TIMESTAMP,
    details STRUCT<department:STRING, position:STRING>
)
USING PARQUET;

INSERT INTO example_table_parquet04 VALUES
('1', 'Alice', '30', 5000.50, TRUE, TIMESTAMP '2024-01-01 10:00:00', STRUCT('HR', 'Manager')),
('2', 'Bob', '25', 6000.75, FALSE, TIMESTAMP '2024-02-01 11:00:00', STRUCT('Engineering', 'Developer')),
('3', 'Charlie','35', 7000.00, TRUE, TIMESTAMP '2024-03-01 12:00:00', STRUCT('Marketing', 'Analyst')),
('4', 'David', '40', 8000.25, FALSE, TIMESTAMP '2024-04-01 13:00:00', STRUCT('Sales', 'Representative')),
('5', 'Eve', '28', 5500.50, TRUE, TIMESTAMP '2024-05-01 14:00:00', STRUCT('Support', 'Technician'));

SELECT * FROM example_table_parquet04;

登入OSS控制台，在目標路徑下查看產生的資料檔案。

登入MaxCompute用戶端，添加_SUCCESS為黑名單參數，讀取OSS的Parquet檔案。假設OSS指定目錄為object-table-test/spark。

SELECT  *
from location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/spark/example_table_parquet04/'
(
'file_format'='parquet',
'rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole',
'file_pattern_blacklist'='.*_SUCCESS.*'
);

返回結果如下：

+----+---------+-----+------------+-----------+---------------------+----------------------------------------------+
| id | name    | age | salary     | is_active | created_at          | details                                      |
+----+---------+-----+------------+-----------+---------------------+----------------------------------------------+
| 1  | Alice   | 30  | 5000.5     | true      | 2024-01-01 10:00:00 | {department:HR, position:Manager}            |
| 2  | Bob     | 25  | 6000.75    | false     | 2024-02-01 11:00:00 | {department:Engineering, position:Developer} |
| 3  | Charlie | 35  | 7000.0     | true      | 2024-03-01 12:00:00 | {department:Marketing, position:Analyst}     |
| 4  | David   | 40  | 8000.25    | false     | 2024-04-01 13:00:00 | {department:Sales, position:Representative}  |
| 5  | Eve     | 28  | 5500.5     | true      | 2024-05-01 14:00:00 | {department:Support, position:Technician}    |
+----+---------+-----+------------+-----------+---------------------+----------------------------------------------+

樣本3：子查詢使用Schemaless Query

準備資料。
登入OSS控制台，上傳測試資料part-00001.snappy.parquet至OSS Bucket指定目錄object-table-test/schema/。具體操作請參見OSS檔案上傳。
登入MaxCompute用戶端，建立內部表，用於接收自動探查到的OSS資料。
```
CREATE TABLE ow_test (
    id INT,
    name STRING,
    age INT
);
```

通過Schemaless Query讀取OSS資料，命令如下。

SELECT * FROM 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
(
  'file_format'='parquet'
);

返回結果如下：

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

將讀取的OSS資料作為子查詢傳給外部SQL，並查詢結果表ow_test。

INSERT OVERWRITE TABLE ow_test
SELECT id,name,age FROM 
(
    SELECT * FROM 
    LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
    (
      'file_format'='parquet'
    )
);

SELECT * FROM ow_test;

返回結果如下：

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

樣本4：將Schemaless Query結果轉存為數倉內部表

準備資料。
登入OSS控制台，上傳測試資料part-00001.snappy.parquet至OSS Bucket指定目錄object-table-test/schema/。具體操作請參見OSS檔案上傳。

登入MaxCompute用戶端，通過Schemaless Query讀取OSS資料。

SELECT * FROM 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
(
  'file_format'='parquet'
);

返回結果如下：

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

將自動探查到的OSS資料通過CREATE TABLE AS語句複製到內表中，並查詢結果。

CREATE TABLE ow_test_2 AS 
  SELECT * FROM 
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
  (
    'file_format'='parquet'
   );

-- 查詢結果表ow_test_2
SELECT * FROM ow_test_2;

返回結果如下：

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

樣本5：將Schemaless Query結果UNLOAD回資料湖

準備資料。
登入OSS控制台，上傳測試資料part-00001.snappy.parquet至OSS Bucket指定目錄object-table-test/schema/。具體操作請參見OSS檔案上傳。

登入MaxCompute用戶端，通過Schemaless Query讀取OSS資料。

SELECT * FROM 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
(
  'file_format'='parquet'
);

返回結果如下：

+------------+------------+------------+
| id         | name       | age        |
+------------+------------+------------+
| 3          | Charlie    | 35         |
| 4          | David      | 40         |
| 5          | Eve        | 28         |
+------------+------------+------------+

將自動探查到的結果UNLOAD到OSS，更多UNLOAD操作，請參見UNLOAD。

UNLOAD FROM (
  SELECT * FROM 
  LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/schema/' 
  ('file_format'='parquet')
) 
INTO 
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/object-table-test/unload/ow_test_3/'
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
WITH SERDEPROPERTIES ('odps.external.data.enable.extension'='true')
STORED AS PARQUET;

查看OSS的目錄下已組建檔案：