MaxCompute支持您基于内置Extractor访问对象存储服务OSS上的非结构化数据。例如CSV或TSV文本文件数据、GZIP压缩格式文件数据。本文为您介绍如何基于内置Extractor来创建OSS外部表,实现访问存储在OSS上的数据。

前提条件

在访问OSS非结构化数据前,请您确认已完成以下操作:
  • OSS授权。

    更多授权操作信息,请参见STS模式授权

  • 已准备好OSS存储空间(Bucket)、OSS目录及OSS数据文件。

    更多OSS存储空间操作信息,请参见创建存储空间

    更多新建OSS目录操作信息,请参见创建目录

    更多上传数据文件操作信息,请参见上传文件

背景信息

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

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

通过OSS外部非分区表或分区表访问OSS数据操作流程不相同,分别为:
  • OSS外部非分区表:通过建表语句创建OSS外部表后,即可通过查询OSS外部表来访问OSS数据。
  • OSS外部分区表:通过建表语句创建OSS外部表后,还需要将OSS上存储的分区数据引入OSS外部表,才可以通过查询OSS外部表来访问OSS数据。
MaxCompute内置的Extractor包含如下两种:
  • com.aliyun.odps.CsvStorageHandler:定义如何读写CSV格式的数据。数据各列以英文逗号(,)为分隔符,换行符为\n
  • com.aliyun.odps.TsvStorageHandler:定义如何读写TSV格式的数据。数据各列以\t为分隔符,换行符为\n
说明 您也可以通过DataWorks配合MaxCompute可视化创建、搜索、查询、配置、加工和分析外部表,详情请参见外部表

使用限制

  • 内置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

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

创建OSS外部表为分区表后,需要执行操作引入OSS目录下的分区数据,然后MaxCompute会根据创建OSS外部表时指定的OSS路径,自动补全OSS外部表的分区数据。语法格式如下:
  • 格式一(推荐):系统会自动解析OSS目录结构,自动识别分区,并为OSS外部表添加分区信息。
    msck repair table <mc_oss_extable_name> add partitions;
    mc_oss_extable_name:必填。待补全分区数据的OSS外部表的名称。
  • 格式二:手动为OSS外部表添加分区信息。
    alter table <mc_oss_extable_name> add partition (<col_name>=<col_value>) [add partition (<col_name>=<col_value>)...]
    • mc_oss_extable_name:必填。待补全分区数据的OSS外部表的名称。
    • col_name:必填。分区列的名称。
    • col_value:必填。分区列的列值。
    col_namecol_value的值需要与OSS目录下的分区数据所在目录名称对齐。例如,分区数据的OSS目录结构如下图,col_name对应directioncol_value对应N、NE、S、SW、W。一个add partition对应一个OSS目录,多个OSS目录需要使用多个add partition分区路径

示例数据

为便于理解,本文为您提供源数据,基于源数据提供相关示例。在OSS上准备的数据信息如下:
  • oss_endpointoss-cn-hangzhou-internal.aliyuncs.com,即华东1(杭州)。
  • Bucket名称oss-mc-test
  • 目录名称Demo1/(与非分区OSS外部表关联)、Demo2/(与分区OSS外部表关联)和Demo3/(关联压缩文件)。目录
  • 上述三个目录下分别上传以下数据文件:
    • Demo1/目录下上传的文件为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/15/2014 0:00,S
      1,7,53,1,46.81006,-92.08174,9/15/2014 0:00,N
      1,8,63,1,46.81006,-92.08174,9/15/2014 0:00,SW
      1,9,4,1,46.81006,-92.08174,9/15/2014 0:00,NE
      1,10,31,1,46.81006,-92.08174,9/15/2014 0:00,N
      Demo1
    • Demo2/目录下包含五个子目录direction=Ndirection=NEdirection=Sdirection=SWdirection=W,这五个子目录下上传的文件为vehicle1.csv、vehicle2.csv、vehicle3.csv、vehicle4.csv和vehicle5.csv。文件中包含的数据信息如下:
      --vehicle1.csv
      1,7,53,1,46.81006,-92.08174,9/15/2014 0:00
      1,10,31,1,46.81006,-92.08174,9/15/2014 0:00
      
      --vehicle2.csv
      1,2,13,1,46.81006,-92.08174,9/14/2014 0:00
      1,3,48,1,46.81006,-92.08174,9/14/2014 0:00
      1,9,4,1,46.81006,-92.08174,9/15/2014 0:00
      
      --vehicle3.csv
      1,6,9,1,46.81006,-92.08174,9/15/2014 0:00
      1,5,47,1,46.81006,-92.08174,9/14/2014 0:00
      1,6,9,1,46.81006,-92.08174,9/15/2014 0:00
      
      --vehicle4.csv
      1,8,63,1,46.81006,-92.08174,9/15/2014 0:00
      
      --vehicle5.csv
      1,4,30,1,46.81006,-92.08174,9/14/2014 0:00
      Demo2
    • Demo3/目录下上传的文件为vehicle.csv.gz,压缩包内文件为vehicle.csv,与Demo1/目录下的文件内容相同。压缩

使用示例

  • 示例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          |
      +------------+------------+------------+
说明 如果底层通过HTTPS方式获取数据,您可以将set odps.sql.unstructured.data.oss.use.https=true;命令与SQL查询语句一起提交执行。