One place for hosting & domains

      Comment installer et utiliser Clickhouse sur Debian 10


      L’auteur a choisi le Free and Open Source Fund pour recevoir une donation dans le cadre du programme Write for DOnations.

      Introduction

      ClickHouse est une base de données analytique open-source, orientée colonne, créée par Yandex pour les cas d’utilisation de données OLAP et de mégadonnées. Le support de ClickHouse pour le traitement des requêtes en temps réel le rend approprié aux applications qui nécessitent des résultats d’analyse en moins d’une seconde. Le langage de requêtes de ClickHouse est un dialecte de SQL qui offre de puissantes capacités de requête déclarative tout en offrant une familiarité et une courbe d’apprentissage plus courte à l’utilisateur final.

      Les bases de données orientées colonnes stockent les enregistrements en blocs groupés par colonnes au lieu de lignes. En ne chargement pas de données pour les colonnes absentes de la requête, les bases de données orientées colonnes passent moins de temps à lire des données tout en complétant les requêtes. Par conséquent, ces bases de données peuvent calculer et retourner des résultats beaucoup plus vite que les systèmes traditionnels basés sur les lignes pour certaines charges de travail (opérations de type OLAP, par exemple).

      Les systèmes de traitement analogique en ligne OnLine Analytics Processing (OLAP) permettent d’organiser de grandes quantités de données et d’effectuer des requêtes complexes. Ils sont capables de gérer des pétaoctets de données et de retourner rapidement les résultats des requêtes. De cette manière, l’OLAP est utile pour travailler dans des domaines tels que la science des données et les analyses d’entreprise.

      Dans ce tutoriel, vous allez installer le serveur de base de données et le client ClickHouse sur votre machine. Vous utiliserez le SGBD pour des tâches typiques et autoriserez éventuellement l’accès à distance depuis un autre serveur, afin de pouvoir vous connecter à la base de données depuis une autre machine. Ensuite, vous testerez ClickHouse en modélisant et en interrogeant des exemples de données de visites de sites web.

      Conditions préalables

      • Un Debian 10 avec un utilisateur non root avec sudo activé et une configuration de pare-feu. Vous pouvez suivre le tutoriel de configuration initiale du serveur pour créer l’utilisateur et configurer le pare-feu.
      • (Facultatif) Un Debian 10 secondaire, un utilisateur non root avec sudo activé et une configuration de pare-feu. Vous pouvez suivre le tutoriel de configuration initiale du serveur.

      Étape 1 – Installation de ClickHouse

      Dans cette section, vous allez installer le serveur et les programmes clients ClickHouse en utilisant apt.

      Tout d’abord, lancez SSH sur votre serveur en exécutant :

      dirmngr est un serveur de gestion de certificats et de clés. Il est requis pour l’ajout et la vérification des clés de dépôt à distance. Installez-le en l’exécutant :

      Yandex gère un référentiel APT qui dispopse de la dernière version de ClickHouse. Ajoutez la clé GPG du référentiel afin de pouvoir télécharger en toute sécurité les paquets ClickHouse validés :

      • sudo apt-key adv --keyserver keyserver.ubuntu.com --recv E0C56BD4

      Vous verrez un résultat similaire à celui qui suit :

      Output

      Executing: /tmp/apt-key-gpghome.JkkcKnBAFY/gpg.1.sh --keyserver keyserver.ubuntu.com --recv E0C56BD4 gpg: key C8F1E19FE0C56BD4: public key "ClickHouse Repository Key <[email protected]>" imported gpg: Total number processed: 1 gpg: imported: 1

      La sortie confirme que la vérification et l’ajout de clé ont bien été effectués.

      Ajoutez le référentiel à votre liste de référentiels APT en exécutant :

      • echo "deb http://repo.yandex.ru/clickhouse/deb/stable/ main/" | sudo tee /etc/apt/sources.list.d/clickhouse.list

      Ici, vous avez extrait la sortie echo du sudo tee pour que cette sortie puisse être imprimée dans un fichier détenu par l’utilisateur root.

      Maintenant, exécutez apt update pour mettre à jour vos packages :

      Les packages clickhouse-server et clickhouse-client sont maintenant disponibles pour l’installation.

      Depuis la version 19.13.3 de ClickHouse, certaines configurations d’OpenSSL 1.1.1 telles que MinProtocol et CipherVersion ne sont pas lues correctement. Pour remédier à cette incompatibilité, modifiez le fichier de configuration OpenSSL et commentez la ligne ssl_conf = ssl_sect dans /etc/ssl/openssl.cnf.

      Modifiez le fichier de configuration en exécutant :

      • sudo nano /etc/ssl/openssl.cnf

      Ensuite, commentez la ligne contenant ssl_conf = ssl_sect, de sorte qu’elle ressemble au fichier suivant :

      /etc/ssl/openssl.cnf

      ...
      
      tsa_name        = yes   # Must the TSA name be included in the reply?
                      # (optional, default: no)
      ess_cert_id_chain   = no    # Must the ESS cert id chain be included?
                      # (optional, default: no)
      ess_cert_id_alg     = sha1  # algorithm to compute certificate
                      # identifier (optional, default: sha1)
      [default_conf]
      #ssl_conf = ssl_sect
      
      [ssl_sect]
      ...
      

      Maintenant que la configuration OpenSSL a été patchée, vous êtes prêt à installer les packages serveur et client de ClickHouse. Installez-les avec :

      • sudo apt install clickhouse-server clickhouse-client

      Lors de l’installation, il vous sera également demandé de définir un mot de passe pour l’utilisateur par défaut de ClickHouse.

      Vous avez installé avec succès le serveur et le client ClickHouse. Vous êtes maintenant prêt à démarrer le service de base de données et à vous assurer qu’il fonctionne correctement.

      Étape 2 – Démarrage du service

      Le package clickhouse-server que vous avez installé dans la section précédente crée un service systemd, qui effectue des actions telles que le démarrage, l’arrêt et le redémarrage du serveur de base de données. systemd est un système init pour Linux qui permet d’initialiser et de gérer des services. Dans cette section, vous allez démarrer le service et vérifier qu’il fonctionne correctement.

      Lancez le service clickhouse-server en exécutant :

      • sudo service clickhouse-server start

      La commande précédente n’affichera aucune sortie. Pour vérifier que le service fonctionne correctement, exécutez :

      • sudo service clickhouse-server status

      Vous verrez une sortie semblable à ce qui suit :

      Output

      ● clickhouse-server.service - ClickHouse Server (analytic DBMS for big data) Loaded: loaded (/etc/systemd/system/clickhouse-server.service; enabled; vendor preset: enabled) Active: active (running) since Sat 2018-12-22 07:23:20 UTC; 1h 9min ago Main PID: 27101 (ClickHouse-serv) Tasks: 34 (limit: 1152) CGroup: /system.slice/ClickHouse-server.service └─27101 /usr/bin/ClickHouse-server --config=/etc/ClickHouse-server/config.xml

      La sortie indique que le serveur est en cours d’exécution.

      Vous avez réussi à démarrer le serveur ClickHouse et vous pourrez maintenant utiliser le programme CLI clickhouse-client pour vous connecter au serveur.

      Étape 3 – Création de bases de données et de tables

      Dans ClickHouse, vous pouvez créer et supprimer des bases de données en exécutant des déclarations SQL directement dans l’invite de la base de données interactive. Les instructions consistent en des commandes qui suivent une syntaxe particulière et qui ordonnent au serveur de la base de données d’effectuer une opération demandée en même temps que les données requises. Vous créez des bases de données en utilisant la syntaxe CREATE DATABASE table_name. Pour créer une base de données, commencez une session client en exécutant la commande suivante :

      • clickhouse-client --password

      Il vous sera demandé d’entrer le mot de passe que vous avez défini pendant l’installation. Entrez-le pour démarrer correctement la session client.

      La commande précédente vous connectera à l’invite du client où vous pouvez exécuter des instructions SQL ClickHouse pour effectuer des actions telles que :

      • Créer, mettre à jour et supprimer des bases de données, des tables, des index, des partitions et des vues.

      • Exécuter des requêtes pour récupérer des données qui sont éventuellement filtrées et regroupées selon diverses conditions.

      Dans cette étape, avec le client ClickHouse prêt à l’insertion de données, vous allez créer une base de données et une table. Aux fins de ce tutoriel, vous allez créer une base de données nommée test, et à l’intérieur de celle-ci vous allez créer une table nommée <^>visits<^> qui effectue le suivi de la durée des visites sur le site web.

      Maintenant que vous êtes à l’intérieur de l’invite de commande ClickHouse, créez votre base de données test en exécutant :

      Vous verrez la sortie suivante qui montre que vous avez créé la base de données :

      Output

      CREATE DATABASE test Ok. 0 rows in set. Elapsed: 0.003 sec.

      Une table ClickHouse est semblable aux tables d’autres bases de données relationnelles ; elle contient une collection de données connexes dans un format structuré. Vous pouvez spécifier des colonnes avec leurs types, ajouter des lignes de données et exécuter différentes sortes de requêtes sur des tables.

      La syntaxe de création de tables dans ClickHouse suit cette structure :

      CREATE TABLE table_name
      (
          column_name1 column_type [options],
          column_name2 column_type [options],
          ...
      ) ENGINE = engine
      

      Les valeurs table_name et column_name peuvent être n’importe quel identifiant ASCII valide. ClickHouse prend en charge un large éventail de types de colonnes, dont les plus courants sont :

      • UIntra64 : utilisé pour le stockage de valeurs entières dans la plage 0 à 1844674407709551615.

      • Float64 : utilisé pour le stockage des nombres à virgule flottante tels que 2039.23, 10.5, etc.

      • Ring : utilisé pour le stockage de caractères de longueur variable. Il ne nécessite pas d’attribut de longueur maximale puisqu’il peut stocker des longueurs arbitraires.

      • Date : utilisé pour le stockage des dates au format YYYY-MM-DD

      • DateTime : utilisé pour le stockage de dates couplées avec les heures et suit le format YYYY-MM-DD HH:MM:SS.

      Après les définitions de la colonne, vous spécifiez le moteur utilisé pour le tableau. Dans ClickHouse, les moteurs déterminent la structure physique des données sous-jacentes, les capacités d’interrogation de la table, ses modes d’accès simultanés et la prise en charge des index.  Différents types de moteurs sont adaptés à différentes exigences d’application. Le type de moteur le plus couramment utilisé et le plus applicable est MergeTree.

      Maintenant que vous avez un bon aperçu de la création de table, vous allez créer une table. Commencez par confirmer la base de données que vous allez modifier :

      Vous verrez la sortie suivante montrant que vous avez passé à la base de données test à partir de la base de données par défaut :

      Output

      USE test Ok. 0 rows in set. Elapsed: 0.001 sec.

      La suite de ce guide vous permettra d’exécuter des instructions dans le contexte de cette base de données.

      Créez votre table visits en exécutant cette commande :

      • CREATE TABLE visits (
      • id UInt64,
      • duration Float64,
      • url String,
      • created DateTime
      • ) ENGINE = MergeTree()
      • PRIMARY KEY id
      • ORDER BY id;

      Voici une ventilation de ce que fait la commande. Vous créez un table nommé visits qui comporte quatre colonnes :

      • id : la colonne de la clé primaire. De même que les autres systèmes RDBMS, une colonne de clés primaire de ClickHouse identifie une ligne ; chaque ligne devrait avoir une valeur unique pour cette colonne.

      • duration : colonne float utilisée pour enregistrer la durée de chaque visite en secondes. Les colonnes float peuvent stocker des valeurs décimales telles que 12.50.

      • url : colonne string qui stocke l’URL visite, comme http://example.com.

      • created : colonne date and time qui suit quand une visite a eu lieu.

      Après les définitions de colonne, vous spécifiez MergeTree comme moteur de stockage pour la table. La famille de moteurs MergeTree est recommandée pour les bases de données de production en raison de sa prise en charge optimisée des grandes insertions en temps réel, de sa robustesse globale et de sa prise en charge des requêtes. En outre, les moteurs MergeTree prennent en charge le tri des lignes par clé primaire, le partitionnement des lignes, ainsi que la réplication et l’échantillonnage des données.

      Si vous avez l’intention d’utiliser ClickHouse pour archiver des données qui ne sont pas souvent interrogées ou pour stocker des données temporaires, vous pouvez utiliser la famille de moteurs Log pour une optimisation dans ce cas d’utilisation.

      Après avoir défini les colonnes, vous définirez d’autres options au niveau de la table. La clause PRIMARY KEY définit id comme la colonne clé primaire et la clause ORDER BY stockera les valeurs triées selon la colonne id. Une clé primaire identifie de manière unique une ligne et est utilisée pour accéder efficacement à une seule ligne et pour colocaliser efficacement les lignes.

      Lorsque vous exécuterez l’instruction create, vous verrez la sortie suivante :

      Output

      CREATE TABLE visits ( id UInt64, duration Float64, url String, created DateTime ) ENGINE = MergeTree() PRIMARY KEY id ORDER BY id Ok. 0 rows in set. Elapsed: 0.010 sec.

      Dans cette section, vous avez créé une base de données et une table pour suivre les données de visite de site web. Dans l’étape suivante, vous allez insérer des données dans la table, mettre à jour les données existantes et supprimer ces données.

      Étape 4 — Insertion, mise à jour et suppression des données et des colonnes

      Dans cette étape, vous utiliserez votre table visits pour insérer, mettre à jour et supprimer des données. La commande suivante est un exemple de syntaxe permettant d’insérer des lignes dans une table ClickHouse :

      INSERT INTO table_name VALUES (column_1_value, column_2_value, ....);
      

      Maintenant, insérez quelques lignes d’exemple de données de visite de site web dans votre table visits en exécutant chacune des instructions suivantes :

      • INSERT INTO visits VALUES (1, 10.5, 'http://example.com', '2019-01-01 00:01:01');
      • INSERT INTO visits VALUES (2, 40.2, 'http://example1.com', '2019-01-03 10:01:01');
      • INSERT INTO visits VALUES (3, 13, 'http://example2.com', '2019-01-03 12:01:01');
      • INSERT INTO visits VALUES (4, 2, 'http://example3.com', '2019-01-04 02:01:01');

      Vous verrez la sortie suivante pour chaque instruction d’insertion.

      Output

      INSERT INTO visits VALUES Ok. 1 rows in set. Elapsed: 0.004 sec.

      La sortie de chaque ligne montre que vous l’avez insérée avec succès dans la table visits.

      Vous allez maintenant ajouter une colonne supplémentaire à la table visits. Lorsque vous ajoutez ou supprimez des colonnes de tables existantes, ClickHouse prend en charge la syntaxe ALTER.

      Par exemple, la syntaxe basique pour ajouter une colonne à une table est la suivante :

      ALTER TABLE table_name ADD COLUMN column_name column_type;
      

      Ajoutez une colonne nommée location qui va enregistrer l’emplacement des visites d’un site web en exécutant l’instruction suivante :

      • ALTER TABLE visits ADD COLUMN location String;

      Vous verrez une sortie semblable à ce qui suit :

      Output

      ALTER TABLE visits ADD COLUMN location String Ok. 0 rows in set. Elapsed: 0.014 sec.

      La sortie montre que vous avez bien ajouté la colonne location.

      À partir de la version 19.13.3, ClickHouse ne permet pas de mettre à jour et de supprimer des lignes individuelles de données, en raison de contraintes de mise en œuvre. Toutefois, ClickHouse prend en charge les mises à jour et les suppressions en vrac et dispose d’une syntaxe SQL distincte pour ces opérations, afin de mettre en évidence leur utilisation non standard.

      La syntaxe suivante est un exemple de mise à jour en vrac des lignes :

      ALTER TABLE table_name UPDATE  column_1 = value_1, column_2 = value_2 ...  WHERE  filter_conditions;
      

      Vous allez exécuter l’instruction suivante pour mettre à jour la colonne url de toutes les lignes qui ont une durée inférieure à 15. Saisissez-la dans l’invite d’exécution de la base de données :

      • ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15;

      La sortie de l’instruction de mise à jour en bloc sera la suivante :

      Output

      ALTER TABLE visits UPDATE url = 'http://example2.com' WHERE duration < 15 Ok. 0 rows in set. Elapsed: 0.003 sec.

      La sortie montre que votre requête de mise à jour a bien été effectuée. Le 0 rows in set dans la sortie indique que la requête n’a renvoyé aucune ligne ; ce sera le cas pour toute requête de mise à jour et de suppression.

      L’exemple de syntaxe pour la suppression en bloc de lignes est similaire à la mise à jour des lignes et présente la structure suivante :

      ALTER TABLE table_name DELETE WHERE filter_conditions;
      

      Pour tester la suppression de données, exécutez l’instruction suivante pour supprimer toutes les lignes qui ont une durée inférieure à 5 :

      • ALTER TABLE visits DELETE WHERE duration < 5;

      La sortie de l’instruction de suppression en bloc sera similaire à :

      Output

      ALTER TABLE visits DELETE WHERE duration < 5 Ok. 0 rows in set. Elapsed: 0.003 sec.

      La sortie confirme que vous avez supprimé les lignes avec une durée inférieure à cinq secondes.

      Pour supprimer des colonnes de votre table, la syntaxe devrait suivre cet exemple de structure :

      ALTER TABLE table_name DROP COLUMN column_name;
      

      Supprimez la colonne location que vous avez précédemment ajoutée en exécutant ce qui suit :

      • ALTER TABLE visits DROP COLUMN location;

      La sortie DROP COLUMN confirmant que vous avez supprimé la colonne sera la suivante :

      Output

      ALTER TABLE visits DROP COLUMN location String Ok. 0 rows in set. Elapsed: 0.010 sec.

      Maintenant que vous avez bien inséré, mis à jour et supprimé des lignes et des colonnes dans votre table visits, vous allez passer à l’interrogation de données dans la prochaine étape.

      Étape 5 – Interrogation de données

      Le langage de requête de ClickHouse est un dialecte SQL personnalisé avec des extensions et des fonctions adaptées aux charges de travail d’analyse. Dans cette étape, vous allez exécuter des requêtes de sélection et d’agrégation pour récupérer des données et des résultats de votre table visits.

      Les requêtes de sélection vous permettent de récupérer des lignes et des colonnes de données filtrées par des conditions que vous spécifiez, ainsi que des options telles que le nombre de lignes à retourner. Vous pouvez sélectionner des lignes et des colonnes de données en utilisant la syntaxe SELECT. La syntaxe de base pour les requêtes SELECT est :

      SELECT func_1(column_1), func_2(column_2) FROM table_name WHERE filter_conditions row_options;
      

      Exécutez l’instruction suivante pour récupérer des valeurs url et de durée pour les lignes où l’url est http://example.com.

      • SELECT url, duration FROM visits WHERE url = 'http://example2.com' LIMIT 2;

      Vous verrez le résultat suivant :

      Output

      SELECT url, duration FROM visits WHERE url = 'http://example2.com' LIMIT 2 ┌─url─────────────────┬─duration─┐ │ http://example2.com │ 10.5 │ └─────────────────────┴──────────┘ ┌─url─────────────────┬─duration─┐ │ http://example2.com │ 13 │ └─────────────────────┴──────────┘ 2 rows in set. Elapsed: 0.013 sec.

      La sortie a renvoyé deux lignes qui correspondent aux conditions que vous avez spécifiées. Maintenant que vous avez sélectionné des valeurs, vous pouvez passer à l’exécution des requêtes d’agrégation.

      Les requêtes d’agrégation sont des requêtes qui opèrent sur un ensemble de valeurs et renvoient des valeurs de sortie uniques. Dans les bases de données analytiques, ces requêtes sont fréquemment exécutées et sont bien optimisées par la base de données. Certaines fonctions agrégées prises en charge par ClickHouse sont :

      • count : renvoie le nombre de lignes correspondant aux conditions spécifiées.

      • sum : renvoie la somme des valeurs des colonnes sélectionnées.

      • avg : renvoie la moyenne des valeurs des colonnes sélectionnées.

      Certaines fonctions agrégées spécifiques à ClickHouse-specific comprennent :

      • uniq : renvoie un nombre approximatif de lignes distinctes assorties.

      • topK : renvoie un tableau des valeurs les plus fréquentes d’une colonne spécifique en utilisant un algorithme d’approximation.

      Pour démontrer l’exécution des requêtes d’agrégation, vous calculerez la durée totale des visites en exécutant la requête sum :

      • SELECT SUM(duration) FROM visits;

      Vous verrez un résultat similaire à celui qui suit :

      Output

      SELECT SUM(duration) FROM visits ┌─SUM(duration)─┐ │ 63.7 │ └───────────────┘ 1 rows in set. Elapsed: 0.010 sec.

      Maintenant, calculez les deux premières URL en exécutant :

      • SELECT topK(2)(url) FROM visits;

      Vous verrez un résultat similaire à celui qui suit :

      Output

      SELECT topK(2)(url) FROM visits ┌─topK(2)(url)──────────────────────────────────┐ │ ['http://example2.com','http://example1.com'] │ └───────────────────────────────────────────────┘ 1 rows in set. Elapsed: 0.010 sec.

      Maintenant que vous avez interrogé avec succès votre table visits, vous allez supprimer des tables et des bases de données lors de la prochaine étape.

      Étape 6 — Suppression de tables et de bases de données

      Dans cette section, vous allez supprimer votre table visits et la base de données test.

      La syntaxe de suppression de table suit cet exemple :

      DROP TABLE table_name;
      

      Pour supprimer la table <^>visits<^>, exécutez l’instruction suivante :

      Vous verrez la sortie suivante déclarant que vous avez bien supprimé la table :

      output

      DROP TABLE visits Ok. 0 rows in set. Elapsed: 0.005 sec.

      Vous pouvez supprimer des bases de données en utilisant la syntaxe DROP database table_name. Pour supprimer la base de données test, exécutez l’instruction suivante :

      La sortie obtenue montre que vous avez bien supprimé la base de données.

      Output

      DROP DATABASE test Ok. 0 rows in set. Elapsed: 0.003 sec.

      Vous avez supprimé des tables et des bases de données lors de cette étape. Maintenant que vous avez créé, mis à jour et supprimé des bases de données, des tables et des données dans votre instance ClickHouse, vous allez activer l’accès à distance à votre serveur de base de données dans la section suivante.

      Étape 7 — Configuration des règles de pare-feu (facultatif)

      Si vous avez l’intention de n’utiliser ClickHouse que localement avec des applications fonctionnant sur le même serveur, ou si vous n’avez pas de pare-feu activé sur votre serveur, vous n’avez pas besoin de suivre cette section. Si, au contraire, vous vous connectez à distance au serveur de base de données ClickHouse, vous devez suivre cette étape.

      Actuellement, votre serveur est doté d’un pare-feu qui désactive l’accès de votre adresse IP publique à tous les ports. Vous allez exécuter les deux étapes suivantes pour autoriser l’accès à distance :

      • Modifiez la configuration de ClickHouse et autorisez-le à écouter toutes les interfaces.

      • Ajoutez une règle de pare-feu autorisant les connexions entrantes au port 8123 (qui est le port HTTP exécuté par le serveur ClickHouse).

      Si vous vous trouvez dans l’invite de la base de données, quittez-la en tapant CTRL+D.

      Modifiez le fichier de configuration en exécutant :

      • sudo nano /etc/clickhouse-server/config.xml

      Ensuite, décommentez la ligne contenant <!-- <listen_host>0.0.0</listen_host> -->, comme dans le fichier suivant :

      /etc/clickhouse-server/config.xml

      ...
       <interserver_http_host>example.yandex.ru</interserver_http_host>
          -->
      
          <!-- Listen specified host. use :: (wildcard IPv6 address), if you want to accept connections both with IPv4 and IPv6 from everywhere. -->
          <!-- <listen_host>::</listen_host> -->
          <!-- Same for hosts with disabled ipv6: -->
          <listen_host>0.0.0.0</listen_host>
      
          <!-- Default values - try listen localhost on ipv4 and ipv6: -->
          <!--
          <listen_host>::1</listen_host>
          <listen_host>127.0.0.1</listen_host>
          -->
      ...
      

      Sauvegardez le fichier et quittez. Pour que la nouvelle configuration soit appliquée, redémarrez le service en exécutant :

      • sudo service clickhouse-server restart

      Vous ne verrez aucune sortie de cette commande. Le serveur de ClickHouse écoute sur le port 8123 pour les connexions HTTP et sur le port 9000 pour les connexions du client-clickhouse. Autorisez l’accès aux deux ports pour l’adresse IP de votre deuxième serveur avec la commande suivante :

      • sudo ufw allow from second_server_ip/32 to any port 8123
      • sudo ufw allow from second_server_ip/32 to any port 9000

      Vous verrez la sortie suivante pour les deux commandes, qui montre que vous avez activé l’accès aux deux ports :

      Output

      Rule added

      ClickHouse sera maintenant accessible depuis l’IP que vous avez ajoutée. N’hésitez pas à ajouter des IP supplémentaires tels que l’adresse de votre machine locale, si nécessaire.

      Pour vérifier que vous pouvez vous connecter au serveur ClickHouse depuis la machine distante, suivez d’abord les étapes de l’Étape 1 de ce tutoriel sur le deuxième serveur et assurez-vous que le clickhouse-client y est installé.

      Maintenant que vous vous êtes connecté au deuxième serveur, lancez une session client en exécutant :

      • clickhouse-client --host your_server_ip --password

      Vous verrez la sortie suivante qui montre que vous vous êtes bien connecté au serveur :

      Output

      ClickHouse client version 19.13.3.26 (official build). Password for user (default): Connecting to your_server_ip:9000 as user default. Connected to ClickHouse server version 19.13.3 revision 54425. hostname :)

      Dans cette étape, vous avez activé l’accès à distance à votre serveur de base de données ClickHouse en ajustant vos règles de pare-feu.

      Conclusion

      Vous avez mis en place avec succès une instance de base de données ClickHouse sur votre serveur et vous avez créé une base de données et une table, ajouté des données, effectué des requêtes et supprimé la base de données. Dans la documentation de ClickHouse, vous pouvez lire leurs analyses comparatives par rapport à d’autres bases de données analytiques commerciales et open-source, ainsi que des documents de référence généraux.

      Les autres caractéristiques offertes par ClickHouse comprennent le traitement distribué des requêtes sur plusieurs serveurs pour améliorer les performances et protéger contre la perte de données en stockant les données sur différentes partitions.



      Source link

      Comment installer et utiliser TimescaleDB sur Ubuntu 18.04


      L’auteur a choisi que le Computer History Museum recevrait 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 :

      2019-11-01 09:00:00    server.cpu.1    0.9
      2019-11-01 09:00:00    server.cpu.15   0.8
      2019-11-01 09:01:00    server.cpu.1    0.9
      2019-11-01 09:01:00    server.cpu.15   0.8
      ...
      

      La gestion des données de séries chronologiques est devenue une compétence essentielle avec l’essor de l’Internet des Objets (IdO) 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 Ubuntu 18.04, le configurer et apprendre à travailler avec lui. Vous créerez des bases de données de séries chronologiques et effectuerez des requêtes simples. Enfin, vous verrez comment vous débarrasser des données inutiles.

      Conditions préalables

      Pour suivre ce tutoriel, vous aurez besoin de :

      Étape 1 – Installation de TimescaleDB

      Timesc3aleDB n’est pas disponible dans les référentiels par défaut d’Ubuntu, vous l’installerez donc dans cette étape depuis l’archive TimescaleDB Personal Packages (PPA).

      Tout d’abord, ajoutez le référentiel APT de Timescale :

      • sudo add-apt-repository ppa:timescale/timescaledb-ppa

      Confirmez cette action en appuyant sur la touche ENTER.

      Ensuite, rafraîchissez votre cache APT pour mettre à jour vos listes de paquets :

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

      • sudo apt install timescaledb-postgresql-10

      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 – Configuration de 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, qui lira le fichier postgresql.conf et vous proposera de manière interactive d’effectuer des changements.

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

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

      Output

      Using postgresql.conf at this path: /etc/postgresql/10/main/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 :

      Output

      ... Is this correct? [(y)es/(n)o]: y Writing backup to: /tmp/timescaledb_tune.backup201911181111

      Ensuite, vous serez invité à changer la variable shared_preload_libraries pour précharger le module TimescaleDB lors du démarrage du serveur PostgreSQL :

      Output

      shared_preload_libraries needs to be updated Current: #shared_preload_libraries = '' Recommended: shared_preload_libraries = 'timescaledb' Is this okay? [(y)es/(n)o]:

      shared_preload_libraries accepte comme valeur une liste de modules séparés par des virgules, en désignant quels modules PostgreSQL doit charger avant de démarrer le serveur de base de données. Ce changement ajoutera le module timescaledb à cette liste.

      Remarque : si une bibliothèque spécifiée par shared_preload_libraries est introuvable, le serveur de la base de données ne se lancera pas. Gardez cela en tête lors du débogage des apps qui font usage de shared_preload_libraries. Pour plus d’informations a ce propos, consultez cet article PostgresqlCO.NF sur shared_preload_libraries.

      Activez le module TimescaleDB en tapant y dans cette invite et en appuyant sur ENTER :

      Output

      ... Is this okay? [(y)es/(n)o]: y success: shared_preload_libraries will be updated

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

      Output

      Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y Recommendations based on 7.79 GB of available memory and 4 CPUs for PostgreSQL 10 Memory settings recommendations Current: shared_buffers = 128MB #effective_cache_size = 4GB #maintenance_work_mem = 64MB #work_mem = 4MB Recommended: shared_buffers = 1994MB effective_cache_size = 5982MB maintenance_work_mem = 1021001kB work_mem = 5105kB Is this okay? [(y)es/(s)kip/(q)uit]:

      timescaledb-tune va automatiquement détecter la mémoire disponible des serveurs et calculer les valeurs recommandées pour un certain nombre de paramètres. shared_buffers, par exemple, détermine la quantité de mémoire allouée pour la mise en cache des données. Par défaut, ce paramètre est relativement faible pour tenir compte d’un plus grand nombre de plates-formes, c’est pourquoi timescaledb-tune a suggéré d’augmenter la valeur de 128 Mo à 1994 Mo, afin de mieux exploiter les ressources en faisant plus de place pour stocker les informations mises en cache, comme les requêtes répétées. La variable work_mem a également été augmentée pour permettre des tris plus complexes.

      Si vous souhaitez en savoir plus sur le processus de réglage des paramètres de mémoire pour PostgreSQL, consultez l’article Tuning Your PostgreSQL Server sur le wiki de PostgreSQL.

      Entrez y pour accepter les valeurs :

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y 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. Ces paramètres déterminent comment plusieurs processeurs peuvent effectuer des requêtes simultanées en parallèle afin d’analyser les bases de données et de renvoyer les données demandées plus rapidement.

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

      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 = 13 max_parallel_workers_per_gather = 1 max_parallel_workers = 2 Is this okay? [(y)es/(s)kip/(q)uit]:

      Ces paramètres régulent le nombre de travailleurs qui traitent les demandes et les tâches de fond. 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.

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: parallelism settings will be updated

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

      Output

      WAL settings recommendations Current: #wal_buffers = -1 #min_wal_size = 80MB #max_wal_size = 1GB Recommended: wal_buffers = 16MB min_wal_size = 4GB max_wal_size = 8GB Is this okay? [(y)es/(s)kip/(q)uit]:

      WAL est une méthode de connexion dans laquelle PostgreSQL enregistre les modifications apportées aux fichiers de données avant que les modifications ne soient apportées à la base de données. En accordant la priorité à un enregistrement à jour des changements de données, WAL vous permet de reconstruire votre base de données en cas de crash. De cette manière, il préserve l’intégrité des données. Cependant, les paramètres par défaut peuvent entraîner des opérations d’entrée/sortie (E/S) inefficaces qui ralentissent les performances d’écriture. Pour remédier à cela, tapez et entrez y :

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: WAL settings will be updated

      Vous trouverez quelques recommandations diverses :

      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 = 500 random_page_cost = 1.1 checkpoint_completion_target = 0.9 max_locks_per_transaction = 64 autovacuum_max_workers = 10 autovacuum_naptime = 10 effective_io_concurrency = 200 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.

      Output

      ... Is this okay? [(y)es/(s)kip/(q)uit]: y success: miscellaneous settings will be updated Saving changes to: /etc/postgresql/10/main/postgresql.conf

      En conséquence, vous obtiendrez un fichier de configuration prêt à l’emploi à l’adresse /etc/postgresql/10/main/postgresql.conf​​​​​​.

      Remarque :Si vous automatisez l’installation, vous pouvez également lancer la commande initiale avec les drapeaux --quiet et --yes, qui appliqueront automatiquement toutes les recommandations et apporteront des modifications au fichier de configuration postgresql.conf :

      • sudo timescaledb-tune --quiet --yes

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

      • sudo systemctl restart postgresql.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éation d’une nouvelle base de données et d’une 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 tables relationnelles à l’avenir.

      Pour le démontrer, vous utiliserez les commandes PostgreSQL pour créer une base de données, puis vous activerez l’extension TimescaleDB pour créer une hypertable, qui est une abstraction de haut niveau de nombreuses tables individuelles. Les hypertables sont les structures principales avec lesquelles vous allez travailler dans TimescaleDB.

      Connectez-vous à votre base de données PostgreSQL :

      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 timeseries;
      • c timeseries

      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 :

      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

      Comme mentionné précédemment, les principaux points d’interaction avec vos données de séries chronologiques sont les hypertables, qui consistent en de nombreuses tables individuelles contenant des données, appelées 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ée une table appelée conditions contenant 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 :

      Output

      create_hypertable ------------------------- (1,public,conditions,t) (1 row)

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

      Étape 4 – Rédaction et suppression 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. Vous pouvez insérer des données dans l’hypertable en utilisant la commande SQL standard INSERT. Insérez quelques échantillons de données sur la température et l’humidité 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 verrez la sortie suivante :

      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 :

      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 :

      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 température est supérieure à 80 ou l'humidité supérieure à 50.

      • DELETE FROM conditions WHERE temperature > 80;
      • DELETE FROM conditions WHERE humidity > 50;

      Après l’opération de suppression, utilisez la commande VACUUM pour récupérer l’espace encore utilisé par les données qui ont été supprimées.

      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 s’agit d’un ensemble de données officiel de TimescaleDB, créé pour 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 sample dans votre base de données. Tout d’abord, quittez Postgresql :

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

      • wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz
      • tar -xvzf weather_small.tar.gz

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

      • sudo -u postgres psql -d timeseries -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 :

      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 – Interrogation des données

      Maintenant que votre table 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 timeseries

      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 :

      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 la table 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 :

      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 une 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 :

      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 – Configuration de la compression et de 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 la donnée suivante :

      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 après 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 une fonction spéciale appelée drop_chunks pour ça. Elle vous 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 les données datant de plus d’un jour.

      Vous recevrez le résultat suivant :

      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 pour automatiser diverses tâches de système.

      Quittez la base de données :

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

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

      crontab

      ...
      
      0 1 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" >/dev/null 2>&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 configuré TimescaleDB sur votre serveur Ubuntu 18.04. 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

      Maintenant que vous savez comment stocker des données de séries chronologiques, vous pourriez utiliser les données pour créer des graphiques. TimescaleDB est compatible avec les outils de visualisation qui fonctionnent avec PostgreSQL, comme Grafana. Vous pouvez utiliser notre tutoriel Comment installer et sécuriser Grafana sur Ubuntu 18.04 pour en savoir plus sur cet outil de visualisation très répandu.



      Source link

      Comment gérer et utiliser les triggers de base de données MySQL sur Ubuntu 18.04


      L’auteur a choisi the Apache Software Foundation pour recevoir un don dans le cadre du programme Write for DOnations.

      Introduction

      Dans MySQL, un trigger (ou déclencheur) est une commande SQL définie par l’utilisateur qui est automatiquement invoquée lors d’une opération INSERT, DELETE ou UPDATE. Le code trigger est associé à une table et est détruit une fois que la table est supprimée. Vous pouvez spécifier une heure de déclenchement de l’action et définir si elle sera activée avant ou après l’événement défini dans la base de données.

      Les triggers ont plusieurs avantages. Par exemple, vous pouvez les utiliser pour générer la valeur d’une colonne dérivée lors d’une instruction INSERT. Un autre cas d’utilisation consiste à faire respecter l’intégrité référentielle par laquelle vous pouvez utiliser un trigger pour sauvegarder un enregistrement dans plusieurs tables apparentées. D’autres avantages incluent la journalisation des actions des utilisateurs dans les tables d’audit ainsi que la copie en direct des données dans différents schémas de base de données à des fins de redondance, pour éviter un point de défaillance.

      Vous pouvez également utiliser des triggers pour maintenir des règles de validation au niveau de la base de données. Cela permet de partager la source de données entre plusieurs applications sans rompre la logique commerciale. Cela réduit considérablement les allers-retours vers le serveur de la base de données, ce qui améliore donc le temps de réponse de vos applications. Comme le serveur de base de données exécute des triggers, ils peuvent profiter de ressources serveur améliorées telles que la RAM et le processeur.

      Dans ce tutoriel, vous allez créer, utiliser et supprimer différents types de triggers dans votre base de données MySQL.

      Conditions préalables

      Avant de commencer, assurez-vous que vous disposez de ce qui suit :

      Étape 1 — Création d’un exemple de base de données

      Dans cette étape, vous allez créer un exemple de base de données client avec plusieurs tables, afin de démontrer le fonctionnement des triggers MySQL.

      Pour en savoir plus sur les requêtes MySQL, consultez notre Introduction aux requêtes dans MySQL.

      Tout d’abord, connectez-vous à votre serveur MySQL en tant que root :

      Entrez votre mot de passe root MySQL lorsqu’il vous est demandé et cliquez sur ENTER pour continuer. Lorsque vous voyez l’invite mysql>, exécutez la commande suivante pour créer une base de données test_db :

      Output

      Query OK, 1 row affected (0.00 sec)

      Ensuite, accédez à test_db avec :

      Output

      Database changed

      Vous commencerez par créer une table de clients customers. Cette table contiendra les enregistrements des clients (customers), y compris les customer_id, customer_name, et level. Il y aura deux niveaux de clients : BASIC et VIP.

      • Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

      Output

      Query OK, 0 rows affected (0.01 sec)

      Maintenant, ajoutez quelques enregistrements à la table customers. Pour ce faire, exécutez les commandes suivantes une par une :

      • Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
      • Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
      • Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

      Vous verrez la sortie suivante après avoir exécuté chacune des commandes INSERT :

      Output

      Query OK, 1 row affected (0.01 sec)

      Pour vous assurer que les échantillons d’enregistrements ont été insérés avec succès, exécutez la commande SELECT :

      Output

      +-------------+---------------+-------+ | customer_id | customer_name | level | +-------------+---------------+-------+ | 1 | JOHN DOE | BASIC | | 2 | MARY ROE | BASIC | | 3 | JOHN DOE | VIP | +-------------+---------------+-------+ 3 rows in set (0.00 sec)

      Vous allez également créer une autre table pour conserver les informations relatives au compte de clients customers. La table contiendra les champs customer_id et status_notes

      Exécutez la commande suivante :

      • Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

      Ensuite, vous allez créer une table de ventes sales. Cette table contiendra les données de vente (sales) relatives aux différents clients via la colonne customer_id :

      • Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

      Output

      Query OK, 0 rows affected (0.01 sec)

      Vous allez ajouter des échantillons de données aux données de vente sales lors des étapes à venir, tout en testant les triggers. Ensuite, créez une table audit_log pour enregistrer les mises à jour apportées à la table des ventes sales lorsque vous implémenterez le trigger AFTER UPDATE à l’étape 5 :

      • Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME ) ENGINE=INNODB;

      Output

      Query OK, 0 rows affected (0.02 sec)

      Avec la base de données test_db et les quatre tables en place, vous allez maintenant travailler avec les différents triggers MySQL dans votre base de données.

      Étape 2 — Création d’un trigger Before Insert

      Dans cette étape, vous allez examiner la syntaxe d’un trigger MySQL avant d’appliquer cette logique pour créer un trigger BEFORE INSERT qui valide le champ sales_amount lorsque des données sont insérées dans la table sales.

      La syntaxe générale utilisée pour créer un trigger MySQL est illustrée dans l’exemple suivant :

      DELIMITER //
      CREATE TRIGGER [TRIGGER_NAME]
      [TRIGGER TIME] [TRIGGER EVENT]
      ON [TABLE]
      FOR EACH ROW
      [TRIGGER BODY]//
      DELIMITER ;
      

      La structure du trigger comprend :

      DELIMITER // : le délimiteur MySQL par défaut est ; — il est nécessaire de le changer en autre chose afin que MySQL traite les lignes suivantes comme une seule commande jusqu’à ce qu’il atteigne votre délimiteur personnalisé. Dans cet exemple, le délimiteur est changé en // et ensuite le délimiteur ; est redéfini à la fin.

      [TRIGGER_NAME] : Un trigger doit avoir un nom et c’est là que vous incluez la valeur.

      [TRIGGER TIME] : Un trigger peut être invoqué à différents moments. MySQL vous permet de définir si le trigger démarrera avant ou après une opération de base de données.

      [TRIGGER EVENT]: Les triggers ne sont appelés que par les opérations INSERT, UPDATE et DELETE. Vous pouvez utiliser n’importe quelle valeur ici en fonction de ce que vous voulez réaliser.

      [TABLE]: Tout trigger que vous créez sur votre base de données MySQL doit être associé à une table.

      FOR EACH ROW : Cette instruction ordonne à MySQL d’exécuter le code du trigger pour chaque ligne que ce dernier affecte.

      [TRIGGER BODY] : le code qui est exécuté lorsque le trigger est invoqué est appelé_ trigger body​​_​. Ce peut être une seule instruction SQL ou plusieurs commandes. Notez que si vous exécutez plusieurs instructions SQL sur le trigger body, vous devez les inclure dans un bloc BEGIN... END.

      Remarque : Lorsque vous créez le trigger body, vous pouvez utiliser les mots-clés OLD et NEW pour accéder aux valeurs de colonnes anciennes et nouvelles entrées lors d’une opération INSERT, UPDATE et DELETE. Dans un déclencheur DELETE, seul le mot-clé OLD peut être utilisé (que vous utiliserez à l’étape 4).

      Vous allez maintenant créer votre premier trigger BEFORE INSERT. Ce trigger sera associé à la table des ventes sales et il sera invoqué avant l’insertion d’un enregistrement pour valider le sales_amount. La fonction du trigger consiste à vérifier si le sales_amount inséré dans la table des ventes est supérieur à 10000 et à signaler une erreur si cette valeur est évaluée comme vraie (true).

      Assurez-vous que vous êtes connecté au serveur MySQL. Ensuite, entrez les commandes MySQL suivantes une par une :

      • DELIMITER //
      • CREATE TRIGGER validate_sales_amount
      • BEFORE INSERT
      • ON sales
      • FOR EACH ROW
      • IF NEW.sales_amount>10000 THEN
      • SIGNAL SQLSTATE '45000'
      • SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
      • END IF//
      • DELIMITER ;

      Vous utilisez l’instruction IF... THEN... END IF pour évaluer si la quantité fournie lors de l’instruction INSERT est comprise dans votre plage. Le trigger est capable d’extraire la nouvelle valeur sales_amount fournie en utilisant le mot-clé NEW.

      Pour faire apparaître un message d’erreur générique, vous utilisez les lignes suivantes pour informer l’utilisateur de l’erreur :

      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
      

      Ensuite, insérez un enregistrement avec un sales_amount de 11000 dans la table des ventes pour vérifier si le trigger arrêtera l’opération :

      • Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

      Output

      ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.

      Cette erreur montre que le code du trigger fonctionne comme prévu.

      Essayez maintenant un nouvel enregistrement avec une valeur de 7500 pour vérifier si la commande aboutira :

      • Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7500');

      Comme la valeur est dans la plage recommandée, vous verrez la sortie suivante :

      Output

      Query OK, 1 row affected (0.01 sec)

      Pour confirmer que les données ont été insérées, exécutez la commande suivante :

      La sortie confirme que les données sont dans la table :

      Output

      +----------+-------------+--------------+ | sales_id | customer_id | sales_amount | +----------+-------------+--------------+ | 1 | 1 | 7500 | +----------+-------------+--------------+ 1 row in set (0.00 sec)

      Dans cette étape, vous avez testé des triggers pour valider les données avant leur insertion dans une base de données.

      Ensuite, vous allez travailler avec le trigger AFTER INSERT pour enregistrer les informations connexes dans différentes tables.

      Étape 3 — Création d’un trigger After Insert

      Les triggers AFTER INSERT sont exécutés lorsque les enregistrements sont insérés avec succès dans une table. Cette fonctionnalité peut être utilisée pour exécuter automatiquement d’autres logiques commerciales. Par exemple, dans une application bancaire, un déclencheur AFTER INSERT peut fermer un compte de prêt lorsqu’un client a fini de rembourser le prêt. Le trigger peut surveiller tous les paiements insérés dans une table de transactions et fermer le prêt automatiquement une fois que le solde du prêt est à zéro.

      Dans cette étape, vous travaillerez avec votre table customer_status en utilisant un trigger AFTER INSERT pour entrer des enregistrements de clients apparentés.

      Pour créer le trigger AFTER INSERT, entrez les commandes suivantes :

      • DELIMITER //
      • CREATE TRIGGER customer_status_records
      • AFTER INSERT
      • ON customers
      • FOR EACH ROW
      • Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
      • DELIMITER ;

      Output

      Query OK, 0 rows affected (0.00 sec)

      Ici, vous demandez à MySQL d’enregistrer un autre enregistrement dans la table customer_status une fois qu’un nouvel enregistrement client est inséré dans la table de clients customers.

      Maintenant, insérez un nouvel enregistrement dans la table customers pour confirmer que votre code de trigger sera invoqué :

      • Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

      Output

      Query OK, 1 row affected (0.01 sec)

      Comme l’enregistrement a été inséré avec succès, vérifiez qu’un nouvel enregistrement de statut a été inséré dans la table customer_status :

      • Select * from customer_status;

      Output

      +-------------+-----------------------------+ | customer_id | status_notes | +-------------+-----------------------------+ | 4 | ACCOUNT OPENED SUCCESSFULLY | +-------------+-----------------------------+ 1 row in set (0.00 sec)

      La sortie confirme que le trigger a bien été exécuté.

      Le trigger AFTER INSERT est utile pour surveiller le cycle de vie d’un client. Dans un environnement de production, les comptes clients peuvent subir différentes étapes telles que l’ouverture, la suspension et la fermeture du compte.

      Dans les étapes suivantes, vous allez travailler avec des triggers UPDATE.

      Étape 4 — Création d’un trigger Before Update

      Un trigger BEFORE UPDATE est similaire au trigger BEFORE INSERT — la différence est le moment où ils sont invoqués. Vous pouvez utiliser le trigger BEFORE UPDATE pour vérifier une logique commerciale avant de mettre à jour un enregistrement. Pour tester cela, vous utiliserez la table customers dans laquelle vous avez déjà inséré certaines données.

      Vous disposez de deux niveaux pour vos clients dans la base de données. Dans cet exemple, une fois qu’un compte client est mis à jour au niveau VIP, le compte ne peut pas être retrogradé au niveau BASIC. Pour faire appliquer une telle règle, vous allez créer un trigger BEFORE UPDATE qui s’exécutera avant l’instruction UPDATE, comme indiqué ci-dessous. Si un utilisateur de la base de données tente de rétrograder un client du niveau VIP au niveau BASIC, une exception définie par l’utilisateur sera déclenchée.

      Entrez les commandes SQL suivantes une par une pour créer le trigger BEFORE UPDATE :

      • DELIMITER //
      • CREATE TRIGGER validate_customer_level
      • BEFORE UPDATE
      • ON customers
      • FOR EACH ROW
      • IF OLD.level='VIP' THEN
      • SIGNAL SQLSTATE '45000'
      • SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
      • END IF //
      • DELIMITER ;

      Vous utilisez le mot-clé OLD pour capturer le niveau fourni par l’utilisateur lors de l’exécution de la commande UPDATE. Encore une fois, vous utilisez l’instruction IF... THEN... END IF pour signaler à l’utilisateur une instruction d’erreur générique.

      Ensuite, exécutez la commande SQL suivante qui tente de rétrograder un compte client associé au customer_id 3 :

      • Update customers set level='BASIC' where customer_id='3';

      Vous verrez la sortie suivante fournissant le SET MESSAGE_TEXT: :

      Output

      ERROR 1644 (45000): A VIP customer can not be downgraded.

      Si vous exécutez la même commande pour un client de niveau BASIC, et que vous essayez de faire passer le compte au niveau VIP, la commande sera exécutée avec succès :

      • Update customers set level='VIP' where customer_id='1';

      Output

      Rows matched: 1 Changed: 1 Warnings: 0

      Vous avez utilisé le trigger BEFORE UPDATE pour faire appliquer une règle commerciale. Vous allez maintenant utiliser un trigger AFTER UPDATE pour l’enregistrement des audits.

      Étape 5 — Création d’un trigger After Update

      Un trigger AFTER UPDATE est invoqué une fois qu’un enregistrement de base de données est mis à jour avec succès. Ce comportement rend le trigger adapté à l’enregistrement des audits. Dans un environnement multi-utilisateurs, l’administrateur peut vouloir consulter un historique des modifications apportées par les utilisateurs dans une table particulière, à des fins d’audit.

      Vous allez créer un trigger qui enregistre l’activité de mise à jour de la table sales. Notre table audit_log contiendra des informations sur les utilisateurs MySQL qui mettent à jour la table sales, la date de la mise à jour, et les valeurs new et old de sales_amount.

      Pour créer le trigger, exécutez les commandes SQL suivantes :

      • DELIMITER //
      • CREATE TRIGGER log_sales_updates
      • AFTER UPDATE
      • ON sales
      • FOR EACH ROW
      • Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
      • DELIMITER ;

      Vous insérez un nouvel enregistrement dans la table audit_log. Vous utilisez le mot-clé NEW pour récupérer la valeur du sales_id et du nouveau sales_amount En outre, vous utilisez le mot-clé OLD pour récupérer le sales_amount précédent puisque vous voulez enregistrer les deux montants à des fins d’audit.

      La commande SELECT User() récupère l’utilisateur actuel de l’opération et l’instruction NOW() extrait la valeur de la date et de l’heure actuelle du serveur MySQL

      Maintenant, si un utilisateur essaie de mettre à jour la valeur d’un enregistrement dans la table sales, le trigger log_sales_updates va insérer un nouvel enregistrement dans la table audit_log.

      Créons un nouvel enregistrement de ventes avec un sales_id aléatoire de 5 et essayons de le mettre à jour. En premier lieu, insérez l’enregistrement des ventes avec :

      • Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

      Output

      Query OK, 1 row affected (0.00 sec)

      Ensuite, mettez à jour l’enregistrement :

      • Update sales set sales_amount='9000' where sales_id='5';

      Vous verrez la sortie suivante :

      Output

      Rows matched: 1 Changed: 1 Warnings: 0

      Exécutez maintenant la commande suivante pour vérifier si le trigger AFTER UPDATE a pu enregistrer un nouvel enregistrement dans la table audit_log :

      Le trigger a enregistré la mise à jour. Votre sortie indique l’ancien et le nouveau sales_amount enregistrés avec l’utilisateur qui a mis à jour les enregistrements :

      Output

      +--------+----------+-----------------+------------+----------------+---------------------+ | log_id | sales_id | previous_amount | new_amount | updated_by | updated_on | +--------+----------+-----------------+------------+----------------+---------------------+ | 1 | 5 | 8000 | 9000 | root@localhost | 2019-11-07 09:28:36 | +--------+----------+-----------------+------------+----------------+---------------------+ 1 row in set (0.00 sec)

      Vous disposez également de la date et de l’heure auxquelles la mise à jour a été effectuée, ce qui est précieux à des fins d’audit.

      Ensuite, vous utiliserez le trigger DELETE pour faire respecter l’intégrité du référencement au niveau de la base de données.

      Étape 6 — Création d’un trigger Before Delete

      Les triggers BEFORE DELETE sont invoqués avant qu’une instruction DELETE ne s’exécute sur une table.  Ces types de triggers sont généralement utilisés pour faire respecter l’intégrité référentielle dans différentes tables apparentées. Par exemple, chaque enregistrement de la table sales concerne un customer_id de la table customers. Si un utilisateur de la base de données a supprimé un enregistrement de la table customers qui a un enregistrement lié dans la table sales, vous n’auriez aucun moyen de connaître le client associé à cet enregistrement.

      Pour éviter cela, vous pouvez créer un trigger BEFORE DELETE pour faire appliquer votre logique. Exécutez les commandes SQL suivantes une par une :

      • DELIMITER //
      • CREATE TRIGGER validate_related_records
      • BEFORE DELETE
      • ON customers
      • FOR EACH ROW
      • IF OLD.customer_id in (select customer_id from sales) THEN
      • SIGNAL SQLSTATE '45000'
      • SET MESSAGE_TEXT = 'The customer has a related sales record.';
      • END IF//
      • DELIMITER ;

      Maintenant, essayez de supprimer un client qui a un enregistrement de vente connexe :

      • Delete from customers where customer_id='2';

      Vous recevrez alors la sortie suivante :

      Output

      ERROR 1644 (45000): The customer has a related sales record.

      Le trigger BEFORE DELETE peut empêcher la suppression accidentelle d’informations connexes dans une base de données.

      Toutefois, dans certaines situations, vous pouvez vouloir supprimer tous les enregistrements associés à un enregistrement particulier dans les différentes tables connexes. Dans cette situation, vous utiliserez le trigger AFTER DELETE, que vous allez tester à l’étape suivante.

      Étape 7 — Création d’un trigger After Delete

      Les triggers AFTER DELETE sont activés une fois qu’un enregistrement a été supprimé avec succès. Vous pouvez par exemple utiliser un trigger AFTER DELETE dans une situation où le niveau de remise qu’un client particulier reçoit est déterminé par le nombre de ventes effectuées pendant une période définie. Si certains des enregistrements du client sont supprimés de la table sales, le niveau de réduction du client devrait être réduit.

      Une autre utilisation du trigger AFTER DELETE consiste à supprimer des informations connexes d’une autre table une fois qu’un enregistrement de table de base est supprimé.  Par exemple, vous allez définir un trigger qui supprime l’enregistrement du client si les enregistrements de ventes avec le customer_id correspondant sont supprimés de la table sales. Exécutez la commande suivante pour créer votre trigger :

      • DELIMITER //
      • CREATE TRIGGER delete_related_info
      • AFTER DELETE
      • ON sales
      • FOR EACH ROW
      • Delete from customers where customer_id=OLD.customer_id;//
      • DELIMITER ;

      Ensuite, exécutez ce qui suit pour supprimer tous les enregistrements de ventes associés à un customer_id de 2 :

      • Delete from sales where customer_id='2';

      Output

      Query OK, 1 row affected (0.00 sec)

      Vérifiez maintenant s’il existe des enregistrements pour le client dans la table sales :

      • Select * from customers where customer_id='2';

      Vous recevrez une sortie Empty Set puisque l’enregistrement client associé au customer_id de 2 a été supprimé par le trigger :

      Output

      Empty set (0.00 sec)

      Vous avez maintenant utilisé chacune des différentes formes de triggers pour effectuer des fonctions spécifiques. Ensuite, vous allez voir comment vous pouvez supprimer un trigger de la base de données si vous n’en avez plus besoin.

      Étape 8 — Suppression de triggers

      Comme pour tout autre objet de la base de données, vous pouvez supprimer des triggers à l’aide de la commande DROP. Voici la syntaxe de suppression d’un trigger :

      Drop trigger [TRIGGER NAME];
      

      Par exemple, pour supprimer le dernier trigger AFTER DELETE que vous avez créé, exécutez la commande suivante :

      • Drop trigger delete_related_info;

      Output

      Query OK, 0 rows affected (0.00 sec)

      La nécessité de supprimer des triggers se pose lorsque vous voulez en recréer la structure. Dans ce cas, vous pouvez supprimer le trigger et en redéfinir un nouveau avec les différentes commandes de trigger.

      Conclusion

      Dans ce tutoriel, vous avez créé, utilisé et supprimé les différents types de triggers d’une base de données MySQL. À l’aide d’un exemple de base de données relative aux clients, vous avez mis en place des triggers pour différents cas d’utilisation tels que la validation des données, une application de logique d’entreprise, l’enregistrement des audits et la mise en application de l’intégrité référentielle.

      Pour obtenir des informations supplémentaires sur l’utilisation de votre base de données MySQL, consultez ce qui suit :



      Source link