Maxcompute-TabStore(OTS)外部表总结

一、概述

外部表的功能，旨在提供除MaxCompute内部表以外的其他数据的处理能力。通过DDL语句在MaxCompute上创建一张外部表，建立MaxCompute表与外部数据源的关联，提供各种数据的接入和输出能力。创建好的外部表可以像普通的MaxCompute表一样使用，充分利用MaxCompute SQL的强大计算功能。目前外部表在专有云支持的技术组件有OSS、RDS、ADB、TabStore(OTS)，本文只介绍TabStore(OTS)外部表的功能和场景。

二、适用场景

（1）适用于MaxCompute和TabStore(OTS)之间的数据同步。

（2）适用于对TabStore(OTS)数据基于主键按照最左原则进行查询，不适合按照属性列（或者主键未遵循最左原则）以及关联查询，因为对外部表全表扫描非常耗时；

三、使用限制

（1）外部表的列定义个数最多为1200个。

（2）一次OTS请求写入属性列的个数不能超过1024。

（3）将MaxCompute中的数据写入OTS时一次不能超过4MB。

（4）通过外部表写入，单个节点支持2万/秒的写入速率。（目前，测试环境在8万/秒写入速率情况下写入失败，原因：测试集群太小，另加硬盘介质HDD支持的读取能力有限，导致后台compaction无法赶上写入的速度，服务器负载（CPU使用率）过高。）

四、验证结论

验证资源环境：TabStore(OTS)实例规格共2个worker机器，机型为 PN69，单台服务器配置具体如下表所示。

部件类型	参数
CPU	[Intel_Xeon_Platinum8160]  96逻辑核
内存	384GB
硬盘1	240GB[240G_sata_ssd]
硬盘2	3840GB[3840G_nvme_ssd]
硬盘3	8000GB[8T_sata_hdd]*12

 

（1）支持外部表写出数据到TabStore(OTS)。

本次验证结论：使用2台物理机的TabStore(OTS)实例，并对目标数据表进行128个预分区，通过外部表最大写入速率15万R/S，平均写入速率10万 R/S。

（2）支持根据主键按照最左匹配原则进行查询。

本次验证结论：对于7.8亿数据的TabStore(OTS)表，查询速率在30秒左右。

查询方式举例：比如数据表的主键为A、B、C、D四列，那么如下四种过滤方式符合最左匹配原则：

过滤字段	SQL
A、B、C、D	SELECT * FROM table_name WHERE A = ‘XX’ AND B=’XX’ AND C=’XX’ AND D=’XX’ ;
A、B、C	SELECT * FROM table_name WHERE A = ‘XX’ AND B=’XX’ AND C=’XX’ ;
A、B	SELECT * FROM table_name WHERE A = ‘XX’ AND B=’XX’ ;
A	SELECT * FROM table_name WHERE A = ‘XX’ ;

 

（3）除了上面根据主键按照最左匹配原则查询外，其他查询都非常缓慢，类似全表扫描。

本次验证结论：对于7.8亿数据的TabStore(OTS)表，查询超过20000秒。

查询方式举例：使用主键进行查询但是不符合最左匹配原则，例如以下查询方式：

过滤字段	SQL
B、C、D	SELECT * FROM table_name WHERE B=’XX’ AND C=’XX’ AND D=’XX’ ;
B、C	SELECT * FROM table_name WHERE B=’XX’ AND C=’XX’ ;
B、D	SELECT * FROM table_name WHERE B= ‘XX’ AND D=’XX’ ;
B	SELECT * FROM table_name WHERE B= ‘XX’ ;
C、D	SELECT * FROM table_name WHERE C= ‘XX’  AND D=‘XX’;
C	SELECT * FROM table_name WHERE C= ‘XX’ ;
D	SELECT * FROM table_name WHERE D= ‘XX’ ;

 

查询方式举例：使用属性列进行查询，例如：

过滤字段	SQL
E、F	SELECT * FROM table_name WHERE E=’XX’ AND F=’XX’ ;
F	SELECT * FROM table_name WHERE F=’XX’ ;

 

查询方式举例：统计TabStore(OTS)表的数据量，例如：

SQL

SELECT count(0)  FROM  table_name ;

 

（4）在TabStore(OTS)表上创建二级索引，通过二级索引的主键字段查询TabStore(OTS)表的所有主键，然后与TabStore(OTS)外部表根据主键进行关联查询，查询性能较差，类似全表扫描后再做关联计算，不推荐使用。

（5）如果需要对TabStore(OTS)表中的数据做统计分析，建议将TabStore(OTS)的数据导入MaxCompute内部表再进行。

本次验证结论：1). 通过外部表统计TabStore(OTS)表（7.8亿数据）的数据量用时近6个小时，将TabStore(OTS)表的数据写入MaxCompute内部表用时2.3小时，查询数据量仅仅用时49秒。

                        2). Maxcompute内部表容量为110GB，同步到TabStore(OTS)后盘古数据大小为268GB，所以将MaxCompute数据同步到TabStore(OTS)后数据会膨胀2~3倍。

五、使用实践

1）外部表读取TabStore(OTS)数据时的并发度

TabStore(OTS)是一个分布式KV数据存储系统，每个数据表都可能存储在多个后端server上，并且根据分区键进行分区，具体存储上的分区策略由TabStore(OTS)决定。目前通过MaxCompute读取TabStore(OTS)数据，默认的并发度将与TabStore(OTS)后端的分区数目相同。唯一的例外是，在采用INTEGER作为分区键，且TabStore(OTS)后端的分区数目大于1时，MaxCompute会自动对并发度再做调整，在更高的并发度上读取数据。

2）外部表写出到TabStore(OTS)数据时的并发度

在将MaxCompute内部数据写出到TabStore(OTS)时，并发度由MaxCompute根据数据量自动进行控制。当然用户也可以手动调节SQL执行过程中的mapper/reducer数目来调整并发度。 但是绝大部分情况下， MaxCompute本身适配的并发度都是比较合理的，一般不建议自己手工设置mapper/reducer数目。另外，在一些场景上，MaxCompute计算服务与TabStore(OTS)存储服务之间会存在着需要适配的情况。一般来说MaxCompute可以调度起计算节点都比较充裕，而大量计算节点同时往TabStore(OTS)写出数据时可能会打满网络。

3）对热点数据做多次计算时建议将数据写入内部表进行

如果需要对一份数据做多次计算，相比每次从TabStore(OTS)去远程读数据，更高效的方法是先一次性把需要的数据导入到MaxCompute成为一个内部表，再进行数据计算与分析。

4）对于已经排序的MaxCompute表内数据建议将数据打散后写入，避免压力集中在一个分区影响写入速率

如果MaxCompute表内数据本身有一定的顺序，例如已经按照Primary Key做过一次排序，则在写入到OTS表时，会导致压力集中在一个OTS分区上面，无法充分利用分布式写入的特点。因此，当出现这种情况时，建议通过distribute by rand()先将数据打散，示例如下：

INSERT OVERWRITE TABLE ots_table_external

SELECT A, B, C, D, E

FROM (SELECT * FROM internal_table_name DISTRIBUTE BY rand()) t;