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