《Trino权威指南2》第三章:使用Trino

前文引用:
《Trino权威指南2》第一章:Trino介绍
《Trino权威指南2》第二章:安装和配置Trino

恭喜!在前几章中,您已经了解了 Trino 并学会了如何安装、配置和启动它。现在您可以开始使用它了。

Trino 命令行界面

Trino 命令行界面(CLI)提供了一个基于终端的交互式 shell,用于运行查询并与 Trino 服务器进行交互,以检查其元数据。

开始

就像 Trino 服务器本身一样,Trino CLI 发布的二进制文件也在 Maven 中央仓库上进行分发。CLI 应用程序以可执行的 JAR 文件形式提供,这使您可以像使用普通的 Unix 可执行文件一样使用它。

您可以在 repo.maven.apache.org/maven2/io/t… 上查看可用版本的列表。

找到与您正在运行的 Trino 服务器版本相同或更新的 CLI 版本。从版本化的目录中下载*-executable.jar 文件,并将其重命名为 trino;例如,使用 wget 和版本 392:

$ wget -O trino \
https://repo.maven.apache.org/maven2/\
io/trino/trino-cli/392/trino-cli-392-executable.jar

确保该文件被设置为可执行文件。为了方便起见,将其放在 PATH 环境变量下可访问的位置,例如将其复制到~/bin 并将该文件夹添加到 PATH 中:

$ chmod +x trino
$ mv trino ~/bin
$ export PATH=~/bin/:$PATH

现在您可以运行 Trino CLI 并确认版本:

$ trino --version
Trino CLI 392

可以通过使用 help 选项获取所有可用选项和命令的文档:

$ trino --help

在开始使用 CLI 之前,您需要确定要与哪个 Trino 服务器进行交互。默认情况下,CLI 会连接到运行在 http://localhost:8080 上的 Trino 服务器。如果您的服务器是用于测试或开发的本地运行,或者您通过 SSH 访问服务器,或者您在 Docker 容器中使用 exec 并且 CLI 已安装在其中,那么您可以开始了:

$ trino
trino>

如果 Trino 运行在不同的服务器上,您需要指定 URL:

$ trino --server http://trino.example.com:8080

对于使用任何身份验证系统的 Trino 部署,需要使用传输层安全性(TLS),因此使用 HTTPS。在这种情况下,端口通常设置为使用默认的 HTTPS 端口 443,因此您可以忽略它:

$ trino --server https://trino.example.com

trino> 提示显示您正在使用交互式控制台访问 Trino 服务器。输入 help 以获取可用命令的列表:

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 语句,在 CLI 中都需要以分号结尾。您可以在“SQL with Trino”中找到更多信息。现在,您可以先探索一些简单的内容以便开始。

首先,您可以检查配置为目录的数据源。至少会有一个内部的元数据目录——system。在我们的情况下,您还会找到tpch和其他一些目录:

SHOW CATALOGS;
 Catalog
---------
 abyss
 brain
 monitor
 system
 tpcds
 tpch
(6 rows)

Query 20220618_163043_00000_9ftag, FINISHED, 1 node
Splits: 11 total, 11 done (100.00%)
0.70 [0 rows, 0B] [0 rows/s, 0B/s]

您可以轻松显示可用的模式以及模式中的表。每次查询 Trino 时,都会返回查询处理统计信息和结果。您将像上面的代码一样看到它们。在接下来的列表中,我们将忽略它们:

trino> SHOW SCHEMAS FROM tpch;
       Schema
--------------------
 information_schema
 sf1
 sf100
 sf1000
 sf10000
 sf100000
 sf300
 sf3000
 sf30000
 tiny
(10 rows)

trino> SHOW TABLES FROM tpch.sf1;
  Table
----------
 customer
 lineitem
 nation
 orders
 part
 partsupp
 region
 supplier
(8 rows)

现在您已经准备好查询一些实际数据了:

trino> SELECT count(name) FROM tpch.sf1.nation;
 _col0
-------
    25
(1 row)

或者,您可以选择一个目录和模式来进行操作,然后在查询中省略限定符:

rino> USE tpch.sf1;
USE
trino:sf1> SELECT count(name) FROM nation:

如果您知道将要使用特定的目录和模式,在启动 CLI 之前,您可以在启动时指定它们:

$ trino --catalog tpch --schema sf1

