SERVER HOIT ASIA

Showing posts with label Data. Show all posts

(2020-Apr-24) Using UNION SQL operator is a very common practice to combine two or more datasets together, it helps to create a single result set from many sourcing ones.

Azure Data Factory (ADF) Mapping Data Flows has a similar capability to combine two streams of data with the help of Union transformation, where both data streams can be stacked using either columns' names or columns' positions within datasets. Nothing special, it's a usual way of doing things.

Image by Free-Photos from Pixabay

Recently I had a chance to work on a special case of creating a file where the first two rows were static and contained output table metadata (columns names and column types), assuming that further reading process would be able to explicitly identify each column data type and consume new file correctly in a single run.

Another way to look at this case will be when a table header would provide both columns names and column data types (source: https://data.world/data-society/global-climate-change-data).

As I have mentioned earlier, it's very easy to create this data flow process in the Azure Data Factory.

Sourcing files
My header file will contain only 2 records (column names and column data types):

Incoming data file contained temperature records for some of the major cities around the world and I was only interested to extract Canadian cities temperature information:

ADF data flow transformation steps

(1) Filter the incoming data sets with the following condition: Country == 'Canada'
(2) Derived Column row_id with a value equals to 2 (or something greater than 1).
(3) Union SourceHeader & updated SourceTemperature datasets:

(4) Sort combined datasets by row_id column in ascending order before saving it into an output file.

Output result set
So, even this whole process to create a mapping data flow in Azure Data Factory to union two data files was very trivial, and I definitely didn't try to show off my development skills :-)

Just an idea that we can physically segregate header metadata with column names and data types in a separate static file and then always attach it to various dynamic incoming data sets with the UNION data transformation, had simplified my process to create the output file that I needed.

Another benefit of using Data Factory Mapping Data Flows!

(2019-Oct-27) Creation or deletion files in your Azure Storage account may initiate data ingestion process and will support your event-driven data platform architecture.

Image by Lars_Nissen_Photoart from Pixabay

Microsoft recently introduced an additional change to file-event triggers within Azure Data Factor. What this change does, it gives you a bit more control for files that you want to be used or not to be used in your data ingestion process.

Before we get into more details on how to use this new "Ignore empty blobs" feature, let's briefly review possible scenarios of using file event triggers in your data processing workflow.

Ingest new data in batches

A batch of incoming sourcing data may come as a set of files and sometimes those files could be archives with other files within them. Those incoming files don't arrive at once, usually, it's a sequential process and it may have some delays between starting and ending files of this set. In order to orchestrate a synchronized data ingestion process and start loading those files as a complete set, your data provider will generate an additional flag-file (or end-file) to indicate the end of file uploading for a particular batch. And only after receiving this flag-file, your data ingestion process starts.

Ingest new data as it comes

With this approach, you create your data ingestion framework to react to each incoming data file that may arrive at a particular location. And as soon as the new file arrives, it triggers your process to ingest just this new data file into your data store.

Where this new ADF triggers change is helpful, it places control on this particularly reactive process to load new data files. In case if your data vendor by mistake or other reasons sends you an empty file, then when you set this "Ignore empty blobs" setting to "Yes", your data ingestion pipeline wouldn't be triggered and you don't have to worry about creating a special logic to handle empty sourcing files in your data ingestion pipeline. Empty files won't be loaded at all.

Working with ADF triggers has become a bit easier ! :-)

(2018-Nov-20) After working and testing the functionality of variables within Azure Data Factory pipelines, I realized that it's worth to explore existing system variables. That's basically could be my toolbox to collect and store control flow metrics of my pipelines.

Looking at the official Microsoft resource System variables supported by Azure Data Factory you're given with a modest selection of system variables that you can analyze and use both on a pipeline and pipeline trigger level. Currently, you have three ways to monitor Azure Data Factory: visually, with the help of Azure Monitor or using a code to retrieve those metrics.

But here is a case of how I want to monitor a control flow of my pipeline in Azure Data Factory:

This the same data ingestion pipeline from my previous blog post - Story of combining things together that builds a list of files from a Blob storage and then data from those files are copied to a SQL database in Azure. My intention is to collect and store event information of all the completed tasks, such as Get Metadata and Copy Data.

Here is a current list of pipeline system variable in my disposal:
@pipeline().DataFactory - Name of the data factory the pipeline run is running within
@pipeline().Pipeline - Name of the pipeline
@pipeline().RunId - ID of the specific pipeline run
@pipeline().TriggerType - Type of the trigger that invoked the pipeline (Manual, Scheduler)
@pipeline().TriggerId - ID of the trigger that invokes the pipeline
@pipeline().TriggerName - Name of the trigger that invokes the pipeline
@pipeline().TriggerTime - Time when the trigger that invoked the pipeline. The trigger time is the actual fired time, not the scheduled time.

