OSS外部表

OSS外部表分为分区表和非分区表，具体类型需要根据数据文件在OSS中的存储路径选择。当数据文件以分区路径方式存储时，需要创建分区表，否则创建非分区表。

注意事项

对于不同格式的数据文件，创建OSS外部表语句在个别参数设置上会不同，请根据创建OSS外部表语法及参数说明创建符合业务需求的外部表，否则读取OSS数据或将数据写入OSS操作时会执行失败。

语法说明

• 场景：通过内置文本数据解析器创建外部表

  语法格式	数据文件格式	示例
  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> ( <col_name> <data_type>, ... ) [comment <table_comment>] [partitioned BY (<col_name> <data_type>, ...)] stored BY '<StorageHandler>' WITH serdeproperties ( ['<property_name>'='<property_value>',...] ) location '<oss_location>';	支持读取或写入OSS的数据文件格式： CSV TSV 以GZIP、SNAPPY或LZO方式压缩的CSV、TSV	创建OSS外部表-非分区表 创建OSS外部表-分区表 创建OSS外部表-压缩数据

• 场景：通过内置开源数据解析器创建外部表

  语法格式	数据文件格式	示例
  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> ( <col_name> <data_type>, ... ) [comment <table_comment>] [partitioned BY (<col_name> <data_type>, ...)] [row format serde '<serde_class>' [WITH serdeproperties ( ['<property_name>'='<property_value>',...]) ] ] stored AS <file_format> location '<oss_location>' [USING '<resource_name>'] [tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];	支持读取或写入OSS的数据文件格式： PARQUET TEXTFILE ORC RCFILE AVRO JSON SEQUENCEFILE Hudi（仅支持读取DLF生成的Hudi数据） 以ZSTD、SNAPPY或GZIP方式压缩的PARQUET 以SNAPPY、ZLIB方式压缩的ORC 以GZIP、SNAPPY、LZO方式压缩的TEXTFILE	创建PARQUET外部表 创建ORC外部表

• 场景：通过自定义解析器创建外部表

  语法格式	数据文件格式	示例
  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> ( <col_name> <date_type>, ... ) [comment <table_comment>] [partitioned BY (<col_name> <data_type>, ...)] stored BY '<StorageHandler>' WITH serdeproperties ( ['<property_name>'='<property_value>',...] ) location '<oss_location>' USING '<jar_name>';	支持读取或写入OSS的数据文件格式：除上述格式外的数据文件。	创建OSS外部表 创建OSS外部表-非文本数据

参数说明

以下为各类格式外表的公共参数说明，您可以在对应格式的文档中查看并使用其独有参数。

• 基础语法参数说明

  参数名称	是否必填	说明
  mc_oss_extable_name	是	待创建的OSS外部表的名称。 表名大小写不敏感，在查询外部表时，无需区分大小写，且不支持强制转换大小写。
  col_name	是	OSS外部表的列名称。 在读取OSS数据场景，创建的OSS外部表结构必须与OSS数据文件结构保持一致，否则无法成功读取OSS数据。
  data_type	是	OSS外部表的列数据类型。 在读取OSS数据场景，创建的OSS外部表各列数据类型必须与OSS数据文件各列数据类型保持一致，否则无法成功读取OSS数据。
  table_comment	否	表注释内容。注释内容为长度不超过1024字节的有效字符串，否则报错。
  partitioned by (col_name data_type, ...)	否	当OSS中的数据文件是以分区路径方式存储时，需要携带该参数，创建分区表。 col_name：分区列名称。 data_type：分区列数据类型。
  'property_name'='property_value'	是	OSS外部表扩展属性，参数详解见各格式文档独有参数。
  oss_location	是	数据文件所在OSS路径。格式为oss://<oss_endpoint>/<Bucket名称>/<OSS目录名称>/。MaxCompute默认会读取该路径下的所有数据文件。 oss_endpoint：OSS访问域名信息。您需要使用OSS提供的经典网络域名，否则将产生OSS流量费用。例如oss://oss-cn-beijing-internal.aliyuncs.com/xxx。更多OSS经典网络域名信息，请参见OSS地域和访问域名。 建议数据文件存放的OSS地域与MaxCompute项目所在地域保持一致。如果跨地域，数据连通性可能存在问题。 Bucket名称：OSS存储空间名称，即Bucket名称。更多查看存储空间名称信息，请参见列举存储空间。 目录名称：OSS目录名称。目录后不需要指定文件名。 --正确用法。 oss://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ --错误用法。 http://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- 不支持HTTP连接。 https://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- 不支持HTTPS连接。 oss://oss-cn-shanghai-internal.aliyuncs.com/Demo1 -- 连接地址错误。 oss://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/vehicle.csv -- 不需要指定文件名。
  '<tbproperty_name>'='<tbproperty_value>'	否	OSS外部表扩展属性，参数详解见各格式文档独有参数。

