Couche Silver

Bientôt disponible !

L'architecture de la couche Silver décrite dans ce document est actuellement en cours de développement. Bien que les capacités de transformation de base soient opérationnelles, le système de configuration et ses détails d'implémentation peuvent évoluer avant la version finale. Si vous êtes intéressé par un accès anticipé ou si vous avez des questions sur cette fonctionnalité, veuillez contacter [email protected].

La couche Silver transforme les données télématiques brutes et les informations métier en entités normalisées prêtes pour les requêtes, avec des métriques et des structures prédéfinies. La couche Bronze contient tout ce qui est capturé depuis les appareils et les systèmes — points individuels, événements et valeurs de champ pratiques pour la vérification et le dépannage. La couche Silver traite ces données brutes en entités significatives telles que trajets, visites de zones et états opérationnels via des transformations configurables qui nettoient, standardisent et agrègent les données en objets analytiques compréhensibles.

💡 Couche Silver en bref: Bronze est tout ce qui est collecté, Silver est ce avec quoi vous pouvez travailler.

Cette couche intermédiaire élimine le travail ETL manuel répétitif et prépare les données pour des analyses pratiques. Les opérateurs de flotte obtiennent des réponses aux questions courantes sans traitement de données étendu, tandis que les intégrateurs disposent d'une base stable pour construire des fonctionnalités évolutives.

Architecture et capacités

La couche Silver organise les données traitées en deux schémas distincts reflétant différentes sources de transformation et schémas d'accès. Les deux schémas fonctionnent au niveau de la couche Silver de l'architecture medallion, positionnée au-dessus des schémas de la couche Bronze (raw_business_data et raw_telematics_data) et en dessous de la couche Gold.

Structure du schéma

La couche Silver utilise une approche de schéma dynamique où les structures de base de données se forment automatiquement en fonction des transformations actives. Contrairement à la couche Bronze avec ses définitions de schéma fixes, les schémas de la couche Silver contiennent uniquement les tables correspondant aux transformations configurées et déployées. Cela signifie que les tables disponibles et leurs structures dépendent des transformations actuellement actives dans votre IoT Query instance.

Les données de la couche Silver sont organisées en deux schémas PostgreSQL :

• processed_common_data: Contient les transformations développées et maintenues par Navixy. Ce schéma est partagé entre tous les clients, fournissant des entités analytiques standardisées qui répondent aux cas d'utilisation télématiques courants. Les tables apparaissent dans ce schéma au fur et à mesure que Navixy développe et déploie de nouvelles transformations pour couvrir des besoins analytiques largement applicables.

• processed_custom_data: Contient les transformations spécifiques aux clients créées pour répondre à des besoins métier uniques. Chaque client dispose d'une instance isolée de ce schéma, permettant des entités analytiques personnalisées sans affecter les autres clients. Les tables de ce schéma correspondent aux transformations configurées spécifiquement pour votre organisation.

Les deux schémas fonctionnent via des configurations de transformation basées sur JSON. Lorsqu'une transformation est configurée et activée, le système crée automatiquement la structure de table correspondante dans le schéma approprié. Lorsque des transformations sont supprimées ou désactivées, leurs tables peuvent être archivées ou supprimées en fonction des politiques de rétention des données.

Cette formation dynamique explique pourquoi la documentation de la couche Silver ne fournit pas de descriptions de schéma fixes comme le fait la couche Bronze. Au lieu de cela, les tables disponibles et leurs structures reflètent les transformations spécifiques configurées pour votre instance IoT Query. Pour comprendre quelles données sont disponibles dans votre couche Silver, examinez la documentation des transformations déployées dans votre instance.

Architecture de traitement

Les transformations de la couche Silver fonctionnent via une architecture pilotée par configuration qui sépare la logique métier de l'orchestration. Chaque transformation est définie par une configuration JSON qui spécifie la logique de traitement SQL, les paramètres, la planification et le comportement de recalcul. Apache Airflow gère le cycle d'exécution en appliquant ces configurations pour traiter les données de la couche Bronze en entités de la couche Silver.

