本文为您介绍如何使用内置Extractor在MaxCompute上访问OSS的数据。

在访问OSS结构化数据前,您需要完成授权,详情请参见OSS的STS模式授予权限

创建外部表后,即可通过查询外部表直接访问OSS上存储的数据。详情请参见创建外部表查询外部表
说明 您也可以通过DataWorks配合MaxCompute对外部表进行可视化创建、搜索、查询、配置、加工和分析,详情请参见外部表

功能介绍

通过外部表访问外部数据源时,您可以使用MaxCompute内置的Extractor,读取按照约定格式存储的OSS数据。

说明 内置Extractor不支持访问OSS文本文件的DATETIME类型数据。

假设有一份CSV数据存储在OSS上,Endpoint为oss-cn-shanghai-internal.aliyuncs.com,Bucket为oss-odps-test,数据文件的存放路径为/demo/vehicle.csv

创建外部表

创建外部表命令示例如下。
CREATE EXTERNAL TABLE IF NOT EXISTS ambulance_data_csv_external
(
vehicleId INT,
recordId INT,
patientId INT,
calls INT,
locationLatitute DOUBLE,
locationLongtitue DOUBLE,
recordTime STRING,
direction STRING
)
STORED BY 'com.aliyun.odps.CsvStorageHandler' 
WITH SERDEPROPERTIES (
 'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole'
) 
LOCATION 'oss://oss-cn-shanghai-internal.aliyuncs.com/oss-odps-test/Demo/';
  • STORED BY:指定内置的StorageHandler。com.aliyun.odps.CsvStorageHandler是内置的处理CSV格式文件的StorageHandler,定义了如何读或写CSV文件。您只需要指定该参数,相关逻辑已经由系统实现。
  • WITH SERDEPROPERTIES:指定外部表相关参数。

    odps.properties.rolearn:指定RAM中AliyunODPSDefaultRole的ARN信息。当关联OSS权限使用STS模式授权时,您需要指定此参数,详情请参见STS模式授权。您可以通过RAM控制台中的角色详情获取。

  • LOCATION:指定需要读取数据的OSS目录,系统会默认读取该目录下所有的文件。
    • 建议您使用OSS提供的内网域名,否则将产生OSS流量费用。
    • 建议您存放数据的OSS区域对应您开通MaxCompute服务的区域。由于MaxCompute只在部分区域部署,跨区域的数据连通性可能存在问题。
    • OSS的目录格式为oss://oss-cn-shanghai-internal.aliyuncs.com/Bucket名称/目录名称/。目录后不加文件名称,如下为错误用法。
      http://oss-odps-test.oss-cn-shanghai-internal.aliyuncs.com/Demo/  -- 不支持HTTP连接。
      https://oss-odps-test.oss-cn-shanghai-internal.aliyuncs.com/Demo/ -- 不支持HTTPS连接。
      oss://oss-odps-test.oss-cn-shanghai-internal.aliyuncs.com/Demo -- 连接地址错误。
      oss://oss://oss-cn-shanghai-internal.aliyuncs.com/oss-odps-test/Demo/vehicle.csv  -- 不需要指定文件名。
      说明 外部表只是在系统中记录了与OSS目录的关联。当删除外部表时,不会删除对应的LOCATION数据。
您可以执行如下命令,查看创建好的外部表结构信息。
DESC EXTENDED <table_name>;

返回结果包含和内部表一样的基础信息、外部表的StorageHandler和Location等信息。

查询外部表

外部表创建成功后,您可以像使用普通表一样使用外部表。假设/demo/vehicle.csv数据如下。
1,1,51,1,46.81006,-92.08174,9/14/2014 0:00,S
1,2,13,1,46.81006,-92.08174,9/14/2014 0:00,NE
1,3,48,1,46.81006,-92.08174,9/14/2014 0:00,NE
1,4,30,1,46.81006,-92.08174,9/14/2014 0:00,W
1,5,47,1,46.81006,-92.08174,9/14/2014 0:00,S
1,6,9,1,46.81006,-92.08174,9/14/2014 0:00,S
1,7,53,1,46.81006,-92.08174,9/14/2014 0:00,N
1,8,63,1,46.81006,-92.08174,9/14/2014 0:00,SW
1,9,4,1,46.81006,-92.08174,9/14/2014 0:00,NE
1,10,31,1,46.81006,-92.08174,9/14/2014 0:00,N
执行如下命令。
SELECT recordId, patientId, direction FROM ambulance_data_csv_external WHRER patientId > 25;
说明
  • 您只能通过MaxCompute SQL操作外部表,不能通过MaxCompute MapReduce操作外部表。
  • 若需要底层通过HTTPS方式获取数据,您可以将命令set odps.sql.unstructured.data.oss.use.https=true;与SQL语句一起提交执行。
