Gebruik mssql-python met DuckDB

DuckDB is een in-process SQL-analyse-engine die Apache Arrow-tabellen direct kan opvragen zonder data te kopiëren. Door DuckDB te combineren met de mssql-python-driver kun je:

  • Voer analytische SQL-queries uit op Microsoft SQL-resultatensets zonder data in pandas of Polars te laden.
  • Doorzoek Arrow-tabellen in het geheugen zonder kopieer-overhead.
  • Verbind Microsoft SQL-gegevens met lokale bestanden (CSV, Parquet, JSON) in één DuckDB-query.
  • Exporteer Microsoft SQL-gegevens naar Parquet, CSV of andere formaten via DuckDB.

Prerequisites

  • Python 3.10 of hoger.
  • De mssql-python, duckdb, en pyarrow pakketten. Installeer alles met pip install mssql-python duckdb pyarrow.
  • Installeer eenmalige vereisten voor het besturingssysteem. Windows-gebruikers kunnen deze stap overslaan. Voor volledige platformdetails, zie Install mssql-python.
    apk add libtool krb5-libs krb5-dev

Een SQL-database maken

Maak een SQL-database aan of maak verbinding met een van de volgende platforms:

De voorbeelden in dit artikel zoeken de AdventureWorks voorbeelddatabase op. Als je het nog niet hebt, bekijk dan de voorbeelddatabases van AdventureWorks.

Afhankelijkheden installeren

pip install mssql-python duckdb pyarrow

Zoek Microsoft SQL-gegevens op met DuckDB

De basisworkflow is: voer een query uit met mssql-python, haal de resultaten op als een Arrow-tabel, en vraag die Arrow-tabel vervolgens op met DuckDB SQL.

Basispatroon

Begin met het opzetten van een verbinding en het ophalen van gegevens als een Arrow-tabel.

import duckdb
import mssql_python

conn = mssql_python.connect(
    "Server=<server>.database.windows.net;"
    "Database=<database>;"
    "Authentication=ActiveDirectoryDefault;"
    "Encrypt=yes"
)
cursor = conn.cursor()

# Fetch Microsoft SQL data as Arrow
cursor.execute("SELECT * FROM Production.Product WHERE ListPrice > 0")
products = cursor.arrow()

# Query the Arrow table with DuckDB
result = duckdb.sql("""
    SELECT Color, COUNT(*) AS ProductCount, AVG(ListPrice) AS AvgPrice
    FROM products
    GROUP BY Color
    ORDER BY ProductCount DESC
""")
print(result.fetchdf())

DuckDB verwijst naar de products Arrow-tabel met de Python-variabelenaam. Er worden geen gegevens gekopieerd in de opslag van DuckDB.

Aggregaat en filter

Gebruik de SQL van DuckDB om Arrow-gegevens te groeperen en te aggregeren.

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
orders = cursor.arrow()

# Top customers by total spend
top_customers = duckdb.sql("""
    SELECT
        CustomerID,
        COUNT(*) AS OrderCount,
        SUM(TotalDue) AS TotalSpent,
        AVG(TotalDue) AS AvgOrderValue
    FROM orders
    GROUP BY CustomerID
    HAVING SUM(TotalDue) > 10000
    ORDER BY TotalSpent DESC
    LIMIT 20
""")
print(top_customers.fetchdf())

Sluit je aan bij meerdere Microsoft SQL-resultaten

Haal meerdere tabellen op van Microsoft SQL en voeg ze toe in DuckDB zonder een cross-server query te schrijven.

# Fetch two tables
cursor.execute("SELECT * FROM Production.Product")
products = cursor.arrow()

cursor.execute("SELECT * FROM Production.ProductSubcategory")
subcategories = cursor.arrow()

# Join in DuckDB
result = duckdb.sql("""
    SELECT
        s.Name AS Subcategory,
        COUNT(*) AS ProductCount,
        ROUND(AVG(p.ListPrice), 2) AS AvgPrice
    FROM products p
    JOIN subcategories s ON p.ProductSubcategoryID = s.ProductSubcategoryID
    GROUP BY s.Name
    ORDER BY AvgPrice DESC
""")
print(result.fetchdf())

Sluit Microsoft SQL-data aan met lokale bestanden

DuckDB kan CSV-, Parquet- en JSON-bestanden rechtstreeks lezen. Combineer SQL Server-gegevens met lokale bestanden in één enkele query.

Samenvoegen met een CSV-bestand

Laad een CSV-bestand en voeg het toe met data van Microsoft SQL.

import csv
from pathlib import Path

cursor.execute("SELECT CustomerID, PersonID FROM Sales.Customer")
customers = cursor.arrow()

csv_path = Path("customer_regions.csv")
with csv_path.open("w", newline="", encoding="utf-8") as file:
    writer = csv.writer(file)
    writer.writerow(["CustomerID", "Region", "Segment"])
    writer.writerows([
        (1, "West", "Premium"),
        (2, "East", "Standard"),
        (3, "Central", "Basic"),
    ])

try:
    result = duckdb.sql("""
        SELECT c.CustomerID, c.PersonID, f.Region, f.Segment
        FROM customers c
        JOIN read_csv_auto('customer_regions.csv') f ON c.CustomerID = f.CustomerID
    """)
    print(result.fetchdf())
