Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1254442%22%20slang%3D%22en-US%22%3EADF%20Data%20Flows%20Script%20Intro%3A%20Copy%2C%20Paste%2C%20Snippets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1254442%22%20slang%3D%22en-US%22%3E%3CP%3EADF%20Data%20Flows%20has%20a%20low-code%20graph-based%20UI%20to%20design%20and%20develop%20data%20transformations%20at%20scale.%20But%20the%20script%20behind%20those%20graphs%20is%20very%20powerful.%20This%20article%20introduces%20you%20to%20the%20Data%20Flow%20Script%20and%20how%20to%20use%20it%20to%20copy%2C%20paste%2C%20and%20share%20data%20transformation%20code.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3C%2FP%3E%3CDIV%20class%3D%22video-embed-center%20video-embed%22%3E%3CIFRAME%20class%3D%22embedly-embed%22%20src%3D%22https%3A%2F%2Fcdn.embedly.com%2Fwidgets%2Fmedia.html%3Fsrc%3Dhttps%253A%252F%252Fwww.youtube.com%252Fembed%252F3_1I4XdoBKQ%253Ffeature%253Doembed%26amp%3Bdisplay_name%3DYouTube%26amp%3Burl%3Dhttps%253A%252F%252Fwww.youtube.com%252Fwatch%253Fv%253D3_1I4XdoBKQ%26amp%3Bimage%3Dhttps%253A%252F%252Fi.ytimg.com%252Fvi%252F3_1I4XdoBKQ%252Fhqdefault.jpg%26amp%3Bkey%3Dfad07bfa4bd747d3bdea27e17b533c0e%26amp%3Btype%3Dtext%252Fhtml%26amp%3Bschema%3Dyoutube%22%20width%3D%22400%22%20height%3D%22225%22%20scrolling%3D%22no%22%20title%3D%22YouTube%20embed%22%20frameborder%3D%220%22%20allow%3D%22autoplay%3B%20fullscreen%22%20allowfullscreen%3D%22true%22%3E%3C%2FIFRAME%3E%3C%2FDIV%3E%3CP%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBelow%20is%20a%20sample%20data%20flow%20where%20I%20have%20a%20fairly%20complex%20Aggregate%20transformation%20that%20I%20wish%20to%20use%20in%20another%20data%20flow.%20To%20do%20that%2C%20I'm%20going%20to%20click%20on%20the%20Script%20button%20the%20ADF%20Data%20Flow%20design%20UI%20to%20view%20the%20script%20behind.%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%22summary1.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178607i5610359183873F1D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22summary1.png%22%20alt%3D%22summary1.png%22%20%2F%3E%3C%2FSPAN%3E%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%22scriptbutton.png%22%20style%3D%22width%3A%20150px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F179574i1ACD119EC05CC2F0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22scriptbutton.png%22%20alt%3D%22scriptbutton.png%22%20%2F%3E%3C%2FSPAN%3E%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%22script2.png%22%20style%3D%22width%3A%20592px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F179576iD608E76EE5AED0E3%2Fimage-dimensions%2F592x282%3Fv%3D1.0%22%20width%3D%22592%22%20height%3D%22282%22%20title%3D%22script2.png%22%20alt%3D%22script2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EDetails%20of%20Data%20Flow%20Script%20are%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fdata-flow-script%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%20at%20the%20ADF%20doc%20page.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEach%20data%20flow%20starts%20with%20a%20source%20transformation.%20Each%20subsequent%20transformation%20in%20the%20script%20will%20start%20with%20the%20name%20of%20the%20incoming%20stream.%20We%20want%20to%20share%20the%20aggregate%20called%20SummaryStats.%20To%20do%20that%2C%20click%20Script%2C%20then%20Ctrl-F%20to%20enter%20into%20Find%2FReplace.%20Search%20for%20%22SummaryStats%22.%20To%20share%20this%20transformation%2C%20we%20copy%20from%20the%20aggregate()%20function%20to%20the%20end%20of%20the%20expression%2C%20just%20before%20the%20transformation%20name.%20The%20name%20of%20the%20transformation%20uses%20the%20syntax%20~%26gt%3B%20and%20then%20the%20name%20of%20transformation.%20When%20I%20share%20this%2C%20I%20do%20not%20copy%20the%20transformation%20name%2C%20assuming%20that%20I%20can%20set%20a%20new%20name%20when%20I%20paste%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20code%20that%20gets%20copied%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Eaggregate(each(match(true())%2C%20%24%24%2B'_NotNull'%20%3D%20countIf(!isNull(%24%24))%2C%20%24%24%20%2B%20'_Null'%20%3D%20countIf(isNull(%24%24)))%2Ceach(match(type%3D%3D'double'%7C%7Ctype%3D%3D'integer'%7C%7Ctype%3D%3D'short'%7C%7Ctype%3D%3D'decimal')%2C%20%24%24%2B'_stddev'%20%3D%20round(stddev(%24%24)%2C2)%2C%20%24%24%20%2B%20'_min'%20%3D%20min%20(%24%24)%2C%20%24%24%20%2B%20'_max'%20%3D%20max(%24%24)%2C%20%24%24%20%2B%20'_average'%20%3D%20round(avg(%24%24)%2C2)%2C%20%24%24%20%2B%20'_variance'%20%3D%20round(variance(%24%24)%2C2))%2C%20each(match(type%3D%3D'string')%2C%20%24%24%2B'_maxLength'%20%3D%20max(length(%24%24))))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20includes%20all%20of%20my%20expression%20function%20language%20work%2C%20so%20I%20can%20share%20that%20code%20with%20other%20data%20flows.%20I%20could%20also%20use%20the%20script-behind%20to%20rename%20my%20transformations%20using%20find%2Freplace.%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%22script3.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F179581i39B2400AC25B4640%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22script3.png%22%20alt%3D%22script3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20started%20including%20shareable%20data%20flow%20script%20snippets%20in%20the%20script%20docs%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fdata-factory%2Fdata-flow-script%23script-snippets%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%20You%20can%20copy%20those%20snippet%20blocks%20and%20paste%20them%20into%20your%20data%20flows.%20Watch%20the%20video%20above%20toward%20the%20end%20for%20an%20example%20of%20how%20to%20use%20the%20code%20snippets.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1254442%22%20slang%3D%22en-US%22%3E%3CP%3EADF%20Data%20Flows%20has%20a%20low-code%20graph-based%20UI%20to%20design%20and%20develop%20data%20transformations%20at%20scale.%20But%20the%20script%20behind%20those%20graphs%20is%20very%20powerful.%20This%20article%20introduces%20you%20to%20the%20Data%20Flow%20Script%20and%20how%20to%20use%20it%20to%20copy%2C%20paste%2C%20and%20share%20transformation%20code.%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%22script3.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F179579i3092B4A126AEB9AC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22script3.png%22%20alt%3D%22script3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1254442%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Factory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20ETL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMapping%20Data%20Flows%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