现在您已经准备好发挥您的 SQL 知识和 Trino 的强大功能来查询配置的数据源了。 要退出 CLI,您可以简单地输入 quit 或 exit,或者按 Ctrl-D 键。

分页查询(Pagination)

默认情况下,查询的结果使用 less 程序进行分页显示,并配置了一组精选的选项。可以通过设置环境变量 TRINO_PAGER 为其他程序(如 more)的名称来覆盖此行为,或者将其设置为空值以完全禁用分页。

历史记录和自动补全(History and Completion)

Trino CLI 会保留先前使用的命令的历史记录。您可以使用上下箭头来浏览历史记录,也可以使用 Ctrl-S 和 Ctrl-R 来搜索历史记录。如果您想重新执行一个查询,按 Enter 键执行查询。

默认情况下,Trino 历史记录文件位于~/.trino_history。您可以使用 TRINO_HISTORY_FILE 环境变量更改默认值。

历史记录还用于在您输入时显示命令和查询的可能完成。使用右箭头键选择显示的命令,按 Enter 键运行查询。

附加诊断信息(Additional Diagnostics)

Trino CLI 提供了--debug 选项,用于在运行查询时启用调试信息:

$ trino --debug

trino> SELECT count(*) FROM tpch.sf1.foo;
Query 20181103_201856_00022_te3wy failed:
 line 1:22: Table tpch.sf1.foo does not exist
io.trino.sql.analyzer.SemanticException:
 line 1:22: Table tpch.sf1.foo does not exist
...
at java.lang.Thread.run(Thread.java:748)

执行查询

可以使用 trino 命令直接执行查询,并在查询完成后退出 Trino CLI。如果您正在执行多个查询的脚本或者正在使用另一个系统自动化更复杂的工作流程,这通常是可取的。执行将返回 Trino 的查询结果。

要使用 Trino CLI 运行查询,请使用--execute 选项。同样重要的是要完全限定表名(例如,catalog.schema.table):

$ trino --execute 'SELECT nationkey, name, regionkey FROM tpch.sf1.nation LIMIT 5'
"0","ALGERIA","0"
"1","ARGENTINA","1"
"2","BRAZIL","1"
"3","CANADA","1"
"4","EGYPT","4"

或者,使用--catalog 和--schema 选项:

$ trino --catalog tpch --schema sf1 \
  --execute 'select nationkey, name, regionkey from nation limit 5'

您可以通过使用分号将查询分隔开来执行多个查询。Trino CLI 还支持在文件中执行命令和 SQL 查询,例如 nations.sql 文件:

USE tpch.sf1;
SELECT name FROM nation;

当您使用 CLI 的-f 选项时,它会在命令行上返回数据,然后退出:

$ trino -f nations.sql
USE
"ALGERIA"
"ARGENTINA"
"BRAZIL"
"CANADA"
...

输出格式

Trino CLI 提供了--output-format 选项,用于在非交互模式下控制输出的显示方式。可用的选项包括 ALIGNED、VERTICAL、TSV、TSV_HEADER、CSV、CSV_HEADER、CSV_UNQUOTED、CSV_HEADER_UNQUOTED、JSON 和 NULL。默认值是 CSV。

忽略错误(Ignoring Errors)

如果您希望在执行文件中的查询时跳过遇到的任何错误,Trino CLI 提供了--ignore-error 选项。默认行为是在遇到第一个错误时停止脚本的执行。

Trino 的 JDBC 驱动

Trino 可以通过使用 Trino Java 数据库连接(JDBC)驱动程序从任何 Java 应用程序访问。JDBC 是一个标准 API,提供了必要的方法,如在关系数据库中查询、插入、删除和更新数据。许多在 JVM 上运行的客户端和服务器端应用程序实现了数据库管理、报表和其他方面的功能,并使用 JDBC 来访问底层数据库。所有这些应用程序都可以使用 Trino 的 JDBC 驱动程序。

Trino JDBC 驱动程序允许您连接到 Trino 并通过 SQL 语句与 Trino 进行交互。

使用 JDBC 驱动程序可以使用强大的 SQL 客户端和数据库管理工具,例如开源工具 DBeaver 或 SQuirreL SQL Client 等。还可以使用使用 JDBC 的报表生成、仪表板和分析工具与 Trino 一起使用。

使用任何这些工具与 Trino 一起使用的步骤类似:

  1. 下载 JDBC 驱动程序。
  2. 将 JDBC 驱动程序放置在应用程序的类路径中。
  3. 配置 JDBC 驱动程序。
  4. 配置与 Trino 的连接。
  5. 连接到 Trino 并使用它。

