Capa Silver

¡Próximamente!

La arquitectura de la capa Silver descrita en este documento está actualmente en desarrollo. Si bien las capacidades centrales de transformación están operativas, el sistema de configuración y sus detalles de implementación pueden evolucionar antes del lanzamiento final. Si está interesado en acceso anticipado o tiene preguntas sobre esta funcionalidad, comuníquese con [email protected].

La capa Silver transforma datos telemáticos crudos e información de negocio en entidades normalizadas y listas para consultar con métricas y estructuras predefinidas. La capa Bronze contiene todo lo capturado desde dispositivos y sistemas: puntos individuales, eventos y valores de campo convenientes para verificación y solución de problemas. La capa Silver procesa estos datos crudos en entidades significativas como viajes, visitas a zonas y estados operativos mediante transformaciones configurables que limpian, estandarizan y agregan datos en objetos analíticos comprensibles.

💡 Capa Silver en resumen: Bronze es todo lo recogido, Silver es con lo que puede trabajar.

Esta capa intermedia elimina el trabajo ETL manual repetitivo y prepara los datos para análisis prácticos. Los operadores de flotas obtienen respuestas a preguntas comunes sin un extenso procesamiento de datos, mientras que los integradores disponen de una base estable para construir funcionalidades escalables.

Arquitectura y capacidades

La capa Silver organiza los datos procesados en dos esquemas distintos que reflejan diferentes orígenes de transformación y patrones de acceso. Ambos esquemas operan al nivel de la capa Silver de la arquitectura medallion, ubicados por encima de los esquemas de la capa Bronze (raw_business_data y raw_telematics_data) y por debajo de la capa Gold.

Estructura del esquema

La capa Silver utiliza un enfoque de esquema dinámico donde las estructuras de base de datos se forman automáticamente según las transformaciones activas. A diferencia de la capa Bronze con sus definiciones de esquema fijas, los esquemas de la capa Silver contienen solo las tablas que corresponden a las transformaciones configuradas y desplegadas. Esto significa que las tablas disponibles y sus estructuras dependen de qué transformaciones estén actualmente activas en su Consulta de IoT instancia.

Los datos de la capa Silver se organizan en dos esquemas de PostgreSQL:

• processed_common_data: Contiene transformaciones desarrolladas y mantenidas por Navixy. Este esquema se comparte entre todos los clientes, proporcionando entidades analíticas estandarizadas que abordan casos de uso telemático comunes. Las tablas aparecen en este esquema a medida que Navixy desarrolla y despliega nuevas transformaciones para cubrir requisitos analíticos ampliamente aplicables.

• processed_custom_data: Contiene transformaciones específicas del cliente creadas para abordar requisitos de negocio únicos. Cada cliente tiene una instancia aislada de este esquema, lo que permite entidades analíticas personalizadas sin afectar a otros clientes. Las tablas en este esquema corresponden a transformaciones configuradas específicamente para su organización.

Ambos esquemas operan mediante configuraciones de transformación basadas en JSON. Cuando una transformación se configura y activa, el sistema crea automáticamente la estructura de la tabla correspondiente dentro del esquema apropiado. Cuando las transformaciones se eliminan o desactivan, sus tablas pueden archivarse o eliminarse según las políticas de retención de datos.

Esta formación dinámica es la razón por la que la documentación de la capa Silver no proporciona descripciones de esquema fijas como lo hace la capa Bronze. En su lugar, las tablas disponibles y sus estructuras reflejan las transformaciones específicas configuradas para su instancia de IoT Query. Para entender qué datos están disponibles en su capa Silver, revise la documentación de transformaciones de las entidades que se hayan desplegado en su instancia.

Arquitectura de procesamiento

Las transformaciones de la capa Silver operan mediante una arquitectura orientada por configuración que separa la lógica de negocio de la orquestación. Cada transformación se define mediante una configuración JSON que especifica la lógica de procesamiento SQL, parámetros, programación y comportamiento de recálculo. Apache Airflow gestiona el ciclo de vida de ejecución, aplicando estas configuraciones para procesar los datos de la capa Bronze en entidades de la capa Silver.

La estructura de configuración JSON permanece idéntica tanto para transformaciones comunes como personalizadas, garantizando patrones de procesamiento coherentes en todas las entidades de la capa Silver. Este enfoque unificado de configuración permite un despliegue flexible de transformaciones manteniendo una ejecución estandarizada y control de versiones.

Para información detallada sobre el sistema de configuración JSON, vea la Configuración JSON sección.

Frescura de los datos

