Postgres递归遍历表：获取BigQuery表/视图的源目标血缘（跳过临时表）

首先假设你的血缘表名为table_lineage，包含以下核心字段（若你的字段名不同可对应替换）：

• source_table_name: 源表/视图的短名称
• source_table_full_name: 源表/视图的完整名称（如project.dataset.table）
• target_table_name: 目标表/视图的短名称
• target_table_full_name: 目标表/视图的完整名称

核心逻辑 #

1. 临时表识别规则：以_temp结尾的表视为临时表（你可根据实际命名规则调整正则表达式）
2. 递归遍历血缘链：从非临时的原始源表出发，跳过所有中间临时表，直接关联到最终的非临时目标表/视图

完整BigQuery SQL查询 #

WITH RECURSIVE lineage_chain AS (
  -- 锚点：所有非临时的原始源表（无上游依赖的起始表）
  SELECT
    s.source_table_full_name AS original_source_full_name,
    s.source_table_name AS original_source_name,
    t.target_table_full_name AS current_target_full_name,
    t.target_table_name AS current_target_name
  FROM table_lineage s
  -- 筛选原始源表：本身不是临时表，且没有被其他表作为目标（即无上游）
  WHERE NOT REGEXP_CONTAINS(s.source_table_name, r'_temp$')
    AND NOT EXISTS (
      SELECT 1 FROM table_lineage t_up
      WHERE t_up.target_table_full_name = s.source_table_full_name
    )
  UNION ALL
  -- 递归：继续遍历血缘链，跳过临时表，直到找到非临时目标
  SELECT
    lc.original_source_full_name,
    lc.original_source_name,
    t.target_table_full_name AS current_target_full_name,
    t.target_table_name AS current_target_name
  FROM lineage_chain lc
  JOIN table_lineage t
    ON lc.current_target_full_name = t.source_table_full_name
  -- 仅当当前目标是临时表时，继续往下遍历
  WHERE REGEXP_CONTAINS(lc.current_target_name, r'_temp$')
),
-- 筛选最终非临时目标，并去重
final_lineage AS (
  SELECT DISTINCT
    original_source_name AS source_name,
    original_source_full_name AS source_full_name,
    current_target_name AS target_name,
    current_target_full_name AS target_full_name
  FROM lineage_chain
  -- 排除临时目标，只保留最终非临时的表/视图
  WHERE NOT REGEXP_CONTAINS(current_target_name, r'_temp$')
)
SELECT * FROM final_lineage
ORDER BY source_name, target_name;

自定义调整说明 #

• 临时表规则修改：如果你的临时表命名规则不是_temp结尾，可修改REGEXP_CONTAINS中的正则，比如临时表以tmp_开头，就改成REGEXP_CONTAINS(table_name, r'^tmp_')
• 字段名适配：如果你的血缘表字段名不同（比如用src_table代替source_table_name），请对应替换SQL中的字段名
• 视图处理：视图的命名若符合非临时规则，会自动被纳入最终目标，无需额外调整

示例验证 #

针对你提到的场景：

• 原始源表t_sales_sft_crmy_che_de_4236生成临时表t_sales_sft_crmy_che_de_temp，再生成t_sales_sft_crmy_che_de
• 同时生成视图v_sales_sft_crmy_che_de
• 临时表t_p_calendar_national_temp经过多轮临时表后生成最终非临时表

该查询会直接输出：

• t_sales_sft_crmy_che_de_4236 → t_sales_sft_crmy_che_de
• t_sales_sft_crmy_che_de_4236 → v_sales_sft_crmy_che_de
• 以及t_p_calendar_national（原始源）对应的最终非临时目标

内容的提问来源于stack exchange，提问作者Vikas Tiwari