Exemples d'utilisation d'instructions SQL pour interroger des données dans un magasin analytique - Tablestore

Une fois la table de mappage pour un magasin analytique créée, utilisez des instructions SQL pour interroger et analyser les données de séries temporelles. Cette rubrique présente des exemples d'utilisation d'instructions SQL pour interroger des données dans un magasin analytique.

Scénario d'exemple

Un fabricant dispose de 100 000 appareils générant chacun un groupe de données de surveillance du CPU toutes les deux minutes. Pour faciliter la gestion et l'analyse de l'état des appareils, le fabricant stocke les données de surveillance collectées dans le cloud afin de réduire les coûts opérationnels et surveille le fonctionnement des appareils en analysant leur état.

Dans ce scénario, utilisez la fonctionnalité de requête SQL pour interroger et analyser rapidement les données de séries temporelles. Supposons que les données de surveillance des appareils soient stockées dans la table de séries temporelles Tablestore nommée device, et que la table de mappage créée pour cette table de séries temporelles soit nommée device::cpu. La figure suivante illustre le schéma de la table de mappage créée pour la table de séries temporelles.

Étape 1 : Créer une table de mappage pour un magasin analytique

Créez une table de mappage dans le modèle multi-valeurs pour une table de séries temporelles à l'aide de l'instruction CREATE TABLE. Pour plus d'informations, consultez la section Tables de mappage pour une table de séries temporelles en SQL.

Syntaxe

Remarque

Les champs _m_name, _data_source, _tags et _time sont des configurations fixes. Conservez-les sans modification.

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

Paramètres

Paramètre	Description
table_name	Nom de la table de mappage, utilisé pour l'identifier. Le format est `time_series_table_name::mapping_table_identifier`. `time_series_table_name` doit correspondre au nom réel de la table de séries temporelles. Spécifiez `mapping_table_identifier` selon vos besoins métier.
column_name	Nom de la colonne. Il doit être équivalent à celui de la table Tablestore. Par exemple, si le nom de la colonne dans la table Tablestore est Aa, le nom de la colonne en SQL doit être Aa, AA, aA ou aa.
data_type	Type de données de la colonne, tel que BIGINT, DOUBLE ou BOOL. Le type de données de la colonne en SQL doit correspondre à celui de la colonne dans la table Tablestore. Pour plus d'informations sur les mappages de types de données, consultez la section Mappages de types de données en SQL.
ENGINE	Moteur d'exécution utilisé lors de l'interrogation des données via la table de mappage. Si ce paramètre est défini sur AnalyticalStore, le moteur de magasin analytique est utilisé. Si ce paramètre n'est pas configuré, le moteur de magasin analytique n'est pas utilisé.

Exemple

Créez une table de mappage dans le modèle multi-valeurs nommée device::cpu pour la table de séries temporelles device.

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;

Étape 2 : Interroger des données en exécutant des instructions SQL

Une fois la table de mappage pour un magasin analytique créée, exécutez l'instruction SELECT pour interroger les données dans le magasin analytique. Une latence de synchronisation existe lors du transfert des données de la table de séries temporelles vers le magasin analytique. Lors de l'interrogation des données les plus récentes, certaines peuvent ne pas encore être synchronisées avec le magasin analytique.

Syntaxe

Remarque

La priorité d'exécution des clauses dans l'instruction SELECT est la suivante : clause WHERE > clause GROUP BY > clause ORDER BY > clauses LIMIT et 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}]

Paramètres

