《Trino权威指南2》第八章:在Trino中使用SQL

前文引用:
《Trino权威指南2》第一章:Trino介绍
《Trino权威指南2》第二章:安装和配置Trino
《Trino权威指南2》第三章:使用Trino
《Trino权威指南2》第四章:Trino的架构
《Trino权威指南2》第五章:生产就绪部署
《Trino权威指南2》第六章:连接器
《Trino权威指南2》第七章:高级连接器示例

在安装和运行 Trino 后,你首先学到了关于 Trino 中一流 SQL 支持的核心特性,可以回到“SQL with Trino”中再次查看该内容,如果你需要一个概览或提醒。

在第 6 章有关连接器的内容中,你了解到在 Trino 中可以使用 SQL 查询许多数据源。

在这一章中,你将深入了解 Trino 的 SQL 支持细节,包括一组用于创建和操作数据库对象(如模式、表、列和视图)的数据定义语言(DDL)语句。你将更详细地了解支持的数据类型和 SQL 语句。在第 9 章,你将学习有关运算符和函数的更高级用法。

总体而言,这一章的目标不是作为 SQL 的参考指南,而是展示 Trino 中的 SQL 功能。有关 Trino 上 SQL 的最新和最完整信息,你可以参考官方 Trino 文档。

Trino 语句

在深入查询 Trino 中的数据之前,了解可用的数据、位置以及数据类型是非常重要的。Trino 语句允许你收集这类信息以及更多。Trino 语句查询系统表和有关已配置的目录、模式等的元数据信息。这些语句在所有 SQL 语句的相同上下文中工作。

语句中的 FROM 和 FOR 子句需要一个完全限定的表、目录或模式的输入,除非使用 USE 设置了默认值。

可以使用 LIKE 子句来限制结果,它使用类似 SQL LIKE 命令的模式匹配语法。

以下是可用的 Trino 语句:

  • SHOW CATALOGS [ LIKE pattern ]:列出可用的目录。
  • SHOW SCHEMAS [ FROM catalog ] [ LIKE pattern ]:列出目录中的模式。
  • SHOW TABLES [ FROM schema ] [ LIKE pattern ]:列出模式中的表。
  • SHOW FUNCTIONS [ LIKE pattern ]:显示可用的 SQL 函数列表。
  • SHOW COLUMNS FROM tableDESCRIBE table:列出表中的列以及它们的数据类型和其他属性。
  • USE catalog.schemaUSE schema:将会话更新为使用指定的目录和模式作为默认值。如果未指定目录,则使用当前目录解析模式。
  • SHOW STATS FOR table_name:显示特定表中的数据大小和计数等统计信息。
  • EXPLAIN query:生成查询计划并详细说明指定 SQL 查询的各个步骤。

让我们看一些在实际使用中可能会派上用场的示例:

SHOW SCHEMAS IN tpch LIKE '%3%';
 Schema
---------
 sf300
 sf3000
 sf30000
(3 rows)

DESCRIBE tpch.tiny.nation;
  Column   |     Type     | Extra | Comment
-----------+--------------+-------+--------
 nationkey | bigint       |       |
 name      | varchar(25)  |       |
 regionkey | bigint       |       |
 comment   | varchar(152) |       |
(4 rows)

EXPLAIN 语句实际上比前面列表中所示的更强大。以下是完整的语法:

EXPLAIN [ ( option [, ...] ) ] <query>
    options: FORMAT { TEXT | GRAPHVIZ | JSON}
             TYPE { LOGICAL | DISTRIBUTED | IO | VALIDATE }

你可以使用 EXPLAIN 语句显示查询计划:

EXPLAIN
SELECT name FROM tpch.tiny.region;

查询计划的输出可以帮助性能调优,更好地理解 Trino 将如何处理你的查询。你可以在第 4 章和第 12 章中了解更多信息。

EXPLAIN 的一个非常简单的用例是检查你的查询语法是否有效:

EXPLAIN (TYPE VALIDATE)
SELECT name FROM tpch.tiny.region;
 Valid
 ------
 true
(1 row)

Trino 系统表

Trino 系统表无需使用目录文件进行配置。所有模式和表都会自动在系统目录中可用。

您可以使用在“Trino Statements”中讨论的语句查询模式和表,以了解有关运行中的 Trino 实例的更多信息。可用的信息包括有关运行时、节点、目录等的数据。检查可用的信息可以帮助您更好地理解和在运行时使用 Trino。

系统表包含以下模式:

SHOW SCHEMAS IN system;
       Schema
--------------------
 information_schema
 jdbc
 metadata
 runtime
(4 rows)

对于查询调优的目的,最有用的表之一是 system.runtime.queriessystem.runtime.tasks

DESCRIBE system.runtime.queries;
      Column       |            Type             | Extra | Comment
-------------------+-----------------------------+-------+---------
 query_id          | varchar                     |       |
 state             | varchar                     |       |
 user              | varchar                     |       |
 source            | varchar                     |       |
 query             | varchar                     |       |
 resource_group_id | array(varchar)              |       |
 queued_time_ms    | bigint                      |       |
 analysis_time_ms  | bigint                      |       |
 planning_time_ms  | bigint                      |       |
 created           | timestamp(3) with time zone |       |
 started           | timestamp(3) with time zone |       |
 last_heartbeat    | timestamp(3) with time zone |       |
 end               | timestamp(3) with time zone |       |
 error_type        | varchar                     |       |
 error_code        | varchar                     |       |
(15 rows)

DESCRIBE system.runtime.tasks;
        Column          |            Type             | Extra | Comment
-------------------------+-----------------------------+-------+---------
 node_id                 | varchar                     |       |
 task_id                 | varchar                     |       |
 stage_id                | varchar                     |       |
 query_id                | varchar                     |       |
 state                   | varchar                     |       |
 splits                  | bigint                      |       |
 queued_splits           | bigint                      |       |
 running_splits          | bigint                      |       |
 completed_splits        | bigint                      |       |
 split_scheduled_time_ms | bigint                      |       |
 split_cpu_time_ms       | bigint                      |       |
 split_blocked_time_ms   | bigint                      |       |
 raw_input_bytes         | bigint                      |       |
 raw_input_rows          | bigint                      |       |
 processed_input_bytes   | bigint                      |       |
 processed_input_rows    | bigint                      |       |
 output_bytes            | bigint                      |       |
 output_rows             | bigint                      |       |
 physical_input_bytes    | bigint                      |       |
 physical_written_bytes  | bigint                      |       |
 created                 | timestamp(3) with time zone |       |
 start                   | timestamp(3) with time zone |       |
 last_heartbeat          | timestamp(3) with time zone |       |
 end                     | timestamp(3) with time zone |       |
(24 rows)

上述表的描述展示了在“使用 Trino Web UI 监控”中更详细解释的底层数据。system.runtime.queries表提供有关 Trino 中执行的当前和过去查询的信息。system.runtime.tasks表为 Trino 中的任务提供了更底层的详细信息。这类似于 Trino Web UI 的查询详情页面上的信息输出。

