内置Extractor访问OSS

背景信息

对象存储服务OSS（Object Storage Service）是一种海量、安全、低成本、高可靠的云存储服务，适合存放任意类型的文件。

您需要在MaxCompute上基于内置的Extractor创建OSS外部非分区表或分区表，才可以访问按照约定方式存储的CSV、TSV格式或以GZIP压缩的CSV或TSV格式的OSS数据。

使用限制

• 内置Extractor不支持访问OSS数据文件的DATETIME类型数据。
• 如果OSS上的数据以压缩格式存储，内置Extractor只支持读取GZIP格式的压缩文件，且压缩包内文件为CSV或TSV格式。
• OSS外部表的结构必须与OSS上的数据文件结构一致。
• 您只能通过MaxCompute SQL操作OSS外部表。

注意事项

使用OSS外部表时，您需要注意：

• 如果您创建的OSS外部表为分区表，分区数据在OSS上的存放路径必须符合一定的格式要求。更多格式要求，请参见OSS外部表的数据分区。
• OSS外部表只是在系统中记录与OSS目录的关联关系。当删除外部表时，不会删除对应OSS目录下的数据。
• 如果OSS上的数据文件类型为归档文件，需要先解冻文件。更多解冻操作，请参见解冻文件。

创建外部表语法格式

create external table [if not exists] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[partitioned by (<col_name> <data_type>, ...)]
--指定内置的Extractor。
stored by '<StorageHandler>' 
--指定外部表相关参数。
with serdeproperties (
 'odps.properties.rolearn'='<ram_arn>'
--OSS文件为GZIP压缩格式时需要配置。
 [,'odps.text.option.gzip.input.enabled'='true']
--其他OSS外部表相关属性，根据实际需要配置。
 [,'<property_name>'='<property_value>'[,'<property_name>'='<property_value>'...]]
) 
location '<oss_location>';

• if not exists：可选。如果不指定if not exists选项而存在同名表，会报错。如果指定if not exists，无论是否存在同名表，即使原表结构与要创建的目标表结构不一致，均返回成功。已存在的同名表的元数据信息不会被改动。
• mc_oss_extable_name：必填。待创建的OSS外部表的名称。
• col_name：必填。OSS外部表的列名称。
• data_type：必填。OSS外部表的列的数据类型。
• partitioned by (<col_name> <data_type>, ...)：可选。指定OSS外部表为分区表时的分区信息。
  • col_name：必填。分区列的名称。
  • data_type：必填。分区列的数据类型。
• StorageHandler：必填。指定内置的Extractor。读写CSV格式数据时，配置为com.aliyun.odps.CsvStorageHandler。读写TSV格式数据时，配置为com.aliyun.odps.TsvStorageHandler。您只需要指定该参数，相关逻辑已经由系统实现。
• odps.properties.rolearn'='<ram_arn>：必填。指定RAM中AliyunODPSDefaultRole的ARN信息。您可以通过RAM控制台中的角色详情获取。
• location：必填。指定数据文件的OSS路径。OSS目录格式为oss://<oss_endpoint>/<Bucket名称>/<目录名称>/。系统会默认读取该目录下的所有文件。
  • oss_endpoint：OSS访问域名信息。建议您使用OSS提供的内网域名，否则将产生OSS流量费用。更多OSS内网域名信息，请参见访问域名和数据中心。建议数据存放的OSS区域与MaxCompute项目所在区域保持一致。由于MaxCompute只在部分区域部署，跨区域的数据连通性可能存在问题。
  • Bucket名称：OSS存储空间名称，即Bucket名称。查看存储空间名称操作，请参见列举存储空间。
  • 目录名称：指定OSS目录名称。目录后不需要指定文件名，错误用法如下：

    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-cn-shanghai-internal.aliyuncs.com/oss-odps-test/Demo/vehicle.csv  -- 不需要指定文件名。

