All Forums Analytics
porterhau5 2 posts Joined 06/12
11 Jun 2012
Connecting to Teradata in R via the teradataR package

It appears that I can successfully connect to Teradata via R, but when I try to perform some function on my td.data.frame, it provides the following error after the last line of my code:

 "42S02 -3807 [NCR][ODBC Teradata Driver][Teradata Database] Object 'my_sample_table' does not exist. "

My code:

library("teradataR")
library("RODBC")
tdConnect("MyTDdb")
tdf <- td.data.frame("my_sample_table","my_db")
my.stats <- td.stats(tdf,"Numerical_Column_of_Interest", c("cnt","min","max","mean"))
 

Note:  I changed the database and table names, but the concept is the same.  I am fairly confident that my td.data.frame() line works because when I put in a bogus table name, it errors out.  When I put in a good table name, it takes about 2 mins to finish running.  I also get the same error message when I try different functions, not just td.stat().

Any ideas on how to make this work?  I have spent about and hour searching google and have found very little documentation or samples on the teradataR package.

 

 

ulrich 727 posts Joined 09/09
12 Jun 2012

You migh need to set the default DB on the connect statement.

the following worked for me:

library(RJDBC)
Lade nötiges Paket: DBI
Lade nötiges Paket: rJava
> .jaddClassPath("path/terajdbc4.jar")
> .jaddClassPath("path/tdgssconfig.jar")
> library(teradataR)
> 
> tdConnect("xxx","user","password","sys_calendar","jdbc")
> 
> tdf <- td.data.frame("calendar")
> my.stats <- td.stats(tdf,"year_of_calendar", c("cnt","min","max","mean"))
> my.stats
               col  xcnt xmin xmax xmean
1 year_of_calendar 73414 1900 2100  2000

Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles

porterhau5 2 posts Joined 06/12
12 Jun 2012

Ulrich, that worked great, thanks!  Thanks for the quick response.

daniem 4 posts Joined 12/11
14 Jun 2012

I am trying to connect via JDBC using Ulrich's approach but I am not having success.  Everything appears to work fine until I try to create the data frame:

 

> tdf<-td.data.frame("ccaef")

Warning message:

In td.data.frame("ccaef") :

  Teradata table not found.  Result is empty data frame.

 

I am able to successfully use the same db connection details to connect via a SAS libref statement.

 

I am running on Red Hat Linux.  I saw the following:

 

JDBC support added to allow Windows or Mac users to run the package with JDBC

 

Stated here:

http://www.decisionstats.com/teradata-updates-teradata-r/

 

Does this mean that the JDBC approach is not currently supported on Red Hat?

 

Thanks.

ulrich 727 posts Joined 09/09
14 Jun 2012

does the table exists in the DB you specified in the tdConnect?

I am not aware that JDBC would not work on Red Hat - it works on Suse

Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles

daniem 4 posts Joined 12/11
15 Jun 2012

Yes, the table does exist.  I have re-confirmed.  Glad to hear that it works on Linux.

Here is my code:

 

library(RJDBC)

 

.jaddClassPath("/teradata_poc/programs/lib/terajdbc4.jar")

.jaddClassPath("/teradata_poc/programs/lib/tdgssconfig.jar")

 

library(teradataR)

 

tdConnect("10.33.2.136","my_uid","my_pwd","EDEE_POC","jdbc ")

 

tdf<-td.data.frame("ccaef")

ulrich 727 posts Joined 09/09
15 Jun 2012

Can you run my sys_calendar example - as calendar should be available on every system.

On a mac I had once the problem that I had to rerun library .jaddClassPath... sections twice...

Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles

daniem 4 posts Joined 12/11
18 Jun 2012

I get the same results even after running the .jaddClassPath sections twice.

daniem 4 posts Joined 12/11
20 Jun 2012

Hi Ulrich,

I am still not able to connect via JDBC but that is ok.  We were able to get ODBC support installed on our Linux environment and that is working for me.

Thanks for your assistance.

ulrich 727 posts Joined 09/09
03 Dec 2012

Just to avoid future problems
tdConnect("10.33.2.136","my_uid","my_pwd","EDEE_POC"," jdbc ")
is not getting a connection due to the blanks in " jdbc "
it has to be "jdbc"
 
tdConnect("10.33.2.136","my_uid","my_pwd","EDEE_POC","jdbc")
unfortunatly no error messag is raised...

 

Der Beginn aller Wissenschaften ist das Erstaunen, dass die Dinge sind, wie sie sind.-Aristoteles

margonat17 1 post Joined 07/13
19 Jul 2013

Does any body know where to get TeradataR version for R 3? When I tried to install the library TeradataR 1.0.1 it gives me this message

>library(teradataR)

Error: package ‘teradataR’ was built before R 3.0.0: please re-install it.

Thank you in advance.
  

DouglasM 2 posts Joined 03/13
16 Sep 2013

I discovered that R v3.0.1 (2013-05-16) and RStudio 0.97.551 (which uses R v3.01 packages) both communicate via RODBC with TeraData v14 on a Windows 7 PC if (and only if) you have TeraData Studio open and logged in.  The SQL shown below works in both R applications.  It was confirmed with a SQL query that is 8 pages long in Word.
 
Refer to http://forums.teradata.com/forum/analytics/teradatar-package-and-r-3-0-0 for details.
 
This was only tested with SELECT queries on Windows 7.  Anyone want to try other configurations and post the results to the topic http://forums.teradata.com/forum/analytics/teradatar-package-and-r-3-0-0?

Alexander K 16 posts Joined 09/13
27 Feb 2014

On SUSE SLES11-SP1, under R v.3.0.2 and the teradataR v.1.1.0, the above recommended sequence to connect R to Teradata produces:
   Error in .jfindClass(as.character(driverClass)[1]) : class not found
Any ideas to resolve this, possibly?
Thanks,
Alexander.

tomnolan 385 posts Joined 01/08
28 Feb 2014

Please list all the R code you are using to connect to Teradata.

Alexander K 16 posts Joined 09/13
28 Feb 2014

I actually figured out how to get RODBC, teradataR and Teradata under a SLES11 SP1 Teradata Express VM; I just wrote a complementary article at:
http://forums.teradata.com/forum/analytics/connecting-to-teradata-in-r-via-the-teradatar-package-teradata-express-vm-version
 
Feel free to make remarks or corrections...
Thanks,
Alexander.

You must sign in to leave a comment.