presto 查询mysql_Presto连接MySQL

准备两台机器，机器名为 bd1 和 bd2，，并且在这两个节点上安装 hadoop，hive，并且准备一个 mysql 数据库。

bd1 - coordinator节点

bd2 - worker节点

安装 presto server

wget -c https://repo1.maven.org/maven2/com/facebook/presto/presto-server/0.211/presto-server-0.211.tar.gz

tar zxvf presto-server-0.211.tar.gz

cd /opt/presto-server-0.211

mkdir etc

coordinator 节点配置

etc/node.properties

node.environment=production

node.id=ffffffff-ffff-ffff-ffff-fffffffffff1

node.data-dir=/opt/presto-server-0.211/data

etc/config.properties

coordinator=true

node-scheduler.include-coordinator=false

http-server.http.port=8080

query.max-memory=8GB

query.max-memory-per-node=1GB

query.max-total-memory-per-node=2GB

discovery-server.enabled=true

discovery.uri=http://bd1:8080

etc/jvm.config

-server

-Xmx8G

-XX:+UseG1GC

-XX:G1HeapRegionSize=32M

-XX:+UseGCOverheadLimit

-XX:+ExplicitGCInvokesConcurrent

-XX:+HeapDumpOnOutOfMemoryError

-XX:+ExitOnOutOfMemoryError

worker 节点配置

etc/node.properties

node.environment=production

node.id=ffffffff-ffff-ffff-ffff-fffffffffff2

node.data-dir=/opt/presto-server-0.211/data

etc/config.properties

coordinator=false

http-server.http.port=8080

query.max-memory=8GB

query.max-memory-per-node=1GB

query.max-total-memory-per-node=2GB

discovery.uri=http://bd1:8080

etc/jvm.config

-server

-Xmx8G

-XX:+UseG1GC

-XX:G1HeapRegionSize=32M

-XX:+UseGCOverheadLimit

-XX:+ExplicitGCInvokesConcurrent

-XX:+HeapDumpOnOutOfMemoryError

-XX:+ExitOnOutOfMemoryError

运行Presto

分别在两个节点上运行下面的启动命令

# 启动 Presto

bin/launcher start

# 停止 Presto

bin/launcher stop

# 前台运行 Presto，建议刚开始的时候使用这种方式，如果配置有错误，可以立刻在console上看到错误信息，方便调试。

bin/launcher run

服务启动后可以通过下面的地址来查看服务状态

http://:8080/ui/

设置 MySQL Connector

创建数据表

由于当前版本的 MySQL Connector 不支持创建数据表的操作，因此我们需要先通过MySQL的终端来创建数据表。

$ mysql -u root -p

MariaDB [(none)]> use test;

MariaDB [test]> create table user(id int not null, username varchar(32) not null, password varchar(32) not null);

MariaDB [test]> insert into user values(1,'user1','password1');

MariaDB [test]> insert into user values(2,'user2','password2');

MariaDB [test]> insert into user values(3,'user3','password3');

MariaDB [test]> select * from user;

+----+----------+-----------+

| id | username | password |

+----+----------+-----------+

| 1 | user1 | password1 |

| 2 | user2 | password2 |

| 3 | user3 | password3 |

+----+----------+-----------+

MySQL Connector 配置

在etc目录下创建catalog目录，然后创建 mysql.properties 文件,编辑保存后将此文件复制的集群中的其它节点相同目录下。

etc/catalog/mysql.properties

connector.name=mysql

connection-url=jdbc:mysql://bd1:3306

connection-user=root

connection-password=

获取 Presto 命令行工具

# 下载

wget -c https://repo1.maven.org/maven2/com/facebook/presto/presto-cli/0.211/presto-cli-0.211-executable.jar

# 这个jar文件是一个自运行的jar包，因此为了使用方便，我们可以将其改名为presto

mv presto-cli-0.211-executable.jar presto

通过 Presto 操作 MySQL 数据库

./presto --server bd1:8080 --catalog mysql --schema test

presto:test> show schemas from mysql;

Schema

--------------------

information_schema

performance_schema

test

(4 rows)

Query 20181024_011707_00040_szyec, FINISHED, 2 nodes

Splits: 19 total, 19 done (100.00%)

0:00 [4 rows, 64B] [9 rows/s, 147B/s]

presto:test> show tables from mysql.test;

Table

-------

user

(1 row)

Query 20181024_011716_00041_szyec, FINISHED, 2 nodes

Splits: 19 total, 19 done (100.00%)

0:01 [1 rows, 18B] [0 rows/s, 16B/s]

presto:test> select * from mysql.test.user;

id | username | password

----+----------+-----------

1 | user1 | password1

2 | user2 | password2

3 | user3 | password3

(3 rows)

Query 20181024_011724_00042_szyec, FINISHED, 1 node

Splits: 17 total, 17 done (100.00%)

0:00 [3 rows, 0B] [7 rows/s, 0B/s]

presto:test> insert into mysql.test.user values(4,'user4','password4');

INSERT: 1 row

Query 20181024_011938_00046_szyec, FINISHED, 2 nodes

Splits: 35 total, 35 done (100.00%)

0:03 [0 rows, 0B] [0 rows/s, 0B/s]

presto:test> select * from mysql.test.user;

id | username | password

----+----------+-----------

1 | user1 | password1

2 | user2 | password2

3 | user3 | password3

4 | user4 | password4

(4 rows)

Query 20181024_011943_00047_szyec, FINISHED, 1 node

Splits: 17 total, 17 done (100.00%)

0:01 [4 rows, 0B] [6 rows/s, 0B/s]