odbc (iodbc or unixodbc) with docker to connect to BQ

[dougfir]

This post does not involve R. I hope that's OK and doesn't breach any rules? I'm nevertheless posting here because:

• I found related posts on this forum during my searching for answers to my current problem e.g. here.
• The documentation I'm reading through, though from another co, is still hosted on rstudio
• I've had a positive experience posting on here in the past, especially relating to odbc and docker, so I'm hopeful
• I've exhausted other avenues of research, including SO

My question...

I am attempting to set up a docker container with an ODBC connection to Google Big Query (BQ). When I enter into my running container, it looks like isql (unixodbc) or iodbctest (iodbc) cannot even see the DSN. I therefore think the issue has more to do with my set up (directories, home, env vars) rather than the contents of the odbc.ini file.

Here is my Docker image. IN the same directory is an odbc.ini file as well as the tarball from Google. I double clicked the tarball which contains two more tarballs, a grabbed the 64 bit tz file and placed in same directory as Dockerfile:

### Docs https://storage.googleapis.com/simba-bq-release/odbc/Simba%20Google%20BigQuery%20ODBC%20Connector%20Install%20and%20Configuration%20Guide.pdf ###

FROM ubuntu:18.04
RUN apt-get update
RUN apt-get install -y unzip vim
RUN apt-get -y install glibc-source

# tried back n forth both unixodbc and iodbc here
# RUN apt-get -y install odbcinst1debian2 libodbc1 odbcinst unixodbc curl
RUN apt-get install -y iodbc

# Environment variables
ENV LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/simba/googlebigqueryodbc/lib
# ENV ODBCINI=/root/.odbc.ini

COPY SimbaODBCDriverforGoogleBigQuery64_2.4.6.1015.tar.gz /SimbaODBCDriverforGoogleBigQuery64_2.4.6.1015.tar.gz
COPY service.json /service.json
COPY odbc.ini /root/.odbc.ini

# Install the simba odbc connector
RUN tar --directory=/opt -zxvf SimbaODBCDriverforGoogleBigQuery64_2.4.6.1015.tar.gz

# According to docs, this dir auto created during install above but it wasn't, create the dir and move extract to it manually
RUN mkdir /opt/simba
RUN mv /opt/SimbaODBCDriverforGoogleBigQuery64_2.4.6.1015 /opt/simba/googlebigqueryodbc

Now, when I build then run this container, I want to test the connection. I tried e.g.:

isql BQODBC
[ISQL]ERROR: Could not SQLDriverConnect

Same result when adding -v flag.

I tried also testing the connection with iodbctest command:

root@7ba66f24c078:~# iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0812.0326

Enter ODBC connect string (? shows list): ?

DSN                              | Driver                                  
------------------------------------------------------------------------------

My odbc.ini file, added to home in Dockerfile with COPY odbc.ini /root/.odbc.ini contains a DSN of the form:

[BQODBC] 
Email=some-email@some-email.iam.gserviceaccount.com 
KeyFilePath=/service.json
Driver=/opt/simba/googlebigqueryodbc/lib/libgooglebigqueryodbc_sb64.so
OAuthMechanism=0
Catalog=ourdata-gcp
LogLevel=5
LogPath=/odbclog
AllowLargeResults=0
LargeResultsDataSetId=_bqodbc_temp_tables
LargeResultsTempTableExpirationTime=3600000

Therefore, expectation was to see BQODBC listed as a DSN with iodbctest.

I chose to add odbc.ini perCOPY odbc.ini /root/.odbc.ini because within a running container, if I cd ~ then pwd I see that this is where I am. But I'm not sure if that's relevant or not.

How can I correctly configure an ubuntu container with odbc, in this case Simba's Big Query driver. Does my Dockerfile otherwise look sound? I could not find any examples of a Dockerfile configured for this anywhere.

[rstub]

• What is the output of ldd /opt/simba/googlebigqueryodbc/lib/libgooglebigqueryodbc_sb64.so?
• Have you tried using the system-wide default location, i.e. COPY odbc.ini /etc/odbc.ini?
• Personally I always use unixodbc, probably because the odbc package in R depends on it.

[dougfir]

Hi @rstub thanks for the suggestion. My Dockerfile has since changed. I managed to get a connection! I followed this post on GH and my repo now looks like below:

• Dockerfile
• odbc.ini
• service.json
• simba.googlebigqueryodbc.ini

My Dockerfile:

### Docs https://storage.googleapis.com/simba-bq-release/odbc/Simba%20Google%20BigQuery%20ODBC%20Connector%20Install%20and%20Configuration%20Guide.pdf ###

FROM ubuntu:18.04

# Env vars
ENV ODBCINI=/opt/simba/setup/odbc.ini
ENV SIMBAGOOGLEBIGQUERYODBCINI=/opt/simba/setup/simba.googlebigqueryodbc.ini
ENV ODBCSYSINI=/opt/simba/setup

# install libraries
RUN apt-get update
RUN apt-get install -y unzip vim wget odbcinst1debian2 libodbc1 odbcinst unixodbc

# Get the driver
RUN wget https://storage.googleapis.com/simba-bq-release/odbc/SimbaODBCDriverforGoogleBigQuery_2.2.2.1007-Linux.tar.gz

# Directory set up
RUN mkdir /opt/simba && tar zxvf SimbaODBCDriverforGoogleBigQuery_2.2.2.1007-Linux.tar.gz -C /opt/simba --strip-components 1
RUN mkdir /opt/simba/driver && tar zxvf /opt/simba/SimbaODBCDriverforGoogleBigQuery64_2.2.2.1007.tar.gz -C /opt/simba/driver --strip-components 1
RUN mv /opt/simba/GoogleBigQueryODBC.did /opt/simba/driver/lib/

# housekeeping
RUN rm /opt/simba/SimbaODBCDriverforGoogleBigQuery*
RUN rm -rf /opt/simba/docs

# auth & config
COPY service.json /service.json
COPY odbc.ini /opt/simba/setup/odbc.ini
COPY simba.googlebigqueryodbc.ini /opt/simba/setup/simba.googlebigqueryodbc.ini

It works! Uses unixodbc too. I wish I fully understood it, one line that confused me was this:

RUN mv /opt/simba/GoogleBigQueryODBC.did /opt/simba/driver/lib/

I don't know why I need to move that file but when I tested without it didn't connect.

With this Dockerfile I use the system wide /etc location for the odbc file, but I did try that previously with the set up I had when posting. Actually, I really wish I understood what the magic bullet was that solved this with this approach, am going to spend some time on it today trying to understand it.

Thanks for taking the time to post some suggestions!