La structure de configuration JSON reste identique pour les transformations communes et personnalisées, assurant des schémas de traitement cohérents pour toutes les entités de la couche Silver. Cette approche de configuration unifiée permet un déploiement flexible des transformations tout en maintenant une exécution standardisée et le contrôle des versions.

Pour des informations détaillées sur le système de configuration JSON, voir le Configuration JSON section.

Fraîcheur des données

Les entités de la couche Silver sont maintenues automatiquement via des processus planifiés définis dans les configurations de transformation. Lorsque vous interrogez les données de la couche Silver, tenez compte des caractéristiques de traitement suivantes :

• Mises à jour planifiées: Chaque transformation traite les nouvelles données de la couche Bronze selon son planning configuré. Les mises à jour ont généralement lieu toutes les heures ou toutes les quelques heures selon la complexité de la transformation.

• Fenêtres de traitement: Les transformations fonctionnent sur des fenêtres temporelles afin de traiter efficacement des segments de données gérables plutôt que des jeux de données entiers.

• Impact du recalcul: Lorsque des modifications de configuration déclenchent un recalcul, les données récentes peuvent présenter de brèves incohérences pendant les fenêtres de traitement.

• Comportement spécifique au schéma: Les transformations dans processed_common_data se mettent à jour simultanément pour tous les clients partageant ce schéma. Les transformations dans processed_custom_data s'exécutent indépendamment par client, permettant une planification et une logique de traitement personnalisées.

Configuration JSON

Cette section décrit l'architecture de configuration actuellement en développement. La structure JSON et les spécifications de paramètres présentées ici représentent l'approche d'implémentation prévue. Les détails finaux d'implémentation peuvent différer à mesure que le développement progresse.

La couche Silver fonctionne sur une architecture pilotée par configuration où les transformations sont définies par des spécifications JSON. Chaque configuration contient la logique de traitement, les paramètres de transformation, les règles de planification et les politiques de recalcul qui déterminent comment les données de la couche Bronze deviennent des entités de la couche Silver.

Structure JSON

Une configuration de transformation se compose de quatre sections :

• version (string) : Version de la configuration suivant le versionnage sémantique

• metadata (object) : Informations de base incluant nom, description, horodatage de création et identifiant du créateur

• sql_template (object) : Spécification de la logique de traitement incluant chemins des fichiers SQL, définitions de table cible et paramètres de transformation

• target (object) : Emplacement de sortie spécifiant le schéma et la table

• scheduler (object) : Contrôle d'exécution incluant l'expression cron, le statut d'activation et la configuration de backfill

Schéma de configuration

{
  "version": "<semantic_version>",
  "metadata": {
    "name": "<transformation_name>",
    "description": "<transformation_description>",
    "created_at": "<iso_8601_timestamp>",
    "created_by": "<creator_identifier>"
  },
  "sql_template": {
    "sql_file": "<path_to_primary_sql_script>",
    "sql_file_1": "<path_to_additional_sql_script>",
    "table_name": "<schema.table_for_source_data>",
    "parameters": {
      "<parameter_name_1>": "<parameter_value_1>",
      "<parameter_name_2>": "<parameter_value_2>"
    }
  },
  "target": {
    "schema": "<output_schema>",
    "table": "<output_table>"
  },
  "scheduler": {
    "cron_expression": "<cron_schedule>",
    "enabled": <boolean>,
    "backfill": {
      "backfill_allowed": <boolean>,
      "start_date": "<iso_8601_date>",
      "end_date": "<iso_8601_date>"
    }
  }
}

Paramétrage des scripts SQL

Les scripts SQL de transformation référencent les paramètres de configuration en utilisant des espaces réservés préfixés par deux-points. Le système substitue les valeurs réelles depuis la configuration lors de l'exécution des scripts et fournit automatiquement des paramètres de fenêtre temporelle standard :

