Använd mssql-python med DuckDB

DuckDB är en in-process SQL-analysmotor som kan fråga Apache Arrow-tabeller direkt utan att kopiera data. Genom att kombinera DuckDB med mssql-python-drivrutinen kan du:

  • Kör analytiska SQL-frågor på Microsoft SQL-resultatuppsättningar utan att ladda data i pandas eller Polars.
  • Fråga Arrow-tabeller i minnet med nollkopieringsöverhead.
  • Koppla ihop Microsoft SQL-data med lokala filer (CSV, Parquet, JSON) i en enda DuckDB-fråga.
  • Exportera Microsoft SQL-data till Parquet, CSV eller andra format via DuckDB.

Förutsättningar

  • Python 3.10 eller senare.
  • Paketen mssql-python, duckdb och pyarrow. Installera allt med pip install mssql-python duckdb pyarrow.
  • Installera operativsystemsspecifika engångsförutsättningar. Windows-användare kan hoppa över detta steg. För fullständiga plattformsdetaljer, se Installera mssql-python.
    apk add libtool krb5-libs krb5-dev

Skapa en SQL-databas

Skapa eller koppla till en SQL-databas på en av följande plattformar:

Exemplen i denna artikel gör frågor mot AdventureWorks exempeldatabasen. Om du inte redan har det, se AdventureWorks exempeldatabaser.

Installera beroenden

pip install mssql-python duckdb pyarrow

Sök Microsoft SQL-data med DuckDB

Det grundläggande arbetsflödet är: kör en fråga med mssql-python, hämta resultaten som en Arrow-tabell, och fråga sedan den Arrow-tabellen med DuckDB SQL.

Grundmönster

Börja med att etablera en anslutning och hämta data som en piltabell.

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 refererar till products Arrow-tabellen med dess Python-variabelnamn. Ingen data kopieras till DuckDB:s lagring.

Aggregera och filtrera

Använd DuckDB:s SQL för att gruppera och aggregera Arrow-data.

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())

Gå med i flera Microsoft SQL-resultat

Hämta flera tabeller från Microsoft SQL och koppla ihop dem i DuckDB utan att skriva en serveröverskridande fråga.

# 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())

Koppla ihop Microsoft SQL-data med lokala filer

DuckDB kan läsa CSV-, Parquet- och JSON-filer direkt. Kombinera SQL Server-data med lokala filer i en enda fråga.

Ansluta till en CSV-fil

Ladda en CSV-fil och koppla ihop den med data från 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)

Sammanfoga med en Parquet-fil

Ladda en Parquet-fil och koppla ihop den med data från 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)

Exportera Microsoft SQL-data

Använd DuckDB:s COPY sats för att exportera Microsoft SQL-data till olika filformat.

Exportera till Parquet

Exportera data till Apache Parquet-format.

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

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

Exportera till CSV

Exportera data till en kommaseparerad värdefil:

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

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

Exporterad partitionerad Parquet

Exportera data till partitionerade Parquet-filer för distribuerad analys:

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))
""")

Strömma stora resultatuppsättningar

För stora datamängder, använd arrow_reader() för att bearbeta data i strömmande batcher utan att ladda in alla rader i minnet samtidigt:

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")

Samla streamingresultat

För att aggregera över alla batcher, registrera varje batch i en persistent DuckDB-anslutning och ackumulera resultat successivt.

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()

Prestandatips

Låt Microsoft SQL ta hand om det tunga arbetet

Microsoft SQL är snabbare för filtrering, sammanfogningar och aggregeringar än att hämta all rådata över nätverket. Använd DuckDB för sekundäranalys av resultatuppsättningar som redan hämtats, inte som ersättning för SQL Server-frågeoptimering.

# 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")

Använd Arrow för alla läsoperationer

Pilbaserad överföring undviker att skapa mellanliggande Python-objekt, vilket minskar minnesanvändningen och förbättrar genomströmningen. Föredra cursor.arrow() framför manuell rad-för-rad-konvertering när du skickar data till DuckDB.

Använd streaming för stora datamängder

För resultatmängder som är större än tillgängligt minne, använd arrow_reader() med en batch_size parameter för att bearbeta data inkrementellt.

Relaterat innehåll

  • Last updated on