本文将为您介绍如何使用内置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。
  • 建立外部表时，需要指定分区ptname1和ptname2的分区结构。
  • 执行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.enabled和odps.text.option.gzip.output.enabled两个属性设置为True。