ADF Data Flows has a low-code graph-based UI to design and develop data transformations at scale. But the script behind those graphs is very powerful. This article introduces you to the Data Flow Script and how to use it to copy, paste, and share data transformation code.

 

 

Below is a sample data flow where I have a fairly complex Aggregate transformation that I wish to use in another data flow. To do that, I'm going to click on the Script button the ADF Data Flow design UI to view the script behind.

 

summary1.png

 

scriptbutton.png

 

script2.png

Details of Data Flow Script are here at the ADF doc page.

 

Each data flow starts with a source transformation. Each subsequent transformation in the script will start with the name of the incoming stream. We want to share the aggregate called SummaryStats. To do that, click Script, then Ctrl-F to enter into Find/Replace. Search for "SummaryStats". To share this transformation, we copy from the aggregate() function to the end of the expression, just before the transformation name. The name of the transformation uses the syntax ~> and then the name of transformation. When I share this, I do not copy the transformation name, assuming that I can set a new name when I paste it.

 

Here is the code that gets copied:

 

 

aggregate(each(match(true()), $$+'_NotNull' = countIf(!isNull($$)), $$ + '_Null' = countIf(isNull($$))),each(match(type=='double'||type=='integer'||type=='short'||type=='decimal'), $$+'_stddev' = round(stddev($$),2), $$ + '_min' = min ($$), $$ + '_max' = max($$), $$ + '_average' = round(avg($$),2), $$ + '_variance' = round(variance($$),2)), each(match(type=='string'), $$+'_maxLength' = max(length($$))))

 

This includes all of my expression function language work, so I can share that code with other data flows. I could also use the script-behind to rename my transformations using find/replace.

 

script3.png

 

I've started including shareable data flow script snippets in the script docs here. You can copy those snippet blocks and paste them into your data flows. Watch the video above toward the end for an example of how to use the code snippets.