Connect R and Vertica using RODBC

Question

This is my first time connecting to Vertica. I have already connected to a MySQL database sucessfully by using RODBC library.

I have the database setup in vertica and I installed the windows 64-bit ODBC driver from https://my.vertica.com/download-community-edition/

When I tried to connect to vertica using R, I get the below error:

channel = odbcDriverConnect(connection = "Server=myserver.edu;Database=mydb;User=mydb;Password=password")

Warning messages:
1: In odbcDriverConnect(connection = "Server=myserver.edu;Database=mydb;User=mydb;Password=password") :
[RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect(connection = "Server=myserver.edu;Database=mydb;User=mydb;Password=password") :
ODBC connection failed

Can someone tell me how to fix this? Or is there any other ways to connect to vertica using R?

Sanitarian

Answer 1

It may not be the fastest, but I prefer to use the Vertica JDBC driver from R. Getting the ODBC drivers working is a little messy across different operating systems. If you already have a Java Runtime Environment (JRE) installed for other applications then this is fairly straightforward.

Download the Vertica JDBC drivers for your Vertica server version from the MyVertica portal. Place the driver (a .jar file) in a reasonable location for your operating system.

Install RJDBC into your workspace:

install.packages("RJDBC",dep=TRUE)

In your R script, load the RJDBC module and create an instance of the Vertica driver, adjusting the classPath argument to point to the location and filename of the driver you downloaded:

library(RJDBC)
vDriver <- JDBC(driverClass="com.vertica.jdbc.Driver", classPath="full\path\to\driver\vertica_jdbc_VERSION.jar")

Make a new connection using the driver object, substituting your connection details for the host, username and password:

vertica <- dbConnect(vDriver, "jdbc:vertica://host:5433/db", "username", "password")

Then run your SQL queries:

myframe = dbGetQuery(vertica, "select Address,City,State,ZipCode from MyTable")

Eighteenth

Thanks @bpanulla. It worked. I need to add a small correction to that. the vDriver should be vDriver <- JDBC(driverClass="com.vertica.jdbc.Driver", classPath="path\to\driver\vertica_VERSION_jdk_5.jar") – Sanitarian

Ahhh interesting. I'm still using Vertica 5, so maybe they changed the package for newer versions. – Eighteenth

thanks @bpanulla. Is there anyways I could upload a tab delimited text file to vertica using JDBC? – Sanitarian

I don't know of a way, other than reading the file in using some programming language (Java, R, etc) and running insert statements. If you have a tab-delimited datafile you're probably better off using Vertica's bulk loading capability via the COPY command (my.vertica.com/docs/6.1.x/HTML/index.htm#1668.htm) – Eighteenth

Answer 2

You have to use double slash in the classPath arguement in JDBC function. for example,

vDriver <- JDBC(driverClass="com.vertica.jdbc.Driver", 
classPath="C:\\Program   Files\\Vertica Systems\\JDBC\\vertica-jdk5-6.1.2-0.jar")

worked for me, while just copying and pasting the route failed.

Owain