JDBC connectivity in R

[Dan Young]

Hello All,

I'm trying to create a JDBC connection via R to my Hadoop running in pseudo-dist mode on my Macbook.  I can connect via the Lingual client fine, and run my queries against my tables.  All is good there so far.

When I try the JDBC connection in R, I'm getting the following error:

> library(RJDBC)

Loading required package: DBI

Loading required package: rJava

> cp <- c(

+ "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar",

+ "/Users/dyoung1/hadoop/hadoop-core-1.0.4.jar",

+ "/Users/dyoung1/.lingual-client/platform/local/lingual-local-1.0.0-wip-172-jdbc.jar") 

> .jinit(classpath=cp) 

> drv <- JDBC("cascading.lingual.jdbc.Driver", "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar")

> connection <- dbConnect(drv, "jdbc:lingual:hadoop;catalog=/user/dyoung1/.lingual/catalog;schema=MOBILE")

13/06/20 15:36:23 INFO jdbc.LingualConnection: using platform: hadoop

13/06/20 15:36:23 INFO jdbc.LingualConnection: using schema: MOBILE

13/06/20 15:36:23 INFO platform.PlatformBrokerFactory: installing platform: local

13/06/20 15:36:23 INFO platform.PlatformBrokerFactory: installing platform: hadoop

13/06/20 15:36:23 INFO jdbc.LingualConnection: using platform: hadoop

13/06/20 15:36:23 INFO jdbc.LingualConnection: using schema: MOBILE

Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1],  : 

  java.sql.SQLException: could not create connection

Is it something to do the the catalog URI? I've tried various permutations for the catalog, with, without,etc........could be an R thing too....I'll try a test java program and keep digging around.....

Regards,

Dano

[Chris K Wensel]

try pointing to 

/user/dyoung1/

as your catalog.

--
You received this message because you are subscribed to the Google Groups "Lingual User" group.
To unsubscribe from this group and stop receiving emails from it, send an email to lingual-user...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Chris K Wensel

ch...@concurrentinc.com

http://concurrentinc.com

[Dan Young]

Hey Chris,

I tried that and it yields the same result.

> library(RJDBC)

Loading required package: DBI

Loading required package: rJava

> cp <- c(

+ "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar",

+ "/Users/dyoung1/hadoop/hadoop-core-1.0.4.jar",

+ "/Users/dyoung1/.lingual-client/platform/local/lingual-local-1.0.0-wip-172-jdbc.jar") 

> .jinit(classpath=cp) 

> 

> drv <- JDBC("cascading.lingual.jdbc.Driver", "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar")

> connection <- dbConnect(drv, "jdbc:lingual:hadoop;catalog=/user/dyoung1/;schema=MOBILE")

13/06/20 19:58:13 INFO jdbc.LingualConnection: using platform: hadoop

13/06/20 19:58:13 INFO jdbc.LingualConnection: using schema: MOBILE

13/06/20 19:58:13 INFO platform.PlatformBrokerFactory: installing platform: hadoop

13/06/20 19:58:13 INFO platform.PlatformBrokerFactory: installing platform: local

13/06/20 19:58:13 INFO jdbc.LingualConnection: using platform: hadoop

13/06/20 19:58:13 INFO jdbc.LingualConnection: using schema: MOBILE

[Chris K Wensel]

make sure you aren't on java 7, hadoop doesn't support it and neither do we, though you should be seeing a better error than that.

that said, you don't need to set the catalog path at all, it defaults to the Hadoop home directory on hdfs which is the path you are setting.

ckw

[dan young]

Hey Chris,

Here's the version of Java I'm using:

Bet on Cowboys, Not Horses....

[dan young]

Sorry, sent that to soon, fat finger....Here's the version of Java I'm using:

java -version

java version "1.6.0_45"

Java(TM) SE Runtime Environment (build 1.6.0_45-b06-451-11M4406)

Java HotSpot(TM) 64-Bit Server VM (build 20.45-b01-451, mixed mode)

Even when I try it without the catalog, or the schema, I get the error:

> library(RJDBC)

Loading required package: DBI

Loading required package: rJava

> cp <- c(

+ "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar",

+ "/Users/dyoung1/hadoop/hadoop-core-1.0.4.jar",

+ "/Users/dyoung1/.lingual-client/platform/local/lingual-local-1.0.0-wip-172-jdbc.jar") 

> .jinit(classpath=cp) 

> drv <- JDBC("cascading.lingual.jdbc.Driver", "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar")

> connection <- dbConnect(drv, "jdbc:lingual:hadoop;schema=MOBILE")

13/06/21 05:50:18 INFO jdbc.LingualConnection: using platform: hadoop

13/06/21 05:50:18 INFO jdbc.LingualConnection: using schema: MOBILE

13/06/21 05:50:18 INFO platform.PlatformBrokerFactory: installing platform: hadoop

13/06/21 05:50:18 INFO platform.PlatformBrokerFactory: installing platform: local

