Skip to main content

SQL Server

Use the SQL Server replication guide when you need to read tables or queries from SQL Server and write the results to MotherDuck. The guide covers Python, pyodbc, SQL Server authentication, and loading dataframe results into MotherDuck.

How it works with MotherDuck​

  1. Connect to SQL Server with the Microsoft ODBC driver and pyodbc.
  2. Read a SQL Server table or query result into a dataframe.
  3. Connect to MotherDuck from Python and persist the dataframe as a MotherDuck table.

Load on a schedule with a Flight​

A Flight is Python that MotherDuck schedules and runs next to your data. Use one instead of running the copy by hand when the load should repeat on a cron, retry on a transient failure, and keep a run history.

There is no DuckDB sqlserver extension, so a Flight runs the same pyodbc read as the replication guide and registers the result on an md: connection:

import os

import duckdb
import pandas as pd
import pyodbc


def main():
    connection_string = (
        "DRIVER={ODBC Driver 17 for SQL Server};"
        f"SERVER={os.environ['SQLSERVER_HOST']},1433;"
        f"DATABASE={os.environ['SQLSERVER_DATABASE']};"
        f"UID={os.environ['SQLSERVER_USER']};"
        f"PWD={os.environ['SQLSERVER_PASSWORD']};"
    )

    connection = pyodbc.connect(connection_string)
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT * FROM Production.BillOfMaterials")
        columns = [column[0] for column in cursor.description]
        bom = pd.DataFrame.from_records(cursor.fetchall(), columns=columns)
    finally:
        connection.close()

    md = duckdb.connect("md:")
    md.register("bom", bom)
    md.execute("CREATE OR REPLACE TABLE my_db.main.bill_of_materials AS SELECT * FROM bom")


if __name__ == "__main__":
    main()

The SQL Server password is a credential, so keep it in a Flight secret rather than in the Flight's source or config. Each PARAMS key is injected into the run as an environment variable, which is what the code above reads:

CREATE SECRET sqlserver_creds IN MOTHERDUCK (
    TYPE FLIGHTS,
    PARAMS MAP {
        'SQLSERVER_HOST': '<host>',
        'SQLSERVER_DATABASE': '<database>',
        'SQLSERVER_USER': '<user>',
        'SQLSERVER_PASSWORD': '<password>'
    }
);

Create the Flight with MD_CREATE_FLIGHT, passing the Python above as source_code, pyodbc and pandas in requirements_txt, flight_secret_names := ['sqlserver_creds'], and a schedule_cron for the cadence you want.

note

pyodbc needs the Microsoft ODBC driver, which is not preinstalled in the Flight runtime. Install it at the start of the run with subprocess and apt-get, as described in Beyond Python.

Related content​