Use ADF to Turn Hierarchical Structures into Relational Data

Published Nov 24 2021 02:44 PM 2,113 Views
Microsoft

Azure Data Factory and Azure Synapse Analytics pipelines include the powerful code-free data transformation feature called Mapping Data Flows. In this post, I'll show you how you can use data flows to visually transform hierarchies into relational data for downstream consumption by destinations that require data to be shaped like a table.

 

The data I'm going to work with is a JSON source and I want to grab the coordinates and type properties from the geometry field. Note that the coordinates property inside the geometry structure is an array.

 

MarkKromer_0-1637792192038.png

 

The way to form this into a relational structure is by building a mapping data flow that first reshapes that data into your desired column format and then takes the array and unrolls that into a series of denormalized rows.

 

MarkKromer_1-1637792493412.png

 

The FlattenStructure transformation in the diagram above is a Derived Column. To turn your structure into a relational table, just pick the name of the struct in the Derived Column and use a column pattern to define which hierarchy you wish to flatten.

 

MarkKromer_3-1637793008306.png

 

What this will produce is an updated projection in your data flow with the 2 properties inside of the geometry structure now as separate columns.

 

MarkKromer_4-1637793153993.png

 

That leaves the coordinates array which requires the Flatten transformation. Arrays are trickier when moving from hierarchical to relational because each value in that array will need to be added to existing rows outside of that array, resulting in a denormalized schema.

 

In the Flatten transformation, you will pick the coordinates array for unroll and then define the target projection.

 

MarkKromer_5-1637793315766.png

 

That's it! You now have a fully flattened set of data from your JSON structures that can be consumed by relational targets.

MarkKromer_6-1637793716580.png

 

Click on these documentation links to learn more about Mapping Data Flows in ADF and Synapse: Flatten transformation, column patterns in Derived Column.

 

%3CLINGO-SUB%20id%3D%22lingo-sub-3003526%22%20slang%3D%22en-US%22%3EUse%20ADF%20to%20Turn%20Hierarchical%20Structures%20into%20Relational%20Data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3003526%22%20slang%3D%22en-US%22%3E%3CP%3EAzure%20Data%20Factory%20and%20Azure%20Synapse%20Analytics%20pipelines%20include%20the%20powerful%20code-free%20data%20transformation%20feature%20called%20Mapping%20Data%20Flows.%20In%20this%20post%2C%20I'll%20show%20you%20how%20you%20can%20use%20data%20flows%20to%20visually%20transform%20hierarchies%20into%20relational%20data%20for%20downstream%20consumption%20by%20destinations%20that%20require%20data%20to%20be%20shaped%20like%20a%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20data%20I'm%20going%20to%20work%20with%20is%20a%20JSON%20source%20and%20I%20want%20to%20grab%20the%20coordinates%20and%20type%20properties%20from%20the%20geometry%20field.%20Note%20that%20the%20coordinates%20property%20inside%20the%20geometry%20structure%20is%20an%20array.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarkKromer_0-1637792192038.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329319iFDDF865D3CD6953D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarkKromer_0-1637792192038.png%22%20alt%3D%22MarkKromer_0-1637792192038.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20way%20to%20form%20this%20into%20a%20relational%20structure%20is%20by%20building%20a%20mapping%20data%20flow%20that%20first%20reshapes%20that%20data%20into%20your%20desired%20column%20format%20and%20then%20takes%20the%20array%20and%20unrolls%20that%20into%20a%20series%20of%20denormalized%20rows.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarkKromer_1-1637792493412.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329321i9958E7EA38DBFE50%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarkKromer_1-1637792493412.png%22%20alt%3D%22MarkKromer_1-1637792493412.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20FlattenStructure%20transformation%20in%20the%20diagram%20above%20is%20a%20Derived%20Column.%20To%20turn%20your%20structure%20into%20a%20relational%20table%2C%20just%20pick%20the%20name%20of%20the%20struct%20in%20the%20Derived%20Column%20and%20use%20a%20column%20pattern%20to%20define%20which%20hierarchy%20you%20wish%20to%20flatten.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarkKromer_3-1637793008306.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329330i50F587D0AA64EB90%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarkKromer_3-1637793008306.png%22%20alt%3D%22MarkKromer_3-1637793008306.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20this%20will%20produce%20is%20an%20updated%20projection%20in%20your%20data%20flow%20with%20the%202%20properties%20inside%20of%20the%20geometry%20structure%20now%20as%20separate%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarkKromer_4-1637793153993.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329331iC7B4F0A1AE6CB4BC%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarkKromer_4-1637793153993.png%22%20alt%3D%22MarkKromer_4-1637793153993.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20leaves%20the%20coordinates%20array%20which%20requires%20the%20Flatten%20transformation.%20Arrays%20are%20trickier%20when%20moving%20from%20hierarchical%20to%20relational%20because%20each%20value%20in%20that%20array%20will%20need%20to%20be%20added%20to%20existing%20rows%20outside%20of%20that%20array%2C%20resulting%20in%20a%20denormalized%20schema.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20Flatten%20transformation%2C%20you%20will%20pick%20the%20coordinates%20array%20for%20unroll%20and%20then%20define%20the%20target%20projection.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarkKromer_5-1637793315766.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329332i3FE8F0D3CD9AC6E2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarkKromer_5-1637793315766.png%22%20alt%3D%22MarkKromer_5-1637793315766.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20it!%20You%20now%20have%20a%20fully%20flattened%20set%20of%20data%20from%20your%20JSON%20structures%20that%20can%20be%20consumed%20by%20relational%20targets.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MarkKromer_6-1637793716580.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329334iB3F7838331A275A1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MarkKromer_6-1637793716580.png%22%20alt%3D%22MarkKromer_6-1637793716580.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20on%20these%20documentation%20links%20to%20learn%20more%20about%20Mapping%20Data%20Flows%20in%20ADF%20and%20Synapse%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fdata-flow-flatten%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EFlatten%20transformation%3C%2FA%3E%2C%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fconcepts-data-flow-column-pattern%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ecolumn%20patterns%20in%20Derived%20Column%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3003526%22%20slang%3D%22en-US%22%3E%3CP%3EAzure%20Data%20Factory%20and%20Azure%20Synapse%20Analytics%20pipelines%20include%20the%20powerful%20code-free%20data%20transformation%20feature%20called%20Mapping%20Data%20Flows.%20In%20this%20post%2C%20I'll%20show%20you%20how%20you%20can%20use%20data%20flows%20to%20visually%20transform%20hierarchies%20into%20relational%20data%20for%20downstream%20consumption%20by%20destinations%20that%20require%20data%20to%20be%20shaped%20like%20a%20table.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3003526%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Factory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Data%20Integration%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20Synapse%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMapping%20Data%20Flows%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Nov 24 2021 02:44 PM
Updated by: