vertica.dplyr not displaying select context in R

lukethor

R package does not display the select statement rows from a Vertica table in the command line. I have used JDBC and RODBC drivers and none of those options works. It can do loading and copying between R and Vertica but does not display results of a R's select statement. see examples below

Shayon_Sanyal

It may not be the fastest, but using the Vertica JDBC driver from R is fairly straight forward. Make sure you have a Java Runtime Environment (JRE) installed.

Download the Vertica JDBC drivers for your Vertica server version from the MyVertica portal (my.vertica.com). 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)
Loading required package: DBI
Loading required package: rJava
vDriver <- JDBC(driverClass="com.vertica.jdbc.Driver", classPath="/home/dbadmin/R/vertica-jdbc-8.1.0-3.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/db", "username", "password")

Then run your SQL queries:

myframe = dbGetQuery(vertica, "select * from Department")
myframe
Id Name
1 2 Sales
2 1 IT

Shayon_Sanyal

It may not be the fastest, but using the Vertica JDBC driver from R is fairly straight forward. Make sure you have a Java Runtime Environment (JRE) installed.

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)
Loading required package: DBI
Loading required package: rJava
vDriver <- JDBC(driverClass="com.vertica.jdbc.Driver", classPath="/home/dbadmin/R/vertica-jdbc-8.1.0-3.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/db", "username", "password")

Then run your SQL queries:

myframe = dbGetQuery(vertica, "select * from Department")
myframe
Id Name
1 2 Sales
2 1 IT

lukethor

Thanks for the answer. Tough that library works as you shown in the example, what I am looking for is by using the library(vertica.dplyr) to display the results. As I said the only thing this library is not displaying is the results once that I use the library(vertica.dplyr). Thank you

Shayon_Sanyal

Hi, can you tell me your R version for this test?

lukethor

R version 3.2.3 (2015-12-10) -- "Wooden Christmas-Tree"
Copyright (C) 2015 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)

lukethor

Here is the answer how I use a different schema than public.
syntax : ' " schema" . " table" '
example : ' " epbuilder_proxy ". " expected_record" '

Example:
orig <- db_load_from_file(vertica, file.name= "/Users/foo/expected_records.csv",'"schema_proxy"."expected_records"', sep = ",")