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

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

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

内置Extractor访问OSS数据

通过外部表访问外部数据源时,需要您自定义不同的Extractor,或者也可以使用MaxCompute内置的Extractor,读取按照约定格式存储的OSS数据。
说明 内置Extractor不支持访问OSS文本文件的DateTime类型数据。如果有此需求请自定义Extractor,详情请参见MaxCompute自定义Exractor访问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模式授权时,需要指定此参数。您可以通过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目录的关联,当Drop这张表时,对应的LOCATION数据不会被删除。
您可以执行如下语句,查看创建好的外部表结构信息。
desc extended <table_name>;

在返回的信息里,除了包含和内部表一样的基础信息,Extended Info还包含外部表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
执行如下SQL语句。
select recordId, patientId, direction from ambulance_data_csv_external where 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
  • 建立外部表时,需要指定分区ptname1ptname2的分区结构。
  • 执行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 字符串 空字符串 在解析或者写出NULL值时代表NULL的字符串。
odps.text.option.ignore.empty.lines True/False True 是否忽略空行。
odps.text.option.encoding UTF-8/UTF-16/US-ASCII UTF-8 指定文本的字符编码。
odps.text.option.delimiter 单个字符 英文逗号(,) 指定文本的列分隔符。
说明

当关联OSS压缩数据的外部表,读(Select)、写(Insert)操作也需要进行,那么创建这个外部表时,需要同时将odps.text.option.gzip.input.enabledodps.text.option.gzip.output.enabled两个属性设置为True。