使用Schemaless Query查詢OSS中的Parquet資料-雲原生MaxCompute MaxCompute-阿里雲 - 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檔案中已知列名的欄位。 DECIMAL類型只支援`precision <=38 && scale<=18`。
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	是	指定Location檔案的格式僅支援Parquet，其他格式將報錯並提示不支援。	parquet	parquet
rolearn	否	指定訪問Location所需RoleARN。登入RAM控制台。在左側導覽列選擇身份管理 > 角色。在基礎資訊地區，可以擷取ARN資訊。說明在SQL語句中，如果未指定RoleARN，系統將預設使用`AliyunODPSDefaultRole`角色的ARN資訊。	`acs:ram::xxxxxx:role/aliyunodpsdefaultrole`	acs:ram::1234****:role/aliyunodpsdefaultrole
file_pattern_blacklist	否	指定需要讀取的檔案的黑名單。若掃描的檔案名稱匹配黑名單，則不讀取該檔案。	Regex。如： `"._SUCCESS$,.\\.hive_staging.*"`	無
file_pattern_whitelist	否	指定需要讀取的檔案的白名單。只有掃描的檔案名稱匹配白名單，才讀取該檔案。	Regex。如： `"._20250124_..parquet"`	`.*`

使用樣本

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

準備資料。
1. 登入Object Storage Service控制台。
2. 在左側導覽列單擊Bucket 列表。
3. 在Bucket 列表頁面，單擊建立 Bucket。
4. 建立OSS Bucket目錄object-table-test/schema/。
5. 準備Parquet檔案用於讀取以及驗證白名單參數。可以在本地執行如下Python代碼建立Parquet檔案。
```
import pandas as pd

# 樣本資料
data = [
    {'id': 3, 'name': 'Charlie', 'age': 35},
    {'id': 4, 'name': 'David', 'age': 40},
    {'id': 5, 'name': 'Eve', 'age': 28}
]

df = pd.DataFrame(data)

df['id'] = df['id'].astype('int32')
df['name'] = df['name'].astype('str')
df['age'] = df['age'].astype('int32')

output_filename = 'sample_data.parquet'
df.to_parquet(output_filename, index=False, engine='pyarrow')
```
6. 上傳Parquet檔案至object-table-test/schema/OSS Bucket目錄。
  1. 登入Object Storage Service控制台。
  2. 在object-table-test/schema/Bucket目錄下，單擊上傳檔案。
7. 在object-table-test/schema/OSS Bucket目錄下準備一個CSV檔案用來驗證黑名單參數。

讀取Parquet檔案。

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

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

SELECT id, name, age 
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.*'
);

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

SELECT id, name, age 
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'='.*sample_data.*'
);

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

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

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

建立OSS Bucket目錄object-table-test/spark/。

基於Serverless Spark產生Parquet資料。更多操作請參見建立SQL任務。如果OSS目錄中已有通過Spark寫入產生的Parquet檔案，可忽略此步驟。

登入E-MapReduce控制台，在左上方選擇地區。
在左側導覽列選擇EMR Serverless > Spark。
在Spark頁面，單擊目標工作空間名稱進入Spark工作空間或單擊创建工作空间，建立成功後單擊進入建立的工作空間。

在左側導覽列選擇資料開發，建立SparkSQL檔案執行如下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檔案。

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

更多Schemaless Query操作請參見使用Schemaless Query方式讀取湖上Parquet資料。

樣本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的目錄下已組建檔案：