Usa mssql-python con Apache Arrow

Il driver mssql-python fornisce metodi di recupero Apache Arrow per il recupero di dati columnari ad alte prestazioni da Microsoft SQL e database SQL di Azure.

Apache Arrow è una piattaforma di sviluppo cross-language per dati columnari in memoria. Il driver converte i set di risultati ODBC direttamente in formato Arrow in C++, bypassando la creazione di oggetti Python per migliorare le prestazioni.

L'integrazione con frecce permette:

  • Trasferimento dati senza copia verso Polars, pandas e DuckDB. "Zero-copy" significa che i dati rimangono in un singolo buffer di memoria che il driver scrive e che le librerie consumanti leggono direttamente, quindi nessuna riga viene duplicata in oggetti Python intermedi.
  • Trasmettere in streaming i set di risultati tramite RecordBatchReader senza caricare tutto in memoria.
  • Formato dati a colonna ideale per carichi di lavoro di analisi e machine learning.
  • Riduzione dell'uso di memoria rispetto alla creazione di oggetti Python riga per riga.

Metodi di cursore

Il pyarrow pacchetto deve utilizzare metodi di recupero Arrow. Installalo con pip install pyarrow. Se pyarrow non è installato, chiamare qualsiasi metodo Arrow genera un ImportError.

Il driver mssql-python aggiunge tre metodi all'oggetto cursore per l'accesso ai dati Arrow. Tutti e tre i metodi convertono i set di risultati ODBC in formato Arrow nel livello C++ del driver, evitando così la creazione di oggetti Python intermedi.

  • arrow() restituisce l'intero insieme di risultati come un'unica tabella in memoria. La più semplice da usare.
  • arrow_batch() restituisce un lotto di righe alla volta, dandoti il controllo manuale del loop.
  • arrow_reader() restituisce un iteratore che genera automaticamente batch. Ideale per lo streaming di risultati grandi.

Utilizzo di cursor.arrow(batch_size=8192)

Recupera l'intero insieme di risultati come un singolo pyarrow.Table. Questo metodo è il più semplice e funziona bene quando l'intero insieme di risultati può essere contenuto in memoria.

import mssql_python

conn = mssql_python.connect(connection_string)
cursor = conn.cursor()

cursor.execute("SELECT ProductID, Name, ListPrice FROM Production.Product")
table = cursor.arrow()

print(type(table))       # <class 'pyarrow.lib.Table'>
print(table.num_rows)    # Number of rows fetched
print(table.num_columns) # Number of columns
print(table.schema)      # Column names and Arrow types
print(table.to_pandas()) # Convert to pandas DataFrame

Note

Se la tua stringa di connessione usa Authentication=ActiveDirectoryDefault, il driver usa DefaultAzureCredential, che prova più fornitori di credenziali in sequenza. La prima connessione può essere lenta perché l'SDK percorre la catena finché non trova un fornitore funzionante. In produzione, se sai quale tipo di credenziale utilizza il tuo ambiente, specificalo direttamente (ad esempio, ActiveDirectoryMSI per l'identità gestita) per evitare il chain walk. Per altre informazioni, vedere Autenticazione di Microsoft Entra.

Utilizzo di cursor.arrow_batch(batch_size=8192)

Recupera un singolo pyarrow.RecordBatch che contiene fino a batch_size righe. Usa questo metodo per cicli di elaborazione batch personalizzati dove serve un controllo dettagliato su quante righe vengono recuperate alla volta.

cursor.execute("SELECT * FROM Production.TransactionHistory")

while True:
    batch = cursor.arrow_batch(batch_size=10000)
    if batch.num_rows == 0:
        break
    # Process each batch
    print(f"Fetched {batch.num_rows} rows")

Utilizzo di cursor.arrow_reader(batch_size=8192)

Restituisce un pyarrow.RecordBatchReader che restituisce oggetti RecordBatch finché il set di risultati non è esaurito. Questo metodo è l'opzione più efficiente in termini di memoria per grandi set di risultati.

cursor.execute("SELECT * FROM Production.TransactionHistory")
reader = cursor.arrow_reader(batch_size=50000)

for batch in reader:
    # Process streaming batches without loading all data
    print(f"Batch: {batch.num_rows} rows")

Modelli comuni

Le tabelle freccia si integrano direttamente con le popolari librerie di dati Python. I seguenti esempi mostrano come passare i dati Arrow a panda, Polars, DuckDB e formati file senza copiare dati.

Caricare i risultati in pandas

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

# Convert to pandas with zero-copy where possible
df = table.to_pandas()
print(df.head())

Carica i risultati in Polars

import polars as pl

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

df = pl.from_arrow(table)
print(df)

Risultati delle query con DuckDB

DuckDB può interrogare le tabelle Arrow direttamente in SQL senza copiare i dati. Questa funzionalità è utile quando hai bisogno di analisi in stile SQL su set di risultati già in formato Arrow.

