Wybierz wzorzec dostępu do danych i analityki za pomocą mssql-python

Sterownik mssql-python oferuje wiele ścieżek do odczytu danych z Microsoft SQL. Każda ścieżka nadaje się do różnych obciążeń roboczych. Ten przewodnik pomaga wybrać odpowiednią platformę na podstawie wielkości danych, potrzeb analitycznych i wymagań dotyczących wydajności.

Decyduj według obciążenia

Użyj tej tabeli, aby znaleźć punkt wyjścia:

Obciążenie Zalecana ścieżka Dlaczego
Dostęp do wierszy aplikacji (web API, CRUD) Metody pobierania kursora Niski narzut, przetwarzanie wiersz po wierszu, brak dodatkowych zależności.
Małe i średnie zapytania raportowe pandas Znane API do filtrowania, grupowania i wizualizacji.
Duże zbiory wyników lub szerokie tabele Ekstrakcja strzałek Transfer kolumnowy bez kopii, minimalny narzut pamięci.
Analiza o wysokiej wydajności Polars z użyciem Arrow Wykonanie wielowątkowe na danych kolumnowych, bez rywalizacji GIL.
SQL ad hoc nad danymi lokalnymi i zdalnymi DuckDB i Arrow Analityka SQL w tabelach Arrow, łączenie z lokalnymi plikami CSV/Parquet.
Eksploracja notatników pandas lub Polars with Arrow Wybieraj na podstawie znajomości zespołu i wielkości danych.

Metody pobierania kursora

Używaj standardowych metod kursora, gdy potrzebujesz dostępu zorientowanego na wiersze bez dodatkowych zależności. Ta metoda jest odpowiednim wyborem dla kodu aplikacji, który przetwarza jeden wiersz po drugim, zwraca odpowiedzi API lub dostarcza logikę aplikacji.

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

Użyj fetchmany() do oszczędnego pod względem pamięci przetwarzania wsadowego dużych zbiorów wyników. Używaj fetchval(), gdy potrzebujesz pojedynczej wartości, takiej jak count, max lub sprawdzenie istnienia.

Pełną dokumentację metody pobierania można znaleźć w artykule Retrieve data.

Wyciąganie strzał

Używaj ekstrakcji Arrow, gdy potrzebujesz danych kolumnowych do analiz, tworzenia obiektu DataFrame lub eksportu do formatu Parquet. Arrow umożliwia przesyłanie danych bez kopiowania ze sterownika, co pozwala uniknąć narzutu związanego z konwersją wiersz po wierszu przy tworzeniu DataFrame'u z fetchall().

Tabele z indeksami columnstore są już przechowywane w formacie kolumnowym w silniku bazodanowym, co sprawia, że ekstrakcja strzałek jest naturalnym wyborem dla tych obciążeń.

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

W przypadku dużych zbiorów wyników użyj arrow_reader() do strumieniowego przetwarzania partii bez ładowania wszystkiego do pamięci:

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

Tabele strzałek są punktem wyjścia dla pand, polarnych i DuckDB. Ekstraktuj raz, a następnie przekonwertuj:

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)

Pełną dokumentację Arrow można znaleźć w artykule o integracji z Apache Arrow.

pandas

Używaj Pandas, gdy potrzebujesz znanego API DataFrame do raportowania, analiz ad hoc lub czyszczenia danych. Pandas najlepiej sprawdza się z zestawami wyników mieszczących się w pamięci (do kilku milionów wierszy, w zależności od szerokości kolumny).

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

W przypadku większych zbiorów wyników utwórz obiekt DataFrame na podstawie Arrow zamiast fetchall():

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

Pełne wzorce pandas, w tym ETL, szeregi czasowe i write-back, można znaleźć w artykule integracja pandas.

Polars z Arrow

Użyj biblioteki Polars, gdy potrzebujesz szybszych operacji na ramkach danych w przypadku większych zbiorów wyników. Polars używa formatu pamięci Apache Arrow, więc transfer z cursor.arrow() jest bezkopiowany. Polars wykonuje także operacje na wielu wątkach, co zapobiega rywalizacji GIL przy transformacjach wymagających obciążenia CPU.

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)

Do streamingu dużych zestawów wyników:

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)

Pełne wzorce Polars znajdziesz w sekcji Integracja z Polars.

DuckDB z Arrow

