Troubleshooting ODBC Driver Issue in DataHub Ingestion on Windows with Docker

ingestion
1

Original Slack Thread

Hello, I´m having problems with ingestion I´m using a local Docker version on Windows but I got the following error

(Background on this error at: <https://sqlalche.me/e/14/dbapi>)```
```~~~~ Ingestion Logs ~~~~
Obtaining venv creation lock...
Acquired venv creation lock
venv is already set up
venv setup time = 0 sec
This version of datahub supports report-to functionality
+ exec datahub ingest run -c /tmp/datahub/ingest/65c2b7fa-7d6f-4385-b133-94fcaf4d3a7e/recipe.yml --report-to /tmp/datahub/ingest/65c2b7fa-7d6f-4385-b133-94fcaf4d3a7e/ingestion_report.json
[2024-08-28 03:11:50,674] INFO     {datahub.cli.ingest_cli:149} - DataHub CLI version: 0.14.0
[2024-08-28 03:11:50,680] INFO     {datahub.ingestion.run.pipeline:255} - No sink configured, attempting to use the default datahub-rest sink.
[2024-08-28 03:11:50,784] INFO     {datahub.ingestion.run.pipeline:272} - Sink configured successfully. DataHubRestEmitter: configured to talk to <http://datahub-gms:8080>
[2024-08-28 03:11:52,924] ERROR    {datahub.entrypoints:218} - Command failed: Failed to configure the source (mssql-odbc): (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server' : file not found (0) (SQLDriverConnect)")```
I have the Driver Installed and Im using UI from Chrome, and my receipe is:

source:
type: mssql-odbc
config:
host_port: http://xxxxxxxxx.windows.net|xxxxxxxxx.windows.net
database: xxxxxx
username: xxxxx
password: ‘xxxxxxx’
use_odbc: ‘True’
uri_args:
driver: ‘ODBC Driver 18 for SQL Server’
Encrypt: yes
TrustServerCertificate: Yes
ssl: ‘True’```attachment

2

Hey there! :wave: Make sure your message includes the following information if relevant, so we can help more effectively!

  1. Are you using UI or CLI for ingestion?
  2. Which DataHub version are you using? (e.g. 0.12.0)
  3. What data source(s) are you integrating with DataHub? (e.g. BigQuery)

<@U06TM7M40RK> Can you help with the questions in this thread?

:information_source: Please continue to tag RunLLM (e.g <@U06TM7M40RK>) to have it continue generating responses! We’re experimenting with a new AI assistant for questions in the <#CUMUWQU66> channel to help you track down helpful resources more quickly. Keep in mind that responses may not always be spot-on; feel free to ask it follow-up questions and don’t forget to provide its responses a :thumbsup: or :thumbsdown: so other folks in the Community know if you’re still looking for help on the topic and to help us improve the assistant!

3

To address the issue you’re encountering with the ODBC driver for SQL Server in your DataHub ingestion setup, it seems that the driver is not being found by the system. Here are some steps to troubleshoot and resolve the problem:

  1. Verify ODBC Driver Installation:
    Ensure that the ODBC Driver 18 for SQL Server is correctly installed on your system. You can check the installation by running the following command in your terminal:

    odbcinst -j

    This command will show the configuration files used by unixODBC. Make sure the driver is listed in the odbcinst.ini file.

  2. Update Dockerfile:
    If the driver is not installed in your Docker container, you need to update your Dockerfile to include the installation of the ODBC driver. Here is an example Dockerfile that installs the ODBC driver:

    FROM python:3.9-slim

# Install unixODBC and the ODBC Driver for SQL Server
RUN apt-get update && \
    apt-get install -y curl apt-transport-https gnupg && \
    curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
    curl https://packages.microsoft.com/config/debian/10/prod.list > /etc/apt/sources.list.d/mssql-release.list && \
    apt-get update && \
    ACCEPT_EULA=Y apt-get install -y msodbcsql18 unixodbc-dev && \
    apt-get clean && \
    rm -rf /var/lib/apt/lists/*

# Install DataHub
RUN pip install acryl-datahub

# Set environment variables
ENV ODBCINI=/etc/odbc.ini
ENV ODBCSYSINI=/etc

# Copy your DataHub recipe
COPY recipe.yml /app/recipe.yml

# Run DataHub ingestion
CMD ["datahub", "ingest", "-c", "/app/recipe.yml"]

  3. Update Ingestion Recipe:
    Ensure your ingestion recipe is correctly configured. Here is an example configuration for the mssql-odbc source type:

    source:
  type: mssql-odbc
  config:
    host_port: 'xxxxxxxxx.windows.net'
    database: 'xxxxxx'
    username: 'xxxxx'
    password: 'xxxxxxx'
    use_odbc: 'True'
    uri_args:
      driver: 'ODBC Driver 18 for SQL Server'
      Encrypt: 'yes'
      TrustServerCertificate: 'Yes'
      ssl: 'True'

  4. Run Docker Container:
    Build and run your Docker container with the updated Dockerfile:

    docker build -t datahub-ingestion-mssql .
docker run --rm datahub-ingestion-mssql

Would you like more detailed guidance on any of these steps?

Sources: