Hadoop from scratch notes: Preparing a minimal CentOs Linux Hyper-V image

Motivation

Hadoop on virtual machines? These posts are describing how to setup a hadoop homelab, to get in touch with hadoop. Yet, replace 'virtual' with 'dedicated physical', then you should by on your way to build a production cluster.

Hadoop is yet another good tool in the toolbox, when working with data. Now a days Hadoop is available as cloud service, but it can be pretty expensive, and specially if you just want to train and play with Hadoop. Some vendors as Cloudera offers a single node 'play' version of Hadoop, which is a great way to start. Yet the reason I'mt writing these notes, was that I did find Cloudera very closed and slow, and also I had to use any other Virtual Machine system than Hyper-V. Also it is not that hard to set up a Hadoop node or cluster from scratch.

Not that, I have anything against e.g. Virtual Box. Even thou I see all OS'es as my play grounds, I'm in a Microsoft period(due to my current work), and thereby my Windows box is best suited for virtualization. And it does already comes with Hyper-V, and it actually works good in Windows 10(Earlier versions did lock the CPU clock cycle, and thereby disabled speed-step). I like my machines light, so I would hate to have more than one system for virtualization.

What is the goal?

The goal is to prepare a virtual machine with a minimal version of Centos Linux. The reason, I have selected Centos OS is, it is supported by Microsoft, and it is Azure certified, and when it is Azure certified, it means it can work better with Hyper-V through Hyper-V Integration Services. I could have chosen Ubuntu (Azure's Hadoop cloud solution runs on Ubuntu), but I had a challenge with a very slow apt-get, and generally did find CentOS more light weight.

When we have a fully configured virtual machine, with CentOS and Hadoop, we are going to use it as a template, for creating more Hadoop nodes.

I prefer to setup Hyper-V with PowerShell, it is good fun and practice, and it more compact than images of the GUI. If you are familiar with the Hyper-V GUI, then you should have no trouble to figure out what to press.

Before we start, make sure Hyper-V is enabled, and get CentOS from here https://www.centos.org/download/, the minimal ISO should be sufficient(CentOS 7 is currently the latest version).

A virtual switch

If you don't have a virtual switch configured in Hyper-V, you have to configure one. You are going to use it for connecting you Hadoop nodes, the internet and you working machine together. Thou the internet is optionally. Creating a so-called external virtual switch called "Virtual Switch" (Yes, I know, the creative name is striking :-) ), is done by typing following PowerShell:

New-VMSwitch -Name "Virtual Switch" -NetAdapterName "Wi-Fi" -AllowManagementOS 1

As NetAdaptorName use "Wi-Fi" or "Ethernet", depending on which NIC provides internet.

The virtual machine and disk

Often the virtual machine and the disk interpreted as one, but,  a virtual machine consist of the "Machine" and "disk image" with the OS, and further, of some data disks". We are going to create the machine and OS disk in one go. 

New-VM -Name "Hadoop01" -MemoryStartupBytes 4GB -NewVHDPath D:\VMs\Hadoop01.vhdx -NewVHDSizeBytes 10GB -SwitchName "Virtual Switch"

Memory (4 GigaBytes) and disk (10 GigaBytes) sizes are dymanic by default, but the machine is only configured with 1 CPU. It can be upgraded with:

Set-VMProcessor -VMName Hadoop01 -Count 2

Make the virtual DVD point to the download CentOS image:

Set-VMDvdDrive -VMName Hadoop01 -Path D:\Downloads\CentOS-7-x86_64-Minimal-1511.iso

Let's go:

Start-VM Hadoop01

You have to connect to the virtual machine by the Hyper-V GUI-

Installing CentOS

Press Enter. I might take a while, before reaching next step

Select your preferred language

Check that the properties circled with yellow, are correct. That will make thing easier for you in generel. The properties circled with red, are critical, so make sure to read below how to set them.

Press 'Done', that is all

Turn on the network. Failing to do this, can require you to turn it on, after every reboot.

Set the root password. Create a user for good practice.

After installation and reboot. Log in, so we can get the IP address of our new machine, by typing the following command(ifconfig is not available on CentOS minimal)

ip addr

 Note the IP address, it can be found under Eth0: 

We are not going to use the Hyper-V viewer further. It can't copy-paste between guest and host, and the proper way to connect to a Linux/Unix server is via a SSH client. I recommend Putty (http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html), but the Git Bash is just as fine.

Type in the IP and press Open.

If using the Git Bash, you can write:

ssh <ip> -l <user>

Where <IP> is the noted IP and <user> is either root or the user created earliere.

Installing/Upgrading Microsoft Linux Integration Services(LIS)

We don't have much in the CentOS minimal, and Microsoft haven't made it easy to download the LIS package without a browser.

Fortunately, it is GNU licensed, so I have made a script to get it from my GitHub account, and to install it, together with wget.

curl -O https://raw.githubusercontent.com/ChristianHenrikReich/automation-scripts/master/centos-minimal/install-hyperv-essentials.sh

chmod 755 install-hyperv-essentials.sh

sudo ./install-hyperv-essentials.sh

When the script is done. The Virtual machine is fully Hyper-V prep'ed and ready to go. And can be used to other things than Hadoop also.

Next: How to install Hadoop on the image

http://programminghave.blogspot.com/2016/03/hadoop-from-scratch-notes-installing.html

SSIS: An easy SCD optimization for dev and prod

The value of reading this post, depends on how you work with SSIS and how database nursing are handled in within your organization.

The optimization is a single index, but if you only nurse indexes in prod, you could waste a great time when developing SCDs in SSIS. The method is simple, when you now the nature of your SCD, then you can create an index right away, and reduce your development waiting time. Specially if you are testing with bigger volumes of data.

Let me show you

Let's say you have following table definitions, and you working in a SSIS project using Visual Studio:

-- Staging

CREATE TABLE Staging.Customers

(

CustomerId UNIQUEIDENTIFIER,

FistName NVARCHAR(200),

MiddleInitials NVARCHAR(200),

LastName NVARCHAR(200),

AccountId INT,

CreationDate DATETIME2

)

GO 

-- Dimension

CREATE TABLE dbo.dimCustomers

(

CustomerDwhKey INT IDENTITY(1,1),

[Current] BIT, 

CustomerId UNIQUEIDENTIFIER,

FistName NVARCHAR(200),

MiddleInitials NVARCHAR(200),

LastName NVARCHAR(200),

AccountId INT,

CreationDate DATETIME2

CONSTRAINT PK_CustomerId PRIMARY KEY CLUSTERED (CustomerDwhKey)

)

GO

You have a Data Flow, where you transfer data from Staging.Customers to the dimension dbo.dimCustomers using the built-in component Slowly Changing Dimension:

In our example setup CustomerId will be a so-called Business key, And Current will be the indicator for which row are current. It should also be noted, it is possible to have more than one Business keys.

We'll configure attributes as:

Now, the Slowly Changing Dimension component work in following way:

For each entity it recieves, it will search the dimension table for an entity with the same business keys(in plural!!!) and is flagged as current, or in plain SQL:

SELECT  attribute[, attribute] FROM dimension_table WHERE current_flag = true AND business_key = input_business_key[, business_key = input_business_key]

Or as it will look like in our example

SELECT AccountId, CreationDate, FirstName, MiddleInititals, LastName FROM dbo.dimCustomers WHERE [Current] = 1 AND CustomerId = some_key

Further, in case we have an historical change, the current entity in the dimension must be expired by setting Current = 0.

UPDATE dbo.dimCustomers SET [Current] = 0 WHERE [Current] = 1 AND CustomerId = some_key 

The solution

As you might have realize by now, we can improve performance tremendously by putting an index on the current flag and the business keys(again plural!!!). For each entity passing through the Slowly Changing Dimension component, the will be at least 1, but likely 2 searches in the dimension table. And the by knowing the business keys and current flag. and the nature of the Slowly Changing Dimension component, you can predict the index which will improve performance.

The index for our sample will be

CREATE NONCLUSTERED INDEX IX_Current_CustomerId ON dbo.dimCustomers

(

[Current],

CustomerId --- Remember to include each business key

)

Should index be a filtered? I'll let that be up to you.

Indexes, bulk and loading of dimensions

Some tend to drop indexes when loading dimenson, with the argument: Bulk loading is fastest without indexes, which SQL Server has to maintain while loading. This argument has to be revised when working with the Slowly Changing Dimension component.

Because the component searches the dimensions so heavily, (in general) it will be faster loading with indexes than without. If there is no indexes, each entity going through the component, will require at least one table scan, which is quite expensive, and gets more expensive as your dimension grows. 

That's all

SQL Server 2014: SQL Server Data Tools(SSDT) and Visual Studio 2013 Challenges in Training Kit 70-463 and in general

Level: 2 where 1 is noob and 5 is totally awesome
System: SQL Server 2014

Notice: I'm using the SQL Server 2014 Developer edition, so if you are using a different edition, things might be different.

Some History

I'm currently studying for the exam 70-463 Implementing a Data Warehouse with Microsoft SQL Server 2012, and I'm using the 70-463 training kit for the exam. I have decided to use SQL Server 2014 for the practical training(regarding to SSDT, I now know it is the best to use SQL Server 2014). It has just been released and regarding to exam 70-463 there is no change. People who took the exam for SQL Server 2012 is also certified for SQL Server 2014. For more see FAQ here at http://www.microsoft.com/learning/en-us/sql-certification.aspx.

When using the training kit, you sooner or later realize, it has become a bit outdated. Which is quite natural, because it was published 25. Dec. 2012, and lot have happened since. There has been new releases and updates of Visual Studio, and we even have a new version of SQL Server. Which, all in all is nice.

The first sign of you might be challenged when using the training kit with SQL Server 2014, is in the beginning. The exam kit provides a list of features you have to install, and last on this list is SQL Server Data Tools, also known as SSDT. As you might discover, this feature is not shipped with SQL Server 2014, and it is the most important component for SSIS and the exam 70-463.

SSDT is a Visual Studio plugin, for developing SQL Server Integration Service(SSIS) packages. SSDT is a replacement from Business Intelligence Development Studio, also known as BIDS.Among things, SSDT delivers SSIS packages templates to Visual Studio. SSIS 2012 depends on Visual Studio 2010, Like SSIS 2014 does, but SQL Server 2012 installs the Visual Studio 2010 IDE if none is present. SQL Server 2014 has failed to do so, everytime I have tried to install it. If there is no SSDT in the SQL Server 2014 installation, it might make sense. Also it is okay, because I prefers to use Visual Studio 2013, and if you are a developer like me, I guess you prefer the same.

And Now the Tricky Part

I discovered Microsoft had developed SSDT (SSDT-BI which it is called now) for Visual Studio 2012, it the most easy package to find, and I thought it was the latest. I installed it, and was hit hard in chapter 3 of the training kit 70-463. Because I has installed SSDT-BI 2012 for Visual Studio 2012.

The problem was I have created a SSIS package with the SQL Server Import and Export tool, the version which comes with SQL Server 2014. It stores it SSIS Packages in the SSIS Package format version 8, while the SSDT 2012 stores in the SSIS Package format version 6. The SSIS Package format is apparently not forward compatible. 

The pain I experienced, in chapter 3 of the training kit, was I had to create a package with the SQL Server Import and Export tool (version 2014 in my case) and add it to a SSIS Project created with SSDT 2012. The because the version numbers didn't match, result was this:

Fortunately I succeeded to find SSDT-BI 2014 well hidden at 

http://blogs.msdn.com/b/analysisservices/archive/2014/04/02/sql-server-data-tools-business-intelligence-for-visual-studio-2013-ssdt-bi.aspx

It saves SSIS Packages in SSIS Package format version 8, like the rest of the SQL Server 2014 Suite, plus it works with Visual Studio 2013. I'll expect it will work it with the rest of the training kit 70-463, or else I'll provide a solution on this blog. As bonus info, you have to select "Perform a new installation of SQL Server 2014", or you will get some kind of architecture error. Despite the option name, it only installs SSTD-BI 2014.

Enjoy