New Journal Article: First article of 2, where you will be able to see how you can use SQL Server 2012′s DQS to solve common data quality problems. http://bit.ly/172Kh5L
Topics covered:
- Data standardization
- Identifying and correcting unrealistic or invalid values
- Validation and correcting records using Regular Expressions
Read Here: How to solve common Data Quality Problems using Data Quality Services (Part 1)
Recapping my social media activities during Jan 1 – Feb 20 2013:
That’s about it for this post.
If we want to read related past posts, here they are:
This is a Quick Post, Just want to share a command to upload local data to HDFS using Hadoop Command Line.
The command looks like:
> hadoop fs -copyFromLocal input.txt input/SqrtJob/input.txt
Download Link Here:
SQL Saturday 185 (Trinidad): Why Big Data Matters? by Paras Doshi
(if you need the .ppt version of this talk, please contact me via http://parasdoshi.com/contact/)
Here are the seven Demo’s that I had posted over the past few weeks, listing them here:
Detecting invalid values in Price domain or unrealistic values in Height domain
How to standardize data using DQS
How to clean records using third-party reference data-sets from Azure Data Market
How to use regular expressions to validate records?
cleaning records like “my company Inc.” and “my company incorporated” using Term Based Relations
In this Post, we’ll see how I (as an Administrator) created a Master Data Services Entity via MDS add-in for Excel:
1. Created a Model via MDS Web Application. I named it “customer Information”
2. Switch to Excel
3. Open the File that has the Data that you want to load to MDS
4. Switch to Master Data tab in Excel
[Resource: Steps I followed to Install SQL Server 2012 Master Data Services (MDS) on my Demo Machine: And then Installed the Excel Add-in]
5. Connect to MDS server (via Excel add-in)
6. Select Model as CustomerInformation
7. Under Build Model, select Create Entity
8. Configure the values in the “Create Entity” > click OK
9. Switch to MDS web application to see the new entity:
I hope this helps! Your comments are very welcome!
To view error(s) that occur during MDS’s staging process, we have two views: stg.viw_name_MemberErrorDetails & stg.viw_name_RelationshipErrorDetails. For the purpose of this blog post, let me show you how I (as an administrator) view information from stg.viw_name_MemberErrorDetails.
1) Name of the entity: supplier & Name of model is Suppliers
2) I imported data into staging table stg.supplier_leaf via SSIS
3) Now here’s how you can see the errors that occur during MDS’s staging process:
we can go to MDS web application to initiate the staging process > start the batch > after completion you can see the status as well as if it has any errors or not:
4) Now if you see that there’s an error, then you can go to stg.viw_name_MemberErrorDetails to see what are the errors. In my case, I am going to run the query:
select * from stg.viw_supplier_MemberErrorDetails where Batch_ID=2
You can get the above query via MDS web application too:
5) And as you can imagine, you can get access to this error data via SSIS (SQL Server Integration services) too. So if you have a workflow that a. Loads data to MDS and b. initiates the Batch process via Stored Procedure; then you can program it to get access to errors from the stg.viw_name_MemberErrorDetails & stg.viw_name_RelationshipErrorDetails tables.
That’s about it for this post! I hope this helps. Your comments are very welcome!
Problem Statement: Find Maximum Temperature for a city from the Input data.
File 1:
New-york, 25
Seattle, 21
New-york, 28
Dallas, 35
File 2:
New-york, 20
Seattle, 21
Seattle, 22
Dallas, 23
File 3:
New-york, 31
Seattle, 33
Dallas, 30
Dallas, 19
Let’s say Map1, Map2 & Map3 run on File1, File2 & File3 in parallel, Here is their output:
(Note how it outputs the “Key – Value” pair. The key would be used by the reduce function later to do a “group by“)
Map 1:
Seattle, 21
New-york, 28
Dallas, 35
Map 2:
New-york, 20
Seattle, 22
Dallas, 23
Map 3:
New-york, 31
Seattle, 33
Dallas, 30
Reduce Function takes the input from Map1, Map2 & Map3, to give an output:
New-york, 31
Seattle, 33
Dallas, 35
Conclusion:
In this post, we visualized MapReduce Programming Model with an example: Finding Max Temp. for a city. And as you can imagine you can extend this post, to visualize:
1) Find Minimum Temperature for a city.
2) In this post, the key was City, But you could substitute it by other relevant real world entity to solve similar looking problems.
I hope this helps.
Related Articles:
In this post, we’ll see how to use Sqoop to load data into HDFS from SQL Server?
With that, here are the steps:
1. You have the Microsoft® HDInsight Preview for Windows Installed on your machine. Here’s a tutorial: Installing HDInsight (Microsoft’s Hadoop) on windows 7
2. Make sure that the Cluster is up & running! To check this, I click on the “Microsoft HDInsight Dashboard” or open http://localhost:8085/ on my machine
Did you get any “wait for cluster to start..” message? No? Great! Hopefully, all your services are working perfectly and you are good to go now!
3. Before we begin, decide on three things:
3a: Username and Password that Sqoop would use to login to the SQL Server database. If you create a new username and pasword, test it via SSMS before you proceed.
3b. select the table that you want to load into HDFS
In my case, it’s this table:
3c: The target directory in HDFS. in my case I want it to be /user/data/sqoopstudent1
You can create by command: hadoop fs -mkdir /user/data/sqoopstudent1
[to learn about how to create directory, read: How to create a directory in Hadoop File System? ]
4. Now Let’s start the Hadoop Command Line (can you see the Icon on the Desktop? Yes? Great! Open that!)
5. Navigate to: c:\Hadoop\sqoop-1.4.2\bin>
*This path may change in future, but navigate to the bin folder under the SQOOP_HOME.
6. Run dir command to see various files under this directory.
Also you can run sqoop help for more information on the command that we are about to run.
7. Now here’s the command to Load data from SQL Server to HDFS:
c:\Hadoop\sqoop-1.4.2\bin>sqoop import –connect “jdbc:sqlserver://localhost;dat
abase=UniversityDB;username=sqoop;password=**********” –table student –tar
get-dir /user/data/sqoopstudent1 -m 1
8. After successfully running the above command, let’s browse the file in HDFS!
That’s about it for this post!
Thanks Aviad Ezra who answered my question on this MSDN thread: An error while trying to use Sqoop on HDInsight to import data from SQL server to HDFS
In this post, we saw how to load data into Hadoop from SQL Server using Sqoop (SQL Hadoop)
Related Articles:
In this post, we’ll see matching activity in action. For the demo purpose, I’ll be using Data-Sets that I’ve found via Movies Sample & EIM tutorial .
we’ve a list of movies and we want to identify “matching” movie titles.
Create a Matching Policy
1) Mapping the Domains:
2) Configuring the Matching Policy:
Note: You can have Matching Rules on more than one domain. I used one domain for demo purposes.
3) View Matching results:
4) Publish the KB (which stores the matching policy)
Once you have the matching policy, you can use this in a Data Quality Project:
5) See How I ran a Data Quality Project (w/ matching activity) in the image below.
Note: You can export the clean data-set via Data Quality Project.
we’ve a list of Supplier Names and we want to identify “matching” supplier names.
Note that in this situation, you would see how to use more than one domain to create a matching rule.
Most of the steps would remain same as situation 1, But I want to show you Matching Policy & Matching Results
Matching Policy:
Matching results:
Also, I want to show that, the matching policy (DQS KB) that we created earlier can be used in Master Data Services too! For more details check out the resource: DQS, MDS & SSIS for EIM
In this post, we saw how DQS can be used to clean “matching” records. For step by step tutorial, please refer to Movies Sample & EIM tutorial .
