We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


vertica.dplyr not displaying select context in R — Vertica Forum

vertica.dplyr not displaying select context in R

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


Comments

  • edited May 2017

    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

  • edited May 2017

    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

  • 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

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

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

  • edited May 2017

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file