SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

Reply
Contributor
Posts: 22

Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

In order to copy SQL Server 2012 tables into Cloudera (Hadoop), the appropriate JDBC drivers must be installed in both Hadoop and in the SAS Vapp.  Here is an excerpt from a terminal window in Cloudera:

root@quickstart tmp]# ls *.jar

sqljdbc41.jar  sqljdbc4.jar  sqljdbc.jar

[[root@quickstart tmp]# chmod 777 *.jar

[root@quickstart tmp]# su - hdfs

-bash-4.1$

-bash-4.1$ hadoop fs –ls /user/oozie/share.lib

–ls: Unknown command

-bash-4.1$

I'm trying to follow the instructions in

What might be doing wrong?

SAS Employee
Posts: 6

Re: Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

It seems Word's auto-correct caused our instructions to have a long dash where it should have a hyphen. So it should be hadoop fs -ls instead of hadoop fs –ls.

Contributor
Posts: 22

Re: Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

Wilbram,

I tried your suggestion.  Here's what I got:

-bash-4.1$ hadoop fs -ls /user/oozie/share.lib

ls: `/user/oozie/share.lib': No such file or directory

SAS Employee
Posts: 6

Re: Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

It should have been hadoop fs -ls /user/oozie/share/lib. Another typo in the instructions I guess.

Btw. this is just the hadoop version of the Unix ls command. So you can start with hadoop fs -ls / to see which directories exist in the root, and then step by step expand your hadoop fs -ls command to navigate down the tree.

Hope this makes sense.

Contributor
Posts: 22

Re: Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

Wilbram,

You've been quite helpful. I've gotten the JDBC drivers installed in Claudera and the SAS Vapp. Do you have any examples of a SQL Server 2012 configuration?  I'm unsure of what values need to be entered in the Database configuration screen and I cannot find anything in the SAS documentation.


Joe

SAS Employee
Posts: 75

Re: Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

Joe, Step 8 in this topic in the SAS Data Loader 2.2 user guide describes the general steps for specifying s DBMS connection:

SAS(R) Data Loader 2.2 for Hadoop: User's Guide

Can you get the IP address and other values you need from your SQL Server administrator?

Dave

SAS Employee
Posts: 6

Re: Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

Posted in reply to DaveR_SAS

Hi Joe,

Here is an example:

 

  • SQL Server
    • Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver
    • Connect string: jdbc:sqlserver://[DBMS-server-hostname]:1433;databaseName=[name-of-database]
    • Userid: [userid-you-would-use-in-SQLServer]
    • Password: [password]

Building JDBC connect strings are not always easy to construct and test. There are a few gotchas, including:

1. Depending on which vendor you are using, the Hadoop VM might not know hosts that are on your local network. Replacing DBMS-server-hostname with the IP-address of your DBMS host should work. Btw. the square bracket ([]) should not be typed into the property fields.

2. Depending on what SQL Server version/edition you are using, it might not have TCP support enabled by default. Searching the internet using "sql server express tcp enable", should provide details on how to enable this.

Cheers,

Wilbram

Contributor
Posts: 22

Re: Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

Once again, thanks for your help.

Contributor
Posts: 22

Re: Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app

I'm getting closer to being able to copy SQL Server tables to Hadoop using SAS Data Loader for Hadoop thanks to help from folks like Wilbram and Ben.  There is still something I am doing wrong.  Here is the error message I'm getting:

Execution failed: Job '0000001-150701145419830-oozie-oozi-W' was killed.Job ID '0000001-150701145419830-oozie-oozi-W' did not complete successfully. The final status was 'KILLED' using using URL 'http://192.168.88.130:11000/oozie/v2/job/0000001-150701145419830-oozie-oozi-W?show=log' and configuration '<configuration> <property><name>oozie.wf.application.path</name><value>/user/sasdemo/SASDataLoader/CopyDatatoHadoop07e6834bc414474583630a030d4255a2</value></property> <property><name>jobTracker</name><value>quickstart.cloudera:8032</value></property> <property><name>nameNode</name><value>quickstart.cloudera:8020</value></property> <property><name>user.name</name><value>sasdemo</value></property> <property><name>queueName</name><value>default</value></property> <property><name>oozie.use.system.libpath</name><value>true</value></property></configuration>'

Ask a Question
Discussion stats
  • 8 replies
  • 634 views
  • 0 likes
  • 3 in conversation