Study notes for exam 70-475: Designing and Implementing Big Data Analytics Solutions

Hi All,

Today I passed the "Designing and Implementing Big Data Analytics Solutions" Microsoft exam.

I have been preparing for this exam (70-475) for a couple of months and I have been using Hadoop ecosystem tools and platforms for awhile.

I wanted to master building big data analytics solutions using HDInsight clusters using Hadoop ecosystem which contains: Storm, Spark, HBase, Hive and HDFS. I worked to cover any gap in understanding I had in Azure Data Lake, ML, Python & R programming and Azure Machine Learning.

This exam covers the following primarily four main technologies (from most covered to least):

1) Hadoop ecosystem: Working with HDFS, HBase, Hive, Storm, Spark and understanding Lambda Architecture. If you want to know more about Lambda Architecture, read my blog post explaining it here.

2) Azure Machine Learning: building/training models, predictive models, classification vs regression vs clustering, recommender algorithms. building custom models, Executing code in R and Python. Ingesting data from Azure Event Hub & transformation in Stream analytics.

3) Azure Data Lake: building pipeline, activities, linked services, move, transform and analyze data, working with storage options in Azure (blob vs block) & tools to transform data.

4) SQL Server and Azure SQL: Security in transit and at rest, SQL Data Warehouse. Working with R in Sql Server 2016/Azure SQL.


My study notes while preparing to pass this test:

1) To protect data at rest as well as querying in Azure SQL Database: Use "Always Encrypted" to make sure data in transit is encrypted. Use "Transparent Data Encryption" to make sure that data at rest is encrypted. Read more about TDE here. Read more about Always Encrypted feature here.

2) When running an Azure ML experiment and you are getting "Out of memory error" here is how to fix it:
   a) Increase the memory settings for the map and reduce operations in the import module.
   b) Use Hive query to limit the amount of data being processed in the import module.

3) The easiest way to manage Hadoop clusters in Azure is to assign every HDInsight cluster to a resource group and to apply tags to all related resources.

4) In Hadoop, When the data is row-based, self-describing with schema and provide compact binary data serialization: it is recommended to use Avro.

5) Which Hadoop cluster type for query and analysis batch jobs:
     a) Spark: A cluster for In-memory processing, interactive queries, and micro-batch stream processing.
     b) Storm: A real-time event processing.
     c) HBase: NoSQL data storage for big data systems.

6) Importing data using Pyhon in Azure ML tips:
    a) Missing values are converted into NA for processing. NA will be converted back to missing values when converted back to datasets.
    b) Azure Dataset are converted to data frames in Pandas. Pandas module is used to work with data in Python.
    c) Number names columns are not ignored. str() function is applied to those.
    d) Duplicate column names are not ignored. The duplicate column names are modified to make sure they have unique names.

7) The only platform that supports ACID transaction in Hadoop file storage options is Apache Orc.

8) You have three utilities you can use to move data from local storage to managed cluster blob storage. These tools are: Azure CLI, PowerShell & AzCopy.

9) How to improve Hive queries using static vs dynamic partitioning, read more here.

10) Understand when to use Filter based Feature Selection in Azure ML.

11) AzureML requires Python to store visualizations as PNG Files. To configure MatPlotLib in AzureML, you should configure it to use AGG backend for rendering and you should save charts as PNG files.

12) To detect potential SQL injection attempts on Azure SQL database in ADL cluster: Enable Threat Detection.

13) To create synthetic samples of dataset for classes that are under represented: use SMOTE module in AzureML.

14) D14 V2 Virtual Machines in Azure supports 100GB in memory processing.

15) You can add multiple contributors to AzureML workspace as users.

16) Understand the minimum requirements for each cluster type in HDInsight;
       a) At least 1 data node for Hadoop cluster type.
       b) At least 1 region server for HBase cluster type.
       c) Two Nimbus nodes for Storm cluster type.
       d) At least 1 worker role for Spark cluster type.

17) If you want to store a file with a file size is greater than 1 TB, you need to use Azure Data Lake Store.

18) In Azure Data Factory (ADF), you can train, score and publish experiments to AzureML using:
      a) AzureML Batch execution: to train and score.
      b) AzureML Update resource activity: to update AzureML web services.

19) In Azure Data Factory (ADF), A pipeline is used to configure several activities, including the sequence and timing activities in a pipeline can be managed as a unit.

20) Working with R models in SQL Server 2016/AzureSQL: read more here.

21) Apache Spark in HDInsight can read files from Azure blob storage (WASB) but not SQL Server.

22) Always Encrypted protects data in transit and at rest will be encrypted. Also this feature allows you to store encryption keys on premise.

23) Transparent Data encryption (TDE) : secure data at rest, it will not protect data in transit and the keys are stored in the cloud.


24) Distcp is a Hadoop tool to copy data to and from HDInsight clusters storage blob into Azure Data lake store.

25) Adlcopy: is a command line utility to copy data from azure blob storage into azure data lake storage account.

26) AzCopy: A tool to copy data from and to Azure blob storage.

