Presto 0.100 JD PDBO使用手册

#概述 Presto处理MySQL数据的模式为每个stage只有一个presto work node处理一个task，也就是说每个Presto work node只处理一个MySQL表，那么导致的结果就是当表的数据量巨大时，Presto效率低下甚至有可能导致查询失败。 PDBO (Presto for Database Optimizer)是专门针对Presto连接RDBMS数据源开发的功能，可以对多个数据源进行交互查询，主要包含以下几个功能点：

• 支持多个节点读取单表，并能持续智能优化查询性能
• 支持MySQL/SQL Server/Oracle数据源
• 支持where条件 push down到RDBMS
• 支持分库分表
• 支持表数据缓存

其中多个节点读取单表包含了动态步长的计算功能，动态步长优化是针对大表的读取的一种处理模式，是为了消除对表进行切分时各个数据段处理的数据稀疏不均导致数据倾斜的问题。动态步长功能需设置PDBO规则配置表的相关信息来配合使用。

#PDBO配置 PDBO功能需要配置相关规则来开启相关功能，相关配置信息存储在MYSQL中。

##创建配置表

• 创建数据库：

Create database route_schema;

• 创建表结构：

CREATE TABLE `table_route` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `connectionurl` varchar(200) DEFAULT NULL,
 `schemaname` varchar(50) DEFAULT NULL,
 `tablename` varchar(50) DEFAULT NULL,
 `autoincrementfield` varchar(50) DEFAULT NULL,
 `remotelyaccessible` varchar(5) DEFAULT NULL,
 `host` varchar(100) DEFAULT NULL,
 `basecatalog` varchar(50) DEFAULT NULL,
 `baseschema` varchar(50) DEFAULT NULL,
 `basetable` varchar(50) DEFAULT NULL,
 `scannodenumber` int(11) DEFAULT NULL,
 `uid` varchar(50) DEFAULT NULL,
 `fieldmaxvalue` bigint(20) DEFAULT NULL,
 `fieldminvalue` bigint(20) DEFAULT NULL,
 `pdboenable` char(1) NOT NULL DEFAULT 'N',
  PRIMARY KEY (`id`)
) ;


CREATE TABLE `db_info` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `dbhost` varchar(50) DEFAULT NULL,
 `dbport` varchar(10) DEFAULT NULL,
 `uid` varchar(50) DEFAULT NULL,
 `username` varchar(30) DEFAULT NULL,
 `password` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`id`)
);


CREATE TABLE `pdbo_log` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 `connectorid` varchar(50) DEFAULT NULL,
 `schemaname` varchar(50) DEFAULT NULL,
 `tablename` varchar(50) DEFAULT NULL,
 `rows` bigint DEFAULT NULL,
 `beginindex` bigint DEFAULT NULL,
 `endindex` bigint DEFAULT NULL,
 `recordflag` enum('new','finish','runhistory','calchistory') NOT NULL COMMENT 'new:新Log;finish:计算完成的结果;runhistory:运行的历史数据;calchistory:计算结果历史数据',
 `timestamp` bigint DEFAULT NULL,
 `scannodes` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

##表信息说明

表名	所在库	说明
table_route	route_schema	PDBO规则配置表
db_info	route_schema	存储连接数据库用户名和密码
pdbo_log	route_schema	Pdbo日志表

• PDBO规则配置表：table_route

字段	字段含义	类型	说明
id	主键	bigint	自增
connectionurl	Jdbc连接字符串	varchar(200)	数据源的JDBC连接字符串
schemaname	分表后的schema	varchar(50)	存储实际数据的数据库实例上的database名字
tablename	分表后的tablename	varchar(50)	存储实际数据的数据库实例上的table名字
scannodenumber	读取表的节点数量	int	默认为1
autoincrementfield	将表分段读取的切片字段	varchar(50)	该字段是为了使presto节点对表进行切分，配置内容为表字段名，暂时支持的字段类型为整形或日期型，最终会在查询数据的sql的where条件中添加类似于如下的过滤条件：autoincrementfield > xx and autoincrementfield <= xxxx。如果为SQL SERVER和Oracle数据库，该字段必须配置；mysql数据库可以不配置，若不配置将使用mysql的limit语法将表进行分段读取。
basecatalog	Presto中的表catalog	varchar(50)	必须和presto中定义properties配置文件名称前缀一致。比如presto中定义mysql数据源为：test.properties配置文件，那么此字段配置内容为：test
baseschema	Presto表所在的schema名称	varchar(50)	由存有表结构的数据库实例中的所有的database的名字来决定
basetable	Presto中的表名	varchar(50)	由存有表结构的数据库实例中的所有的table的名字来决定
host	Presto计算节点的ip地址	varchar(100)	格式：ip:port，指定使用哪个Presto Worker去读取数据。
remotelyaccessible	是否允许远程读取数据	varchar(5)	默认为Y，当为Y时 ，则Presto会随机分配一个节点读取数据；当为N时，则强制指定上述host所配置的presto节点读取该数据。
fieldmaxvalue	分片字段最大值	bigint	分片字段的最大值，默认为0
fieldminvalue	分片字段最小值	bigint	分片字段的最小值，默认为0
pdboenable	是否开启动态步长计算	char(1)	默认为N，N为不开启，Y为开启

