Trino CLI的使用、Python/Scala连接Presto/Trino

1. 客户端安装和使用

下载，然后重命名，最后赋予执行权限

[root@trino1 ~]#
[root@trino1 ~]# wget https://repo1.maven.org/maven2/io/trino/trino-cli/367/trino-cli-367-executable.jar
[root@trino1 ~]#
[root@trino1 ~]# mv trino-cli-367-executable.jar trino
[root@trino1 ~]#
[root@trino1 ~]# chmod +x trino
[root@trino1 ~]#

trino命令使用

[root@trino1 ~]# 
[root@trino1 ~]# ./trino --version
Trino CLI 367
[root@trino1 ~]# 
[root@trino1 ~]# ./trino --help
[root@trino1 ~]# 
[root@trino1 ~]# ./trino --server trino1:8080 --catalog system --schema runtime
trino:runtime> 

• 可以通过参数--debug查看调试信息
• 通过参数--execute "sql"执行SQL，完成后关闭连接
• 通过参数-f sql_file执行SQL文件中的命令
• 参数--output-format CSV指定在非交互模式下，数据的输出格式，可选的格式有：ALIGNED, VERTICAL, TSV, TSV_HEADER, CSV, CSV_HEADER, CSV_UNQUOTED, CSV_HEADER_UNQUOTED, JSON, NULL，其中NULL没有输出
• 参数--ignore-errors表示，当使用-f sql_file执行脚本时，忽略错误，继续执行脚本中后面的命令

获取帮助和显示函数

trino:runtime>
trino:runtime> help

Supported commands:
QUIT
EXIT
CLEAR
EXPLAIN [ ( option [, ...] ) ] <query>
    options: FORMAT { TEXT | GRAPHVIZ | JSON }
             TYPE { LOGICAL | DISTRIBUTED | VALIDATE | IO }
DESCRIBE <table>
SHOW COLUMNS FROM <table>
SHOW FUNCTIONS
SHOW CATALOGS [LIKE <pattern>]
SHOW SCHEMAS [FROM <catalog>] [LIKE <pattern>]
SHOW TABLES [FROM <schema>] [LIKE <pattern>]
USE [<catalog>.]<schema>

trino:runtime> 
trino:runtime> show functions;
trino:runtime>

描述表结构和查询表数据

trino:runtime>
trino:runtime> describe system.runtime.nodes;
    Column    |  Type   | Extra | Comment 
--------------+---------+-------+---------
 node_id      | varchar |       |         
 http_uri     | varchar |       |         
 node_version | varchar |       |         
 coordinator  | boolean |       |         
 state        | varchar |       |         
(5 rows)

Query 20211229_182353_00037_47sv8, FINISHED, 3 nodes
Splits: 6 total, 6 done (100.00%)
0.94 [5 rows, 333B] [5 rows/s, 354B/s]

trino:runtime>
trino:runtime> select * from system.runtime.nodes;
               node_id                |         http_uri          | node_version | coordinator | state  
--------------------------------------+---------------------------+--------------+-------------+--------
 d1c6b138-6643-11ec-a357-30d042079a38 | http://192.168.23.83:8080 | 367          | false       | active 
 a49c04c9-6642-11ec-acff-30d042079a38 | http://192.168.23.81:8080 | 367          | true        | active 
 bcd1f84a-6643-11ec-97d8-30d042079a38 | http://192.168.23.82:8080 | 367          | false       | active 
(3 rows)

Query 20211227_024842_00005_hwk5i, FINISHED, 2 nodes
Splits: 2 total, 2 done (100.00%)
0.49 [3 rows, 213B] [6 rows/s, 432B/s]

trino:runtime> 

• 可以使用||运算符连接两个字符串类型的字段

退出客户端

trino:runtime>
trino:runtime> exit;
[root@trino1 ~]# 

也可以通过HTTP请求获取数据，例如请求http://192.168.23.81:8080/v1/info，返回如下数据:

{"nodeVersion":{"version":"367"},"environment":"trino_cluster","coordinator":true,"starting":false,"uptime":"1.07h"}

2. Python客户端的使用

安装

[root@trino1 ~]#
[root@trino1 ~]# pip3 install trino
[root@trino1 ~]#

使用

import trino
from trino import transaction


if __name__ == '__main__':

    conn = trino.dbapi.connect(
        host = '192.168.23.81',
        port = 8080,
        user = 'trino',
        catalog = 'system',
        schema = 'runtime',
        isolation_level = transaction.IsolationLevel.READ_COMMITTED
    )

    cursor = conn.cursor()
    cursor.execute("select * from nodes")
    rows = cursor.fetchmany(size = 3)

    print(rows)

    cursor.close()
    conn.close()

3. JDBC driver的使用

添加依赖

        <dependency>
            <groupId>io.trino</groupId>
            <artifactId>trino-jdbc</artifactId>
            <version>377</version>
        </dependency>

Trino的Druid连接池

package org.mq.streamWarehouse.riskEvaluation.trinoConnect

import com.alibaba.druid.pool.{DruidDataSource, DruidPooledConnection}

object TrinoConnectPool {

  val dataSource = new DruidDataSource()

  dataSource.setDriverClassName("io.trino.jdbc.TrinoDriver")
  dataSource.setUrl("jdbc:trino://192.168.23.81:8080/system/runtime")
  dataSource.setUsername("trino")


  dataSource.setInitialSize(2)
  dataSource.setMaxActive(30)
  dataSource.setMinIdle(2)
  //检查时间
  dataSource.setMaxWait(5000)

  def getConnection(): DruidPooledConnection = {

    dataSource.getConnection()
  }
}