• :window_start - Début de la fenêtre de traitement (horodatage ISO-8601)

• :window_end - Fin de la fenêtre de traitement (horodatage ISO-8601)

Les paramètres personnalisés sont définis dans la section sql_template.parameters et contrôlent la logique spécifique à la transformation, telle que les seuils de qualité, les règles métier et les méthodes de calcul.

Exemple SQL avec paramètres :

SELECT device_id, device_time, speed
FROM raw_telematics_data.tracking_data_core
WHERE device_time >= :window_start
  AND device_time < :window_end
  AND satellites >= :min_satellites
  AND speed >= :min_speed_kmh;

Gestion des versions de configuration

Lorsqu'un paramètre de configuration change, une nouvelle version est créée. Chaque version représente un ensemble spécifique de règles de traitement qui étaient actives pendant une période donnée, permettant de suivre l'évolution de la logique de transformation.

Déclencheurs de création de version:

• Tout paramètre dans sql_template.parameters change

• Les chemins des fichiers de script SQL sont modifiés

• Le schéma cible ou la table change

• Les réglages du planificateur ou du backfill sont ajustés

Application de la version: Lorsqu'une nouvelle version de configuration est créée et appliquée, le système traite les données en fonction du mode de recalcul sélectionné.

Modes de recalcul

Le système de configuration prend en charge trois modes de recalcul qui contrôlent la façon dont les modifications de paramètres affectent les données historiques et futures. Ces modes offrent de la flexibilité pour équilibrer les exigences de cohérence des données et l'efficacité du traitement.

Recalcul vers l'avant uniquement

Le mode vers l'avant applique les nouveaux paramètres de configuration uniquement aux données traitées après le changement de version. Les données historiques restent inchangées, préservant les valeurs calculées avec les paramètres précédents.

Quand utiliser: Ajustements mineurs de paramètres qui ne changent pas fondamentalement la définition des entités, tester de nouveaux paramètres avant un recalcul complet, ou gérer les coûts de calcul en évitant le retraitement historique.

Comportement: Si vous changez min_speed_kmh de 3 à 5 le 8 décembre, seuls les trajets traités à partir du 8 décembre utiliseront le nouveau seuil. Les trajets calculés avant le 8 décembre conservent leurs valeurs d'origine.

Recalcul complet de l'historique

Le mode de recalcul complet traite toutes les données historiques dans la plage de dates de backfill configurée en utilisant les nouveaux paramètres. Le système remplace toutes les entités existantes par des valeurs nouvellement calculées.

Quand utiliser: Changements fondamentaux des définitions d'entités ou des algorithmes de détection, correction d'erreurs systémiques dans des calculs précédents, ou standardisation de toutes les données historiques selon les règles métier actuelles.

Comportement: Modifier la logique de détection des trajets nécessite de recalculer tous les trajets afin d'assurer des définitions d'entités cohérentes sur l'ensemble de la période.

Recalcul partiel

Le mode de recalcul partiel traite une fenêtre temporelle limitée de données historiques, typiquement des jours ou semaines récentes.

Quand utiliser: Correction de problèmes de qualité des données récents, mise à jour de paramètres qui affectent principalement les schémas opérationnels récents, ou mise en œuvre de changements avec un impact historique limité.

Configuration: Spécifiez un paramètre backfill_days (par ex., 7 pour la semaine dernière) soit dans la configuration soit lors du déclenchement manuel du recalcul. Le système met à jour les enregistrements existants dans la fenêtre temporelle spécifiée.

Transformations disponibles

La couche Silver fournit actuellement deux groupes de transformations qui démontrent l'approche pilotée par configuration et servent de modèles pour développer des entités personnalisées.

Trajets

Il s'agit d'une transformation de suivi de mouvement qui identifie des segments de mouvement continus à partir des données de suivi brutes et calcule des métriques complètes de trajet.

