【大数据】Presto(Trino)SQL 语法进阶

一、概述

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)还支撑多种内置函数,如聚合函数、字符串函数、日期函数等。此外,它还支撑复杂的窗口函数和嵌套查询以及联合查询。这些高档功用能够协助用户更轻松地处理大数据集,并快速查询所需的数据。

【大数据】Presto(Trino)SQL 语法进阶
前面也讲解了一部分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_schemajdbcmetadataruntime,下面将逐个讲解。

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包含以下表:attributescatalogcolumnsprocedure_columnsprocedurespseudo_columnsschemassuper_tablessuper_typestable_typestablestypesudts,能够用来查询已衔接的数据库的表和视图的元数据。

以下是一个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_propertiescatalogscolumn_propertiesmaterialized_view_propertiesmaterialized_viewsschema_propertiestable_commentstable_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_truncdate_adddate_difftime等。

  • Hive运用自己的函数称号处理日期和时刻,如from_unixtimeunix_timestampdate_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运用自己的数据类型,例如STRINGINT等。Trino能够经过Hive Connector运用在Hive中界说的表。

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

这儿仅仅针对上篇文章的一些弥补,有任何疑问欢迎给我留言,可重视我大众号【大数据与云原生技术分享】加群交流或私信交流~

【大数据】Presto(Trino)SQL 语法进阶