27) While working with large binary files and you would like to optimize the speed of AzureML experiment, you can do the following:
      a) Developers should write data as block blob.
      b) The blob format should be in CSV or TSV.
      c) You should NOT turn off the cached results option.
      d) You can NOT filter data using SQL but R language.


28) SQL DB contributor role allows monitoring and auditing of SQL databases without granting permissions to modify security or audit policies.

29) To process data in HDInsight clusters in Azure Data Factory (ADF):
      a) Add a new item to the pipeline in the solution explorer.
      b) Select Hive Transformation.
      c) Construct JSON to process the cluster data in an activity.

30) Understanding Tumbling vs Hopping vs Sliding Windows in Azure Stream Analytics. (link)

Hope this helps you get ready to pass the test, and good luck everyone!
Let's get all certified ya'll data wranglers :-)


-- ME


References:
1) Microsot Exam 70-475 details, skills measured and more:
https://www.microsoft.com/en-us/learning/exam-70-475.aspx

Secure sensitive data with SQL Server on Azure

Hi All,

SQL Server 2016 has released a new feature that is called "Dynamic Data Masking" which allows to mask sensitive information to be masked on the fly without a change to a line of code in your application. The video covers the DDM technology, the need for it and how to implement it on SQL DB in Azure. This feature can be implemented either using SQL PaaS (Azure SQL DBs) or IaaS (SQL Server VMs) on Azure.

If you have for example Personal Identification Information (PII), Credit Card Numbers, or Social Security Numbers that you would like to secure from non-authorized view from viewing restricted information to comply with your security policies. DDM is the right feature for you with zero change line of code.

SQL Database Dynamic Data Masking (DDM) limits sensitive data exposure by masking it to non-privileged users. Dynamic data masking is in preview for Basic, Standard, and Premium service tiers in the V12 version of Azure SQL Database.

Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer. It's a policy-based security feature that hides the sensitive data in the result set of a query over designated database fields, while the data in the database is not changed.

In this video, I cover how to configure DDM feature in SQL Database on Azure using the new Azure portal. How to manage existing masks and how to test out this feature using SQL Server Management Studio (SSMS).

The video covers how to manage multiple users scenario to allow either masked or unmasked data on a SQL database in Azure.

Enjoy the video below.

Channel 9 video link


Enjoy!

Azure Data Platform presentation on DC Cloud Tour event

Hi All,

During Azure Cloud Tour in DC, I presented a session about Azure data platform services which includes wide range of services for developers, and architects.

When you build a cloud based solution, you need to have an understanding of the available options to store and secure your data. This presentation covers the following topics:

1) SQL Server Database on Azure as PaaS.
2) SQL Server Database security features such as: Row Level Security, Dynamic Data Masking and Transparent Data Encryption (TDE).
3) SQL Server VMs as IaaS vs PaaS offering.
4) DocumentDB as NoSql option on Azure.
5) Elastic Database Pool for SQL Servers on Azure.
6) Azure Search as enterprise scalable solution.
7) HDInsight Microsoft Hadoop cluster implementation available on Windows and Linux.
8) Data Warehousing as a service on Azure.
9) Data Lake: enterprise wide repository of your data in its original format.

Azure Data platform from Mostafa Elzoghbi

Hope this helps.

How to parse comma delimited string into IN Clause in SQL Server

Hi Folks,

Today i was trying to pass multi-value parameter into my select query in SQL Server. To accomplish this i stored the multi-value parameter in a comma delimited string. Then, i want to pass it in my IN clause of the select statement. here is an example to what i want to accomplish:

Delcare @myVar varchar(500)
Set @myVar='1,2,3,4,5,7'

Select * from Employee
where EmployeeId IN (@myVar)

You will get this error:
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value '1,2,3,4,5,7' to data type int.

It makes sense, because i have a varchar that holds all my values and i want to pass those in the IN clause that only accepts integer values!.

The solution for this problem is to parse this string into set of integers and pass it back to your query in your IN clause. For this reason, i have created a function that return a table of 1 column of type int. This will be passed back to the select statement i have mentioned above.

The function code to parse comma delimited string into set of integers:

-- =============================================
-- Author: Mostafa Elzoghbi
-- Create date: 12/10/2012
-- Description: Parse a string into set of numbers
-- =============================================
CREATE FUNCTION [dbo].[ParseCommaDelimitedString]

( @CommaSeparatedStr
nvarchar(1000) =NULL)
RETURNS @myTable TABLE ([Id] [int] NOT NULL)
AS
BEGIN
declare @pos int
declare @piece varchar(500)
-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@CommaSeparatedStr ),1) <> ','
set @CommaSeparatedStr = @CommaSeparatedStr + ','
set @pos = patindex('%,%' , @CommaSeparatedStr )
while @pos <> 0
begin
set @piece = left(@CommaSeparatedStr , @pos - 1)
-- You have a piece of data, so insert it, print it, do whatever you want to with it.
insert @myTable
select @piece

set @CommaSeparatedStr = stuff(@CommaSeparatedStr , 1, @pos, '')
set @pos = patindex('%,%' , @CommaSeparatedStr )
end
RETURN
END
-- =============================================