import duckdb

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

# Query the Arrow table with DuckDB SQL
result = duckdb.sql("SELECT CustomerID, SUM(TotalDue) FROM arrow_table GROUP BY CustomerID")
print(result.fetchall())

Trasmetti set di risultati di grandi dimensioni in formato Parquet

Per set di risultati grandi, si trasferisce i batch di Arrow direttamente su un file Parquet senza caricare l'intero dataset in memoria. ParquetWriter scrive ciascun batch incrementalmente.

import pyarrow.parquet as pq

cursor.execute("SELECT * FROM Production.TransactionHistory")
reader = cursor.arrow_reader(batch_size=100000)

# Write streaming batches to a Parquet file
writer = None
for batch in reader:
    if writer is None:
        writer = pq.ParquetWriter("output.parquet", batch.schema)
    writer.write_batch(batch)

if writer:
    writer.close()

Esportazione in altri formati

PyArrow fornisce scrittori integrati per CSV e il formato file IPC Arrow (noto anche come Feather V2). I file IPC Arrow conservano esattamente i tipi di Arrow e sono rapidi da leggere.

import pyarrow as pa
import pyarrow.csv as pcsv

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

# Write to CSV
pcsv.write_csv(table, "products.csv")

# Write to an Arrow IPC file
with pa.ipc.new_file("products.arrow", table.schema) as writer:
    writer.write_table(table)

Mapping dei tipi di dati

I metodi di recupero Arrow mappano i tipi SQL di Microsoft ai tipi di Arrow a livello C++.

Tipo SQL Microsoft Tipo freccia
int, smallint, tinyint, bigint int32, int16, int8int64
float, reale float64, float32
decimal, numeric decimal128
bit bool
Char, Varchar, Nchar, Nvarchar utf8
testo, ntesto large_utf8
binario, varbinario binary, large_binary
date date32
time time64[us]
datetime, datetime2, smalldatetime timestamp[us]
datetimeoffset timestamp[us, tz=UTC]
uniqueidentifier utf8 (stringa in maiuscolo)
xml utf8

Note

Il driver converte il datetimeoffset tipo in UTC perché le colonne Arrow richiedono un fuso orario fisso. Il driver normalizza le informazioni sul fuso orario per cella da Microsoft SQL a UTC durante la conversione.

Il sql_variant tipo non è supportato dai metodi Arrow fetch e genera un'eccezione di tipo di dato non supportata. Usa lo standard fetchone(), fetchmany(), oppure fetchall() per query che restituiscono sql_variant colonne.

Considerazioni sulle prestazioni

I metodi di recupero Arrow sono i più veloci per l’analisi e le operazioni sui dati in blocco, mentre i metodi standard con cursore sono più adatti a modelli transazionali con set di risultati di piccole dimensioni.

Quando usare Arrow rispetto al fetch standard

Scenario Approccio consigliato
Recupera alcune righe da visualizzare fetchone() / fetchall()
Carica dati in panda o Polar cursor.arrow()
Elabora set di dati di grandi dimensioni in blocchi cursor.arrow_reader()
Ricerche a riga singola o piccoli set di risultati fetchone() / fetchval()
Pipeline di analisi o di aggregazione cursor.arrow() + Polars/DuckDB
Scrivi i risultati su Parquet o Arrow IPC cursor.arrow_reader() + PyArrow I/O

Gestione della memoria per grandi dataset

Per insiemi di risultati che potrebbero superare la memoria disponibile, si usa arrow_reader() con un ragionevole batch_size.

cursor.execute("SELECT * FROM Production.TransactionHistory")

# Process in batches of 100K rows
reader = cursor.arrow_reader(batch_size=100000)
total_rows = 0

for batch in reader:
    # Work with each batch individually
    total_rows += batch.num_rows
    # batch goes out of scope and memory is freed

print(f"Processed {total_rows} rows")

Regola la dimensione del batch

Il parametro batch_size controlla quante righe vengono recuperate in ogni lotto. La dimensione ottimale dipende dalla larghezza della riga e dalla memoria disponibile. Le file più larghe con colonne grandi come nvarchar(max) o varbinary(max) beneficiano di quantità di batch più piccole, mentre le file strette beneficiano di quelle più grandi.

  • Default (8192): Buon equilibrio per la maggior parte dei carichi di lavoro.
  • Più piccoli (1000-5000): Da usare per tavoli ampi con colonne grandi.
  • Più grandi (50000-100000): Da usare per tabelle strette o quando la velocità di trasmissione conta più della memoria.
# Narrow table with many rows - use larger batches
cursor.execute("SELECT ProductID, ListPrice FROM Production.Product")
table = cursor.arrow(batch_size=100000)

# Wide table with LOB columns - use smaller batches
cursor.execute("SELECT * FROM Production.Document")
table = cursor.arrow(batch_size=1000)

Contenuti correlati

  • Last updated on