Connect R and Vertica Using Rodbc

Connect R and Vertica using RODBC

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")

Not able to connect to Vertica using Informatica Workflow Manager

Assuming you have Informatica server installed on the same Windows machine, you have to create a ODBC connection object in Workflow manager.

In the "Relational Connection Browser", select type as ODBC and click on "New...". In the Connect String property, specify the name of the System DSN you created, e.g. PROD_reporting

Sample Image



Related Topics



Leave a reply



Submit