Agréger les données de séries chronologiques avec TimescaleDB

Avec des millions de connexions API chaque mois, nous ne pouvons pas analyser les tendances et les performances des produits à l'aide de requêtes directes et lentes sur notre énorme base de données d'analyse. Nous avons plutôt besoin d'un moyen simple de :

• agrégez rapidement d'énormes quantités de données.
• produire des résumés périodiques.

Voici Les agrégats continus de TimescaleDB. Chez Mindee, nous suivons et stockons chaque appel d'API avec Timescaledb, une base de données SQL chronologique, afin de comprendre les performances de nos API et de trouver des moyens de les améliorer.

Les agrégats continus de TimescaleDB collectent des données dans les vues matérialisées de PostgreSQL, ce qui vous permet d'exécuter des requêtes plus rapidement. Ils peuvent être actualisés automatiquement, soit en continu, soit en suivant une politique, soit sur l'ensemble de la base de données, soit sur une plage spécifique pour aider à flux de travail documentaires. L'avantage est qu'ils sont très rapides puisqu'ils ne prennent en compte que les données qui ont changé depuis le dernier rafraîchissement.

Prérequis

• Échelle de temps DB 2+
• Postgres 12+

Création d'un agrégat continu

Créons un agrégat continu trivial, basé sur un réponses_API table.

Nous agrégerons par heure la somme des réponses réussies de l'API, la somme des erreurs, la taille moyenne des fichiers et le temps de calcul maximal pour chaque client.

CREATE MATERIALIZED VIEW api_responses_hourly_summaryWITH (timescaledb.continuous, timescaledb.materialized_only=true) ASSELECT time_bucket(INTERVAL '1 hour', created_at) AS time_bucket,    customer_name AS customer_name,    COUNT(CASE WHEN response_status = 'success' THEN 1 END) AS successes,    COUNT(CASE WHEN response_status = 'error' THEN 1 END) AS errors,    AVG(file_size) AS avg_file_size,    MAX(processing_time) AS max_processing_timeFROM api_responsesGROUP BY customer_name, time_bucketWITH NO DATA;

À partir de l'exemple ci-dessus, l'option timescaledb.materialized_only=true désactive l'agrégation en temps réel et l'option SANS DONNÉES crée instantanément l'agrégat continu sans actualisation des données.

Remarque : rejoindre, filtrer, trier par, et distincte ne peut pas être utilisé dans la création de l'agrégat.

Si vous souhaitez supprimer ce que nous venons de faire, SUPPRIMER LA VUE MATÉRIALISÉE La commande supprimera l'agrégat de votre choix.

DROP MATERIALIZED VIEW api_responses_hourly_summary;

Ajouter une politique de rétention

Vous pouvez ajouter une politique pour supprimer automatiquement les données de votre agrégat au bout d'un certain temps. Par exemple, la commande suivante créera une politique qui supprimera les données de l'agrégat au bout de 6 mois.

SELECT add_retention_policy('api_responses_hourly_summary', INTERVAL '6 months');

Et ce qui suit supprimera la politique de rétention.

SELECT remove_retention_policy('api_responses_hourly_summary');

Rafraîchir l'agrégat continu

Il existe trois manières d'actualiser les données pour obtenir un agrégat continu :

• manuellement sur une fenêtre de temps sélectionnée.
• en suivant périodiquement une politique d'actualisation automatique.
• en continu grâce à une agrégation en temps réel.

Rafraîchir manuellement un agrégat continu

À l'heure actuelle, notre agrégat continu ne contient aucune donnée. Remplissons-le par une actualisation manuelle au cours des 6 derniers mois.

CALL refresh_continuous_aggregate('api_responses_hourly_summary', '2022-01-01', '2022-04-30');

Les deux dates figurant dans les paramètres sont le début et la fin de la fenêtre temporelle sur laquelle l'agrégat sera calculé. Gardez également à l'esprit que même pour cette fenêtre temporelle, l'agrégat ne sera calculé que pour les données qui nécessitent réellement un calcul.

Comme vous avez probablement beaucoup de données, je vous suggère de ne commencer votre première actualisation que sur une courte période de temps plutôt que sur tout. Lors des tests, il était beaucoup plus rapide d'effectuer plusieurs petites actualisations sur de petites fenêtres que d'en effectuer une seule sur une grande fenêtre.

Savais-tu que tu pourrais aussi rationaliser les processus RH grâce à l'IA?

Créez une politique d'actualisation périodique

Si nous voulons que l'agrégat mette à jour ses données périodiquement, nous pouvons ajouter une politique d'actualisation. La politique repose sur trois arguments :

• start_offset : début de la fenêtre d'actualisation, par rapport au moment où l'actualisation s'exécute.
• end_offset : la fin de la fenêtre d'actualisation, par rapport au moment où l'actualisation s'exécute.
• schedule_interval : l'intervalle entre l'exécution de deux actualisations

La politique suivante permettra d'actualiser les données toutes les 10 minutes au cours des deux dernières heures.

SELECT add_continuous_aggregate_policy('api_responses_hourly_summary',    start_offset => INTERVAL '2 hour',    end_offset => INTERVAL '0 hour',    schedule_interval => INTERVAL '10 minutes');

Il peut ensuite être supprimé à l'aide des méthodes suivantes :

Optez pour une agrégation continue en temps réel

TimescaleDB propose une option d'agrégation en temps réel qui, contrairement à la politique d'actualisation, permet à votre agrégat d'être toujours à jour. La commande suivante permet d'activer l'agrégation en temps réel :

ALTER MATERIALIZED VIEW api_responses_hourly_summary set (timescaledb.materialized_only = false);

N'oubliez pas d'en savoir plus sur notre Modèle LiLT!

Interrogez l'agrégat

TimescaleDB étant basé sur PostgreSQL, les agrégats continus peuvent être interrogés à l'aide de n'importe quelle commande SQL standard.

SELECT customer_name, successes, errorFROM api_responses_hourly_summaryWHERE customer_name = 'my_number_one_customer'GROUP BY time_bucket

Conclusion

Lorsque vous travaillez avec des données déjà agrégées par tranches, certaines mesures telles que la moyenne, la médiane ou les percentiles peuvent être difficiles à calculer. Heureusement, le boîte à outils TimescaleDB officielle fournit de nombreux outils d'analyse permettant de calculer des approximations de plusieurs mesures.