Pilih akses data dan pola analitik dengan mssql-python

Driver mssql-python menyediakan beberapa jalur untuk membaca data dari Microsoft SQL. Setiap jalur sesuai dengan beban kerja yang berbeda. Panduan ini membantu Anda memilih yang tepat berdasarkan ukuran data, kebutuhan analisis, dan persyaratan performa.

Tentukan berdasarkan beban kerja

Gunakan tabel ini untuk menemukan titik awal Anda:

Beban Kerja Jalur yang direkomendasikan Mengapa
Akses baris pada aplikasi (API web, CRUD) Metode pengambilan kursor Beban tambahan rendah, pemrosesan per baris, tanpa dependensi tambahan.
Permintaan pelaporan kecil hingga menengah pandas API yang sudah dikenal untuk pemfilteran, pengelompokan, dan visualisasi.
Set hasil besar atau tabel lebar Ekstraksi panah Transfer kolom tanpa salinan, overhead memori minimal.
Analitik berperforma tinggi Polars dengan Arrow Eksekusi multithread pada data kolom, tidak ada perselisihan GIL.
SQL ad hoc pada data lokal dan data jarak jauh DuckDB dengan Arrow Analisis SQL pada tabel Arrow, gabungkan dengan file CSV/Parquet lokal.
Eksplorasi buku catatan pandas atau Polars with Arrow Pilih berdasarkan keakraban tim dan ukuran data.

Metode pengambilan kursor

Gunakan metode kursor standar saat Anda memerlukan akses berorientasi baris tanpa dependensi tambahan. Metode ini adalah pilihan yang tepat untuk kode aplikasi yang memproses satu baris pada satu waktu, mengembalikan respons API, atau memberi makan logika aplikasi.

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

Gunakan fetchmany() untuk pemrosesan batch yang hemat memori dari kumpulan hasil besar. Gunakan fetchval() saat Anda memerlukan satu nilai seperti jumlah, maksimum, atau pemeriksaan keberadaan.

Untuk dokumentasi metode pengambilan lengkap, lihat Mengambil data.

Ekstraksi panah

Gunakan ekstraksi Arrow saat Anda memerlukan data kolumnar untuk analitik, pembuatan DataFrame, atau ekspor ke Parquet. Arrow menyediakan transfer data tanpa salinan dari driver, yang menghindari overhead konversi baris demi baris untuk membangun DataFrame dari fetchall().

Tabel dengan indeks columnstore sudah disimpan dalam format kolumnar di mesin basis data, sehingga ekstraksi Arrow menjadi sangat sesuai untuk beban kerja tersebut.

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

Untuk kumpulan hasil besar, gunakan arrow_reader() untuk mengalirkan batch tanpa memuat semuanya ke dalam memori:

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

Tabel Arrow adalah titik awal untuk pandas, Polars, dan DuckDB. Ekstrak sekali, lalu konversikan:

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)

Untuk dokumentasi Arrow lengkap, lihat Integrasi Apache Arrow.

pandas

Gunakan panda saat Anda memerlukan DataFrame API yang sudah dikenal untuk pelaporan, analisis ad hoc, atau pembersihan data. pandas berfungsi paling baik dengan set hasil yang muat dalam memori (hingga beberapa juta baris, tergantung pada lebar kolom).

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

Untuk kumpulan hasil yang lebih besar, buat DataFrame dari Arrow, bukan fetchall():

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

Untuk pola penggunaan pandas yang lengkap, termasuk ETL, deret waktu, dan penulisan kembali, lihat integrasi pandas.

Polars dengan Arrow

Gunakan Polar saat Anda membutuhkan operasi DataFrame yang lebih cepat pada kumpulan hasil yang lebih besar. Polars menggunakan Apache Arrow sebagai format memorinya, sehingga transfer dari cursor.arrow() adalah zero-copy. Polars juga menjalankan operasi pada beberapa utas, yang menghindari perselisihan GIL pada transformasi berat 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)

Untuk streaming kumpulan hasil besar:

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)

Untuk pola Kutub lengkap, lihat Integrasi Kutub.

DuckDB dengan Arrow