Las entidades de la capa Silver se mantienen automáticamente mediante procesos programados definidos en las configuraciones de transformación. Cuando consulte datos de la capa Silver, considere estas características de procesamiento:

• Actualizaciones programadas: Cada transformación procesa nuevos datos de la capa Bronze según su calendario configurado. Las actualizaciones normalmente ocurren cada hora o cada pocas horas según la complejidad de la transformación.

• Ventanas de procesamiento: Las transformaciones operan sobre ventanas temporales para procesar eficientemente segmentos de datos manejables en lugar de conjuntos de datos completos.

• Impacto del recálculo: Cuando los cambios de configuración provocan recálculo, los datos recientes pueden mostrar breves inconsistencias durante las ventanas de procesamiento.

• Comportamiento específico por esquema: Las transformaciones en processed_common_data se actualizan simultáneamente para todos los clientes que comparten ese esquema. Las transformaciones en processed_custom_data se ejecutan de forma independiente por cliente, permitiendo programación y lógica de procesamiento personalizadas.

Configuración JSON

Esta sección describe la arquitectura de configuración que actualmente está en desarrollo. La estructura JSON y las especificaciones de parámetros mostradas aquí representan el enfoque de implementación planificado. Los detalles finales de implementación pueden diferir a medida que avance el desarrollo.

La capa Silver opera sobre una arquitectura orientada por configuración donde las transformaciones se definen mediante especificaciones JSON. Cada configuración contiene la lógica de procesamiento, parámetros de transformación, reglas de programación y políticas de recálculo que determinan cómo los datos de la capa Bronze se convierten en entidades de la capa Silver.

Estructura JSON

Una configuración de transformación consta de cuatro secciones:

• version (cadena): Versión de la configuración siguiendo versionado semántico

• metadata (objeto): Información básica que incluye nombre, descripción, marca temporal de creación e identificador del creador

• sql_template (objeto): Especificación de la lógica de procesamiento que incluye rutas de archivos SQL, definiciones de tabla de destino y parámetros de transformación

• target (objeto): Ubicación de salida que especifica esquema y tabla

• scheduler (objeto): Control de ejecución que incluye programación cron, estado de habilitación y configuración de backfill

Esquema de configuración

{
  "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>"
    }
  }
}

Parametrización de scripts SQL

Los scripts SQL de transformación hacen referencia a parámetros de configuración usando marcadores de posición con prefijo de dos puntos. El sistema sustituye los valores reales de la configuración al ejecutar los scripts y proporciona automáticamente parámetros estándar de ventana temporal:

• :window_start - Inicio de la ventana de procesamiento (marca temporal ISO-8601)

• :window_end - Fin de la ventana de procesamiento (marca temporal ISO-8601)

Los parámetros personalizados se definen en la sql_template.parameters sección y controlan la lógica específica de la transformación, como umbrales de calidad, reglas de negocio y métodos de cálculo.

Ejemplo de SQL con parámetros:

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;

Control de versiones de configuración

Cuando cambia cualquier parámetro de configuración, se crea una nueva versión. Cada versión representa un conjunto específico de reglas de procesamiento que estuvieron activas durante un periodo de tiempo, lo que permite rastrear cómo evolucionó la lógica de transformación.

Desencadenantes de creación de versión:

• Cualquier parámetro en sql_template.parameters cambios

• Se modifican las rutas de archivos de scripts SQL

• Cambio de esquema o tabla de destino

• Se ajustan la programación o la configuración de backfill

Aplicación de la versión: Cuando se crea y aplica una nueva versión de configuración, el sistema procesa los datos según el modo de recálculo seleccionado.

Modos de recálculo

El sistema de configuración admite tres modos de recálculo que controlan cómo los cambios de parámetros afectan a los datos históricos y futuros. Estos modos ofrecen flexibilidad para equilibrar los requisitos de consistencia de datos con la eficiencia del procesamiento.

Recálculo hacia adelante únicamente

El modo hacia adelante aplica los nuevos parámetros de configuración solo a los datos procesados después del cambio de versión. Los datos históricos permanecen sin cambios, conservando los valores calculados con parámetros anteriores.

Cuándo usar: Ajustes menores de parámetros que no cambian fundamentalmente las definiciones de entidades, probar nuevos parámetros antes de un recálculo completo o gestionar costos de computación evitando el reprocesamiento histórico.

Comportamiento: Si cambia min_speed_kmh de 3 a 5 el 8 de diciembre, solo los viajes procesados a partir del 8 de diciembre usan el nuevo umbral. Los viajes calculados antes del 8 de diciembre mantienen sus valores originales.

Recálculo completo del historial

