1.前言

阿里時序時空數據庫TSDB最新推出TSQL，支持標準SQL的語法和函數。用戶使用熟悉的SQL，不僅僅查詢更簡單易用，用戶還可以利用SQL強大的功能，實現更加復雜的計算分析。

2. 為什么需要用SQL做時序查詢？

2.1 SQL擁有廣泛用戶基礎

SQL作為一個誕生于上世紀70年代的編程語言已經存在幾十年了。這是一個相對而言較“古老”的編程語言，但又是一個有著廣泛用戶基礎的語言。
在跟蹤主要編程語言的流行程度的TIOBE index[1]中，SQL在2019年4月份的排名是第8。而如果把排名列在11-20之間的SQL的兩個“兄弟”PL/SQL, Transact-SQL也合并進來的話，SQL的流行度應該更高。

?

根據stackoverflow網站的調查 [2]，SQL在最流行的編程語言榜上排在第4位。

?

無論TIOBE index還是stackoverflow的編程語言排行榜，都從一個側面反映了SQL的廣泛用戶基礎。作為一個查詢語言，SQL是用戶和數據庫系統交互的(直接或間接)主要方式。支持一個擁有廣泛用戶基礎的查詢語言，對于推廣數據庫系統來說，是非常重要的。

2.2 用戶學習成本

最近幾年出現的幾個主要面向時序場景的數據庫，除了TimescaleDB是在Postgres基礎上所以支持PG生態包括SQL語言支持，其他幾個比如InfluxDB, OpenTSDB, Prometheus都有各自不同的查詢語言和接口：InfluxDB有InfluxQL，OpenTSDB有自己的Restful API, 而Prometheus有PromQL。每一個系統都可以聲稱自己的語言是獨一無二的，更適合時序查詢這樣的場景；但不可否認的事實是用戶需要去花時間去學習一種新的語言，并且如果這個語言為了功能完善，還在不斷演進中，這樣的學習成本對用戶來說，尤其顯得高了。
舉個例子，InfluxDB的InfluxQL并不支持Join，Subqueries, 以及SQL中很常見的UDF等功能，這意味著用戶并不能在不同數據之間進行關聯分析計算，也不能在系統函數基礎上進行擴展開發。InfluxDB設計者在聽到社區的意見后，做了一個很有“創意”的事情：在新版本里支持Join，UDF等功能，但并不是讓InfluxQL變得更加接近于SQL，而是在一個全新的Flux(一個新的functional scripting language)里支持 [3]。用戶想要做InfluxQL不能做的事情，那就再來學習一個新語言吧。
一個很有意思的事情，10多年前開始出現的NoSQL系統，比如MapReduce/Hadoop, BigTable,Casandra，HBase等，一開始也是以各自不同的查詢語言出現的。在經歷了多年用戶推廣之后，NoSQL開始擁抱SQL，變成了NotOnlySQL或者NewSQL。時序數據庫這樣一個新興的數據庫領域，也有可能重復這樣的歷史。原因很簡單，用戶學習一個新語言的成本越高，越會阻礙一個系統被推廣到大眾接受的程度。

2.3 BI工具生態支持

時序數據庫提供SQL的查詢支持，一個很重要的原因是將時序數據庫的應用場景擴展到商業分析(BI/Business Analysis)，商業決策這樣高附加值領域。
當前幾個主要的時序數據庫，包括InfluxDB, OpenTSDB和Prometheus，主要側重于基礎性能監控這樣的場景，利用Grafana這樣的可視化工具，實現監控報警這一類基本功能。另一方面，監控報警還沒有充分利用挖掘時序數據的商業價值。進一步的功能，需要充分利用現有SQL生態系統中的商業分析工具，比如Tableau, Qlik，Oracle BI, IBM Cognos等。這些BI工具，往往是以SQL的方式同后端數據庫交互。從這個角度來說，時序數據庫的SQL支持對于對接BI生態系統中的各種工具，尤為重要。

2.4 TSQL面向的用戶群

在阿里時序數據庫TSDB支持的兼容OpenTSDB查詢協議之上推出的TSQL查詢引擎，主要是面向以下兩類用戶：

**- 時序數據庫TSDB的新應用開發者?
**：這類用戶往往以前使用關系數據庫，因為關系數據庫本身處理時序數據的性能和可擴展性的局限，而轉而使用TSDB。這些新應用開發者，希望TSDB在提供比關系數據庫更好的時序性能和擴展性的同時，能夠用他們以前熟悉的查詢語言進行應用開發，而不是去學習一個新的查詢語言。
**- 數據分析師：
**這類用戶并不開發應用，他們的工作是利用已有的商業分析工具，對時序數據進行進一步的查詢分析。他們本身并不直接使用SQL， 但所使用的工具以SQL作為和時序數據庫TSDB交互的查詢語言。

