使用SQL查詢分析儲存資料範例 - Tablestore

在建立分析儲存的映射關係後，您可以使用SQL查詢與分析時序資料。本文介紹分析儲存SQL查詢範例。

範例情境

某廠商有100000台裝置，每台裝置每兩分鐘會產生一組CPU監控資料。為了方便管理和分析裝置狀態，廠商會將採集的裝置監控資料接入到雲端進行儲存來降低業務成本，同時通過分析裝置狀態來監控裝置運行情況。

在該範例情境中，您可以通過SQL查詢功能來快速查詢與分析時序資料。假如裝置的監控資料存放區在Tablestore時序表device中，時序表的映射關係為`device::cpu`，schema如下圖所示。

步驟一：建立分析儲存的映射關係

通過CREATE TABLE語句建立多值模型映射關係。更多資訊，請參見時序表的SQL映射關係。

文法

說明

_m_name、_data_source、_tags和_time欄位為固定配置，保留相應內容即可，無需修改。

CREATE TABLE table_name (
  `_m_name` VARCHAR(1024), 
  `_data_source` VARCHAR(1024), 
  `_tags` VARCHAR(1024), 
  `_time` BIGINT(20),
  `column_name ` data_type, 
  ......
  `user_column_namen ` data_type,
  PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`))ENGINE=AnalyticalStore

參數

參數	說明
table_name	表名，用於唯一標識一張表。格式為`時序表名稱::映射表標識`。其中`時序表名稱`必須與實際時序表名稱相同，`映射表標識`由使用者在建立時自訂。
column_name	列名。SQL中的列名必須和原始表中的列名等效，例如原始表中列名為Aa，在SQL中列名必須使用Aa、AA、aA或者aa中的一個。
data_type	列的資料類型，包含BIGINT、DOUBLE、BOOL等多種資料類型。 SQL中列的資料類型必須和原始表中列的資料類型相匹配。關於資料類型映射的更多資訊，請參見SQL資料類型映射。
ENGINE	使用映射表查詢資料時的執行引擎。當配置此項為AnalyticalStore時，表示流量分析儲存引擎。當未配置此項時，表示不流量分析儲存引擎。

樣本

建立device時序表的多值模型映射關係`device::cpu`。

CREATE TABLE `device::cpu` 
(`_m_name` VARCHAR(1024),
 `_data_source` VARCHAR(1024),
 `_tags` VARCHAR(1024),
 `_time` BIGINT(20), 
 `usage_user` BIGINT(20), 
 `usage_system` BIGINT(20),  
 `usage_idle` BIGINT(20), 
 `usage_nice` BIGINT(20), 
 `usage_iowait` BIGINT(20), 
 `usage_irq` BIGINT(20), 
 `usage_softirq` BIGINT(20), 
 `usage_steal` BIGINT(20),
 `usage_guest` BIGINT(20), 
 `usage_guest_nice` BIGINT(20),
 `one_hour_stamp` BIGINT(20),
 `one_minute_stamp` BIGINT(20),
 PRIMARY KEY(`_m_name`,`_data_source`,`_tags`,`_time`))  ENGINE=AnalyticStore;

步驟二：使用SQL查詢資料

建立映射關係後，使用SELECT語句查詢表中的資料。時序分析儲存寫入資料存在同步延遲，查詢最近資料進行計算時可能會存在部分資料未同步的情況。

文法

說明

SELECT語句中子句的執行優先順序為WHERE子句 > GROUP BY分組查詢 > ORDER BY排序 > LIMIT和OFFSET。

SELECT
 select_expr [, select_expr] ...
 [FROM table_references]
 [WHERE where_condition]
 [GROUP BY groupby_condition]
 [ORDER BY order_condition]
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]

參數

參數	是否必選	說明
select_expr	是	列名或者列運算式，格式為`column_name[, column_name][, column_exp],...`。通過列運算式指定需要查詢的列。使用規則如下：使用星號（*）查詢所有列，支援配合WHERE子句指定查詢條件。使用列名指定查詢的列。配合彙總函式實現資料統計與分析。配合時間函數實現按時間分組。
table_references	是	目標時序表映射關係名稱。
where_condition	否	WHERE子句，可配合不同條件實現相應功能。配合關係運算子查詢符合指定條件的資料，格式為`column_name operator value [AND \| OR] [column_name operator value]`。使用規則如下：支援配合算術運算子、關係運算子等構造的簡單運算式使用。支援配合邏輯運算子構造的組合運算式使用。時序表中的_time列支援配合時間函數實現時間範圍選擇。時序表中的_tags列支援配合時序函數實現指定標籤值查詢。
groupby_condition	否	GROUP BY分組查詢，可配合時序函數使用。使用規則如下：支援按照欄位分組。支援配合時序函數實現對時間進行分桶。
order_condition	否	ORDER BY排序，格式為`column_name [ASC \| DESC][,column_name [ASC \| DESC],...]`。支援使用ASC或者DESC關鍵字設定排序方式。預設按照升序（ASC）排列。支援設定多個欄位進行排序。通常與LIMIT配合使用限定返回的行數。
row_count	否	本次查詢需要返回的最大行數。
offset	否	本次查詢的資料位移量，預設位移量為0。

樣本

樣本一：查詢所有裝置在2023-01-05 05:14:00到2023-01-07 09:14:00時間段內每天的最大usage_irq和最大usage_softirq。
重要
unix_timestamp_micros("2023-01-05 05:14:00.000000")中時間的時區為系統時區（中國為UTC+8北京時間）。
```
SELECT time_bin(_time,"1day"), max(usage_irq),max(usage_softirq) 
FROM `device::cpu` 
WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000") 
GROUP BY 1 ORDER BY 1;
```

樣本二：查詢在host_50625且cpu架構為x64的裝置所有時間平均usage_nice。

SELECT avg(usage_nice) 
FROM `device::cpu` 
WHERE _data_source = "host_50625" 
AND tag_value_at(_tags,"arch") = "x64";

樣本三：查詢所有裝置在2023-01-05 05:14:00到2023-01-07 09:14:00時間段內資料行數以及平均usage_user和平均usage_system。

SELECT count(*),avg(usage_idle),avg(usage_system) 
FROM `device::cpu` 
WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000");

樣本四：將cpu資料每周分組，對每組資料按照每小時再分組，計算每個分組中usage_user的標準差。

SELECT week(from_unixtime_micros(_time)) as week,time_bin(_time,"1h"), stddev(usage_user) 
FROM `device::cpu` 
GROUP BY 1,2 ORDER BY 1,2;

樣本五：查詢在host_50625的所有裝置在2023-01-05 05:14:00到2023-01-07 09:14:00時間段內每兩小時最後時刻（lastpoint）的usage_user,usage_system以及usage_nice。

SELECT time_bin(_time,"2h"), max_by(usage_user,_time),
max_by(usage_system,_time),max_by(usage_nice,_time) 
FROM `device::cpu` 
WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000")
AND _data_source = 'host_50625'
GROUP BY 1 ORDER BY 1;

樣本六：輸出usage_user為100時在2023-01-05 05:14:00到2023-01-07 09:14:00時間段內的時間資料、月份名稱、星期名稱、小時、分鐘、秒以及微秒。

SELECT from_unixtime_micros(_time) as time, 
monthname(from_unixtime_micros(_time)) as monthname,
dayname(from_unixtime_micros(_time)) as dayname,
hour(from_unixtime_micros(_time)) as hour,
minute(from_unixtime_micros(_time)) as minute,
second(from_unixtime_micros(_time)) as second,
microsecond(from_unixtime_micros(_time)) as microsecond 
FROM `device::cpu` 
WHERE _time > unix_timestamp_micros("2023-01-05 05:14:00.000000") 
AND _time < unix_timestamp_micros("2023-01-07 09:14:00.000000") 
AND usage_user = 100 LIMIT 100;