Référence rapide:

• But: Convertir des données de localisation au niveau point en analyses au niveau trajet

• Table principale: business_data.tracks

• Métriques clés: Distance, durée, statistiques de vitesse, limites géographiques

• Données sources: raw_telematics_data.tracking_data_core, raw_telematics_data.states

Table : business_data.tracks

La table tracks stocke des informations agrégées sur les segments de mouvement continu avec des métriques pré-calculées et un contexte géographique.

Clé primaire: track_id (identifiant unique auto-incrémenté)

Descriptions des champs:

Le track_id champ identifie de manière unique chaque segment de trajet. Le device_id champ référence l'appareil de suivi depuis la couche Bronze. Le track_start_time et track_end_time champs définissent les frontières temporelles du trajet. Le track_duration champ fournit un format de durée lisible par l'humain tandis que track_duration_seconds permet des calculs numériques. Le track_distance_meters champ contient la distance totale parcourue. Les champs de vitesse (avg_speed, max_speed, min_speed) fournissent un résumé statistique en kilomètres par heure. Les coordonnées de départ (latitude_start, longitude_start, altitude_start) et les coordonnées de fin (latitude_end, longitude_end, altitude_end) définissent les limites géographiques. Le points_in_track champ indique la qualité des données via le nombre de points. Les start_zone et end_zone champs se lient aux données de référence des zones lorsque les trajets commencent ou se terminent dans des zones définies.

Relations de données:

-- Jointure avec les informations sur l'appareil
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id

-- Jointure avec les détails du véhicule
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id

Algorithme de détection des trajets

La transformation identifie les trajets en utilisant la détection de mouvement qui analyse la vitesse, la distance et les schémas temporels. Un trajet représente un segment continu de mouvement séparé des autres trajets par des périodes de stationnement ou des lacunes de données.

Le système démarre un nouveau trajet lorsque le premier point de suivi apparaît pour un appareil, lorsque le mouvement commence après une durée de stationnement dépassant le seuil configuré, lorsque le mouvement reprend après une lacune de données dépassant le délai d'attente configuré, ou lorsqu'un seul point de localisation LBS (tour cellulaire) est enregistré. Le système termine un trajet lorsque le mouvement s'arrête et que la durée de stationnement atteint le seuil configuré, ou lorsqu'une lacune de données dépassant le délai d'attente se produit.

Classification du mouvement:

• En mouvement: Vitesse ≥ seuil configuré

• Stationnement: Vitesse < seuil ET durée ≥ durée de stationnement configurée

• Lacune de données: Temps entre points ≥ délai de séparation configuré

Validation de la qualité: Les trajets générés doivent respecter des seuils de qualité configurables pour être inclus — minimum 2 points de suivi, vitesse maximale ≥ seuil configuré, distance totale ≥ seuil configuré, et heures de début et de fin définies. Le système filtre les données anormales incluant des vitesses irréalistes pour les points LBS, des coordonnées nulles et une couverture satellitaire insuffisante.

Calcul des métriques: Les métriques de trajet sont calculées à partir des points de suivi validés. La distance représente la longueur géométrique totale. Les statistiques de vitesse incluent la moyenne, le maximum et le minimum à partir des vitesses des points. La durée est la différence temporelle entre les heures de fin et de début. Les limites géographiques capturent les coordonnées du premier et du dernier point. L'association aux zones correspond aux zones de départ et d'arrivée issues des données de référence lorsque les trajets commencent ou se terminent dans des zones définies.

Paramètres de configuration

Paramètre	Description	Unité
min_parking_seconds	Seuil de durée pour la détection de stationnement	secondes
tracks_split_timeout_seconds	Délai maximal entre les points avant de scinder les trajets	secondes
min_distance_meters	Distance minimale du trajet pour la validation de qualité	mètres
min_speed_kmh	Seuil de vitesse minimale pour la détection de mouvement	km/h
max_lbs_speed_kmh	Vitesse maximale réaliste pour les points LBS	km/h
min_satellites	Nombre minimum de satellites pour la qualité GPS	nombre

