Válassz adathozzáférési és analitikai mintát mssql-python segítségével

Az mssql-python illezőprogram több útvonalat biztosít az adatok olvasásához a Microsoft SQL-ből. Minden útvonal más-más munkaterheléshez illeszkedik. Ez az útmutató segít kiválasztani a megfelelő megoldást az adatméret, elemzési igények és teljesítménykövetelmények alapján.

Döntés a munkaterhelés szerint

Használd ezt a táblázatot, hogy megtaláld a kiindulópontodat:

Munkaterhelés Ajánlott elérési út Miért
Alkalmazás sorszintű hozzáférése (web API, CRUD) Kurzor behozási módszerek Alacsony többletterhelés, soronkénti feldolgozás, nincsenek további függőségek.
Kis és közepes jelentési lekérdezések pandas Ismerős API a szűréshez, csoportosításhoz és vizualizációhoz.
Nagy eredményhalmazok vagy széles táblázatok Nyíl kinyerése Nulla másolatos oszlopos átvitel, minimális memória terhelés.
Nagy teljesítményű analitika Polars és Arrow Többszálas végrehajtás oszlopos adatokon, GIL miatti versengés nélkül.
Ad hoc SQL helyi és távoli adatokon keresztül DuckDB az Arrow-val SQL-elemzés Arrow-táblákon, összekapcsolás helyi CSV-/Parquet-fájlokkal.
A Notebook megismerése pandas vagy Polars Arrow-val Válassz a csapat ismeretessége és az adatok mérete alapján.

Kurzorlekérési módszerek

Használj szabványos kurzori módszereket, ha sororientált hozzáférésre van szükséged extra függőségek nélkül. Ez a módszer a megfelelő választás olyan alkalmazáskódhoz, amely soronként dolgozza fel, API válaszokat ad vissza, vagy alkalmazás logikát táplál.

import mssql_python

conn = mssql_python.connect(
    server="<server>.database.windows.net",
    database="<database>",
    authentication="ActiveDirectoryDefault",
    encrypt="yes"
)

cursor = conn.cursor()

# fetchone(): Process rows one at a time
cursor.execute("SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ListPrice > %(threshold)s", {"threshold": 100})
row = cursor.fetchone()
while row:
    print(f"{row.Name}: ${row.ListPrice:.2f}")
    row = cursor.fetchone()

# fetchmany(): Process in batches
cursor.execute("SELECT ProductID, Name FROM Production.Product")
while True:
    batch = cursor.fetchmany(100)
    if not batch:
        break
    for row in batch:
        print(row.Name)

# fetchval(): Get a single scalar value
cursor.execute("SELECT COUNT(*) FROM Production.Product")
count = cursor.fetchval()

A(z) fetchmany() elem használatával memóriahatékonyan dolgozhat fel nagy méretű eredményhalmazokat kötegekben. Használd fetchval() , ha szükséged van egyetlen értékre, például szám, max vagy létező ellenőrzésre.

A teljes fetch method dokumentációért lásd: Adat lekérése.

Nyíl kinyerése

Használd az Arrow extractiont, amikor oszlopos adatra van szükséged analitikához, DataFrame építéshez vagy exportáláshoz a Parquet-re. Az Arrow másolásmentes adatátvitelt biztosít a meghajtóból, így elkerülhető a DataFrame fetchall() alapján történő létrehozásának soronkénti átalakítási többletterhe.

A columnstore-indexekkel rendelkező táblák már oszlopos formátumban vannak tárolva az adatbázis motorban, így az Arrow extraction természetes választás ezekhez a feladatokhoz.

cursor.execute("""
    SELECT ProductID, Name, ListPrice, Color
    FROM Production.Product
    WHERE ListPrice > 0
""")

# Get a single Arrow table
arrow_table = cursor.arrow()
print(f"{arrow_table.num_rows} rows, {arrow_table.num_columns} columns")

Nagy eredményhalmazok esetén arrow_reader() használd a csomagok streamelésére anélkül, hogy mindent betöltenénk a memóriába:

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")

# Stream Arrow record batches
reader = cursor.arrow_reader(batch_size=10000)
for batch in reader:
    # Each batch is a pyarrow.RecordBatch
    print(f"Batch: {batch.num_rows} rows")