以下是从系统表查询的一些有用示例。

列出 Trino 集群中的节点:

SELECT * FROM system.runtime.nodes;

显示所有失败的查询:

SELECT * FROM system.runtime.queries WHERE state='FAILED';

显示所有运行中的查询,包括它们的 query_id:

SELECT * FROM system.runtime.queries WHERE state='RUNNING';

系统表还提供了一种通过 QUERY_ID 终止正在运行的查询的机制:

CALL system.runtime.kill_query(query_id => 'QUERY_ID', message => 'Killed');

除了有关 Trino 在运行时、集群、工作节点等的所有信息外,Trino 连接器还能够公开有关连接的数据源的系统数据。例如,在“面向分布式存储数据源的 Hive 连接器”中讨论的 Hive 连接器可以配置为 datalake 目录中的连接器。它会自动在系统表中公开有关 Hive 的数据:

SHOW TABLES FROM datalake.system;

这些信息包括已使用的分区等方面。

Catalogs

Trino 目录表示使用连接器配置的目录属性文件的数据源,如第 6 章所讨论的。目录包含一个或多个模式,它们提供了一组表的集合。

例如,您可以配置一个目录以访问 PostgreSQL 上的关系型数据库。或者,您可以配置一个目录以通过 JMX 连接器提供对 JMX 信息的访问。其他目录的示例包括使用 Iceberg 连接器的目录,以 Iceberg 表格式连接到对象存储数据源,或者配置为访问实时分布式 OLAP 数据存储的 Pinot 连接器。当在 Trino 中运行 SQL 语句时,您是针对一个或多个目录运行它。

可以使用相同的连接器拥有多个目录。例如,您可以创建两个独立的目录来公开运行在同一服务器上的两个 PostgreSQL 数据库。

在 Trino 中引用表时,完全限定的表名始终以目录为根。例如,完全限定的表名 datalake.test_data.test指的是 datalake 目录中 test_data 模式中的 test 表。该目录可以使用任何连接器。因此,对于用户来说,底层系统大部分都是抽象的。

您可以通过访问系统数据来查看 Trino 服务器中可用的目录列表:

SHOW CATALOGS;
 Catalog
 ---------
 abyss
 datalake
 monitor
 salesdb
 stream
 system
(6 rows)

如果您想知道目录中使用的连接器是什么,您需要查询系统目录:

SELECT *
FROM system.metadata.catalogs
WHERE catalog_name='brain';
 catalog_name | connector_id | connector_name
--------------+--------------+----------------
 brain        | brain        | memory
(1 row)

目录、模式和表的信息不会被 Trino 存储;Trino 没有自己的永久存储系统。这是连接器的责任,它要向 Trino 提供此信息。通常,这是通过从底层数据库查询目录、与对象存储分离的元数据存储,或连接器中的其他配置来完成的。连接器处理此过程,仅在请求时向 Trino 提供信息。

Schemas

在一个目录中,Trino 包含模式。模式包含表、视图和各种其他对象,是组织表的一种方式。目录和模式共同定义了可以查询的一组表。

当使用 Trino 访问关系型数据库(如 PostgreSQL)时,一个模式在目标数据库中对应于相同的概念。其他类型的连接器可能会选择以适合底层数据源的方式将表组织到模式中。连接器的实现决定了模式在目录中的映射方式。例如,对于 Hive 连接器,在 Trino 中将 Hive 中的数据库暴露为一个模式。

通常,在配置目录时,模式已经存在。然而,Trino 也允许创建和对模式进行其他操作。

让我们看一下创建模式的 SQL 语句:

CREATE SCHEMA [ IF NOT EXISTS ] schema_name
[ WITH ( property_name = expression [, ...] ) ]

WITH 子句可用于将属性与模式关联。例如,对于 Hive 连接器,创建模式实际上会在 Hive 中创建一个数据库。有时,希望覆盖 hive.metastore.warehouse.dir 指定的数据库的默认位置:

CREATE SCHEMA datalake.web
WITH (location = 's3://example-org/web/')

请参阅最新的 Trino 文档以获取模式属性列表,或在 Trino 中查询配置的属性列表:

SELECT * FROM system.metadata.schema_properties;
-[ RECORD 1 ]-+------------------------------
catalog_name  | datalake
property_name | location
default_value |
type          | varchar
description   | Base file system location URI

您可以更改现有模式的名称:

ALTER SCHEMA name RENAME TO new_name

还支持删除模式:

DROP SCHEMA [ IF EXISTS ] schema_name

在删除模式之前,您需要删除其中的表。一些数据库系统支持 CASCADE 关键字,该关键字表示 DROP 语句会删除对象(如模式)中的所有内容。在当前阶段,Trino 不支持 CASCADE。

Information Schema

信息模式是 SQL 标准的一部分,在 Trino 中作为一组视图的形式提供关于目录中的模式、表、列、视图和其他对象的元数据。这些视图包含在一个名为 information_schema 的模式中。每个 Trino 目录都有自己的 information_schema。诸如 SHOW TABLES、SHOW SCHEMA 等命令是获取与 information_schema 中相同信息的简写。

information_schema 对于使用诸如商业智能工具之类的第三方工具是至关重要的。许多这些工具会查询 information_schema,以了解存在哪些对象。

information_schema 有九个总的视图。这些在每个连接器中都是相同的。对于一些不支持某些功能的连接器(例如,角色),对该连接器中 information_schema 的查询可能会导致不支持的错误:

SHOW TABLES IN system.information_schema;
             Table
--------------------------------
 applicable_roles
 columns
 enabled_roles
 role_authorization_descriptors
 roles
 schemata
 table_privileges
 tables
 views
(9 rows)

您可以查询模式中的表列表。请注意,information_schema 表也会返回:

SELECT * FROM datalake.information_schema.tables;
 table_catalog |    table_schema    |    table_name    | table_type
 ---------------+--------------------+------------------+----------
 datalake      | web                | nation           | BASE TABLE
 datalake      | information_schema | enabled_roles    | BASE TABLE
 datalake      | information_schema | roles            | BASE TABLE
 datalake      | information_schema | columns          | BASE TABLE
 datalake      | information_schema | tables           | BASE TABLE
 datalake      | information_schema | views            | BASE TABLE
 datalake      | information_schema | applicable_roles | BASE TABLE
 datalake      | information_schema | table_privileges | BASE TABLE
 datalake      | information_schema | schemata         | BASE TABLE
(9 rows)

此外,您可以通过在这些查询中使用 WHERE 子句查看特定表的列:

SELECT table_catalog, table_schema, table_name, column_name
FROM datalake.information_schema.columns
WHERE table_name = 'nation';
 table_catalog |    table_schema    |    table_name    |  column_name
---------------+--------------------+------------------+-------------
 datalake      | web                | nation           | regionkey
 datalake      | web                | nation           | comment
 datalake      | web                | nation           | nationkey
 datalake      | web                | nation           | name
...

