*L'auteur a choisi le Computer History Museum comme bénéficiaire d'une donation dans le cadre du programme Write for Donations.*

Introduction

De nombreuses apps, telles que les systèmes de surveillance et les systèmes de collecte de données, permettent d'accumuler des données en vue d'une analyse plus approfondie. Ces analyses portent souvent sur la façon dont une donnée ou un système évolue dans le temps. Dans ces cas, les données sont représentées sous la forme d'une série chronologique, chaque point de données étant accompagné d'un horodatage. Voici un exemple :

				
					
&lt;^&gt;2019-11-01 09:00:00&lt;^&gt; server.cpu.1 0.9

&lt;^&gt;2019-11-01 09:00:00&lt;^&gt; server.cpu.15 0.8

&lt;^&gt;2019-11-01 09:01:00&lt;^&gt; server.cpu.1 0.9

&lt;^&gt;2019-11-01 09:01:00&lt;^&gt; server.cpu.15 0.8

...

La pertinence des données de séries chronologiques a récemment augmenté grâce aux nouveaux déploiements de l'Internet des Objets (IoT) et de l'Internet Industriel des Objets. Il existe de plus en plus d'appareils qui recueillent diverses informations sur les séries chronologiques : traqueurs de forme physique, montres intelligentes, stations météo domestiques et divers capteurs, pour n'en citer que quelques-uns. Ces appareils collectent beaucoup d'informations, et toutes ces données doivent être stockées quelque part.

Les bases de données relationnelles classiques sont le plus souvent utilisées pour stocker des données, mais elles ne conviennent pas toujours lorsqu'il s'agit des énormes volumes de données des séries chronologiques. Lorsque vous devez traiter une grande quantité de données de séries chronologiques, les bases de données relationnelles peuvent être trop lentes. C'est pourquoi des bases de données spécialement optimisées, appelées bases de données NoSQL, ont été créées pour éviter les problèmes des bases de données relationnelles.

TimescaleDB est une base de données open source optimisée pour le stockage de données de séries chronologiques. Elle est implémentée comme extension de PostgreSQL et combine la facilité d'utilisation des bases de données relationnelles et la rapidité des bases de données NoSQL. Par conséquent, elle vous permet d'utiliser PostgreSQL à la fois pour stocker des données commerciales et des données de séries chronologiques en un seul endroit.

En suivant ce tutoriel, vous allez installer TimescaleDB sur CentOS 7, le configurer et apprendre à travailler avec lui. Vous apprendrez à créer des bases de données de séries chronologiques et à effectuer des requêtes simples. Enfin, vous apprendrez comment supprimer les données inutiles.

Conditions préalables

Pour suivre ce tutoriel, vous aurez besoin de :

Un serveur CentOS 7 configuré en suivant notre guide Configuration initiale du serveur avec CentOS 7, comprenant un utilisateur non root avec des privilèges sudo et un pare-feu configuré avec firewalld. Pour implémenter firewalld, consultez la section "Configuration d'un pare-feu de base" du tutoriel Étapes complémentaires recommandées pour les nouveaux serveurs CentOS 7.

PostgreSQL installé sur votre serveur. Suivez notre guide Comment installer et utiliser PostgreSQL sur CentOS 7 pour l'installer et le configurer.

Étape 1 - Installation de TimescaleDB

TimescaleDB n'est pas disponible dans les référentiels de package par défaut de CentOS. Au cours de cette étape, vous l'installerez donc à partir du dépôt tiers de TimescaleDB.

Tout d'abord, créez un nouveau fichier de référentiel :

				
					
sudo vi /etc/yum.repos.d/timescaledb.repo

Entrez en mode insertion en appuyant sur i et copiez la configuration suivante dans le fichier :

				
					
[label /etc/yum.repos.d/timescaledb.repo]

[timescale_timescaledb]

name=timescale_timescaledb

baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch

repo_gpgcheck=1

gpgcheck=0

enabled=1

gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey

sslverify=1

sslcacert=/etc/pki/tls/certs/ca-bundle.crt