Gunakan DuckDB saat Anda perlu menjalankan analitik SQL pada data yang diekstrak, menggabungkan data server dengan file CSV atau Parquet lokal, atau mengekspor hasil ke format file. DuckDB bekerja dengan tabel Arrow dengan akses zero-copy.

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

Gabungkan data server dengan file lokal:

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

Ekspor ke Parket:

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

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

Untuk pola DuckDB lengkap, lihat Integrasi DuckDB.

Fitur Microsoft SQL yang memengaruhi keputusan jalur baca

Mesin database memiliki fitur yang secara langsung memengaruhi jalur baca mana yang paling berfungsi. Pertimbangkan fitur-fitur ini saat memilih pendekatan Anda:

Indeks penyimpanan kolom

Tabel dengan indeks penyimpan kolom menyimpan data dalam format kolom. Ekstraksi Arrow adalah pilihan yang paling alami untuk tabel-tabel ini karena data sudah berformat kolumnar di dalam mesin. Jika kueri analitik Anda memindai tabel lebar dengan jutaan baris, indeks columnstore nonkluster di sisi server yang dipadukan dengan ekstraksi Arrow di sisi klien memberikan throughput menyeluruh terbaik.

Pandangan terindeks

Tampilan yang diindeks menghitung terlebih dahulu dan menyimpan hasil agregat atau gabungan di server. Jika analisis pandas atau Polars Anda berulang kali menghitung agregasi yang sama, pertimbangkan untuk membuat tampilan terindeks dan melakukan kueri pada tampilan tersebut sebagai gantinya. Server secara otomatis mempertahankan tampilan saat data yang mendasarinya berubah.

Toko Permintaan (Query Store)

Query Store melacak statistik eksekusi kueri dari waktu ke waktu. Gunakan ini untuk mengidentifikasi kueri mana yang cukup mahal sehingga layak dilakukan ekstraksi Arrow dan analisis DataFrame lokal, dibandingkan dengan pembacaan langsung melalui kursor. Jika kueri berjalan dalam hitungan milidetik, fetch kursor tidak masalah. Jika proses ini memindai jutaan baris, ekstraksi Arrow dan analisis lokal dapat mengurangi beban server.

Pemrosesan kueri pintar

Fitur pemrosesan kueri cerdas Microsoft SQL, seperti gabungan adaptif, mode batch di rowstore, dan umpan balik peruntukan memori, secara otomatis mengoptimalkan eksekusi kueri. Fitur-fitur ini berfungsi terlepas dari jalur baca klien mana yang Anda pilih, tetapi fitur ini paling menguntungkan kueri analitik besar. Anda tidak perlu menyetel petunjuk atau rencana eksekusi untuk sebagian besar beban kerja.

Alirkan set hasil berukuran besar

Untuk kumpulan hasil yang tidak muat dalam memori, gunakan pola streaming:

Streaming berbasis kursor dengan 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 berbasis panah ke 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-pola yang harus dihindari

Anti-pola praktek Problem Pendekatan yang lebih baik
fetchall() kemudian pd.DataFrame() untuk tabel besar Memuat semua baris ke dalam memori dua kali (sekali sebagai tuple, sekali sebagai DataFrame). Gunakan cursor.arrow() kemudian arrow_table.to_pandas().
Mengonversi Arrow ke pandas hanya untuk memfilter baris Memboroskan memori untuk salinan penuh pandas. Lakukan pemfilteran di SQL (klausa WHERE) atau gunakan Polars/DuckDB langsung pada tabel Arrow.
SELECT * Saat Anda membutuhkan tiga kolom Mentransfer data yang tidak perlu dari server. Cantumkan hanya kolom yang Anda butuhkan.
Membangun DataFrame untuk menghitung COUNT(*) Server menghitung agregat lebih cepat daripada Python. Gunakan SELECT COUNT(*) dan fetchval().
Membuka koneksi baru per kueri Pembuatan koneksi memerlukan biaya besar bahkan jika overhead pooling diperhitungkan. Gunakan kembali koneksi dalam satu unit kerja logis.
Panah Rantai -> panda -> Kutub Setiap konversi menyalin data. Langsung ke format target Anda: Arrow -> Polars atau Arrow -> pandas.

Konten terkait

  • Last updated on