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:

Carga de trabalho Caminho recomendado Porquê
Acesso à linha de aplicações (web API, CRUD) Métodos de busca por cursor Baixa sobrecarga, processamento linha a linha, sem dependências extra.
Consultas de relatórios de pequena a média dimensão pandas API familiar para filtragem, agrupamento e visualização.
Grandes conjuntos de resultados ou tabelas largas Extração de flechas Transferência colunar sem cópia, sobrecarga de memória mínima.
Análise de elevado desempenho Polares com Flecha Execução com múltiplas threads em dados colunares, sem contenção do GIL.
SQL ad hoc sobre dados locais e remotos DuckDB com Flecha Análises SQL sobre tabelas Arrow, combinar com ficheiros CSV/Parquet locais.
Exploração de cadernos pandas ou Polars com Flecha Escolha com base na familiaridade da equipa e no tamanho dos dados.

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

Anti-padrão Problema Abordagem melhor
fetchall() depois pd.DataFrame() , para tabelas grandes Carrega todas as linhas na memória duas vezes (uma como tuplas, outra como DataFrame). Utilize cursor.arrow() depois arrow_table.to_pandas().
Converter o Arrow em pandas só para filtrar linhas Desperdiça memória na cópia completa do pandas. Filtre em SQL (cláusula WHERE) ou use Polars/DuckDB diretamente sobre a tabela Arrow.
SELECT * Quando precisas de três colunas Transfere dados desnecessários do servidor. Liste apenas as colunas de que precisa.
Construir um DataFrame para calcular COUNT(*) O servidor calcula agregados mais rapidamente do que o Python. Usar SELECT COUNT(*) e fetchval().
Abrir uma nova ligação por consulta A criação de ligações é dispendiosa mesmo com a sobrecarga do agrupamento de ligações. Reutilize ligações dentro de uma unidade lógica de trabalho.
Flecha de Encadeamento -> pandas -> Polars Cada conversão copia os dados. Vá diretamente para o seu formato de destino: Arrow -> Polars ou Arrow -> pandas.

Conteúdo relacionado

  • Last updated on