• 'odps.text.option.gzip.input.enabled'='true'：当OSS数据文件为GZIP压缩格式时，必须配置。
• <property_name>'='<property_value>'：可选。property_name为属性名称，property_value为属性值。OSS外部表相关属性列表如下。

  属性名称	说明	属性值	默认值
  odps.text.option.gzip.input.enabled	当OSS文件为GZIP压缩格式，创建OSS外部表时需要配置。打开或关闭读取GZIP压缩格式文件功能。	True False	False
  odps.text.option.gzip.output.enabled	当OSS文件为GZIP压缩格式且需要执行写操作，创建OSS外部表时需要配置。打开或关闭写GZIP压缩格式文件功能。	True False	False
  odps.text.option.header.lines.count	跳过文本文件前N行。	非负整数	0
  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	当CSV某个字段中包含换行（CRLF）、双引号（需要在"前再加"转义）或英文逗号（分隔符）时，整个字段必须用双引号（" "）括起来作为列分隔符。该参数指定是否识别CSV的列分隔符"。	True False	False

补全外部表分区数据语法格式

示例数据

使用示例

• 示例1：基于Demo1/目录下的OSS数据文件创建OSS外部非分区表，并查看外部表中的数据。
  1. 登录MaxCompute客户端，创建外部表。命令示例如下：

     create external table if not exists mc_oss_csv_external1
     (
     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::xxxxxx:role/aliyunodpsdefaultrole'
     ) 
     location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo1/';

     您可以执行desc extended mc_oss_csv_external1;命令查看创建好的OSS外部表结构信息。

  2. 通过MaxCompute客户端，基于创建的OSS外部表，从OSS读取并处理数据。命令示例如下：

     select recordId, patientId, direction from mc_oss_csv_external1 where patientId > 25;

     返回结果如下。

     +------------+------------+------------+
     | recordid   | patientid  | direction  |
     +------------+------------+------------+
     | 1          | 51         | S          |
     | 3          | 48         | NE         |
     | 4          | 30         | W          |
     | 5          | 47         | S          |
     | 7          | 53         | N          |
     | 8          | 63         | SW         |
     | 10         | 31         | N          |
     +------------+------------+------------+

• 示例2：基于Demo2/目录下的OSS数据文件创建OSS外部分区表，并查看外部表中的数据。
  1. 登录MaxCompute客户端，创建外部表。命令示例如下：

     create external table if not exists mc_oss_csv_external2
     (
     vehicleId int,
     recordId int,
     patientId int,
     calls int,
     locationLatitute double,
     locationLongtitue double,
     recordTime string
     )
     partitioned by (
     direction string
     )
     stored by 'com.aliyun.odps.CsvStorageHandler' 
     with serdeproperties (
      'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole'
     ) 
     location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo2/';

     您可以执行desc extended mc_oss_csv_external2;命令查看创建好的OSS外部表结构信息。

  2. 通过MaxCompute客户端，将分区数据引入OSS外部表中。命令示例如下：

     msck repair table mc_oss_csv_external2 add partitions;
     --等效于如下语句。
     alter table mc_oss_csv_external2 add partition (direction = 'N') partition (direction = 'NE') partition (direction = 'S') partition (direction = 'SW') partition (direction = 'W');

  3. 通过MaxCompute客户端，基于创建的OSS外部表，从OSS读取并处理数据。命令示例如下：

     select recordId, patientId, direction from mc_oss_csv_external2 where direction = 'NE';

     返回结果如下。

     +------------+------------+------------+
     | recordid   | patientid  | direction  |
     +------------+------------+------------+
     | 2          | 13         | NE         |
     | 3          | 48         | NE         |
     | 9          | 4          | NE         |
     +------------+------------+------------+

• 示例3：基于Demo3/目录下的GZIP文件创建OSS外部表，并查看外部表中的数据。
  1. 登录MaxCompute客户端，创建外部表。命令示例如下：

     create external table if not exists mc_oss_csv_external3
     (
     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::xxxxxx:role/aliyunodpsdefaultrole',
      'odps.text.option.gzip.input.enabled'='true'
     ) 
     location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo3/';

     您可以执行desc extended mc_oss_csv_external3;命令查看创建好的OSS外部表结构信息。

  2. 通过MaxCompute客户端，基于创建的OSS外部表，从OSS读取并处理数据。命令示例如下：

     select recordId, patientId, direction from mc_oss_csv_external3 where patientId > 25;

     返回结果如下。

     +------------+------------+------------+
     | recordid   | patientid  | direction  |
     +------------+------------+------------+
     | 1          | 51         | S          |
     | 3          | 48         | NE         |
     | 4          | 30         | W          |
     | 5          | 47         | S          |
     | 7          | 53         | N          |
     | 8          | 63         | SW         |
     | 10         | 31         | N          |
     +------------+------------+------------+