topic Re: Loading SQL Server 2012 Data Into Cloudera (Hadoop)(hadoop, app in SAS Data Management

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

Eureka — Wed, 24 Jun 2015 18:24:58 GMT

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?

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

Wilbram-SAS — Wed, 24 Jun 2015 20:46:21 GMT

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.

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

Eureka — Wed, 24 Jun 2015 21:17:58 GMT

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

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

Wilbram-SAS — Wed, 24 Jun 2015 21:32:26 GMT

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.

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

Eureka — Thu, 25 Jun 2015 16:51:27 GMT

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

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

DaveR_SAS — Thu, 25 Jun 2015 17:14:05 GMT

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

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

Wilbram-SAS — Thu, 25 Jun 2015 17:32:08 GMT

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

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

Eureka — Thu, 25 Jun 2015 18:53:58 GMT

Once again, thanks for your help.

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

Eureka — Thu, 02 Jul 2015 00:04:21 GMT

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