Microsoft Azure provides a number of data analytics related products and services. It allows users to tailor the solutions to meet different requirements, for example, architecture for modern data warehouse, advanced analytics with big data or real time analytics.

The following diagram shows a typical advanced analytics solution architecture on Azure.

image

The architecture has four main layers:

  • Ingest: Azure Data Factory is used for data ingestion. Azure Data Factory is a hybrid and serverless data integration (ETL) service which works with data wherever it lives, in the cloud or on-premises, with enterprise-grade security.
  • Store: Data can be stored in Azure storage products including File, Disk, Blob, Queue, Archive and Data Lake Storage. Azure Data Lake Storage is a massively scalable data lake storage optimized for
    Apache Spark and Hadoop analytics engines.
  • Prep & Train: In this layer, data can be cleaned and transformed using Azure Databricks. Databricks is an Apache Spark based analytics platform. Python, Scala, R and other language interpreters can be to analyse data. Spark machine learning libraries can also be used to create models.
  • Model & Serve: After data preparation and transformation, the insights or output database can be written into Azure storage products or databases. Cosmos DB is Microsoft's globally distributed multi-model database. Azure SQL Data Warehouse is a massively parallel processing (MPP) data warehouse designed for the cloud. And then data can be consumed via applications or analysis services. Data stored in Azure Data Lake Storage can be also used in SQL Data Warehouse through PolyBase features in SQL Server.

Objectives

In this page, I am going to demonstrate how to ingest data from on-premise data to cloud by using Data Factory and then transform data using HDInsights before loading data into Azure SQL Data Warehouse. Eventually the data is consumed by Power BI.

The following diagram shows the steps required to complete the whole exercise.

image

Prerequisites

  • Azure subscription
  • Azure storage account
  • Azure HDInsights Spark cluster
  • Azure SQL Data Warehouse instance (optional)
  • Power BI (optional)
  • Access to Azure portal
  • On-premise SQL Server instance or any other data sources

Detailed steps

Create Azure Data Factory

image

Once the resource is created, click Author & Monitor button to go to Azure Data Factory workspace. 

image

The workspace looks like the following:

image

Create new pipeline

Click ‘Create pipeline’ button to create a new pipeline.

Create an activity to copy data from on-premise to Cloud

Drag ‘Copy Data’ activity from Data Transformation category to create an activity named CopyDataFromSQLServerToAzureBlobStorage.

image

And then click Source tab to setup the source data.

image

Click ‘+ New button’ to create a new source.

image

Select SQL Server as Data Store and then click Finish button to go to the dataset UI to setup the properties for the dataset.

Name the dataset as Premise_Mecury_Db_context_BlogPost:

image

And then click Connection tab to setup the connection for the database.

image

Click the ‘+ New’ button to create a linked service.

And now follow step 6 to step 15 to setup the linked service in the following page:

https://docs.microsoft.com/en-us/azure/data-factory/tutorial-hybrid-copy-portal#create-a-pipeline

Once the setup is successful, you can select a table in your local SQL Server database.

For example, I am using context.BlogPosts as the source table:

image

You can further detect the schema too as the following screenshot shows:

image

Go back to the pipeline:

image

Now we can setup the Sink.

image

Click button ‘+ New’ to create a new dataset.

This time we use Azure Blob Storage as Data Store:

image

Click Finish button to continue to edit this dataset:

image

And then setup the Connection by creating a new linked service which connects to one of your existing storage. Use Parquet as file format.

You can also create a new storage account:

image

The following screenshot shows my settings for the linked service to Azure Blob Storage.

image

File path is also required for writing into blob:

image

Till now, we have completed the step to ingest data from on-premise SQL Server database into Cloud. The follow items are created in data factory:

  • Data pipeline: pipeline1
  • Dataset for on-premise data: OnPremise_Mecury_Db_context_BlogPost
  • Dataset for Azure Blob Storage: DA_DEMO_ABS

Renamed the pipeline1 as DA-DEMO-Pipeline and then click Publish ALL button to publish.

Create a HDInsight Spark activity to transform data

Now drag a Spark activity into the pipeline:

image

Now we need to configure the HDInsights cluster for this Spark activity.

image

For this tutorial, I am using a predefined HDInsight cluster and also linking the Azure Storage to it too.

And then setup the script using the following code:

from pyspark.sql import SparkSession,SQLContext

spark = SparkSession.builder \
      .appName("SparkonADF - Transform")\
      .enableHiveSupport()\
      .getOrCreate()

# import here so that we can sql functions
from pyspark.sql.functions import *

blobPath="wasb://demo@dademoabs.blob.core.windows.net/blogs_raw"
sqlContext = SQLContext(spark)
#read back parquet to DF
blogs = sqlContext.read.parquet(blobPath)
blogs.registerTempTable("blogs")

# transformation to add a new date column
blogs_new=sqlContext.sql("SELECT *, CAST(DatePublished AS DATE) AS DatePublished_DATE FROM blogs")

# register temp table
blogs_new.registerTempTable("blogs_new")

# save as new parquet files
blobPathNew="wasb://demo@dademoabs.blob.core.windows.net/blogs_transformed"
blogs_new.write.mode("overwrite").parquet(blobPathNew);


image

In my environment, the script file is named transform.py and is stored in sparktransform folder.

In YARN, we should be able to see the running application:

image

Create another Copy Data activity to write transformed data into Azure SQL Data Warehouse

You can now create another Copy Data activity to load transformed data into Azure SQL Data Warehouse (Sink).

For more details, please refer to https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse

As I have limited credit for my current subscription, I will skip this step.

Visualize the data in Power BI

Now we can visualize the data through Power BI. No matter the transformed data is stored in Azure Blob Storage or Azure SQL Data Warehouse, Power BI has connectors available to get data from them.

HDInsights Spark can also be used as source too if we save the transformed data as table in Hive.

image

For example, the following code snippet shows how to save data frame as table:

# Save as table
blogs_new.write.mode("overwrite").saveAsTable("blogs_transfomed")

In my case, I will use HDInsights Spark as source data for visualization.

The detailed steps about how to use HDInsights Spark as source are available here: https://docs.microsoft.com/en-us/power-bi/spark-on-hdinsight-with-direct-connect.

Input server:

image

Click OK button to continue.

And then input user name and password:

image

Click Connect button to continue.

The available Hive tables will show:

image]

In this case, select blogs_transformed as source.

Click Load button to load data into the workspace.

And then, we can visualize the data in Power BI:

image

Remember to enable the following option if creating running total measures.

image

Once it is done, the report can also be published into powerbi.com:

image

Once published, you can then view the report in the website:

image

Summary

With Azure Data Factory, it is very easy to move data between on-premises and cloud by using Copy Data activity; you can also use Spark, Databricks and ML activities to perform transformations or to create models.

The transformed data can then be stored in different storage products for different consumptions. 

info Last modified by Raymond at 2 years ago * This page is subject to Site terms.

More from Kontext

Entity Framework Core Code-First - Generate Covering Index with Columns Included

local_offer entity-framework local_offer asp.net core local_offer Azure local_offer C#

visibility 18
thumb_up 0
access_time 10 days ago

In SQL Server or some other relational databases, it is a very common requirement to create covering index with columns included in index pages beside the index key columns. With Entity Framework Core, you can also easily generate covering indexes using purely C# code. Scenario For ...

open_in_new ASP.NET Core

local_offer Azure local_offer kontext

visibility 9
thumb_up 0
access_time 3 months ago

In the past few months, Kontext's DNS server was not very stable as it went offline several times accidentally. When the DNS server is down, our domain (kontext.tech) cannot be parsed successfully to the IP address of Azure App Service (the service that hosts this website); thus it became unreach...

open_in_new Kontext Information

Modern Web Application - Azure Blob Storage for Uploaded Files

local_offer Azure local_offer asp.net core local_offer dotnetcore

visibility 297
thumb_up 0
access_time 3 months ago

With cloud platforms like Azure, we can totally separate user content storage from web application storage to decouple components from each other and to make the application easy to scale and deploy. This article provides detailed information with code snippets about how to use Azure server-less product Blob Storage and App Service to enable horizontally scalable web application for users to upload files (BLOBs).

open_in_new Azure

Azure SQL Database Automated Backup Strategy

local_offer Azure local_offer SQL Server

visibility 84
thumb_up 0
access_time 3 months ago

When designing the architecture of Kontext platform, Azure SQL Database is chosen as the storage for relational data. TDE and other advanced security features are always enabled to protect the database. Backup plans are also employed to ensure I can always restore the database for as point of tim...

open_in_new Azure

info About author

comment Comments (0)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

No comments yet.

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.


Learn more arrow_forward