finally:
    csv_path.unlink(missing_ok=True)

Sluit aan bij een Parquet-bestand

Laad een Parquet-bestand en voeg het aan met data van Microsoft SQL.

from pathlib import Path

import pyarrow as pa
import pyarrow.parquet as pq

cursor.execute("SELECT ProductID, Name, ListPrice FROM Production.Product")
products = cursor.arrow()

parquet_path = Path("order_history.parquet")
order_history = pa.table({
    "ProductID": [1, 2, 680],
    "OrderDate": ["2024-06-01", "2024-03-15", "2024-01-10"],
    "Quantity": [10, 5, 3],
})
pq.write_table(order_history, parquet_path)

try:
    result = duckdb.sql("""
        SELECT p.Name, p.ListPrice, h.OrderDate, h.Quantity
        FROM products p
        JOIN read_parquet('order_history.parquet') h ON p.ProductID = h.ProductID
        WHERE h.OrderDate >= '2024-01-01'
    """)
    print(result.fetchdf())
finally:
    parquet_path.unlink(missing_ok=True)

Exporteer Microsoft SQL-gegevens

Gebruik de COPY DuckDB-instructie om Microsoft SQL-gegevens te exporteren naar verschillende bestandsformaten.

Exporteren naar Parquet

Exporteer gegevens naar Apache Parquet-formaat.

cursor.execute("SELECT * FROM Production.Product")
products = cursor.arrow()

duckdb.sql("COPY products TO 'products.parquet' (FORMAT PARQUET)")

Exporteren naar CSV

Exporteer gegevens naar een komma-gescheiden waardenbestand:

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
orders = cursor.arrow()

duckdb.sql("COPY orders TO 'orders.csv' (FORMAT CSV, HEADER)")

Gepartitioneerde Parquet exporteren

Exporteer data naar gepartitioneerde Parquet-bestanden voor gedistribueerde analyse:

import shutil
from pathlib import Path

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
orders = cursor.arrow()

output_dir = Path("sales_data")
shutil.rmtree(output_dir, ignore_errors=True)

duckdb.sql("""
    COPY (SELECT *, YEAR(OrderDate) AS OrderYear FROM orders)
    TO 'sales_data'
    (FORMAT PARQUET, PARTITION_BY (OrderYear))
""")

Stroom grote resultaatsets

Voor grote datasets kun je het gebruik arrow_reader() gebruiken om data in streamingbatches te verwerken zonder alle rijen tegelijk in het geheugen te laden:

cursor.execute("SELECT * FROM Production.TransactionHistory")
reader = cursor.arrow_reader(batch_size=50000)

# Process each batch with DuckDB
total_rows = 0
for batch in reader:
    result = duckdb.sql("""
        SELECT ProductID, SUM(ActualCost) AS TotalCost
        FROM batch
        GROUP BY ProductID
    """)
    total_rows += batch.num_rows
    print(f"Processed {total_rows} rows")

Verzamel streamingresultaten

Om over alle batches te aggregeren, registreer je elke batch in een persistente DuckDB-verbinding en verzamel je successief resultaten.

cursor.execute("SELECT * FROM Production.TransactionHistory")
reader = cursor.arrow_reader(batch_size=50000)

duck = duckdb.connect()
duck.execute("CREATE TABLE transactions (ProductID INT, ActualCost DOUBLE, Quantity INT)")

for batch in reader:
    duck.execute("INSERT INTO transactions SELECT ProductID, ActualCost, Quantity FROM batch")

# Query the accumulated data
result = duck.sql("""
    SELECT ProductID, SUM(ActualCost) AS TotalCost, SUM(Quantity) AS TotalQty
    FROM transactions
    GROUP BY ProductID
    ORDER BY TotalCost DESC
    LIMIT 10
""")
print(result.fetchdf())
duck.close()

Tips voor prestaties

Laat Microsoft SQL het zware werk doen

Microsoft SQL is sneller voor filtering, joins en aggregaties dan het ophalen van alle ruwe data via de wire. Gebruik DuckDB voor secundaire analyse van al opgehaalde resultatensets, niet als vervanging voor SQL Server-queryoptimalisatie.

# Suboptimal: Pull all rows, filter in DuckDB
cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
orders = cursor.arrow()
result = duckdb.sql("SELECT * FROM orders WHERE TotalDue > 1000")

# Better: Filter in Microsoft SQL, analyze in DuckDB
cursor.execute("SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue > 1000")
orders = cursor.arrow()
result = duckdb.sql("SELECT CustomerID, SUM(TotalDue) FROM orders GROUP BY CustomerID")

Gebruik Arrow voor alle leesbewerkingen

Pijl-gebaseerde overdracht voorkomt het creëren van tussenliggende Python-objecten, wat het geheugengebruik vermindert en de doorvoer verbetert. Geef de voorkeur cursor.arrow() boven handmatige rij-voor-rij conversie bij het doorgeven van data aan DuckDB.

Gebruik streaming voor grote datasets

Gebruik je voor resultaatsets die groter zijn dan het beschikbare geheugen arrow_reader() met een batch_size-parameter om gegevens incrementeel te verwerken.

Verwante onderwerpen

  • Last updated on