Que vous commenciez tout juste à travailler avec un ensemble de données spécifique ou que vous surveilliez des activités et des rapports basés sur des ensembles de données existants, l’une des premières choses que vous devez prendre en compte est la qualité des données que vous traitez. La continuité est l’un des facteurs les plus critiques pour évaluer la qualité des données de séries chronologiques. Les systèmes de séries chronologiques servent généralement des cas d’utilisation où les données doivent être consommées, traitées et traitées de toute urgence.
Prenons l’exemple d’un véhicule de transport en commun. Pour la sécurité des passagers et la rapidité du service, les véhicules ont besoin de leurs différents capteurs – GPS, capteurs de proximité, capteurs de pression, capteurs de diagnostic moteur, etc. L’utilisation continue des données de ces capteurs aide le service de transport public à garantir rapidité, sécurité et fiabilité. Cependant, une rupture dans les données provenant de ces capteurs signifierait qu’il y a un problème.
La plupart des frameworks d’accès aux données, y compris les langages de requête et les bibliothèques importables, vous permettent de filtrer et de voir les colonnes ou les lignes où les données sont manquantes. Le concept de continuité et d’exhaustivité des données n’est plus pertinent nulle part que lorsqu’il s’agit de données de séries chronologiques. Par définition, les données de séries chronologiques doivent être continues. Cependant, la granularité du continuum peut différer selon les exigences.
Lorsque vous devez tester l’exhaustivité de vos données dans une base de données relationnelle, vous devez souvent écrire des requêtes SQL complexes associées à des tables intermédiaires ou temporaires pour trouver les données manquantes. Dans certains cas, ces requêtes peuvent être fastidieuses et non performantes. QuestDB est une base de données de séries chronologiques qui vous permet de stocker et de consommer vos données sous forme de tableau, mais ce n’est pas ce que vous appelleriez une base de données relationnelle traditionnelle. Pour répondre aux charges de travail de séries chronologiques, QuestDB étend les fonctionnalités SQL standard à l’aide d’extensions SQL. L’une de ces extensions est la SAMPLE BY extension, qui vous permet de trouver et de traiter facilement les données manquantes.
Ce didacticiel vous expliquera comment utiliser les extensions SQL de QuestDB pour trouver des lacunes dans vos données sans requêtes complexes ni surcharge.
Base de données
Pour démontrer comment trouver des lacunes dans les données de séries chronologiques, nous utiliserons le trades ensemble de données, qui est facilement disponible sur le site Web de démonstration QuestDB. Les trades L’ensemble de données contient des données commerciales anonymisées en temps réel pour Bitcoin et Ethereum en dollars américains du 8 mars 2022 à ce jour. Voici la structure de table du trades base de données:
CREATE TABLE ‘trades’ (
symbol SYMBOL capacity 256 CACHE,
side SYMBOL capacity 256 CACHE,
price DOUBLE,
amount DOUBLE,
timestamp TIMESTAMP
) timestamp (timestamp) PARTITION BY DAY;Pour plus de détails sur l’ensemble de données et le processus utilisé pour ingérer des données dans QuestDB, veuillez consulter l’article ‘Démo de données cryptographiques en direct diffusées avec QuestDB et Grafana’. Maintenant que vous comprenez la structure et le contenu du trades ensemble de données, essayons de déterminer s’il manque quelque chose.
Recherche de données manquantes
Utiliser QuestDB
Comme mentionné précédemment dans l’article, vous pouvez utiliser des extensions SQL pour rechercher les données manquantes dans QuestDB. Il existe trois mots clés (ou phrases clés SQL) que vous devez connaître et qui sont uniques à QuestDB :
SAMPLE BYvous permet de créer des groupes et des ensembles de données basés sur des plages de temps.FILLL’option vous permet de spécifier un comportement de remplissage lors de l’utilisationSAMPLE BYqui, à son tour, vous permet d’effectuer une interpolation de séries chronologiques sur les données.ALIGN TO CALENDARvous permet d’aligner vos intervalles de temps sur une date calendaire basée sur un fuseau horaire ou un décalage.
Vous pouvez trouver les données manquantes en utilisant une combinaison des extensions SQL susmentionnées. Tout d’abord, examinons une requête de base utilisant ces extensions pour obtenir un décompte quotidien des transactions en décembre jusqu’à la date de cette année, en utilisant la requête suivante :
SELECT timestamp,
COUNT(*) trades_in_december_2022
FROM 'trades'
WHERE timestamp IN '2022–12'
SAMPLE BY 1d
ALIGN TO CALENDAR;L’exécution de cette requête nous donne le résultat suivant lors de la sélection de « Dessiner » dans la vue Graphique :
Notez que l’exécution d’une requête agrégée aussi simple avec une faible granularité est possible sur n’importe quelle base de données. Cela ne devient un problème avec d’autres bases de données que lorsque les données sont trop granulaires, en particulier en temps réel. Maintenant que vous savez comment utiliser les extensions SQL dont vous avez besoin, passons à notre requête qui trouve les données manquantes.
Dans la requête, nous trouverons l’indicateur de prix moyen pondéré en fonction du volume (VWAP) pour le trades base de données. L’idée clé est d’obtenir tous les horodatages où nous n’avons pas de données pour calculer le VWAP pour tous les échanges Bitcoin depuis la date de début de l’ensemble de données jusqu’à maintenant. Dans la requête suivante, vous pouvez voir que les transactions sont échantillonnées par 1 seconde à l’aide de la SAMPLE BY 1s déclaration:
WITH extract AS (
SELECT timestamp,
ROUND(SUM(price * amount) / SUM(amount),2) AS vwap_price,
ROUND(SUM(amount),2) AS volume
FROM trades
WHERE symbol="BTC-USD"
AND timestamp > DATEADD('d', -1, NOW())
SAMPLE BY 1s
ALIGN TO CALENDAR)
SELECT timestamp,
vwap_price,
volume
FROM extract
WHERE vwap_price IS NULL
OR vwap_price = 0;Cependant, la requête ne donne rien, comme le montre l’image ci-dessous :
Pourquoi? Parce que QuestDB ne renverra rien s’il n’y a pas de données pour un horodatage ou une plage d’horodatage basée sur le SAMPLE BY agrégateur. Pour obtenir les données manquantes des résultats, vous devrez utiliser le FILL mot-clé comme celui-ci :
En utilisant cela, vous pouvez forcer le remplissage de tous les enregistrements de valeur zéro pour vwap_price avec NULL. La requête complète pour trouver les données manquantes ressemblera à ceci :
WITH extract AS (
SELECT timestamp,
ROUND(SUM(price * amount) / SUM(amount),2) AS vwap_price,
ROUND(SUM(amount),2) AS volume
FROM trades
WHERE symbol="BTC-USD"
AND timestamp > DATEADD('d', -1, NOW())
SAMPLE BY 1s FILL(NULL)
ALIGN TO CALENDAR)
SELECT timestamp,
vwap_price,
volume
FROM extract
WHERE vwap_price IS NULL;Lorsque vous exécuterez la requête, vous obtiendrez toutes les fenêtres 1s où les données manquaient, comme indiqué dans l’image ci-dessous :
Encore une fois, il est indéniable que l’exécution de requêtes ponctuelles et ad hoc qui s’agrègent sur des dimensions de granularité inférieures, telles que 1d ou 1m, peut ne pas être si difficile à faire dans d’autres bases de données. Toutefois, si vous souhaitez continuer à exécuter ces requêtes à grande échelle, elles peuvent créer des problèmes de performances dans votre base de données relationnelle traditionnelle. Vous pouvez obtenir des résultats similaires dans d’autres bases de données si vous souhaitez rechercher quotidiennement des données manquantes, comme le montre l’image ci-dessous :
PostgreSQLName
Si vous deviez effectuer la même opération dans une base de données PostgreSQL, vous auriez besoin d’exécuter un generate_series() fonction pour générer un tas de données, puis le joindre avec le trades base de données. Par souci de simplicité, supposons que le format d’horodatage généré par les deux systèmes sera le même. Pour identifier les lacunes dans PostgreSQL, vous devrez écrire quelque chose comme ceci :
WITH all_seconds AS (
SELECT *
FROM generate_series('2022–12–17 00:00:00',
'2022–12–17 23:59:59',
INTERVAL '1 second')
dummy_timestamp)
SELECT *
FROM (SELECT s.dummy_timestamp,
ROUND(SUM(t.price * t.amount) / SUM(t.amount),2) AS vwap_price,
ROUND(SUM(t.amount),2) AS volume
FROM all_seconds AS s
LEFT JOIN trades AS t
ON s.dummy_timestamp = t.timestamp
GROUP BY s.dummy_timestamp)
WHERE t.vwap_price IS NULL;PostgreSQL a l’avantage d’avoir une fonction de générateur qui prend en charge toutes sortes de cas d’utilisation de génération de données factices, comme vous l’avez vu ci-dessus. Toutes les bases de données n’ont pas cette fonction. Dans MySQL, par exemple, vous devez utiliser des expressions de table communes récursives (CTE) pour faire le travail. Dans certaines autres bases de données, cela peut être encore plus gênant.
Comment la recherche de données manquantes aide-t-elle ?
L’identification des données manquantes est de la plus haute importance car elle peut avoir un impact considérable sur l’exactitude et la fiabilité de chaque système ou personne qui les consomme. En ce qui concerne les bases de données de séries chronologiques, de nombreux cas d’utilisation viennent à l’esprit, en particulier ceux qui impliquent des dispositifs informatiques de pointe et des dispositifs IoT, tels que des capteurs et des détecteurs.
Prenons l’exemple des capteurs qui envoient des données sur les systèmes critiques des machines industrielles, telles que les vibrations, les vibrations, le couple, la pression, etc. Les données provenant de bon nombre de ces capteurs contribuent non seulement à améliorer l’efficacité de la machine, mais également à détecter les premiers signes de défaillances possibles de la machine. Dans de nombreux cas, ces données peuvent également contribuer à améliorer la sécurité et la fiabilité. Si le flux continu de données de séries chronologiques est interrompu, c’est-à-dire que les données sont manquantes, les avantages susmentionnés d’avoir des données en temps réel sont perdus – et peuvent causer plus de dégâts que prévu, car la fiabilité de ces systèmes dépend beaucoup. . C’est pourquoi il est très utile d’identifier les données manquantes, et QuestDB rend cela très facile.
Conclusion
Poursuivant notre thème sur les extensions SQL, ce didacticiel vous a guidé dans la recherche de données manquantes à l’aide de SAMPLE BY, FILLet ALIGN BY CALENDAR mots-clés avec des requêtes simples et très performantes. Cet article a également exploré certains avantages de l’identification des données manquantes, en particulier dans les ensembles de données de séries chronologiques. Maintenant, il est temps pour vous de tenter le coup. Un système est prêt pour vous sur le site Web de démonstration. Emmenez-le faire un tour!