以开源的数据库管理工具 DBeaver 为例,它使得这个过程变得简单。在安装并启动 DBeaver 后,按照以下步骤操作:

  1. 从文件菜单中选择新建。
  2. 从 DBeaver 部分选择数据库连接,然后点击下一步。
  3. 在输入字段中输入 trino,选择 Trino 图标,然后点击下一步。输入 prestosql,并选择 PrestoSQL 图标以连接到 Trino 集群版本 350 及更早版本。
  4. 配置与 Trino 的连接,然后点击完成。注意,需要提供用户名。在没有身份验证的默认 Trino 安装中,可以提供一个随机名称。
  5. 现在,在左侧的数据库导航器中可以看到连接,并可以检查模式和表,如图 3-1 所示。您还可以启动 SQL 编辑器,开始编写查询并与 Trino 进行交互。

SQuirreL SQL Client 和许多其他工具使用类似的过程。某些步骤,例如下载 JDBC 驱动程序和配置数据库驱动程序和连接,可能需要更多的手动操作。让我们详细了解一下。

图片

下载和注册驱动程序

Trino JDBC 驱动程序分发在 Maven 中央仓库上。驱动程序以 JAR 文件的形式提供。 您可以在 repo.maven.apache.org/maven2/io/t… 上查看可用版本的列表。

确定最大的数字,该数字表示最新发布的版本,并进入该文件夹并下载.jar 文件。您也可以在命令行上下载存档文件,例如使用 wget 命令下载版本 392 的文件:

$ wget https://repo.maven.apache.org/maven2/\
io/trino/trino-jdbc/392/trino-jdbc-392.jar

要在应用程序中使用 Trino JDBC 驱动程序,将其添加到 Java 应用程序的类路径中。这对于每个应用程序都有所不同,但通常使用一个名为 lib 的文件夹,就像 SQuirreL SQL Client 一样。一些应用程序包括一个对话框来添加库到类路径中,可以作为手动复制文件的替代方法。

加载驱动程序通常需要重新启动应用程序。

现在,您可以注册驱动程序。在 SQuirreL SQL Client 中,您可以使用界面左侧的驱动程序选项卡上的+按钮来创建新的驱动程序。

配置驱动程序时,您需要确保配置以下参数:

  • 类名:io.trino.jdbc.TrinoDriver
  • 示例 JDBC
  • URL:jdbc:trino://host:port/catalog/schema
  • 名称:Trino
  • 网站: trino.io

仅类名、JDBC URL 和类路径上的 JAR 是驱动程序实际操作所需的。其他参数是可选的,取决于应用程序。

建立与 Trino 的连接

注册了驱动程序并启动了 Trino 后,您现在可以从应用程序连接到 Trino。

在 SQuirreL SQL Client 中,这个连接配置称为别名。您可以使用用户界面左侧的 Alias 选项卡和+按钮创建一个新的别名,并设置以下参数:

  • 名称

Trino 连接的描述性名称。如果您要连接多个 Trino 实例、不同的模式和数据库,则名称更为重要。选择一个有意义的名称可以帮助您区分这些连接。

  • 驱动程序

选择先前创建的 Trino 驱动程序。

  • URL

JDBC URL 采用以下模式:jdbc:trino://host:port/catalog/schema,其中 catalog 和 schema 是可选的。您可以使用 JDBC URL jdbc:trino://localhost:8080 连接到先前在本地机器上安装并在 http://localhost:8080 上运行的 Trino。host 参数是 Trino 协调器所在的主机。这与通过 TrinoCLI 进行连接时使用的主机名相同。它可以是 IP 地址或 DNS 主机名的形式。port 参数是连接到 Trino 的 HTTP 端口。可选的 catalog 和 schema 参数用于通过指定的 catalog 和 schema 进行连接。当您指定这些参数时,在查询中不需要完全限定表名。

  • 用户名

即使在 Trino 上未配置身份验证,也需要提供用户名。这允许 Trino 报告任何查询的发起者。

  • 密码

密码与用户关联,并用于身份验证。默认安装的 Trino 不需要密码,因为未配置身份验证。

JDBC 驱动程序可以接收更多参数作为属性。提供这些值的机制取决于应用程序。DBeaver 和 SQuirreL SQL Client 都包括一个用户界面,可在连接配置的一部分中指定属性:

  • SSL

