Hämta OpenTelemetry-data

Den här sidan innehåller exempel på SQL-frågor för OpenTelemetry-data som matas in i Delta-tabeller av Zerobus Ingest OTLP. Tabellscheman och kolumninformation finns i OpenTelemetry-tabellreferens för Zerobus-inmatning.

I exemplen nedan ersätter du <catalog>.<schema>.<prefix> med prefixet katalog, schema och tabellnamn.

Kolumner som attributes, resource.attributes, instrumentation_scope.attributesoch body (loggar) lagras som VARIANT. Använd syntaxen :key::type för att extrahera värden. Returnerar attributes:['http.method']::string till exempel attributet http.method som en sträng.

Spänner över

Följande frågor returnerar data från spans-tabellen, som lagrar distribuerade spårningsdata.

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

Loggar

Följande frågor returnerar data från loggtabellen, som lagrar strukturerade loggposter och deras allvarlighetsgrad.

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

Statistik

Följande frågor returnerar data från måtttabellen, som lagrar mått,summa- och histogrammått.

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

Nästa steg

• OpenTelemetry-tabellreferens för Zerobus-inmatning: Referens för tabellscheman och kolumndefinitioner för tabeller för intervall, loggar och mått.
• Konfigurera OpenTelemetry-klienter (OTLP) för att skicka data till Unity Catalog: Konfigurera en OpenTelemetry SDK eller Insamlare för att skicka data till Zerobus-inmatning.
• Felhantering av zerobus-inmatning: Felsöka vanliga fel och felkoder.