【大数据】Presto（Trino）SQL 语法进阶

文章目录

• • 一、概述
  • 二、环境准备
  • 三、Trino 系统库表的讲解
  • • 1）information_schema
    • 2）jdbc
    • 3）metadata
    • 4）runtime（重点）
    • 四、Trino查询Hive数据
    • • 1）查询Hive表
      • 2）创建Hive表
      • 3）加载数据到Hive表
      • 4）分区查询优化
      • 5）trino 操作hive数据源完整示例
      • • 1、配置数据源
        • 2、创建Hive表
        • 3、加载数据到Hive表
        • 4、执行Trino查询
        • 五、Trino SQL 与 Hive SQL 的语法的区别
        • • 1）针对时间类型的函数名称
          • 2）join时ON语法的支持
          • 3）数据类型

            一、概述

            Presto（Trino）是一个快速、分布式的SQL查询引擎，可以用于查询各种数据源，包括Hadoop、NoSQL、关系型数据库等。下面是Presto（Trino）SQL语法的概述：

            它支持标准SQL语法，包括以下SQL命令：

            • SELECT：用于从一个或多个表中检索数据，指定所需的列和过滤条件。

            • FROM：用于指定要查询的表名、子查询或视图，这些源可能跨越数据库和表格。

            • JOIN：用于将两个或多个表格中的列连接到单个结果集中。

            • WHERE：用于指定WHERE子句中定义的条件，以从原始数据集中筛选数据。

            • GROUP BY：用于根据一个或多个列对结果集进行分组。

            • HAVING：用于对GROUP BY后的结果集应用过滤器来定义筛选条件。

            • ORDER BY：用于根据一个或多个列对结果集进行排序。

            • LIMIT：用于限制查询结果集的行数。

              除了标准SQL命令外，Presto（Trino）还支持多种内置函数，如聚合函数、字符串函数、日期函数等。此外，它还支持复杂的窗口函数和嵌套查询以及联合查询。这些高级功能可以帮助用户更轻松地处理大数据集，并快速查询所需的数据。

              

              前面也讲解了一部分SQL语法，建议先查阅我上一篇文章：【大数据】Presto（Trino）配置参数以及 SQL语法，这里只是正对上一篇文章的一些补充。

              二、环境准备

              如已经有环境了，可以忽略，如想快熟部署Presto（Trino）环境可参考我这篇文章：【大数据】通过 docker-compose 快速部署 Presto（Trino）保姆级教程

              docker exec -it trino-coordinator bash
              # --catalog：数据源 --schema：数据库
              ${TRINO_HOME}/bin/trino-cli --server http://trino-coordinator:8080 --user=hadoop
              

              三、Trino 系统库表的讲解

              Presto（Trino）系统库表是用于管理和查询Presto（Trino）系统元数据的特殊表格。这些表格位于系统库（system）中，可容易地查询并返回有关Presto（Trino）集群、数据库、表和列的元数据信息。system 源数据下有：information_schema、jdbc、metadata、runtime，下面将一一讲解。

              1）information_schema

              information_schema是一个标准化的数据库元数据信息架构，用于在关系型数据库中存储信息，例如表、列、索引、约束、列类型、用户等。

              Presto（Trino）支持information_schema元数据架构，可以用于查询表和列信息、数据类型、约束、索引、用户权限等。

              以下是information_schema中一些常见表名称及其描述，以下就是Presto（Trino）system.information_schema中的表：

              • applicable_roles：列出了当前会话用户所属的所有角色信息，包括角色名称、拥有者和角色状态等。如果当前会话用户没有被分配任何角色，则applicable_roles表将返回空结果集。

              • columns：列出数据库中每个表格的列信息，例如名称、数据类型、可否为空等。

              • enabled_roles：用于列出当前会话用户被授予的、激活的所有角色信息。

              • roles：用于列出所有可用角色的详细信息，包括角色名称、拥有者和是否可用等。

              • schemata：列出数据库中所有模式的信息，例如名称、所有者等。

              • table_privileges：用于列出与表和视图相关的所有权限的详细信息，包括授予的角色和权限等。

              • tables：列出数据库中所有表格的信息，例如名称、模式、所属拥有者等。

              • views：列出数据库中所有视图的信息，例如名称、所属模式、列信息等。

                使用information_schema，用户可以轻松地查询数据库元数据，从而进行数据库管理和查询优化。稍微了解以下即可。

                2）jdbc

                Trino（以前称为Presto）提供了一个名为system.jdbc的内置系统表，该表提供了与JDBC连接有关的信息。

                system.jdbc包含以下表：attributes、catalog、columns、procedure_columns、procedures、pseudo_columns、schemas、super_tables、super_types、table_types、tables、types、udts，可以用来查询已连接的数据库的表和视图的元数据。

                以下是一个system.jdbc查询的示例：

                SELECT * FROM system.jdbc.tables WHERE catalog='hive';
                

                此查询将返回连接到Trino（Presto）节点的JDBC数据库中属于hive的所有表和视图的元数据。这些元数据可以用于管理和查询数据库中的对象。这个也稍微了解即可。

                值得注意的是，Presto以分布式方式运行，因此涉及多个节点。如果查询涉及到远程节点上的表，请确保在远程节点上安装了相应的JDBC驱动程序。

                3）metadata

                Trino（以前称为Presto）提供了一个名为system.metadata的内置系统表，该表提供了与Trino中可用表和列的元数据相关的信息。

                system.metadata 包含以下表：analyze_properties、catalogs、column_properties、materialized_view_properties、materialized_views、schema_properties、table_comments、table_properties，可以用来查询Trino中可用表和列的配置信息。也稍微了解即可。

                4）runtime（重点）

                Trino（以前称为Presto）提供了一个名为system.runtime的内置系统表，该表提供了与Trino集群运行时状态相关的信息。

                system.runtime 包含多个子表，包括nodes、tasks、queries、transactions和query_info，可用于查询集群中的正在运行的任务、查询和节点的状态。以下是这些子表的简要介绍：

                • nodes: 提供有关集群中每个节点的基本信息，如节点ID、主机名、HTTP地址和数据传输地址等。

                • optimizer_rule_stats：用于记录优化器规则的统计信息。每次Trino执行查询时，优化器会尝试应用多个规则来优化查询计划。optimizer_rule_stats 记录了每个规则被应用的次数、应用后产生的计划改进、优化器用时等信息。

                • queries: 提供有关正在运行或曾经运行的查询的信息，如查询ID、状态、发起用户、起始时间、最后活动时间、执行时间、SQL语句等。

                • tasks: 提供有关正在运行的任务及其状态的信息，如任务ID、节点ID、查询ID、任务类型等

                • transactions: 提供有关当前正在运行的事务及其状态的信息，如事务ID、状态、开始时间、最后活动时间等。

                  以下是一个system.runtime查询的示例：

                  # --catalog：数据源 --schema：数据库
                  ${TRINO_HOME}/bin/trino-cli --server http://trino-coordinator:8080 --user=hadoop
                  # 查看所有数据源
                  show catalogs;
                  # 查看系统数据源库
                  show schemas from system;
                  # 查看trino节点
                  SELECT * FROM system.runtime.nodes;
                  # 下面两张表一般可用作监控，像Grafana监控
                  # 查询将返回当前正在运行的所有查询的信息，包括其查询ID、发起用户、起始时间和执行时间等。这些信息可用于监视和调试正在运行的查询并了解其执行情况。
                  SELECT * FROM system.runtime.queries WHERE state='RUNNING' limit 10;
                  select * from system.runtime.queries limit 10;
                  select * from system.runtime.tasks limit 10;
                  

                  值得注意的是，由于system.runtime提供了有关集群中所有节点和任务的信息，因此查询这些表可能会对集群产生一定的负载和影响，特别是在查询大量数据时。因此，请根据需要谨慎使用这些表。

                  四、Trino查询Hive数据

                  Trino（以前称为Presto）是一个分布式的SQL查询引擎，可以查询各种不同的数据源，包括Hive。以下是一些常见的使用Trino查询Hive数据的方法。

                  1）查询Hive表

                  在Trino中，可以使用标准的SELECT语句查询Hive表。例如，以下查询将返回Hive表my_table中的所有行：

                   SELECT * FROM hive.default.my_table;
                  

                  Hive表的位置可以使用catalog.schema.table格式的完全限定名称指定。

                  2）创建Hive表

                  在Trino中，可以使用CREATE TABLE语句创建新的Hive表。例如，以下语句将在Hive中创建一个名为new_table的新表：

                  CREATE TABLE hive.default.new_table (
                    col1 varchar,
                    col2 int,
                    col3 decimal(10,2)
                  )
                  WITH (
                    format = 'ORC',
                    partitioned_by = ARRAY['col3']
                  );
                  

                  通过WITH子句指定了新表的格式和分区键。在Trino中创建的Hive表与在Hive中创建的表一样，并且可以通过Hive和Trino共享。

                  3）加载数据到Hive表

                  可以使用Trino的INSERT语句将数据加载到Hive表中。例如，以下语句将向名为my_table的Hive表中插入新行：

                  INSERT INTO hive.default.new_table VALUES ('value1', 123, 45.6);
                  

                  可以使用SELECT语句从其他表中选择数据，并将其插入到Hive表中。

                  4）分区查询优化

                  在Hive表中，可以使用分区将数据组织成更小的块，以提高查询性能。 Trino可以通过分区查询，只查询符合条件的数据子集。以下是查询特定分区的示例：

                  SELECT * FROM hive.default.new_table WHERE col1 = 'value1' AND col2 = 123;
                  

                  这将查询Hive表my_table中col1等于value1和col2等于123的子集。在大数据的情况下，这种分区查询能大大提高查询性能。

                  5）trino 操作hive数据源完整示例

                  1、配置数据源

                  $TRINO_HOME/etc/catalog/hive.properties

                  connector.name=hive
                  hive.metastore.uri=thrift://hive-metastore:9083
                  hive.allow-drop-table=true
                  hive.allow-rename-table=true
                  # hive.config.resources是一个可选属性，如果没有设置该属性，则Hive会使用默认的Hadoop配置文件。但是，在实际应用中，很多Hadoop集群的配置可能与默认值不同，为了确保Hive能够正确地工作，使用hive.config.resources属性指定必要的配置文件是非常必要的。
                  hive.config.resources=${HADOOP_HOME}/etc/hadoop/conf/core-site.xml,${HADOOP_HOME}/etc/hadoop/conf/hdfs-site.xml
                  

                  2、创建Hive表

                  可以使用Trino的CREATE TABLE语句创建新的Hive表。以下是一个创建用于存储电影数据的Hive表的示例：

                  CREATE TABLE hive.default.movies (
                    movie_id bigint,
                    title varchar,
                    rating real, -- real类似与float类型
                    genres varchar,
                    release_year int
                  )
                  WITH (
                    format = 'ORC',
                    partitioned_by = ARRAY['release_year'] -- 注意这里的分区字段必须是上面顺序的最后一个
                  );
                  

                  该表的格式为ORC（format是Trino创建表时的一个可选属性，用于指定表的存储格式。Trino支持多种存储格式，包括Parquet、ORC、JSON、CSV等），并按照release_year列进行分区。

                  3、加载数据到Hive表

                  可以使用INSERT语句将数据加载到Hive表中。以下语句将向名为movies的Hive表中插入新行：

                  INSERT INTO hive.default.movies
                  VALUES 
                  (1, 'Toy Story', 8.3, 'Animation|Adventure|Comedy', 1995), 
                  (2, 'Jumanji', 6.9, 'Action|Adventure|Family', 1995), 
                  (3, 'Grumpier Old Men', 6.5, 'Comedy|Romance', 1995);
                  INSERT INTO hive.default.movies
                  VALUES 
                  (4, 'Toy Story', 8.3, 'Animation|Adventure|Comedy', 1996), 
                  (5, 'Jumanji', 6.9, 'Action|Adventure|Family', 1996), 
                  (6, 'Grumpier Old Men', 6.5, 'Comedy|Romance', 1996);
                  

                  此语句将向movies表中添加6行新数据。

                  4、执行Trino查询

                  可以使用标准的SELECT语句查询Hive表。例如，以下查询将返回Hive表movies中的所有行：

                  SELECT * FROM hive.default.movies;
                  

                  也可以执行带有WHERE子句的查询以过滤数据。例如，以下查询将返回release_year等于1995的子集：

                  SELECT * FROM hive.default.movies
                  WHERE release_year = 1995;
                  

                  可以使用JOIN操作将Hive表与其他表进行连接。例如，以下查询将连接movies表和ratings表，返回包含这两个表中匹配行的结果集：

                  SELECT m.title, m.release_year, r.rating
                  FROM hive.default.movies AS m
                  JOIN hive.default.ratings AS r ON m.movie_id = r.movie_id;
                  

                  需要注意的是，Trino对Hive表的支持与Hive版本相关。在使用Trino之前，请确保已经使用兼容的版本配置了Hive。

                  五、Trino SQL 与 Hive SQL 的语法的区别

                  Trino与Hive SQL虽然有很多相似之处，但也存在一些语法上的差异。以下是一些常见的差异：

                  1）针对时间类型的函数名称

                  • Trino使用标准的SQL函数名称处理日期和时间，如date_trunc、date_add、date_diff、time等。

                  • 而Hive使用自己的函数名称处理日期和时间，如from_unixtime、unix_timestamp、date_sub等。

                    2）join时ON语法的支持

                    Trino使用标准的SQL语法在JOIN操作中使用ON子句指定连接条件，例如：

                    SELECT *
                    FROM table1
                    JOIN table2 ON table1.col1 = table2.col1;
                    

                    而Hive早期版本不支持ON子句，在JOIN操作中需要使用WHERE子句指定连接条件，例如：

                    SELECT *
                    FROM table1
                    JOIN table2 WHERE table1.col1 = table2.col1;
                    

                    但从Hive 0.13版本开始，已经支持使用ON子句指定连接条件。

                    3）数据类型

                    Trino支持标准的SQL数据类型，例如VARCHAR、INTEGER等。而Hive使用自己的数据类型，例如STRING、INT等。Trino可以通过Hive Connector使用在Hive中定义的表。

                    需要注意的是，虽然存在一些语法上的差异，但大多数SQL功能在Trino和Hive中都是同样的。在迁移SQL查询时，需要注意这些差异并相应地更改语法以使其与Trino相容。

                    这里只是针对上篇文章的一些补充，有任何疑问欢迎给我留言，可关注我公众号【大数据与云原生技术分享】加群交流或私信沟通~