El modo de recálculo completo procesa todos los datos históricos dentro del rango de fechas de backfill configurado usando los nuevos parámetros. El sistema reemplaza todas las entidades existentes con valores recalculados.

Cuándo usar: Cambios fundamentales en las definiciones de entidades o en los algoritmos de detección, corregir errores sistemáticos en cálculos previos o estandarizar todos los datos históricos con las reglas de negocio actuales.

Comportamiento: Cambiar la lógica de detección de viajes requiere recalcular todos los viajes para asegurar definiciones de entidad coherentes en todo el rango temporal.

Recálculo parcial

El modo de recálculo parcial procesa una ventana temporal limitada de datos históricos, normalmente días o semanas recientes.

Cuándo usar: Corregir problemas recientes de calidad de datos, actualizar parámetros que afectan principalmente patrones operativos recientes o implementar cambios con impacto histórico limitado.

Configuración: Especifique un backfill_days parámetro (p. ej., 7 para la última semana) ya sea en la configuración o al activar manualmente el recálculo. El sistema actualiza los registros existentes dentro de la ventana temporal especificada.

Transformaciones disponibles

La capa Silver actualmente ofrece dos grupos de transformaciones que demuestran el enfoque orientado por configuración y sirven como plantillas para desarrollar entidades personalizadas.

Viajes

Es una transformación de rastreo de movimiento que identifica segmentos de movimiento continuo a partir de datos de seguimiento crudos y calcula métricas completas de viaje.

Referencia rápida:

• Propósito: Convertir datos de ubicación a nivel de punto en análisis a nivel de viaje

• Tabla principal: business_data.tracks

• Métricas principales: Distancia, duración, estadísticas de velocidad, límites geográficos

• Datos fuente: raw_telematics_data.tracking_data_core, raw_telematics_data.states

Tabla: business_data.tracks

La tabla tracks almacena información agregada sobre segmentos de movimiento continuo con métricas precalculadas y contexto geográfico.

Clave primaria: track_id (identificador único con incremento automático)

Descripciones de campos:

La track_id campo identifica de forma única cada segmento de viaje. El device_id campo referencia el dispositivo de seguimiento desde la capa Bronze. El track_start_time y track_end_time campos definen los límites temporales del viaje. El track_duration campo proporciona el formato de duración legible por humanos mientras que track_duration_seconds permite cálculos numéricos. El track_distance_meters campo contiene la distancia total recorrida. Los campos de velocidad (avg_speed, max_speed, min_speed) proporcionan un resumen estadístico en kilómetros por hora. Las coordenadas de inicio (latitude_start, longitude_start, altitude_start) y las coordenadas de fin (latitude_end, longitude_end, altitude_end) definen los límites geográficos. El points_in_track campo indica la calidad de los datos mediante el recuento de puntos. Los start_zone y end_zone campos enlazan con los datos de referencia de zonas cuando los viajes comienzan o terminan dentro de zonas definidas.

Relaciones de datos:

-- Unir con información del dispositivo
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id

-- Unir con detalles del vehículo
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

Algoritmo de detección de viajes

La transformación identifica viajes usando detección de movimiento que analiza velocidad, distancia y patrones temporales. Un viaje representa un segmento continuo de movimiento separado de otros viajes por periodos de estacionamiento o brechas de datos.

El sistema inicia un nuevo viaje cuando aparece el primer punto de seguimiento para un dispositivo, cuando comienza el movimiento después de una duración de estacionamiento que excede el umbral configurado, cuando se reanuda el movimiento tras una brecha de datos que excede el tiempo de espera configurado o cuando se registra un único punto de ubicación LBS (torre celular). El sistema finaliza un viaje cuando el movimiento se detiene y la duración de estacionamiento alcanza el umbral configurado, o cuando ocurre una brecha de datos que excede el tiempo de espera.

Clasificación del movimiento:

• En movimiento: Velocidad ≥ umbral configurado

• Estacionado: Velocidad < umbral Y duración ≥ duración de estacionamiento configurada

• Brecha de datos: Tiempo entre puntos ≥ tiempo de separación configurado

Validación de calidad: Los viajes generados deben cumplir umbrales de calidad configurables para ser incluidos: mínimo 2 puntos de seguimiento, velocidad máxima ≥ umbral configurado, distancia total ≥ umbral configurado y tiempos de inicio y fin definidos. El sistema filtra datos anómalos incluyendo velocidades irreales para puntos LBS, coordenadas cero y cobertura satelital insuficiente.