Az Arrow-táblák kiindulási alapot jelentenek a pandas, a Polars és a DuckDB számára. Egyszer kivonni, majd átalakítani:

cursor.execute("""
    SELECT ProductID, Name, ListPrice, Color
    FROM Production.Product
    WHERE ListPrice > 0
""")
arrow_table = cursor.arrow()

# Arrow -> pandas
df = arrow_table.to_pandas()

# Arrow -> Polars (zero-copy)
import polars as pl
df = pl.from_arrow(arrow_table)

A teljes Arrow dokumentációért lásd: Apache Arrow integráció.

pandas

Használd a pandas-t, ha szükséged van egy ismerős DataFrame API-ra jelentéshez, alkalmi elemzéshez vagy adattisztításhoz. A Pandas a legjobban olyan eredménykészletekkel működik, amelyek memóriába illeszkednek (akár néhány millió sor, az oszlop szélességétől függően).

cursor.execute("""
    SELECT p.Name, p.ListPrice, pc.Name AS Category
    FROM Production.Product p
    JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
    JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
    WHERE p.ListPrice > 0
""")

import pandas as pd
rows = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
df = pd.DataFrame.from_records(rows, columns=columns)

# Analyze
print(df.groupby("Category")["ListPrice"].agg(["mean", "count"]))

Nagyobb eredményhalmazok esetén a DataFrame-et az Arrow használatával hozd létre a(z) fetchall() helyett:

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
arrow_table = cursor.arrow()
df = arrow_table.to_pandas()

A teljes pandas mintákért, beleértve az ETL-t, idősorokat és a visszaírást, lásd a pandas integrációt.

Polars az Arrow-val

Használj Polars-t, ha nagyobb eredményhalmazoknál gyorsabb DataFrame műveletekre van szükséged. A Polars az Apache Arrow-t használja memóriaformátumként, ezért a(z) cursor.arrow() rendszerből történő adatátvitel másolásmentes. A Polars több szálon is hajt végre műveleteket, így elkerülhető a GIL miatti versengés a CPU-igényes átalakítások során.

import polars as pl

cursor.execute("""
    SELECT ProductID, Name, ListPrice, Color
    FROM Production.Product
    WHERE ListPrice > 0
""")

arrow_table = cursor.arrow()
df = pl.from_arrow(arrow_table)

# Filter and aggregate
result = (
    df.filter(pl.col("ListPrice") > 100)
    .group_by("Color")
    .agg(pl.col("ListPrice").mean().alias("AvgPrice"))
    .sort("AvgPrice", descending=True)
)
print(result)

Nagy eredményhalmazok streameléséhez:

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")

reader = cursor.arrow_reader(batch_size=50000)
frames = []
for batch in reader:
    frames.append(pl.from_arrow(batch))

df = pl.concat(frames)

A teljes Polars-mintákért lásd: Polars-integráció.

DuckDB az Arrow-val

Használd a DuckDB-t, amikor SQL analitikát kell futtatnod a kinyert adatokon, szerveradatokat helyi CSV vagy Parquet fájlokkal kell összekapcsolnod, vagy eredményeket fájlformátumba exportálnod. A DuckDB Arrow táblákon működik, nulla másolat hozzáféréssel.

import duckdb

cursor.execute("""
    SELECT ProductID, Name, ListPrice, Color
    FROM Production.Product
    WHERE ListPrice > 0
""")

products = cursor.arrow()

# Run DuckDB SQL on the Arrow table
result = duckdb.sql("""
    SELECT Color, AVG(ListPrice) AS AvgPrice, COUNT(*) AS Count
    FROM products
    WHERE Color IS NOT NULL
    GROUP BY Color
    ORDER BY AvgPrice DESC
""")
print(result.fetchdf())

Szerveradatokat helyi fájlhoz kötöttünk:

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

# Join with a local CSV file
result = duckdb.sql("""
    SELECT c.CustomerID, c.TerritoryID, l.Region
    FROM customers c
    JOIN read_csv_auto('regions.csv') l ON c.TerritoryID = l.TerritoryID
""")

Exportálás Parquet formátumba:

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

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

A teljes DuckDB mintákhoz lásd: DuckDB integráció.

Microsoft SQL funkciók, amelyek befolyásolják az olvasási útvonal döntéseket