Exemple de configuration

{
  "version": "1.0.0",
  "metadata": {
    "name": "Movement Tracking Transformation",
    "description": "Processes raw tracking data into trip entities with quality validation and metric calculation",
    "created_at": "2025-01-15T00:00:00Z",
    "created_by": "system"
  },
  "sql_template": {
    "sql_file": "/opt/airflow/dags/repo/sql-file/tracks.sql",
    "table_name": "business_data.tracks",
    "parameters": {
      "min_parking_seconds": 300,
      "tracks_split_timeout_seconds": 1200,
      "min_distance_meters": 100,
      "min_speed_kmh": 3,
      "max_lbs_speed_kmh": 200,
      "min_satellites": 4
    }
  },
  "target": {
    "schema": "business_data",
    "table": "tracks"
  },
  "scheduler": {
    "cron_expression": "0 */8 * * *",
    "enabled": true,
    "backfill": {
      "backfill_allowed": true,
      "start_date": "2024-01-01",
      "end_date": "2025-12-31"
    }
  }
}

Exemple de script SQL

L'exemple SQL simplifié suivant démontre l'utilisation de paramètres dans la logique de transformation :

-- Détection de trajets avec paramètres configurables
WITH tracking_with_gaps AS (
    SELECT 
        device_id,
        device_time,
        latitude,
        longitude,
        speed,
        satellites,
        LAG(device_time) OVER (PARTITION BY device_id ORDER BY device_time) as prev_time,
        LAG(speed) OVER (PARTITION BY device_id ORDER BY device_time) as prev_speed
    FROM raw_telematics_data.tracking_data_core
    WHERE device_time >= :window_start
      AND device_time < :window_end
      AND satellites >= :min_satellites
      AND latitude != 0 AND longitude != 0
),
trip_boundaries AS (
    SELECT *,
        CASE 
            WHEN prev_speed < :min_speed_kmh 
                 AND EXTRACT(EPOCH FROM (device_time - prev_time)) >= :min_parking_seconds
                 AND speed >= :min_speed_kmh
            THEN true
            WHEN EXTRACT(EPOCH FROM (device_time - prev_time)) >= :tracks_split_timeout_seconds
            THEN true
            ELSE false
        END as is_trip_start
    FROM tracking_with_gaps
)
INSERT INTO business_data.tracks (device_id, track_start_time, track_end_time)
SELECT device_id, MIN(device_time), MAX(device_time)
FROM trip_boundaries
WHERE speed >= :min_speed_kmh
GROUP BY device_id, SUM(is_trip_start::int) OVER (PARTITION BY device_id ORDER BY device_time);

Exemples de requêtes

Obtenir tous les trajets pour un appareil spécifique :

SELECT 
    track_id,
    track_start_time,
    track_end_time,
    track_duration,
    track_distance_meters,
    avg_speed,
    start_zone,
    end_zone
FROM business_data.tracks
WHERE device_id = 12345
ORDER BY track_start_time DESC;

Calculer le résumé quotidien des distances :

SELECT 
    DATE(track_start_time) as trip_date,
    COUNT(*) as total_trips,
    SUM(track_distance_meters) / 1000.0 as total_km,
    AVG(avg_speed) as avg_speed_kmh,
    SUM(track_duration_seconds) / 3600.0 as total_hours
FROM business_data.tracks
WHERE device_id = 12345
  AND track_start_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE(track_start_time)
ORDER BY trip_date DESC;

Trouver les trajets entre des zones spécifiques :

SELECT 
    t.track_id,
    t.track_start_time,
    t.track_duration,
    t.track_distance_meters / 1000.0 as distance_km,
    t.start_zone,
    t.end_zone,
    v.license_plate
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE t.start_zone = 'Distribution Center'
  AND t.end_zone = 'Customer Site A'
  AND t.track_start_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY t.track_start_time DESC;