现在您了解了目录和模式,让我们了解一下 Trino 中的表定义。表是一组无序的行,这些行以具有特定数据类型的命名列进行组织。这与任何关系数据库中的情况相同,其中表由行、列和这些列的数据类型组成。从源数据到表的映射是由目录定义的。

连接器实现确定了如何将表映射到模式。例如,将 PostgreSQL 表暴露给 Trino 通常是比较直接的,因为 PostgreSQL 本地支持 SQL 和表的概念。唯一的区别通常在于可用和使用的数据类型方面。然而,对于其他系统的连接器的实现需要更多的创意,特别是如果它们在设计上缺乏严格的表概念。例如,Apache Kafka 连接器将 Kafka 主题暴露为 Trino 中的表。

在 SQL 查询中通过使用完全合格的名称(例如,catalog-name.schema-name.table-name)访问表。

让我们看一下在 Trino 中创建表的 CREATE TABLE 语句:

CREATE TABLE [ IF NOT EXISTS ]
table_name (
  { column_name data_type [ COMMENT comment ]
  [ WITH ( property_name = expression [, ...] ) ]
  | LIKE existing_table_name [ { INCLUDING | EXCLUDING } PROPERTIES ] }
  [, ...]
)
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]

如果您了解 SQL,这个通用语法应该很熟悉。在 Trino 中,可选的 WITH 子句具有重要的用途。其他系统(例如 Hive)已经扩展了 SQL 语言,以便用户可以指定在标准 SQL 中无法表达的逻辑或数据。采用这种方法违反了 Trino 的基本理念,即尽可能接近 SQL 标准。这也使得支持许多连接器变得难以管理,因此已经被替换为使用 WITH 子句使用表和列属性。

创建表后,您可以使用标准 SQL 中的 INSERT INTO 语句。例如,在 iris 数据集创建脚本中,首先创建了一个表;参见“Iris 数据集”。然后直接从查询中插入值:

CREATE TABLE iris (
  sepal_length_cm real,
  sepal_width_cm real,
  petal_length_cm real,
  petal_width_cm real,
  species varchar(10)
);
INSERT INTO iris (
  sepal_length_cm,
  sepal_width_cm,
  petal_length_cm,
  petal_width_cm,
  species )
VALUES
  ( ... )

如果数据通过单独的查询可用,可以使用 SELECT 和 INSERT。例如,您想要将内存目录中的数据复制到 PostgreSQL 中的现有表:

INSERT INTO postgresql.flowers.iris
SELECT * FROM brain.default.iris;

如果目标目录中尚不存在表,则可以使用 CREATE TABLE AS SELECT 语法。这通常被称为 CTAS 查询:

CREATE TABLE postgresql.flowers.iris AS
SELECT * FROM brain.default.iris;

SELECT 语句可以包含条件和语句支持的任何其他特性。

表和列属性

让我们通过使用“Hive Connector for Distributed Storage Data Sources”(参见表 8-1)中的 Hive 连接器来学习如何使用 WITH 子句创建表。

图片

使用表 8-1 中的属性,让我们使用 Trino 在 Hive 中创建与 Hive 中创建表相同的表。

首先使用 Hive 语法:

CREATE EXTERNAL TABLE page_views(
  view_time INT,
  user_id BIGINT,
  page_url STRING,
  view_date DATE,
  country STRING)
 STORED AS ORC
 LOCATION 's3://example-org/web/page_views/';

与在 Trino 中使用 SQL 进行比较:

CREATE TABLE datalake.web.page_views(
  view_time timestamp,
  user_id BIGINT,
  page_url VARCHAR,
  view_date DATE,
  country VARCHAR
)
WITH (
  format = 'ORC',
  external_location = 's3://example-org/web/page_views'
);

正如您所见,Hive DDL 已扩展了 SQL 标准。然而,Trino 使用相同目的的属性,因此遵循 SQL 标准。

您可以查询 Trino 的系统元数据以列出配置的表属性:

SELECT * FROM system.metadata.table_properties;

要列出配置的列属性,可以运行以下查询:

SELECT * FROM system.metadata.column_properties;

复制现有表

您可以通过使用现有表作为模板来创建新表。LIKE 子句创建具有与现有表相同列定义的表。默认情况下,不会复制表和列属性。由于属性在 Trino 中很重要,我们建议在语法中使用 INCLUDING PROPERTIES 以便将它们一同复制。在使用 Trino 执行数据转换时,此功能非常有用:

CREATE TABLE datalake.web.page_view_bucketed(
  comment VARCHAR,
  LIKE datalake.web.page_views INCLUDING PROPERTIES
)
WITH (
  bucketed_by = ARRAY['user_id'],
  bucket_count = 50
)

使用 SHOW 语句检查新创建的表定义:

SHOW CREATE TABLE datalake.web.page_view_bucketed;

创建的表定义与原始表进行比较:

SHOW CREATE TABLE datalake.web2.page_views;

从查询结果创建新表

使用 CREATE TABLE AS SELECT(CTAS)语句可以创建一个新表,其中包含 SELECT 查询的结果。表的列定义是通过动态查看查询的结果列数据而创建的。该语句可用于创建临时表或作为创建转换表的过程的一部分:

CREATE TABLE [ IF NOT EXISTS ] table_name [ ( column_alias, ... ) ]
[ COMMENT table_comment ]
[ WITH ( property_name = expression [, ...] ) ]
AS query
[ WITH [ NO ] DATA ]

默认情况下,新表将使用查询的结果数据填充。

CTAS 可用于转换表和数据。实质上,您正在使用 Trino 进行 ETL 工作负载,可以在一个目录或甚至在不同目录之间,也就是不同的数据源之间进行,如“提取、转换、加载和联合查询”中所讨论的。例如,您可以将 TEXTFILE 格式的未分区数据加载到具有 ORC 格式数据的新分区表中:

CREATE TABLE datalake.web.page_views_orc_part
WITH (
   format = 'ORC',
   partitioned_by = ARRAY['view_date','country']
)
AS
SELECT *
FROM datalake.web.page_view_text

下一个示例展示了从 page_views 表的结果会话化查询中创建表:

CREATE TABLE datalake.web.user_sessions
AS
SELECT user_id,
       view_time,
       sum(session_boundary)
         OVER (
           PARTITION BY user_id
           ORDER BY view_time) AS session_id
FROM (SELECT user_id,
             view_time,
             CASE
                WHEN to_unixtime(view_time) -
                     lag(to_unixtime(view_time), 1)
                        OVER(
                           PARTITION BY user_id
                           ORDER BY view_time) >= 30
             THEN 1
             ELSE 0
             END AS session_boundary
      FROM page_views) T
ORDER BY user_id,
         session_id

修改表

ALTER TABLE 语句可以执行诸如重命名表、添加列、删除列或在表中重命名列等操作:

ALTER TABLE name RENAME TO new_name

ALTER TABLE name ADD COLUMN column_name data_type
  [ COMMENT comment ] [ WITH ( property_name = expression [, ...] ) ]

ALTER TABLE name DROP COLUMN column_name

ALTER TABLE name RENAME COLUMN column_name TO new_column_name