• 数据源信息表：db_info

字段	字段含义	类型	说明
Id	主键	int
dbhost	数据库实例ip		暂时未使用，可以不配置
dbport	数据库实例端口		暂时未使用，可以不配置
uid	与table_route关联字段
username	用户名
password	密码

• PDBO日志表：pdbo_log 该表的数据自动生成，无需配置

字段	字段含义	类型	说明
id	主键	bigint
connectorid		varchar(50)	同table_route.basecatalog字段对应
schemaname			同table_route.baseschema字段对应
tablename			同table_route.basetable字段对应
rows	数据行数		每个split读取的行数
beginindex			每个split数据范围的起始位置
endindex			每个split数据范围的结束位置
recordflag	记录标示	enum	new:split运行产生的新Log; finish:计算完成的结果; runhistory:运行的历史数据; calchistory:计算结果历史数据
timestamp	时间戳		每次插入新的记录或者由动态步长算法算法后的记录时的时间戳
scannodes	读取数据的节点个数		读取当前表数据的节点个数

##测试用例

INSERT INTO TABLE_ROUTE(CONNECTIONURL,SCHEMANAME,TABLENAME,BASECATALOG,BASESCHEMA,BASETABLE,HOST,REMOTELYACCESSIBLE,SCANNODENUMBER,UID, FIELDMAXVALUE, FIELDMINVALUE, PDBOENABLE) VALUES ('jdbc:mysql://127.0.0.2:3306/','testdb','test_table_0','test','test_db','testtable','127.0.0.2','N',2,'uid001',1000,1,’Y’);

INSERT INTO DB_INFO(UID,USERNAME,PASSWORD) VALUES('uid001','username','password');

#Presto配置

PDBO新增的Presto参数主要包含两大部分的配置参数：PDBO规则信息所在的MYSQL实例的连接信息和缓存相关配置，目前PDBO适用于MySQL/SQL Server/Oracle数据源。

##PDBO规则信息相关参数 以下列举的参数配置在数据源的配置文件中，在Coordinator和Worker都需要进行配置。

参数名称	说明	默认值
jdbc-sub-table-connection-url	PDBO规则信息所在的MySQL数据库实例地址
jdbc-sub-table-connection-user	PDBO规则信息所在的MySQL数据库连接用户
jdbc-sub-table-connection-password	PDBO规则信息所在的MySQL数据库连接密码
jdbc-reload-subtable-interval	刷新PDBO规则信息的时间间隔	5min
jdbc-sub-table-allocator-enable	PDBO规则信息是否加载标识：在Coordinator的配置文件中需设置为true，而worker的配置文件上需设置为false。	false
jdbc-sub-table-enable	是否启用PDBO功能标识	false

##缓存功能相关的参数 以下列举的参数配置在数据源的配置文件中，在Coordinator和Worker都需要进行配置。

参数名称	说明
jdbc-cache-enable	是否开启缓存功能，默认为false
jdbc-cache-table-config	缓存表的配置信息，包括表名和表字段: 格式为json，样例：[{"test_a":["name","id","age"]},{"test_b":["deptno","deptname"]},{"test_c":"empno","deptno"]}]
jdbc-cache-table-clause	缓存表的默认过滤条件，参数配置内包括表和对应的过滤条件，格式为json，样例：{"test_a":"ts >= '2015-04-01 00:00:00'","test_b":"id >= '1000'"}
jdbc-cache-refresh-interval	缓存刷新时间，默认5分钟
jdbc-cache-expire-interval	缓存过期时间，默认5分钟