Géorepères

La transformation Geofences pré-calculer les limites géographiques en géométries PostGIS et suit quand les appareils entrent, restent à l'intérieur et sortent de ces zones définies. Ce traitement élimine la nécessité de calculs spatiaux en temps réel lors des requêtes, améliorant considérablement les performances pour les analyses basées sur la localisation.

Cette transformation illustre le traitement de données spatiales et la détection d'événements à partir de flux de localisation continus.

Référence rapide:

• But: Pré-calculer les géométries de geofence et suivre la présence des appareils dans des zones géographiques

• Tables principales: business_data.geofence_geometries, business_data.geofence_visits

• Métriques clés: Durée de visite, heures d'entrée/sortie, utilisation des geofences

• Bénéfice de performance: Requêtes spatiales 10 à 100x plus rapides vs. calcul de géométrie à la volée

• Données sources: raw_business_data.zones, raw_business_data.geofence_points, raw_telematics_data.tracking_data_core

Table : business_data.geofence_geometries

La table geofence_geometries stocke des représentations géométriques optimisées des geofences pour des requêtes spatiales efficaces.

Clé primaire: geofence_id

Descriptions des champs:

Le geofence_id champ identifie de manière unique chaque geofence et référence raw_business_data.zones.zone_id. Le geofence_type champ indique la forme du geofence (cercle, polygone ou itinéraire). Le geofence_label champ contient le nom du geofence pour l'affichage et la référence. Le adresse champ stocke la description de l'emplacement du geofence. Le color champ contient le code couleur HEX pour la visualisation. Le geofence_geom champ contient la représentation géographique pour les opérations spatiales. Les created_at et updated_at champs suivent les changements temporels.

Spécifications des types de geofence:

• Cercle: Défini par un point central et un rayon

• Polygone: Points ordonnés formant une forme fermée

• Itinéraire: Trajectoire linéaire avec rayon de tampon

Comportement de synchronisation: La table se synchronise automatiquement lorsque les données source de geofence changent dans la couche Bronze.

Table : business_data.geofence_visits

La table geofence_visits enregistre la présence historique des appareils dans les geofences, incluant l'heure d'entrée, l'heure de sortie et la durée de la visite.

Clé primaire: Clé composite sur (device_id, geofence_id, enter_time)

Descriptions des champs:

Le device_id champ référence l'appareil de suivi. Le geofence_id champ référence le geofence depuis geofence_geometries. Le enter_time champ indique quand l'appareil est entré dans le geofence. Le exit_time champ indique quand l'appareil est sorti (NULL pour les visites en cours). Le duration champ contient la durée calculée de la visite.

Relations de données:

-- Jointure avec les détails du geofence
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id

-- Jointure avec les informations sur l'appareil et le véhicule
FROM business_data.geofence_visits AS gv
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id

Algorithme de détection des visites

La transformation suit la présence des appareils dans les geofences en comparant les points de suivi aux géométries des geofences. Les enregistrements de visite capturent quand les appareils entrent, restent à l'intérieur et sortent des geofences définis.

Détection d'entrée: Le système détecte une entrée lorsqu'un point de suivi de l'appareil se trouve à l'intérieur de la géométrie du geofence et que le point précédent était à l'extérieur de ce geofence ou qu'aucun point précédent n'existe.

Détection de sortie: Le système détecte une sortie lorsqu'un point de suivi de l'appareil se trouve à l'extérieur de la géométrie du geofence et que le point précédent était à l'intérieur du geofence.

Regroupement des visites: Des paires consécutives entrée-sortie forment un seul enregistrement de visite. Les visites ouvertes (aucune sortie détectée) affichent NULL dans exit_time et sont mises à jour lorsque la sortie est détectée lors de cycles de traitement ultérieurs.