3. 現有時序數據庫系統SQL查詢能力比較

這里簡單對比時序數據庫系統中提供SQL查詢，或SQL-like查詢能力的InfluxDB, TimescaleDB, 阿里云TSDB。

?

4. TSQL系統架構

?

上圖是TSQL的總體架構以及和TSDB引擎和存儲之間的協調工作關系。簡單來講，TSQL是一個典型的MPP的SQL分析引擎，通過Connector同TSDB引擎和存儲進行數據交換。Connector支持MetaAPI和DataAPI。

TSQL是在兩個Apache開源項目基礎上演進開發的：

• Apache Calcite作為SQL的解析器和計劃生成和優化器。
• Apache Drill提供分布式的SQL執行層。
  Apache Calcite作為一個擴展性好，支持標準SQL語法和語義的SQL計劃生成器，已經被很多數據處理相關的開源項目使用[6]，包括大數據ETL的Apache Hive, HBase上的SQL解決方案Apache Phoenix, 也有流數據處理框架Apache Fink (阿里的Blink)和Apache Beam等。 TSQL使用Calcite作為SQL計劃生成器，可以在兼容標準SQL方面，充分利用開源社區已有的成果。

4.1 時序數據Schema管理

InfluxDB, OpenTSDB和Prometheus都采用的是一種Schema-on-write的方式，也就是用戶并不需要明確定義metric的schema, 而是將schema的信息隱藏在數據中，在數據寫入的時候，同時管理著schema。這樣做的好處是更高的靈活性：

• 在寫入數據的時候，用戶不需要事先必須用Create Table DDL來創建table；
• 在時序數據tag set出現變化的時候，用戶不需要事先用Alter Table來修改table的schema。

TimeScaleDB從PG上擴展而來，所以是采用的是嚴格的Schema的管理方式。在使用靈活性方面，不如上面其他3個時序數據庫。

Calcite作為一個SQL計劃生成器，很適合時序數據庫這樣的比較松散的Schema管理方式。 Calcite的Schema Adapter，可以支持

動態的Schema 發現，

任意一個數據集，只要實現Schema管理中的接口API, 就可以在計劃解析生成階段被當成一個Table來處理。

TSQL在Calcite的Schema Adapter基礎上，利用TSDB引擎中新增加的MetaAPI，來完成SQL計劃解析和生成。這免去了用戶必須事先在一個集中式的catalog中預先定義Table DDL等繁瑣工作，給用戶帶來了很多的靈活性。

4.2 時序數據查詢執行

TSQL的執行層，利用了Apache Drill的runtime execution。Drill的runtime execution，具備以下特點

• 利用off-heap內存作為計算內存，減少Java heap內存GC所帶來的延遲問題
• 基于Columnar格式的ValueVector (Apache Arrow的前身)，提升查詢執行效率
• 動態代碼生成和編譯
  UDF支持

5. TSQL時序查詢功能

我們以一個基礎性能監控場景來舉例說明TSQL能完成的時序查詢功能。利用一個時序數據庫業界公開的時序性能Benchmark[5] 生成的模擬數據，按照DevOps這樣的場景，產生了cpu相關的10不同的metric。每個metric對應了機房(datecenter)，主機(hostname)，rack等標簽下所采集的服務器cpu相關的指標數據。

5.1 元數據查詢

可以用下面的方式查詢TSDB中所有的metric/table

SHOW TABLES FROM tsdb

如果我們希望列出所有以cpu為前綴的metric/table，可以在上面的查詢基礎之上添加附帶過濾條件.

show TABLES from tsdb where TABLE_NAME like 'cpu%'

下圖給出了命令的部分輸出:

?

在獲得metric/table 名字后，我們可以進一步用SQL中的'DESCRIBE'命令來查詢這個metric/table的schema信息

describe tsdb.`cpu.usage_user`

下圖顯示了上面的'describe'命令的部分結果：

?

5.2 時序數據簡單查詢

用下面的SQL查詢可以獲得指定時間段內的'cpu.usage_user'的指標值，時間戳，以及對應的標簽值。

select * from tsdb.`cpu.usage_user` where `timestamp` between '2019-05-01 16:00:00' and '2019-05-01 18:00:00'

這里， 將被轉換成 metric/table下所有的列，包括指標值，時間戳，所有的標簽列。可以以具體的列名的一個列表來代替。
作為對比，如果把上面的查詢轉化成OpenTSDB協議來查詢，相對應的查詢如下：

{"start": "1556726400000","end": "1556733600000","queries": [{"aggregator": "none","metric": "cpu.usage_user","rate": null,"downsample": null,"filters": []}] }

