2

Creating a queue table in SQL Server is a much-studied problem. However, I would like to implement one in Azure Synapse where many of the building blocks do not exists. Specifically

  • no table hints (READPAST etc.)

  • no OUTPUT clause

  • sp_getapplock is not available

Our Synapse instance is configured READ UNCOMMITTED.

Each evening a batch job will run. Fewer than 100 items will be placed in the queue, in the desired order. These will then be processed by between 4 and 10 concurrent consumers until the queue is drained. The cycle repeats the following day.

The number of items and consumers can change from day to day. The elapsed time for items will be quite skewed, from under a minute to over an hour. The design of the queue and the consumer code is completely open. The process is orchestrated by Azure Data Factory (ADF); the solution can rely on ADF if needed. We would rather avoid additional Azure services to limit costs. When the run fails we do not want to re-process completed items but those in-flight can be abandoned and started from scratch i.e. checkpoint/ restart at the item level is desired.

Improve this question
5
  • Hi, we implement a pattern not dissimilar to this, where parts of it have to run sequentially and part of it concurrently. I use Synapse Pipelines and a series of nested pipelines executed by the Execute Pipeline activity because as you know, you cannot nest ForEach loops. An earlier 'parent' ForEach loop runs sequentially and later nested ForEach loops run in parallel to take advantage of that great feature of ADF/ Synapse Pipelines. I can describe in more detail if you think that would be useful.
    – wBob
    Aug 2, 2021 at 15:48
  • @wBob that sounds clever. I can't visualise how the outer, sequential pipeline gives the desired behaviour so yes please, more detail.
    – Michael Green
    Aug 3, 2021 at 4:19
  • Any reason you wouldn't use Azure service bus for this? For like $10 per month you get 13m items which should be enough. You can set up multiple consumers on a topic and this answer suggests FIFO is possible as well: stackoverflow.com/questions/28702033/…
    – blobbles
    Sep 10, 2022 at 20:12
  • @blobbles architectural simplicity, too. Adding another service adds CI/CD effort, monitoring, training, support etc. If there were a solution that avoided this incremental complexity it would be preferable.
    – Michael Green
    Sep 11, 2022 at 1:09
  • @MichaelGreen - it just depends where you want to put your complexity, I guess. Designing a queue in ADF is probably a lot more complex than using the already provided services. We use Service Bus with Terraform and wrap releases of IaC with code changes, but once setup the queues/topics virtually never change, just new ones added and new consumers. Since the schema of the message isn't part of the topic setup, it's a pretty light automation overhead, only a few lines of Terraform code.
    – blobbles
    Sep 11, 2022 at 20:33

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.