Connect R and Vertica using RODBC
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 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)
vDriver <- JDBC(driverClass="com.vertica.jdbc.Driver", classPath="full\path\to\driver\vertica_jdbc_VERSION.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:5433/db", "username", "password")
Then run your SQL queries:
myframe = dbGetQuery(vertica, "select Address,City,State,ZipCode from MyTable")
Not able to connect to Vertica using Informatica Workflow Manager
Assuming you have Informatica server installed on the same Windows machine, you have to create a ODBC connection object in Workflow manager.
In the "Relational Connection Browser", select type as ODBC and click on "New...". In the Connect String property, specify the name of the System DSN you created, e.g. PROD_reporting
Related Topics
Cannot Read File with "#" and Space Using Read.Table or Read.CSV in R
Loess Fit and Resulting Equation
How to Calculate Confidence Intervals for Nonlinear Least Squares in R
Get Names of Column with Max Value for Each Row
How to Adapt a Latex Beamer Theme to Apply It in an Rmarkdown::Beamer_Presentation
Row Not Consolidating Duplicates in R When Using Multiple Months in Date Filter
R: Formatting Plotly Hover Text
Ddply + Summarize for Repeating Same Statistical Function Across Large Number of Columns
How to Compare Two Factors with Different Levels
Plotting Wide Format Data Using R Ggplot
Convert String of Anyformat into Dd-Mm-Yy Hh:Mm:Ss in R
How to Replace Lower/Upper Triangular Elements of a Matrix
Results Transposed with R Apply
Join Two Data Tables and Use Only One Column from Second Dt