可以在時間戳的過濾條件基礎上，增加指標列上的條件。下面的查詢，列出指定時間段內，3臺主機上的指標值，并且使用limit, 把查詢結果限制在100行。

select * from tsdb.`cpu.usage_user` where `timestamp` between '2019-05-01 16:00:00' and '2019-05-01 18:00:00' and hostname in ('host_1', 'host_5', 'host_10') limit 100

可以在查詢中使用標準SQL中豐富的數值計算函數，字符串函數或時間戳函數。下面的SQL，我們分別使用了數值運算函數sqrt, 時間戳函數extract 和字符串lower。

5.3 時序降精度，聚合運算

如果我們要計算兩小時之內，每臺主機上每5分鐘的指標cpu.usage_user的最大值，最小值，以及數據采樣點的個數。這樣的查詢，代表了在時間維度上的降精度，并且在標簽hostname上進行的聚合運算。用TSQL來表示這樣的查詢：

selecthostname,tumble(`timestamp`, interval '5' minute) ts,max(`value`) maxV,min(`value`) minV,count(`value`) cntfrom tsdb.`cpu.usage_user`where `timestamp` between 1556726400000 and 1556733600000 and hostname in ('host_8','host_5','host_6') group by hostname, ts

如果用OpenTSDB的協議來查詢:

{"start": "1556726400000","end": "1556733600000","queries": [{"aggregator": "max","metric": "cpu.usage_user","downsample": "5m-max","tags":{"hostname":"host_8|host_5|host_6"}},{"aggregator": "min","metric": "cpu.usage_user","downsample": "5m-min","tags":{"hostname":"host_8|host_5|host_6"}},{"aggregator": "sum","metric": "cpu.usage_user","rate": null,"downsample": "5m-count","tags":{"hostname":"host_8|host_5|host_6"}}] }

可以看到，相比較原來Restful API的查詢，TSQL能夠用更簡潔的方式來表示相同的查詢語義；并且，如果用戶本來就熟悉SQL的使用方法，節省用戶去學習Restfule API里JSON各個字段的含義。從降低用戶學習成本，增加易用性這個角度，TSQL帶來了較明顯的價值。

TSQL不僅僅帶來查詢簡潔，用戶易用的優點，并且，更重要的是，用TSQL能夠表達Restful API里不能直接表達的查詢語義。在TSDB引入TSQL之前，如果用戶需要進行這樣的查詢計算，則用戶必須通過自己的應用程序，在Restful API獲得數據后，再進行后計算，來滿足業務需要。在自己的應用程序中進行后計算，往往需要付出很大的應用開發代價。

5.4 聚合后計算，過濾，排序

下面的例子，計算2個小時內，3臺機器上每5分鐘內，cpu.usage_user指標值的最大值和最小值的差異超過10.0的時段和hostname, 并按照差異值從大到小排序：
在上面的例子中個，在獲得最大值和最小值后，進一步計算兩者的差異值，并根據差異值進行過濾和排序。這樣的聚合后計算處理，無法用OpenTSDB的查詢協議表示；用戶如果要表達這樣的語義，就必須在應用程序中計算。

selecthostname,tumble(`timestamp`, interval '5' minute) ts,max(`value`) - min(`value`) as diffVfrom tsdb.`cpu.usage_user`where `timestamp` between '2019-05-01 16:00:00' and '2019-05-01 18:00:00' and hostname in ('host_1', 'host_5', 'host_10') group by hostname, ts HAVING diffV > 10.0 order by diffV DESC

5.5 任意復雜的條件表達式

TSDB的Restful API對于只提供有限的幾種filter, 而并不支持任意filter通過AND/OR的組合。比如下面的例子，是一個TSQL業務中使用的查詢。其中WHERE條件部分是并不能用Restful API來表示的，因為Restful下的filters是只有AND, 而OR只有在相同tag上通過'value1|value2|vale3'這樣的形式來表達。

where((obj_id='ems30_NA62_183249003' and obj_type='ems30_NA62_20204' and room='ems30_NA62_C-T01.NA62' and building='ems30_NA62_C') or(obj_id='ems30_NA62_183249746' and obj_type='ems30_NA62_20204' and room='ems30_NA62_C-T01.NA62' and building='ems30_NA62_C') or(obj_id='ems30_NA62_183246962' and obj_type='ems30_NA62_20204' and room='ems30_NA62_C-T01.NA62' and building='ems30_NA62_C') or(obj_id='ems30_NA62_183248143' and obj_type='ems30_NA62_20204' and room='ems30_NA62_C-T01.NA62' and building='ems30_NA62_C') or(obj_id='ems30_NA62_183249191' and obj_type='ems30_NA62_20204' and room='ems30_NA62_C-T01.NA62' and building='ems30_NA62_C') or(obj_id='ems30_NA62_183249964' and obj_type='ems30_NA62_20204' and room='ems30_NA62_C-T01.NA62' and building='ems30_NA62_C') or(obj_id='ems30_NA62_183247148' and obj_type='ems30_NA62_20204' and room='ems30_NA62_C-T01.NA62' and building='ems30_NA62_C')) and `timestamp` between '2019-04-25 18:20:21' and '2019-04-25 18:20:31'...

