Week Eleven Activities: Data transfer to Azure SQL database from HDFS and firewall setup

In this implementation phase, I am going to demonstrate how to transfer data from HDInsight Hadoop cluster to Azure SQL database, and integration between business intelligence Power BI and Microsoft Azure SQL database for data consumption and data visualization.

The configuration tasks are:

  1. Connection establish between Azure SQL database with HDFS
  2.  Create table in Azure SQL database
  3. Transfer data from HDFS to Azure SQL “Security delay” table  by Apache Sqoop
  4. Task 4: Login Azure SQL database via visual studio tools and firewall setup

 Task 1: Connection establish between Azure SQL database with HDFS

Step 1: The first step is to install FreeTDS.  “FreeTDS is a set of libraries for Unix and Linux that allows your programs to natively talk to Microsoft SQL Server and Sybase databases” – www.freetds.org

# FreeTDS Installation command line for database connection from HDInsight Linux clustering

sshuser@hdnO-flight:~$ sudo apt-get –assume-yes install freetds-dev freetds-bin

1.jpg

2.jpg

Step 2: establish connection with Azure SQL Database by the following command line

TDSVER=8.0 tsql -H flightdelayse.database.windows.net -U momataj -P <@password> -p 1433 -D flightdelaydb

3.jpg

Task 2: Create table in Azure SQL database

step 1: Create a table in the SQL database for exporting the file from HDFS by the following script

CREATE TABLE [dbo].[securitydelay](
[origin_city_name] [nvarchar](50) NOT NULL,
[security_delay] float,
CONSTRAINT [PK_security] PRIMARY KEY CLUSTERED
([origin_city_name] ASC))
GO

1.jpg

Step 2: You can verify the newly created table by the following query command line

2

Select * from information_schema.tables

Task 3: Transfer data from HDFS to Azure SQL “Security delay” table  by Apache Sqoop

Step 1: ## Testing connection command line:

sqoop list-databases –connect jdbc:sqlserver://flightdelayse.database.windows.net:1433 –username momataj –password <password>

“Apache Sqoop(TM) is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases.” —sqoop.apache.org/

3.jpg

 

 Step 2: Export data to the “securitydelay” of Azure SQL database from HDInsight Hadoop Storage system by the following Sqoop command line

 

sqoop export –connect ‘jdbc:sqlserver://flightdelayse.database.windows.net:1433;database=flightdelaydb’ –username momataj –password <type password> –table ‘securityt_delay’ –export-dir ‘/momo/flightdelay/output’ –fields-terminated-by ‘\t’ -m 1

4.jpg

Step 3: Now we will check data is exported or not in the security delay table.

5

Task 4: Login Azure SQL database via visual studio tools and firewall setup

Go to the Azure Dashboard -> “flightdelaydb” <your database name>- click “Tools”

1.jpg

Select “Open in Visual Studio”

2.jpg

Before connecting through the Visual studio, Need to setup firewall. Click’ Configure your firewall”

3.jpg

Click “Add client IP”. Allow your public IP to access Azure Service

4.jpg

5.jpg

Now you can login Azure SQL database from your local machine via Visual Studio tools

click on “Open in visual studio.”  The visual studio auto open for you if it is already installed on your machine. If not! You need to install visual studio tools for Microsoft Azure.

6.jpg

Now you can see the table schema, select query output from the table “securitydelay.”

13.jpg

 

Thank you 🙂

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s