学习使用prestodb

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

环境准备

  1. 三台虚拟机bigdata1、bigdata2、bigdata3
  2. 操作系统、JDK1.8、Python【不要使用python3】

    1
    2
    3
    4
    5
    6
    7
    8
    $ 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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    $ 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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    $ 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目录下执行下面命令即可

    1
    ./bin/launcher start

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

    1
    2
    3
    4
    ./bin/launcher run

    ......
    INFO main com.facebook.presto.server.PrestoServer ======== SERVER STARTED ========

    jps命令查看

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    $ 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地址)

    image

官网命令行接口实例

  1. 下载presto-cli-0.179-executable.jar
  2. 可以重命名为presto

    1
    $ mv presto-cli-0.179-executable.jar presto
  3. 执行命令测试,并观察UI界面Finished Queries

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    $ ./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>

    image

  4. 查询

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    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]

    image

JDBC客户端连接访问presto

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

    1
    2
    3
    4
    5
    6
    7
    <dependencies>
    <dependency>
    <groupId>com.facebook.presto</groupId>
    <artifactId>presto-jdbc</artifactId>
    <version>0.179</version>
    </dependency>
    </dependencies>
  2. 代码测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    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. 运行结果

    image
    image

本文标题:学习使用prestodb

本文作者:邵志鹏

本文链接:https://icocoro.me/2017/06/21/1706-presto01/

版权声明: 署名-非商业性使用-禁止演绎 4.0 国际 转载请注明出处。

邵志鹏 wechat
扫一扫上面的二维码关注我的公众号
0%