上述示例命令会提交一个作业,调用内置CSV Extractor,从OSS读取并处理数据。返回结果如下。
+------------+------------+-----------+
| recordId   | patientId  | direction |
+------------+------------+-----------+
| 1          | 51         | S         |
| 3          | 48         | NE        |
| 4          | 30         | W         |
| 5          | 47         | S         |
| 7          | 53         | N         |
| 8          | 63         | SW        |
| 10         | 31         | N         |
+------------+------------+-----------+

外部表分区自动补全(MSCK REPAIR TABLE)

MaxCompute支持根据数据所在的OSS文件路径自动补全外部表分区,语法格式如下。
MSCK [REPAIR] TABLE external_table_name [ADD PARTITIONS];
语法说明:
  • 将数据导入OSS时,OSS文件路径需要符合格式oss://xxx/table-location/ptname1=ptvalue1/ptname2=ptvalue2/xxx
  • 建立外部表时,需要指定分区的结构。
  • 执行MSCK [REPAIR] TABLE external_table_name [ADD PARTITIONS];时,SQL会自动解析OSS的目录结构,自动识别分区,并为外部表添加分区信息。
示例
  1. 上传数据至OSS,OSS文件路径如下。OSS
  2. 执行如下命令创建外部表orc_pt_v0,外部表中需要指定分区pt的结构。
    CREATE EXTERNAL TABLE orc_pt_v0
    (
    name STRING
    )
    PARTITIONED BY (pt bigint)
    STORED BY 'com.aliyun.odps.CsvStorageHandler' 
    WITH SERDEPROPERTIES (
    'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole'
    ) 
    LOCATION 'oss://xxx/odps-ext-reg-perf/orc-pt-v0';
  3. 执行如下命令自动补全外部表orc_pt_v0的分区。
    MSCK REPAIR TABLE orc_pt_v0 ADD PARTITIONS;
    --该场景中,MSCK语法等价于执行如下三条语句
    ALTER TABLE orc_pt_v0 ADD PARTITION (pt=1);
    ALTER TABLE orc_pt_v0 ADD PARTITION (pt=10);
    ALTER TABLE orc_pt_v0 ADD PARTITION (pt=100);

读取GZIP格式压缩的CSV或TSV数据

MaxCompute只支持通过内置Extractor读取OSS上以GZIP格式压缩的CSV或TSV数据。与读取非压缩文件相比,主要区别是SERDEPROPERTIES指定的属性。
说明 如果OSS上的数据文件为归档文件,请登录OSS,解冻文件。
创建外部表的命令示例如下。
CREATE EXTERNAL TABLE IF NOT EXISTS ambulance_data_csv_external
(
vehicleId BIGINT,
recordId BIGINT,
patientId BIGINT,
calls BIGINT,
locationLatitute DOUBLE,
locationLongtitue DOUBLE,
recordTime STRING,
direction STRING
)
STORED BY 'com.aliyun.odps.CsvStorageHandler'
WITH SERDEPROPERTIES (
 'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole'
 [,'odps.text.option.gzip.input.enabled'='true']
 [,'name3'='value3']
)
LOCATION 'oss://oss-cn-hangzhou-zmf.aliyuncs.com/oss-odps-test/Demo/SampleData/CSV/AmbulanceData/';
SERDEPROPERTIES支持的属性项如下。
属性名 属性值 默认值 说明
odps.text.option.gzip.input.enabled
  • True
  • False
False 打开或关闭读压缩。
odps.text.option.gzip.output.enabled
  • True
  • False
False 打开或关闭写压缩。
odps.text.option.header.lines.count 非负整数 0 跳过文本文件前N行。
odps.text.option.null.indicator 字符串 空字符串 指定文本中的哪种字符串会被解析为SQL中的NULL。例如\N指代NULL,则a,\N,b会解析为a, NULL, b
odps.text.option.ignore.empty.lines
  • True
  • False
True 指定是否忽略空行。
odps.text.option.encoding
  • UTF-8
  • UTF-16
  • US-ASCII
  • GBK
UTF-8 指定文本的字符编码。
odps.text.option.delimiter 单个字符 英文逗号(,) 指定文本的列分隔符。
odps.text.option.use.quote
  • True
  • False
False 当CSV某个字段中包含换行(CRLF)、双引号(需要在"前再加"转义)或英文逗号(分隔符)时,整个字段必须用双引号(" ")括起来作为列分隔符。该参数指定是否识别CSV的列分隔符"
说明

当关联OSS压缩数据的外部表时,如果您需要同时执行读或写操作,在创建外部表时,请将odps.text.option.gzip.input.enabledodps.text.option.gzip.output.enabled属性设置为True。