Quelle véritable utilité des colonnes dynamiques de MariaDB ?

Bonjour à toutes et à tous,

Et oui, encore une question concernant le même projet dont je vous parlais il y a quelques mois déjà.

Le projet en question concernait les objets connectés. Le serveur recevant les données est écrit en Java et la base de données est relationnelle et se nomme MariaDB.

J’ai cru comprendre que les bases de données relationnelles ne sont plus un choix de référence dans le domaine des objets connectés, mais j’espère malgré tout avoir fait un bon choix.

La base de données possède une table intéressante pour ce sujet, c’est la table qui contient toutes les données que peut envoyer un objet connecté au serveur. Ainsi, lorsque je décide de prendre en compte une nouvelle fonctionnalité basée sur une nouvelle donnée, j’ajoute une nouvelle colonne dans la table.

J’ai vu qu’ajouter des colonnes à une table déjà possédant déjà de nombreuses valeurs était quelque chose de simple, mais qui demandait beaucoup de temps à la machine…

A présent, ma table de données contient 34 colonnes. Toutes représentant des données différentes et potentiellement utiles (il peut y en avoir une bonne centaine…mais j’en doute). Et en réalité, seulement 5 données sont toujours présentes dans les données reçues. Et le reste est optionnel. Donc vide / initialisé avec des valeurs par défaut. Je pourrais par exemple n’avoir que 6 colones. 5 données obligatoires et une 6ème qui contiendra les données optionnelles.

Je me suis rendu compte hier que 34 colonnes, ça commençait à faire un peu gros et fouillis. Du coup, j’ai pensé au JSON ou aux Colonnes Dynamiques (choix qui est peut-être le mieux), par l’intermédiaire du type de données "Blob". Ainsi, une ligne ne possèdera que les données (colonnes) nécéssaires.

Pour en finir, je me demandais si le choix de passer de colonnes pré-définies (schémas statique) à des colonnes dynamiques serait un bon choix pour mon projet. (enfin, du peu que je vous en ai dit). Si, ce choix ne présenterait à première vue aucun gros inconvénient pour des requêtes déjà existantes, comme par exemple, la manipulation des chiffres. Puisqu’il n’y aurait plus de valeur par defaut (0 par exemple), serait-il toujours aussi simple de calculer des moyennes ou des choses de ce style ? (je vais faire des tests) La gestion des données ne deviendra-t-elle pas quelque chose de super compliqué ?

Après, si jamais je venais à chosir ce type de stockage de données, je sais très bien que des requêtes seront à adapter. C’est évident.

Désolé encore pour ce pavé.

Merci beaucoup pour votre aide, A bientôt.

Salut,

Avant de rentrer dans le détail technique de ces colonnes dynamiques, je pense qu’il faut déjà jeter un oeil à ton modèle de données qui semble avoir beaucoup de mal à scaler.

Si tu comptes avoir un schéma qui n’est jamais fixe, alors une base de données relationnelle n’est probablement pas le bon choix. Mais avant d’en arriver à cette conclusion, peut-être as-tu simplement un problème de conception (dans tous les cas, une table à 34 colonnes est clairement indicative d’un problème) ?

Est-ce que tu pourrais nous en dire plus sur les données que tu manipules, et la façon dont tu y accèdes ?

Bonjour ! Et merci pour ton aide.

Bon, je ne suis pas un professionnel de la base de données, donc je risque de te répondre à côté de la plaque.

Tu parles de scalabilité, c’est quelque chose qui me préocupe et je pense que ma base de données (en plus de son côté relatioinnel) ne répond pas vraiment à ce critère. Il y a peu de temps malheureusement, j’avais regardé du côté de Cassandra qui me paraissait bien.

Pour ce qui est du schéma fixe ou non, je dirais que seule la table "data" peut avoir besoin d’évoluer. Les autres sont de bien plus petites tables pour la plupart. Les plus grosses sont : "data" (34 colonnes), "tracker" (14 colonnes) et celle de l’utilisateur (11 colonnes).

Voici une petite partie de la base de données. Je peux t’envoyer plus de détails sur le schéma en privé si tu le souhaites.

Les données sont presque tout le temps des chiffres. Ces chiffres varient à chaque nouvelle ligne.

J’enregistre ces données en Java et j’y accède principalement en PHP avec MySQLi.

J’espère avoir répondu au mieux à tes questions.

