基于GitLab-DolphinScheduler的SQL审核CI/CD实践

背景

数据开发在基于DolphinScheduler调度平台进行sql开发，对于需求的迭代和变更，需要在DolphinScheduler上进行sql的变更，但是在DolphinScheduler仅有的是对于sql task任务节点的修改记录的不同版本，只能进行版本的切换，对于sql文件的版本管控缺乏如git对文件变更的直观变化记录。

工作流

Developer -> 在Dolpin上创建工作流&sql任务流-> 保存工作流(gitlab在main分支创建对应sql文件,默认内容为select 1)

-> 拉取gitlab的main分支 -> gitlab创建自己的分支 -> 修改gitlab文件(对应工作流) -> 发起merge request

-> 触发gitlab CI/CD管道进行sql规则校验 -> 飞书通知审核人审核 -> 审核通过merge到主分支 -> 通知成功merge并保存变更的文件内容到dolpin

实现

1.DolphinScheduler上进行sql插件编辑权限的管控，在用户上新增sql编辑功能的权限管控

2.DolphinScheduler工作流定时上下线通知

修改dolpin源码

ProcessDefinitionServiceImpl.releaseProcessDefinition()方法上下线新增飞书通知

SchedulerServiceImpl.setScheduleState()方法上下线新增飞书通知

3.java-gitlab api操作DolphinScheduler创建编辑工作流同步到gitlab对应的sql文件

修改dolpin源码，创建工作流/编辑工作流/删除工作流同步操作gitlab上的文件

(历史任务初始化到gitlab文件-shell脚本)

4.GitLab 的数据库 CI/CD工作流

安装gitlab runner环境

编写.gitlab-ci.yml文件放在对应gitlab项目下

# This file is a template, and might need editing before it works on your project.
# To contribute improvements to CI/CD templates, please follow the Development guide at:
# https://docs.gitlab.com/ee/development/cicd/templates.html
# This specific template is located at:
# https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/ci/templates/Getting-Started.gitlab-ci.yml
# This is a sample GitLab CI/CD configuration file that should run without any modifications.
# It demonstrates a basic 3 stage CI/CD pipeline. Instead of real tests or scripts,
# it uses echo commands to simulate the pipeline execution.
#
# A pipeline is composed of independent jobs that run scripts, grouped into stages.
# Stages run in sequential order, but jobs within stages run in parallel.
#
# For more information, see: https://docs.gitlab.com/ee/ci/yaml/index.html#stages

stages:          # List of stages for jobs, and their order of execution
  - build
  - test
  - deploy

merge_request_rule_check:
# 指定Runner<必填>
  tags:
    - bigdata_gitlab_runner_01
  stage: build
  script:
    - echo $CI_MERGE_REQUEST_TARGET_BRANCH_NAME
    - echo $CI_MERGE_REQUEST_EVENT_TYPE
    - echo $CI_PIPELINE_SOURCE
    - echo $CI_COMMIT_BRANCH
    - bash /opt/gitlab-scripts/datawharehouse_pro/merge_request_rule_check.sh
  rules:
    - if: '$CI_MERGE_REQUEST_EVENT_TYPE == "detached" && $CI_MERGE_REQUEST_TARGET_BRANCH_NAME=="main"'

merge_review_notification:
  tags:
    - bigdata_gitlab_runner_01  
  stage: build
  script:
    - echo $CI_MERGE_REQUEST_EVENT_TYPE
    - echo $CI_PIPELINE_SOURCE
    - echo $CI_COMMIT_BRANCH
    - bash /opt/gitlab-scripts/datawharehouse_pro/merge_review_notification.sh $CI_PROJECT_ID
  needs:
    - merge_request_rule_check
  rules:
    - if: '$CI_PIPELINE_SOURCE == "merge_request_event" && $CI_MERGE_REQUEST_EVENT_TYPE == "detached"'

review_result_notification:
  tags:
    - bigdata_gitlab_runner_01  
  stage: build
  script:
    - echo $CI_MERGE_REQUEST_EVENT_TYPE
    - echo $CI_MERGE_REQUEST_TITLE
    - echo $CI_MERGE_REQUEST_IID
    - echo $CI_COMMIT_TITLE
    - bash /opt/gitlab-scripts/datawharehouse_pro/review_result_notification.sh $CI_PROJECT_ID
  rules:
    - if: '$CI_COMMIT_BRANCH == "main" && $CI_PIPELINE_SOURCE == "push"'
  #allow_failure: true

merge_request_rule_check.sh功能：对于提交merge request的提交变更文件的内容进行sql规则校验，校验通过才能提交merge request成功。

如下图为触发sql规则检验

merge_review_notification.sh功能：对于提交merge request成功飞书通知到reviewer

review_result_notification.sh功能：更新变更内容到dolpin指定的task(修改元数据task definition和task definition log表)并进行merge成功的飞书通知。