13/06/21 05:50:18 INFO jdbc.LingualConnection: using platform: hadoop

13/06/21 05:50:18 INFO jdbc.LingualConnection: using schema: MOBILE

Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1],  : 

  java.sql.SQLException: could not create connection

> 

> cp <- c(

+ "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar",

+ "/Users/dyoung1/hadoop/hadoop-core-1.0.4.jar",

+ "/Users/dyoung1/.lingual-client/platform/local/lingual-local-1.0.0-wip-172-jdbc.jar") 

> .jinit(classpath=cp) 

> drv <- JDBC("cascading.lingual.jdbc.Driver", "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar")

> connection <- dbConnect(drv, "jdbc:lingual:hadoop")

13/06/21 05:55:46 INFO jdbc.LingualConnection: using platform: hadoop

13/06/21 05:55:46 INFO platform.PlatformBrokerFactory: installing platform: local

13/06/21 05:55:46 INFO platform.PlatformBrokerFactory: installing platform: hadoop

13/06/21 05:55:46 INFO jdbc.LingualConnection: using platform: hadoop

Error in .jcall(drv@jdrv, "Ljava/sql/Connection;", "connect", as.character(url)[1],  : 

  java.sql.SQLException: could not create connection

> 

If I use local, the connection seems to work:

> connection <- dbConnect(drv, "jdbc:lingual:local;schema=MOBILE")

13/06/21 05:51:48 INFO jdbc.LingualConnection: using platform: local

13/06/21 05:51:48 INFO jdbc.LingualConnection: using schema: MOBILE

13/06/21 05:51:48 INFO catalog.FileCatalogManager: reading catalog from: /Users/dyoung1/.lingual/catalog

13/06/21 05:51:48 INFO jdbc.LingualConnection: using platform: local

13/06/21 05:51:48 INFO jdbc.LingualConnection: using schema: MOBILE

I also observed that I have to include the lingual-local-1.0.0-wip-172-jdbc.jar file in the RJDBC classpath, otherwise I can't even seem to initialize the drv:

> library(RJDBC)

Loading required package: DBI

Loading required package: rJava

> cp <- c(

+ "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar",

+ "/Users/dyoung1/hadoop/hadoop-core-1.0.4.jar")

> drv <- JDBC("cascading.lingual.jdbc.Driver", "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar")

Error in .jfindClass(as.character(driverClass)[1]) : class not found

> 

> library(RJDBC)

Loading required package: DBI

Loading required package: rJava

> cp <- c(

+ "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar",

+ "/Users/dyoung1/hadoop/hadoop-core-1.0.4.jar")

> drv <- JDBC("cascading.lingual.jdbc.Driver", "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar")

Error in .jfindClass(as.character(driverClass)[1]) : class not found

> cp <- c(

+ "/Users/dyoung1/.lingual-client/platform/hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar",

+ "/Users/dyoung1/hadoop/hadoop-core-1.0.4.jar",

+ "/Users/dyoung1/.lingual-client/platform/local/lingual-local-1.0.0-wip-172-jdbc.jar") 

> .jinit(classpath=cp) 

[1] 0

> 

Not sure if any of this is related....

Regards,

Dano

On Thu, Jun 20, 2013 at 11:14 PM, Chris K Wensel <ch...@wensel.net> wrote:

--

[Dan Young]

Ok, tried a JDBC connection in SQuirreLSQL and this is the stack trace I'm getting when trying to connect:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.sql.SQLException: could not create connection

at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:232)

at java.util.concurrent.FutureTask.get(FutureTask.java:91)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:439)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)

at java.lang.Thread.run(Thread.java:680)

Caused by: java.lang.RuntimeException: java.sql.SQLException: could not create connection

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:171)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104)

... 6 more

Caused by: java.sql.SQLException: could not create connection

at cascading.lingual.jdbc.JaninoFactory.createConnection(JaninoFactory.java:60)

at cascading.lingual.jdbc.Driver.connect(Driver.java:157)

at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167)

... 8 more

Caused by: java.lang.reflect.InvocationTargetException

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

at cascading.lingual.jdbc.JaninoFactory.create(JaninoFactory.java:154)

at cascading.lingual.jdbc.JaninoFactory.createConnection(JaninoFactory.java:44)

... 11 more

Caused by: java.lang.NoClassDefFoundError: org/apache/commons/configuration/Configuration

at org.apache.hadoop.metrics2.lib.DefaultMetricsSystem.<init>(DefaultMetricsSystem.java:37)

at org.apache.hadoop.metrics2.lib.DefaultMetricsSystem.<clinit>(DefaultMetricsSystem.java:34)

at org.apache.hadoop.security.UgiInstrumentation.create(UgiInstrumentation.java:51)

at org.apache.hadoop.security.UserGroupInformation.initialize(UserGroupInformation.java:216)

at org.apache.hadoop.security.UserGroupInformation.ensureInitialized(UserGroupInformation.java:184)

