Eseguire query sui dati di OpenTelemetry

Questa pagina fornisce query SQL di esempio per i dati OpenTelemetry inseriti nelle tabelle Delta da Zerobus Ingest OTLP. Per i dettagli sugli schemi di tabella e sulle colonne, consultare il riferimento della tabella OpenTelemetry per Zerobus Ingest.

Negli esempi seguenti sostituire <catalog>.<schema>.<prefix> con il prefisso del catalogo, dello schema e del nome della tabella.

Colonne come attributes, resource.attributes, instrumentation_scope.attributes, body (log) vengono archiviate come VARIANT. Usare la :key::type sintassi per estrarre i valori. Ad esempio, restituisce attributes:['http.method']::string l'attributo http.method come stringa.

Estensioni

Le query seguenti restituiscono dati dalla tabella spans, che archivia i dati di traccia distribuiti.

-- Recent spans with duration and attributes
SELECT
  time,
  service_name,
  name,
  (end_time_unix_nano - start_time_unix_nano) / 1000000 AS duration_ms,
  status.code AS status_code,
  attributes:['http.method']::string AS http_method,
  attributes:['http.status_code']::int AS http_status
FROM <catalog>.<schema>.<prefix>_otel_spans
WHERE time > current_timestamp() - INTERVAL 1 HOUR
ORDER BY time DESC
LIMIT 100;

-- Filter spans by attribute value
SELECT *
FROM <catalog>.<schema>.<prefix>_otel_spans
WHERE attributes:['http.status_code']::int = 200
  AND time > current_timestamp() - INTERVAL 1 HOUR;

-- Slowest operations by service
SELECT
  service_name,
  name,
  COUNT(*) AS call_count,
  AVG((end_time_unix_nano - start_time_unix_nano) / 1000000) AS avg_duration_ms,
  PERCENTILE_APPROX((end_time_unix_nano - start_time_unix_nano) / 1000000, 0.95) AS p95_duration_ms
FROM <catalog>.<schema>.<prefix>_otel_spans
WHERE time > current_timestamp() - INTERVAL 1 HOUR
GROUP BY service_name, name
ORDER BY avg_duration_ms DESC;

-- Spans by service name, environment, and SDK version
SELECT
  service_name,
  resource.attributes:['deployment.environment']::string AS environment,
  instrumentation_scope.attributes:['otel.library.version']::string AS sdk_version,
  COUNT(*) AS span_count
FROM <catalog>.<schema>.<prefix>_otel_spans
WHERE time > current_timestamp() - INTERVAL 1 HOUR
GROUP BY service_name, environment, sdk_version;

Registri

Le query seguenti restituiscono dati dalla tabella dei log, che archivia i record di log strutturati e i relativi livelli di gravità.

-- Recent logs with body and attributes
SELECT
  time,
  service_name,
  severity_text,
  body::string AS message,
  attributes:['exception.type']::string AS exception_type
FROM <catalog>.<schema>.<prefix>_otel_logs
WHERE time > current_timestamp() - INTERVAL 1 HOUR
ORDER BY time DESC
LIMIT 100;

-- Error logs by service
SELECT
  service_name,
  severity_text,
  COUNT(*) AS log_count
FROM <catalog>.<schema>.<prefix>_otel_logs
WHERE severity_text IN ('ERROR', 'WARN')
  AND time > current_timestamp() - INTERVAL 1 HOUR
GROUP BY service_name, severity_text
ORDER BY log_count DESC;

-- Structured log body
SELECT
  time,
  service_name,
  body:message::string AS message,
  body:error.code::int AS error_code
FROM <catalog>.<schema>.<prefix>_otel_logs
WHERE time > current_timestamp() - INTERVAL 1 HOUR
  AND body:error.code IS NOT NULL;

Metriche

Le query seguenti restituiscono dati dalla tabella delle metriche, che archivia le misurazioni del misuratore, della somma e dell'istogramma.

-- Recent metrics with values
SELECT
  time,
  service_name,
  name,
  metric_type,
  COALESCE(gauge.value, sum.value) AS value
FROM <catalog>.<schema>.<prefix>_otel_metrics
WHERE time > current_timestamp() - INTERVAL 1 HOUR
ORDER BY time DESC
LIMIT 100;

-- Gauge metrics over time
SELECT
  date_trunc('minute', time) AS minute,
  name,
  AVG(gauge.value) AS avg_value,
  MAX(gauge.value) AS max_value
FROM <catalog>.<schema>.<prefix>_otel_metrics
WHERE metric_type = 'gauge'
  AND time > current_timestamp() - INTERVAL 1 HOUR
GROUP BY 1, 2
ORDER BY minute;

-- Gauge attributes (attributes are nested inside each metric-type struct)
SELECT
  time,
  name,
  gauge.value,
  gauge.attributes:['host.name']::string AS host
FROM <catalog>.<schema>.<prefix>_otel_metrics
WHERE metric_type = 'gauge'
  AND time > current_timestamp() - INTERVAL 1 HOUR;

-- Filter metrics by resource attribute
SELECT
  time,
  name,
  gauge.value
FROM <catalog>.<schema>.<prefix>_otel_metrics
WHERE resource.attributes:['deployment.environment']::string = 'production'
  AND metric_type = 'gauge'
  AND time > current_timestamp() - INTERVAL 1 HOUR;

Passaggi successivi

• Informazioni di riferimento sulla tabella OpenTelemetry per Zerobus Ingest: informazioni di riferimento per schemi di tabella e definizioni di colonna per span, log e tabelle delle metriche.
• Configurare i client OpenTelemetry (OTLP) per l'invio di dati a Unity Catalog: configurare un SDK OpenTelemetry o agente di raccolta per l'invio di dati a Zerobus Ingest.
• Gestione degli errori di ingestione Zerobus: risoluzione dei comuni errori e codici di errore.