metadata_expire=300

Lorsque vous avez terminé, appuyez sur ESC pour quitter le mode insertion, puis sur :wq et ENTER pour enregistrer et quitter le fichier. Pour en savoir plus sur l'éditeur de texte vi et son successeur vim, consultez notre tutoriel Installation et utilisation de l'éditeur de texte Vim sur un serveur Cloud.

Vous pouvez maintenant passer à l'installation. Ce tutoriel utilise PostgreSQL version 11 ; si vous utilisez une version différente de PostgreSQL (9.6 ou 11, par exemple), remplacez la valeur dans la commande suivante et exécutez-la :

				
					
sudo yum install -y timescaledb-postgresql-&lt;^&gt;11&lt;^&gt;

TimescaleDB est maintenant installé et prêt à être utilisé. Ensuite, vous allez l'activer et ajuster certains des paramètres qui lui sont associés dans le fichier de configuration PostgreSQL afin d'optimiser la base de données.

Étape 2 - Configurez TimescaleDB

Le module TimescaleDB fonctionne bien avec les paramètres de configuration par défaut de PostgreSQL, mais pour améliorer les performances et faire un meilleur usage des ressources du processeur, de la mémoire et du disque, les développeurs de TimescaleDB suggèrent de configurer certains paramètres individuels. Cette configuration peut être effectuée automatiquement avec l'outil timescaledb-tune ou en éditant manuellement le fichier postgresql.conf de votre serveur.

Dans ce tutoriel, vous utiliserez l'outil timescaledb-tune. Il lit le fichier postgresql.conf et suggère de manière interactive d'apporter des changements.

Exécutez la commande suivante pour démarrer l'assistant de configuration :

				
					
sudo timescaledb-tune --pg-config=/usr/pgsql-&lt;^&gt;11&lt;^&gt;/bin/pg_config

Tout d'abord, il vous sera demandé de confirmer le chemin d'accès au fichier de configuration PostgreSQL :

				
					
[secondary_label Output]

Using postgresql.conf at this path:

/var/lib/pgsql/&lt;^&gt;11&lt;^&gt;/data/postgresql.conf



Is this correct? [(y)es/(n)o]:

L'utilitaire détecte automatiquement le chemin d'accès au fichier de configuration. Il faut donc le confirmer en entrant y :

				
					
[secondary_label Output]

...

Is this correct? [(y)es/(n)o]: &lt;^&gt;y&lt;^&gt;

Writing backup to:

/tmp/timescaledb_tune.backup&lt;^&gt;201912191633&lt;^&gt;

Ensuite, activez le module TimescaleDB en tapant y au message-guide suivant et en appuyant sur ENTER :

				
					
[secondary_label Output]

shared_preload_libraries needs to be updated

Current:

#shared_preload_libraries = ''

Recommended:

shared_preload_libraries = 'timescaledb'

Is this okay? [(y)es/(n)o]: &lt;^&gt;y&lt;^&gt;

success: shared_preload_libraries will be updated

En fonction des caractéristiques de votre serveur et de la version de PostgreSQL, il vous sera alors proposé de régler vos paramètres. Appuyez sur y pour lancer le processus de réglage :

				
					
[secondary_label Output]

Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: &lt;^&gt;y&lt;^&gt;

Recommendations based on &lt;^&gt;7.64&lt;^&gt; GB of available memory and &lt;^&gt;4&lt;^&gt; CPUs for PostgreSQL &lt;^&gt;11&lt;^&gt;



Memory settings recommendations

Current:

shared_buffers = 128MB

#effective_cache_size = 4GB

#maintenance_work_mem = 64MB

#work_mem = 4MB

Recommended:

shared_buffers = &lt;^&gt;1955MB&lt;^&gt;

effective_cache_size = &lt;^&gt;5865MB&lt;^&gt;

maintenance_work_mem = &lt;^&gt;1001121kB&lt;^&gt;

work_mem = &lt;^&gt;5005kB&lt;^&gt;

Is this okay? [(y)es/(s)kip/(q)uit]:

timescaledb-tune détectera automatiquement la mémoire disponible du serveur et calculera les valeurs recommandées pour les paramètres shared_buffers, effective_cache_size, maintenance_work_mem et work_mem. Si vous souhaitez en savoir plus sur la façon dont il fonctionne, consultez la page GitHub pour timescaledb-tune.

Si ces paramètres semblent OK, tapez y :

				
					
[secondary_label Output]

...

Is this okay? [(y)es/(s)kip/(q)uit]: &lt;^&gt;y&lt;^&gt;

success: memory settings will be updated

À ce stade, si votre serveur dispose de plusieurs processeurs ; vous trouverez les recommandations pour les paramètres de parallélisme. Cependant, si vous n'avez qu'un processeur, timescaledb-tune vous renverra directement vers les paramètres WAL.

Ceux qui disposent de plusieurs processeurs rencontreront des recommandations de ce type :

				
					
[secondary_label Output]

Parallelism settings recommendations

Current:

missing: timescaledb.max_background_workers

#max_worker_processes = 8

#max_parallel_workers_per_gather = 2

#max_parallel_workers = 8

Recommended:

timescaledb.max_background_workers = 8

max_worker_processes = 15

max_parallel_workers_per_gather = 2

max_parallel_workers = 4

Is this okay? [(y)es/(s)kip/(q)uit]:

Ces paramètres régulent le nombre de *travailleurs* qui traitent des demandes et des tâches de base. Vous pouvez en apprendre davantage sur ces paramètres grâce à la documentation TimescaleDB et PostgreSQL.

Tapez y et ENTER pour accepter ces paramètres.

				
					
[secondary_label Output]

...

Is this okay? [(y)es/(s)kip/(q)uit]: &lt;^&gt;y&lt;^&gt;

success: parallelism settings will be updated

Ensuite, vous trouverez des recommandations pour Write Ahead Log (WAL):

				
					
[secondary_label Output]

WAL settings recommendations

Current:

#wal_buffers = -1

#min_wal_size = 80MB

#max_wal_size = 1GB

Recommended:

wal_buffers = &lt;^&gt;16MB&lt;^&gt;

min_wal_size = &lt;^&gt;4GB&lt;^&gt;

max_wal_size = &lt;^&gt;8GB&lt;^&gt;

Is this okay? [(y)es/(s)kip/(q)uit]:

WAL préserve l'intégrité des données, mais les paramètres par défaut peuvent entraîner des E/S inefficaces qui ralentissent les performances d'écriture. Tapez et entrez y pour optimiser ces paramètres :

				
					
[secondary_label Output]

...

Is this okay? [(y)es/(s)kip/(q)uit]: &lt;^&gt;y&lt;^&gt;

success: WAL settings will be updated

Vous trouverez quelques recommandations diverses :

				
					
[secondary_label Output]

Miscellaneous settings recommendations

Current:

#default_statistics_target = 100

#random_page_cost = 4.0

#checkpoint_completion_target = 0.5

#max_locks_per_transaction = 64

#autovacuum_max_workers = 3

#autovacuum_naptime = 1min

#effective_io_concurrency = 1

Recommended:

default_statistics_target = &lt;^&gt;500&lt;^&gt;

random_page_cost = &lt;^&gt;1.1&lt;^&gt;

checkpoint_completion_target = &lt;^&gt;0.9&lt;^&gt;

max_locks_per_transaction = &lt;^&gt;64&lt;^&gt;

autovacuum_max_workers = &lt;^&gt;10&lt;^&gt;

autovacuum_naptime = &lt;^&gt;10&lt;^&gt;

effective_io_concurrency = &lt;^&gt;200&lt;^&gt;

Is this okay? [(y)es/(s)kip/(q)uit]:

Tous ces paramètres visent à accroître les performances. Par exemple, les DSS peuvent traiter de nombreuses demandes simultanées, de sorte que la meilleure valeur pour la effective_io_concurrency pourrait se situer dans les centaines. Vous pouvez trouver plus d'informations sur ces options dans la documentation PostgreSQL.

