Utilisez mssql-python avec DuckDB

DuckDB est un moteur d’analyse SQL en cours de processus qui peut interroger directement les tables Apache Arrow sans copier de données. En combinant DuckDB avec le pilote mssql-python, on peut :

  • Exécutez des requêtes SQL analytiques sur des jeux de résultats Microsoft SQL sans charger les données dans pandas ou Polars.
  • Interrogez des tables Arrow en mémoire sans surcoût de copie.
  • Rejoignez les données SQL Microsoft avec des fichiers locaux (CSV, Parquet, JSON) dans une seule requête DuckDB.
  • Exportez les données SQL de Microsoft vers Parquet, CSV ou d’autres formats via DuckDB.

Logiciels requis

  • Python 3.10 ou version ultérieure.
  • Les mssql-python, duckdb et pyarrow paquets. Installez tout avec pip install mssql-python duckdb pyarrow.
  • Installez les prérequis ponctuels spécifiques au système d'exploitation. Les utilisateurs Windows peuvent sauter cette étape. Pour tous les détails sur la plateforme, voir Installer mssql-python.
    apk add libtool krb5-libs krb5-dev

Créer une base de données SQL

Créer ou connecter une base de données SQL sur l’une des plateformes suivantes :

Les exemples de cet article interrogent la base de données d’exemple AdventureWorks . Si vous ne l’avez pas déjà, consultez les bases de données d’exemple AdventureWorks.

Installer des dépendances

pip install mssql-python duckdb pyarrow

Interroger les données SQL Microsoft avec DuckDB

Le flux de travail de base est : exécuter une requête avec mssql-python, récupérer les résultats sous forme de table de flèches, puis interroger cette table de flèches avec DuckDB SQL.

Motif de base

Commencez par établir une connexion et récupérer les données sous forme de table Arrow.

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 fait référence à la products table Arrow par son nom de variable Python. Aucune donnée n’est copiée dans le stockage de DuckDB.

Agrégat et filtre

Utilisez le SQL de DuckDB pour regrouper et agréger les données Arrow.

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

Rejoindre plusieurs résultats Microsoft SQL

Récupérez plusieurs tables depuis Microsoft SQL et rejoignez-les dans DuckDB sans écrire de requête inter-serveurs.

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

Relier les données SQL Microsoft avec des fichiers locaux

DuckDB peut lire nativement les fichiers CSV, Parquet et JSON. Combinez les données de SQL Server avec des fichiers locaux dans une seule requête.

Associer avec un fichier CSV

Chargez un fichier CSV et rejoignez-le avec les données de 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)

Effectuer une jointure avec un fichier Parquet

Chargez un fichier Parquet et rejoignez-le avec les données de 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)

Exporter les données SQL de Microsoft

Utilisez la déclaration de COPY DuckDB pour exporter les données Microsoft SQL vers différents formats de fichiers.

Exporter vers Parquet

Exportez les données vers le format Apache Parquet.

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

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

Exporter au format CSV

Exportez les données vers un fichier de valeurs séparées par virgules :

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

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

Exporter un fichier Parquet partitionné

Exportez les données vers des fichiers Parquet partitionnés pour l’analyse distribuée :

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

Traiter en flux de grands ensembles de résultats

Pour les grands ensembles de données, utilisez arrow_reader() pour traiter les données en flux sans charger toutes les lignes en mémoire en même temps :

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

Accumuler les résultats en streaming

Pour agréger sur tous les lots, enregistrez chaque lot dans une connexion DuckDB persistante et accumulez les résultats de manière incrémentale.

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

Astuces pour les performances

Laissez Microsoft SQL gérer le gros du travail

Microsoft SQL est plus rapide pour le filtrage, les jointures et les agrégations que de récupérer toutes les données brutes via le fil. Utilisez DuckDB pour l’analyse secondaire des ensembles de résultats déjà récupérés, et non comme un remplacement de l’optimisation des requêtes SQL Server.

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

Utilisez Arrow pour toutes les opérations de lecture

Le transfert basé sur des flèches évite de créer des objets Python intermédiaires, ce qui réduit la consommation de mémoire et améliore le débit. Privilégiez cursor.arrow() plutôt que la conversion manuelle ligne par ligne lors du transfert de données vers DuckDB.

Utilisez le streaming pour de grands ensembles de données

Pour les ensembles de résultats dont la taille dépasse la mémoire disponible, utilisez arrow_reader() avec un paramètre batch_size pour traiter les données de manière incrémentale.

Contenu connexe

  • Last updated on