Paramètre	Obligatoire	Description
select_expr	Oui	Nom de la colonne ou expression de colonne au format `column_name[, column_name][, column_exp],...`. Utilisez des expressions de colonne pour spécifier les colonnes à interroger. Respectez les règles suivantes : Utilisez le caractère générique astérisque (*) pour interroger toutes les colonnes. Vous pouvez également utiliser la clause WHERE pour spécifier la condition de requête. Spécifiez la colonne à interroger en utilisant son nom. Utilisez des fonctions d'agrégation pour effectuer des statistiques et des analyses. Utilisez des fonctions temporelles pour effectuer un regroupement par temps.
table_references	Oui	Nom de la table de mappage créée pour la table de séries temporelles.
where_condition	Non	Clause WHERE, combinable avec différentes conditions pour implémenter des fonctionnalités spécifiques. Utilisez la clause WHERE avec des opérateurs relationnels au format `column_name operator value [AND \| OR] [column_name operator value]` pour interroger les données répondant aux conditions spécifiées. Lors de l'utilisation de `where_condition`, respectez les règles suivantes : Utilisez des expressions simples construites avec des opérateurs arithmétiques et relationnels. Utilisez des expressions combinées construites avec des opérateurs logiques. Utilisez des fonctions temporelles avec la colonne `_time` de la table de séries temporelles pour sélectionner une plage de temps. Utilisez des fonctions de séries temporelles avec la colonne `_tags` de la table de séries temporelles pour interroger les données ayant les valeurs de tag spécifiées.
groupby_condition	Non	Clause GROUP BY pour les requêtes groupées, utilisable avec des fonctions de séries temporelles. Lors de l'utilisation de `groupby_condition`, tenez compte des éléments suivants : Regroupez les données par champ. Utilisez des fonctions de séries temporelles pour regrouper les données par intervalle de temps.
order_condition	Non	Clause ORDER BY pour le tri, au format `column_name [ASC \| DESC][,column_name [ASC \| DESC],...]`. Utilisez ASC ou DESC pour spécifier la méthode de tri. La méthode de tri par défaut est ascendante (ASC). Spécifiez plusieurs champs pour le tri. En général, `order_condition` est utilisée conjointement avec la clause LIMIT pour spécifier le nombre maximal de lignes à renvoyer.
row_count	Non	Nombre maximal de lignes à renvoyer pour cette requête.
offset	Non	Décalage des données pour cette requête. Le décalage par défaut est 0.

Exemples

Exemple 1 : Interrogez la valeur maximale de la colonne usage_irq et la valeur maximale de la colonne usage_softirq pour tous les appareils, jour par jour, dans la plage de temps allant de 2023-01-05 05:14:00 à 2023-01-07 09:14:00.

Important
Le fuseau horaire de unix_timestamp_micros("2023-01-05 05:14:00.000000") est le fuseau horaire du système (pour la Chine, il s'agit de l'heure de Pékin, 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;
```
Exemple 2 : Interrogez la moyenne de toutes les valeurs de la colonne usage_nice pour toutes les périodes, pour les appareils ayant host_50625 comme source de données et une architecture CPU x64.
```
SELECT avg(usage_nice) 
FROM `device::cpu` 
WHERE _data_source = "host_50625" 
AND tag_value_at(_tags,"arch") = "x64";
```
Exemple 3 : Interrogez le nombre de lignes de données, la moyenne de toutes les valeurs de la colonne usage_user et la moyenne de toutes les valeurs de la colonne usage_system pour tous les appareils, dans la plage de temps allant de 2023-01-05 05:14:00 à 2023-01-07 09:14:00.
```
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");
```
Exemple 4 : Regroupez les données CPU par semaine, puis subdivisez chaque groupe par heure pour calculer l'écart type des valeurs de la colonne usage_user dans chaque sous-groupe.
```
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;
```

Exemple 5 : Interrogez la valeur des colonnes usage_user, usage_system et usage_nice au dernier point de chaque intervalle de deux heures pour tous les appareils ayant host_50625 comme source de données, dans la plage de temps allant de 2023-01-05 05:14:00 à 2023-01-07 09:14:00.

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;

Exemple 6 : Affichez l'heure, le mois, le jour de la semaine, l'heure, la minute, la seconde et la microseconde lorsque la valeur de la colonne usage_user est égale à 100, dans la plage de temps allant de 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;