#rodbc

[mjliu]

Science Log 10.59 *date 1504.24 - Connect to hive in R using RODBC

First of all, no RODBC package (binary) available for Yosemite, what need to be done, is download the most recent one from CRAN, then 

R CMD INSTALL /path/to/RODBC/download

then to connect, using odbcDriverConnect to point to the lib

odbcDriverConnect('Driver=/opt/hortonworks/universal/libhortonworkshiveodbc.dylib;host=enterprise;port=1170;user=spock;password=llap')

Now feels fine.

🖖 

[mjliu]

This is what puzzled me to make RODBC working on my Mac

impala <- odbcDriverConnect("Driver=/opt/cloudera/impalaodbc/lib/universal/libclouderaimpalaodbc.dylib;host=192.168.26.135;port=21050")

see put the driver directly in the connection string and do NOT use any curly brakets 

Same applies to FreeTDS driver and other hadoop hive drivers

[mjliu]

The unintuitive Could Not SQLExecDirect RODBC Error and solution

So I've got this non sense error while using sqlSave

[RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE ... blah blah blah

Here is the short story, and it is important to talk about the setup, I'm using RODBC and FreeTDS to connect to the SQL Azure database. Remember it is SQL Azure database. First, it does not play nice with the database without a cluster key, or a key, so what need to do is addPK = T or longer version addPK = TRUE

Solution #1. two steps approach

This will help create the proper table structure, however it'll still fail you. Don't worry. Now what we need to do is to append the data sqlSave(ch, USArrests, addPK = T) It will tell you the error, then what you need to do next is to append the data sqlSave(ch, USArrests, tablename = 'USArrests', append = T) Now you ready to hail or shout hurray?!

Solution #2.

That is not me, I'm lazy, it should be done in one command

That solution does not seem to be making sense, here is the better version

sqlSave(ch, cust, tablename="dbo.customers", safer=F, addPK=T)

Safer = F does the trick. not sure if this is the bug from FreeTDS or RODBC sqlSave function. 

======

If you actually read this far, congratulations, you found the solution - INSTALL Microsoft SQL Server ODBC Driver

http://www.microsoft.com/en-us/download/details.aspx?id=36437 

[mjliu]

So I had some fun with Windows Azure VM and SQL Azure and of coz R

The Goal is to setup RStudio server and have it work with SQL Azure via RODBC

Step 1. Instal R

   26  sudo apt-get update

   28  sudo apt-get install r-base

Step 2. Install RStudio Server

   33  sudo apt-get install gdebi-core

   34  sudo apt-get install libapparmor1

   36  wget http://download2.rstudio.org/rstudio-server-0.98.1062-amd64.deb

   37  sudo gdebi rstudio-server-0.98.1062-amd64.deb

Step 3. Instal ggplot2, unixODBC and RODBC

   47  sudo apt-get install r-cran-ggplot2

   51  sudo apt-get install unixODBC

   52  sudo apt-get install r-cran-rodbc

Step 4. Install FreeTDS and configure the DSN to point to SQL Azure database

  123  sudo apt-get install tdsodbc

  127  sudo ln -s /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so /usr/lib/libtdsodbc.so

  128  sudo ln -s /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so /usr/lib/libtdsS.so

  130  odbcinst -j

  132  odbcinst -q -d

  154  tsql -H <azureserver>.database.windows.net -p 1433 -U mike -D <databasename>

  155 isql -v sqlserver mike pass 

here are the ini files

$ cat /etc/odbcinst.ini  # or ~/.odbcinst.ini

[FreeTDS]

Driver = /usr/lib/libtdsodbc.so

Setup  = /usr/lib/libtdsS.so

$ cat /etc/odbc.ini  # or ~/.odbc.ini

[sqlserver]

Description = SQL Azure

Driver = FreeTDS

Server = <azureserver>.database.windows.net

Database = <database name>

Port = 1433

TDS_Version = 8.0