This site uses cookies to deliver our services. By using this site, you acknowledge that you have read and understand our Cookie and Privacy policy. Your use of Kontext website is subject to this policy. Allow Cookies and Dismiss

Advanced analytics on big data with Azure - Tutorial

186 views 0 comments last modified about 3 months ago Raymond Tang

Azure power-bi

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. 

Related pages

Use Google Cloud BigQuery as Data Source in Power BI

313 views   0 comments last modified about 4 months ago

BigQuery is Google’s serverless data warehouse in Google Cloud. Power BI can consume data from various sources including RDBMS, NoSQL, Could, Services, etc. It is also easy to get data from BigQuery in Power BI. In this article, I am going to demonstrate how to connect to BigQuery to create...

View detail

Data Analysis Expressions to Create Static Tables in PowerBI

86 views   0 comments last modified about 5 months ago

DATATABLE StaticTable1 = DATATABLE("IntCol",INTEGER,"StringCol",STRING,{{1,"User1"},{2,"User2"}}) The above expression generates a table with two columns IntCol and StringCol : ...

View detail

Power Analytics with Power BI and Google Analytics

191 views   0 comments last modified about 5 months ago

Power BI is my favourite BI and visualization tool as it is very simple yet powerful. It doesn’t only support traditional data sources like databases, CSV, JSON, XML and etc., but also supports emerging sources that are available in HDFS, Spark, R, Salesforce, Google Analytics and cloud platforms...

View detail

Power BI Analytics - Connect to DBMS and Card

63 views   0 comments last modified about 5 months ago

Power BI supports connecting to most of the DBMS databases such as SQL Server, Oracle, Teradata, MySQL, DB2, Sybase, Snowflake, Google BigQuery, Impala and etc. This page summarizes the steps to connect to SQL Azure and to create the following part of the sample dashboard of this series: ...

View detail

Add comment

Please login first to add comments.  Log in New user?  Register

Comments (0)

No comments yet.