Ah ah ! Merci docteur ! Je suis tombé dans le piège alors. Effectivement, j’ai pensé comme tu l’as si bien décrit au début.

C’est tout à fait ça. Les données sont sauvegardées pour qu’on puisse voir les objets sur une carte du monde, leur déplacement, la vitesse à un instant T et tout ce qui s’y réfère. C’est surtout ça. Ensuite, oui, il y a quelques graphiques, mais pour le moment, c’est un peu pour le "fun" ; l’évolution de la vitesse, la visualisation de la décharge de la batterie…

Exactement. Il y a en plus tout ce qui touche au GPS (latitude, longitude, satellites, vitesse, orientation, etc…). Event sert à dire "grâce ou à cause de quoi les données ont été générées". Si par exemple, le chiffre est 50, ce sera à cause d’un choc. Et comme tu le dis, je vais prendre les 30 derniers "timestamp" et l’"id" de l’évènement (ici 50) pour refaire cet historique.

Donc comme tu as pu le deviner, j’ai une autre table "event" qui possède juste un "ID" et un "nom". Il y aura le code de l’évènement et son nom.

Dans cette base de données, j’y inclus aussi les traductions des différents élements qui devront être traduits dans le site. Donc cette table "event" aura une autre table "event_translation".

Ce qui est réellement problématique, c’est que tu vas stocker une trentaine de zéros par ligne pour 5 ou 6 données utiles, donc cette table va prendre 5 fois plus de place qu’elle n’en a réellement besoin.

Ce que tu dis est entièrement vrai, même si je n’avais pas d’ordre de grandeur en tête. Mais 5 fois plus lourd, effectivement, il faut changer ça. Et jusqu’à présent (et encore un peu maintenant), je n’ai pas vraiment d’idée pour résoudre ce problème. A la limite, j’aurais su le déplacer en créant une autre table.

D’accord, oui, tu me conseilles d’aller plus loin et d’"éclater" un peu plus la table data ? Pourquoi pas. Par contre, je ne sais pas si je saurais gérer correctement toutes les nouvelles tables ; les insertions dans plusieurs tables successives et les recerches à effectuer. Je vais tenter ça sur une copie de la base de données.

Je vais regarder tout ça et essayer de voir pour les regrouper alors. Merci !

Aussi, concernant les colonnes dynamiques (j’ai découvert ça il y a peu de temps), qu’en penses-tu ?

Enfin, je ne sais pas si tu parlais vraiment de ça, mais quand tu parlais de "scaler" et peut-être de NoSQL est-ce que Cassandra (par exemple) serait bien meilleure dans mon cas ? Et si oui, pourquoi les bases de données NoSQL sont-elles de plus en plus appréciée ? (j’ai vu qu’éviter les relations est plus rapide) Y a t’il des inconvénients à ces bases de données ? à avoir un schémas qui peut bouger aussi facilement ?

Que penses-tu être le mieux pour dans mon cas parmis tout ces choix (éclater une table, colonnes dynamique, NoSQL) ?

Je suis désolé pour toutes ces questions qui nous font s’éloigner du sujet principal… Mais cette éventualité, celle de passer à du NoSQL est de plus en plus présente pour moi depuis quelques semaines et depuis, je compare et j’hésite sans avancer entre ces deux types de BDD. :/

Merci pour ce retour sur les colonnes dynamiques !

J’ai bien aimé ta comparaison avec le troll des cavernes. Elle est très explicite. Je ne l’avais j’ai lue.

Très intéressant tout ça.

Je pense que l’analytique correspond bien au projet sur lequel je travaille (même si je ne sais pas quels sont les autres domaines).

Donc, de ce que j’ai compris avec le paragraphe 2 (pour de très gros flux de données, complexité), le paragraphe 1 (requêtage plus facile) ainsi que ta confirmation, rester sur une base de données relationnelle ne te paraît pas risqué. C’est plutôt rassurant pour la suite.

Ma base de données, effectivement, gère plusieurs choses ; les utilisateurs, des traductions pour le site et les objets connectés qu’ils possèdent. Mais oui, ces données ont des liens entre elles. Aussi, ces données (et les tables) ne changeront très probablement que très peu (peu d’ajouts / suppressions).

Pour savoir comment obtenir les données, je pense que je devrais utiliser la clef primaire de la table initiale, data. Lorsque j’insèrerais les données obligatoires dans data, je récupèrerais l'id généré et l’utiliserais dans chacune des nouvelles tables tables créées en tant que clef primaire également.

