(五)Presto Api

一、说明

1、参考官网文档

2、本文主要阐述presto连接hive、mysql、kafka的相关api

二、启动相关环境

1、启动Hive相关

(1)启动hadoop

(2)启动mydql

(3)启动hive的metaStore

2、启动mydql

3、启动kafka相关

(1)启动zookeeper

(2)启动kafka

4、启动presto

端口号为8099，主节点为master

三、presto控制台输出

1、presto连接Hive

[root@master bin]# ./presto --server master:8099 --catalog=hive
presto> show schemas;
       Schema       
--------------------
 db_hive            
 db_hive_test       
 default            
 information_schema 
(4 rows)

Query 20201119_013118_00001_mz5sf, FINISHED, 3 nodes
Splits: 36 total, 36 done (100.00%)
0:02 [4 rows, 64B] [1 rows/s, 27B/s]

presto:db_hive> use db_hive_test;
USE
presto:db_hive_test> show tables;
   Table   
-----------
 src       
 student   
 student01 
 student02 
 teacher   
 teacher01 
 teacher02 
(7 rows)

Query 20201119_013209_00007_mz5sf, FINISHED, 3 nodes
Splits: 36 total, 36 done (100.00%)
0:01 [7 rows, 207B] [10 rows/s, 306B/s]

presto:db_hive_test> select * from student;
 id |   name   
----+----------
  1 | xiaohong 
  2 | xiaolan  
(2 rows)

Query 20201119_013216_00009_mz5sf, FINISHED, 2 nodes
Splits: 17 total, 17 done (100.00%)
0:03 [2 rows, 25B] [0 rows/s, 8B/s]

presto:db_hive_test>

2、presto连接mysql

[root@master bin]# ./presto --server master:8099 --catalog=mysql
presto> show schemas;
       Schema       
--------------------
 hive               
 information_schema 
 ke                 
 performance_schema 
 test               
(5 rows)

Query 20201119_023533_00021_mz5sf, FINISHED, 3 nodes
Splits: 36 total, 36 done (100.00%)
0:00 [5 rows, 71B] [14 rows/s, 204B/s]

presto> use test;
USE
presto:test> show tables;
       Table       
-------------------
 walk_info         
 walk_info_test    
 walk_info_tmp     
(9 rows)

Query 20201119_023542_00023_mz5sf, FINISHED, 3 nodes
Splits: 36 total, 36 done (100.00%)
0:00 [9 rows, 227B] [25 rows/s, 631B/s]

presto:test> seelct * from walk_info;
Query 20201119_023552_00024_mz5sf failed: line 1:1: mismatched input 'seelct'. Expecting: 'ALTER', 'ANALYZE', 'CALL', 'COMMIT', 'CREATE', 'DEALLOCATE', 'DELETE', 'DESC', 'DESCRIBE', 'DROP', 'EXECUTE', 'EXPLAIN', 'GRANT', 'INSERT', 'PREPARE', 'RESET', 'REVOKE', 'ROLLBACK', 'SET', 'SHOW', 'START', 'USE', <query>
seelct * from walk_info

presto:test> select * from walk_info;
   user    |      counttime      | walkplace  | newwalknum 
-----------+---------------------+------------+------------
 zhangSan  | 2020-07-02 13:44:43 | 操场西南门 |          1 
 liSi      | 2020-07-02 13:44:45 | 操场西南门 |          3 
 wangWu    | 2020-07-02 13:44:47 | 操场东北门 |          3 
 xiaoQiang | 2020-07-02 13:44:49 | 操场西北门 |          2 
 zhangSan  | 2020-07-02 13:44:51 | 操场西门   |          3 
 liSi      | 2020-07-02 13:44:53 | 操场东南门 |          1 
 wangWu    | 2020-07-02 13:44:55 | 操场东南门 |          3 
 xiaoQiang | 2020-07-02 13:44:57 | 操场西门   |          1 
 zhangSan  | 2020-07-02 13:44:59 | 操场北门   |          1 
(9 rows)

Query 20201119_023557_00025_mz5sf, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:00 [9 rows, 0B] [54 rows/s, 0B/s]

presto:test>

3、presto连接kafka

[root@master bin]# ./presto --server master:8099 --catalog=kafka
presto> show schemas;
       Schema       
--------------------
 information_schema 
 mydb01             
 mydb02             
(3 rows)

Query 20201119_020222_00014_mz5sf, FINISHED, 3 nodes
Splits: 36 total, 36 done (100.00%)
0:00 [3 rows, 45B] [8 rows/s, 123B/s]