Appuyez sur y puis sur ENTER pour continuer.

				
					
[secondary_label Output]

...

Is this okay? [(y)es/(s)kip/(q)uit]: &lt;^&gt;y&lt;^&gt;

success: miscellaneous settings will be updated

Saving changes to: /var/lib/pgsql/&lt;^&gt;11&lt;^&gt;/data/postgresql.conf

En conséquence, vous obtiendrez un fichier de configuration prêt à l'emploi à l'adresse /var/lib/pgsql/<^>11<^>/data/postgresql.conf.

Remarque : si vous effectuez l'installation le début, vous pouvez également exécuter la commande initiale avec les drapeaux --quiet et --yes, ce qui aura pour conséquence d'appliquer automatiquement toutes les recommandations et effectuera les modifications souhaitées au fichier de configuration postgresql.conf.

				
					
sudo timescaledb-tune --pg-config=/usr/pgsql-&lt;^&gt;11&lt;^&gt;/bin/pg_config --quiet --yes

Pour que les changements de configuration prennent effet, vous devez redémarrer le service PostgreSQL :

				
					
sudo systemctl restart postgresql-&lt;^&gt;11&lt;^&gt;.service

La base de données fonctionne désormais avec des paramètres optimaux et est prête à travailler avec les données des séries chronologiques. Dans les étapes suivantes, vous allez essayer de travailler avec ces données : créer de nouvelles bases de données et de nouvelles hypertables, et effectuer des opérations.

Étape 3 - Créez une nouvelle base de données et une nouvelle hypertable

Grâce à l'optimisation de la configuration de votre TimecaleDB, vous êtes prêt à travailler avec des données de séries chronologiques. TimescaleDB est implémenté comme une extension de PostgreSQL, de sorte que les opérations avec des données de séries chronologiques ne sont pas très différentes des opérations avec des données relationnelles. En même temps, la base de données vous permet de combiner librement les données des séries chronologiques et des tableaux relationnels à l'avenir.

Tout d'abord, vous allez créer une nouvelle base de données et activer l'extension TimescaleDB pour celle-ci. Connectez-vous à votre base de données PostgreSQL :

				
					
sudo -u postgres psql

Créez maintenant une nouvelle base de données et connectez-vous à celle-ci. Ce tutoriel appellera la base de données <^>timeseries<^> :

				
					
CREATE DATABASE &lt;^&gt;timeseries&lt;^&gt;;

\c &lt;^&gt;timeseries&lt;^&gt;

Vous pouvez trouver des informations supplémentaires sur le fonctionnement d'une base de données PostgreSQL dans notre tutoriel Comment créer, supprimer et gérer des tables dans PostgreSQL sur un serveur Cloud.

Enfin, activez l'extension TimescaleDB :

				
					
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Vous verrez le résultat suivant :

				
					
[secondary_label Output]

WARNING:

WELCOME TO

 _____ _ _ ____________

|_ _(_) | | | _ \ ___ \

 | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /

 | | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \

 | | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /

 |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/

 Running version 1.5.1

For more information on TimescaleDB, please visit the following links:



 1. Getting started: https://docs.timescale.com/getting-started

 2. API reference documentation: https://docs.timescale.com/api

 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture



Note: TimescaleDB collects anonymous reports to better understand and assist our users.

For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.



CREATE EXTENSION

Le principal point d'interaction avec vos données de séries chronologiques sont les hypertables, une abstraction de nombreux tableaux individuels contenant les données, appelés "chunks".

Pour créer une hypertable, commencez par une table SQL normale et convertissez-la ensuite en hypertable via la fonction create_hypertable.

Créez une table qui permettra de stocker des données pour suivre la température et l'humidité dans un ensemble d'appareils au fil du temps :

				
					
CREATE TABLE conditions (

 time TIMESTAMP WITH TIME ZONE NOT NULL,

 device_id TEXT,

 temperature NUMERIC,

 humidity NUMERIC

);