Używaj DuckDB, gdy musisz uruchomić analitykę SQL na wyodrębnionych danych, połączyć dane serwera z lokalnymi plikami CSV lub Parquet, albo wyeksportować wyniki do formatów plików. DuckDB pracuje na tabelach Arrow z dostępem bez kopiowania.

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

Połącz dane serwera z plikiem lokalnym:

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

Eksportuj do formatu Parquet:

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

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

Pełne wzorce DuckDB można znaleźć w integracji z DuckDB.

Funkcje Microsoft SQL wpływające na decyzje dotyczące ścieżki odczytu

Silnik bazy danych posiada funkcje bezpośrednio wpływające na to, która ścieżka odczytu działa najlepiej. Weź pod uwagę następujące cechy, wybierając podejście:

Indeksy kolumnowe

Tabele z indeksami columnstore przechowują dane w formacie kolumnowym. Ekstrakcja strzałek jest naturalnym przekazaniem tych tabel, ponieważ dane są już kolumnowe w silniku. Jeśli Twoje zapytania analityczne skanują szerokie tabele z milionami wierszy, nieklastrowany indeks kolumnowy po stronie serwera w połączeniu z ekstrakcją Arrow po stronie klienta zapewnia najlepszą całościową przepustowość.

Widoki indeksowane

Indeksowane widoki prekalkulują i zapisują zagregowane lub połączone wyniki na serwerze. Jeśli twoja analiza Pandas lub Polars wielokrotnie oblicza tę samą agregację, rozważ stworzenie widoku indeksowanego i zapytanie do tego widoku. Serwer automatycznie utrzymuje widok w miarę zmian danych bazowych.

Magazyn zapytań

Query Store śledzi statystyki wykonywania zapytań w czasie. Użyj tego, aby określić, które zapytania są na tyle kosztowne, że uzasadniają ekstrakcję do Arrow i analizę w lokalnym obiekcie DataFrame, zamiast bezpośredniego odczytu za pomocą kursora. Jeśli zapytanie wykonuje się w ciągu milisekund, pobieranie z użyciem kursora jest odpowiednie. Jeśli przeskanuje miliony wierszy, ekstrakcja strzałek i analiza lokalna mogą zmniejszyć obciążenie serwera.

Inteligentne przetwarzanie zapytań

Inteligentne funkcje przetwarzania zapytań Microsoft SQL, takie jak adaptacyjne połączenia, tryb wsadowy w pamięci wierszowej oraz informacja zwrotna z przydziału pamięci, automatycznie optymalizują wykonywanie zapytań. Te funkcje działają niezależnie od wybranej ścieżki czytania klienta, ale najbardziej pomagają dużym zapytaniom analitycznym. Nie musisz dostosowywać podpowiedzi ani planów wykonania w przypadku większości obciążeń.

Przesyłaj strumieniowo duże zbiory wyników

Dla zestawów wyników, które nie mieszczą się w pamięci, użyj wzorców strumieniowania:

Strumieniowanie oparte na kursorze z 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

Strumieniowanie oparte na Arrow do Parquet:

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

Antywzorce, których należy unikać

Antywzorzec Problem Lepsze podejście
fetchall() następnie pd.DataFrame() dla dużych tabel Wczytuje wszystkie wiersze do pamięci dwukrotnie (raz w postaci krotek, raz w postaci ramki danych). Użyj cursor.arrow() wtedy arrow_table.to_pandas().
Konwertowanie formatu Arrow do pandas tylko po to, aby filtrować wiersze Marnuje pamięć na pełną kopię Pandas. Filtruj w SQL (klauzula WHERE) lub użyj Polars/DuckDB bezpośrednio na tabeli Arrow.
SELECT * gdy potrzebujesz trzech kolumn Przesyła niepotrzebne dane z serwera. Wypisz tylko te kolumny, których potrzebujesz.
Budowanie DataFrame'u do obliczeń COUNT(*) Serwer oblicza agregacje szybciej niż Python. Użyj SELECT COUNT(*) i fetchval().
Otwieranie nowego połączenia na każde zapytanie Tworzenie połączeń jest kosztowne, nawet przy narzutach poolingowych. Ponownie wykorzystaj połączenia w ramach logicznej jednostki pracy.
Łańcuchowa Strzała -> Pandas -> Polary Każda konwersja kopiuje dane. Przejdź bezpośrednio do formatu docelowego: Arrow →> Polars lub Arrow →> pandas.

Treści powiązane

  • Last updated on