Az adatbázis motornak olyan funkciói vannak, amelyek közvetlenül befolyásolják, melyik olvasási út működik a legjobban. A megfelelő megközelítés kiválasztásakor vegye figyelembe ezeket a szempontokat:

Oszlopos adattárolású indexek

Columnstore indexekkel rendelkező táblák oszlopos formátumban tárolják az adatokat. Az Arrow-kinyerés természetes választás ezekhez a táblákhoz, mert az adatok a motorban már eleve oszlopos formában vannak. Ha az analitikai lekérdezéseid széles táblákat pásztolnak több millió sorból, akkor a szerver oldalon egy nem klaszterizált columnstore index és az ügyfél oldali Arrow extraction adja a legjobb végponttól végig terjedési sebességet.

Indexelt nézetek

Az indexelt nézetek előre kiszámítják és a kiszolgálón tárolják az összesített vagy összekapcsolt eredményeket. Ha a pandas- vagy Polars-elemzés többször is ugyanazt az aggregációt végzi el, fontolja meg egy indexelt nézet létrehozását, és inkább azt kérdezze le. A szerver automatikusan fenntartja a nézetet, amikor az alapadatok változnak.

Kérdéstár

Query Store idővel követi a lekérdezések végrehajtási statisztikáit. Használd arra, hogy azonosítsd, mely lekérdezések elég drágák ahhoz, hogy indokolják az Arrow kivonatolást és a helyi DataFrame elemzést a közvetlen kurzorolvasás helyett. Ha egy lekérdezés ezredmásodperceken belül fut, a kurzor felhívása rendben van. Ha milliónyi sorokat szkenneszt, az Arrow kivonása és helyi elemzése csökkentheti a szerver terhelését.

Intelligens lekérdezésfeldolgozás

A Microsoft SQL intelligens lekérdezésfeldolgozó funkciói, mint az adaptív csatlakozások, a soráruházban lévő batch mód és memóriatámogatási visszajelzés, automatikusan optimalizálják a lekérdezések végrehajtását. Ezek a funkciók függetlenül működnek, melyik ügyfél olvasási útvonalat választod, de a legnagyobb elemző lekérdezéseket segítik. Nem kell tippeket vagy végrehajtási terveket a legtöbb munkaterheléshez hangolni.

Nagy eredményhalmazok streamelése

Azok esetén az eredményhalmazok esetén, amelyek nem férnek be a memóriába, használj streaming mintákat:

Kurzoralapú adatfolyam ezzel: fetchmany()

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
while True:
    batch = cursor.fetchmany(5000)
    if not batch:
        break
    for row in batch:
        print(row[0])  # Process each row

Arrow-alapú streamelés Parquet-re:

import pyarrow.parquet as pq

cursor.execute("SELECT * FROM Sales.SalesOrderHeader")
reader = cursor.arrow_reader(batch_size=50000)
writer = None

for batch in reader:
    if writer is None:
        writer = pq.ParquetWriter("orders.parquet", batch.schema)
    writer.write_batch(batch)

if writer:
    writer.close()

Elkerülendő anti-minták

Anti-pattern Probléma Jobb megközelítés
fetchall() majd pd.DataFrame() nagy táblázatok esetén Minden sort kétszer tölt be memóriába (egyszer tupleként, egyszer DataFrame-ként). Használd cursor.arrow() majd arrow_table.to_pandas().
Az Arrow pandas-szá alakítása csak a sorok szűréséhez A teljes Pandas másolatra memóriát pazarol. Szűrj SQL-ben (WHERE klauzulával), vagy használd közvetlenül a Polarst/DuckDB-t az Arrow-táblán.
SELECT * amikor három oszlopra van szükséged Felesleges adatokat továbbít a szerverről. Csak azokat az oszlopokat sorold fel, amikre szükséged van.
Adatkeret építése a számításhoz COUNT(*) A szerver gyorsabban számolja az aggregált adatokat, mint a Python. Használja SELECT COUNT(*) és fetchval().
Új kapcsolat megnyitása lekérdezésenként A kapcsolat létrehozása a kapcsolatkészletezés többletterhelése mellett is költséges. Használd újra a kapcsolatokat egy logikai egységen belül.
Nyílláncolás -> pandas -> Polars Minden átváltás másolja az adatokat. Lépjen közvetlenül a célformátumra: Arrow -> Polars vagy Arrow -> pandas.

Kapcsolódó tartalom

  • Last updated on