Download as PDF, PPTX
![© 2018 Cathrine Wilhelmsen (hi@cathrinew.net) Lookup Output { "firstRow" : { "Column1" : "Value", "Column2" : "Value" } } { "count" : "2", "value" : [ { "Column1" : "Value", "Column2" : "Value" }, { "Column1" : "Value", "Column2" : "Value" } ] }](https://image.slidesharecdn.com/sqlsatoslo-cathrinewilhelmsen-dynamicpipelinesinazuredatafactory-190902110243/75/Building-Dynamic-Pipelines-in-Azure-Data-Factory-SQLSaturday-Oslo-55-2048.jpg)
![© 2018 Cathrine Wilhelmsen (hi@cathrinew.net) Lookup Output: All Rows { "count" : "2", "value" : [ { "Column1" : "Value", "Column2" : "Value" }, { "Column1" : "Value", "Column2" : "Value" } ] } value](https://image.slidesharecdn.com/sqlsatoslo-cathrinewilhelmsen-dynamicpipelinesinazuredatafactory-190902110243/75/Building-Dynamic-Pipelines-in-Azure-Data-Factory-SQLSaturday-Oslo-58-2048.jpg)
Uploaded byCathrine Wilhelmsen
Building Dynamic Pipelines in Azure Data Factory (SQLSaturday Oslo)
The document discusses building dynamic data pipelines in Azure Data Factory, emphasizing the importance and techniques for using dynamic solutions, such as parameters, expressions, and loops. It covers best practices for implementing foreach loops, lookups, and conditionals to enhance pipeline flexibility and efficiency. The author provides demos and examples to illustrate key concepts throughout the content.
Building Dynamic Pipelines in Azure Data Factory (SQLSaturday Oslo)
- 1. Building Dynamic Pipelines inAzure Data Factory Cathrine Wilhelmsen SQLSatOslo · August 31st, 2019
- 2. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Building Dynamic Data Pipelines in Azure Data Factory
- 3. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Our sponsorsPlatinum and event Gold Global Silver Bronze Raffle
- 4.
- 5. © 2018 CathrineWilhelmsen (hi@cathrinew.net) You are an Azure Data Factory developer
- 6. © 2018 CathrineWilhelmsen (hi@cathrinew.net)
- 7. © 2018 CathrineWilhelmsen (hi@cathrinew.net) …this is getting pretty tedious…
- 8. © 2018 CathrineWilhelmsen (hi@cathrinew.net) There has to be a better way!
- 9.
- 10. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Why use dynamic solutions?
- 11. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Why not use dynamic solutions?
- 12. © 2018 CathrineWilhelmsen (hi@cathrinew.net) How dynamic should the solution be?
- 13. © 2018 CathrineWilhelmsen (hi@cathrinew.net) How dynamic should the solution be?
- 14. © 2018 CathrineWilhelmsen (hi@cathrinew.net) How do you make dynamic solutions? 1. 2. 3. 4.
- 15.
- 16. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Parameters
- 17. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Passing Parameters
- 18. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Passing Parameters trigger pipeline dataset user activity linked service
- 19. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Passing Parameters trigger pipeline dataset user activity linked service pipeline
- 20. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Passing Parameters trigger pipeline dataset user activity linked service data flow
- 21. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Passing Parameters trigger pipeline dataset user activity linked service activity
- 22. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Passing Parameters trigger pipeline dataset user activity linked service pipeline activity data flow
- 23. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Parameters @pipeline().parameters. @dataset(). @linkedService(). ParameterName ParameterName ParameterName
- 24. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Parameters @pipeline().parameters. @dataset(). @linkedService(). ParameterName ParameterName ParameterName
- 25. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Parameters @pipeline().parameters. @pipeline().DataFactory @pipeline().TriggerTime ParameterName and System Variables
- 26. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Parameters @pipeline().parameters. @pipeline().DataFactory @pipeline().TriggerTime ParameterName and System Variables
- 27.
- 28. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Expressions @
- 29. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Functions
- 30. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Expressions with Functions @
- 31.
- 32. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Combining Strings @concat @{…}
- 33. © 2018 CathrineWilhelmsen (hi@cathrinew.net) String Concatenation @concat('First Name: ', pipeline().parameters.FirstName, ', Last Name: ', pipeline().parameters.LastName)
- 34. © 2018 CathrineWilhelmsen (hi@cathrinew.net) String Interpolation First Name: @{pipeline().parameters.FirstName}, Last Name: @{pipeline().parameters.LastName}
- 35. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Combining Strings String Concatenation String Interpolation
- 36.
- 37.
- 38. © 2018 CathrineWilhelmsen (hi@cathrinew.net) ForEach Loops
- 39. © 2018 CathrineWilhelmsen (hi@cathrinew.net) ForEach Loops @item()
- 40.
- 41. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Array Items
- 42. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Array Items colors @item()
- 43. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Array Items @item parts @item colors @item sets
- 44.
- 45. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Object Items
- 46. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Object Items { "Name": "colors", "Extension": "csv" } @item()
- 47. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Object Items { "Name": "colors", "Extension": "csv" } @item().Name
- 48. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Object Items @item().Name parts @item().Name colors @item().Name sets
- 49. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Limitations when using Foreach Loops Nested ForEach Loops are not supported Maximum number of items: 100 000 Maximum number of parallel executions: 50 Default number of parallel executions: 20
- 50. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Best Practices for using ForEach Loops Executing one activity? Add directly in ForEach Executing multiple activities? Create new pipeline and execute from ForEach
- 51.
- 52.
- 53. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Lookup
- 54. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Lookup Output
- 55. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Lookup Output { "firstRow" : { "Column1" : "Value", "Column2" : "Value" } } { "count" : "2", "value" : [ { "Column1" : "Value", "Column2" : "Value" }, { "Column1" : "Value", "Column2" : "Value" } ] }
- 56. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Lookup Output: First Row Only { "firstRow" : { "Column1" : "Value", "Column2" : "Value" } } firstRow
- 57. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Lookup Output: First Row Only { "firstRow" : { "Column1" : "Value", "Column2" : "Value" } } firstRow .Column1
- 58. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Lookup Output: All Rows { "count" : "2", "value" : [ { "Column1" : "Value", "Column2" : "Value" }, { "Column1" : "Value", "Column2" : "Value" } ] } value
- 59. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Lookup Output @{activity('Lookup').output.firstRow.Column1} @{activity('Lookup').output.value}
- 60. DEMO Let's add alookup to control our loop!
- 61.
- 62. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Controls and Conditionals
- 63. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Branching and Chaining Success Failure Completion Skipped
- 64. © 2018 CathrineWilhelmsen (hi@cathrinew.net) If Condition
- 65. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Variables
- 66. DEMO Let's create some ifstatements!
- 67. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Controls and Conditionals
- 68. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Until
- 69. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Get Metadata
- 70. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Get Metadata
- 71. DEMO Let's check for fileexistence!
- 72. © 2018 CathrineWilhelmsen (hi@cathrinew.net) Good luck!
- 73. © 2018 CathrineWilhelmsen (hi@cathrinew.net) @cathrinew cathrinew.net hi@cathrinew.net thank you!