1

I'm using Azure SQL Pools/Synapse/SQL DW and have a rule hierarchy that I need to process. At each level a parent can specify if all (AND) or any (OR) children are required in order for the rule to be satisfied. Each level in the hierarchy can specify a different condition to the parent (so you could have an AND condition that contains an OR etc.)

In pure SQL this can be implemented as a loop that starts from leaf level and parses each level by left joining the hierarchy onto the data to be evaluated. Any data that does not match the condition is pruned from the dataset. AND conditions are processed by counting the distinct number of children that exist and the distinct number of children that match.

This creates a lot of complex SQL to maintain, as well as using a less efficient loop. I suspect that the graph functionality may be a better structure here, but cannot see any inbuilt functionality that would actually help with the processing. Likewise hierarchyid sounds appropriate for this however I don't believe it exists in Azure Synapse/Pools/DW

Neil P
  • 2,920
  • 5
  • 33
  • 64

1 Answers1

1

Azure Synapse Analytics dedicated SQL pools do not support either the graph tables or the hierarchyId available in SQL Server box product and Azure SQL DB. Therefore your best option is to probably use a nearby Azure SQL DB to do this processing. Use Azure Data Factory (ADF) or Synapse Pipelines to move data between them.

Alternately, I've done a few question answers which I think give good coverage on using graph or hierarchical data in Synapse and some of the approaches which include: using Azure SQL DB, using WHILE loops and using Azure Synapse Notebooks and the GraphFrames library:

The second question in particular is quite thorough.

wBob
  • 13,710
  • 3
  • 20
  • 37