Cálculo de métricas: Las métricas del viaje se calculan a partir de puntos de seguimiento validados. La distancia representa la longitud geométrica total. Las estadísticas de velocidad incluyen valores promedio, máximo y mínimo a partir de las velocidades de los puntos. La duración es la diferencia temporal entre los tiempos de fin e inicio. Los límites geográficos capturan las coordenadas del primer y último punto. La asociación a zonas coincide con las zonas de inicio y fin desde los datos de referencia cuando los viajes comienzan o terminan dentro de zonas definidas.

Parámetros de configuración

Parámetro	Descripción	Unidad
min_parking_seconds	Umbral de duración para la detección de estacionamiento	segundos
tracks_split_timeout_seconds	Brecha máxima entre puntos antes de dividir viajes	segundos
min_distance_meters	Distancia mínima del viaje para la validación de calidad	metros
min_speed_kmh	Umbral mínimo de velocidad para la detección de movimiento	km/h
max_lbs_speed_kmh	Velocidad máxima realista para puntos LBS	km/h
min_satellites	Recuento mínimo de satélites para calidad GPS	conteo

Ejemplo de configuración

{
  "version": "1.0.0",
  "metadata": {
    "name": "Transformación de Rastreo de Movimiento",
    "description": "Procesa datos de seguimiento crudos en entidades de viaje con validación de calidad y cálculo de métricas",
    "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"
    }
  }
}

Ejemplo de script SQL

El siguiente SQL simplificado demuestra el uso de parámetros en la lógica de transformación:

-- Detección de viajes con parámetros 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);

Consultas de ejemplo

Obtener todos los viajes de un dispositivo específico:

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;

Calcular resumen de distancia diario:

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;

Encontrar viajes entre zonas específicas:

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;

Geocercas

La transformación Geofences pre-calcula límites geográficos como geometrías PostGIS y rastrea cuándo los dispositivos entran, permanecen dentro y salen de estas áreas definidas. Este procesamiento elimina la necesidad de cálculos espaciales en tiempo real durante las consultas, mejorando significativamente el rendimiento para análisis basados en ubicación.

Esta transformación demuestra el procesamiento de datos espaciales y la detección de eventos a partir de flujos continuos de ubicación.

Referencia rápida:

• Propósito: Precalcular geometrías de geocercas y rastrear la presencia de dispositivos en áreas geográficas

• Tablas principales: business_data.geofence_geometries, business_data.geofence_visits

• Métricas principales: Duración de la visita, tiempos de entrada/salida, utilización de geocercas

• Beneficio de rendimiento: Consultas espaciales 10-100x más rápidas frente al cálculo de geometría en tiempo real

• Datos fuente: raw_business_data.zones, raw_business_data.geofence_points, raw_telematics_data.tracking_data_core

Tabla: business_data.geofence_geometries

La tabla geofence_geometries almacena representaciones geométricas optimizadas de las geocercas para consultas espaciales eficientes.

Clave primaria: geofence_id

Descripciones de campos:

La geofence_id campo identifica de forma única cada geocerca y referencia raw_business_data.zones.zone_id. El geofence_type campo indica la forma de la geocerca (círculo, polígono o ruta). El geofence_label campo contiene el nombre de la geocerca para visualización y referencia. El address campo almacena la descripción de la ubicación de la geocerca. El color campo contiene el código de color HEX para visualización. El geofence_geom campo contiene la representación geográfica para operaciones espaciales. Los created_at y updated_at campos registran los cambios temporales.

Especificaciones por tipo de geocerca:

• Círculo: Definido por punto central y radio

• Polígono: Puntos ordenados que forman una forma cerrada

• Ruta: Camino lineal con radio de búfer

Comportamiento de sincronización: La tabla se sincroniza automáticamente cuando los datos fuente de geocercas cambian en la capa Bronze.

Tabla: business_data.geofence_visits

La tabla geofence_visits registra la presencia histórica de dispositivos dentro de geocercas incluyendo hora de entrada, hora de salida y duración de la visita.

Clave primaria: Clave compuesta en (device_id, geofence_id, enter_time)

Descripciones de campos:

La device_id campo referencia el dispositivo de seguimiento. El geofence_id campo referencia la geocerca desde geofence_geometries. El enter_time campo marca cuando el dispositivo entró en la geocerca. El exit_time campo marca cuando el dispositivo salió (NULL para visitas en curso). El duration campo contiene la longitud de la visita calculada.

Relaciones de datos:

-- Unir con detalles de geocerca
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id

-- Unir con información de dispositivo y vehículo
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

Algoritmo de detección de visitas

