如何按DataForm例程对BigQuery的JOBS_BY_PROJECT作业进行分类？

• 解析作业job_id提取DataForm元数据
  DataForm触发的BigQuery作业ID通常带有固定前缀，格式多为dataform-<仓库ID>-<工作流ID>-<后缀>。可以用正则函数提取关键标识：

  SELECT
    job_id,
    REGEXP_EXTRACT(job_id, r'dataform-([a-zA-Z0-9-]+)-([a-zA-Z0-9-]+)') AS dataform_wareflow_id,
    SPLIT(REGEXP_EXTRACT(job_id, r'dataform-([a-zA-Z0-9-]+)-([a-zA-Z0-9-]+)'), '-')[OFFSET(0)] AS dataform_repo_id,
    SPLIT(REGEXP_EXTRACT(job_id, r'dataform-([a-zA-Z0-9-]+)-([a-zA-Z0-9-]+)'), '-')[OFFSET(1)] AS dataform_workflow_id,
    job_type,
    start_time,
    end_time
  FROM
    `your-project-id.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  WHERE
    job_id LIKE 'dataform-%'
  

• 通过user_email关联DataForm服务账号
  DataForm执行作业会使用专属服务账号，格式一般为dataform@<项目ID>.iam.gserviceaccount.com或自定义关联账号。结合导出的DataForm执行日志表，可以关联仓库和工作流信息：

  SELECT
    j.job_id,
    df.repo_name,
    df.workflow_name,
    j.start_time
  FROM
    `your-project-id.INFORMATION_SCHEMA.JOBS_BY_PROJECT` j
  LEFT JOIN
    `your-project-id.dataform_logs.executions` df -- 假设DataForm执行日志已导出至此表
  ON
    j.job_id = df.bigquery_job_id
  WHERE
    j.user_email = 'dataform-service-account@your-project-id.iam.gserviceaccount.com'
  

• 从作业query字段提取DataForm注释
  DataForm生成的SQL会自动添加专属注释，比如-- DataForm: repo=<仓库名>, workflow=<工作流名>, action=<动作名>。用正则从query字段中提取这些元数据：

  SELECT
    job_id,
    REGEXP_EXTRACT(query, r'-- DataForm: repo=([^,]+), workflow=([^,]+)') AS dataform_metadata,
    REGEXP_EXTRACT(query, r'-- DataForm: repo=([^,]+)') AS dataform_repo,
    REGEXP_EXTRACT(query, r'-- DataForm: workflow=([^,]+)') AS dataform_workflow,
    query
  FROM
    `your-project-id.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  WHERE
    query LIKE '%-- DataForm:%'
  

• 配置DataForm自动添加标签
  如果之前标签方案无效，可能是未在DataForm中配置全局或动作级标签。可以在project-config.json中添加全局标签：

  {
    "defaultDatabase": "your-project-id",
    "defaultSchema": "your-dataset",
    "tags": {
      "dataform_repo": "your-repo-name",
      "dataform_workflow": "your-workflow-name"
    }
  }
  

  之后在JOBS_BY_PROJECT中通过labels字段筛选分类：

  SELECT
    job_id,
    labels.dataform_repo,
    labels.dataform_workflow
  FROM
    `your-project-id.INFORMATION_SCHEMA.JOBS_BY_PROJECT`,
    UNNEST(labels) AS labels
  WHERE
    labels.key IN ('dataform_repo', 'dataform_workflow')
  

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