OpenTelemetry-Daten abfragen

Diese Seite enthält Beispiel-SQL-Abfragen für OpenTelemetry-Daten, die in Delta-Tabellen von Zerobus Ingest OTLP aufgenommen wurden. Informationen zu Tabellenschemas und Spaltendetails finden Sie in der OpenTelemetry-Tabellenreferenz für Zerobus Ingest.

Ersetzen Sie in den folgenden Beispielen das <catalog>.<schema>.<prefix> Präfix "Katalog", "Schema" und "Tabellenname".

Spalten wie attributes, resource.attributes, , instrumentation_scope.attributesund body (Protokolle) werden als VARIANTgespeichert. Verwenden Sie die :key::type Syntax, um Werte zu extrahieren. Gibt beispielsweise attributes:['http.method']::string das http.method Attribut als Zeichenfolge zurück.

Umfasst

Die folgenden Abfragen geben Daten aus der Spans-Tabelle zurück, in der verteilte Ablaufverfolgungsdaten gespeichert werden.

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

Protokolle

Die folgenden Abfragen geben Daten aus der Protokolltabelle zurück, die strukturierte Protokolldatensätze und deren Schweregrad speichert.

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

Metriken

Die folgenden Abfragen geben Daten aus der Metriktabelle zurück, in der Messwerte, Summen und Histogramme gespeichert werden.

-- 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ächste Schritte

• OpenTelemetry-Tabellenreferenz für Zerobus Ingest: Referenz für Tabellenschemas und Spaltendefinitionen für Spannen, Protokolle und Metriktabellen.
• Konfigurieren von OpenTelemetry-Clients (OTLP) zum Senden von Daten an den Unity-Katalog: Konfigurieren Sie ein OpenTelemetry SDK oder einen Collector zum Senden von Daten an Zerobus Ingest.
• Zerobus Ingest Error Handling: Fehlerbehebung bei häufigen Fehlern und Fehlercodes.