SQL 文を使用して分析ストア内のデータをクエリする例 - Tablestore

分析ストアのマッピングテーブルを作成すると、SQL 文を使用して時系列データをクエリおよび分析できます。このトピックでは、SQL 文を使用して分析ストア内のデータをクエリする方法の例について説明します。

サンプルシナリオ

あるメーカーには 100,000 台のデバイスがあり、各デバイスは 2 分ごとに 1 つのグループの CPU モニタリングデータを生成します。デバイスの状態の管理と分析を容易にするために、メーカーは収集したデバイスのモニタリングデータをクラウドに保存してビジネスコストを削減し、デバイスの状態を分析することでデバイスの動作を監視します。

このシナリオでは、SQL クエリ機能を使用して、時系列データをすばやくクエリおよび分析できます。デバイスのモニタリングデータは device という名前の Tablestore 時系列テーブルに保存され、時系列テーブル用に作成されたマッピングテーブルの名前は `device::cpu` であるとします。次の図は、時系列テーブル用に作成されたマッピングテーブルのスキーマを示しています。

ステップ 1: 分析ストアのマッピングテーブルを作成する

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	マッピングテーブルの名前。マッピングテーブルを識別するために使用されます。フォーマットは `time_series_table_name::mapping_table_identifier` です。`time_series_table_name` は実際の時間シリーズテーブル名と同じである必要があります。`mapping_table_identifier` は、ビジネス要件に基づいて指定する必要があります。
column_name	列名。SQL の列名は、Tablestore テーブルの列名と同じである必要があります。たとえば、Tablestore テーブルの列名が Aa の場合、SQL の列名は Aa、AA、aA、または aa である必要があります。
data_type	列のデータ型（BIGINT、DOUBLE、BOOL など）。 SQL の列のデータ型は、Tablestore テーブルの列のデータ型と一致する必要があります。データ型のマッピングの詳細については、「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;

ステップ 2: 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]` の形式で関係演算子とともに WHERE 句を使用して、指定された条件を満たすデータをクエリできます。 where_condition を使用する場合は、次のルールに準拠する必要があります。算術演算子と関係演算子で構成される単純式を使用できます。論理演算子で構成される複合式を使用できます。時系列テーブルの _time 列で時間関数を使用して、時間範囲を選択できます。時系列テーブルの _tags 列で時系列関数を使用して、指定されたタグ値を持つデータをクエリできます。
groupby_condition	いいえ	グループ化されたクエリ用の GROUP BY 句。時系列関数で使用できます。 groupby_condition を使用する場合は、次の項目に注意してください。フィールドごとにデータをグループ化できます。時系列関数を使用して、時間ごとにバケット化を実装できます。
order_condition	いいえ	`column_name [ASC \| DESC][,column_name [ASC \| DESC],...]` の形式のソート用の ORDER BY 句。 ASC または DESC を使用して、ソート方法を指定できます。デフォルトのソート方法は昇順（ASC）です。ソート用に複数のフィールドを指定できます。ほとんどの場合、order_condition は LIMIT 句とともに使用して、返される最大行数を指定します。
row_count	いいえ	このクエリで返される最大行数。
offset	いいえ	このクエリのデータオフセット。デフォルトのオフセットは 0 です。

例

例 1: 2023-01-05 05:14:00 から 2023-01-07 09:14:00 までの時間範囲内で、1 日あたりのすべてのデバイスの usage_irq 列の最大値と usage_softirq 列の最大値をクエリします。
重要
unix_timestamp_micros("2023-01-05 05:14:00.000000") のタイムゾーンはシステムタイムゾーンです（中国の場合は UTC + 08:00 北京時間です）。
```
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;
```
例 2: host_50625 と x64 CPU アーキテクチャを持つデバイスのすべての期間の usage_nice 列のすべての値の平均をクエリします。
```
SELECT avg(usage_nice) 
FROM `device::cpu` 
WHERE _data_source = "host_50625" 
AND tag_value_at(_tags,"arch") = "x64";
```
例 3: 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");
```
例 4: 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;
```

例 5: 2023-01-05 05:14:00 から 2023-01-07 09:14:00 までの時間範囲内で、host_50625 を持つすべてのデバイスの 2 時間ごとの最後のポイントにおける 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;

例 6: 2023-01-05 05:14:00 から 2023-01-07 09:14:00 までの時間範囲内で、usage_user 列の値が 100 である場合の時間、月、曜日、時、分、秒、マイクロ秒を出力します。

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;