##开启动态步长功能相关参数 以下列举的参数仅在Coordinator上的config.properties中进行配置

参数名称	说明	默认值
pdbo-execute-enable	功能开启标识	false
pdbo-refresh-interval	计算间隔时长	3min
pdbo-calc-threads	计算线程个数	4
pdbo-connection-url	pdbo_log表所在数据库实例地址	NA
pdbo-connection-user	pdbo_log表所在数据库连接用户名	NA
pdbo-connection-password	pdbo_log表所在数据库连接密码	NA
pdbo-clean-history-interval	清理历史数据间隔时长	6hours

##MySQL Connector开启PDBO功能配置样例 新建test.properties Connectors数据源配置文件，其内容为：

connector.name=mysql
connection-url=jdbc:mysql://127.0.0.1:3306
connection-user=root
connection-password=root
#pdbo configuration
jdbc-reload-subtable-interval=5m
jdbc-sub-table-connection-url=jdbc:mysql://127.0.0.1:3306
jdbc-sub-table-connection-user=user
jdbc-sub-table-connection-password=password
jdbc-sub-table-allocator-enable=true
jdbc-sub-table-enable=true
#jdbc cache configuration
jdbc-cache-enable=true
jdbc-cache-table-config=[{"test_a":["name","id","age"]},{"test_b":["deptno","deptname"]},{"test_c":["empno","deptno"]}]
jdbc-cache-table-clause={"test_a":"ts >= '2015-04-01 00:00:00'","test_b":"id >= '1000'"}
jdbc-cache-refresh-interval=5m
jdbc-cache-expire-interval=5m

对以上示例配置文件中的配置参数说明如下： connection-url：在分库分表的配置中该参数指的是存储逻辑库表schema信息的数据库实例url（这里不存储实际的数据，只存储逻辑库表的shema信息）

#功能说明

##多个节点读取一个表 在TABLE_ROUTE中增加一条记录，需要指定读取的节点个数和切分字段。例如：

INSERT INTO TABLE_ROUTE(CONNECTIONURL,SCHEMANAME,TABLENAME,BASECATALOG,BASESCHEMA,BASETABLE,HOST,REMOTELYACCESSIBLE,SCANNODENUMBER, AUTOINCREMENTFIELD,UID) VALUES ('jdbc:mysql://127.0.0.2:3306/','testdb','test_table','test','test_db','testtable','127.0.0.2','N',2,’id’,'uid001');
INSERT INTO DB_INFO(UID,USERNAME,PASSWORD) VALUES('uid001','username','password');

##分库分表 某些特定业务在使用MySQL分库分表时，会按照一定的业务规则将数据写入到指定表，但PDBO的分库分表功能不需要知道这些配置，依次将分表信息配置到TABLE_ROUTE中即可，并且在编写SQL查询时，各分表表名对外完全透明，只需要按照正常的业务逻辑写SQL即可。 配置样例：

INSERT INTO TABLE_ROUTE(CONNECTIONURL,SCHEMANAME,TABLENAME,BASECATALOG,BASESCHEMA,BASETABLE,HOST,REMOTELYACCESSIBLE,SCANNODENUMBER, AUTOINCREMENTFIELD,UID) VALUES ('jdbc:mysql://127.0.0.2:3306/','testdb_0','test_table_0','test','test_db','testtable','127.0.0.2','N',1,null,'uid001'),
('jdbc:mysql://127.0.0.2:3306/','testdb_1','test_table_1','test','test_db','testtable','127.0.0.2','N',1,null,'uid001'),
('jdbc:mysql://127.0.0.2:3306/','testdb_2','test_table_3','test','test_db','testtable','127.0.0.2','N',1,null,'uid001');
INSERT INTO DB_INFO(UID,USERNAME,PASSWORD) VALUES('uid001','username','password');

##动态步长计算 动态步长功能包含两个部分：日志收集和步长计算。其中步长计算在coordinator上进行的因此首先需要在config.properties中打开开启标示：

pdbo-execute-enable=true

然后设置route_schema库的连接信息：

pdbo-connection-url=jdbc:mysql://127.0.0.1:3306
pdbo-connection-user=username
pdbo-connection-password=password

日志收集开启只需要在table_route.pdboenable设置为Y即可