Interview Prep
Career
GenAI
Prompt Engg
ChatGPT
LLM
Langchain
RAG
AI Agents
Machine Learning
Deep Learning
GenAI Tools
LLMOps
Python
NLP
SQL
AIML Projects

Reading list

Machine Learning Basics for a Newbie
6 Steps of Machine learning Lifecycle Introduction to Predictive Modeling
Introduction to Exploratory Data Analysis & Data Insights Descriptive Statistics Inferential Statistics How to Understand Population Distributions?
Reading Data Files into Python Different Variable Datatypes
Probability for Data Science Basic Concepts of Probability Axioms of Probability Conditional Probability
Central Tendencies for Continuous Variables Spread of Data KDE plots for Continuous Variable Overview of Distribution for Continuous variables Normal Distribution Skewed Distribution Skeweness and Kurtosis Distribution for Continuous Variable
Central Tendencies for Categorical Variables Understanding Discrete Distributions Performing EDA on Categorical Variables
Dealing with Missing Values Understanding Outliers Identifying Outliers in Data Outlier Detection in Python Outliers Detection Using IQR, Z-score, LOF and DBSCAN
Sample and Population Central Limit Theorem Confidence Interval and Margin of Error
Bivariate Analysis Introduction
Covariance Pearson Correlation Spearman's Correlation & Kendall's Tau Correlation versus Causation Tabular and Graphical methods for Bivariate Analysis Performing Bivariate Analysis on Continuous-Continuous Variables
Tabular and Graphical methods for Continuous-Categorical Variables Introduction to Hypothesis Testing P-value Two sample Z-test T-test T-test vs Z-test Performing Bivariate Analysis on Continuous-Catagorical variables
Chi-Squares Test Bivariate Analysis on Categorical Categorical Variables
Multivariate Analysis A Comprehensive Guide to Data Exploration The Data Science behind IPL
Supervised Learning vs Unsupervised Learning Reinforcement Learning Generative and Descriminative Models Parametric and Non Parametric model
Machine Learning Pipeline Preparing Dataset Build a Benchmark Model: Regression Build a Benchmark Model: Classification
Evaluation Metrics for Machine Learning Everyone should know Confusion Matrix Accuracy Precision and Recall AUC-ROC Log Loss R2 and Adjusted R2
Dealing with Missing Values Replacing Missing Values Imputing Missing Values in Data Working with Categorical Variables Working with Outliers Preprocessing Data for Model Building
Understanding Cost Function Understanding Gradient Descent Math Behind Gradient Descent Assumptions of Linear Regression Implement Linear Regression from Scratch Train Linear Regression in Python Implementing Linear Regression in R Diagnosing Residual Plots in Linear Regression Models Generalized Linear Models Introduction to Logistic Regression Odds Ratio Implementing Logistic Regression from Scratch Introduction to Scikit-learn in Python Train Logistic Regression in python Multiclass using Logistic Regression How to use Multinomial and Ordinal Logistic Regression in R ? Challenges with Linear Regression Introduction to Regularisation Implementing Regularisation Ridge Regression Lasso Regression
Introduction to K Nearest Neighbours Determining the Right Value of K in KNN Implement KNN from Scratch Implement KNN in Python
Bias Variance Tradeoff Introduction to Overfitting and Underfitting Visualizing Overfitting and Underfitting Selecting the Right Model What is Validation? Hold-Out Validation Understanding K Fold Cross Validation
Introduction to Feature Selection Feature Selection Algorithms Missing Value Ratio Low Variance Filter High Correlation Filter Backward Feature Elimination Forward Feature Selection Implement Feature Selection in Python Implement Feature Selection in R
Introduction to Decision Tree Purity in Decision Tree Terminologies Related to Decision Tree How to Select Best Split Point in Decision Tree? Chi-Squares Information Gain Reduction in Variance Optimizing Performance of Decision Tree Train Decision Tree using Scikit Learn Pruning of Decision Trees
Introduction to Feature Engineering Feature Transformation Feature Scaling Feature Engineering Frequency Encoding Automated Feature Engineering: Feature Tools
Introduction to Naive Bayes Conditional Probability and Bayes Theorem Introduction to Bayesian Adjustment Rating: The Incredible Concept Behind Online Ratings! Working of Naive Bayes Math behind Naive Bayes Types of Naive Bayes Implementation of Naive Bayes
Understanding how to solve Multiclass and Multilabled Classification Problem Evaluation Metrics: Multi Class Classification
Introduction to Ensemble Techniques Basic Ensemble Techniques Implementing Basic Ensemble Techniques Finding Optimal Weights of Ensemble Learner using Neural Network Why Ensemble Models Work well?
Introduction to Stacking Implementing Stacking Variants of Stacking Implementing Variants of Stacking Introduction to Blending Bootstrap Sampling Introduction to Random Sampling Hyper-parameters of Random Forest Implementing Random Forest Out-of-Bag (OOB) Score in the Random Forest IPL Team Win Prediction Project Using Machine Learning Introduction to Boosting Gradient Boosting Algorithm Math behind GBM Implementing GBM in python Regularized Greedy Forests Extreme Gradient Boosting Implementing XGBM in python Tuning Hyperparameters of XGBoost in Python Implement XGBM in R/H2O Adaptive Boosting Implementing Adaptive Boosing LightGBM Implementing LightGBM in Python Catboost Implementing Catboost in Python
Different Hyperparameter Tuning methods Implementing Different Hyperparameter Tuning methods GridsearchCV RandomizedsearchCV Bayesian Optimization for Hyperparameter Tuning Hyperopt
Understanding SVM Algorithm SVM Kernels In-depth Intuition and Practical Implementation SVM Kernel Tricks Kernels and Hyperparameters in SVM Implementing SVM from Scratch in Python and R
Introduction to Principal Component Analysis Steps to Perform Principal Compound Analysis Computation of Covariance Matrix Finding Eigenvectors and Eigenvalues Implementing PCA in python Visualizing PCA A Brief Introduction to Linear Discriminant Analysis Introduction to Factor Analysis
Introduction to Clustering Applications of Clustering Evaluation Metrics for Clustering Understanding K-Means Implementation of K-Means in Python Implementation of K-Means in R Choosing Right Value for K Profiling Market Segments using K-Means Clustering Hierarchical Clustering Implementation of Hierarchial Clustering DBSCAN Defining Similarity between clusters Build Better and Accurate Clusters with Gaussian Mixture Models
Understand Basics of Recommendation Engine with Case Study
8 Ways to Improve Accuracy of Machine Learning Models
Introduction to Dask Working with CuML
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to AutoML Implementation of MLBox Introduction to PyCaret TPOT Auto-Sklearn EvalML
Pickle and Joblib Introduction to Model Deployment
Deploying Machine Learning Model using Streamlit Deploying ML Models in Docker Deploy Using Streamlit Deploy on Heroku Deploy Using Netlify Introduction to Amazon Sagemaker Setting up Amazon SageMaker Using SageMaker Endpoint to Generate Inference Deploy on Microsoft Azure Cloud Introduction to Flask for Model Deploying ML model using Flask
Model Deployment in Android Model Deployment in Iphone

From Blob Storage to SQL Database Using Azure Data Factory

chaitanya
chaitanya Last Updated : 04 May, 2022
8 min read

This article was published as a part of the Data Science Blogathon.

Introduction

Azure data factory (ADF) is a cloud-based ETL (Extract, Transform, Load) tool and data integration service which allows you to create a data-driven workflow. The data-driven workflow in ADF orchestrates and automates the data movement and data transformation. In this article, I’ll show you how to create a blob storage, SQL database, data factory in Azure and then build a pipeline to copy data from Blob Storage to SQL Database using copy activity.

Create Azure Blob Storage

Azure storage account provides highly available, massively scalable and secure storage for storing a variety of data objects such as blobs, files, queues and tables in the cloud. Azure storage account contains content which is used to store blobs. This azure blob storage is used to store massive amounts of unstructured data such as text, images, binary data, log files, etc.

For creating azure blob storage, you first need to create an Azure account and sign in to it. After signing into the Azure account follow the below steps:

Step 1: On the azure home page, click on Create a resource.

 

Step 2: Select Storage Account -> Create

 

Step 3: On the Basics page, select the subscription, create or select an existing resource group, provide the storage account name, select the region, performance, redundancy and click Next. I have selected LRS for saving costs.

Step 4: On the Advanced page, configure the security, blob storage and azure files settings as per your requirements and click Next. I have chosen the hot access tier so that I can access my data frequently.

Step 5: On the Networking page, configure network connectivity, and network routing and click Next.

Step 6: Click on Review + Create. After the storage account is created successfully, its home page is displayed. Now, select Data storage-> Containers.

Step 7: Click on + Container. Write new container name as employee and select public access level as Container. Click Create.

Step 8: Create a blob, launch excel, copy the following text and save it in a file named Emp.csv on your machine.

  FirstName     LastName     Department     Salary
  Rahul   Patel   Sales   90000
  Chaitanya   Shah   R&D   95000
  Ashna   Jain   HR   93000
  Mansi   Garg   Sales   81000
  Vipul   Gupta   HR   84000

Step 9: Upload the Emp.csv  file to the employee container.

Now, we have successfully uploaded data to blob storage. We will move forward to create Azure SQL database.

Create Azure SQL Database

Azure SQL Database is a massively scalable PaaS database engine. It provides high availability, scalability, backup and security. Azure SQL Database delivers good performance with different service tiers, compute sizes and various resource types. It also provides advanced monitoring and troubleshooting features to find real-time performance insights and issues. Azure SQL Database provides below three deployment models:

1. Single database: It is the simplest deployment method. In this approach, a single database is deployed to the Azure VM and managed by the SQL Database Server. Each database is isolated from the other and has its own guaranteed amount of memory, storage, and compute resources.

2. Elastic pool: Elastic pool is a collection of single databases that share a set of resources. This deployment model is cost-efficient as you can create a new database, or move the existing single databases into a resource pool to maximize the resource usage.

3. Managed instance: Managed Instance is a fully managed database instance. It helps to easily migrate on-premise SQL databases.

Follow the below steps to create Azure SQL database:

Step 1: On the azure home page, click on Create a resource.

 

Step 2: Select SQL Database -> Create

 

Step 3: On the Basics page, select the subscription, create or select an existing resource group, provide a database name, create or select an existing server, choose if you want to use the elastic pool or not, configure compute + storage details, select the redundancy and click Next. I have selected LRS for saving costs.

Step 4: On the Networking page, configure network connectivity, connection policy, encrypted connections and click Next.

Step 5: Click on Review + Create. After the Azure SQL database is created successfully, its home page is displayed. Now, select Query editor (preview) and sign in to your SQL server by providing the username and password.

Step 6: Paste the below SQL query in the query editor to create the table Employee.

CREATE TABLE dbo.Employee
(
ID int IDENTITY(1,1) NOT NULL,
FirstName varchar(50),
LastName varchar(50),
Department varchar(50),
Salary int
)
GO
CREATE CLUSTERED INDEX IX_emp_ID ON dbo.Employee (ID);

Note: Ensure that Allow Azure services and resources to access this Server option are turned on in your SQL Server.

Now, we have successfully created Employee table inside the Azure SQL database. We will move forward to create Azure data factory.

Create a Data Factory in Azure

Azure data factory (ADF) is a cloud-based ETL (Extract, Transform, Load) tool and data integration service. ADF is a cost-efficient and scalable fully managed serverless cloud data integration tool.

Follow the below steps to create a data factory:

Step 1: On the azure home page, click on Create a resource.

 

Step 2: Search for a data factory in the marketplace. Select Create -> Data Factory

 

Step 3: On the Basics page, select the subscription, create or select an existing resource group, provide the data factory name, select the region and data factory version and click Next.

Step 4: On the Git configuration page, either choose to configure git later or enter all the details related to the git repository and click Next.

Step 5: On the Networking page, fill manage virtual network and self-hosted integration connectivity to Azure Data Factory options according to your requirement and click Next.

Step 6: Click on Review + Create. After the data factory is created successfully, the data factory home page is displayed. Click on open in Open Azure Data Factory Studio.

 

 

Create Pipeline to Copy Data

Step 1: In Azure Data Factory Studio, Click New-> Pipeline. Rename the pipeline from the Properties section.

Step 2: In the Activities toolbox, search for Copy data activity and drag it to the pipeline designer surface. Rename it to CopyFromBlobToSQL.

 

Step 3: In Source tab, select +New to create the source dataset. Search for Azure Blob Storage.

Select Continue-> Data Format DelimitedText -> Continue

 

Enter your name, select the checkbox first row as a header, and click +New to create a new Linked Service.

In the new Linked Service, provide service name, select authentication type, azure subscription and storage account name. Click Create.

After the linked service is created, it navigates back to the Set properties page. Now, select Emp.csv path in the File path.

Click OK. In the Source tab, make sure that SourceBlobStorage is selected. To preview data, select Preview data option.

Step 4: In Sink tab, select +New to create a sink dataset. Search for Azure SQL Database.

Select Continue. Enter your name, and click +New to create a new Linked Service.

In the new Linked Service, provide service name, select azure subscription, server name, database name, authentication type and authentication details. Click Create.

After the linked service is created, it navigates back to the Set properties page. Now, select dbo.Employee in the Table name. Click OK.

Step 5: Validate the Pipeline by clicking on Validate All. After validation is successful, click Publish All to publish the pipeline.

Step 6: Run the pipeline manually by clicking trigger now.

Step 7: Verify that CopyPipeline runs successfully by visiting the Monitor section in Azure Data Factory Studio.

 

Conclusion

In this article, we have learned how to build a pipeline to copy data from Azure Blob Storage to Azure SQL Database using Azure Data Factory. Our focus area in this article was to learn how to create Azure blob storage, Azure SQL Database and data factory. We also gained knowledge about how to upload files in a blob and create tables in SQL Database. Most importantly, we learned how we can copy blob data to SQL using copy activity. Hopefully, you got a good understanding of creating the pipeline. Please let me know your queries in the comments section below.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion. 

chaitanya
chaitanya

Full Stack developer with 2.6 years of experience in developing applications using
Azure, SQL Server & Power BI. I love to read and write blogs. I am always willing to learn new technologies, easily adaptable to changes, and have a good time
management, problem-solving, logical and communication skills

Free Courses

Generative AI 4.7

Generative AI - A Way of Life

Explore Generative AI for beginners: create text and images, use top AI tools, learn practical skills, and ethics.
Large Language Models 4.5

Getting Started with Large Language Models

Master Large Language Models (LLMs) with this course, offering clear guidance in NLP and model training made simple.
Prompt Engineering 4.6

Building LLM Applications using Prompt Engineering

This free course guides you on building LLM apps, mastering prompt engineering, and developing chatbots with enterprise data.
LlamaIndex 4.8

Improving Real World RAG Systems: Key Challenges & Practical Solutions

Explore practical solutions, advanced retrieval strategies, and agentic RAG systems to improve context, relevance, and accuracy in AI-driven applications.
RAG systems using LlamaIndex 4.7

Microsoft Excel: Formulas & Functions

Master MS Excel for data analysis with key formulas, functions, and LookUp tools in this comprehensive course.

Recommended Articles

Responses From Readers

Clear

softech
softech

Azure is a nice and useful concept. Thanks author for sharing. Keep it up.

123

Write for us

Write, captivate, and earn accolades and rewards for your work
  • Reach a Global Audience
  • Get Expert Feedback
  • Build Your Brand & Audience
  • Cash In on Your Knowledge
  • Join a Thriving Community
  • Level Up Your Data Science Game
imag

We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.

Show details