The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

How to connect R to vertica

choisikachoisika
in General Discussion

Hi 

 

I want to connect R and vertica

 

 

but, I don't know everything

 

each download R and vertica??

 

and what is the next job??

 

please help me ... 

Comments

  • Casey_SCasey_S

    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.
  • Install the R library you want to use to make your client connection. For this demonstration I'll use RODBC.
      • install.packages('RODBC')
    1. In your R code create a database connection and execute a query. (Check out the RODBC docs for more details about doing this. https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf)
    2. # 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)

      3.  

      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

  • choisikachoisika

    Hi Casey_S

     

    Thank you for your kindress

     

    I'll try 

     

    Have a nice day~!!

  • heisonheison

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

  • SruthiASruthiA Employee

    Hi Heison,

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

    [[email protected] lib64]$ cd /opt/vertica/lib64/
    [[email protected] lib64]$ ls
    libverticaodbc.so
    [[email protected] lib64]$ pwd
    /opt/vertica/lib64
    [[email protected] lib64]$

  • PadepPadep

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

    • Leave a Comment

    BoldItalicStrikethroughOrdered listUnordered list
    Format
    Heading 1Heading 2
    QuoteCodeSpoiler
    Emoji
    :):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
    Url
    Image
    Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
    Drop image/file
    Home • General DiscussionComment As ...

    Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.