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：分区列数据类型。
  '<(tb)property_name>'='<(tb)property_value>'	是	OSS外部表扩展属性，参数详解见各格式文档独有参数。
  oss_location	是	数据文件所在OSS路径。默认读取该路径下的所有数据文件。 格式为oss://<oss_endpoint>/<Bucket名称>/<OSS目录名称>/。 oss_endpoint： OSS访问域名信息。需要使用OSS提供的经典网络域名，即带 -internal 的endpoint。 例如oss://oss-cn-beijing-internal.aliyuncs.com/xxx。 更多OSS经典网络域名信息，请参见OSS地域和访问域名。 建议数据文件存放的OSS地域与MaxCompute项目所在地域保持一致。如果跨地域，数据连通性可能存在问题。 不写endpoint。系统会默认补充一个当前Project所在地域的endpoint。 不推荐这种方式，如果文件存储跨地域，可能存在数据连通性问题。 Bucket名称：OSS存储空间名称。 例如oss://oss-cn-beijing-internal.aliyuncs.com/your_bucket/path/。 更多查看存储空间名称信息，请参见列举存储空间。 目录名称：OSS目录名称。目录后不需要指定文件名。 例如oss://oss-cn-beijing-internal.aliyuncs.com/oss-mc-test/Demo1/。 错误示例： -- 不支持HTTP连接。 http://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- 不支持HTTPS连接。 https://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- 连接地址错误。 oss://oss-cn-shanghai-internal.aliyuncs.com/Demo1 -- 不需要指定文件名。 oss://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/vehicle.csv 权限规范（RamRole）： 显示指定（推荐）：创建自定义角色并绑定权限策略，使用该自定义角色的ARN信息，创建详情请参见STS模式授权。 使用Default（不推荐）：使用aliyunodpsdefaultrole角色的ARN信息。

• WITH serdeproperties属性

  property_name	使用场景	property_value	默认值
  odps.properties.rolearn	使用STS模式授权时，请添加该属性。	指定RAM中Role（具有访问OSS权限）的ARN信息。您可以通过RAM控制台中的角色详情进行获取。示例如下acs:ram::xxxxxx:role/aliyunodpsdefaultrole。	当MaxCompute和OSS的Owner是同一个账号时： 若建表语句中不写odps.properties.rolearn，默认使用aliyunodpsdefaultrole角色的ARN信息。 若您想用自定义角色的ARN信息，需要提前创建自定义角色，创建详情请参见OSS的STS模式授权（方式二）。 当MaxCompute和OSS的Owner不是同一个账号时，您需要填写自定义角色的ARN信息，创建详情请参见OSS的STS模式授权（方式三）。

补全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>/...

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
...

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）。

通过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，不同取值时的实现原理如下：

导出文件管理

以动态分区形式写出大文件

-- 必须开启动态分区能力。
set odps.sql.reshuffle.dynamicpt=true; 	

-- 设置期望每个reducer消费的数据,假设希望每个文件是4G。
set odps.adaptive.shuffle.desired.partition.size=4096;	

• 通过外部表处理OSS数据时，报错Inline data exceeds the maximun allowed size，如何解决？

• 在MaxCompute上访问OSS外部表，编写UDF本地测试通过，上传后报错内存溢出，如何解决？

• 如何通过OSS外部表将多个小文件输出为一个文件？

• 读取OSS外部表时报错Couldn't connect to server，如何解决？

• 创建OSS外部表时报错Network is unreachable (connect failed) ，如何解决？

• 通过OSS分片上传功能将数据写入OSS场景下，旧数据被删除，但是新数据未写入，如何解决？

通过外部表处理OSS数据时，报错Inline data exceeds the maximun allowed size，如何解决？

• 问题现象

  处理OSS数据时，报错Inline data exceeds the maximum allowed size。

• 产生原因

  OSS Store对于每一个小文件有一个大小限制，如果超过3 GB则报错。

• 解决措施

  针对该问题，您可以通过调整以下两个属性进行处理。其原理是通过属性值调整执行计划，控制每个Reducer写入外部表OSS的数据大小，使得OSS Store文件不超过3 GB的限制。

  set odps.sql.mapper.split.size=256; #调整每个Mapper读取数据的大小，单位是MB。
  set odps.stage.reducer.num=100; #调整Reduce阶段的Worker数量。

在MaxCompute上访问OSS外部表，编写UDF本地测试通过，上传后报错内存溢出，如何解决？