Avec une jointure, comme tu le dis, ce sera peut-être légèrement plus compliqué, mais pas tant que ça au final. Je n’aurais qu’à faire un JOIN sur l’ID (clef primaire) et à "sélectionner" la table qui contient la donnée voulue… Ca peut être bien. Qu’en penses-tu ?

Après (ou avant), il ne me reste plus qu’à essayer de séparer tout ça en leur trouvant des liens.

Citation de moi-même : En prenant du recul, je me suis rendu compte que le passage qui suit ne vous apportera pas grand chose…vous pouvez sans doute l’ignorer (Oui. La moitié du texte…). ^^'

Quant au projet, il est sérieux. Je suis dans une start-up. J’ai très vité été le dernier développeur de l’entreprise (il n’y en avait qu’un seul). Je suis "stagiaire" et je dois m’occuper de toutes les parties du projet. Donc BDD, Serveur Java, Web, Mobile, """Sécurité"""… Je me sent souvent seul face aux tâches et aux choix que je dois faire, ce qui peut engendrer des erreurs de ma part. J’apprends constament et découvre sans cesse de nouvelles choses. Parfois tardivement, comme ici. Mais j’essaye de faire quelque chose de propre et d’assez évolutif car l’entreprise croit au projet. Petite pression donc.

Ainsi, j’évite moi-même de m’aventurer dans des technologies trop exotiques, inconnues ou trop récentes.

La documentation de MariaDB est bien faite. L’utilisation de colonnes dynamique semble bien à première vue. Mais je pense qu’il ne faut pas prendre la liberté de changer de noms de colonnes trop souvent au risque de ne plus jamais avoir accès aux anciennes données.

J’ai aussi vu sur les forums anglais de nombreuses personnes demander aussi si l’utilisation du JSON (apparement une alternative aux colonnes dynamiques) était une pratique acceptable…difficile de le savoir tant les avis sont variés. Mais ce serait apparement à éviter.

Si jamais j’ose m’aventurer avec cette nouvelle notion, j’essayerai d’en faire part dans un article de Zeste de Savoir.

Y’a deux cas potentiellement intéressants des DB orientées colonnes :

• stocker des données hétérogènes dans la même table (en gros des données qui ont la même clef primaire mais pas les mêmes attributs) : ça peut être une bonne idée, mais ça peut aussi vite devenir l’enfer (à désérializer par exemple : si c’est un produit de nature "X" alors je vais lire les colonnes A, B, C, sinon je vais lire B, D, et F). Ca peut être très pratique, mais très pénible aussi, gaffe.
• stocker des données "time series". En gros tu auras toujours une même clef primaire, et une colonne par "point dans le temps". Le nom de la colonne sera par exemple la date. Ca ressemble beaucoup plus à ton cas d’usage. Si vraiment t’as des tonnes et des tonnes de points de mesure pour un appareil donné, et que tu ne souhaites stocker QUE ça dans le SGBD (en gros tes jointures et tes requêtes du style INNER JOIN user ON ... WHERE user.name in (...) tu les fais en SQL), ça peut valoir le coup de regarder du côté: soit des bases de données time-series, soit orientée colonne (genre Cassandra). Mais ça n’aura d’intérêt que si tu as des tonnes et des tonnes d’objets qui publient des tonnes et des tonnes de mesures.

tl;dr: C’est assez logique que tu aies regardé du côté des SGBD orientés colonnes car effectivement ton cas d’usage ressemble à leur cas d’usage. Mieux vaudrait aller chercher (si vraiment c’est pertinent) un SGBD dont c’est l’objectif premier (InfluxDB, Cassandra par exemple). Mais ça va introduire beaucoup de complexité pour quelque chose dont tu n’as sans doute pas encore besoin pour l’instant. Garde ça dans le fond de ta tête pour l’instant, tu pourras y revenir par la suite

Bonjour ! Et merci de ta réponse.

Je viens de voir que FaceBook par exemple aurait remplacé Cassandra (que j’ai cité plus haut) par HBase. Hé ben. Pourquoi y a t-il autant de choix de BDD ?

Pour le premier point, j’ai justement peur que le côté pratique de mes propositions devienne une horreur à gérer par la suite.