需要注意的是,根据连接器和连接器的授权模型,使用默认行为时可能不允许执行这些操作。例如,Hive 连接器默认情况下限制了这些操作。

删除表

使用 DROP TABLE 语句,您可以删除一个表:

DROP TABLE  [ IF EXISTS ] table_name

根据连接器实现的不同,这可能会或可能不会删除底层数据。您应该参考连接器文档以获取更多解释。 在某些情况下,您可能只想删除表中的数据,同时保留表本身以便添加新数据。Trino 支持 TRUNCATE TABLE 来实现这个目的:

TRUNCATE TABLE table_name

对于不支持 TRUNCATE 的连接器,您必须使用 DROP TABLE,然后再使用 CREATE TABLE。

连接器中对表的限制

在本章中,我们已经讨论了 Trino 支持的各种 SQL 语句。然而,并不是说 Trino 中的每个数据源都支持所有语句和语法可能性,或提供相同的语义。

连接器的实现以及底层数据源的能力和语义对这些可能性有很大的影响。

如果尝试使用特定连接器不支持的语句或操作,Trino 将返回一个错误。例如,系统模式和表用于公开有关 Trino 系统的信息。它不支持创建表,因为对于内部系统数据表来说,这根本没有意义。如果尝试创建表,您将收到一个错误:

CREATE TABLE system.runtime.foo(a int);

查询失败:此连接器不支持创建表

如果使用不支持写操作的连接器插入数据,或者在不支持视图的连接器中创建视图或材料化视图等,将显示类似的错误。请查阅每个连接器文档中的 SQL 支持部分,了解连接器支持的语句的详细信息。

视图

视图是基于 SQL 查询结果集的虚拟表。在许多关系型数据库管理系统中,对视图的支持非常好。然而,在 Trino 中,情况更为复杂。

Trino 将来自底层数据源的视图视为表。这使您可以将视图用于一些非常有用的目的:

  • 在更易于消费的视图中公开来自多个表的数据
  • 通过具有受限制的列和/或行的视图来限制可用的数据
  • 方便地提供经过处理、转换的数据

此功能依赖于在连接的数据源中创建和管理这些视图的支持。使用视图自动要求底层数据源完全拥有视图中的数据,因此需要进行创建视图和保持其更新的处理。因此,使用视图可以使您在几个步骤内将查询的处理推送到关系型数据库管理系统:

  1. 发现在 Trino 上运行的表数据上的 SQL 查询存在性能问题。
  2. 通过查看执行的 EXPLAIN 计划来对系统进行故障排除。
  3. 意识到特定子查询导致性能瓶颈。
  4. 创建预处理子查询的视图。
  5. 在 SQL 查询中使用该视图,替换表。
  6. 享受性能的好处。

Trino 还支持在 Trino 本身创建视图。在 Trino 中,视图只是定义视图的 SQL 语句和视图的名称。要在 Trino 中创建和使用视图,您需要一个使用 Hive 连接器或任何其他配置 Hive 元数据存储服务或类似元数据存储系统的连接器的目录。这是必要的,因为 Trino 本身没有任何集成的元数据存储。定义视图中的数据的 SQL 查询可以访问任何目录,甚至多个目录。视图是在包含元数据存储的目录中创建和使用的,即使定义视图的查询访问不同的目录。

当用户查询视图时,定义从元数据存储加载,并运行定义视图的 SQL 查询,就好像用户提交了实际查询。这允许用户创建更简单的查询,同时访问潜在的许多目录、模式、表和特定列,并隐藏查询的所有复杂性。

材料化视图是更强大的功能,由 Iceberg 连接器支持。材料化视图是一个具有缓存数据的视图。在首次创建时,必须运行定义材料化视图的 SQL 语句,以便获取数据并存储在单独的表中。随后的查询可以直接访问缓存的数据,因此查询速度可能要快得多。对底层数据进行任何更改的副作用是,必须定期刷新材料化视图。

另一种特殊情况是 Hive 视图。这些是来自传统 Hive 系统的视图,以 Hive 查询语言编写。这些 Hive 视图也存储在 Hive 元数据存储中。乍一看,Hive 查询语言与 SQL 非常相似。然而,由于存在差异,Hive 视图无法直接解析和执行为 SQL 语句。但是,Trino 用户可以使用 Hive 连接器以及内置的转换库 Coral 来使用这些视图。

总体而言,可以看出视图可以显著改善访问复杂数据的便利性,并且在 Trino 中是一个得到很好支持的功能。

会话信息和配置

在使用 Trino 时,所有配置都在一个称为会话的用户特定上下文中维护。该会话包含表示用于当前用户与 Trino 交互的许多方面配置的键值对。

您可以使用 SQL 命令与该信息进行交互。首先,您可以查看当前配置,甚至使用 LIKE 模式缩小您感兴趣的选项:

SHOW SESSION LIKE 'query%';

此查询返回有关许多属性的信息,例如 query_max_cpu_time、query_max_execution_time、query_max_planning_time,包括当前值、默认值、数据类型(整数、布尔值或 varchar)以及属性的简要描述。

属性列表很长,包括 Trino 行为的许多配置选项,例如查询的内存和 CPU 限制、查询计划算法以及基于成本的优化器的使用。

作为用户,您可以更改这些属性,从而影响当前用户会话的性能。您可以为特定的查询或工作负载设置特定的选项,或者将它们测试为用于集群的主文件为基础的 Trino 配置文件(由集群使用的 config.properties)的全局推出。

例如,您可以激活使用共位连接的查询规划的实验性算法:

SET SESSION colocated_join = true;

您可以确认该设置是否有效:

SHOW SESSION LIKE 'colocated_join';

撤消设置并返回默认值,您可以重置会话属性:

RESET SESSION colocated_join;

除了全局会话属性之外,一些目录配置属性可以修改为特定的用户会话。例如,PostgreSQL 连接器支持属性 unsupported-type-handling。它默认为 IGNORE,因此将省略具有不受支持的数据类型的列的数据。

此属性还可用作目录会话属性,名为 unsupported_type_handling。请注意,与目录配置属性相比,目录会话属性使用类似的名称,用下划线替换破折号。您可以使用此属性更改 crm 目录和会话的列处理,并使用以下查询将数据转换为 VARCHAR:

SET SESSION crm.unsupported_type_handling='CONVERT_TO_VARCHAR';

现在,数据不再被忽略,而是在 Trino 查询中作为字符串可用,您可以使用各种字符串、日期、JSON 和其他格式相关属性修改数据,甚至将其转换为所需的 Trino 数据类型。

数据类型

Trino 支持 SQL 标准中描述的大多数数据类型,这些类型也得到许多关系数据库的支持。在本节中,我们讨论了 Trino 中的数据类型支持。

并非所有的 Trino 连接器都支持所有的 Trino 数据类型。而且 Trino 可能不支持底层数据源的所有类型。数据类型是如何在底层数据源和 Trino 之间进行转换的,这取决于连接器的实现。底层数据源可能不支持相同的类型,或者相同的类型可能具有不同的命名。例如,MySQL 连接器将 Trino 的 REAL 类型映射到 MySQL 的 FLOAT 类型。

