How to connect R to vertica

Hi choisika,

There are a lot of different client drivers you can use to connect to Vertica from R. So if the client driver I use in my example doesn't work for your needs, then you can try using another. RODBC is the first one that jumps to my mind, and is the one I've used a lot with my Vertica instances. RJDBC is another option, but I've found it to be very slow for my work loads-- although it might work fine for you.

Here are the things you'll need to do:

1. Download the Vertica Client Drivers from my.vertica.com.
2. Setup an ODBC connection.

• Docs: https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/ConnectingToHPVertica/InstallingDrivers/CreatingAnODBCDataSourceNameDSN.htm

• install.packages('RODBC')

# Here is a short example of making a database connection

library(RODBC)

# Open a client connection with the database
# Note: It is not recommended to use your password in plaintext
vertica <- odbcDriverConnect("driver={/opt/hpe/odbc/lib64/libverticaodbc.so};server=node01.example.com;database=my_db;Uid=dbadmin;Pwd=123password")

# Run a query
sample_query <- sqlQuery(vertica, "SELECT * FROM foo")

# Don't forget to close your connection
odbcClose(vertica)

The tricky part of doing this is setting up the ODBC connection. Make sure to carefully read the instructions.

If you have any other questions or run into another issue, please let me know.

-C

Hi Casey_S

Thank you for your kindress

I'll try 

Have a nice day~!!

How can you locate the Driver {/opt/hpe/odbc/lib64/libverticaodbc.so} file?

Hi Heison,

           Currently it is located at /opt/vertica/lib64 location in 9.1.x. 

[dbadmin@sanumula1 lib64]$ cd /opt/vertica/lib64/
[dbadmin@sanumula1 lib64]$ ls
libverticaodbc.so
[dbadmin@sanumula1 lib64]$ pwd
/opt/vertica/lib64
[dbadmin@sanumula1 lib64]$

Hi,
If the output of my R script is a ggplot, how I can manage this output? Is it possible to "send" it to a sw presentation layer like Power BI, Tableau, Grafana, etc? Any link to browse on this topic? Thanks in advance...

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-lang 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.