Camada Silver

Em breve!

A arquitetura da camada Silver descrita neste documento está atualmente em desenvolvimento. Enquanto as capacidades centrais de transformação estão operacionais, o sistema de configuração e seus detalhes de implementação podem evoluir antes do lançamento final. Se você estiver interessado em acesso antecipado ou tiver perguntas sobre essa funcionalidade, entre em contato com [email protected].

A camada Silver transforma dados telemáticos brutos e informações de negócio em entidades normalizadas e prontas para consulta, com métricas e estruturas predefinidas. A camada Bronze contém tudo capturado por dispositivos e sistemas — pontos individuais, eventos e valores de campo convenientes para verificação e solução de problemas. A camada Silver processa esses dados brutos em entidades significativas como viagens, visitas a zonas e estados operacionais por meio de transformações configuráveis que limpam, padronizam e agregam dados em objetos analíticos compreensíveis.

💡 Camada Silver em resumo: Bronze é tudo o que é coletado, Silver é com o que você pode trabalhar.

Essa camada intermediária elimina trabalhos manuais repetitivos de ETL e prepara os dados para análises práticas. Operadores de frotas obtêm respostas para perguntas comuns sem processamento extensivo de dados, enquanto integradores ganham uma base estável para construir funcionalidades escaláveis.

Arquitetura e capacidades

A camada Silver organiza os dados processados em dois schemas distintos que refletem diferentes fontes de transformação e padrões de acesso. Ambos os schemas operam no nível da camada Silver da arquitetura medallion, posicionados acima dos schemas da camada Bronze (raw_business_data e raw_telematics_data) e abaixo da camada Gold.

Estrutura do schema

A camada Silver usa uma abordagem de schema dinâmico onde as estruturas de banco de dados se formam automaticamente com base nas transformações ativas. Diferente da camada Bronze com suas definições de schema fixas, os schemas da camada Silver contêm apenas as tabelas que correspondem às transformações configuradas e implantadas. Isso significa que as tabelas disponíveis e suas estruturas dependem de quais transformações estão atualmente ativas na sua Consulta IoT .

Os dados da camada Silver são organizados em dois schemas PostgreSQL:

• processed_common_data: Contém transformações desenvolvidas e mantidas pela Navixy. Este schema é compartilhado entre todos os clientes, fornecendo entidades analíticas padronizadas que atendem casos de uso telemáticos comuns. As tabelas aparecem neste schema conforme a Navixy desenvolve e implanta novas transformações para atender requisitos analíticos amplamente aplicáveis.

• processed_custom_data: Contém transformações específicas do cliente criadas para atender requisitos de negócio exclusivos. Cada cliente tem uma instância isolada deste schema, permitindo entidades analíticas customizadas sem afetar outros clientes. As tabelas neste schema correspondem a transformações configuradas especificamente para sua organização.

Ambos os schemas operam por meio de configurações de transformação baseadas em JSON. Quando uma transformação é configurada e ativada, o sistema cria automaticamente a estrutura de tabela correspondente dentro do schema apropriado. Quando transformações são removidas ou desativadas, suas tabelas podem ser arquivadas ou removidas com base nas políticas de retenção de dados.

Essa formação dinâmica é a razão pela qual a documentação da camada Silver não fornece descrições de schema fixas como a camada Bronze faz. Em vez disso, as tabelas disponíveis e suas estruturas refletem as transformações específicas configuradas para sua instância do IoT Query. Para entender quais dados estão disponíveis na sua camada Silver, revise a documentação das transformações para as entidades que foram implantadas em sua instância.

Arquitetura de processamento

As transformações da camada Silver operam por meio de uma arquitetura orientada por configuração que separa a lógica de negócio da orquestração. Cada transformação é definida por uma configuração JSON que especifica a lógica de processamento SQL, parâmetros, agendamento e comportamento de recalculação. O Apache Airflow gerencia o ciclo de vida de execução, aplicando essas configurações para processar dados da camada Bronze em entidades da camada Silver.

A estrutura de configuração JSON permanece idêntica para transformações comuns e personalizadas, garantindo padrões de processamento consistentes entre todas as entidades da camada Silver. Essa abordagem de configuração unificada permite implantação flexível de transformações enquanto mantém execução padronizada e controle de versão.

