[GitHub] [dolphinscheduler] weixiaonan1 opened a new issue, #12953: [Feature][alert-plugin & task-SQL] Send results to message queue (result of workflow executiton and SQL query task)

[GitBox]

weixiaonan1 opened a new issue, #12953:
URL: https://github.com/apache/dolphinscheduler/issues/12953

   ### Search before asking
   
   - [X] I had searched in the 
[issues](https://github.com/apache/dolphinscheduler/issues?q=is%3Aissue) and 
found no similar feature requirement.
   
   
   ### Description
   
   This issue consists of two parts： 
   1. add alert-plugin-MessageQueue(MQ) to send result of workflow execution to 
MQ which user specified 
   2. In query SQL task, add a choice to send query results to MQ(use alarm 
group mentioned in 1) in addition to send email
   
   These two features will provide a lot of convenience for 
dolphinscheduler(ds)'s integration with third-part systems. 
   1. Many third-part systems use ds as an infrastructure, using open-api to 
manage projects and workflows, like creating workflows, starting workflow 
manully and setting timing rules for automatic scheduling. In many situations, 
these systems need to get the execution result, and continue to execute some 
codes. However, when we(or scheduled task) start a workflow and we don't know 
the execution results unless Polling for the status. Provided alert plugins 
like dingTalk, email,  etc  are difficult to be integreted into third-part 
systems to get results. It's a good idea to **send execution result to message 
queue**, and then these systems can consume the msgs.
   2. In some situations, for example, we have many sqls which need to be 
executed in different db instanses or even in different type of databases. We 
can send these sqls to ds, because ds support many different types of 
datasource like mysql, pgsql (easy to extend like DM and other db), also ds can 
execute these sqls  parallelly in many workers. But currently ,we cannot get 
query results which never be stored and only support to be sent with emails. 
When we implement the alert-plugin-MQ, we can add a  choice to select a alert 
group(use alert-plugion-MQ) to send results to MQ for third-part systems to 
consume.
   
   这个issue实际上是两个问题：
   1. 告警插件新增一个消息队列插件，将工作流的执行结果写入用户指定的消息队列中
   2. 在SQL任务中，查询类型的SQL除了支持将结果发送邮件外，还支持将结果写入消息队列中（可以复用1中所述的告警组）
   
   这两个特性将会给dolphinscheduler(ds)对第三方系统的集成带来极大的便利。
   
   1. 
很多系统将ds作为底层的基础设施，利用ds提供的open-api管理项目和工作流，如创建工作流、手动启动工作流、给工作流设定定时策略交给ds定时执行等。但是在手动执行工作流或者定时任务启动工作流时，我们无法及时的拿到执行结果(定时任务是自动启动的，手动执行接口是异步的)。第三方系统往往需要拿到执行结果并根据成功或者失败执行对应的逻辑，而现在提供的告警插件(邮件、钉钉等)很难集成在第三方系统中来获取执行结果，使用轮询的方式查看工作流状态开销也很大，比较好的方式是利用消息队列，将执行结果写入消息队列供第三方系统消费。
   2. 
在有些场景下，如有很多在不同的数据库实例，甚至是不同类型的数据库上的SQL需要执行，我们会将SQL交给ds来执行，一方面利用ds支持多种数据源的能力(MySQL,
 Oracle, 
而且很方便拓展，如达梦数据库等)，另一方面也利用ds分布式执行的能力并行执行增加效率。SQL执行结束后将结果发送给第三方系统，第三方系统继续执行下面的逻辑。但是现有的SQL任务执行结果不会保存，而且只支持将结果发送给邮件，所以第三方系统拿不到执行结果。如果增加了消息队列告警插件，我们就可以像发送邮件一样，在Query
 SQL任务中指定消息队列告警组，将SQL查询结果写入消息队列，第三方系统就可以及时消费了。
   
   
   
   
   
   ### Use case
   
   1. add alert-plugin-MessageQueue(MQ): Apache RocketMQ as an example
       - user can input ProducerGroupName, NameSvrAddress, Topic, Tags to 
specify a MQ as alert instance.
       - create alert group with this instance
       - start a workflow and choose this alert group, set Notification 
Strategy to all
       - third-part systems consume msgs in MQ(the screenshot is from 
RocketMQ-Dashboard)
   <div align="center"><img 
src="https://user-images.githubusercontent.com/33857431/203058931-ff364c68-c08f-449c-a433-0523e294e127.png";
 width="500px"></div>
   <div align="center"><img 
src="https://user-images.githubusercontent.com/33857431/203062914-73524e7f-e001-475a-8883-f94f4204fa65.png";
 width="500px"></div>
   <div align="center"><img 
src="https://user-images.githubusercontent.com/33857431/203065905-c76cbbdc-b7b3-4e67-97a2-1634ec7f9bf1.png";
 width="600px"></div>
   
   2. add a choice to support send SQL query results to MQ
       - user can choose to send to MQ in Query SQL task
       - choose a MQ alert group
       - when the task finished, the query results will be sent to MQ    
       - third-part systems consume msgs in MQ
   
   <div align="center"><img 
src="https://user-images.githubusercontent.com/33857431/203066830-71d610dc-f918-4110-a9d2-64f678b1e37e.png";
 width="500px"></div>
   
   
   ### Related issues
   
   [Feature]Send to message queue request after reading data（读取数据后发送到消息队列需求） 
#657
   
   ### Are you willing to submit a PR?
   
   - [X] Yes I am willing to submit a PR!
   
   ### Code of Conduct
   
   - [X] I agree to follow this project's [Code of 
Conduct](https://www.apache.org/foundation/policies/conduct)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: 
commits-unsubscr...@dolphinscheduler.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org