The Hadoop on Azure Sqoop Import Sample Tutorial

Table of Contents

• Overview
  
   
  • Goals
  • Key technologies
  • Setup and Configuration

   

• Tutorial
  
   
  • How to set up a SQL database
  • How to use Sqoop from Hadoop on Azure to import SQL Database query results to the HDFS cluster in Hadoop On Azure.

   

• Summary

Overview

This tutorial shows how to use Sqoop to import data from a SQL database on Windows Azure to an Hadoop on Azure HDFS cluster.

While Hadoop is a natural choice for processing unstructured and
semi-structured data, such as logs and files, there may also be a need
to process structured data stored in relational databases.

Sqoop is a tool designed to transfer data between Hadoop and
relational databases. You can use it to import data from a relational
database management system (RDBMS) such as SQL or MySQL or Oracle into
the Hadoop Distributed File System (HDFS), transform
the data in Hadoop with MapReduce or Hive, and then export the data
back into an RDBMS. In this tutorial, you are using a SQL Database for
your relational database.

Sqoop is an open source software product of Cloudera, Inc. Software development for Sqoop has recently moved from gitHub to the Apache
Sqoop  site.

In Hadoop on Azure, Sqoop is deployed from the Hadoop Command Shell on
the head node of the HDFS cluster. You use the Remote Desktop feature
available in the Hadoop on Azure portal to access the head node of the
cluster for this deployment.

Goals

In this tutorial, you see three things:

1. How to set up a SQL database on Windows Azure for use with the tutorial.
2. How to use the Remote Desktop feature in Hadoop on Azure to access the head node of the HDFS cluster.
3. How to import relational data from SQL Server to a Hadoop on Azure HDFS cluster by using Sqoop.

Key technologies

• Windows Azure
• Hadoop on Azure
• Sqoop

Setup and Configuration

You must have an account to access Hadoop on Azure and have created a
cluster to work through this tutorial. To obtain an account and create
an Hadoop cluster, follow the instructions outlined in the
Getting started with Microsoft Hadoop on Azure section of the Introduction to Hadoop on Azure topic.

You will also need your outward facing IP address for your current
location when configuring your firewall on SQL Database. To obtain it,
go to the site
WhatIsMyIP
and make a note of it. Later in the procedure, you also need the
outward facing IP address for the head of the Hadoop cluster. You can
obtain this IP address in the same way.

Tutorial

This tutorial is composed of the following segments:

1. How to set up a SQL database.
2. How to use Sqoop from Hadoop on Azure to import data to the HDFS cluster.

How to set up a SQL database

Log in into your Windows Azure account. To create a database server, click the
Database icon in the lower left-hand corner on the page.

On the Getting Started page, click the Create a new SQL Database Server option.

Select the type of subscription (such as Pay-As-You-Go) associated with you account in the
Create Server window and press Next.

Select the appropriate Region in the Create Server window and click
Next.

Specify the login and password of the server-level principal of your SQL Database server and then press
Next.

Press Add to specify a firewall rule that allows
your current location access to SQL Database to upload the
AdventureWorks database. The firewall grants access based on the
originating IP address of each request. Use the IP address found
with the configuration preliminaries of this tutorial for the values to
add. Specify a Rule name, such as shown, but remember to use your IP
address, not the one used for illustration purposes below. (You must
also add the outward IP address of the head node
in you Hadoop cluster. If you know it already, add it now.) Then press
the
Finish button.

Download the AdventureWorks2012 database onto your local machine from
Recommended Downloads link on the Adventure Works for SQL Database
site.

Unzip the file, open an Administrator Command Prompt, and navigate to
the AdventureWorks directory inside the AdventureWorks2012ForSQLAzure
folder.

Run CreateAdventureWorksForSQLAzure.cmd by typing the following:

CreateAdventureWorksForSQLAzure.cmd servername username password

For example, if the assigned SQL Database server is named b1gl33p,
the administrator user name "Fred", and the password "Secret", you would
type the following:

CreateAdventureWorksForSQLAzure.cmd b1gl33p.database.windows.net Fred@b1gl33p Secret

The script creates the database, installs the schema, and populates the database with sample data.

Return to the WindowsAzurePlatform portal page, click your subscription on the left-hand side (Pay-As-You-Go in the example below) and select your database (here named wq6xlbyoq0). The AventureWorks2012 should be listed
in the Database Name column. Select it and press the Manage icon at the top of the page.

Enter the credentials for the SQL database when prompted and press Log on.

This opens the Web interface for the Adventure Works database on SQL Database. Press the
New Query icon at the top to open the query editor.

Since Sqoop currently adds square brackets to the table name, we need
to add a synonym to support two-part naming for SQL Server tables. To
do so, run the following query:

CREATE SYNONYM [Sales.SalesOrderDetail] FOR Sales.SalesOrderDetail

Run the following query and review its result.

select top 200 * from [Sales.SalesOrderDetail]

How to use Sqoop from Hadoop on Azure to import SQL Database query results to the HDFS cluster in Hadoop
On Azure.

From your Account page, scroll down to the Open Ports icon in the
Your cluster section and click the icon to open the ODBC Server port on the head node in your cluster.

Return to your Account page, scroll down to the Your cluster section and click the
Remote Desktop icon this time to open the head node in your cluster.

Select Open when prompted to open the .rdp file.

Select Connect in the Remote Desktop Connection window.

Enter your credentials for the Hadoop cluster (not your Hadoop on Azure account) into the
Windows Security window and select OK.

Open Internet Explorer and go to the site WhatIsMyIP
to obtain the outward facing IP address for the head node of the
cluster. Return the SQL Database management page and add a firewall rule
that allows your Hadoop cluster access to SQL Database. The firewall
grants access based on the originating
IP address of each request.

Double-click on the Hadoop Command Shell icon in the upper left hand of the Desktop to open it. Navigate to the
"c:\Apps\dist\sqoop\bin" directory and run the following command:

sqoop import --connect
"jdbc:sqlserver://[serverName].database.windows.net;username=[userName]@[serverName];password=[password];database=AdventureWorks2012"
--table Sales.SalesOrderDetail --target-dir /data/lineitemData -m 1

So, for example, for the following values:
* server name: wq6xlbyoq0
* username: HadoopOnAzureSqoopAdmin
* password: Pa$$w0rd

The sqoop command is:

sqoop import --connect
"jdbc:sqlserver://wq6xlbyoq0.database.windows.net;username=HadoopOnAzureSqoopAdmin@wq6xlbyoq0;password=Pa$$w0rd;;database=AdventureWorks2012"
--table Sales.SalesOrderDetail --target-dir /data/lineitemData -m 1

Return to the Accounts page of the Hadoop on Azure portal and open the
Interactive Console this time. Run the #lsr command from the JavaScript console to list the files and directories on your HDFS cluster. 

Run the #tail command to view selected results from the part-m-0000 file.

tail /user/RAdmin/data/SalesOrderDetail/part-m-00000

Summary

In this tutorial, you have seen how to use Sqoop to import data from a
SQL database on Windows Azure to an Hadoop on Azure HDFS cluster.