学习使用prestodb

Presto是一个开源的分布式SQL查询引擎，适用于交互式分析查询，数据量支持GB到PB字节。
官方网站:prestodb.io
中文网站:prestodb-china

环境准备

1. 三台虚拟机bigdata1、bigdata2、bigdata3

2. 操作系统、JDK1.8、Python【不要使用python3】

   $ cat /etc/issue
   CentOS release 6.8 (Final)
   $ java -version
   java version "1.8.0_131"
   Java(TM) SE Runtime Environment (build 1.8.0_131-b11)
   Java HotSpot(TM) 64-Bit Server VM (build 25.131-b11, mixed mode)
   $ python --version
   Python 2.6.6

3. hadoop-2.6.5、apache-hive-2.1.1-bin

presto准备

presto-server-0.179.tar.gz

prestodb docs

下载解压和配置

下载解压

1. 每台机器分别下载并解压presto-server-0.179.tar.gz到presto-server-0.179目录
2. 分别在presto-server-0.179目录下新建etc目录
3. 其他以下面为准

配置catlog-hive(datasource)

1. 第一台机器，作为coordinator

   $ ls etc/
   catalog  config.properties  jvm.config  log.properties  node.properties
   
   $ vi etc/config.properties
   coordinator=true
   datasources=hive
   node-scheduler.include-coordinator=false
   http-server.http.port=8008
   query.max-memory=50GB
   query.max-memory-per-node=1GB
   discovery-server.enabled=true
   discovery.uri=http://bigdata1:8008
   
   $ vi etc/jvm.config
   -server
   -Xmx16G
   -XX:+UseG1GC
   -XX:G1HeapRegionSize=32M
   -XX:+UseGCOverheadLimit
   -XX:+ExplicitGCInvokesConcurrent
   -XX:+HeapDumpOnOutOfMemoryError
   -XX:+ExitOnOutOfMemoryError
   
   $ vi etc/log.properties
   com.facebook.presto=INFO
   
   $ vi etc/node.properties
   node.environment=production
   node.id=presto-1
   node.data-dir=/work/presto/data
   
   $ ls etc/catalog/
   hive.properties
   
   $ vi etc/catalog/hive.properties
   connector.name=hive-hadoop2
   hive.metastore.uri=thrift://bigdata1:9083

2. 第二、三台机器，作为worker，注意etc/node.properties在两台机器中分别为node.id=presto-2、node.id=presto-3

   $ ls etc/
   catalog config.properties  jvm.config  log.properties  node.properties
   
   $ vi etc/config.properties
   coordinator=false
   http-server.http.port=8008
   query.max-memory=2GB
   query.max-memory-per-node=1GB
   discovery.uri=http://bigdata1:8008
   
   $ vi etc/jvm.config
   -server
   -Xmx16G
   -XX:+UseG1GC
   -XX:G1HeapRegionSize=32M
   -XX:+UseGCOverheadLimit
   -XX:+ExplicitGCInvokesConcurrent
   -XX:+HeapDumpOnOutOfMemoryError
   -XX:+ExitOnOutOfMemoryError
   
   $ vi etc/log.properties
   com.facebook.presto=INFO
   
   $ vi etc/node.properties
   node.environment=production
   node.id=presto-2
   node.data-dir=/work/presto/data
   
   $ ls etc/catalog/
   hive.properties
   
   $ vi etc/catalog/hive.properties
   connector.name=hive-hadoop2
   hive.metastore.uri=thrift://bigdata1:9083

启动和查看

1. 每台机器均在presto-server-0.179目录下执行下面命令即可

   ./bin/launcher start

   如果想查看控制台，则以如下命令启动，停止则用./bin/launcher stop

   ./bin/launcher run
   
   ......
   INFO    main    com.facebook.presto.server.PrestoServer ======== SERVER STARTED ========

   jps命令查看

   $ jps
   3238 NameNode
   4487 PrestoServer
   5016 Jps
   3416 SecondaryNameNode
   3769 RunJar
   3628 RunJar
   
   $ jps
   2625 Jps
   2228 PrestoServer
   2141 DataNode

2. 浏览器访问http://192.168.99.100:8008/ (192.168.99.100为bigdata1的IP地址)

   

官网命令行接口实例

1. 下载presto-cli-0.179-executable.jar

2. 可以重命名为presto

   $ mv presto-cli-0.179-executable.jar presto

3. 执行命令测试，并观察UI界面Finished Queries

   $ ./presto --server bigdata1:8008 --catalog hive --schema default
   presto:default> show tables;
             Table
   --------------------------
    kylin_cal_dt
    kylin_category_groupings
    kylin_country
    kylin_sales
   (4 rows)
   
   Query 20170621_123635_00002_du8wp, FINISHED, 2 nodes
   Splits: 18 total, 18 done (100.00%)
   0:02 [4 rows, 128B] [2 rows/s, 68B/s]
   
   presto:default>

   

4. 查询

   presto:default> select * from kylin_country limit 10;
    country |  latitude  | longitude  |         name
   ---------+------------+------------+----------------------
    AD      |  42.546245 |   1.601554 | Andorra
    AE      |  23.424076 |  53.847818 | United Arab Emirates
    AF      |   33.93911 |  67.709953 | Afghanistan
    AG      |  17.060816 | -61.796428 | Antigua and Barbuda
    AI      |  18.220554 | -63.068615 | Anguilla
    AL      |  41.153332 |  20.168331 | Albania
    AM      |  40.069099 |  45.038189 | Armenia
    AN      |  12.226079 | -69.060087 | Netherlands Antilles
    AO      | -11.202692 |  17.873887 | Angola
    AQ      | -75.250973 |  -0.071389 | Antarctica
   (10 rows)
   
   Query 20170621_131606_00007_du8wp, FINISHED, 2 nodes
   Splits: 18 total, 18 done (100.00%)
   0:06 [244 rows, 8.34KB] [43 rows/s, 1.48KB/s]

   

JDBC客户端连接访问presto

1. pom.xml添加presto-jdbc-0.179依赖

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

2. 代码测试

   package me.icocoro.presto;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.Statement;
   
   /**
    * Created by shaozhipeng on 2017/6/21.
    */
   public class MainTest {
       public static final String JDBC_DRIVER = "com.facebook.presto.jdbc.PrestoDriver";
       public static final String URL = "jdbc:presto://192.168.99.100:8008/hive/default";
   
       public static void main(String[] args) throws Exception {
   
           Connection conn = DriverManager.getConnection(URL, "hadoop", "");
           Statement stmt = conn.createStatement();
           ResultSet rs = stmt.executeQuery("select * from kylin_country limit 5");
           while (rs.next()) {
               String country = rs.getString(1);
               String latitude = rs.getString(2);
               String longitude = rs.getString(3);
               System.out.println(String.format("country=%s, latitude=%s, longitude=%s", country, latitude, longitude));
           }
   
       }
   }

3. 运行结果