And after digging a bit more and testing pipeline activity, I've discovered additional metrics that I can retrieve on the level of each individual task:
PipelineName,
JobId,
ActivityRunId,
Status,
StatusCode,
Output,
Error,
ExecutionStartTime,
ExecutionEndTime,
ExecutionDetails,
Duration

Here is my final pipeline in ADF that can populate all these metrics into my custom logging database table:

And this is how I made it work:

1) First I created dbo.adf_pipeline_log table in my SQL database in Azure:

2) Then I used [Append Variable] Activity task as "On Completion" outcome from the "Get Metadata" activity with the following expression to populate a new array type var_logging variable:

var_logging = 
@concat('Metadata Store 01|Copy|',
,pipeline().DataFactory,'|'
,activity('Metadata Store 01').Duration,'|'
,activity('Metadata Store 01').Error,'|'
,activity('Metadata Store 01').ExecutionDetails,'|'
,activity('Metadata Store 01').ExecutionEndTime,'|'
,activity('Metadata Store 01').ExecutionStartTime,'|'
,activity('Metadata Store 01').JobId,'|'
,activity('Metadata Store 01').Output,'|'
,pipeline().Pipeline,'|'
,activity('Metadata Store 01').ActivityRunId,'|'
,activity('Metadata Store 01').Status,'|'
,activity('Metadata Store 01').StatusCode)

where each of the system variables is concatenated and separated with pipe character "|".

I did a similar thing to populate the very same var_logging variable in the ForEach container where actual data copy operation occurs:

3) And then I used this final tasks to populate my dbo.adf_pipeline_log table using data from the var_logging variable by calling a stored procedure:

Where the whole trick is to split each of the text lines of the var_logging variable into another array of values split by "|" characters. Then by knowing the position of each individual system variables values, I can set them to their appropriate stored procedure parameters / columns in my logging table (e.g. @split(item(),'|')[0] for the ActivityTask).

This provided me a flexibility to see both Completed and Failed activity runs (to test a failed activity I had to temporarily rename the target table of my Copy Data task). I can now read this data and get more additional insights from the SQL Server table.

Let me know what you think about this, and have a happy data adventure!

(2018-Oct-29) There are only a few sentences in the official Microsoft web page that describe newly introduced activity task (Append Variable) to add a value to an existing array variable defined in Azure Data Factory - Append Variable Activity in Azure Data Factory But it significantly improves your ability to control a workflow of the data transformation activities of your Data Factory pipeline.

Suppose, you have several folders in your Azure Blob storage container, where daily product inventory data from different stores is saved. You need to transfer those files with product stock daily snapshots to a database.

It will be a natural way to get a list of files from all sourcing folders and then load them all into your database.

Technically:
1) We can read metadata of our sourcing folders from the Blob storage
2) Then we can extract all the files names and save them in one queue object
3) And finally, use this file list queue to read and transfer data into a SQL database.

Let's recreate this use case in our Azure Data Factory pipeline.

1) To get metadata of our sourcing folders, we need to select "Child Items" for the output of our [Get Metadata] activity task:

Which provides a list of sub-folders and files inside the given folder with a list of name and type of each child item.
Here is an output of this task using a list of files from my first store:

2) Then I need to extract file names. To make it happen I pass the output of the [Get Metadata] activity task to my [ForEach] container.
Where Items parameter is set to @activity('Metadata Store 01').output.childitems.

And then within this loop container, I actually start using this new [Append Variable] activity task and specifically choose to extract only names and not types from the previous task output set. Please note that this task can only be used for 'Array' type of variables in your pipeline.

Then all that I have to do is to replicate this logic for other sourcing folders and stream the list of the file names to the very same variable.
In my case it's var_file_list. Don't forget to define all the necessary variables within your pipeline.

3) Once all the file names are extracted into my array variable, then I can use this as a queue for my data load task. My next [ForEach] loop container's Items parameter will be set to this value: @variables('var_file_list')

And internal [Copy Data] activity task within this loop container will receive file names by using individual item names from my variable loop set [@item()].

List of files is appended from each sourcing folders and then all the files are successfully loaded into my Azure SQL database. Just to check a final list of file names, I copied the content of my var_file_list variable into another testing var_file_list_check variable to validate its content.

Azure Data Factory allows more flexibility with this new [Append Variable] activity task and I do recommend to use it more and more in your data flow pipelines! :-)