Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1026631%22%20slang%3D%22en-US%22%3ELoad%20different%20SQL%20database%20tables%20based%20on%20different%20file%20types%20in%20ADF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1026631%22%20slang%3D%22en-US%22%3E%3CP%3EA%20very%20common%20scenario%20in%20data%20lakes%20is%20that%20you%20will%20receive%20files%20that%20land%20in%20different%20types%20and%20shapes%20and%20you%20need%20to%20load%20those%20into%20database%20tables.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20accomplish%20this%20in%20an%20automated%2C%20operationalized%20manner%2C%20you%20will%20need%20an%20ETL%20process%20to%20ingest%20those%20files%20and%20load%20them%20correctly%20into%20the%20proper%20SQL%20database%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20ADF%2C%20you%20can%20set%20either%20polling%20via%20scheduled%20or%20window%20triggers.%20Or%20you%20can%20use%20event-based%20file%20arrival%20triggers%20to%20trigger%20a%20pipeline%20with%20a%20data%20flow.%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%252FPIGw-Z-0upw%253Ffeature%253Doembed%26amp%3Burl%3Dhttps%253A%252F%252Fwww.youtube.com%252Fwatch%253Fv%253DPIGw-Z-0upw%26amp%3Bimage%3Dhttps%253A%252F%252Fi.ytimg.com%252Fvi%252FPIGw-Z-0upw%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%20frameborder%3D%220%22%20allow%3D%22autoplay%3B%20fullscreen%22%20allowfullscreen%3D%22true%22%20title%3D%22Video%22%3E%3C%2FIFRAME%3E%3C%2FDIV%3E%3CP%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%2C%20data%20flow%20is%20used%20with%20many%20different%20sources%2C%20reading%20from%20multiple%20folders%20with%20wildcards%2C%20and%20landing%20the%20data%20into%20different%20SQL%20tables%20based%20on%20rules-based%20criteria.%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%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158504i9400E832DF75BB50%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20need%20to%20pick-up%20many%20different%20files%20in%20a%20source%20Blob%20or%20ADLS%20folder%2C%20you%20can%20use%20wildcards%20(*Tweets.csv)%20with%20no%20schema%20defined%20in%20the%20dataset.%20Set%20%22Allow%20schema%20drift%22%20in%20your%20source%20%26amp%3B%20sink%20and%20then%20create%20an%20on-the-fly%20SQL%20database%20table.%20That%20is%20shown%20in%20the%20video%20and%20diagram%20above%20in%20the%20top%20stream.%20The%20sink%20dataset%20has%20no%20schema%20defined%20for%20the%20SQL%20table.%20The%20table%20is%20defined%20and%20generated%20on%20the%20fly%20based%20on%20the%20source%20metadata%20and%20auto-mapping%20is%20set%20in%20the%20Sink.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%202nd%20example%20stream%20in%20this%20data%20flow%20uses%20wildcarding%20to%20pick-up%202%20files%3A%20%7Btrip_data_1.csv%2C%20trip_fare_1.csv%7D.%20By%20using%20the%20%22Column%20to%20store%20file%20name%22%20property%20in%20the%20source%2C%20ADF%20will%20maintain%20the%20name%20of%20the%20file%20along%20with%20the%20row%20data.%20This%20lineage%20allows%20you%20to%20use%20a%20Conditional%20Split%20transformation%20based%20on%20the%20file%20name%20so%20that%20you%20can%20load%20the%20fare%20data%20into%20the%20fares%20SQL%20table%20and%20the%20trip%20data%20into%20the%20Trip%20Data%20SQL%20table%20using%202%20different%20sink%20transformations.%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%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158505iDF35E762DF25F557%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFinally%2C%20the%203rd%20example%20uses%20parameters.%20In%20this%20case%2C%20I%20have%20a%20data%20flow%20parameter%20that%20stores%20the%20name%20of%20the%20incoming%20filename%20and%20a%20dataset%20parameter%20that%20defines%20the%20name%20of%20the%20table%20that%20I%20wish%20to%20load%20with%20no%20schema.%20I%20am%20using%20Auto%20Mapping%20in%20the%20schema%20to%20keep%20this%20flow%20generic%20and%20reusable.%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%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F158506iEC75C77C76AD0027%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20all%20of%20these%20cases%2C%20because%20I%20need%20to%20search%20and%20iterative%20many%20files%20in%20the%20source%2C%20I%20have%20simple%2C%20generic%20source%20datasets%20that%20only%20define%20the%20Linked%20Service%20credentials%20to%20my%20storage%20account%20and%20a%20blank%20dataset%20that%20only%20points%20to%20my%20storage%20container.%20The%20wildcarding%20handles%20the%20path%20and%20filename%20resolution.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20you%20execute%20this%20data%20flow%20as%20a%20single%20data%20flow%20activity%20in%20an%20ADF%20pipeline%20the%20entire%20set%20of%203%20streams%20will%20all%20run%20together%20on%20a%20single%20Spark%20execution%20at%20scale%20with%20no%20extra%20effort%20on%20your%20part.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1026631%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20to%20solve%20the%20common%20scenario%20in%20data%20lakes%20where%20many%20different%20text%20files%20land%20in%20your%20lake%20and%20need%20to%20get%20loaded%20into%20various%20different%20SQL%20database%20tables%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1026631%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Factory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EBig%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMapping%20Data%20Flows%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

A very common scenario in data lakes is that you will receive files that land in different types and shapes and you need to load those into database tables.

 

To accomplish this in an automated, operationalized manner, you will need an ETL process to ingest those files and load them correctly into the proper SQL database table.

 

With ADF, you can set either polling via scheduled or window triggers. Or you can use event-based file arrival triggers to trigger a pipeline with a data flow.

 

 

In this case, data flow is used with many different sources, reading from multiple folders with wildcards, and landing the data into different SQL tables based on rules-based criteria.

 

clipboard_image_1.png

 

If you need to pick-up many different files in a source Blob or ADLS folder, you can use wildcards (*Tweets.csv) with no schema defined in the dataset. Set "Allow schema drift" in your source & sink and then create an on-the-fly SQL database table. That is shown in the video and diagram above in the top stream. The sink dataset has no schema defined for the SQL table. The table is defined and generated on the fly based on the source metadata and auto-mapping is set in the Sink.

 

The 2nd example stream in this data flow uses wildcarding to pick-up 2 files: {trip_data_1.csv, trip_fare_1.csv}. By using the "Column to store file name" property in the source, ADF will maintain the name of the file along with the row data. This lineage allows you to use a Conditional Split transformation based on the file name so that you can load the fare data into the fares SQL table and the trip data into the Trip Data SQL table using 2 different sink transformations.

 

clipboard_image_0.png

Finally, the 3rd example uses parameters. In this case, I have a data flow parameter that stores the name of the incoming filename and a dataset parameter that defines the name of the table that I wish to load with no schema. I am using Auto Mapping in the schema to keep this flow generic and reusable.

 

clipboard_image_1.png

 

In all of these cases, because I need to search and iterative many files in the source, I have simple, generic source datasets that only define the Linked Service credentials to my storage account and a blank dataset that only points to my storage container. The wildcarding handles the path and filename resolution.

 

When you execute this data flow as a single data flow activity in an ADF pipeline the entire set of 3 streams will all run together on a single Spark execution at scale with no extra effort on your part.