• 问题现象

  在MaxCompute上访问OSS外部表，编写UDF本地测试通过，上传后返回如下报错。

  FAILED: ODPS-0123131:User defined function exception - Traceback:
  java.lang.OutOfMemoryError: Java heap space        

  设置如下参数后运行时间增加但依然报错。

  set odps.stage.mapper.mem = 2048; 
  set odps.stage.mapper.jvm.mem = 4096;       

• 产生原因

  外部表的对象文件太多，内存占用过大且未设置分区。

• 解决措施

  • 使用小数据量查询。

  • 将对象文件进行分区，以减少内存占用。

如何通过OSS外部表将多个小文件输出为一个文件？

通过Logview日志，查看SQL的执行计划中最后一个是Reducer还是Joiner。

• 如果是Reducer，则执行语句set odps.stage.reducer.num=1;

• 如果是Joiner，则执行语句set odps.stage.joiner.num=1;

读取OSS外部表时报错Couldn't connect to server，如何解决？

• 问题现象

  读取OSS外部表的数据时，报错ODPS-0123131:User defined function exception - common/io/oss/oss_client.cpp(95): OSSRequestException: req_id: , http status code: -998, error code: HttpIoError, message: Couldn't connect to server。

• 产生原因

  • 原因一：创建OSS外部表时，oss_location地址中的oss_endpoint使用了公网地址，未使用内网地址。

  • 原因二：创建OSS外部表时，oss_location地址中的oss_endpoint使用了其他Region的地址。

• 解决措施

  • 对于原因一：

    您需要核查OSS外部表的建表语句中oss_location参数的oss_endpoint是否为内网地址，如果是公网地址则需要修改为内网地址，具体参数信息请参见参数说明。

    例如，用户在印度尼西亚（雅加达）地域，使用了oss://oss-ap-southeast-5.aliyuncs.com/<bucket>/....地址创建外部表，应该改为对应的内网地址oss://oss-ap-southeast-5-internal.aliyuncs.com/<bucket>/....。

  • 对于原因二：

    您需要核查OSS外部表的建表语句中，oss_location参数的oss_endpoint是否为要访问的Region的Endpoint，更多OSS经典网络域名信息，请参见OSS地域和访问域名。

创建OSS外部表时报错Network is unreachable (connect failed) ，如何解决？

• 问题现象

  创建OSS外部表时，报错ODPS-0130071:[1,1] Semantic analysis exception - external table checking failure, error message: Cannot connect to the endpoint 'oss-cn-beijing.aliyuncs.com': Connect to bucket.oss-cn-beijing.aliyuncs.com:80 [bucket.oss-cn-beijing.aliyuncs.com] failed: Network is unreachable (connect failed)。

• 产生原因

  创建OSS外部表时，oss_location地址中的oss_endpoint使用了公网地址，未使用内网地址。

• 解决措施

  你需要核查OSS外部表的建表语句中，oss_location参数的oss_endpoint是否为内网地址。如果是公网地址，则需要修改为内网地址，具体参数信息请参见参数说明。

  例如，用户在华北2（北京）地域，使用了oss://oss-cn-beijing.aliyuncs.com/<bucket>/....地址创建外部表，应该改为对应的内网地址oss://oss-cn-beijing-internal.aliyuncs.com/<bucket>/....。

基于OSS外部表执行SQL作业时，运行慢，如何解决？

• OSS外部表中的GZ压缩文件读取慢

  • 问题现象

    用户创建了一个OSS外部表，数据源为OSS中的GZ压缩文件，大小为200 GB。在读取数据过程中执行缓慢。

  • 产生原因

    由于Map端执行计算的Mapper数量过少，所以SQL处理慢。

  • 解决措施

    • 对于结构化数据，您可以设置以下参数调整单个Mapper读取数据量的大小，加速SQL执行。

      set odps.sql.mapper.split.size=256; #调整每个Mapper读取Table数据的大小，单位是MB。       

    • 对于非结构化数据，您需要查看OSS外部表路径下的OSS文件是否只有1个。如果只有1个，由于压缩方式下的非结构化数据不支持拆分，所以只能生产1个Mapper，导致处理速度较慢。建议您在OSS对应的外部表路径下，将OSS大文件拆分为小文件，从而增加读取外部表生成的Mapper数量，提升读取速度。

• 使用SDK搜索MaxCompute外部表数据速度慢

  • 问题现象

    使用SDK搜索MaxCompute外部表数据速度慢。

  • 解决措施

    外部表仅支持全量搜索，所以较慢，建议您改用MaxCompute内部表。

通过OSS分片上传功能将数据写入OSS场景下，旧数据被删除，但是新数据未写入，如何解决？

• 问题现象

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

• 产生原因

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

• 解决措施

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

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