Azure Data Factory - Adding Keyvault secret for password in SSIS Package in Azure Data Factory
Storing the sensitive information is an importing thing while developing a solution. Using Azure KeyVault we can store our secrets and access them using a linked service in Azure Data Factory.
Azure Data Factory - Run SSIS packages in ADF as File System (Project Model) using SSIS-IR
In this we are using Project Deployment Model, we are taking the .ispac file that is getting generated after build. From that we are uploading the .ispac file and the package(.dtsx) to Azure File Share.
If you want to know the FileSystem (Package),
Please visit: https://youtu.be/LzvoUwriio8
Azure Data Factory - Run SSIS packages in ADF as File System (Package Model) using SSIS-IR
In this we are using package deployment model and storing our Configuration File (.dtsConfig) and Package(.dtsx) in an Azure File Share.
Using Azure Data Factory we are calling the package as File System (Package) and giving the configuration file with package
ADF looks for the configuration file and performs the action based on the content /variables that we given in the configuration file
Azure Data Factory -Run SSIS-IR using Self Hosted IR as proxy to access On premise SQL Database Demo
Here we are accessing Onpremise SQL Database Data using Self Hosted IR as a proxy, We are deploying the SSIS-IR in the Azure SQL Database.
Azure Data Factory - Lift & Shift SSIS -Execute SSIS package in ADF using Project deployment Model (SSISDB) by creating SSIS - Integration Runtime
In this video we are using Project Deployment model . We are deploying our Project into SSISDB which is being Hosted in Azure SQL Database Server.
We are executing the packages from Azure Data Factory which are hosted in the SSISDB.
Here we are deploying the SSISDB catalog into on premise local SQL server
Azure Data Factory - Send alert email for Activity or Pipeline Failure using Logic App
We can send a custom alert email for any activity or pipeline failure using Logic Apps from Azure Data Factory
Azure Data Factory - Invoke an Azure Databricks notebook from an Azure Data Factory Pipeline
We have a notebook which performs a transformation and generates a csv file.
We can execute that Azure Databricks notebook from Azure Data Factory using Databricks Activity.
Azure Data Factory - Incremental data copy Copy files that are Created / Modified today
This helps us to get files which are added or modified on Today's date
Similarly there are multiple approaches to get today's file
If you want only non-empty file and modified or created date with today,
please follow below
https://youtu.be/LOo9JC-HtLk
If you want Copy Today's files with date and timestamp in name of the file and copy based on the name, please follow below
https://youtu.be/u67xQ1u6NjU
Azure Data Factory - Copy files which are not empty and last modified is today to ADLS container
This approach helps us to get only files which are not empty
Expression is @greaterOrEquals(activity('Lookup1').output.count,2)
This checks file along with the header, Sometimes files can also contain header but it could be an empty file with no further rows. The above expressions evaluates row count from 2 or more and that will be treated as non empty file.
For True conditions,
Take a copy Activity,
Source is same dataset given to lookup activity
FileName as parameter, should have @item().name as value
Sink as dataset which points a folder
and copy behavior as Preserve hierarchy
For False conditions,
Just create a Wait activity
To access one premise file system, we need to setup a Self Hosted Integration runtime.
Default Azure runtime will have only scope up to Azure environment, to access files/resources outside azure we need to configure Self Hosted Integrated Runtime.
Here to stimulate an On premise environment we had a Windows 10 virtual machine and we installed Self Hosted IR in that
Using Lookup activity and forEach activity we can copy multiple SQL tables to multiple files dynamically taking their tablename as file name into Azure Datalake Storage.
Once Datalake storage is mounted. We can read the files present in the Datalake using spark dataframes.
We can also write the output to the files and save them into different folder with a specific name
Mounting an Azure Datalake using Oauth with Service principal and Azure Keyvault
This is the most secure way to access it
This prevents to store keys in the code which is insecure way to define in code.
Mounting Azure Datalake Storage to Azure Databricks
Mounting object storage to DBFS allows you to access objects in object storage as if they were on the local file system.
It is like we are accessing DataBricks File System (DBFS)
We can mount the file system using dbutils.fs.mount()
We can mount Azure Blob Storage either by using Account Key or By using SAS key
Widget commands helps to add parameters to your notebook. Sometimes values cannot be taken as hard coded values
We need a mechanism to take values from other Azure Services Dynamically for that we have Widget Commands.
They are:
text
dropdown
combobox
multiselect
Let us see all utilities with example
Azure KeyVault is a service is Azure where we can storage our Secrets, Certificates, Keys,.
Storing the Access keys and we are linking to Databricks using Secret Scope
http://<databricks_Instance_Link>#secrets/createScope
Azure DataBricks File System
Databricks File System (DBFS) is a distributed file system mounted into an Azure Databricks workspace and available on Azure Databricks clusters.
DBFS is an abstraction on top of scalable object storage
The default storage location in DBFS is known as the DBFS root.
/FileStore: Imported data files,
generated plots, and uploaded libraries
/databricks-datasets: Sample
public datasets.
/databricks-results: Files generated by downloading the full results of a query.
Below video contains example video with importing CSV file to DBFS and perform some transformation in it
Azure Data bricks
Azure data bricks is a platform which provides you computational resources and a integrated interface to write code to perform data transformation.
It prevents time to setup environments of Python, R, Scala and SQL. It provides all for us with 0 configuration.
It contains Workspace ,Cluster and Notebook to write your code.
1. Workspace is an environment provided to you by Azure Data bricks
2. Cluster is set of computation resources and configurations on which we can run
workloads
3. Notebook is a web-based interface to a document that contains a code,
Visualizations and narrative text
Below video have a simple way to create a dataframe in Azure databricks
DERIVED COLUMN Transformation
This helps to modify or generate a new column based on the condition we define. This can also helps to generate a new column based on the data of the existing columns
SORT Transformation
As name defines this helps to sort the data of the column based on the column we provide to it
Below video have the complete explanation of both with example
UNION Transformation
This helps to combine multiple streams of data. This is similar to UNION in SQL , but we can combine N number of streams
Data flows in Azure Data Factory
Data Flows helps to apply transformation logic with a code less graphical interface.
Its helps to build the logic with ease.
Below video shows with an example how to perform JOIN, SELECT, FILTER, AGGREGATE Transformations
Azure Data Factory - We can copy files in a folder incrementally that are Created or Modified today to another Folder in Azure Data Lake
Dynamic Data loading helps us to Load data to resources without having effort of giving hard coded names with concept of parameterize of values
In this video we will see to load data from different CSV files from different folders to different tables in Azure SQL Database We used Lookup and Foreach activity to achieve the dynamic data load and copy activity to copy the resourcesTumbling window triggers helps to execute a pipeline with an specified internal .
That interval is known as a window. Pipeline will be trigger based on give value of recurrence.
Schedule trigger helps to execute a pipeline at a particular scheduled time (Start Time,Recurrence, End Time). We can define an end date to schedule trigger.
Features:
a. You can specify a specific date, Month, Days in a week to run this trigger in a given duration.
b. These can be used to attach to multiple pipelines
c. Many triggers also can be attached to a single pipeline.
d. We cannot schedule for a past date (which doesn't make any sense)
Below is the video to demonstrate how it works
Storage event based trigger helps to execute the pipeline based on a particular event occurred in the given trigger
AVRO is a row-based storage format
PARQUET is a columnar-based storage format.
PARQUET is much better for analytical querying, i.e., reads and querying are much more efficient than writing.
Building a pipeline with Copy activity to Extract files from a Zipped folder from Azure Data Lake
