如果OSS上的数据为开源格式,内置Extractor或自定义Extractor无法满足需求,您需要在创建OSS外部表时,指定开源数据格式相关信息。本文为您介绍如何创建OSS外部表,实现关联、读取及处理存储在OSS上的开源格式数据。

前提条件

请确认您已完成如下操作:
  • OSS授权。

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

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

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

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

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

背景信息

MaxCompute支持通过非结构化框架关联存储在OSS的各种流行开源格式数据。对于开源格式数据,非结构框架会直接调用开源社区的实现来进行开源数据格式解析,并且与MaxCompute系统无缝对接。

该方案的实现流程与通过内置Extractor访问OSS的流程相同,二者的区别在于创建OSS外部表的语法不相同。本文仅针对建表语法着重进行介绍,使用限制、注意事项及实现流程示例请参见内置Extractor访问OSS

MaxCompute支持的开源数据格式包括:PARQUET、TEXT、CSV、JSON、ORC、AVRO、SEQUENCEFILE。

说明 您也可以通过DataWorks配合MaxCompute可视化创建、搜索、查询、配置、加工和分析外部表,详情请参见外部表

创建外部表语法格式

create external table [if not exists] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[partitioned by (<col_name> <data_type>, ...)]
[row format serde '<serde_class>'
  [with serdeproperties (
    'odps.properties.rolearn'='<ram_arn>' 
    [,'<property_name>'='<property_value>'[,'<property_name>'='<property_value>'...]])
]
--读取开源格式数据时独有的配置。
stored as <file_format>
location '<oss_location>'
[using '<resource_name>'];
  • 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:必填。分区列的数据类型。
  • serde_class:可选。仅在使用特殊格式时才需要配置,例如TEXTFILE(CSV、JSON、TEXT),其他场景可不配置。不同开源格式对应的serde_class如下:
    • PARQUET:org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe
    • TEXTFILE:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
    • ORC:org.apache.hadoop.hive.ql.io.orc.OrcSerde
    • AVRO:org.apache.hadoop.hive.serde2.avro.AvroSerDe
    • SEQUENCEFILE:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
    • RCFILE:org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe
    • ORCFILE:org.apache.hadoop.hive.ql.io.orc.OrcSerde
  • odps.properties.rolearn'='<ram_arn>:可选。指定RAM中AliyunODPSDefaultRole的ARN信息。您可以通过RAM控制台中的角色详情获取。
  • '<property_name>'='<property_value>':可选。property_name为属性名称,property_value为属性值。更多外部表属性信息,请参见下文中的建表语法示例。
  • file_format:必填。指定OSS上存储的开源数据的格式。例如ORC、PARQUET。
    说明 单个文件大小不能超过3 GB,如果文件过大,建议拆分。
  • 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  -- 不需要指定文件名。
  • using:可选。当使用自定义的serde class时,需要基于该参数指定依赖的资源。资源中包含了自定义的serde class。上传资源操作,请参见资源操作

关联PARQUET数据建表示例

建表语法示例如下。
create external table [if not exists] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
stored as parquet
location '<oss_location>';
默认PARQUET数据不压缩,如果您需要在MaxCompute上压缩PARQUET数据,请在SQL语句前添加set odps.sql.hive.compatible=true;命令,打开压缩开关,并与SQL语句一起提交。支持的压缩格式如下:
  • 在建表语句的with serdeproperties下增加'mcfed.parquet.compression'='SNAPPY'属性,指定PARQUET的压缩格式为SNAPPY
  • 在建表语句的with serdeproperties下增加'mcfed.parquet.compression'='GZIP'属性,指定PARQUET的压缩格式为GZIP

关联TEXTFILE数据建表示例

如果您的数据格式为TEXTFILE(例如CSV、JSON、TEXT),并且存放在OSS上,同时希望通过OSS外部表读写数据且支持压缩,MaxCompute提供外部表对SNAPPY、LZO压缩文件的读写能力。更多压缩格式数据读写信息,请参见支持压缩格式数据

  • 关联TEXT数据建表示例
    • 如果数据为JSON格式,存储为TEXTFILE文件,同时多个TEXTFILE文件存放在OSS的多个目录中,并以统一存储和命名方式组织,则可以使用MaxCompute分区表和数据进行关联。创建分区表语法示例如下。
      create external table [if not exists] <mc_oss_extable_name>
      (
      <col_name> <data_type>,
      ...
      )
      partitioned by (<col_name> <data_type>, ...)
      row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
      stored as textfile
      location '<oss_location>';
    • 如果OSS下的分区目录是以Partition Name方式组织,需要补全分区数据。补全分区数据操作,请参见补全外部表分区数据语法格式
    • 如果OSS分区目录不是按Partition Name方式组织,或不在表目录下,示例如下。
      oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket/text_data_20170102/;
      oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket/text_data_20170103/;
      ...
      此时,可以使用如下命令补全分区信息。
      alter table tpch_lineitem_textfile add partition(ds="20170102")
      location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket/text_data_20170102/';
      alter table tpch_lineitem_textfile add partition(ds="20170103")
      location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket/text_data_20170103/';
      ...
    • 建表时不支持自定义row formatrow format默认值如下。
      FIELDS TERMINATED BY :'\001'
      ESCAPED BY :'\'
      COLLECTION ITEMS TERMINATED BY :'\002'
      MAP KEYS TERMINATED BY :'\003'
      LINES TERMINATED BY :'\n'
      NULL DEFINED AS :'\N'
  • 关联CSV数据建表示例
    建表语法示例如下。
    create external table [if not exists] <mc_oss_extable_name>
    (
    <col_name> <data_type>,
    ...
    )
    [partitioned by (<col_name> <data_type>, ...)]
    row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
      with serdeproperties 
        ('separatorChar'=',', 'quoteChar'='"', 'escapeChar'='\\')
    stored as textfile
    location '<oss_location>';
    CSV格式数据的建表语句支持设置如下with serdeproperties属性。
    'separatorChar'=','
    'quoteChar'='"'
    'escapeChar'='\\'
    说明 OpenCSVSerde只支持STRING类型。执行DML语句时,您需要在SQL语句前增加set odps.sql.hive.compatible=true;命令,与SQL语句一起提交执行。
  • 关联JSON数据建表示例
    建表语法示例如下。
    create external table [if not exists] <mc_oss_extable_name>
    (
    <col_name> <data_type>,
    ...
    )
    [partitioned by (<col_name> <data_type>, ...)]
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored as textfile
    location '<oss_location>';

关联ORC数据建表示例

建表语法示例如下。
create external table [if not exists] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[partitioned by (<col_name> <data_type>, ...)]
stored as orc
location '<oss_location>';

关联AVRO数据建表示例

建表语法示例如下。
create external table [if not exists] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[partitioned by (<col_name> <data_type>, ...)]
stored as avro
location '<oss_location>';

关联SEQUENCEFILE数据建表示例

建表语法示例如下。
create external table [if not exists] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[partitioned by (<col_name> <data_type>, ...)]
stored as sequencefile
location '<oss_location>';

读取及处理OSS的开源格式数据

基于上述创建外部表示例可知,对于不同文件类型,您只需要简单修改file format即可。

完成外部表创建后,您可以根据需要选择如下方式之一对OSS外部表进行操作:
  • 方式一(推荐):将OSS的开源格式数据导入MaxCompute内部表,然后再读取及处理OSS的开源数据。

    如果直接使用OSS外部表,每次读取数据都涉及OSS的I/O操作,且MaxCompute系统本身针对内部存储设计的许多高性能优化都无法应用,因此性能上会有所损失。如果您需要对数据进行反复计算或对计算的高效性比较敏感,推荐先在MaxCompute项目中创建一个与OSS外部表Schema相同的内部表,然后将OSS上的开源数据导入MaxCompute内部表,在内部表上执行复杂查询语句,以便获得更高的计算性能。

    说明 SQL语句中涉及到复杂数据类型时,需要在SQL语句前添加set odps.sql.type.system.odps2=true;命令,和SQL语句一起提交执行。更多数据类型信息,请参见数据类型版本说明
    命令示例如下。
    create table table_internal like table_extable;
    insert overwrite table table_internal select * from table_extable;
  • 方式二:直接读取及处理OSS的开源数据,与MaxCompute内部表的操作相同。

    OSS外部表会被当作一个普通的内部表一样使用,不同之处在于,MaxCompute是直接从OSS读取对应的数据进行处理。

    说明 对于关联TEXTFILE数据的OSS外部表,您需要在Session级别设置set odps.sql.hive.compatible=true;后再读取数据,否则会报错。