Cette commande créera une table appelée conditions avec quatre colonnes. La première colonne enregistrera l'horodatage, qui comprend le fuseau horaire et ne peut être vide. Ensuite, vous utiliserez la colonne temporelle pour transformer votre table en une hypertable qui est partitionnée par le temps :

				
					
SELECT create_hypertable('conditions', 'time');

Cette commande appelle la fonction create_hypertable(), qui crée une hyper table TimescaleDB à partir d'une table PostgreSQL en remplaçant cette dernière.

Vous recevrez le résultat suivant :

				
					
[secondary_label Output]

 create_hypertable

-------------------------

 (1,public,conditions,t)

(1 row)

Dans cette étape, vous avez créé une nouvelle hypertable pour stocker les données des séries chronologiques. Vous pouvez maintenant la remplir de données en écrivant dans l'hypertable, puis en la supprimant.

Étape 4 - Rédigez et supprimez des données

Au cours de cette étape, vous insérerez des données à l'aide de commandes SQL standard et importerez de grands ensembles de données provenant de sources externes. Cela vous montrera les aspects de la base de données relationnelle de TimescaleDB.

Tout d'abord, essayez les commandes de base. Les données peuvent être insérées dans l'hypertable en utilisant la commande SQL standard INSERT. Insérez quelques échantillons de données sur temperature et humidity pour l'appareil théorique, weather-pro-000000, en utilisant la commande suivante :

				
					
INSERT INTO conditions(time, device_id, temperature, humidity)

 VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);

Vous recevrez le résultat suivant :

				
					
[secondary_label Output]

INSERT 0 1

Vous pouvez également insérer plusieurs lignes de données à la fois. Essayez ce qui suit :

				
					
INSERT INTO conditions

 VALUES

 (NOW(), 'weather-pro-000002', 71.0, 51.0),

 (NOW(), 'weather-pro-000003', 70.5, 50.5),

 (NOW(), 'weather-pro-000004', 70.0, 50.2);

Vous recevrez ce qui suit :

				
					
[secondary_label Output]

INSERT 0 3

Vous pouvez également spécifier que la commande INSERT renverra une partie ou la totalité des données insérées en utilisant l'énoncé RETURNING :

				
					
INSERT INTO conditions

 VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;

Vous verrez le résultat suivant :

				
					
[secondary_label Output]

 time | device_id | temperature | humidity

-------------------------------+--------------------+-------------+----------

 2019-09-15 14:14:01.576651+00 | weather-pro-000002 | 70.1 | 50.1

(1 row)

Si vous souhaitez supprimer des données de l'hypertable, utilisez la commande SQL standard DELETE. Exécutez la procédure suivante pour supprimer toute donnée dont la temperature est supérieure à 80 ou dont l'humidity est supérieure à 50.

				
					
DELETE FROM conditions WHERE temperature &gt; 80;

DELETE FROM conditions WHERE humidity &gt; 50;

Après la procédure de suppression, il est recommandé d'utiliser la commande VACUUM, qui permettra de récupérer l'espace encore utilisé par les données qui ont été supprimées.

				
					
VACUUM conditions;

Vous pouvez trouver plus d'informations sur la commande VACUUM dans la documentation PostgreSQL.

Ces commandes conviennent parfaitement à la saisie de données à petite échelle, mais comme les données de séries chronologiques génèrent souvent d'énormes ensembles de données provenant de plusieurs appareils simultanément, il est également essentiel de savoir comment insérer des centaines ou des milliers de lignes à la fois. Si vous avez préparé des données provenant de sources externes sous une forme structurée, par exemple au format cvs, cette tâche peut être accomplie rapidement.

Pour tester la procédure, vous utiliserez un échantillon de données qui représente des données de température et d'humidité provenant de divers endroits. Il a été créé par les développeurs de TimescaleDB pour vous permettre de tester leur base de données. Vous pouvez consulter davantage d'informations sur les échantillons de fichiers de données dans la documentation TimescaleDB.

Voyons comment vous pouvez importer les données provenant de l'échantillon de données weather_small dans votre base de données. Tout d'abord, quittez Postgresql :