• WITH serdeproperties属性

  property_name	使用场景	property_value	默认值
  odps.properties.rolearn	使用STS模式授权时，请添加该属性。	指定RAM中Role（具有访问OSS权限）的ARN信息。您可以通过RAM控制台中的角色详情进行获取。示例如下acs:ram::xxxxxx:role/aliyunodpsdefaultrole。	建表语句中如果不写odps.properties.rolearn，默认会使用aliyunodpsdefaultrole角色的ARN信息。

补全OSS外部表分区数据语法

创建的OSS外部表为分区表时，需要额外执行引入分区数据的操作，具体如下。

• 方式一（推荐）：自动解析OSS目录结构，识别分区，为OSS外部表添加分区信息。

  该方式适用于一次性补全全部缺失的历史分区的场景。MaxCompute会根据您创建OSS外部表时指定的分区目录，自动补全OSS外部表的分区，而不用逐个按照分区列名和名称增加。

  MSCK REPAIR TABLE <mc_oss_extable_name> ADD PARTITIONS [ WITH properties (key:VALUE, key: VALUE ...)];

  该方式不适用于处理增量数据的补充，尤其是在OSS目录中包含大量分区（如超过1000个）的情况下。当新增的分区远少于已有分区时，频繁使用msck命令会导致对OSS目录大量的重复扫描和元数据更新情况，从而降低命令执行的效率。对于需要更新增量分区的场景，建议采用方式二。

• 方式二：手动执行命令为OSS外部表添加分区信息。

  该方式适用于历史分区已经创建完成，需要频繁地周期性追加分区的场景。在执行数据写入任务之前创建好分区，即使OSS上有新数据写入，也不需要刷新对应分区，外部表即可读取OSS目录上的最新数据。

  ALTER TABLE <mc_oss_extable_name> 
    ADD PARTITION (<col_name>=<col_value>)[
    ADD PARTITION (<col_name>=<col_value>)...][location URL];

  col_name和col_value的值需要与分区数据文件所在目录名称对齐。

  假设，分区数据文件所在的OSS目录结构如下图，col_name对应direction，col_value对应N、NE、S、SW、W。一个add partition对应一个子目录，多个OSS子目录需要使用多个add partition。

  

注意事项

• 创建好OSS外部表后，才可以通过外部表读取OSS数据。MaxCompute支持的OSS数据文件类型及创建OSS外部表语法，请参见语法说明。

• SQL语句中涉及到复杂数据类型时，需要在SQL语句前添加set odps.sql.type.system.odps2=true;命令，然后一起提交执行。数据类型相关信息请参见数据类型版本说明。

• 对于映射开源数据的OSS外部表，需要在Session级别设置set odps.sql.hive.compatible=true;后再读取OSS数据，否则会报错。

• OSS对外提供的带宽资源有限，若短时间内数据读写流量超过实例带宽上限，会直接影响OSS外表的数据读写速度。关于OSS带宽详情，请参见使用限制及性能指标。

语法说明

<select_statement> FROM <from_statement>;

• select_statement：SELECT子句，从源表中查询需要插入目标表的数据。

• from_statement：FROM子句，数据来源，如外表名称。

非分区数据

创建OSS外部表（非分区表）后，读取OSS数据方式如下：

• 方式一（推荐）：将OSS的开源格式数据导入MaxCompute内部表，然后再读取OSS数据。

  该方式适用于需要对数据进行反复计算或对计算性能要求高的场景。通过在MaxCompute项目中创建与OSS外部表Schema相同的内部表，将OSS数据导入该内部表后执行复杂查询，可充分利用MaxCompute针对内部存储的优化机制，提升计算性能。命令示例如下：

  CREATE TABLE <table_internal> LIKE <mc_oss_extable_name>;
  INSERT OVERWRITE TABLE <table_internal> SELECT * FROM <mc_oss_extable_name>;

• 方式二：与MaxCompute内部表的操作一致，直接读取OSS数据。

  该方式适用于对计算性能要求不高的场景。与读取内部表不同的是，每次读取数据都是直接从OSS读取对应的数据。

分区数据

创建OSS外部表后，MaxCompute会全量扫描OSS目录下的所有数据，包括子目录下的数据文件。当数据量较大时，对全量目录扫描会产生不必要的I/O消耗以及数据处理时间。解决该问题有如下两种方式。

• 方式一（推荐）：在OSS上将数据以标准分区路径或自定义分区路径方式存储。

  创建OSS外部表时，需要在建表语句中指定分区及oss_location信息。推荐使用标准分区路径存储OSS数据。

• 方式二：规划多个数据存放路径。

  创建多个OSS外部表读取各个路径下的数据，即每个OSS外部表指向OSS数据的一个子集。该方式操作繁琐，数据管理效果不佳，不推荐使用。

标准分区路径格式

oss://<oss_endpoint>/<Bucket名称>/<目录名称>/<partitionKey1=value1>/<partitionKey2=value2>/...

示例：某公司会将每天产生的Log文件以CSV格式存放在OSS上，并通过MaxCompute以天为周期处理数据。则存储OSS数据的标准分区路径应设置如下。

oss://oss-odps-test/log_data/year=2016/month=06/day=01/logfile
oss://oss-odps-test/log_data/year=2016/month=06/day=02/logfile
oss://oss-odps-test/log_data/year=2016/month=07/day=10/logfile
oss://oss-odps-test/log_data/year=2016/month=08/day=08/logfile
...

自定义分区路径格式

自定义分区路径格式：只有分区列值，没有分区列名。示例如下：

oss://oss-odps-test/log_data_customized/2016/06/01/logfile
oss://oss-odps-test/log_data_customized/2016/06/02/logfile
oss://oss-odps-test/log_data_customized/2016/07/10/logfile
oss://oss-odps-test/log_data_customized/2016/08/08/logfile
...

OSS的数据已按照分区方式存储，但其路径并非标准分区路径格式，MaxCompute支持将不同的子目录绑定至不同的分区，从而访问子目录数据。

实现方案：创建OSS外部表后，通过alter table ... add partition ... location ...命令指定子目录，将不同的子目录绑定到不同的分区，命令示例如下。

ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '01')
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket名称/oss-odps-test/log_data_customized/2016/06/01/';
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '06', day = '02')
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket名称/oss-odps-test/log_data_customized/2016/06/02/';
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '07', day = '10')
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket名称/oss-odps-test/log_data_customized/2016/07/10/';
ALTER TABLE log_table_external ADD PARTITION (year = '2016', month = '08', day = '08')
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/bucket名称/oss-odps-test/log_data_customized/2016/08/08/';

查询优化

• 动态统计信息收集

  由于数据存储在外部数据湖且缺乏预先统计信息，查询优化器采用保守策略，查询效率低。动态统计信息收集功能支持Optimizer在Query执行中临时统计表的Stats来发现小表，并主动采用Hash Join、优化Join Order、减少Shuffle和缩短执行的Pipeline等方法，实现查询优化。

  SET odps.meta.exttable.stats.onlinecollect=true;
  SELECT * FROM <tablename>;

• 外表Split优化

  通过设置split size参数调整单个并发处理的数据大小，从而优化查询效率。其中split size的设置有如下影响。

  • 表数据量非常大，每次读取的数据太少，会导致split数太多，并行度多大，Instance大多数时间在排队等资源。

  • 表数据量非常小，每次读取的数据太多，会导致split数太少，并发不够，大部分资源都空闲。

  SET odps.stage.mapper.split.size=<value>;
  SELECT * FROM <tablename>;

语法说明

INSERT {INTO|OVERWRITE} TABLE <table_name> PARTITION (<ptcol_name>[, <ptcol_name> ...]) 
<select_statement> FROM <from_statement>;

如果需要向动态分区中插入数据，请参考插入或覆写动态分区数据（DYNAMIC PARTITION）。

注意事项

• 如果INSERT OVERWRITE ... SELECT ... FROM ...;操作在源数据表from_tablename上分配了1000个Mapper，则最后将产生1000个TSV或CSV文件。

• 可以通过MaxCompute提供的配置控制生成文件的数量。

  • Outputer在Mapper里：通过odps.stage.mapper.split.size控制Mapper的并发数，从而调整生成的文件数量。

  • Outputer在Reduce或Joiner里：分别通过odps.stage.reducer.num和odps.stage.joiner.num来调整生成的文件数量。

通过OSS分片上传功能将数据写入OSS

当需要将数据以开源格式写入OSS时，可以通过基于开源数据解析器创建的OSS外部表及OSS的分片上传功能，执行INSERT操作将数据写入OSS。分片上传功能信息，请参见分片上传。

开启OSS分片上传功能设置如下：

setproject odps.sql.unstructured.oss.commit.mode =true;

set odps.sql.unstructured.oss.commit.mode =true;

属性odps.sql.unstructured.oss.commit.mode默认取值为false，不同取值时的实现原理如下：

常见问题

insert overwrite操作场景，在极端情况下如果作业运行失败会出现与预期不一致的问题，表现为旧数据被删除，但是新数据并没有写入。

• 问题原因：新写入的数据因为极低概率的硬件故障或元数据更新失败等原因没有成功写入目标表，且OSS的删除操作不支持回滚，导致被删除的旧数据无法恢复。

• 解决方案：

  • 如果基于旧数据再覆写OSS外部表，例如insert overwrite table T select * from table T;，需要提前做好OSS数据备份，作业运行失败时，再基于备份的旧数据覆写OSS外部表。

  • 如果insert overwrite作业可以重复提交，当作业运行失败时重新提交作业即可。