Pre-requisites
- Make sure DistributedR is setup as per the steps described in previous guide.
- Check whether vertica server is up and accessible. vsql -h 10.0.0.3 -U dbadmin -d test
In this particular case there is vertica database server installed with Vertica-R-Udtf setup. The R runtime is being provided from within vertica hence the environment variables changed as below.
Reference: http://www.vertica.com/wp-content/uploads/2014/06/vRODBC-Installation-Guide.pdf
1. Add the following variables in the .bashrc of root for library linking and source it.
export R_HOME=/opt/vertica/R export DR_HOME=/opt/hp/distributedR export LD_LIBRARY_PATH=/usr/local/lib/:/opt/vertica/lib64/:/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.34.x86_64/jre/lib/amd64/server:$DR_HOME/lib:$DR_HOME/third_party/lib:$DR_HOME/third_party/lib/atomicio/:$LD_LIBRARY_PATH
2. Install using the DistributedR-master/third_party/unixODBC-2.3.1.tar.gz
$ tar -xvzf unixODBC-2.3.1.tar.gz $ cd unixODBC-2.3.1 $ ./configure && make $ make install
3. Install the vRODBC available in the DistributedR-master/ source folder
$ R CMD INSTALL vRODBC/
4. Create the config files for vRODBC
# Create a directory for the config files $ mkdir /home/dbadmin/vRODBC/
5. Add the following content to this folder in vertica.ini
[Driver] DriverManagerEncoding = UTF-16 ODBCInstLib = /usr/local/lib/libodbcinst.so ErrorMessagesPath = /opt/vertica/lib64 LogLevel = 0 LogPath = /tmp
6. Add the following content to this folder in odbc.ini
[Test] Description = vRODBC test client Driver = /opt/vertica/lib64/libverticaodbc.so Database = test Servername = 10.0.0.3 UserName = dbadmin Password = test Port = 5433 ConnSettings = Locale = en_US
7. Use the following environment variables in the users .bashrc and source it
export R_HOME=/opt/vertica/R export JAVA_HOME=/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.34.x86_64/jre export DR_HOME=/opt/hp/distributedR export PATH=/opt/vertica/bin:/opt/vertica/R/bin:$DR_HOME/bin:$PATH export LD_LIBRARY_PATH=/usr/local/lib/:/opt/vertica/lib64/:/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.34.x86_64/jre/lib/amd64/server:/home/dbadmin/JAGS/lib:$DR_HOME/lib:$DR_HOME/third_party/lib:$DR_HOME/third_party/lib/atomicio/:$LD_LIBRARY_PATH # ODBC Configurations export VERTICAINI=/home/dbadmin/vRODBC/vertica.ini export ODBCINI=/home/dbadmin/vRODBC/odbc.ini
8. Open the R console and test the connections using the following script
library(vRODBC) connect <- odbcConnect("Test") segment <- sqlQuery(connect, "SELECT * FROM role") segment odbcClose(connect)
great topic..
Samir
LikeLike