What you need after that to re-write your query as follows:

Select *
from Employee
where EmployeeId IN
( select * from ParseCommaDelimitedString(@myVar))

Now, you will be able to pass any comma delimited string, varchar,nvarchar to this function and it returns table of integer that you can set in the IN clause of you select statement or any other T-SQL statements you works with your logic.

Hope this helps.

--ME

Change Sql Azure DB Size

Hi All,

I was trying to change my business edition DB to web edition in SQL Azure and i didn't find a sample TSQL to run to shrink the DB file size from 20 GB (Business) to 1 GB (Web) Edition.

Here is the TSQL you want to run after deleting the unnecessary data in your DB and make sure its size under the required new DB size.



Alter database YOURDBNAME

 modify (          
           maxsize=1 GB,
           Edition='Web'
        )

Hope this helps.

Thoughts & practices in Developing Cloud based solutions in Azure

Hi All,

I'm working for a while in designing and developing cloud based solutions using windows azure platform. In addition, I love working in the cloud and the concept to focus on developing business requirements rather than the infrastructure preparation and servers setup. I like the concept of the configuration management since you can build your virtual network and the server configuration form an XML file and with few clicks your project is hosted and deployed.

In this post i will share some ideas and thoughts that you might face or have in your mind when you start developing cloud based solutions.

1) Project Types: Using VS 2010, you can build different types to be hosted in windows azure, You can build web sites, web application in addition to back end services. The websites & web applications are called WEB ROLES. and back end services are called WORKER ROLES.

2) Databases: You can deploy your existing database by creating scripts in SQL 08 R2 Management studio that can have schema+data,schema only,data only and connect to the cloud DB server and run those scripts. then you db is ready to be used. The management console in Azure gives you the connection string to connect to your db under SQL azure tab.

3) DB size: As we have till now, you have 2 editions for DB: Web or Business, Web edition allow you to create DB up to 5 GB. and Business Edition allows you to create DB up to 50 GB. since we pay as we use in the cloud, you might start your project with specific size and then you need to extend this. and this is a FAQ for most of developers that have created their dbs and need to extend the DB size in the cloud

To change your DB edition and size option, check out this link:

http://msdn.microsoft.com/en-us/library/ff394109.aspx

4) VM configuration: Every deployment in Azure is a VM in the data center. So, you can configure the VM size and all deployment configurations such as: RAM,Processor,Trust level,Virtual Network setup...etc on your project before deploy it in the cloud. You can do this by right click on the cloud service project and select properties option.

To know more about how to configure your cloud project in VS 2010, check out this link:

http://msdn.microsoft.com/en-us/library/ee405486.aspx

5) Make sure to change the custom dlls required property Copy Local = True in VS 2010. So you will have them in the deployment package.

6) Here is a general troubleshooting tips for hosted services:

http://msdn.microsoft.com/en-us/library/gg465402.aspx

Hope this helps.

Regards,Mostafa Arafa

twitter.com/mostafaelzoghbi

How to calculate SQL Azure Storage per client in your DB ?

Hi All,
If you have a cloud based solution hosted in Windows Azure and you are using SQL Azure Database as a backend database. You will need to think about how much storage each client who is going to use your solution will consume from DB storage you have in the cloud. This concept is essential since the concept behind Software As A Service aka (SaaS) is to pay as you go. So, the client pays only for the storage he takes in the cloud and this is a competitive advantage you should give when you sell cloud based solutions.

Since you have a backend database that has all your clients data, You need to give the storage that has been taken by each client in your database. To do this, I was thinking that if i can get the Database size in MB and the size of each table. I'd be able to calculate the storage for each client.

For example: If i have a table in my DB called Client, and set of tables Table1,Table2...etc.To get the total DB size in MB in SQL Azure, please check this command:

-- Calculates the size of the database.   

SELECT SUM(reserved_page_count)*8.0/1024  FROM sys.dm_db_partition_stats;   

GO

To get the size for each table, here is how to get the size of each table in SQL Azure:

-- Calculates the size of individual database objects.   

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024  

FROM sys.dm_db_partition_stats, sys.objects   

WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id   GROUP BY sys.objects.name; GO

So if i get the number of records in Table1 for client 1, So the storage that has been used in table1 by client 1 is equal to =Total Table Size * ( Client Records / Total Records )

Same will be calculated for all other tables.

The total amount of storage for client 1 is the summation Storage by client 1 for each table.

So you can show this amount in your interface under your system admin module and will be able to charge each customer on monthly basis for example.

Another question : What about the synchronization feature for cloud data in DBs ? You can either back up the database on periodic basis or do data sync in SQL Azure. Read more about SQL Azure Sync. framework:

http://archive.msdn.microsoft.com/sync/Release/ProjectReleases.aspx?ReleaseId=5037

Hope this Helps, Please post here any questions if something came across your mind ?

Thanks.

References:

- Monitoring SQL Azure Dynamic Management Views:

http://msdn.microsoft.com/en-us/library/ff394114.aspx

Regards,
Mostafa Arafa
twitter.com/mostafaelzoghbi