\q

Ensuite, téléchargez l'ensemble de données et extrayez-le :

				
					
cd /tmp

curl https://timescaledata.blob.core.windows.net/datasets/&lt;^&gt;weather_small&lt;^&gt;.tar.gz -o &lt;^&gt;weather_small&lt;^&gt;.tar.gz

tar -xvzf &lt;^&gt;weather_small&lt;^&gt;.tar.gz

Puis, importez les données de température et d'humidité dans votre base de données :

				
					
sudo -u postgres psql -d &lt;^&gt;timeseries&lt;^&gt; -c "\COPY conditions FROM weather_small_conditions.csv CSV"

Cette opération vous permet de vous connecter à la base de données <^>timeseries<^> et d'exécuter la commande \COPY qui copie les données du fichier choisi dans l'hypertable des conditions. Elle s'exécutera pendant quelques secondes.

Lorsque les données ont été saisies dans votre tableau, vous obtenez le résultat suivant :

				
					
[secondary_label Output]

COPY 1000000

Au cours de cette étape, vous avez ajouté des données à l'hypertable manuellement et par lots. Ensuite, continuez à effectuer des requêtes.

Étape 5 - Effectuez des interrogations de données

Maintenant que votre tableau contient des données, vous pouvez effectuer diverses requêtes pour l'analyser.

Pour commencer, connectez-vous à la base de données :

				
					
sudo -u postgres psql -d &lt;^&gt;timeseries&lt;^&gt;

Comme mentionné précédemment, pour travailler avec les hypertables, vous pouvez utiliser des commandes SQL standard. Par exemple, pour afficher les 10 dernières entrées de l'hypertable des conditions, exécutez la commande suivante.

				
					
SELECT * FROM conditions LIMIT 10;

Vous verrez le résultat suivant :

				
					
[secondary_label Output]

 time | device_id | temperature | humidity

------------------------+--------------------+--------------------+----------

 2016-11-15 12:00:00+00 | weather-pro-000000 | 39.9 | 49.9

 2016-11-15 12:00:00+00 | weather-pro-000001 | 32.4 | 49.8

 2016-11-15 12:00:00+00 | weather-pro-000002 | 39.800000000000004 | 50.2

 2016-11-15 12:00:00+00 | weather-pro-000003 | 36.800000000000004 | 49.8

 2016-11-15 12:00:00+00 | weather-pro-000004 | 71.8 | 50.1

 2016-11-15 12:00:00+00 | weather-pro-000005 | 71.8 | 49.9

 2016-11-15 12:00:00+00 | weather-pro-000006 | 37 | 49.8

 2016-11-15 12:00:00+00 | weather-pro-000007 | 72 | 50

 2016-11-15 12:00:00+00 | weather-pro-000008 | 31.3 | 50

 2016-11-15 12:00:00+00 | weather-pro-000009 | 84.4 | 87.8

(10 rows)

Cette commande vous permet de voir quelles données se trouvent dans la base de données. Comme la base de données contient un million d'enregistrements, vous avez utilisé LIMIT 10 pour limiter la sortie à 10 entrées.

Pour voir les entrées les plus récentes, triez le tableau de données par ordre décroissant dans le temps :

				
					
SELECT * FROM conditions ORDER BY time DESC LIMIT 20;

Cela permettra d'obtenir les 20 entrées les plus récentes.

Vous pouvez également ajouter un filtre. Par exemple, pour voir les entrées de l'appareil weather-pro-000000, exécutez la commande suivante :

				
					
SELECT * FROM conditions WHERE device_id = 'weather-pro-000000' ORDER BY time DESC LIMIT 10;

Dans ce cas, vous verrez les 10 points de données de température et d'humidité les plus récents enregistrés par l'appareil weather-pro-000000.

En plus des commandes SQL standard, TimescaleDB fournit également un certain nombre de fonctions spéciales qui sont utiles pour l'analyse des données des séries chronologiques. Par exemple, pour trouver la médiane des valeurs de température, vous pouvez utiliser la requête suivante avec la fonction percentile_cont.

				
					