Para informações detalhadas sobre o sistema de configuração JSON, consulte a Configuração JSON seção.

Atualidade dos dados

As entidades da camada Silver são mantidas automaticamente por meio de processos agendados definidos nas configurações de transformação. Ao consultar dados da camada Silver, considere as seguintes características de processamento:

• Atualizações agendadas: Cada transformação processa novos dados da camada Bronze de acordo com seu agendamento configurado. Atualizações tipicamente ocorrem a cada hora ou a cada poucas horas, dependendo da complexidade da transformação.

• Janelas de processamento: As transformações operam em janelas baseadas em tempo para processar segmentos de dados gerenciáveis de forma eficiente em vez de conjuntos de dados inteiros.

• Impacto da recalculação: Quando mudanças de configuração acionam recalculação, dados recentes podem apresentar breves inconsistências durante as janelas de processamento.

• Comportamento específico por schema: Transformações em processed_common_data são atualizadas simultaneamente para todos os clientes que compartilham esse schema. Transformações em processed_custom_data executam independentemente por cliente, permitindo agendamento e lógica de processamento personalizados.

Configuração JSON

Esta seção descreve a arquitetura de configuração atualmente em desenvolvimento. A estrutura JSON e as especificações de parâmetros mostradas aqui representam a abordagem planejada de implementação. Detalhes finais da implementação podem diferir à medida que o desenvolvimento avança.

A camada Silver opera em uma arquitetura orientada por configuração onde transformações são definidas por especificações JSON. Cada configuração contém a lógica de processamento, parâmetros de transformação, regras de agendamento e políticas de recalculação que determinam como os dados da camada Bronze se tornam entidades da camada Silver.

Estrutura JSON

Uma configuração de transformação consiste em quatro seções:

• version (string): Versão da configuração seguindo versionamento semântico

• metadata (object): Informações básicas incluindo nome, descrição, timestamp de criação e identificador do criador

• sql_template (object): Especificação da lógica de processamento incluindo caminhos de arquivos SQL, definições de tabela de destino e parâmetros de transformação

• target (object): Local de saída especificando schema e tabela

• scheduler (object): Controle de execução incluindo cron de agendamento, status de habilitação e configuração de backfill

Esquema de configuração

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

Parametrização de script SQL

Scripts SQL de transformação referenciam parâmetros de configuração usando placeholders com prefixo de dois-pontos. O sistema substitui valores reais da configuração ao executar os scripts e fornece parâmetros padrão de janela de tempo automaticamente:

• :window_start - Início da janela de processamento (timestamp ISO-8601)

• :window_end - Fim da janela de processamento (timestamp ISO-8601)

Parâmetros personalizados são definidos na sql_template.parameters seção e controlam lógica específica da transformação, como limites de qualidade, regras de negócio e métodos de cálculo.

Exemplo de SQL com 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;

Versionamento de configuração

Quando qualquer parâmetro de configuração muda, uma nova versão é criada. Cada versão representa um conjunto específico de regras de processamento que estiveram ativas durante um período de tempo, permitindo rastrear como a lógica de transformação evoluiu.

Gatilhos de criação de versão:

• Qualquer parâmetro em sql_template.parameters mudanças

• Caminhos de arquivos de scripts SQL são modificados

• Schema ou tabela de destino mudam

• Configurações de scheduler ou backfill são ajustadas

Aplicação de versão: Quando uma nova versão de configuração é criada e aplicada, o sistema processa os dados com base no modo de recalculação selecionado.

Modos de recalculação

O sistema de configuração suporta três modos de recalculação que controlam como mudanças de parâmetros afetam dados históricos e futuros. Esses modos fornecem flexibilidade para equilibrar requisitos de consistência de dados com eficiência de processamento.

Recalculação apenas para frente

O modo apenas para frente aplica novos parâmetros de configuração apenas aos dados processados após a mudança de versão. Dados históricos permanecem inalterados, preservando valores calculados com parâmetros anteriores.

Quando usar: Ajustes menores de parâmetro que não alteram fundamentalmente definições de entidade, testar novos parâmetros antes de recalcular completamente ou gerenciar custos de computação evitando reprocessamento histórico.

Comportamento: Se você alterar min_speed_kmh de 3 para 5 em 8 de dezembro, apenas viagens processadas a partir de 8 de dezembro em diante usarão o novo limite. Viagens calculadas antes de 8 de dezembro manterão seus valores originais.

