Használd mssql-python-t a DuckDB-vel

A DuckDB egy folyamatban lévő SQL analitikai motor, amely közvetlenül képes lekérdezést végezni Apache Arrow táblákra anélkül, hogy adatokat másolna. A DuckDB és az mssql-python driverrel való kombinálása lehetővé teszi:

  • Fusson analitikus SQL lekérdezéseket Microsoft SQL eredményhalmazokon anélkül, hogy adatokat töltene be pandasba vagy Polarsba.
  • Arrow-táblák lekérdezése a memóriában, másolás nélküli többletterhelés nélkül.
  • Csatlakozzon a Microsoft SQL adataihoz helyi fájlokkal (CSV, Parquet, JSON) egyetlen DuckDB lekérdezésben.
  • Exportálja a Microsoft SQL adatait Parquet, CSV vagy más formátumokba a DuckDB-n keresztül.

Prerequisites

  • Python 3.10 vagy újabb verzió.
  • A mssql-python, duckdb, és pyarrow csomagok. Telepítsd az összeset ezzel: pip install mssql-python duckdb pyarrow.
  • Egyszeri operációs rendszerspecifikus előfeltételek telepítése. A Windows felhasználók ezt a lépést kihagyhatják. A platform teljes részleteiért lásd: Install mssql-python.
    apk add libtool krb5-libs krb5-dev

SQL-adatbázis létrehozása

Létrehozni vagy csatlakozni SQL adatbázishoz az alábbi platformok egyikén:

A cikkben szereplő példák a AdventureWorks mintaadatbázist kérdezik. Ha még nincs meg, nézd meg az AdventureWorks mintaadatbázisokat.

Függőségek telepítése

pip install mssql-python duckdb pyarrow

Microsoft SQL-adatok lekérdezése a DuckDB-vel

Az alapvető munkafolyamat a következő: futtatj egy lekérdezést mssql-python-tal, letöltsd az eredményeket Arrow táblaként, majd lekérdezd az Arrow táblát DuckDB SQL-szel.

Alapminta

Kezdje a kapcsolat létrehozásával és az adatok Arrow-táblaként történő lekérésével.

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

A DuckDB a Arrow táblára a Python változó néven hivatkozikproducts. Semmilyen adat nem másolódik le a DuckDB tárolójába.

Aggregáció és szűrő

Használd a DuckDB SQL-jét az Arrow adatok csoportosítására és összesítésére.

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

Csatlakozz több Microsoft SQL eredményhez

Több táblát is letöltsd a Microsoft SQL-ből, és csatlakoztasd őket a DuckDB-be anélkül, hogy szervereken átívelő lekérdezést írnál.

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

Csatlakozz a Microsoft SQL adatokhoz helyi fájlokkal

A DuckDB képes natívan olvasni CSV, Parquet és JSON fájlokat. Egyesítse az SQL Server adatait helyi fájlokkal egyetlen lekérdezésben.

CSV fájllal való csatlakozás

Tölts be egy CSV fájlt, és csatlakoztasd hozzá a Microsoft SQL adataival.

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)

Csatlakozás Parquet fájllal

Tölts be egy Parquet fájlt, és kösd össze a Microsoft SQL adataival.

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)

Microsoft SQL-adatok exportálása

Használd COPY a DuckDB állítását a Microsoft SQL adatainak exportálásához különböző fájlformátumokba.

Exportálás Parquet formátumba

Exportálja az adatokat Apache Parquet formátumba.

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

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

Exportálás CSV-fájlba

Exportálni az adatokat egy vesszővel elválasztott értékfájlba:

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

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

Particionált Parquet exportálása

Exportálni adatokat partíciós Parquet fájlokba elosztott elemzéshez:

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

Nagy eredményhalmazok streamelése

Nagy adathalmazok esetén a arrow_reader() használatával streamelt kötegekben dolgozhatja fel az adatokat anélkül, hogy egyszerre az összes sort betöltené a memóriába:

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

Streaming eredmények összegyűjtése

Az összes tétel összesítéséhez regisztráljuk az egyes tételeket egy tartós DuckDB kapcsolatba, és fokozatosan gyűjtsük az eredményeket.

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

Teljesítménnyel kapcsolatos tippek

Hagyd, hogy a Microsoft SQL végezze a nehéz munkát

A Microsoft SQL gyorsabb a szűrésben, csatlakozásokban és aggregációkban, mint az összes nyers adat áthúzása a vezetéken. Használd a DuckDB-t másodlagos elemzésre az már lehozott eredményhalmazok esetében, nem az SQL Server lekérdezésoptimalizálásának helyettesítésére.

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

Minden olvasási művelethez használd az Arrow-t

Az Arrow-alapú átvitel elkerüli a köztes Python objektumok létrehozását, ami csökkenti a memóriahasználatot és javítja az áteresztőképességet. Részesítse előnyben a(z) cursor.arrow() használatát a kézi, soronkénti átalakítás helyett, amikor adatokat ad át a DuckDB-nek.

Streaming használata nagy adathalmazokhoz

Az elérhető memóriánál nagyobb eredményhalmazok esetén használj arrow_reader() paraméterrel batch_size az adatok fokozatos feldolgozására.

Kapcsolódó tartalom

  • Last updated on