Access SQL Server from Nebari

[empollon9]

All,

It might be a dumb question, but I have not seen it anywhere. If you need to import your data from a DB (I use Microsoft SQL Server), you need to install the drivers. I am using pyodbc. example below:

import pyodbc
import pandas as pd

server = 'servername'
database = 'yourDB'
username = 'myuser'
password = 'mysecret'

conn = pyodbc.connect(
                'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server + '.colchester.local' +
                ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)


sql = 'SELECT * FROM MyTable'
params = None
result = pd.read_sql_query(sql, conn, params=p)

print(result.head(3)) # Display top three rows

This fails as

Traceback (most recent call last):
  File "/home/yournebariuser/testconnection.py", line 10, in <module>
    conn = pyodbc.connect(
           ^^^^^^^^^^^^^^^
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

I tried to install the driver in Nebari following Microsoft guide

But I found that you cannot sudo from a bash terminal either on VS Code or JupyterHub

sudo apt-get update
bash: sudo: command not found

[viniciusdc]

Hi @empollon9, Based on what I understood about your issue, you installed a package from Conda that requires a few drivers. It might be more straightforward for you to build on top of our jupytelab docker image and install the appropriate drivers for the Ubuntu platform as outlined here

[viniciusdc]

Then you can reference your image in the nebari-config:

default_images:
  jupyterhub: "quay.io/nebari/nebari-jupyterhub:2024.12.1"
  jupyterlab: "quay.io/nebari/nebari-jupyterlab:2024.12.1"

changing the image repository to your own

[empollon9]

Hi @viniciusdc, it is not really a conda package, but the DataBase Driver for Microsoft SQL Server. This will be used accross the data platform to grab data from our own database. I would have expected this to be an standard thing to do an a data platform.

What you propose seems like a work around, but I am not sure I want to do that, as I will have to do this accross all environments we can ever configure in conda. Is there a more straightforward way to install the database drivers?

[empollon9]

Just to rephrase the question, maybe it was not clear. I need to install ODBC drivers to grab data from our Data WareHouse. I tried to install them through sudo on the terminal on VS Code, but i get this message:

sudo apt-get update
bash: sudo: command not found

How are you supposed to install drivers on the server?

[Adam-D-Lewis]

Hi @empollon9, I believe we are all on the same page, but I'll expand a bit to try to make things clearer and give some rationale for why things are the way they are currently.

The jupyterlab servers that spin up are docker images and do not have sudo access. Even if they did have sudo access, you would still be required to re-install the drivers each time you start up a jupyterlab server since the fresh docker image would not retain the prior changes. The only files that are retained across bootups are the user home dir including conda envs and the shared directory. I agree the way to address this in Nebari currently is to do what @viniciusdc suggested. A sample docker file might look something like the below though I haven't tested it and it would certainly need some tweaking.

FROM <the nebari base jupyterlab image>
USER 0
RUN sudo apt update
RUN sudo apt install <drivers-you-want>
USER 1000

then you'd need to store it in some image registry like dockerhub, and use it by specifying the default_images.jupyterlab key in the nebari config as @viniciusdc mentioned. Then when you spin up a jupyterlab server, it will have the drivers you need. One downside of this approach is you'd need to build a new image based on the new nebari-jupyterlab image with each Nebari upgrade. There have been issues and discussions around making this more user friendly, but nothing has materialized at the moment.

I believe another alternative (maybe better in this case?) would be to package the drivers in conda-forge and then you could add them to your conda envs as needed without changing the docker image, but I'm less experienced with packaging.

[empollon9]

Hi @Adam-D-Lewis , yes you are right, I was reading all day today and I came to the same conclusion. I understood that from the nebari documentation here, that this is the way forward. And as you said, this might be right for system packages, but for ODBC drivers, seems like a pain we need to pay, as what is a data science platform without "data".

I also found the documentation very slim in this regard, and also on the custom docker images. Maybe once I get going with this, I would volunteer to extend for the benefit of everyone coming to the same blocking wall.

[dharhas]

@empollon9

There is a balance on what you include in a base docker image and what you don't. The more things you add, the larger the image gets and then there are more things to keep secure and also starting up servers takes longer. Not everyone uses MSSQL and including MSSQL drivers by default isn't a request that anyone has made yet.

Most database drivers can be installed via conda so we don't need to include them in the base image. MSSQL isn't available on conda-forge because of Microsoft licensing issues so it needs to be installed in the image.

Longer term, we have discussed ways to make building and using custom docker images easier, but getting there requires resources.