Ensuite, pour les tonnes de données, elles arriveront toujours à la même intervalle de temps. Je peux dire que la journée, les données arriveront toutes les 30 ou 60 secondes (tout dépend de l’objet) En revanche, la nuit, les flux de données à traiter seront bien (bien) moindres. Si il ne se passe rien, un envoi toutes les 15 minutes par exemple serait suffisant.

Pour 1000 objets (ce serait déjà beau d’avoir ça), toutes les 30 secondes, ça ferait 2.880.000 ((3600÷30)×1000×24) nouvelles données par jour. Par contre, je ne sais pas à partir de quel moment, on peut dire que cela devient énorme.

Ensuite, je ne stocke pas seulement les données des objets connectés. Il y a aussi des données des comptes utilisateurs et la truduction du site. Il y a aussi d’autres toutes petites tables concernant les IoT, mais elle ne changeront jamais. Je devrai juste y ajouter de nouvelles lignes (pas de colonnes).

Effectivement, peut-être que j’aurais le temps de changer pour une autre BDD une fois la "machine lancée". Mais si la base de données traditionnelle peut tenir quelques années (5 ans peut-être), ça pourrait permettre à l’entreprise d’embaucher un développeur supplémentaire, ou à moi d’avoir du temps pour rectifier tout ça. Mais selon ce qu’a dit @nohar, mélanger une BDD relationnnelle et une BDD NoSQL, cela dépasse clairement mon domaine de compétences.

Le principal, c’est d’avoir ça en tête comme tu le dis, et que ça tienne le choc jusque là. Je vais continuer à regarder un peu comment ces bases de données fonctionnent. J’avais cru voir que Cassandra avait l’avantage d’avoir un langage semblable au SQL, mais avec moins de fonctionnalités (pourtant utiles ).

Bonjour ! Je reviens pour vous dire que je n’ai pas oublié ce sujet.

J’ai pas mal de choses à faire en ce moment en entreprise et à l’école.

Je peux seulement vous parler de mes avancées.

J’ai essayé de réduire la table "data" en plusieurs petites tables. Je n’ai cependant pas réussi à le faire correctement. Je me retrouve toujours avec des tables "fourre tout" et le découpage est un peu compliqué puisqu’il faut trouver des liens entre les données. Et pour certaines, c’est tendu.

Je me suis alors retourné vers les colonnes dynamiques… Il m’a donc fallu réécrire totalement ma méthode de sauvegarde des données reçues. Rien de très compliqué au final.

De ça (requête préparée) :

INSERT INTO data (imei, event, longitude, latitude, altitude, timestamp) VALUES (?, ?, ?, ?, ?, ?);

C’est passé à ça :

INSERT INTO data (imei, event, longitude, latitude, altitude, timestamp, dynamic_columns) VALUES (?, ?, ?, ?, ?, ?, COLUMN_CREATE('input_1', ?, 'speed', ?));

Le COLUMN_CREATE (fonction de MariaDB) fait référence au nom de ma colonne dynamique (dynamic_columns). Cette fonction prend en paramètres des clefs ainsi que des valeurs. Donc on déclare la clef…puis sa valeur. Et ainsi de suite pour les autres colonnes que l’on souhaite créer.

Bon c’est bien beau tout ça… mais est-ce que ça fonctionne bien ??? Beeeeh…Pour mes tests, cela fonctionne parfaitement bien.

Par exemple pour obtenir une vitesse…si elle n’existe pas dans toutes les données reçues :

SELECT id, imei, `timestamp`, COLUMN_GET(optional_data,"speed" as float))
FROM `data`
WHERE COLUMN_EXISTS(optional_data, "speed");

Et alors ? Est-ce que je vais sauter les yeux fermés sur cette solution ?

Je ne sais pas. Même si je suis tenté. Il faut que je teste ce mode de fonctionnement sur d’autres requêtes et dans d’autres véritables programmes. Je n’ai pas encore eu le temps de le faire jusqu’à présent.

Ce qui me fait hésiter, c’est que comme on insère des données dans les colonnes sans leur donner de type au moment de la création de la table, il faut leur donner un type au moment ou l’on effectue la requête. D’où le "speed" as float.

Aussi, de par son côté dynamique, une ligne peut ou non posséder une colonne qui existe ou non dans une autre ligne… (pas très clair)

Est-ce que cela ne pose pase de soucis ? Est-ce que cela ne complexifie pas trop les requêtes ?

C’est ce que je veux découvrir.

J’espère que ce retour vous aura intéressé. Je vous donnerai des nouvelles sur mes avancées plus tard.

A bientôt !