监控和查询事件

每个管道都会发布事件,捕捉审计日志、数据质量检查、进度和数据血缘。 你可以从两个来源查询这些事件:

  • pipeline_events系统表包含区域内工作区所有管道的事件,是查询事件的推荐方式。 它还处于测试阶段。

  • 每个流水线 事件日志 是一个包含单个流水线事件的三角表。

你用标准SQL查询事件。 为了帮助你入门,本页还提供了系统表的示例仪表盘和常见查询。

Requirements

要访问该系统表,用户必须以下之一:

示例仪表板

该示例仪表盘读取 pipeline_events 系统表,以跟踪区域内每个管道的更新、流量吞吐量、积压、数据质量和错误情况。 按流水线、表格、标签和时间范围过滤每页。

流水线状态明细(含错误和警告计数)

每条管道的每小时行数都变更了

预期失败:行失败次数及随时间变化的最大失败百分比

每个目标表每小时的积压字节数

导入仪表板

  1. 下载 仪表盘的JSON文件。

  2. 将仪表板导入工作区。 有关说明,请参见 导入仪表盘文件。

监控查询

仪表盘中的以下查询展示了常见的管道监控用例。

每个管道的最新误差

此查询返回过去7天内出错的每条管道的最新错误,最外层例外。

SELECT
  workspace_id,
  pipeline_id,
  event_time,
  message,
  error.exceptions[0].error_class AS exception_error_class,
  error.exceptions[0].sql_state   AS exception_sql_state
FROM
  system.lakeflow_pipeline_events_preview.pipeline_events
WHERE
  level = 'ERROR'
  AND event_time >= current_timestamp() - INTERVAL 7 DAYS
QUALIFY
  ROW_NUMBER() OVER (PARTITION BY workspace_id, pipeline_id ORDER BY event_time DESC) = 1
ORDER BY
  event_time DESC

每小时错误率

这个查询会每小时统计每条管道的错误,这样你就能发现峰值。

SELECT
  pipeline_id,
  date_trunc('HOUR', event_time) AS hour,
  count(*) AS num_errors
FROM
  system.lakeflow_pipeline_events_preview.pipeline_events
WHERE
  level = 'ERROR'
  AND event_time >= current_timestamp() - INTERVAL 7 DAYS
GROUP BY
  pipeline_id,
  date_trunc('HOUR', event_time)
ORDER BY
  hour DESC

每次流程的行数变化

此查询按小时汇总流追加、更新插入和删除的行数。 每个指标都是以每个微批次为单位的计数,因此在该窗口内求和即可得到吞吐量。

SELECT
  origin.flow_name,
  date_trunc('HOUR', event_time) AS hour,
  SUM(
    ifnull(variant_get(details, '$.flow_progress.metrics.num_output_rows', 'BIGINT'), 0)
    + ifnull(variant_get(details, '$.flow_progress.metrics.num_upserted_rows', 'BIGINT'), 0)
    + ifnull(variant_get(details, '$.flow_progress.metrics.num_deleted_rows', 'BIGINT'), 0)
  ) AS rows_changed
FROM
  system.lakeflow_pipeline_events_preview.pipeline_events
WHERE
  event_type = 'flow_progress'
  AND event_time >= current_timestamp() - INTERVAL 7 DAYS
GROUP BY
  origin.flow_name,
  date_trunc('HOUR', event_time)
ORDER BY
  hour DESC,
  rows_changed DESC

按流程划分的积压项

该查询返回每个流的最新积压读数。 一个 COMPLETED 流被追赶,因此其积压记录为 0。

WITH latest_flow_progress AS (
  SELECT
    workspace_id,
    pipeline_id,
    origin.flow_name,
    event_time,
    CASE
      WHEN variant_get(details, '$.flow_progress.status', 'STRING') = 'COMPLETED' THEN 0
      ELSE variant_get(details, '$.flow_progress.metrics.backlog_bytes', 'BIGINT')
    END AS backlog_bytes
  FROM
    system.lakeflow_pipeline_events_preview.pipeline_events
  WHERE
    event_type = 'flow_progress'
    AND event_time >= current_timestamp() - INTERVAL 1 HOUR
    AND (
      variant_get(details, '$.flow_progress.metrics.backlog_bytes', 'BIGINT') IS NOT NULL
      OR variant_get(details, '$.flow_progress.status', 'STRING') = 'COMPLETED'
    )
  QUALIFY
    ROW_NUMBER() OVER (PARTITION BY workspace_id, pipeline_id, origin.flow_name ORDER BY event_time DESC) = 1
)
SELECT *
FROM latest_flow_progress
WHERE backlog_bytes > 0
ORDER BY backlog_bytes DESC

按数据集划分的未达预期项

该查询返回的是每个数据集、每次更新在最后一天内失败的记录预期。 它会基于预期 dataset值来触发,即使 origin.dataset_name 事件本身没有预期值,预期值也会被填充。

SELECT
  pipeline_id,
  update_id,
  coalesce(expectation.dataset, origin.dataset_name) AS dataset_name,
  expectation.name AS expectation_name,
  SUM(expectation.failed_records) AS failed_records
FROM
  system.lakeflow_pipeline_events_preview.pipeline_events
  LATERAL VIEW explode(variant_get(details, '$.flow_progress.data_quality.expectations', 'ARRAY<STRUCT<name:STRING,dataset:STRING,passed_records:BIGINT,failed_records:BIGINT>>')) AS expectation
WHERE
  event_type = 'flow_progress'
  AND event_time >= current_timestamp() - INTERVAL 1 DAY
GROUP BY
  pipeline_id,
  update_id,
  coalesce(expectation.dataset, origin.dataset_name),
  expectation.name
HAVING
  SUM(expectation.failed_records) > 0
ORDER BY
  failed_records DESC