Skip to main content
Last updated on

Query OpenTelemetry data

Beta

This feature is in Beta.

This page provides example SQL queries for OpenTelemetry data ingested into Delta tables by Zerobus Ingest OTLP. For table schemas and column details, see OpenTelemetry table reference for Zerobus Ingest.

In the examples below, replace <catalog>.<schema>.<prefix> with your catalog, schema, and table name prefix.

Columns such as attributes, resource.attributes, instrumentation_scope.attributes, and body (logs) are stored as VARIANT. Use the :key::type syntax to extract values. For example, attributes:['http.method']::string returns the http.method attribute as a string.

note

Querying VARIANT columns requires Databricks Runtime 15.3+. For variant shredding performance benefits, use Databricks Runtime 17.2+.

Spans​

The following queries return data from the spans table, which stores distributed trace data.

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

Logs​

The following queries return data from the logs table, which stores structured log records and their severity levels.

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

Metrics​

The following queries return data from the metrics table, which stores gauge, sum, and histogram measurements.

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

Next steps​