文章目录
一. Trino CLI
1. 安装client
Trino CLI提供了一个基于终端的交互式shell。你可以通过它运行查询并与Trino服务端交互来检查其元数据。
下载地址:trino-cli-434-executable.jar
# 下载
wget -O trino https://repo1.maven.org/maven2/io/trino/trino-cli/434/trino-cli-434-executable.jar
# 添加可执行权限、并将其添加到PATH下,方便使用
chmod +x trino
mv trino ~/bin
export PATH=~/bin/:$PATH
2. 使用client执行sql
./trino http://trino.example.com:8080
# 帮助文档
trino> 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>
运行sql
trino> select * from "mysql-1".flinkx_test.flinkx_hdfs_log
-> ;
id | ip | stamp | url | refer | useragent | pid | uid | mid | insert_time | create_time | create_date
------+-----------------+-------+------+-------+-----------+----------+------+------+---------------------+-------------+-------------
1 | NULL | NULL | NULL | aaaa | NULL | 20221212 | NULL | NULL | 2023-01-31 16:32:09 | NULL | 2023-01-11
1 | NULL | NULL | NULL | aaaa | NULL | 20221212 | NULL | NULL | 2023-01-31 18:37:20 | NULL | 2023-01-11
...
(18 rows)
Query 20231206_032803_00000_78prf, FINISHED, 1 node
Splits: 1 total, 1 done (100.00%)
0.67 [18 rows, 0B] [26 rows/s, 0B/s]
连接trino时可以设置默认的catalog(某个连接实例)和schema(数据库),这样可以直接查询表。
./trino http://localhost:8080/mysql-1/flinkx_test
trino:flinkx_test> show tables;
Table
-----------------------------------------------------
aaa
aaa_csv111_1
aaa_csv111_2
...
USE默认的catalog和schema,直接查询此库下的表
trino> USE tpch.tiny;
USE
trino:tiny>
debug功能
./trino http://localhost:8080/mysql-1/flinkx_test --debug
trino:flinkx_test> select * from "mysql-1".flinkx_test.flinkx_hdfs_log
-> ;
id | ip | stamp | url | refer | useragent | pid | uid | mid | insert_time | create_time | create_date
------+-----------------+-------+------+-------+-----------+----------+------+------+---------------------+-------------+-------------
1 | NULL | NULL | NULL | aaaa | NULL | 20221212 | NULL | NULL | 2023-01-31 16:32:09 | NULL | 2023-01-11
1 | NULL | NULL | NULL | aaaa | NULL | 20221212 | NULL | NULL | 2023-01-31 18:37:20 | NULL | 2023-01-11
...
http://localhost:8080/ui/query.html?20231206_033726_00007_78prf
Splits: 1 total, 1 done (100.00%)
CPU Time: 0.0s total, 3K rows/s, 0B/s, 4% active
Per Node: 0.0 parallelism, 30 rows/s, 0B/s
Parallelism: 0.0
Peak Memory: 64B
0.60 [18 rows, 0B] [30 rows/s, 0B/s]
--execute选项
./trino http://localhost:8080/mysql-1/flinkx_test --execute 'select * from "mysql-1".flinkx_test.flinkx_hdfs_log limit 3;'
"1","","","","aaaa","","20221212","","","2023-01-31 16:32:09","","2023-01-11"
"1","","","","aaaa","","20221212","","","2023-01-31 18:37:20","","2023-01-11"
"","124.239.193.194","","","","","h_home","","","2023-02-10 10:52:01","",""
避免使用完全限定符
# 使用`--catalog`和`--schema`选项来
./trino http://localhost:8080 --catalog "mysql-1" --schema flinkx_test \
--execute 'select * from flinkx_hdfs_log limit 3;'
# 通过http设限定符
./trino http://localhost:8080/mysql-1/flinkx_test \
--execute 'select * from flinkx_hdfs_log limit 3;'
执行多个sql
trino http://localhost:8080 \
--execute 'use "mysql-1".flinkx_test;select * from flinkx_hdfs_log limit 3;'
执行sql文件
vim trino.sql
use "mysql-1".flinkx_test;
select * from flinkx_hdfs_log limit 3;
./trino http://localhost:8080 -f trino.sql
输出格式
Trino CLI提供了
--output-format
选项来控制如何在非交互模式下显示输出,可用的选项有ALIGNED、VERTICAL、TSV、TSV_HEADER、CSV、CSV_HEADER、CSV_UNQUOTED、CSV_HEADER_UNQUOTED、JSON和NULL,默认值是CSV。
忽略错误
Trino CLI提供了
--ignore-error
选项来忽略执行文件中的查询时遇到的任何错误。默认行为是在遇到第一个错误时终止执行脚本。
二. JDBC driver 连接Trino
任何Java应用程序都可以通过Java数据库连接(JDBC)驱动连接到Trino。通过JDBC驱动,所有这些应用程序都可以使用Trino。
Trino的JDBC驱动允许你连接到Trino并使用SQL语句与Trino交互。
如果你熟悉JDBC驱动的不同实现,就知道Trino的JDBC驱动是Type 4驱动,这仅仅意味着它直接与Trino原生协议通信。
1. 通过DBeaver用户界面连接
查看表时出现如下错误:
需要声明用户,默认输入admin、密码为空
重新连接可以看到注册的catalog了
可以像之前使用DBeaver查看表了。
2. JDBC Driver in java
2.1. 环境配置
需要java 8 或更高
所有通过JDBC连接Trino的用户,必须在system.jdbc
schema中赋予权限
maven依赖:
<dependency>
<groupId>io.trino</groupId>
<artifactId>trino-jdbc</artifactId>
<version>434</version>
</dependency>
其他版本地址在:
A list of all available versions can be found in the Maven Central Repository.
客户端使用http协议和 Trino client REST API 和Trino进行通讯。
2.2. 注册和配置driver
JDBC URL支持的格式:
jdbc:trino://host:port
jdbc:trino://host:port/catalog
jdbc:trino://host:port/catalog/schema
举例:
jdbc:trino://example.net:8080/hive/sales
连接example.net上的trino(8080端口),并明确了catalog和schema。
如果trino的驱动识别不到,则可以显式声明:
io.trino.jdbc.TrinoDriver
。
2.3. 连接参数
声明方式有如下两种
// properties
String url = "jdbc:trino://example.net:8080/hive/sales";
Properties properties = new Properties();
properties.setProperty("user", "test");
properties.setProperty("password", "secret");
properties.setProperty("SSL", "true");
Connection connection = DriverManager.getConnection(url, properties);
// URL parameters
String url = "jdbc:trino://example.net:8443/hive/sales?user=test&password=secret&SSL=true";
Connection connection = DriverManager.getConnection(url);
当然这两种方式我们可以混用,有些参数通过properties设置,有些在url中设置。
完整的参数见:Parameter reference
2.4. 查询例子
package com.gao;
import java.sql.*;
public class TrinoQueryExample {
public static void main(String[] args) {
// Trino数据库连接信息
String trinoUrl = "jdbc:trino://xxx:8080";
String trinoUser = "admin";
String trinoPassword = "";
// SQL查询语句
String sqlQuery = "select * from \"mysql-1\".flinkx_test.flinkx_hdfs_log limit 3";
try {
Class.forName("io.trino.jdbc.TrinoDriver");
// 建立Trino数据库连接
Connection connection = DriverManager.getConnection(trinoUrl, trinoUser, trinoPassword);
// 创建Statement对象
Statement statement = connection.createStatement();
// 执行查询
ResultSet resultSet = statement.executeQuery(sqlQuery);
// 处理查询结果
while (resultSet.next()) {
// 根据查询结果的列名或索引获取数据
Integer id = resultSet.getInt("id");
Timestamp create_time = resultSet.getTimestamp("create_time");
// 处理获取的数据
System.out.println("id: " + id + ", create_time: " + create_time);
}
// 关闭资源
resultSet.close();
statement.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
}
id: 1, create_time: null
id: 1, create_time: null
id: 0, create_time: null
参考:
《trino权威指南:原书第二版》
trino官网:https://trino.io/docs/current/client.html