Recalculação de histórico completo

O modo de recalculação completo processa todos os dados históricos dentro do intervalo de datas de backfill configurado usando os novos parâmetros. O sistema substitui todas as entidades existentes por valores recém-calculados.

Quando usar: Mudanças fundamentais nas definições de entidade ou algoritmos de detecção, corrigir erros sistemáticos em cálculos anteriores ou padronizar todos os dados históricos com as regras de negócio atuais.

Comportamento: Alterar a lógica de detecção de viagens requer recalcular todas as viagens para garantir definições consistentes de entidades em toda a faixa temporal.

Recalculação parcial

O modo de recalculação parcial processa uma janela limitada de dados históricos, tipicamente dias ou semanas recentes.

Quando usar: Corrigir problemas de qualidade de dados recentes, atualizar parâmetros que afetam principalmente padrões operacionais recentes ou implementar mudanças com impacto histórico limitado.

Configuração: Especifique um backfill_days parâmetro (por exemplo, 7 para a última semana) seja na configuração ou ao acionar manualmente a recalculação. O sistema atualiza registros existentes dentro da janela de tempo especificada.

Transformações disponíveis

A camada Silver atualmente fornece dois grupos de transformação que demonstram a abordagem orientada por configuração e servem como modelos para desenvolvimento de entidades customizadas.

Viagens

É uma transformação de rastreamento de movimento que identifica segmentos contínuos de movimento a partir de dados brutos de rastreamento e calcula métricas abrangentes da viagem.

Referência rápida:

• Finalidade: Converte dados de localização em nível de ponto em análises em nível de viagem

• Tabela principal: business_data.tracks

• Principais métricas: Distância, duração, estatísticas de velocidade, limites geográficos

• Dados de origem: raw_telematics_data.tracking_data_core, raw_telematics_data.states

Tabela: business_data.tracks

A tabela tracks armazena informações agregadas sobre segmentos contínuos de movimento com métricas pré-calculadas e contexto geográfico.

Chave primária: track_id (identificador único com auto-incremento)

Descrições dos campos:

O track_id campo identifica unicamente cada segmento de viagem. O device_id campo referencia o dispositivo de rastreamento da camada Bronze. O track_start_time e track_end_time campos definem os limites temporais da viagem. O track_duration campo fornece formato de duração legível enquanto track_duration_seconds permite cálculos numéricos. O track_distance_meters campo contém a distância total percorrida. Campos de velocidade (avg_speed, max_speed, min_speed) fornecem resumo estatístico em quilômetros por hora. Coordenadas iniciais (latitude_start, longitude_start, altitude_start) e coordenadas finais (latitude_end, longitude_end, altitude_end) definem os limites geográficos. O points_in_track campo indica a qualidade dos dados por meio da contagem de pontos. Os campos start_zone e end_zone vinculam aos dados de referência de zonas quando viagens começam ou terminam dentro de zonas definidas.

Relacionamentos de dados:

-- Juntar com informações do dispositivo
FROM business_data.tracks AS t
JOIN raw_business_data.devices AS d
  ON t.device_id = d.device_id

-- Juntar com detalhes do veí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 detecção de viagens

A transformação identifica viagens usando detecção de movimento que analisa velocidade, distância e padrões temporais. Uma viagem representa um segmento contínuo de movimento separado de outras viagens por períodos de estacionamento ou lacunas de dados.

O sistema inicia uma nova viagem quando o primeiro ponto de rastreamento aparece para um dispositivo, quando o movimento começa após duração de estacionamento que excede o limite configurado, quando o movimento é retomado após uma lacuna de dados que excede o timeout configurado, ou quando um único ponto de localização LBS (torre de celular) é registrado. O sistema encerra uma viagem quando o movimento para e a duração de estacionamento atinge o limite configurado, ou quando ocorre uma lacuna de dados que excede o timeout.

Classificação de movimento:

• Em movimento: Velocidade ≥ limite configurado

• Estacionamento: Velocidade < limite E duração ≥ duração de estacionamento configurada

• Lacuna de dados: Tempo entre pontos ≥ timeout de separação configurado

