Выберите шаблон доступа к данным и аналитики с помощью mssql-python

Драйвер mssql-python предоставляет несколько путей для чтения данных из Microsoft SQL. Каждый путь подходит под разные нагрузки. Это руководство поможет вам выбрать правильный вариант, исходя из размера данных, потребностей в анализе и требований к производительности.

Решайте по нагрузке

Используйте эту таблицу, чтобы найти отправную точку:

Рабочая нагрузка	Рекомендуемый путь	Почему
Доступ к строкам приложений (веб-API, CRUD)	Методы выбора курсора	Низкие накладные расходы, обработка по очереди, отсутствие дополнительных зависимостей.
Запросы для создания небольших и средних отчетов	pandas	Знакомый API для фильтрации, группировки и визуализации.
Большие наборы результатов или широкие таблицы	Извлечение стрелок	Передача в колоночном формате без копирования, минимальные накладные расходы на память.
Высокопроизводительная аналитика	Поляры со стрелой	Многопоточное выполнение операций с колоночными данными без конкуренции за GIL.
Специальные SQL-запросы к локальным и удаленным данным	DuckDB с использованием Arrow	SQL-аналитика в таблицах Arrow, соединяйте их с локальными файлами CSV/Parquet.
Исследование блокнотов	Панды или Поляры со стрелой	Выбирайте, исходя из опыта команды и объёма данных.

Методы извлечения курсора

Используйте стандартные методы курсора, когда нужен строковый доступ без лишних зависимостей. Этот метод является правильным выбором для кода приложения, который обрабатывает по одной строке за раз, возвращает ответы API или подаёт логику приложений.

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

Использование fetchmany() для эффективной по памяти пакетной обработки больших наборов результатов. Используйте fetchval(), когда требуется одно значение, например количество, максимум или проверка существования.

Полную документацию по методу fetch см. в разделе «Получение данных».

Извлечение стрелок

Используйте Arrow extraction, когда нужны столбцовные данные для аналитики, построения DataFrame или экспорта в Parquet. Arrow обеспечивает передачу данных без копирования из драйвера, что позволяет избежать накладных расходов, связанных с построчным преобразованием при построении DataFrame из fetchall().

Таблицы с индексами columnstore уже хранятся в колоночном формате в движке базы данных, поэтому извлечение данных в формате Arrow естественно подходит для таких рабочих нагрузок.

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

Для больших наборов результатов используйте arrow_reader() для потоковой передачи пакетов без загрузки всего в память:

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

Таблицы Arrow служат отправной точкой для pandas, Polars и DuckDB. Извлечь один раз, затем конвертировать:

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)

Полная документация по Arrow см. раздел интеграция Apache Arrow.

pandas

Используйте Pandas, когда вам нужен знакомый API DataFrame для отчетности, разового анализа или очистки данных. Pandas лучше всего работает с наборами результатов, которые помещаются в память (до нескольких миллионов строк, в зависимости от ширины столбца).

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

Для больших наборов результатов постройте DataFrame из Arrow вместо fetchall():

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

Полные шаблоны использования pandas, включая ETL, временные ряды и обратную запись, см. в разделе интеграция pandas.

Поляры со стрелой

Используйте Polars, когда нужны более быстрые операции с DataFrame на больших наборах результатов. Polars использует Apache Arrow в качестве формата представления данных в памяти, поэтому передача данных из cursor.arrow() выполняется без копирования. Polars также выполняет операции с несколькими потоками, что позволяет избежать конкуренции GIL при преобразованиях с нагрузкой на 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)

Для потоковой передачи больших наборов результатов:

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)

Полные шаблоны Polars см. в разделе Интеграция с Polars.

DuckDB с Arrow

Используйте DuckDB, когда нужно запустить SQL-аналитику на извлеченных данных, объединить серверные данные с локальными файлами CSV или Parquet, или экспортировать результаты в форматы файлов. DuckDB работает с таблицами Arrow с нулевым доступом к копированию.

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

Объедините данные сервера с локальным файлом:

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

Экспорт в Parquet:

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

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

Полные шаблоны DuckDB см. Интеграция DuckDB.

Функции Microsoft SQL, влияющие на выбор пути чтения

В движке базы данных есть функции, которые напрямую влияют на то, какой путь чтения работает лучше всего. Учитывайте следующие особенности при выборе подхода:

Индексы колоностора

Таблицы с индексами столбцов хранят данные в столбцах. Извлечение в формате Arrow — это естественный способ передачи данных для этих таблиц, поскольку данные в движке уже имеют столбцовый формат. Если ваши аналитические запросы сканируют широкие таблицы с миллионами строк, то некластерный индекс Columnstore на серверной стороне в сочетании с извлечением Arrow на стороне клиента даст наилучшую сквозную пропускную способность.

Индексированные представления

Индексированные представления предварительно вычисляют и хранят агрегированные или объединённые результаты на сервере. Если при анализе данных с помощью pandas или Polars одна и та же агрегация вычисляется многократно, рассмотрите возможность создания индексированного представления и выполнения запросов к нему вместо этого. Сервер автоматически поддерживает представление по мере изменения базовых данных.

Хранилище запросов

хранилище запросов отслеживает статистику выполнения запросов на протяжении времени. Используйте это, чтобы определить, какие запросы достаточно ресурсоёмкие, чтобы имело смысл выполнять выгрузку в Arrow и локальный анализ в DataFrame, а не непосредственное чтение через курсор. Если запрос выполняется за миллисекунды, курсорное извлечение подойдёт. Если он сканирует миллионы строк, извлечение Arrow и локальный анализ могут снизить нагрузку на сервер.

Интеллектуальная обработка запросов

Функции интеллектуальной обработки запросов в Microsoft SQL, такие как адаптивные соединения, пакетный режим для хранилища строк и обратная связь по выделению памяти, автоматически оптимизируют выполнение запросов. Эти функции работают независимо от выбранного пути чтения клиента, но больше всего они приносят пользу крупным аналитическим запросам. Для большинства задач не нужно настраивать подсказки или планы исполнения.

Потоковая передача больших наборов результатов

Для наборов результатов, которые не помещаются в память, используйте потоковые шаблоны:

Потоковая передача с использованием курсора с 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 на 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()

Антипаттернов, которых стоит избегать

Антипаттерн	Проблема.	Лучший подход
fetchall() затем pd.DataFrame() для больших таблиц	Загружает все строки в память дважды (один раз в виде кортежей, один раз как DataFrame).	Используйте cursor.arrow(), затем arrow_table.to_pandas().
Преобразование Arrow в pandas только для фильтрации строк	Тратит память на создание полной копии pandas.	Фильтруйте в SQL (WHERE клауза) или используйте Polars/DuckDB напрямую в таблице Arrow.
SELECT * Когда нужны три колонки	Передаёт ненужные данные с сервера.	Перечисляйте только те колонки, которые вам нужны.
Построение таблицы данных для вычисления COUNT(*)	Сервер вычисляет агрегаты быстрее, чем Python.	Использование SELECT COUNT(*) и fetchval().
Открытие нового соединения для каждого запроса	Создание соединения требует больших затрат даже с учётом накладных расходов на пул соединений.	Используйте связи в рамках логической единицы работы.
Цепная стрела —> панды —> полярные	Каждое преобразование копирует данные.	Перейдите напрямую к целевому формату: Arrow -> Polars или Arrow -> pandas.

Связанные материалы

• Получение данных
• Интеграция Apache Arrow
• Интеграция с pandas
• Интеграция поляров
• Интеграция с DuckDB
• Настройка производительности
• Загрузка данных и паттерны перемещения