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

How to: Create ASP.NET MVC Authentication tables in an existing database

Hi All,

I am writing this blog post to show how to migrate or move the standard MVC authentication tables into your existing database in your asp.net web application.

Background
When you create a MVC web application in VS 2013, VS embed a sql server database in your project, and this project is being used as a source of asp.net membership. Since you will not use this database as an embedded file and you want to have it as part of your existing database in SQL Server instance. The file is created under App_Data folder. To see the .mdf file click on Show all files in the solution explorer.
You can see the contents of this database and all tables in the server explorer window.

How to create those tables in SQL server ?

First, you can delete the .mdf file in App_Data folder. Since we don't need any of these tables.Then, we need to update the default connection string in the web.config to point to our database.

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=SERVER\INSTANCENAME;Initial Catalog=DBNAME;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>


Third, Open Nuget Package Manager and write the following commands:

Enable-Migrations
Add-Migration Init
Update-Database

Check out your database, all ASP.NET membership tables with Prefix Asp have been create and then you can test it out by running your application and execute membership actions such as Signing up or Signing in to your application.

Created tables after running above commands:
AspNetRoles
AspNetUserClaims
AspNetUserLogins
AspNetUserRoles
AspNetUsers
__MigrationHistory


Hope this helps.

Thanks

-- ME

Import SSIS package error 0xC0010014 when importing SSIS package

Hi Folks,

I'm writing this blog post to share an interesting find while i was developing a SSIS package using VS 2012 with BI tools. Microsoft has release on March this year 2013 BI tools to VS 2012 so you don't need to use VS2010 shell while developing SSIS packages.

The download link for BI tools in VS 2012 is here:
http://msdn.microsoft.com/en-us/library/jj856966.aspx

After i developed my SSIS package and try to deploy it on the SQL Server 2008 R2. I was getting the following error message when importing the package, here is the screen shots:

 

If you are using the Management studio you will get this above error without details!, but to get the detailed error message use SSIS Execute Package Utility, you can launch it from SQL SERVER 2008 R2 --> Integration Services --> SSIS execute Package utility

.

If you read the actual error message it says:
Package Migration from Version 6 to Version 3 failed with error 0xC001700A

Basically the Version that i used to develop the SSIS is higher than the recognized version by the SQL Server 2008 R2. To fix this problem you need to change the package version from 6 to 3 and it works like a charm!

Follow the following steps:
1) Right clich on our dtsx file and select edit, Open the file in notepad.
2) Look for:

      DTS:Name="PackageFormatVersion">6

3) Change the number 6 for the version to 3 and save the file.
4) Select the File System File from the SSIS execute utility and you will not get any errors!
6) From the management studio you can import the SSIS with no errors.

Hope this helps.

[Update 05/09/2013] You will run through other issues if you used VS 2012 SQL Data tools against SQL Server 2008 R2, one of them is the sql job agent will fail to run the imported job due to the xml format mismatch while executing the package. So, it is recommended to develop any SSIS SQL2008 R2 components using the old BIDS VS 2008.


-ME
 

Calling Stored procedure from EF takes long time and times out!

Hi Folks,
 
I was going through an interesting performance issue when calling a stored procedure from my web application more than one time and the WCF was timing out because of the stored procedure execution takes long time to execute.
 
At the first, I have a parameterized stored procedure that takes different parameters. First time it executes as expected.
 
The second time, the EF calls the stored procedure and i don't get any results back to my application. i thought there is an issue in the Stored procedure but no because the stored procedure works as expected from the SQL management studio.
 
 
This means, there is something is going on between the EF bindings and getting results to mapped views/entities we have in the DBML file.
 
I was searching and trying to find any clue why this could happen ? I tried different things but it didn't work, until i read the following articles about performance issues in EF:
 
http://msdn.microsoft.com/en-us/library/cc853327.aspx
 
I realized that i don't have issues in terms of the SQL performance, but EF does have some overhead on executing any stored procedure and getting the results back to mapped view objects. Since this search stored procedure return list of objects to one of my entities, i want to know why does it take long time to execute and i don't get my results back ?!
 
I found the solution for my problem by doing 2 steps:
 
1) Since i don't want to track any loaded objects for my entities, i added the following merge option with No Tracking when i call my stored procedure.

C# Code:

this.ObjectContext.Search(EmpId,EmpName, DeptId,DeptName, System.Data.Objects.MergeOption.NoTracking).ToList();


2) Since SQL Server has a known performance issue called Paramter sniffing, which means the SQL Server engine allows to compile the plan for the stored procedure and reuse this plan for subsequent calls of the same stored procedure with different paramters! and this was my case, i pass long list of parameters but with different values. the first time executes as expected because the server chose to compile the plan with the results but the second time it re-uses the compiled plan which might not work well with the results that i want and this takes long time to get the expected results!
 
This is a known issue when your stored procedure inlcudes Like keyword in your queries and you use the input paramters directly to filter your results without using other temp paramters assigned from the input parameters.
 
What you need to do is to force SQL to compile the query each call and not to use a precompiled plan and this can be accomplished by using : RECOMPILE query hints along with your query. This is supported in SQL 2005 and above. I'm using SQL 08 R2 server.

Select * FROM myView
WHERE DeptName like '%' + @DeptName+ '%' 
OPTION (RECOMPILE); -- This enhance the performance query and fix the parameter sniffing issue for multiple calls stored procedure.

 After these 2 changes, I ran my web application, the application works as expected and no times out for the stored procedure that is being called twice!

Hope this helps.


Reference:
1) Table Hints in SQL Server:
http://msdn.microsoft.com/en-us/library/ms181714.aspx

2)  Paramter Sniffing in SQL Enginer:
http://blogs.technet.com/b/mdegre/archive/2012/03/19/what-is-parameter-sniffing.aspx