Calcul de la durée: La durée de la visite est calculée comme la différence temporelle entre les événements d'entrée et de sortie. Les visites ouvertes affichent une durée NULL jusqu'à ce qu'une sortie soit détectée.

Paramètres de configuration

Paramètre	Description	Unité
spatial_buffer_meters	Distance de tampon pour la détection des limites de geofence	mètres
min_visit_duration_seconds	Durée minimale de visite à enregistrer	secondes
max_visit_gap_seconds	Intervalle de temps maximal avant de considérer la visite terminée	secondes

Exemple de configuration

{
  "version": "1.0.0",
  "metadata": {
    "name": "Geofences Transformation",
    "description": "Pre-computes geofence geometries and tracks device presence within defined geographic areas",
    "created_at": "2025-01-15T00:00:00Z",
    "created_by": "system"
  },
  "sql_template": {
    "sql_file": "/opt/airflow/dags/repo/sql-file/geofence_visits.sql",
    "table_name": "business_data.geofence_visits",
    "parameters": {
      "spatial_buffer_meters": 50,
      "min_visit_duration_seconds": 60,
      "max_visit_gap_seconds": 1800
    }
  },
  "target": {
    "schema": "business_data",
    "table": "geofence_visits"
  },
  "scheduler": {
    "cron_expression": "0 * * * *",
    "enabled": true,
    "backfill": {
      "backfill_allowed": true,
      "start_date": "2024-01-01",
      "end_date": "2025-12-31"
    }
  }
}

Exemples de requêtes

Obtenir toutes les visites d'un geofence spécifique :

SELECT 
    gv.device_id,
    gv.enter_time,
    gv.exit_time,
    gv.duration,
    v.license_plate,
    gg.geofence_label
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE gg.geofence_label = 'Main Warehouse'
  AND gv.enter_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY gv.enter_time DESC;

Calculer les statistiques d'utilisation des geofences :

SELECT 
    gg.geofence_label,
    COUNT(*) as total_visits,
    COUNT(DISTINCT gv.device_id) as unique_devices,
    AVG(EXTRACT(EPOCH FROM gv.duration) / 60) as avg_duration_minutes,
    MAX(EXTRACT(EPOCH FROM gv.duration) / 60) as max_duration_minutes
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
WHERE gv.enter_time >= CURRENT_DATE - INTERVAL '30 days'
  AND gv.exit_time IS NOT NULL
GROUP BY gg.geofence_label
ORDER BY total_visits DESC;

Trouver les appareils actuellement présents :

SELECT 
    gg.geofence_label,
    gv.device_id,
    v.license_plate,
    gv.enter_time,
    NOW() - gv.enter_time as time_in_geofence
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE gv.exit_time IS NULL
ORDER BY gg.geofence_label, gv.enter_time;

Analyser les schémas d'entrée/sortie des geofences par heure :

SELECT 
    gg.geofence_label,
    EXTRACT(HOUR FROM gv.enter_time) as entry_hour,
    COUNT(*) as entries,
    AVG(EXTRACT(EPOCH FROM gv.duration) / 60) as avg_visit_minutes
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
WHERE gv.enter_time >= CURRENT_DATE - INTERVAL '30 days'
  AND gv.exit_time IS NOT NULL
GROUP BY gg.geofence_label, EXTRACT(HOUR FROM gv.enter_time)
ORDER BY gg.geofence_label, entry_hour;

Identifier les appareils avec les temps de séjour les plus longs :

SELECT 
    gg.geofence_label,
    v.license_plate,
    gv.enter_time,
    gv.exit_time,
    gv.duration,
    EXTRACT(EPOCH FROM gv.duration) / 3600 as duration_hours
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id
JOIN raw_business_data.devices AS d
  ON gv.device_id = d.device_id
JOIN raw_business_data.vehicles AS v
  ON d.object_id = v.object_id
WHERE gv.exit_time IS NOT NULL
  AND gv.enter_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY gv.duration DESC
