HDinsight Emulator – Using Sqoop to import/export data from/to SQL Server Express

This post will walk you through the process of importing/exporting data from/to SQL Server Express via Sqoop .

Create dev box in the cloud (optional)

If you don't want to install the HDInsight Emulator on your development machine, you can create a VM in the cloud via Azure IaaS Virtual Machine Service (given that you have Azure Subscription).

Open the Azure Management Portal, click on the Virtual Machine tab, click ‘New’ and select your preferred version on Visual Studio.

If you don’t need Visual Studio, you can install VM with Windows Server.

Once your VM is ready, it will appear as ‘Running’ in the Virtual Machine Tab. You can Remote Connect to the VM using the link in the portal.

For more details on Azure VM, in this post, Scott Hanselman shows how he’s using the Visual Studio image to get work done from his Surface 2 Tablet.

Install HDinsight Emulator

HDInsight Emulator is a single node HDInsight deployment that allows developers to develop on and debug jobs on their local development box. You can install the HDInsight Emulator via Web Platform Installer from here. All the missing prerequisites will be detected and installed automatically!

Once the installation is complete, you will notice that all the supported Hadoop services will be running as Windows Services on your local machine.

.

Install and configure SQL Server Express

If you have full SQL Server instance installed on your machine, you can skip to the next step. If you want to use the free SQL Express instance, some extra configurations are needed. Here’s how you install and configure SQL Express to make it work with Sqoop.

• Open ‘Web Platform Installer’ (install from here)
• Type ‘sql express ‘ in the search box
• Click ‘Add’ next to ‘SQL Server Express 2008’ and ‘SQL Server Express 2008 Management Studio’

• Set the password for the admin account (you will need to use it in the next step)

• Open ‘SQL Server Configuration Manager’

• Enable the TCP/IP protocol for SQLExpress.
  • Expend ‘SQL Server Network Configuration’
  • Right click on ‘Protocols for SQLExpress’
  • Right click on ‘TCP/IP’ and select ‘Enable’

• Restart SQL Server service.
  • Click on ‘SQL Server Services’
  • Right click on ‘SQL Server (SQLExpress)’ and select ‘Restart’

 

• Start ‘SQL Server Browser’ service
  • Click on ‘SQL Server Services’
  • Right click on ‘SQL Server browser’ and select ‘Properties’
  • Change the ‘Start Mode’ to ‘Automatic’, click apply and close the properties window.
  • Start the service

Create Database and Table

• Open ‘SQL Server Management Studio’ and Connect to ‘SQL Express’ server (default)

• Create ‘New Query’

• Run the following command to create new Database (sqoop) and Table (w3c) schema

CREATE DATABASE sqoop 

Go

Use sqoop

Go

CREATE TABLE [dbo].[w3c]( 

[logdate] nvarchar(150) NULL, [logtime] nvarchar(150) NULL, 

[c_ip] nvarchar(150) NULL, [cs_username] nvarchar(150) NULL, 

[s_ip] nvarchar(150) NULL, [s_port] nvarchar(150) NULL, 

[cs_method] nvarchar(150) NULL, [cs_uri_stem] nvarchar(150) NULL, 

[cs_uri_query] nvarchar(150) NULL, [sc_status] [bigint] NULL, 

[sc_bytes] [bigint] NULL, [cs_bytes] [bigint] NULL, 

[time_taken] [bigint] NULL, [cs_agent] nvarchar(150) NULL, 

[cs_referrer] nvarchar(150) NULL) ON [PRIMARY] 

Upload data to HDFS

• Download the file w3c_2MB.txt 
• Upload the file from your local drive to HDFS under the folder ‘sqoop’
  • Open the ‘Hadoop Command Prompt’ window (shortcut should be available on your desktop), type:

   > hadoop fs -copyFromLocal w3c_2MB.txt ./sqoop/w3c_2MB.txt

• Make sure that the file has been uploaded, type:

   > hadoop fs –lsr

Export from HDFS to SQL Express

• Open the ‘Hadoop Command Prompt’ window (shortcut should be available on your desktop), type:
• In the script bellow replace:
  • <your-sql-password> with the password that you’ve chosen when you installed SQL Express
  • <your-user-name> with the name of your machine

If you are using full SQL Server: instead of localhost\sqlexpress, use localhost. Make sure that the specified user (sa) is enabled and has write permissions on your SQL Server. Notice that you must use SQL Server Authentication.

   > cd c:\Hadoop\sqoop-1.4.2\bin

   > sqoop export --connect "jdbc:sqlserver://localhost\sqlexpress;databaseName=sqoop;user=sa;password=<your-sql-password>" --table w3c --export-dir /user/<your-user-name>/sqoop -m 1 -input-fields-terminated-by " "

Note:

• Make sure that the export completed successfully

• Open ‘SQL Server Management Studio’ and Connect to ‘SQL Express’ server (default).
• Create ‘New Query’ Run:

Use sqoop

Go

Select * from w3c

Import from SQL Express back to HDFS

• Open the ‘Hadoop Command Prompt’ window (shortcut should be available on your desktop)
• In the script bellow replace:
  • <your-sql-password> with the password that you’ve chosen when you installed SQL Express
  • <your-user-name> with the name of your machine

   > cd c:\Hadoop\sqoop-1.4.2\bin

   > sqoop import --connect "jdbc:sqlserver://localhost\sqlexpress;databaseName=sqoop;user=sa;password=<your-sql-password>" --table w3c --target-dir /user/<your-user-name>/sqoop/import --fields-terminated-by " " -m 1

• Make sure that the export completed successfully

    > hadoop fs –ls  /user/<your-user-name>/sqoop/import

Import data from SQL Server Express directly to Hive table

• Open the ‘Hadoop Command Prompt’ window (shortcut should be available on your desktop)
• In the script bellow replace:
  • <your-sql-password> with the password that you’ve chosen when you installed SQL Express

   > cd c:\Hadoop\sqoop-1.4.2\bin

   > sqoop import --connect "jdbc:sqlserver://localhost\sqlexpress;databaseName=sqoop;user=sa;password=<your-sql-password>" --table w3c --hive-table w3c_sqoop --create-hive-table --hive-import -m 1

• Make sure that the export completed successfully.
• Open hive CLI:

   > c:\Hadoop\hive-0.9.0\bin\hive

   hive> SELECT COUNT(*) FROM w3c_sqoop

HDInsight - Hadoop on Windows Azure!!

In the past year, I’ve been working with the HDInsight team on making the Hadoop eco-system available on Windows Server and Windows Azure. We built services that enable Azure users to quickly deploy elastic Hadoop clusters (based on Hortonworks’s Hadoop Distribution Package for Windows) on Windows Azure. By harnessing the parallel processing  power of Hadoop, HDInsight clusters enable users to effectively analyze Tera bytes and Peta bytes of data stored in Azure Blob Storage.

We released installation package that enables developers to quickly and easily install a single node HDInsight cluster (HDInsight Emulator) on their dev box.

We developed open source SDK that includes PowerShell cmdlets (now integrated with Windows Azure PowerShell) and .NET APIs that make it easier to deploy, manage and run jobs against HDInsight cluster.

To help you get started, this post demonstrates how to:

1. Deploy and interact with HDInsight cluster on Windows Azure.
2. Install HDInsight Emulator on a local dev box.

HDInsight Service

HDInsight Service enables users to deploy Hadoop clusters on Azure. These cluster are used to analyze data stored in Azure Blob Storage. To make this work, Azure HDInsight clusters are configured to use the ASV (Azure Storage Vault) implementation of HDFS, which reads/writes/streams data via Azure Blob instead of the local file-system.

The practice of using Azure Blob instead of local storage often raises questions regarding network latency and the loss of Data Locality. Luckily, HDInsight clusters and storage accounts are deployed on Azure Q10 infrastructure that features incredibly low networking overhead. As a result, for up to 50 worker nodes, reading from Azure Blob is just as fast  as reading from the local disk.

Storing the data in Azure storage instead of on the workers local storage has many benefits. In addition to geo replication and faster writes, the most obvious gain is that the data is not attached to the cluster. This enables you to create/delete clusters without a need to migrate the data. Brad Sarsfield and Denny Lee did a great job explaining ‘Why use Blob Storage with HDInsight on Azure?’

Creating an HDInsight cluster

Once you have Windows Azure subscription, deploying HDInsight cluster is only couple of clicks away. You can use the management portal to create a storage account (will store the data to be processed by your HDInsight cluster) and HDInsight cluster that will be associated with that account.

Once your cluster is ready, it will appear under the HDInsight tab.

Since your new HDinsight cluster is using its associated storage account as Distributed File System - you will notice that all the files that you might be used to see in HDFS (if you used Hadoop on non cloud environment) are stored in the Blob Storage under a container with the name of your cluster.

Interacting with HDInsight cluster

You can use the Windows Azure PowerShell module to deploy/delete HDInsight clusters and run jobs on your cluster. Click here to install via Web Platform Installer.

Once the installation is complete, launch the Windows Azure PowerShell window.

You can quickly authenticate using your Windows Azure Account (you can also use cert). Type: Add-AzureAccount

The Sign in window will appear,  enter your credentials and click Continue.

Select your subscription:

PS C:\> $subscriptionName ="Visual Studio Ultimate with MSDN"

PS C:\> Select-AzureSubscription $subscriptionName

Query for available cluster:

Once the appropriate subscription is selected, you can query for a list of your HDInsight clusters by:

PS C:\> Get-AzureHDInsightCluster

Run 10GB GraySort (Tera Gen/Sort/Validate) Job

Since hadoop-examples.jar comes with the HDInsight cluster, you can run any one of the jobs available in that examples package.

Follow the instructions here to run the GraySort mini benchmark that will generate 10GB of data, sort the data and validate the results. Works like a champ!

HDInsight Emulator

HDInsight Emulator is a single node HDInsight deployment that allows developers to develop and debug jobs on their local development box. You can install the HDInsight Emulator via Web Platform Installer from here. All the missing prerequisites will be detected and installed automatically!

Once the installation is complete, you will notice that all the supported Hadoop services will be running as Windows Services on your local machine.

You are good to go! Follow the instructions here to learn how to run MR/HIve/Pig jobs on your local HDInsight cluster. You will notice that HDFS is configured as the default distributed file system. You can however, change the core-site.xml to point to your Azure Blob Storage account.