在某些情况下,数据类型需要进行转换。一些连接器将不受支持的类型转换为 Trino 的 VARCHAR 类型,基本上是源数据的字符串表示,或者完全忽略读取该列。有关每个连接器文档和源代码中类型映射部分的具体详细信息,请参考。

回到完全支持的数据类型的长列表。表 8-2 到表 8-6 描述了 Trino 中的数据类型,并在适用的情况下提供了示例数据。

图片

与 VARCHAR 不同,CHAR 始终分配 n 个字符。以下是一些您应该了解的特性和错误:

  • 如果将具有少于 n 个字符的字符字符串转换,将添加尾随空格。
  • 如果将具有多于 n 个字符的字符字符串转换,它将被截断而不会出错。
  • 如果将长度超过列定义的 VARCHAR 或 CHAR 插入到表中,将发生错误。
  • 如果将长度短于在列中定义的 CHAR 插入到表中,该值将填充空格以匹配定义的长度。
  • 如果将长度短于在列中定义的 VARCHAR 插入到表中,将存储字符串的确切长度。在比较 CHAR 值时,包括前导和尾随空格。

以下示例突显了这些行为:

SELECT length(cast('hello world' AS char(100)));
-- 结果:
-- _col0
-- -----
-- 100

SELECT cast('hello world' AS char(15)) || '~';
-- 结果:
-- _col0
-- ----------------
-- hello world    ~

SELECT cast('hello world' AS char(5));
-- 结果:
-- _col0
-- -------
-- hello

SELECT length(cast('hello world' AS varchar(15)));
-- 结果:
-- _col0
-- -------
-- 11

SELECT cast('hello world' AS varchar(15)) || '~';
-- 结果:
-- _col0
-- --------------
-- hello world~

SELECT cast('hello world' as char(15)) = cast('hello world' as char(14));
-- 结果:
-- _col0
-- -------
-- true

SELECT cast('hello world' as varchar(15)) = cast('hello world' as varchar(14));
-- 结果:
-- _col0
-- -------
-- true

USE brain.default;
CREATE TABLE varchars(col varchar(5));
-- 创建表

INSERT INTO varchars values('1234');
-- 插入值

INSERT INTO varchars values('123456');
-- 错误:在 INSERT 期间,将 varchar(6)转换为 varchar(5)时无法截断非空格字符

集合数据类型

随着数据变得越来越庞大和复杂,有时它会以更复杂的数据类型(例如数组和映射)的形式存储。许多关系数据库管理系统(RDBMS)以及一些 NoSQL 系统本身就原生支持复杂数据类型。Trino 支持其中一些集合数据类型,详见表 8-7。它还提供了对"Unnesting Complex Data Types"(解构复杂数据类型)中详细说明的 UNNEST 操作的支持。

图片

时间数据类型

表格 8-8 描述了与日期和时间相关的时间数据类型。

图片

在 Trino 中,TIMESTAMP 表示为 Java Instant 类型,表示距离 Java 纪元之前或之后的时间量。对于最终用户来说,这应该是透明的,因为值会以不同的格式解析和显示。

对于不包含时区信息的类型,值将根据 Trino 会话时区进行解析和显示。对于包含时区信息的类型,值将使用时区进行解析和显示。

字符串文字可以由 Trino 解析为 TIMESTAMP、TIMESTAMP WITH TIMEZONE、TIME、TIME WITH TIMEZONE 或 DATE。表 8-9 到 8-11 描述了可用于解析的格式。如果要使用 ISO 8601,可以使用 from_iso8601_timestamp 或 from_iso8601_date 函数。

图片

在打印 TIMESTAMP、TIMESTAMP WITH TIMEZONE、TIME、TIME WITH TIMEZONE 或 DATE 的输出时,Trino 使用表 8-12 中的输出格式。如果要以严格的 ISO 8601 格式输出,可以使用 to_iso8601 函数。

图片

时区

时区添加了重要的额外时间信息。Trino 支持 TIME WITH TIMEZONE,但最好使用带有 DATE 或 TIMESTAMP 的时区。这可以通过 DATE 格式来考虑夏令时。时区必须表示为数值 UTC 偏移值:

+08:00 -10:00

让我们看一些例子:

SELECT TIME '02:56:15' AS utc;
   utc
 ---------
 02:56:15
(1 row)

SELECT TIME '02:56:15' AT TIME ZONE '+08:00' AS perth_time;
   perth_time
 ---------------
 17:56:15+08:00

SELECT TIME '02:56:15' AT TIME ZONE '-08:00' AS sf_time;
    sf_time
 ---------------
 01:56:15-08:00
(1 row)

SELECT TIMESTAMP '1983-10-19 07:30:05.123456';
           _col0
 ---------------------------
 1983-10-19 07:30:05.123456
(1 row)

SELECT TIMESTAMP '1983-10-19 17:30:05.123456' AT TIME ZONE '-08:00';
            _col0
 ----------------------------
 1983-10-19 06:30:05.123456 -08:00
(1 row)

间隔(Intervals)

数据类型 INTERVAL 可以是 YEAR TO MONTH 或 DAY TO SECOND,如表 8-13 和 8-14 所示。

图片

以下示例突出了我们描述的一些行为:

SELECT INTERVAL '1-2' YEAR TO MONTH;
 _col0
 ------
 1-2
(1 row)

SELECT INTERVAL '4' MONTH;
 _col0
 -------
 0-4
(1 row)

SELECT INTERVAL '4-1' DAY TO SECOND;
Query xyz failed: '4-1' is not a valid interval literal

SELECT INTERVAL '4' DAY TO SECOND;
     _col0
 ----------------
 4 00:00:00.000
(1 row)

SELECT INTERVAL '4 01:03:05.44' DAY TO SECOND;
     _col0
 ----------------
 4 01:03:05.440
(1 row)

SELECT INTERVAL '05.44' SECOND;
     _col0
 ----------------
 0 00:00:05.440
(1 row)

类型转换

有时需要明确地将值或文字转换为不同的数据类型。这称为类型转换,可以使用 CAST 函数执行:

CAST(value AS type)

现在假设您需要将列 view_date与数据类型 DATE与日期 2019-01-01进行比较,这是一个文字字符串:

SELECT *
FROM datalake.web.page_views
WHERE view_date > '2019-01-01';

此查询失败,因为 Trino 没有一个能够比较日期和字符串文字的大于(>)比较运算符。但是,它有一个比较函数,知道如何比较两个日期。因此,我们需要使用 CAST 函数强制转换其中一个类型。在这个例子中,将字符串转换为日期是最合理的:

SELECT *
FROM datalake.web.page_views
WHERE view_date > CAST('2019-01-01' AS DATE);

Trino 还提供了另一个转换函数 try_cast。它尝试执行类型强制转换,但与 CAST不同,如果转换失败,则 try_cast返回一个空值。当不需要错误时,这可能很有用:

try_cast(value AS type)

让我们以将字符文字强制转换为数字类型为例:

SELECT CAST('1' AS INTEGER);
_col0
-------
     1
(1 row)
SELECT CAST('a' AS INTEGER);
Query failed: Cannot cast 'a' to INT
SELECT TRY_CAST('a' AS INTEGER);
_col0
------
 NULL
(1 row)

SELECT语句基础知识

SELECT语句至关重要,因为它允许你以表格格式从一个或多个表中返回数据,最少可以减少到一行,或者潜在地仅返回一个值。 在 Trino 中,带有多个不同目录和模式的表,即完全不同的数据源,会使 SELECT 查询具有额外的复杂性,你在“Trino 中的查询联邦”中学到了这一点。 现在,让我们深入了解并学习所有可用的强大功能。让我们从语法概述开始:

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expr [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count | ALL ] ]

select_expr代表以表列、派生表列、常量或零行、一行或多行的形式返回的查询数据。通用表达式可以包括函数、运算符、列和常量。你可以运行一个只有 SELECT select_expr的查询,用于测试,但其用途有限:

SELECT 1, 1+1, upper('lower');
 _col0 | _col1 | _col2
-------+-------+------
     1 |     2 | LOWER
(1 row)

SELECT select_expr [, ...] FROM from_item是查询的最基本形式。它允许你从底层表中检索所有数据或仅选择的列。它还允许你在底层数据上计算表达式。 假设我们有两个表,也称为关系,nation 和 customer。这些示例取自 TPC-H,详见“Trino TPC-H 和 TPC-DS Connectors”。为简洁起见,示例表被截短为仅有几行和几列。我们在整个本章的多个选择查询示例中使用这些数据。 你可以返回 sf1 模式中 nation 表的选择列和数据:

SELECT nationkey, name, regionkey
FROM tpch.sf1.nation;
 nationkey |      name      | regionkey
-----------+----------------+-----------
         0 | ALGERIA        |         0
         1 | ARGENTINA      |         1
         2 | BRAZIL         |         1
         3 | CANADA         |         1
         4 | EGYPT          |         4
         5 | ETHIOPIA       |         0
...

现在是 customer 表的一些示例数据:

SELECT custkey, nationkey, phone, acctbal, mktsegment
FROM tpch.tiny.customer;
 custkey | nationkey |      phone      | acctbal | mktsegment
---------+-----------+-----------------+---------+------------
     751 |         0 | 10-658-550-2257 | 2130.98 | FURNITURE
     752 |         8 | 18-924-993-6038 | 8363.66 | MACHINERY
     753 |        17 | 27-817-126-3646 | 8114.44 | HOUSEHOLD
     754 |         0 | 10-646-595-5871 | -566.86 | BUILDING
     755 |        16 | 26-395-247-2207 | 7631.94 | HOUSEHOLD
...

除了仅返回选择数据外,我们还可以使用函数转换数据并返回结果:

SELECT acctbal, round(acctbal) FROM tpch.sf1.customer;
 acctbal | _col1
---------+--------
 7470.96 | 7471.0
 8462.17 | 8462.0
 2757.45 | 2757.0
 -588.38 | -588.0
 9091.82 | 9092.0
...

where 条件

WHERE 子句在 SELECT 查询中用作过滤器。它包含一个评估为 TRUE、FALSE 或 UNKNOWN 的条件。在查询执行过程中,该条件针对每一行进行评估。如果评估结果不等于 TRUE,该行将被跳过并从结果集中省略。否则,该行将被发出并作为结果的一部分发送给用户或用于进一步处理。

WHERE 子句条件由一个或多个布尔表达式组成,这些表达式由连接性的 AND 和 OR 连接:

SELECT custkey, acctbal
FROM tpch.sf1.customer WHERE acctbal < 0;
 custkey | acctbal
---------+---------
   75016 | -735.89
   75027 | -399.78
   75028 | -222.92
   75034 | -679.38
   75037 | -660.07
...

SELECT custkey, acctbal FROM tpch.sf1.customer
WHERE acctbal > 0 AND acctbal < 500;
 custkey | acctbal
---------+---------
   75011 |  165.71
   75012 |   41.65
   75021 |   176.2
   75022 |  348.24
   75026 |   78.64
...

WHERE 子句条件很重要,因为它用于多个查询优化。在“查询计划”中,你可以了解有关查询计划和优化的更多信息。当查询多个表时,可以通过 WHERE 子句中的条件将它们连接起来。Trino 使用此信息来确定高效的查询执行计划。

GROUP BY 和 HAVING 条件

GROUP BY 和 HAVING 子句通常在分析查询中使用。GROUP BY 用于将相同值的行合并为一行:

SELECT mktsegment
FROM tpch.sf1.customer
GROUP BY mktsegment;
 mktsegment
 -----------
 MACHINERY
 AUTOMOBILE
 HOUSEHOLD
 BUILDING
 FURNITURE
(5 rows)

在 Trino 中的分析查询中,GROUP BY 通常与聚合函数结合使用。这些函数是从构成单个组的行中计算的。以下查询计算所有客户的总账户余额,按市场细分进行拆分:

SELECT mktsegment, round(sum(acctbal) / 1000000, 3) AS acctbal_millions
FROM tpch.sf1.customer
GROUP BY mktsegment;
 mktsegment | acctbal_millions
------------+------------------
 MACHINERY  |          134.439
 AUTOMOBILE |          133.867
 BUILDING   |          135.889
 FURNITURE  |          134.259
 HOUSEHOLD  |          135.873

聚合函数也可以在没有使用 GROUP BY 子句的情况下使用。在这种情况下,整个关系作为聚合函数的输入,因此我们可以计算整体账户余额:

SELECT round(sum(acctbal) / 1000000, 3) AS acctbal_millions
FROM tpch.sf1.customer;
 acctbal_millions
------------------
          674.327

HAVING 子句类似于 WHERE 子句。它对每一行进行评估,仅当条件评估为 TRUE 时才发出行。HAVING 子句在 GROUP BY 之后进行评估并在分组的行上操作。WHERE 子句在 GROUP BY 之前进行评估并在单个行上评估。

以下是完整的查询:

SELECT mktsegment,
      round(sum(acctbal), 1) AS acctbal_per_mktsegment
FROM tpch.tiny.customer
GROUP BY mktsegment;
 mktsegment | acctbal_per_mktsegment
------------+------------------------
 BUILDING   |              1444587.8
 HOUSEHOLD  |              1279340.7
 AUTOMOBILE |              1395695.7
 FURNITURE  |              1265282.8
 MACHINERY  |              1296958.6
(5 rows)

这是在分组数据上使用条件的过滤结果:

SELECT mktsegment,
       round(sum(acctbal), 1) AS acctbal_per_mktsegment
FROM tpch.tiny.customer
GROUP BY mktsegment
HAVING round(sum(acctbal), 1) > 1300000;
 mktsegment | acctbal_per_mktsegment
