AI 助理
文档备案控制台
官方文档
输入文档关键字查找
首页

特色功能：Schemaless Query

更新时间：
复制为 MD 格式
产品详情
我的收藏

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

否

指定需要读取的文件的黑名单。若扫描的文件名匹配黑名单，则不读取该文件。

正则表达式。如：

".*_SUCCESS$,.*\\.hive_staging.*"

无

file_pattern_whitelist

否

指定需要读取的文件的白名单。只有扫描的文件名匹配白名单，才读取该文件。

正则表达式。如：

".*_20250124_.*.parquet"

.*

使用示例

示例1：通过设置黑白名单参数读取OSS数据

  1. 准备数据。

    1. 登录对象存储OSS控制台。

    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. 登录对象存储OSS控制台。

      2. 在object-table-test/schema/Bucket目录下，单击上传文件。

    7. 在object-table-test/schema/OSS Bucket目录下准备一个CSV文件用来验证黑名单参数。

  2. 读取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写入的数据

  1. 创建OSS Bucket目录object-table-test/spark/。

  2. 基于Serverless Spark生成Parquet数据。更多操作请参见创建SQL任务。如果OSS目录中已有通过Spark写入生成的Parquet文件，可忽略此步骤。

    1. 登录E-MapReduce控制台，在左上角选择地域。

    2. 在左侧导航栏选择EMR Serverless > Spark。

    3. 在Spark页面，单击目标工作空间名称进入Spark工作空间或单击创建工作空间，创建成功后单击进入新建的工作空间。

    4. 在左侧导航栏选择数据开发，新建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;

  3. 登录OSS控制台，在目标路径下查看生成的数据文件。

  4. 登录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

  1. 准备数据。

    登录OSS控制台，上传测试数据part-00001.snappy.parquet至OSS Bucket指定目录object-table-test/schema/。具体操作请参见OSS文件上传。

  2. 登录MaxCompute客户端，创建内部表，用于接收自动探查到的OSS数据。

    CREATE TABLE ow_test (
    id INT,
    name STRING,
    age INT
);

  3. 通过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         |
+------------+------------+------------+

  4. 将读取的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结果转存为数仓内部表

  1. 准备数据。

    登录OSS控制台，上传测试数据part-00001.snappy.parquet至OSS Bucket指定目录object-table-test/schema/。具体操作请参见OSS文件上传。

  2. 登录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         |
+------------+------------+------------+

  3. 将自动探查到的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回数据湖

  1. 准备数据。

    登录OSS控制台，上传测试数据part-00001.snappy.parquet至OSS Bucket指定目录object-table-test/schema/。具体操作请参见OSS文件上传。

  2. 登录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         |
+------------+------------+------------+

  3. 将自动探查到的结果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的目录下已生成文件：image

上一篇：自定义解析器下一篇：OSS外部表相关分区操作