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

[Eureka]

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?

[Wilbram-SAS]

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.

[Eureka]

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

[Wilbram-SAS]

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.

[Eureka]

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

[DaveR_SAS]

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

[Wilbram-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

[Eureka]

Once again, thanks for your help.

[Eureka]

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>'