at org.apache.hadoop.security.UserGroupInformation.isSecurityEnabled(UserGroupInformation.java:236)

at org.apache.hadoop.security.KerberosName.<clinit>(KerberosName.java:79)

at org.apache.hadoop.security.UserGroupInformation.initialize(UserGroupInformation.java:209)

at org.apache.hadoop.security.UserGroupInformation.ensureInitialized(UserGroupInformation.java:184)

at org.apache.hadoop.security.UserGroupInformation.isSecurityEnabled(UserGroupInformation.java:236)

at org.apache.hadoop.security.UserGroupInformation.getLoginUser(UserGroupInformation.java:466)

at org.apache.hadoop.security.UserGroupInformation.getCurrentUser(UserGroupInformation.java:452)

at org.apache.hadoop.fs.FileSystem$Cache$Key.<init>(FileSystem.java:1494)

at org.apache.hadoop.fs.FileSystem$Cache.get(FileSystem.java:1395)

at org.apache.hadoop.fs.FileSystem.get(FileSystem.java:254)

at org.apache.hadoop.fs.FileSystem.get(FileSystem.java:123)

at cascading.lingual.platform.hadoop.HadoopPlatformBroker.getDefaultFileSystem(HadoopPlatformBroker.java:437)

at cascading.lingual.platform.hadoop.HadoopPlatformBroker.getDefaultFileSystemURIScheme(HadoopPlatformBroker.java:430)

at cascading.lingual.platform.hadoop.HadoopPlatformBroker.makeURIScheme(HadoopPlatformBroker.java:416)

at cascading.lingual.platform.hadoop.HadoopPlatformBroker.getFileSystem(HadoopPlatformBroker.java:381)

at cascading.lingual.platform.hadoop.HadoopPlatformBroker.getFullPath(HadoopPlatformBroker.java:312)

at cascading.lingual.platform.PlatformBroker.makeFullCatalogFilePath(PlatformBroker.java:278)

at cascading.lingual.platform.PlatformBroker.getFullCatalogPath(PlatformBroker.java:217)

at cascading.lingual.catalog.FileCatalogManager.readCatalog(FileCatalogManager.java:76)

at cascading.lingual.platform.PlatformBroker.loadCatalog(PlatformBroker.java:227)

at cascading.lingual.platform.PlatformBroker.getCatalog(PlatformBroker.java:188)

at cascading.lingual.platform.PlatformBroker.startConnection(PlatformBroker.java:118)

at cascading.lingual.platform.hadoop.HadoopPlatformBroker.startConnection(HadoopPlatformBroker.java:93)

at cascading.lingual.jdbc.LingualConnection.initialize(LingualConnection.java:103)

at cascading.lingual.jdbc.LingualConnection.<init>(LingualConnection.java:79)

at SC.<init>(Unknown Source)

... 17 more

Caused by: java.lang.ClassNotFoundException: org.apache.commons.configuration.Configuration

at java.net.URLClassLoader$1.run(URLClassLoader.java:202)

at java.security.AccessController.doPrivileged(Native Method)

at java.net.URLClassLoader.findClass(URLClassLoader.java:190)

at net.sourceforge.squirrel_sql.fw.util.MyURLClassLoader.findClass(MyURLClassLoader.java:209)

at java.lang.ClassLoader.loadClass(ClassLoader.java:306)

at java.lang.ClassLoader.loadClass(ClassLoader.java:247)

... 48 more

Another thing I noticed is that I need to register both the local and the hadoop JDBC jar files. If I don't, i get a class not found error while trying to register in either R/SquirrelSQL for the org/slf4j/* classes. 

dans-MacBook-Pro:platform dyoung1$ jar -tvf local/lingual-local-1.0.0-wip-172-jdbc.jar | grep slf4j | wc -l

      40

dans-MacBook-Pro:platform dyoung1$ jar -tvf hadoop/lingual-hadoop-1.0.0-wip-172-jdbc.jar | grep slf4j | wc -l

       0

Currently I'm have the following in my classpath

lingual-hadoop-1.0.0-wip-172-jdbc.jar

lingual-local-1.0.0-wip-172-jdbc.jar 

hadoop-core-1.0.4.jar

Still digging.

Regards,

Dano

[Chris K Wensel]

ok, good stuff.

will look through this again, something could have fallen in the cracks recently in an attempt to shore up the jars and java version stuff.

sadly this is notoriously hard to automate testing for.

[Dan Young]

Thanx Chris, no problem.  

Great stuff you guys are doing @ Concurrent!  Cascading rocks!

BTW,  I was able to the the SquirrelSQL to register and connect w/o error.  I had to add the commons-configuration-1.6.jar file to the classpath. I just get some cryptic error when I try to run a query....

query>select * from clicks;

org.eigenbase.util.EigenbaseContextException: From line 1, column 15 to line 1, column 20

I'll keep an eye on the github repo....

Regards,

Dano