一、概述

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相容。

這裡隻是針對上篇文章的一些補充，有任何疑問歡迎給我留言，可關注我公衆号【大資料與雲原生技術分享】加群交流或私信溝通~

【大資料】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）資料類型

繼續閱讀

根據WADL生成REST風格WebService的用戶端代碼

幾種基于HTTP協定的RPC性能比較

SET XACT_ABORT { ON | OFF }SQL Server 是否自動復原到目前事務文法備注

Restlet 2.0 邊學邊寫（七）Restlet傳回xml和json資料格式

android多個頁面滑動視圖實作

仿照Launcher的Workspace實作左右滑動切換

ROLAP場景下的互動式計算引擎Impala和Presto

解答有關REST的十點疑惑解答有關REST的十點疑惑

解答有關REST的十點疑惑

API Demos 2.2 研讀筆記（2）——Animation

Build RESTful web service using Jersey

Django REST framework開發-3

英國口語縮寫

Facebook官方文檔學習 - 調用接口篇

REST式架構，Roy的博士論文，了解，RESTlet-

rio multi-device用戶端連接配接Rest Server