支持任意組合的AND/OR的條件表達式，對于應用開發是很有意義的。在集團基礎監控業務(raptor-pro)中，一個突出的亮點是“定制化監控報警”：允許業務方的用戶來定制查詢條件，并且查詢條件可以是任意的AND/OR組合。TSQL為"定制化監控報警"的功能實現，提供了有力的技術保障。

5.6 多個metric之間join

這個查詢，把cpu.usage_system和cpu.usage_idle在hostname和timestamp上做等值join, 然后計算每5分鐘兩個度量值之和的sum。

select t1.hostname, tumble(t1.`timestamp`, interval '5' minute ) ts, sum(t1.`value` + t2.`value`) as sumV from tsdb.`cpu.usage_system` t1, tsdb.`cpu.usage_idle` t2 where t1.`timestamp` >='2019-05-01' and t1.`timestamp` <= '2019-05-01 01:00:00' and t1.hostname = t2.hostnameand t1.`timestamp`= t2.`timestamp` group by t1.hostname, ts

上面的查詢，如果我們采用TSDB的多值模型，把cpu.usage_system和cpu.usage_idle處理成一個metric的不同的field, 則不需要join就可以完成。但如果我們需要在分組聚合后的結果上再做join, 多值模型也無法解決問題。

5.7 分組聚合后join計算

下面的查詢，分別對cpu.usage_system和cpu.usage_idel按照5分鐘計算聚合函數sum(), 再通過join, 對齊，計算相對應的比例。并且，每個子查詢的Where條件，除了包括在tag上和時間戳上的條件，還包括值上的過濾條件。
類似這樣的查詢，是無法直接在TSDB的RestAPI來實現的；用戶只能在自己的應用程序中實現，增加了應用開發成本。

select f0.hostname, f0.ts, f0.sumV / f1.sumV as resultValue from (select hostname,tumble(`timestamp`, interval '5' minute) ts, sum(`value`) as sumVfrom tsdb.`cpu.usage_system`wherehostname in ('host_0', 'host_5', 'host_10') and`timestamp` between '2019-05-01 00:00:00' and '2019-05-01 01:00:00' and `value`<=50group by hostname, ts) as f1 join (select hostname,tumble(`timestamp`, interval '5' minute ) ts, sum(`value`) as sumVfrom tsdb.`cpu.usage_idle`wherehostname in ('host_0', 'host_5', 'host_10') and`timestamp` between '2019-05-01 00:00:00' and '2019-05-01 01:00:00' and `value`<=30group by hostname, ts) as f0 on f1.hostname = f0.hostname and f1.ts = f0.ts

5.8 UDF擴展功能

使用UDF來擴展功能，對于時序數據庫這樣聚焦特定領域的數據庫來說，是非常必要的，因為往往SQL標準中定義的函數，并不能完全滿足需要。TSQL有一個完善的UDF的體系，用戶只要按照約定的接口，用Java語義就可以實現擴展。比如，我們在TSQL中引入的把時間戳分割成不重合的窗口的函數tumble，其實現就是由下面不到15行代碼完成。
用戶可以用Java實現不同的scalar UDF或者aggregate UDF, 并把編譯后的jar加入到TSQL的系統類庫目錄，就可以自行擴展TSQL的查詢計算功能了。

@FunctionTemplate(name = "tumble", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)public static class Tumble implements DrillSimpleFunc {@Param TimeStampHolder timeStamp;@Param IntervalDayHolder interval;@Output TimeStampHolder out;@Overridepublic void setup() {}@Overridepublic void eval() {long intervalMs = interval.days * org.apache.drill.exec.vector.DateUtilities.daysToStandardMillis + interval.milliseconds;out.value = timeStamp.value - timeStamp.value % intervalMs;}}

6.TSQL可視化查詢

阿里云TSDB已經提供了TSQL可視化交互式開發功能，通過web頁面可以方便的進行TSQL的測試和開發，如下圖Demo所示。

原文鏈接
本文為云棲社區原創內容，未經允許不得轉載。

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的通过SQL即可让监控分析更简单更高效的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。