SELECT percentile_cont(0.5)

 WITHIN GROUP (ORDER BY temperature)

 FROM conditions

 WHERE device_id = 'weather-pro-000000';

Vous verrez le résultat suivant :

				
					
[secondary_label Output]

 percentile_cont

-----------------

 40.5

(1 row)

De cette façon, vous verrez la température médiane du lieu où se trouve le capteur weather-pro-00000 pour toute la période d'observation.

Pour afficher les dernières valeurs de chacun des capteurs, vous pouvez utiliser la fonction last :

				
					
select device_id, last(temperature, time)

 FROM conditions

 GROUP BY device_id;

Dans la sortie, vous verrez la liste de tous les capteurs et les dernières valeurs pertinentes.

Pour obtenir les premières valeurs, utilisez la fonction first.

L'exemple suivant est plus complexe. Il indiquera les températures horaires moyennes, minimales et maximales pour le capteur choisi au cours des dernières 24 heures :

				
					
SELECT time_bucket('1 hour', time) "hour",

trunc(avg(temperature), 2) avg_temp,

trunc(min(temperature), 2) min_temp,

trunc(max(temperature), 2) max_temp

FROM conditions

WHERE device_id = 'weather-pro-000000'

GROUP BY "hour" ORDER BY "hour" DESC LIMIT 24;

Ici, vous avez utilisé la fonction time_bucket, qui agit comme une version plus puissante de la fonction date_trunc de PostgreSQL. Vous verrez ainsi à quelles périodes de la journée la température augmente ou diminue :

				
					
[secondary_label Output]

 hour | avg_temp | min_temp | max_temp

------------------------+----------+----------+----------

 2016-11-16 21:00:00+00 | 42.00 | 42.00 | 42.00

 2016-11-16 20:00:00+00 | 41.92 | 41.69 | 42.00

 2016-11-16 19:00:00+00 | 41.07 | 40.59 | 41.59

 2016-11-16 18:00:00+00 | 40.11 | 39.79 | 40.59

 2016-11-16 17:00:00+00 | 39.46 | 38.99 | 39.79

 2016-11-16 16:00:00+00 | 38.54 | 38.19 | 38.99

 2016-11-16 15:00:00+00 | 37.56 | 37.09 | 38.09

 2016-11-16 14:00:00+00 | 36.62 | 36.39 | 37.09

 2016-11-16 13:00:00+00 | 35.59 | 34.79 | 36.29

 2016-11-16 12:00:00+00 | 34.59 | 34.19 | 34.79

 2016-11-16 11:00:00+00 | 33.94 | 33.49 | 34.19

 2016-11-16 10:00:00+00 | 33.27 | 32.79 | 33.39

 2016-11-16 09:00:00+00 | 33.37 | 32.69 | 34.09

 2016-11-16 08:00:00+00 | 34.94 | 34.19 | 35.49

 2016-11-16 07:00:00+00 | 36.12 | 35.49 | 36.69

 2016-11-16 06:00:00+00 | 37.02 | 36.69 | 37.49

 2016-11-16 05:00:00+00 | 38.05 | 37.49 | 38.39

 2016-11-16 04:00:00+00 | 38.71 | 38.39 | 39.19

 2016-11-16 03:00:00+00 | 39.72 | 39.19 | 40.19

 2016-11-16 02:00:00+00 | 40.67 | 40.29 | 40.99

 2016-11-16 01:00:00+00 | 41.63 | 40.99 | 42.00

 2016-11-16 00:00:00+00 | 42.00 | 42.00 | 42.00

 2016-11-15 23:00:00+00 | 42.00 | 42.00 | 42.00

 2016-11-15 22:00:00+00 | 42.00 | 42.00 | 42.00

(24 rows)

Vous trouverez d'autres fonctions utiles dans la documentation TimescaleDB.

Maintenant, vous savez comment traiter vos données. Ensuite, vous allez voir comment supprimer les données inutiles et comment comprimer les données.

