ScheduleTrigger无重叠 [英] ScheduleTrigger Without Overlap

问题描述

我有一个ADF v2管道，该管道每小时将一个表的增量数据副本复制到另一个db.  步骤如下:

1. 在目标数据库上截断table1_stage
2. 将数据从源table1复制到目标table1_stage
3. 将记录从table1_stage合并到目标1上的table

这一切都很好，直到有大量更新，并且管道需要一个多小时才能完成.当先前的触发器仍在步骤3上时，新的触发器将开始并截断table1_stage.显然，这会引起问题.

为了补救，我更改为最大并发值为1的翻滚窗口.这解决了主要问题，但又带来了另一种烦恼.如果管道需要7个小时才能运行，则完成后翻滚窗口就会赶上，并且基本上会运行7次 连续填写错过的时间间隔.由于我实际上没有在工作中使用时间窗口，因此这并不是真正必要的，我希望它仅在上次结束时间之后一个小时运行.似乎通过类似的方法很容易 计划的触发器上的最大并发性"选项，但似乎不可用.另一种选择是只设置预定的天蓝色函数或webjob以在我需要它们时启动管道运行，但希望保留所有操作方面 包含在ADF中.

解决方案

您可以执行以下操作:

-在目标数据库中创建一个表，表中有1列称为进行中".包含布尔值.

-创建2个存储过程，1个将进行中的行更新为true，1个更新为false.

-在安排好的触发器开始时，创建一个直到脚本，该脚本包含一个布尔值是true还是false的查找，别忘了添加一个等待，否则它将继续执行.

-如果为false，请运行SP以将进度更新为true.

-让它运行，完成后，运行存储过程以将其更新为false. 

I have an ADF v2 pipeline that does an incremental data copy of a table every hour to a different db.   The steps are as follows:

1. truncate table1_stage on destination db
2. copy data from source table1 to destination table1_stage
3. merge records from table1_stage to table1 on destination

This all works great until there are is a big batch of updates and the pipeline takes more than an hour, to complete.  A new trigger starts and truncates table1_stage while the previous trigger is still on step 3.  This obviously causes a problem.  

To remedy I changed to a tumbling window with a max concurrency of 1.  This solves the major problem but introduced another annoyance.  If a pipeline takes 7 hours to run, when it finishes the tumbling window catches up and basically runs 7 times in a row to fill in the missed intervals.  Since I am not actually making use of the time windows in the job, this isn't really necessary, I would prefer it to just run an hour after the last finish time.  Seems like this would be easy with a similar max concurrency option on the scheduled trigger, but that doesn't appear to be available. Another option is to just set up a scheduled azure function or webjob to kick off the pipeline runs when I want them, but was hoping to keep all the operational aspects contained within ADF.  Is there any oter way to accomplish this?

解决方案

You could do the following:

- Create a table in the destination database with 1 column called "inprogress" containing a boolean.

- Create 2 stored procedures, 1 to update the inprogress row to true and 1 to update to false. 

- On start of the scheduled trigger create a until script that contains a lookup if the boolean is true or false, don't forget to add a wait, otherwise it keeps doing it.

- If its false, run the SP to update the inprogress to true. 

- Let it run, when its done, run a storedprocedure to update it to false. 

这篇关于ScheduleTrigger无重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！