LIMIT 20;

Développement d'entités personnalisées

La couche Silver illustre des modèles de transformation via ses transformations disponibles, qui servent de modèles pour développer des entités analytiques personnalisées. En utilisant les données de la couche Bronze, les capacités SQL et l'architecture de configuration, des entités personnalisées peuvent être développées pour répondre à des besoins métier spécifiques.

Approche de développement

Les entités personnalisées de la couche Silver suivent l'architecture pilotée par configuration décrite dans ce document. L'approche consiste à définir la logique de transformation dans des scripts SQL et à créer des configurations JSON qui spécifient les paramètres et les plannings pour une exécution automatisée.

Capacités clés: Agréger plusieurs points de données bruts en objets analytiques uniques, appliquer la logique métier et les règles de validation, pré-calculer des métriques pour accélérer les requêtes, maintenir la précision temporelle via un traitement planifié, et intégrer des opérations spatiales avec le contexte métier.

Types d'entités personnalisées potentiels

• Entités opérationnelles: États opérationnels et modes de travail spécifiques à l'entreprise, modèles de périodes de travail et suivi des cycles de service, métriques d'utilisation des actifs, détection des fenêtres de maintenance

• Entités comportementales: Notation de risque personnalisée basée sur plusieurs facteurs, analyse et classification des comportements de conduite, surveillance de la conformité avec seuils configurables, agrégation des indicateurs de sécurité

• Entités de performance: Mesures et KPI spécifiques au secteur, calculs d'efficacité utilisant des formules personnalisées, indicateurs d'optimisation des ressources, suivi de l'atteinte des niveaux de service

• Entités basées sur des événements: Détection d'événements personnalisée avec conditions complexes, agrégation d'alertes et reconnaissance de motifs, identification d'anomalies par méthodes statistiques, suivi des violations de seuils

Modèle de configuration

{
  "version": "1.0.0",
  "metadata": {
    "name": "<custom_entity_name>",
    "description": "<transformation_purpose_and_business_value>",
    "created_at": "<iso_8601_timestamp>",
    "created_by": "<developer_identifier>"
  },
  "sql_template": {
    "sql_file": "/opt/airflow/dags/repo/sql-file/<custom_entity>.sql",
    "table_name": "business_data.<custom_entity>",
    "parameters": {
      "<threshold_parameter>": <numeric_value>,
      "<time_window_parameter>": <numeric_value>,
      "<filter_enabled_parameter>": <boolean_value>
    }
  },
  "target": {
    "schema": "business_data",
    "table": "<custom_entity>"
  },
  "scheduler": {
    "cron_expression": "<cron_schedule>",
    "enabled": true,
    "backfill": {
      "backfill_allowed": true,
      "start_date": "<iso_8601_date>",
      "end_date": "<iso_8601_date>"
    }
  }
}

Directives pour les scripts SQL

Utilisez des valeurs paramétrées :

WHERE value >= :custom_threshold
  AND duration_seconds <= :time_window_seconds
  AND (:quality_filter_enabled = false OR quality_score >= 0.8)

Exploitez des fenêtres temporelles standard :

FROM raw_telematics_data.tracking_data_core
WHERE device_time >= :window_start
  AND device_time < :window_end

Structurez le traitement en étapes :

WITH raw_data AS (
    -- Extraire et filtrer les données source
),
enriched_data AS (
    -- Joindre aux données de référence et calculer des valeurs intermédiaires
),
aggregated_entities AS (
    -- Agréger dans la structure d'entité finale
)
INSERT INTO business_data.custom_entity (...)
SELECT ... FROM aggregated_entities;

Ressources supplémentaires

Pour les modèles de requêtes détaillés et le travail avec les données de la couche Silver, référez-vous à la IoT Query SQL Recipe Book.

Si vous êtes intéressé par un accès anticipé ou avez des questions sur cette fonctionnalité, veuillez contacter [email protected].