启用连接的 SSL 使用,true 或 false。如果集群通过 HTTPS 可用,则必须将其设置为 true。

  • SSLTrustStorePath

SSL 信任库的路径。

  • SSLTrustStorePassword

SSL 信任库的密码。

  • user 和 password

相当于用户名和密码参数。

applicationNamePrefix 用于标识 Trino 应用程序的属性。这用于设置 Trino 查询的源名称。此名称显示在 Trino Web UI 中,以便管理员可以看到查询的来源。此外,它可以与资源组一起使用,在资源组中使用 ApplicationName 来决定如何分配 Trino 中的资源。这在“资源组”中进行了讨论。

有关 JDBC 驱动程序的可用参数的完整列表可以在 Trino 文档中找到;请参阅“文档”。

配置完连接后,您可以使用它连接到 Trino。这使您可以查询 Trino 本身和所有配置的模式和数据库。查询执行、报表生成或任何其他功能的具体特性取决于连接到 Trino 的应用程序。图 3-2 显示了在 SQuirreL SQL Client 中成功连接到 Trino 并显示示例查询和结果集的情况。

图片

Trino 和 ODBC

与 JDBC 驱动程序连接到 Trino 类似,Open Database Connectivity (ODBC) 允许支持 ODBC 的任何应用程序使用 Trino。它为通常是基于 C 的应用程序提供了一个 API。

目前,尚未提供用于 Trino 的开源 ODBC 驱动程序。但是,可以从 Starburst 和 Simba 购买商业驱动程序。

这使得许多流行的数据库管理、商业智能、报表和分析等领域的应用程序能够使用 Trino,例如 Microsoft Power BI、Tableau、SAS、Quest Toad 等。ODBC 还支持 Microsoft Excel 的使用。

客户端库

除了 Trino CLI 和 JDBC 驱动程序之外,Trino 团队直接维护 trino-python-client 和 trino-go-client。此外,更大的 Trino 社区的许多成员也创建了用于 Trino 的客户端库。

您可以找到用于 C、Node.js、R、Ruby 和其他语言的库。有一个列表在 Trino 网站上维护,可以在“网站”中找到。

这些库可以用于将 Trino 与这些语言生态系统中的应用程序集成,包括您自己的应用程序。

Trino web UI

每个 Trino 服务器都提供一个 Web 界面,通常称为 Trino Web UI。Trino Web UI(如图 3-3 所示)公开了有关 Trino 服务器和服务器上的查询处理的详细信息。

Trino Web UI 可以通过与 Trino 服务器相同的地址访问,使用相同的 HTTP 端口号。默认情况下,该端口为 8080;例如, trino.example.com:8080 。因此,在您的本地安装中,您可以在 http://localhost:8080 上查看 Web UI。

您需要提供一个用户名和登录信息。默认情况下,未配置任何身份验证,因此您可以提供一个随机的用户名。

主要的仪表板显示了关于 Trino 利用率和查询列表的详细信息。更多详细信息可以在 UI 中找到。所有这些信息对于操作 Trino 和管理正在运行的查询非常有价值。

使用 Web UI 非常有助于监视 Trino 并进行性能调优,如“使用 Trino Web UI 进行监控”中更详细地解释。作为初学者用户,最主要的用途是确认服务器正在运行并正在处理您的查询。

图片

Trino 与 SQL 的结合

Trino 是一个符合 ANSI SQL 标准的查询引擎。它允许您使用相同的 SQL 语句、函数和运算符查询和操作任何连接的数据源中的数据。

Trino 致力于遵循现有的 SQL 标准。Trino 的主要设计原则之一是不发明另一种类似 SQL 的查询语言,也不偏离 SQL 标准太多。只有当标准没有定义等效的功能时,才考虑扩展 SQL 功能集。即使在这种情况下,也会非常小心地设计该功能,考虑到标准中类似功能和其他现有 SQL 实现的特点,以此作为未来可能成为标准的参考。

Trino 不定义与之符合的特定 SQL 标准版本。相反,标准被视为一份不断发展的文件,最新的标准版本始终被认为是重要的。另一方面,Trino 尚未实现 SQL 标准中定义的所有强制性功能。按照规定,如果发现现有功能不符合标准,它将被弃用,并在后续版本中用符合标准的功能替代。

可以使用 Trino CLI 以及之前讨论过的任何通过 JDBC 或 ODBC 连接的数据库管理工具来查询 Trino。

概念

Trino 使得可以通过 SQL 访问外部数据源,例如关系型数据库、键值存储和对象存储。在 Trino 中,以下概念是理解的重要内容:

  • 连接器(Connector)

将 Trino 适配到数据源上。每个目录(Catalog)与一个特定的连接器相关联。

  • 目录(Catalog)

定义访问数据源的细节,包含模式(Schema)并配置要使用的特定连接器。

  • 模式(Schema)

一种组织表的方式。目录和模式共同定义了可查询的表集合。

  • 表(Table)

无序行的集合,以具有数据类型的命名列进行组织。

第一个示例

本节提供了对支持的 SQL 和 Trino 语句的简要概述,更详细的内容可以在第 8 和第 9 章中找到。Trino 元数据包含在系统目录中。可以使用特定的语句来查询这些数据,并且通常可以了解可用的目录、模式、信息模式、表、函数等更多信息。 使用以下语句列出所有目录:

SHOW CATALOGS;
 Catalog
---------
 abyss
 brain
 monitor
 system
 tpcds
 tpch
(6 rows)

显示 tpch 目录中的所有模式,如下所示:

SHOW SCHEMAS FROM tpch;
       Schema
 ------------------
 information_schema
 sf1
 sf100
 sf1000
 sf10000
 sf100000
 sf300
 sf3000
 sf30000
 tiny
(10 rows)

以下是如何列出 sf1 模式中的表:

SHOW TABLES FROM tpch.sf1;
  Table
 ---------
 customer
 lineitem
 nation
 orders
 part
 partsupp
 region
 supplier
(8 rows)

通过以下方式了解 region 表中的数据情况:

DESCRIBE tpch.sf1.region;
  Column   |     Type     | Extra | Comment
-----------+--------------+-------+---------
 regionkey | bigint       |       |
 name      | varchar(25)  |       |
 comment   | varchar(152) |       |
(3 rows)

还有其他有用的语句,例如 USE 和 SHOW FUNCTIONS。有关系统目录和 Trino 语句的更多信息,请参阅“Trino 语句”。 通过了解可用的目录、模式和表,您可以使用标准 SQL 来查询数据。 您可以检查可用的地区:

SELECT name FROM tpch.sf1.region;
    name
 ------------
 AFRICA
 AMERICA
 ASIA
 EUROPE
 MIDDLE EAST
(5 rows)

您可以返回子集并对列表进行排序:

SELECT name FROM tpch.sf1.region
 WHERE name like 'A%' ORDER BY name DESC;
  name
 ---------
 ASIA
 AMERICA
 AFRICA
(3 rows)

支持连接多个表和其他 SQL 标准的部分:

SELECT nation.name AS nation, region.name AS region
FROM tpch.sf1.region, tpch.sf1.nation
WHERE region.regionkey = nation.regionkey
AND region.name LIKE 'AFRICA'
ORDER by nation;
   nation   | region
------------+--------
 ALGERIA    | AFRICA
 ETHIOPIA   | AFRICA
 KENYA      | AFRICA
 MOROCCO    | AFRICA
 MOZAMBIQUE | AFRICA
(5 rows)

Trino 支持使用||进行字符串连接的操作符。您还可以使用加号(+)和减号(-)等数学运算符。 您可以修改上述查询,使用 JOIN 操作符并将结果字符串连接到一个字段中:

SELECT nation.name || ' / ' || region.name AS Location
FROM tpch.sf1.region JOIN tpch.sf1.nation
ON region.regionkey = nation.regionkey
AND region.name LIKE 'AFRICA'
ORDER BY Location;
      Location
 -------------------
 ALGERIA / AFRICA
 ETHIOPIA / AFRICA
 KENYA / AFRICA
 MOROCCO / AFRICA
 MOZAMBIQUE / AFRICA
(5 rows)

除了运算符外,Trino 还支持各种各样的函数。它们涵盖了从简单用例到非常复杂的功能。您可以使用 SHOW FUNCTIONS 在 Trino 中显示函数列表。 一个简单的例子是计算所有订单的平均价格并显示舍入后的整数值:

SELECT cast(round(avg(totalprice)) AS integer) AS average_price
FROM tpch.sf1.orders;
 average_price
 --------------
     151220
(1 row)

有关 SQL 用法的更多详细信息可在 Trino 文档和第 8 章中找到。关于函数和运算符的信息也可以在网站上找到,并且您可以在第 9 章中找到更多示例的概述。

发表回复