La transformación rastrea la presencia del dispositivo dentro de geocercas comparando puntos de seguimiento contra las geometrías de geocercas. Los registros de visita capturan cuándo los dispositivos entran, permanecen dentro y salen de geocercas definidas.

Detección de entrada: El sistema detecta la entrada cuando un punto de seguimiento del dispositivo cae dentro de la geometría de la geocerca y el punto anterior estaba fuera de esa geocerca o no existe punto anterior.

Detección de salida: El sistema detecta la salida cuando un punto de seguimiento del dispositivo cae fuera de la geometría de la geocerca y el punto anterior estaba dentro de la geocerca.

Agrupamiento de visitas: Pares consecutivos de entrada-salida forman un registro de visita único. Las visitas abiertas (no se detectó salida) muestran NULL en exit_time y se actualizan cuando la salida ocurre en ciclos de procesamiento posteriores.

Cálculo de duración: La duración de la visita se calcula como la diferencia temporal entre los eventos de entrada y salida. Las visitas abiertas muestran duración NULL hasta que se detecte una salida.

Parámetros de configuración

Parámetro	Descripción	Unidad
spatial_buffer_meters	Distancia de búfer para la detección del límite de la geocerca	metros
min_visit_duration_seconds	Duración mínima de la visita para registrar	segundos
max_visit_gap_seconds	Brecha de tiempo máxima antes de considerar la visita finalizada	segundos

Ejemplo de configuración

{
  "version": "1.0.0",
  "metadata": {
    "name": "Transformación Geofences",
    "description": "Precalcula geometrías de geocercas y rastrea la presencia de dispositivos dentro de áreas geográficas definidas",
    "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"
    }
  }
}

Consultas de ejemplo

Obtener todas las visitas a una geocerca específica:

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;

Calcular estadísticas de utilización de geocercas:

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;

Encontrar dispositivos actualmente presentes:

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;

Analizar patrones de entrada/salida de geocercas por hora:

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;

Identificar dispositivos con mayores tiempos de permanencia:

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;

Desarrollo de entidades personalizadas

La capa Silver demuestra patrones de transformación mediante sus transformaciones disponibles, que sirven como plantillas para desarrollar entidades analíticas personalizadas. Usando datos de la capa Bronze, capacidades SQL y la arquitectura de configuración, se pueden desarrollar entidades personalizadas para abordar requisitos de negocio específicos.

Enfoque de desarrollo

Las entidades personalizadas de la capa Silver siguen la arquitectura orientada por configuración descrita en este documento. El enfoque implica definir la lógica de transformación en scripts SQL y crear configuraciones JSON que especifiquen parámetros y horarios para la ejecución automatizada.

Capacidades clave: Agregar múltiples puntos de datos crudos en objetos analíticos únicos, aplicar lógica de negocio y reglas de validación, precalcular métricas para acelerar consultas, mantener precisión temporal mediante procesamiento programado e integrar operaciones espaciales con contexto de negocio.

Tipos potenciales de entidades personalizadas

• Entidades operativas: Estados operativos y modos de trabajo específicos de la empresa, patrones de turnos y seguimiento de ciclos de trabajo, métricas de utilización de activos, detección de ventanas de mantenimiento

• Entidades comportamentales: Puntuación de riesgo personalizada basada en múltiples factores, análisis y clasificación de patrones de conducción, supervisión de cumplimiento con umbrales configurables, agregación de indicadores de seguridad

• Entidades de rendimiento: Métricas y KPI específicos por industria, cálculos de eficiencia mediante fórmulas personalizadas, indicadores de optimización de recursos, seguimiento del cumplimiento de niveles de servicio

• Entidades basadas en eventos: Detección de eventos personalizada con condiciones complejas, agregación de alertas y reconocimiento de patrones, identificación de anomalías mediante métodos estadísticos, seguimiento de violaciones de umbrales

Plantilla de configuración

{
  "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>"
    }
  }
}

Directrices para scripts SQL

Use valores parametrizados:

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

Aproveche ventanas de tiempo estándar:

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

Estructure el procesamiento en etapas:

WITH raw_data AS (
    -- Extraer y filtrar datos fuente
),
enriched_data AS (
    -- Unir con datos de referencia y calcular valores intermedios
),
aggregated_entities AS (
    -- Agregar en la estructura final de la entidad
)
INSERT INTO business_data.custom_entity (...)
SELECT ... FROM aggregated_entities;

Recursos adicionales

Para patrones de consulta detallados y trabajo con datos de la capa Silver, consulte el IoT Query SQL Recipe Book.

Si está interesado en acceso anticipado o tiene preguntas sobre esta funcionalidad, por favor contacte a [email protected].