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

サンプルシナリオ

あるメーカーは 100,000 台のデバイスを保有しており、各デバイスは 2 分ごとに CPU 監視データの一連のデータを生成します。デバイスの状態を管理および分析しやすくするために、メーカーは収集したデバイス監視データをクラウドに保存してビジネスコストを削減し、デバイス状態の分析によってデバイスの稼働状況を監視します。

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

手順1：分析ストアのマッピングテーブルの作成

CREATE TABLE 文を使用して、時系列テーブル用に複数値モデルのマッピングテーブルを作成します。詳細については、「Mapping tables for a time series table in 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 テーブルのカラムのデータ型と一致している必要があります。データ型マッピングの詳細については、「Data type mappings in 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=AnalyticalStore;

手順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 句です。さまざまな条件と組み合わせて、特定の機能を実現できます。WHERE 句を`column_name operator value [AND \| OR] [column_name operator value]`の形式でリレーショナル演算子と組み合わせて使用し、指定した条件を満たすデータをクエリできます。 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までの期間において、全デバイスを対象に、日単位で 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;
```
例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_idle カラムの値の平均、および 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 時間ごとの最新ポイント (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;

例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;