Escolha um padrão de acesso a dados e análise com mssql-python

O mssql-python driver fornece múltiplos caminhos para ler dados do Microsoft SQL. Cada opção adequa-se a diferentes cargas de trabalho. Este guia ajuda-o a escolher o mais adequado com base no tamanho dos seus dados, necessidades de análise e requisitos de desempenho.

Decide por carga de trabalho

Use esta tabela para encontrar o seu ponto de partida:

Métodos de busca por cursor

Use métodos padrão de cursor quando precisar de acesso orientado a linhas sem dependências adicionais. Este método é a escolha certa para código de aplicação que processa uma linha de cada vez, devolve respostas da API ou alimenta a lógica da aplicação.

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

Utilize fetchmany() para o processamento em lote de grandes conjuntos de resultados com utilização eficiente da memória. Usa fetchval() quando precisares de um único valor, como uma contagem, um máximo ou uma verificação de existência.

Para documentação completa do método fetch, veja Recuperar dados.

Extração de flechas

Utilize a extração Arrow quando precisar de dados colunares para análise de dados, a construção de DataFrames ou exportação para Parquet. O Arrow fornece transferência de dados sem cópia a partir do controlador, o que evita a sobrecarga de conversão linha a linha ao criar um DataFrame a partir de fetchall().

As tabelas com índices de columnstore já estão armazenadas em formato colunar no motor da base de dados, tornando a extração por Arrow uma escolha natural para essas cargas de trabalho.

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

Para conjuntos de resultados grandes, use arrow_reader() para transmitir lotes sem carregar tudo na memória:

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

As tabelas Arrow são o ponto de partida para pandas, Polars e DuckDB. Extrai uma vez, depois converte:

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)

Para documentação completa do Arrow, veja integração com o Apache Arrow.

pandas

Use pandas quando precisar de uma API DataFrame familiar para relatórios, análises ad hoc ou limpeza de dados. O Pandas funciona melhor com conjuntos de resultados que cabem na memória (até alguns milhões de linhas, dependendo da largura da coluna).

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

Para conjuntos de resultados maiores, constrói o DataFrame a partir do Arrow em vez de fetchall():

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

Para padrões completos do pandas, incluindo ETL, séries temporais e write-back, veja integração do pandas.

Polares com Flecha

Use Polars quando precisar de operações DataFrame mais rápidas em conjuntos de resultados maiores. O Polars utiliza o Apache Arrow como formato de memória, pelo que a transferência de cursor.arrow() é zero-copy. O Polars também executa operações em várias threads, o que evita a contenção do GIL em transformações intensivas em 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)

Para streaming de grandes conjuntos de resultados:

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)

Para padrões completos de Polars, veja integração de Polars.

DuckDB com Arrow

Use o DuckDB quando precisar de executar análises SQL em dados extraídos, juntar dados do servidor com ficheiros CSV ou Parquet locais, ou exportar resultados para formatos de ficheiro. O DuckDB opera em tabelas Arrow com acesso sem cópia.

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

Juntar os dados do servidor com um ficheiro local:

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

Exportar para Parquet:

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

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

Para padrões completos do DuckDB, veja integração com o DuckDB.

Funcionalidades do SQL Server da Microsoft que afetam as decisões do trajeto de leitura

O motor de base de dados tem funcionalidades que afetam diretamente qual o caminho de leitura que funciona melhor. Considere estas características ao escolher a sua abordagem:

Índices de Columnstore

Tabelas com índices de coluna armazenam dados em formato colunar. A extração em Arrow é a forma natural de transferência para estas tabelas, porque os dados já estão em formato colunar no motor. Se as suas consultas analíticas processam tabelas largas com milhões de linhas, um índice columnstore não clusterizado no lado do servidor, combinado com a extração em Arrow no lado do cliente, proporciona a melhor taxa de transferência de ponta a ponta.

Visualizações indexadas

As visualizações indexadas pré-computam e armazenam resultados agregados ou juntos no servidor. Se a sua análise com pandas ou Polars calcula repetidamente a mesma agregação, considere criar uma vista indexada e consultar essa vista em vez dela. O servidor mantém automaticamente a visualização à medida que os dados subjacentes mudam.

Query Store

A Query Store acompanha estatísticas de execução de consultas ao longo do tempo. Utilize-o para identificar quais as consultas suficientemente dispendiosas para justificar uma extração com Arrow e uma análise local em DataFrame, em vez de uma leitura direta do cursor. Se uma consulta é executada em milissegundos, a obtenção por cursor é adequada. Se forem analisadas milhões de linhas, a extração em Arrow e a análise local podem reduzir a carga no servidor.

Processamento inteligente de consultas

As funcionalidades inteligentes de processamento de consultas do Microsoft SQL, como joins adaptativos, modo batch no rowstore e feedback de concessão de memória, otimizam automaticamente a execução das consultas. Estas funcionalidades funcionam independentemente do caminho de leitura do cliente escolhido, mas são as que mais beneficiam as grandes consultas analíticas. Não precisas de ajustar dicas ou planos de execução para a maioria das cargas de trabalho.

Transmita grandes conjuntos de resultados

Para conjuntos de resultados que não cabem na memória, use padrões de streaming:

Transmissão baseada em cursor com 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

Streaming baseado em Arrow para 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()

Anti-padrões a evitar

Conteúdo relacionado

• Recuperar dados
• Integração com Apache Arrow
• Integração com o Pandas
• Integração com Polars
• Integração com DuckDB
• Afinação de desempenho
• Padrões de carregamento e movimento de dados