Setting up your own Hadoop Cluster using Azure HDInsights

How to get started with Hadoop and Hive

Install prerequisites to manage your cluster

Prereq	Link
Install .net4.5	http://www.microsoft.com/en-gb/download/details.aspx?id=30653
Install powershell 4	http://www.microsoft.com/en-gb/download/details.aspx?id=40855

Log into Windows Azure account

Sign up using http://azure.microsoft.com/en-us/  free trail link

Then click the portal link to manage your Azure services. You should end up with something like this menu on the side

Create a new Storage account

1. Click on the storage link in the Azure left side menu
2. Then click the new link at the bottom. This will prompt you with the below options to create a new storage account. 
3. Choose a unique name for your URL. If the tick box turns green it means your account name is unique
   
4. Choose create storage account at the bottom
5. This will then start creating your storage account, you may need to wait 5 mins for it to complete

Create new HDInsights cluster

1. Click on the HDInsight link on the Azure left side menu
2. Then click the new link at the bottom. This will prompt you with the options below to create a new Hadoop cluster
3. Choose a unique name for your URL
4. Choose 1 data node for the cluster size (unless you want to go crazy then be my guest)
5. Select the storage you created in the above section
   
6. Click Create HDInsight Cluster. This takes a while, especially first time. Between 5min-40min 

Connecting to your Cluster

1. When you click All Items in the top left menu, you should see something like this. Confirm your HDSight Cluster is running
   
2. Open Powershell ISE
3. Run the following

   Get-AzureSubscription Get-AzureHDInsightCluster

4. Download the publish settings file to your local computer and keep note of the path
5. Click on your HDInsight cluster Right arrow
   
6. Then choose Dashboard
   
7. Take note of your subscription name and your cluster name
   

Running Hive Queries against your Cluster

1. Run a new script in powershell and replace configurations where nessasary

   Import-AzurePublishSettingsFile "<FULL_PATH_TO_PUBLISH_SETTINGS_FILE>" $subscriptionName = "<SUBSCRIPTION_NAME>" $clusterName = "<CLUSTER_NAME>"             $querystring = "select country, state, count(*) as records from hivesampletable group by country, state order by records desc limit 5" Select-AzureSubscription -SubscriptionName $subscriptionName Use-AzureHDInsightCluster $clusterName Invoke-Hive -Query $queryString

   
   Here is an example i have used

   Import-AzurePublishSettingsFile "C:\Powershell\Hadoop\jeremyking77Azure.publishsettings" $subscriptionName = "Visual Studio Professional with MSDN" $clusterName = "jeremyking77"             $querystring = "select country, state, count(*) as records from hivesampletable group by country, state order by records desc limit 5" Select-AzureSubscription -SubscriptionName $subscriptionName Use-AzureHDInsightCluster $clusterName Invoke-Hive -Query $queryString

2. You should get output like the following

   Successfully connected to cluster jeremyking77 Submitting Hive query.. Started Hive query with jobDetails Id : job_1405933745625_0003 Hive query completed Successfully United States   California  6881 United States   Texas   6539 United States   Illinois    5120 United States   Georgia 4801 United States   Massachusetts   4450

Find Diacritics (accent) marks in SQL Server

We had some issues where a downstream system was using usernames with diacritics in the URL. This was breaking the downstream system.
The following code may help you find diacritics in your data

/*
 Example script for how to find diacritical marks/accents in data
*/

/* Create some example data with and without accents */
DECLARE @exampledata TABLE (exampleString VARCHAR(50)) --has to be varchar
INSERT INTO @exampledata ( exampleString )
VALUES  (N'Peter'),(N'AURÉLIEN'),(N'JEREMY'),(N'kroužek'),(N'tomato'),(N'voël')

/*  Temp table for accent insensitive 
 Load with Accent insensitive collation */
DECLARE @insensitive TABLE (exampleString VARCHAR(50))
INSERT INTO @insensitive ( exampleString )
SELECT exampleString Collate SQL_Latin1_General_CP1253_CI_AI FROM @exampledata

/* Check for accent sensitive items */
SELECT  *
FROM    @exampledata s
WHERE   NOT EXISTS ( SELECT 1
                     FROM   @insensitive i
                     WHERE  i.exampleString = s.exampleString )
go

Create a table based on a temporary table schema

First of all, this is not really a good practice to do in your production environment but this is a script i created which allows you to create a composite table from a temporary table

This has quite a few limitations but generally gives you the basic structure of the table you want without keys, indexes, null contraints etc
This has been created to work with SQL Server 2012, if you need to make it work for older versions you will need to update the CONCAT function to use the old string concatenation
Replace your table names with the ones in the script

Heres the script

/*
 Intialise variables and set table names you want to create a table from
*/
DECLARE @columns VARCHAR(max)
DECLARE @createsql nVARCHAR(max)
DECLARE @tempTableName sysname
DECLARE @newTableName sysname
DECLARE @columnIterator int
DECLARE @columnName sysname
DECLARE @columnType VARCHAR(13)

SET @tempTableName = '#temp'
SET @newTableName = 'MYNEWTABLE'


/*
 Check the temp table exists
*/
IF OBJECT_ID(CONCAT('tempdb.dbo.',@tempTableName)) IS NULL
BEGIN
 PRINT 'Temp table does not exist'
 RETURN
END
 
/*
 Table variable to hold the columns
*/
DECLARE @cols TABLE ([TABLE_QUALIFIER] sysname, [TABLE_OWNER] sysname, [TABLE_NAME] sysname, [COLUMN_NAME] sysname, [DATA_TYPE] smallint, 
[TYPE_NAME] varchar(13), [PRECISION] int, [LENGTH] int, [SCALE] smallint, [RADIX] smallint, [NULLABLE] smallint, 
[REMARKS] varchar(254), [COLUMN_DEF] nvarchar(4000), [SQL_DATA_TYPE] smallint, [SQL_DATETIME_SUB] smallint, 
[CHAR_OCTET_LENGTH] int, [ORDINAL_POSITION] int, [IS_NULLABLE] varchar(254), [SS_DATA_TYPE] TINYINT
)

INSERT INTO @cols
EXEC tempdb..sp_columns @table_name = @tempTableName


/*
 build the create table statement using the columns we know about
*/
SELECT TOP 1 @columnIterator = ORDINAL_POSITION,@columnName = COLUMN_NAME, @columnType = TYPE_NAME
from @cols
ORDER BY ORDINAL_POSITION

WHILE 1=1
BEGIN

 SET @columns = CONCAT(@columns,' ',@columnName,' ', @columnType, ', ')

 SELECT TOP 1 @columnIterator = ORDINAL_POSITION,@columnName = COLUMN_NAME, @columnType = TYPE_NAME
 from @cols
 WHERE @columnIterator < ORDINAL_POSITION
 ORDER BY ORDINAL_POSITION

 IF @@ROWCOUNT = 0
  BREAK

END

SET @columns = left(@columns,LEN(@columns)-1)
SET @createsql = CONCAT('CREATE TABLE ', @newTableName, '( ',@columns,') ')

/*
 Create the table
*/
EXEC sp_executeSQL @statement = @createsql
go

Finding common column names in tables for UNION queries

Sometimes if you have tables with a lot of columns which you are trying to union, it can be hard to identify the columns they have in common and you may get this error a lot

Msg 205, Level 16, State 1, Line 1

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target

 One of the nice things you can do using the INFORMATION_SCHEMA views in SQL Server 2008+ is to query the schema to find the columns in common

SELECT  COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS H
WHERE   TABLE_NAME = ''
        AND EXISTS ( SELECT 1
                     FROM   INFORMATION_SCHEMA.COLUMNS W
                     WHERE  TABLE_NAME = ''
                            AND W.COLUMN_NAME = H.COLUMN_NAME )

 This will return the column names that your two tables/views have in common with output like below

You can then use this output to generate a select list for your UNION queries

Using the 2012 MDS Web application with Chrome

Problem

MDS Menu items get lost behind the silverlight control on the page when trying to view entities

Also see this link http://hoolihan.net/blog-tim/2009/07/07/silverlight-and-z-index/

Issue

This is because of the z-index for the silverlight control compared to the menu items is not layered correctly

Resolution

1. Browse to C:\Program Files\Microsoft SQL Server\110\Master Data Services\WebApplication\Explorer\ or equivalent
2. Update AttributeSL.aspx and ExplorerHierarchySL.aspx to have this parameter

   <param name="Windowless" value="true" />