presto> use mydb01;
USE
presto:mydb01> show tables;
      Table      
-----------------
 mytest          
 topic_walkcount 
(2 rows)

Query 20201119_020233_00016_mz5sf, FINISHED, 3 nodes
Splits: 36 total, 36 done (100.00%)
0:00 [2 rows, 53B] [7 rows/s, 187B/s]

presto:mydb01> select * from mytest;
 _partition_id | _partition_offset | _message_corrupt | _message | _message_length | _key_corrupt | _key | _key_length |  _timestamp   
---------------+-------------------+------------------+----------+-----------------+--------------+------+-------------+---------------
             0 |                 0 | false            | test   |               8 | false        | NULL |           0 | 1605667188718 
             0 |                 1 | false            | test00   |               6 | false        | NULL |           0 | 1605667192709 
             0 |                 2 | false            | test55   |               6 | false        | NULL |           0 | 1605667195083 
(3 rows)

Query 20201119_020251_00017_mz5sf, FINISHED, 2 nodes
Splits: 17 total, 17 done (100.00%)
0:01 [3 rows, 20B] [3 rows/s, 20B/s]

presto:mydb01>

四、相关Api

环境：

<dependencies>
    <dependency>
        <groupId>com.facebook.presto</groupId>
        <artifactId>presto-jdbc</artifactId>
        <version>0.243.2</version>
    </dependency>
</dependencies>

1、presto连接Hive的Api

package com.learn.presto;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class PrestoHive {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try{
            //加载Driver类
            Class.forName("com.facebook.presto.jdbc.PrestoDriver");
            //获得连接，3个参数分别是url,presto的用户名和密码
            connection = DriverManager.getConnection("jdbc:presto://master:8099/hive/db_hive_test", "presto", null);
            //通过连接获得Statement
            statement = connection.createStatement();
            //运行sql获得返回值
            resultSet = statement.executeQuery("select * from student");
            //遍历
            while (resultSet.next()){
                System.out.println(resultSet.getInt("id")+"\t"+resultSet.getString("name"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

程序输出：

1	xiaohong
2	xiaolan

Process finished with exit code 0

2、presto连接mysql的Api

package com.learn.presto;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class PrestoMysql {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try{
            //加载Driver类
            Class.forName("com.facebook.presto.jdbc.PrestoDriver");

            String url = "jdbc:presto://master:8099/mysql/test";
            Properties properties = new Properties();
            properties.setProperty("user", "presto");
            properties.setProperty("password", "");
            //properties.setProperty("SSL", "true");//如果没有设置密码，要注释掉此行；
            connection = DriverManager.getConnection(url, properties);
            //通过连接获得Statement
            statement = connection.createStatement();
            //运行sql获得返回值
            resultSet = statement.executeQuery("select * from walk_info");
            //遍历
            while (resultSet.next()){
                System.out.println(resultSet.getString("user")+"\t"+resultSet.getString("counttime")+"\t"+resultSet.getString("walkplace")+"\t"+resultSet.getString("newwalknum"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

程序输出： 

zhangSan	2020-07-02 13:44:43	操场西南门	1
liSi	2020-07-02 13:44:45	操场西南门	3
wangWu	2020-07-02 13:44:47	操场东北门	3
xiaoQiang	2020-07-02 13:44:49	操场西北门	2
zhangSan	2020-07-02 13:44:51	操场西门	3
liSi	2020-07-02 13:44:53	操场东南门	1
wangWu	2020-07-02 13:44:55	操场东南门	3
xiaoQiang	2020-07-02 13:44:57	操场西门	1
zhangSan	2020-07-02 13:44:59	操场北门	1

Process finished with exit code 0

3、presto连接kafka的Api

package com.learn.presto;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class PrestoKafka {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try{
            //加载Driver类
            Class.forName("com.facebook.presto.jdbc.PrestoDriver");
            String url = "jdbc:presto://master:8099/kafka/mydb01?user=test";
            connection = DriverManager.getConnection(url);
            //通过连接获得Statement
            statement = connection.createStatement();
            //运行sql获得返回值
            resultSet = statement.executeQuery("select * from mytest");
            //遍历
            while (resultSet.next()){
                System.out.println(resultSet.getString("_message")+"\t"+resultSet.getString("_message_length"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            try {
                resultSet.close();
                statement.close();
                connection.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }
}

程序输出： 

test	8
test00	6
test55	6

Process finished with exit code 0