Presto 0.107 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中。

##表信息说明

表名	所在库	说明
Pdbo_table	Route_schema	Pdbo表信息
pdbo_route	Route_schema	Pdbo路由信息
Db_info	Route_schema	存储连接数据库用户名和密码

• Pdbo表信息：Pdbo_table

字段	字段含义	类型	说明
tableid	主键	bigint	自增
dbtype	数据库类型	Varchar	MYSQL SQLSERVER ORACLE
connectorid	Presto catalog连接标示符	varchar(50)	必须和presto中定义properties配置文件名称前缀一致。比如presto中定义mysql数据源为：coo.properties配置文件，那么此字段配置内容为：coo
presto_schema	Presto schema名称	varchar(50)	由存有表结构的数据库实例中的所有的database的名称来决定
presto_table	Presto 中使用的查询表名	varchar(50)	有存有表结构的数据库实例中的所有的table的名称来决定
calc_step_enable	开启动态步长计算标示	char(1)	默认为N
control_scan_concurrency_enabled	并发控制标示	char(1)	抓取表数据时，是否开启并发控制，默认为N，
scan_concurrency_count	int	当并发控制功能开启时，设置并发的数量。

• Pdbo路由信息：Pdbo_route

字段	字段含义	类型	说明
routeid	主键	bigint	自增
tableid	Pdbo_table主键	bigint	Pdbo_table主键
dbhost	数据库IP	varchar(25)	存储实际数据的数据库实例ip
dbport	数据库端口	varchar(10)	存储实际数据的数据库实例端口
connection_properties	连接参数	varchar(200)	JDBC连接参数
source_schema	数据库名称	varchar(50)	存储实际数据的数据库实例上的database名称
source_table	表名称	varchar(50)	存储实际数据的数据库实例上的table名称
splitfield	切分字段	varchar(50)	切分字段是为了使presto node分段读取表，配置内容为表字段名，暂时支持的字段类型为整形和日期型，最终会在查询数据的sql的where条件中添加类似于如下的过滤条件：splitfield > xx and splitfield <= xxxx。如果scannodenumber所配置的值大于1，切分字段必须配置。
fieldmaxvalue	切分字段最大值	bigint	如果设置了切分字段，必须设置切分字段最大值，且必须大于0
fieldminvalue	切分字段最小值	bigint	默认为0
remotelyaccessible	是否远程读	char(1)	是否远程读
presto_work_host	presto计算节点	varchar(25)	指定presto节点读取该表数据，如果设置了切分字段，那么所重构的split的HostAddress均为该presto 节点。
scannodenumber	读取数据的节点数量	int	读取数据的节点数量
uid	数据源信息关联字段	varchar(50)	数据源信息关联字段

• 数据源信息表：db_info

字段	字段含义	类型	说明
Id	主键	int	自增
dbhost	Mysql实例ip	VARCHAR	可以不配置
dbport	Mysql实例端口	VARCHAR	可以不配置
uid	与table_route关联字段	VARCHAR	与table_route关联字段
username	用户名	VARCHAR	用Base64加密存储
password	密码	VARCHAR	用Base64加密存储

##创建配置表

• 创建数据库：

Create database route_schema;

• 创建表结构：

CREATE TABLE `pdbo_table` (
 `tableid` bigint(20) NOT NULL AUTO_INCREMENT,
 `dbtype` enum('MYSQL','SQLSERVER','ORACLE') NOT NULL,
 `connectorid` varchar(50) DEFAULT NULL,
 `presto_schema` varchar(50) DEFAULT NULL,
 `presto_table` varchar(50) DEFAULT NULL,
 `calc_step_enable` char(1) NOT NULL DEFAULT 'N',
 `control_scan_concurrency_enabled` char(1) NOT NULL DEFAULT 'N',
 `scan_concurrency_count` int(11) DEFAULT NULL,
 PRIMARY KEY (`tableid`)
) ;


CREATE TABLE `pdbo_route` (
 `routeid` bigint(20) NOT NULL AUTO_INCREMENT,
 `tableid` bigint(20) NOT NULL,
 `dbhost` varchar(25) DEFAULT NULL,
 `dbport` varchar(10) DEFAULT NULL,
 `connection_properties` varchar(200) DEFAULT NULL,
 `source_schema` varchar(50) DEFAULT NULL,
 `source_table` varchar(50) DEFAULT NULL,
 `splitfield` varchar(50) DEFAULT NULL,
 `remotelyaccessible` char(1) NOT NULL DEFAULT 'Y',
 `presto_work_host` varchar(25) DEFAULT NULL,
 `scannodenumber` int(11) DEFAULT NULL,
 `uid` varchar(50) DEFAULT NULL,
 `fieldmaxvalue` bigint(20) DEFAULT NULL,
 `fieldminvalue` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`routeid`)
) ;

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`)
) ENGINE=MyISAM

##测试用例

INSERT INTO pdbo_table(dbtype,connectorid,presto_schema,presto_table) VALUES
('MYSQL','mysql','dbtest','test0001'), 
('MYSQL','mysql','dbtest','test0002'),  
('SQLSERVER','sqlserver','dbo','test0003'); 

INSERT INTO pdbo_route(tableid,dbhost,dbport,connection_properties,source_schema,source_table,splitfield,remotelyaccessible,presto_work_host,scannodenumber,fieldmaxvalue,fieldminvalue,uid) value
(1,'127.0.0.1','3358','?autoReconnect=true&maxReconnects=5&connectTimeout=10000','db_test','test01',null,'Y',null,0,0,0,2),
(1,'127.0.0.1','3358','?autoReconnect=true&maxReconnects=5&connectTimeout=10000','db_test','test02',null,'Y',null,0,0,0,2),
(2,'127.0.0.1','3306','?autoReconnect=true&maxReconnects=5&connectTimeout=10000','db_test_2','test001',null,'Y',null,0,0,0,null),
(2,'127.0.0.1','3306','?autoReconnect=true&maxReconnects=5&connectTimeout=10000','db_test_2','test002',null,'Y',null,0,0,0,null),
(2,'127.0.0.1','3306','?autoReconnect=true&maxReconnects=5&connectTimeout=10000','db_test_2','test003',null,'Y',null,0,0,0,null),
(3,'127.0.0.1','1433','/test','dbo','test','id','Y',null,20,1000000,1,null);

INSERT INTO DB_INFO(UID,USERNAME,PASSWORD) VALUES('2','dGVzdA==','dGVzdA==');

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

#功能说明

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

INSERT INTO pdbo_table(dbtype,connectorid,presto_schema,presto_table) VALUES
('MYSQL','mysql','dbtest','test0001'); 

INSERT INTO pdbo_route(tableid,dbhost,dbport,connection_properties,source_schema,source_table,splitfield,remotelyaccessible,presto_work_host,scannodenumber,fieldmaxvalue,fieldminvalue,uid) value
(1,'127.0.0.1','3358','?autoReconnect=true&maxReconnects=5&connectTimeout=10000','db_test','test01','id','Y',null,2,1000,1,null);

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

##动态步长计算 动态步长功能包含两个部分：日志收集和步长计算。其中步长计算在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

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