Étape 6 - Configurez la compression et la suppression des données

À mesure que les données s'accumulent, elles prennent de plus en plus de place sur votre disque dur. Pour gagner de l'espace, la dernière version de TimescaleDB propose une fonction de compression des données. Cette fonction ne nécessite aucune modification des paramètres du système de fichiers et peut être utilisée pour rendre votre base de données plus efficace. Pour plus d'informations sur le fonctionnement de cette compression, consultez cet article de TimescaleDB sur la compression.

Tout d'abord, activez la compression de votre hypertable :

				
					
ALTER TABLE conditions SET (

 timescaledb.compress,

 timescaledb.compress_segmentby = 'device_id'

);

Vous recevrez les données suivantes :

				
					
[secondary_label Output]

NOTICE: adding index _compressed_hypertable_2_device_id__ts_meta_sequence_num_idx ON _timescaledb_internal._compressed_hypertable_2 USING BTREE(device_id, _ts_meta_sequence_num)

ALTER TABLE

Remarque : vous pouvez également configurer TimescaleDB pour compresser les données sur la période spécifiée. Par exemple, vous pouvez exécuter :

				
					
SELECT add_compress_chunks_policy('conditions', INTERVAL '7 days');

Dans cet exemple, les données seront automatiquement compressées au bout d'une semaine.

Vous pouvez voir les statistiques sur les données compressées avec la commande :

				
					
SELECT *

FROM timescaledb_information.compressed_chunk_stats;

Vous verrez ensuite une liste de chunks avec leur état : état de la compression et combien d'espace est occupé par les données non compressées et compressées, en octets.

Si vous n'avez pas besoin de stocker des données pendant une longue période, vous pouvez supprimer les données obsolètes pour libérer encore plus d'espace. Il existe pour cela une fonction spéciale appelée drop_chunks. Elle permet de supprimer les chunks contenant des données antérieures à la date spécifiée :

				
					
SELECT drop_chunks(interval '24 hours', 'conditions');

Cette requête supprimera tous les morceaux de l'hypertable des conditions qui n'incluent que des données datant de plus d'un jour.

Vous recevrez le résultat suivant :

				
					
[secondary_label Output]

 drop_chunks

----------------------------------------

 _timescaledb_internal._hyper_1_2_chunk

(1 row)

Pour supprimer automatiquement les anciennes données, vous pouvez configurer une tâche cron. Consultez notre tutoriel pour apprendre à utiliser cron en vue d'automatiser diverses tâches de système.

Quittez la base de données :

\q

Ensuite, modifiez votre crontab avec la commande suivante, qui doit être exécutée depuis le shell :

				
					
crontab -e

Maintenant, ajoutez les lignes suivantes à la fin de votre fichier :

				
					
[label crontab]

...



0 1 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" &gt;/dev/null 2&gt;&amp;1

Cette tâche consiste à supprimer les données obsolètes qui datent de plus d'un jour à 1 heure du matin tous les jours.

Conclusion

Vous avez maintenant installé TimescaleDB sur votre serveur CentOS. Vous avez également essayé de créer des hypertables, d'y insérer des données, d'interroger les données, de compresser et de supprimer les enregistrements inutiles. Grâce à ces exemples, vous pourrez profiter des principaux avantages de TimescaleDB par rapport aux systèmes traditionnels de gestion de bases de données relationnelles pour le stockage de données chronologiques, notamment :

Des taux d'acquisition de données plus élevés

Une exécution des requêtes plus rapide

Des caractéristiques orientées chronologiquement

Comment installer et utiliser TimescaleDB sur CentOS 7

Table of Contents

Introduction

Conditions préalables

Étape 1 - Installation de TimescaleDB

Étape 2 - Configurez TimescaleDB

Étape 3 - Créez une nouvelle base de données et une nouvelle hypertable

Étape 4 - Rédigez et supprimez des données

Étape 5 - Effectuez des interrogations de données

Étape 6 - Configurez la compression et la suppression des données

Conclusion

Links

Newsletter

Contact