Search code examples
pythonsql-serverazure

How to connect a microsoft SQL Server database to Open Data Discovery (odd-odd-platform)?

I'm trying to connect my SQL Server database to the Open Data Discovery (ODD) platform. Here are the steps I've followed so far:

  1. I deployed a SQL Server database using Azure Data Studio by following this guide.

  2. I created the database: `CREATE DATABASE BikeStores;

  3. I created the tables and loaded the data according to this tutorial.

  4. I checked the IP and port with the following query:` Azure Data Studio

  5. I verified the connection to the database using Python:

import pyodbc

# Connection parameters
server = 'localhost'
database = 'BikeStores'
username = 'sa'
password = 'my_password'

# Connection string
conn_str = (
    f'DRIVER={{ODBC Driver 17 for SQL Server}};'
    f'SERVER={server};'
    f'DATABASE={database};'
    f'UID={username};'
    f'PWD={password}'
)

try:
    # Connect to the database
    with pyodbc.connect(conn_str) as conn:
        cursor = conn.cursor()
        
        # Query to get table names
        query = """
        SELECT TABLE_SCHEMA, TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_TYPE = 'BASE TABLE'
        """
        cursor.execute(query)
        
        # Fetch the data
        tables = cursor.fetchall()
        
        # Output the table names
        for table in tables:
            schema, name = table
            print(f'{schema}.{name}')

except pyodbc.Error as e:
    print(f'Connection error: {e}')

The output was:

enter image description here

  1. Following the ODD setup guide, I cloned the ODD Platform repository:

git clone https://github.com/opendatadiscovery/odd-platform

cd odd-platform

docker-compose -f docker/demo.yaml up -d odd-platform-enricher

  1. I opened the ODD interface at http://localhost:8080/management/datasources, then added a collector at http://localhost:8080/management/collectors:

enter image description here

  1. I copied the token and pasted it into the odd-platform/docker/config/collector_config.yaml file as follows:

enter image description here

  1. I started the collector with the following command:

docker-compose -f docker/demo.yaml up -d odd-collector

  1. Tried restarting the collector several times:

docker-compose -f docker/demo.yaml restart odd-collector

Problem:

The ODD Platform does not detect the connected database, and the data is not loading. What steps did I miss or do incorrectly?

Solution

  • I resolved my issue by changing the IP address (172.18.0.1) enter image description here and updating the collector_config.yaml file. enter image description here