------------+------------------------
 AUTOMOBILE |              1395695.7
 BUILDING   |              1444587.8
(2 rows)

ORDER BY 和 LIMIT 条件

ORDER BY 子句包含用于对结果进行排序的表达式。该子句可以包含多个表达式,从左到右进行评估。通常在左侧表达式对于多行的情况下具有相同值时,会使用多个表达式来解决排名的问题。这些表达式可以指示排序顺序,可以是升序(例如,A-Z,1-100)或降序(例如,Z-A,100-1)。

LIMIT 子句用于仅返回指定数量的行。与 ORDER BY 子句一起使用,LIMIT 可用于查找有序集的前 N 个结果:

SELECT mktsegment,
       round(sum(acctbal), 2) AS acctbal_per_mktsegment
FROM tpch.sf1.customer
GROUP BY mktsegment
HAVING sum(acctbal) > 0
ORDER BY acctbal_per_mktsegment DESC
LIMIT 3;
 mktsegment | acctbal_per_mktsegment
 -----------+------------------------
 BUILDING   |         1.3588862194E8
 HOUSEHOLD  |         1.3587334117E8
 MACHINERY  |         1.3443886167E8
(3 rows)

通常,Trino 能够将执行 ORDER BY 和 LIMIT 作为一个组合步骤而不是分开执行。

可以在没有 ORDER BY 子句的情况下使用 LIMIT,但它们通常一起使用。原因是 SQL 标准,因此也是 Trino,不保证结果的任何顺序。这意味着在没有 ORDER BY 子句的情况下使用 LIMIT 可能会在每次运行相同查询时返回不同的非确定性结果。在 Trino 这样的分布式系统中,这变得更加明显。

JOIN 语句

SQL 允许你使用 JOIN 语句将来自不同表的数据组合在一起。Trino 支持 SQL 标准的连接,如 INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN 和 CROSS JOIN。JOIN 语句的全面探讨超出了本书的范围,但在许多其他书籍中有详细介绍。

让我们专注于一些例子,并探讨与 Trino 相关的特定细节:

SELECT custkey, mktsegment, nation.name AS nation
FROM tpch.tiny.nation JOIN tpch.tiny.customer
ON nation.nationkey = customer.nationkey;
 custkey | mktsegment |     nation
 --------+------------+----------------
     745 | FURNITURE  | CHINA
     746 | MACHINERY  | SAUDI ARABIA
     747 | FURNITURE  | INDIA
     748 | AUTOMOBILE | UNITED KINGDOM
     749 | MACHINERY  | UNITED STATES
...

Trino 还有一个隐式的交叉连接:表的列表用逗号分隔,并且连接在 WHERE 子句中定义条件:

SELECT custkey, mktsegment, nation.name AS nation
FROM tpch.tiny.nation, tpch.tiny.customer
WHERE nation.nationkey = customer.nationkey;
 custkey | mktsegment |     nation
 --------+------------+----------------
    1210 | AUTOMOBILE | MOZAMBIQUE
    1211 | HOUSEHOLD  | CHINA
    1212 | HOUSEHOLD  | RUSSIA
    1213 | HOUSEHOLD  | GERMANY
    1214 | BUILDING   | EGYPT
...

连接可能是查询处理中最昂贵的操作之一。当查询中存在多个连接时,连接可以通过不同的排列进行处理。TPC-H 基准测试的 Q09 查询是这样一个复杂查询的很好的例子:

SELECT
  nation,
  o_year,
  sum(amount) AS sum_profit
FROM (
       SELECT
         N.name AS nation,
         extract(YEAR FROM o.orderdate) AS o_year,
         l.extendedprice * (1 - l.discount) - ps.supplycost * l.quantity
             AS amount
       FROM
         part AS p,
         supplier AS s,
         lineitem AS l,
         partsupp AS ps,
         orders AS o,
         nation AS n
       WHERE
         s.suppkey = l.suppkey
         AND ps.suppkey = l.suppkey
         AND ps.partkey = l.partkey
         AND p.partkey = l.partkey
         AND o.orderkey = l.orderkey
         AND s.nationkey = n.nationkey
         AND p.name LIKE '%green%'
     ) AS profit
GROUP BY
  nation,
  o_year
ORDER BY
  nation,
  o_year DESC;

UNION、INTERSECT 和 EXCEPT 子句

UNION、INTERSECT 和 EXCEPT 在 SQL 中被称为集合操作。它们用于将多个 SQL 语句的数据合并成一个单一的结果集。

虽然你可以使用连接和条件来实现相同的语义,但通常使用集合操作更容易。Trino 比等效的 SQL 更高效地执行它们。

当你学习集合操作的语义时,通常从基本的整数开始会更容易。你可以从 UNION 开始,它将所有值组合并去除重复项:

SELECT * FROM (VALUES 1, 2)
UNION
SELECT * FROM (VALUES 2, 3);

结果:

_col0
------
     2
     3
     1
(3 rows)

UNION ALL 保留所有重复项:

SELECT * FROM (VALUES 1, 2)
UNION ALL
SELECT * FROM (VALUES 2, 3);

结果:

_col0
 -----
     1
     2
     2
     3
(4 rows)

INTERSECT 返回两个查询中都找到的所有元素:

SELECT * FROM (VALUES 1, 2)
INTERSECT
SELECT * FROM (VALUES 2, 3);

结果:

_col0
------
     2
(1 row)

EXCEPT 返回在第一个查询中找到的元素,去除了在第二个查询中找到的所有元素:

SELECT * FROM (VALUES 1, 2)
EXCEPT
SELECT * FROM (VALUES 2, 3);

结果:

_col0
------
     1
(1 row)

每个集合操作都支持使用可选的修饰符 ALL 或 DISTINCT。DISTINCT 关键字是默认的,不需要指定。ALL 关键字用作保留重复项的一种方式。

分组操作

你已经学过基本的 GROUP BY 和聚合。Trino 还支持 SQL 标准的高级分组操作。使用 GROUPING SETS、CUBE 和 ROLLUP,用户可以在单个查询中对多个集合执行聚合。

Grouping sets 允许你在同一个查询中对多个列列表进行分组。例如,假设我们想要在 (state, city, street)、(state, city) 和 (state) 上进行分组。如果没有 grouping sets,你必须在每个组中运行单独的查询,然后将结果组合起来。有了 grouping sets,Trino 计算每个集合的分组。结果模式是跨集合的列的并集。对于不属于组的列,添加了一个空值。

ROLLUP 和 CUBE 可以使用 GROUPING SETS 表达,并且是一种简写。ROLLUP 用于基于层次结构生成组集。例如,ROLLUP(a, b, c) 生成组集 (a, b, c)、(a, b)、(a)、()。CUBE 操作生成分组的所有可能组合。例如,CUBE(a, b, c) 生成组集 (a, b, c)、(a, b)、(a, c)、(b, c)、(a)、(b)、(c)、()。

例如,假设你想要计算每个市场细分的账户余额总额,并计算所有市场细分的总账户余额:

SELECT mktsegment,
  round(sum(acctbal), 2) AS total_acctbal,
  GROUPING(mktsegment) AS id
FROM tpch.tiny.customer
GROUP BY ROLLUP (mktsegment)
ORDER BY id, total_acctbal;

使用 ROLLUP,你可以计算不同组的聚合。在这个例子中,前五行表示每个市场细分的账户余额总额。最后一行表示所有账户余额的总和。因为没有 mktsegment 的组,所以它被留为空值。GROUPING 函数用于标识哪些行属于哪些组。

如果没有 ROLLUP,你必须将其作为两个单独的查询运行并组合它们。在这个例子中,我们可以使用 UNION,它可以帮助你概念上理解 ROLLUP 在做什么:

SELECT mktsegment,
       round(sum(acctbal), 2) AS total_acctbal,
       0 AS id
FROM tpch.tiny.customer
GROUP BY mktsegment
UNION
SELECT NULL, round(sum(acctbal), 2), 1
FROM tpch.tiny.customer
ORDER BY id, total_acctbal;

with 语句

WITH 子句用于在单个查询中定义内联视图。这通常用于使查询更易读,因为查询可能需要多次包含相同的嵌套查询。

在此查询中,让我们找出总账户余额大于市场细分平均值的市场细分:

SELECT mktsegment,
       total_per_mktsegment,
       average
FROM
  (
    SELECT mktsegment,
       round(sum(acctbal)) AS total_per_mktsegment
    FROM tpch.tiny.customer
    GROUP BY 1
  ),
  (
    SELECT round(avg(total_per_mktsegment)) AS average
    FROM
      (
        SELECT mktsegment,
           sum(acctbal) AS total_per_mktsegment
        FROM tpch.tiny.customer
        GROUP BY 1
      )
  )
WHERE total_per_mktsegment > average;

正如你所看到的,这个查询有点复杂。使用 WITH 子句,我们可以简化它如下:

WITH
total AS (
  SELECT mktsegment,
    round(sum(acctbal)) AS total_per_mktsegment
  FROM tpch.tiny.customer
  GROUP BY 1
),
average AS (
  SELECT round(avg(total_per_mktsegment)) AS average
  FROM total
)
SELECT mktsegment,
  total_per_mktsegment,
  average
FROM total,
  average
WHERE total_per_mktsegment > average;

在这个例子中,第二个内联视图引用了第一个。你可以看到 WITH 内联视图被执行了两次。目前,Trino 不会将结果物化以在多个执行之间共享。实际上,这将取决于查询的复杂性的基于成本的决策,因为多次执行查询可能比存储和检索结果更有效。

子查询

Trino 支持许多常见的子查询用法。子查询是一个充当更高级别表达式输入的表达式。在 SQL 中,子查询可以分为三类:

  • 标量子查询
  • ANY/SOME
  • ALL

每个类别都有两种类型,即无关联和关联。关联子查询是引用子查询外部其他列的子查询。

标量子查询

标量子查询是指返回单个值的查询,即一行一列:

SELECT regionkey, name
FROM tpch.tiny.nation
WHERE regionkey =
  (SELECT regionkey FROM tpch.tiny.region WHERE name = 'AMERICA');

在这个标量示例中,子查询的结果是 1。WHERE 条件本质上变成了 regionkey = 1,并对每一行进行评估。从逻辑上讲,子查询对 nation 表中的每一行都进行评估,例如,对于一百行,会评估一百次。然而,Trino 足够智能,只对子查询进行一次评估,并在其他所有情况下使用静态值。

EXISTS 子查询

一个存在子查询在存在任何行时评估为 true。这些查询通常用作相关子查询。虽然存在无关联子查询的可能性,但这并不太实际,因为返回单行的任何内容都会被评估为 true:

SELECT
  EXISTS(
      SELECT t.*
      FROM tpch.tiny.region AS r
      WHERE r.name = 'ASIA'
      AND t.name = 'CHINA'),
  t.name
FROM tpch.tiny.nation AS t;

另一种常见的存在子查询形式是 NOT EXISTS。然而,这只是对存在子查询结果应用否定。

量化子查询

ANY 子查询采用表达式 操作符 量化器 (子查询) 的形式。有效的操作符值包括 <、>、<=、>=、= 或 <>。可以使用 SOME 代替 ANY。这种类型查询的最常见形式是 expression IN subquery,它等效于 expression = ANY subquery。

SELECT name
FROM nation
WHERE regionkey = ANY (SELECT regionkey FROM region)

此查询等效于以下形式,其中 IN 是简写形式:

SELECT name
FROM nation
WHERE regionkey IN (SELECT regionkey FROM region)

子查询必须返回确切的一列。目前,Trino 不支持比较多于一列的行表达式子查询。在语义上,对于外部查询的给定行,将评估子查询并将表达式与子查询的每个结果行进行比较。如果这些比较中至少有一个评估为 TRUE,则 ANY 子查询条件的结果为 TRUE。如果没有比较评估为 TRUE,则结果为 FALSE。对于外部查询的每一行都会重复这个过程。

需要注意一些微妙之处。如果表达式为 NULL,则 IN 表达式的结果为 NULL。此外,如果没有比较评估为 TRUE,但子查询中有 NULL 值,则 IN 表达式将评估为 NULL。在大多数情况下,这通常不会引起注意,因为 FALSE 或 NULL 的结果会过滤掉该行。但是,如果此 IN 表达式将作为输入传递给对 NULL 值敏感的外围表达式(例如,用 NOT 包围),那么这将很重要。

ALL 子查询与 ANY 类似。对于外部查询的给定行,将评估子查询并将表达式与子查询的每个结果行进行比较。如果所有比较都评估为 TRUE,则 ALL 的结果为 TRUE。如果至少有一个 FALSE 评估,ALL 的结果为 FALSE。

与 ANY 一样,一些微妙之处一开始可能不太明显。当子查询为空且不返回任何行时,ALL 将评估为 TRUE。如果没有比较返回 FALSE,并且至少有一个比较返回 NULL,则 ALL 的结果为 NULL。ALL 的最常见形式是<> ALL,它等效于 NOT IN。

从表中删除数据

DELETE 语句可以从表中删除数据行。该语句提供一个可选的 WHERE 子句,用于限制删除哪些行。如果没有 WHERE 子句,将删除表中的所有数据:

DELETE FROM table_name [ WHERE condition ]

各种连接器对删除操作的支持有限或根本不支持。例如,Kafka 连接器不支持删除操作。Hive 连接器仅在 WHERE 子句指定可以用于删除整个分区的分区键时才支持删除操作:

DELETE FROM datalake.web.page_views
WHERE view_date = DATE '2019-01-14' AND country = 'US'

总结

在 Trino 中使用 SQL 可以做的事情是令人振奋的,不是吗?通过本章的知识,您已经可以编写非常复杂的查询并对暴露给 Trino 的任何数据进行一些相当复杂的分析了。

当然,还有更多。所以,请继续阅读第 9 章,了解使用 Trino 查询数据的函数、操作符和其他功能。

发表回复