Validação de qualidade: Viagens geradas devem atender limites de qualidade configuráveis para ser incluídas — mínimo de 2 pontos de rastreamento, velocidade máxima ≥ limite configurado, distância total ≥ limite configurado e horários de início e fim definidos. O sistema filtra dados anômalos incluindo velocidades irrealistas para pontos LBS, coordenadas zero e cobertura insuficiente de satélites.

Cálculo de métricas: As métricas da viagem são calculadas a partir de pontos de rastreamento validados. Distância representa o comprimento geométrico total. Estatísticas de velocidade incluem média, máxima e mínima a partir das velocidades dos pontos. Duração é a diferença de tempo entre o fim e o início. Limites geográficos capturam as coordenadas do primeiro e do último ponto. Associação a zonas correlaciona zonas de início e fim a partir dos dados de referência quando viagens começam ou terminam dentro de zonas definidas.

Parâmetros de configuração

Parâmetro	Descrição	Unidade
min_parking_seconds	Limiar de duração para detecção de estacionamento	segundos
tracks_split_timeout_seconds	Lacuna máxima entre pontos antes de dividir viagens	segundos
min_distance_meters	Distância mínima da viagem para validação de qualidade	metros
min_speed_kmh	Limiar mínimo de velocidade para detecção de movimento	km/h
max_lbs_speed_kmh	Velocidade máxima realista para pontos LBS	km/h
min_satellites	Contagem mínima de satélites para qualidade GPS	contagem

Exemplo de configuração

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

Exemplo de script SQL

O SQL simplificado a seguir demonstra o uso de parâmetros na lógica da transformação:

-- Detecção de viagens com parâmetros configuráveis
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);

Exemplos de consultas

Obter todas as viagens para um 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 resumo diário de distância:

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 viagens 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;

Geofences

A transformação Geofences pré-calcula limites geográficos como geometrias PostGIS e rastreia quando dispositivos entram, permanecem dentro e saem dessas áreas definidas. Esse processamento elimina a necessidade de cálculos espaciais em tempo real durante consultas, melhorando significativamente o desempenho para análises baseadas em localização.

Essa transformação demonstra processamento de dados espaciais e detecção de eventos a partir de fluxos contínuos de localização.

Referência rápida:

• Finalidade: Pré-calcular geometrias de geofence e rastrear presença de dispositivos em áreas geográficas

• Tabelas principais: business_data.geofence_geometries, business_data.geofence_visits

• Principais métricas: Duração da visita, horários de entrada/saída, utilização de geofence

• Benefício de desempenho: Consultas espaciais 10-100x mais rápidas vs. cálculo de geometria em tempo real

• Dados de origem: raw_business_data.zones, raw_business_data.geofence_points, raw_telematics_data.tracking_data_core

Tabela: business_data.geofence_geometries

A tabela geofence_geometries armazena representações geométricas otimizadas das geofences para consultas espaciais eficientes.

Chave primária: geofence_id

Descrições dos campos:

O geofence_id campo identifica unicamente cada geofence e referencia raw_business_data.zones.zone_id. O geofence_type campo indica a forma da geofence (círculo, polígono ou rota). O geofence_label campo contém o nome da geofence para exibição e referência. O endereço campo armazena a descrição de localização da geofence. O color campo contém o código de cor HEX para visualização. O geofence_geom campo contém a representação geográfica para operações espaciais. Os created_at e updated_at campos rastreiam mudanças temporais.

Especificações de tipo de geofence:

• Círculo: Definido por ponto central e raio

• Polígono: Pontos ordenados formando uma forma fechada

• Rota: Caminho linear com raio de buffer

Comportamento de sincronização: A tabela sincroniza automaticamente quando os dados de geofence de origem mudam na camada Bronze.

Tabela: business_data.geofence_visits

A tabela geofence_visits registra a presença histórica de dispositivos dentro das geofences incluindo horário de entrada, horário de saída e duração da visita.

Chave primária: Chave composta em (device_id, geofence_id, enter_time)

Descrições dos campos:

O device_id campo referencia o dispositivo de rastreamento. O geofence_id campo referencia a geofence de geofence_geometries. O enter_time campo marca quando o dispositivo entrou na geofence. O exit_time campo marca quando o dispositivo saiu (NULL para visitas em andamento). O duration campo contém o comprimento calculado da visita.

Relacionamentos de dados:

-- Juntar com detalhes da geofence
FROM business_data.geofence_visits AS gv
JOIN business_data.geofence_geometries AS gg
  ON gv.geofence_id = gg.geofence_id

-- Juntar com informações do dispositivo e do veí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 detecção de visita

A transformação rastreia a presença do dispositivo dentro das geofences comparando pontos de rastreamento com geometrias de geofence. Registros de visita capturam quando dispositivos entram, permanecem e saem das geofences definidas.

Detecção de entrada: O sistema detecta entrada quando um ponto de rastreamento do dispositivo está dentro da geometria da geofence e o ponto anterior estava fora dessa geofence ou não existe ponto anterior.

Detecção de saída: O sistema detecta saída quando um ponto de rastreamento do dispositivo está fora da geometria da geofence e o ponto anterior estava dentro da geofence.

Agrupamento de visitas: Pares consecutivos de entrada-saída formam um único registro de visita. Visitas abertas (sem saída detectada) mostram NULL em exit_time e são atualizadas quando a saída ocorre em ciclos de processamento subsequentes.

Cálculo da duração: A duração da visita é calculada como a diferença de tempo entre os eventos de entrada e saída. Visitas abertas mostram duração NULL até que uma saída seja detectada.

Parâmetros de configuração

Parâmetro	Descrição	Unidade
spatial_buffer_meters	Distância de buffer para detecção de limite de geofence	metros
min_visit_duration_seconds	Duração mínima da visita para registro	segundos
max_visit_gap_seconds	Lacuna máxima de tempo antes de considerar a visita encerrada	segundos

Exemplo de configuração

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

Exemplos de consultas

Obter todas as visitas a uma geofence 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 estatísticas de utilização de geofence:

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 atualmente 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;

Analisar padrões de entrada/saída 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 com maiores tempos de permanência:

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;

Desenvolvimento de entidades customizadas

A camada Silver demonstra padrões de transformação por meio de suas transformações disponíveis, que servem como modelos para desenvolver entidades analíticas customizadas. Usando dados da camada Bronze, capacidades SQL e a arquitetura de configuração, entidades customizadas podem ser desenvolvidas para atender requisitos específicos de negócio.

Abordagem de desenvolvimento

Entidades Silver customizadas seguem a arquitetura orientada por configuração descrita neste documento. A abordagem envolve definir a lógica de transformação em scripts SQL e criar configurações JSON que especificam parâmetros e agendamentos para execução automatizada.

Principais capacidades: Agregar múltiplos pontos de dados brutos em objetos analíticos únicos, aplicar lógica de negócio e regras de validação, pré-calcular métricas para acelerar consultas, manter precisão temporal por meio de processamento agendado e integrar operações espaciais com contexto de negócio.

Tipos potenciais de entidades customizadas

• Entidades operacionais: Estados operacionais e modos de trabalho específicos da empresa, padrões de turno e rastreamento de ciclo de trabalho, métricas de utilização de ativos, detecção de janelas de manutenção

• Entidades comportamentais: Pontuação de risco personalizada baseada em múltiplos fatores, análise e classificação de padrões de direção, monitoramento de conformidade com limites configuráveis, agregação de indicadores de segurança

• Entidades de desempenho: Métricas e KPIs específicos do setor, cálculos de eficiência usando fórmulas personalizadas, indicadores de otimização de recursos, acompanhamento do cumprimento de níveis de serviço

• Entidades baseadas em eventos: Detecção de eventos personalizada com condições complexas, agregação de alertas e reconhecimento de padrões, identificação de anomalias usando métodos estatísticos, acompanhamento de violações de limite

Modelo de configuração

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

Diretrizes 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)

Aproveite janelas de tempo padrão:

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

Estruture o processamento em estágios:

WITH raw_data AS (
    -- Extrair e filtrar dados de origem
),
enriched_data AS (
    -- Realizar junção com dados de referência e calcular valores intermediários
),
aggregated_entities AS (
    -- Agregar na estrutura final da entidade
)
INSERT INTO business_data.custom_entity (...)
SELECT ... FROM aggregated_entities;

Recursos adicionais

Para padrões detalhados de consulta e trabalho com dados da camada Silver, consulte o IoT Query SQL Recipe Book.

Se você tiver interesse em acesso antecipado ou tiver perguntas sobre essa funcionalidade, entre em contato com [email protected].