Utilisez des colonnes clairsemées et des ensembles de colonnes avec mssql-python

Les colonnes éparses sont une fonctionnalité d’optimisation du stockage de Microsoft SQL pour les valeurs NULL dans les tables comportant de nombreuses colonnes pouvant contenir des valeurs NULL. Les applications clientes considèrent les colonnes clairsemées comme des colonnes régulières. Le pilote mssql-python les lit et écrit comme n’importe quelle autre colonne sans traitement particulier.

Fonctionnalité	Description
Colonnes éparses	Les valeurs NULL utilisent un stockage nul
Ensembles de colonnes	Représentation XML de toutes les colonnes éparses
Tables larges	Prise en charge de jusqu’à 30 000 colonnes

Note

Les colonnes creuses sont une fonctionnalité du serveur. Le pilote mssql-python ne nécessite aucune configuration ou API spéciale pour fonctionner avec des colonnes clairsemées. La seule différence visible par le client : lorsque vous utilisez des ensembles de colonnes, ils retournent une représentation XML de valeurs de colonnes clairsemées.

Mieux adapté à :

• Tables avec 20 à 50 % ou plus de valeurs NULL.
• Stockage de documents avec attributs variables.
• Modèles EAV (Entité-Attribut-Valeur).
• Données de capteurs avec de nombreuses lectures optionnelles.

Créer des colonnes éparses

Définissez les colonnes clairsemées dans votre schéma de table en ajoutant le SPARSE NULL modificateur aux colonnes qui contiendront fréquemment des valeurs NULL.

Table à colonnes creuses de base

Créez un tableau avec des colonnes clairsemées pour les attributs optionnels.

CREATE TABLE ProductAttributes (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Sparse columns for optional attributes
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

Avec un ensemble de colonnes

Ajoutez un ensemble de colonnes afin de fournir un accès XML à toutes les colonnes éparses simultanément.

CREATE TABLE ProductAttributesWithSet (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    -- Column set provides XML access to all sparse columns
    SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
    -- Sparse columns
    Color NVARCHAR(50) SPARSE NULL,
    Size NVARCHAR(20) SPARSE NULL,
    Weight DECIMAL(10,2) SPARSE NULL,
    Material NVARCHAR(100) SPARSE NULL,
    Warranty INT SPARSE NULL,
    Manufacturer NVARCHAR(100) SPARSE NULL
);

Insérer des données de colonne clairsemées

Insérez dans les colonnes éparses en indiquant leur nom, comme vous le feriez avec des colonnes normales.

Insérer des colonnes individuelles

Insérer des produits avec des valeurs spécifiques renseignées dans des colonnes éparses.

import mssql_python

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

# Create the table with sparse columns
cursor.execute("DROP TABLE IF EXISTS ProductAttributes")
cursor.execute("""
    CREATE TABLE ProductAttributes (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert with some sparse columns populated
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Color, Size)
    VALUES (%(id)s, %(name)s, %(color)s, %(size)s)
""", {"id": 1, "name": "T-Shirt", "color": "Blue", "size": "Large"})

# Insert with different sparse columns
cursor.execute("""
    INSERT INTO ProductAttributes (ProductID, ProductName, Weight, Material)
    VALUES (%(id)s, %(name)s, %(weight)s, %(material)s)
""", {"id": 2, "name": "Coffee Mug", "weight": 0.35, "material": "Ceramic"})

conn.commit()

Insertion par ensemble de colonnes (XML)

Insérez simultanément plusieurs valeurs de colonnes éparses en passant du XML au jeu de colonnes.

# Create the table with a column set
cursor.execute("DROP TABLE IF EXISTS ProductAttributesWithSet")
cursor.execute("""
    CREATE TABLE ProductAttributesWithSet (
        ProductID INT PRIMARY KEY,
        ProductName NVARCHAR(100) NOT NULL,
        SparseAttributes XML COLUMN_SET FOR ALL_SPARSE_COLUMNS,
        Color NVARCHAR(50) SPARSE NULL,
        Size NVARCHAR(20) SPARSE NULL,
        Weight DECIMAL(10,2) SPARSE NULL,
        Material NVARCHAR(100) SPARSE NULL,
        Warranty INT SPARSE NULL,
        Manufacturer NVARCHAR(100) SPARSE NULL
    )
""")

# Insert using column set XML
cursor.execute("""
    INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
    VALUES (%(id)s, %(name)s, %(xml)s)
""", {
    "id": 3,
    "name": "Laptop Bag",
    "xml": "<Color>Black</Color><Size>Medium</Size><Material>Nylon</Material><Warranty>24</Warranty>"
})
conn.commit()

Insertion dynamique d’attributs

Créez une fonction qui accepte les attributs dynamiques comme un dictionnaire et construit automatiquement le XML.

def insert_with_attributes(cursor, product_id: int, name: str, attributes: dict):
    """Insert product with dynamic sparse column attributes."""
    # Build XML for column set
    xml_parts = [f"<{key}>{value}</{key}>" for key, value in attributes.items()]
    attributes_xml = "".join(xml_parts)
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": attributes_xml or None})

# Usage
insert_with_attributes(cursor, 4, "Headphones", {
    "Color": "Silver",
    "Warranty": 12,
    "Manufacturer": "AudioTech"
})
conn.commit()

Requête des colonnes clairsemées

Interrogez les colonnes clairsemées par nom, ou récupérez toutes les valeurs clairsemées d’un coup via l’ensemble de colonnes.

Interroger les colonnes individuelles

Interrogez des colonnes clairsemées spécifiques en utilisant la syntaxe standard SELECT.

# Query specific sparse columns
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size
    FROM ProductAttributes
    WHERE Color IS NOT NULL
""")

for row in cursor:
    print(f"{row.ProductName}: {row.Color}, {row.Size}")

Requête via l’ensemble de colonnes

Récupérez toutes les valeurs de colonnes clairsemées en XML à partir de l’ensemble de colonnes.

# Get column set XML
cursor.execute("""
    SELECT ProductID, ProductName, SparseAttributes
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
print(f"Product: {row.ProductName}")
print(f"Attributes XML: {row.SparseAttributes}")

Analyse XML de l’ensemble de colonnes en Python

Analysez le XML à partir du jeu de colonnes pour le convertir en dictionnaire Python afin de faciliter la manipulation.

from xml.etree import ElementTree as ET

def get_product_attributes(cursor, product_id: int) -> dict:
    """Get product with parsed sparse attributes."""
    cursor.execute("""
        SELECT ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        return None
    
    result = {"ProductName": row.ProductName}
    
    # Parse XML column set
    if row.SparseAttributes:
        # Wrap in root element for parsing
        xml_str = f"<root>{row.SparseAttributes}</root>"
        root = ET.fromstring(xml_str)
        
        for elem in root:
            result[elem.tag] = elem.text
    
    return result

# Usage
product = get_product_attributes(cursor, 3)
print(product)
# {'ProductName': 'Laptop Bag', 'Color': 'Black', 'Size': 'Medium', 'Material': 'Nylon', 'Warranty': '24'}

Requête avec SELECT *

Lorsque vous utilisez SELECT *, l’ensemble de colonnes revient comme une seule colonne XML au lieu de colonnes individuelles et clairsemées.

# SELECT * returns column set instead of individual sparse columns
cursor.execute("""
    SELECT * FROM ProductAttributesWithSet WHERE ProductID = %(id)s
""", {"id": 3})

row = cursor.fetchone()
# Returns: ProductID, ProductName, SparseAttributes (not individual columns)
print(f"Columns: {[col[0] for col in cursor.description]}")

Sélectionner explicitement chaque colonne individuellement

Pour récupérer des colonnes clairsemées individuelles d’un tableau avec un ensemble de colonnes, listez-les explicitement dans la clause SELECT.

# To get individual sparse columns with column set table, list them explicitly
cursor.execute("""
    SELECT ProductID, ProductName, Color, Size, Weight, Material, Warranty, Manufacturer
    FROM ProductAttributesWithSet
    WHERE ProductID = %(id)s
""", {"id": 3})

# Now each sparse column is available as separate property
row = cursor.fetchone()
print(f"Color: {row.Color}, Material: {row.Material}")

Mettre à jour les colonnes éparses

Mettez à jour individuellement les colonnes à valeurs éparses par leur nom, ou remplacez toutes les valeurs éparses en une seule opération via le XML de l’ensemble de colonnes.

Mettre à jour les colonnes individuelles

Mettez à jour des valeurs de colonnes clairsemées spécifiques en utilisant la syntaxe SQL UPDATE standard.

cursor.execute("""
    UPDATE ProductAttributes
    SET Color = %(color)s, Weight = %(weight)s
    WHERE ProductID = %(id)s
""", {"id": 1, "color": "Red", "weight": 0.2})
conn.commit()

Mise à jour via l’ensemble de colonnes

Remplacez toutes les valeurs de colonnes clairsemées en mettant à jour directement le XML de l’ensemble de colonnes.

# Replace all sparse column values via column set
cursor.execute("""
    UPDATE ProductAttributesWithSet
    SET SparseAttributes = %(xml)s
    WHERE ProductID = %(id)s
""", {
    "id": 3,
    "xml": "<Color>Navy</Color><Size>Large</Size><Material>Leather</Material>"
})
conn.commit()
# Note: This clears any sparse columns not included in the XML

Mise à jour partielle à travers un jeu de colonnes

Mettre à jour uniquement des attributs clairsemés spécifiques tout en préservant les valeurs des autres attributs non inclus dans la mise à jour.

# To update only specific attributes, merge with existing
def update_attributes(cursor, product_id: int, updates: dict):
    """Update specific sparse attributes while preserving others."""
    # Get current attributes
    cursor.execute("""
        SELECT Color, Size, Weight, Material, Warranty, Manufacturer
        FROM ProductAttributesWithSet
        WHERE ProductID = %(id)s
    """, {"id": product_id})
    
    row = cursor.fetchone()
    if row is None:
        raise ValueError(f"Product {product_id} not found")
    
    # Merge updates
    current = {
        "Color": row.Color,
        "Size": row.Size,
        "Weight": row.Weight,
        "Material": row.Material,
        "Warranty": row.Warranty,
        "Manufacturer": row.Manufacturer
    }
    
    for key, value in updates.items():
        current[key] = value
    
    # Build XML with non-null values
    xml_parts = []
    for key, value in current.items():
        if value is not None:
            xml_parts.append(f"<{key}>{value}</{key}>")
    
    cursor.execute("""
        UPDATE ProductAttributesWithSet
        SET SparseAttributes = %(xml)s
        WHERE ProductID = %(id)s
    """, {"id": product_id, "xml": "".join(xml_parts) or None})

# Usage
update_attributes(cursor, 3, {"Color": "Brown", "Warranty": 36})
conn.commit()

Modèles de colonnes dynamiques

Construisez des requêtes flexibles qui recherchent dynamiquement à travers des colonnes clairsemées, en utilisant des listes de permis pour valider les noms des colonnes et prévenir les attaques d’injection SQL.

Requête des données de type EAV

Recherchez des produits en fonction d’un nom et d’une valeur d’attribut spécifiques à l’aide de la correspondance au modèle Entité-Attribut-Valeur (EAV).

def find_products_by_attribute(cursor, attribute_name: str, attribute_value: str) -> list:
    """Find products with specific attribute value."""
    # Validate column name against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    if attribute_name not in allowed_columns:
        raise ValueError(f"Invalid attribute: {attribute_name}")
    
    cursor.execute(f"""
        SELECT ProductID, ProductName, {attribute_name}
        FROM ProductAttributesWithSet
        WHERE {attribute_name} = %(value)s
    """, {"value": attribute_value})
    
    return cursor.fetchall()

# Find all blue products
blue_products = find_products_by_attribute(cursor, "Color", "Blue")

Recherche flexible d’attributs

Recherchez des produits correspondant à plusieurs attributs optionnels en même temps.

def search_by_attributes(cursor, **attributes) -> list:
    """Search products by multiple optional attributes."""
    # Validate column names against allowed sparse columns to prevent SQL injection
    allowed_columns = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    invalid = set(attributes.keys()) - allowed_columns
    if invalid:
        raise ValueError(f"Invalid attributes: {invalid}")
    
    conditions = ["1=1"]  # Always true base condition
    params = {}
    
    for i, (key, value) in enumerate(attributes.items()):
        if value is not None:
            conditions.append(f"{key} = %(attr_{i})s")
            params[f"attr_{i}"] = value
    
    query = f"""
        SELECT ProductID, ProductName, SparseAttributes
        FROM ProductAttributesWithSet
        WHERE {' AND '.join(conditions)}
    """
    
    cursor.execute(query, params)
    return cursor.fetchall()

# Search by multiple attributes
results = search_by_attributes(cursor, Color="Black", Material="Nylon")

Considérations relatives aux performances

Évaluez quand des colonnes clairsemées offrent des avantages de stockage, évaluez les coûts généraux et utilisez la surveillance des performances pour optimiser la conception de vos colonnes clairsemées.

Quand utiliser des colonnes éparses

De bons candidats pour des chroniques épurées incluent :

• Colonnes avec plus de 60 à 70 % de valeurs NULL.
• Des tableaux larges avec de nombreuses colonnes optionnelles.
• Des charges de travail où l’optimisation du stockage est une priorité.

Évitez d’utiliser des colonnes clairsemées lorsque :

• La plupart des lignes ont des valeurs (chaque valeur non-NULL ajoute 4 octets de surcharge).
• La colonne est fréquemment utilisée dans les clauses WHERE.
• La colonne fait partie de l’index groupé.

Vérifiez les économies de stockage

Comparez la taille de stockage des versions clairsemées et non clairsemées d’une même table.

-- Compare storage with and without sparse
EXEC sp_spaceused 'ProductAttributes';
EXEC sp_spaceused 'ProductAttributesWithoutSparse';

Considérations sur l’indice

Vous pouvez indexer des colonnes éparses. Les index filtrés fonctionnent bien pour les données clairsemées car ils sautent les lignes NULL :

CREATE INDEX IX_Products_Color
ON ProductAttributes(Color)
WHERE Color IS NOT NULL;

Opérations en bloc

Optimisez les insertions de plusieurs produits avec des colonnes clairsemées en construisant un ensemble de colonnes XML en Python avant de passer les lignes à bulkcopy().

Insertion en bloc avec colonnes éparses

Utilisez la copie en bloc pour insérer efficacement plusieurs produits avec des attributs dispersés.

def bulk_insert_with_attributes(conn, products: list[dict]):
    """Bulk insert products with sparse attributes."""
    rows = []
    for product in products:
        attrs = product.get("attributes", {})
        xml = "".join(f"<{k}>{v}</{k}>" for k, v in attrs.items()) or None
        rows.append((product["id"], product["name"], xml))
    
    cursor = conn.cursor()
    result = cursor.bulkcopy("ProductAttributesWithSet", rows)
    return result["rows_copied"]

# Usage
products = [
    {"id": 100, "name": "Widget A", "attributes": {"Color": "Red", "Size": "Small"}},
    {"id": 101, "name": "Widget B", "attributes": {"Weight": 1.5, "Material": "Steel"}},
    {"id": 102, "name": "Widget C", "attributes": {}},  # No sparse attributes
]
bulk_insert_with_attributes(conn, products)
conn.commit()

Bonnes pratiques

Suivez les schémas de validation, utilisez des ensembles de colonnes pour plus de flexibilité, et surveillez la rareté des colonnes afin de garantir que votre conception de colonne clairsemée répond aux objectifs de performance et de maintenabilité.

Valider les valeurs des colonnes creuses

Validez que tous les attributs clairsemés correspondent à l’ensemble autorisé avant d’insérer les données.

# Sparse columns have the same constraints as regular columns
# The SPARSE keyword only affects storage

def validate_and_insert(cursor, product_id: int, name: str, attributes: dict):
    """Insert with validation."""
    allowed_attributes = {"Color", "Size", "Weight", "Material", "Warranty", "Manufacturer"}
    
    invalid = set(attributes.keys()) - allowed_attributes
    if invalid:
        raise ValueError(f"Unknown attributes: {invalid}")
    
    xml_parts = [f"<{k}>{v}</{k}>" for k, v in attributes.items()]
    
    cursor.execute("""
        INSERT INTO ProductAttributesWithSet (ProductID, ProductName, SparseAttributes)
        VALUES (%(id)s, %(name)s, %(xml)s)
    """, {"id": product_id, "name": name, "xml": "".join(xml_parts) or None})

Utilisez des ensembles de colonnes pour plus de flexibilité

Les ensembles de colonnes simplifient l’utilisation des colonnes éparses :

• Ajouter de nouvelles colonnes clairsemées sans modifications de code.
• Stockez les attributs dynamiques.
• Sérialisez et désérialisez automatiquement le XML.

Sans ensemble de colonnes, il faut des listes de colonnes explicites. Avec un ensemble de colonnes, la colonne XML gère automatiquement les attributs dynamiques.

Surveiller le pourcentage de valeurs NULL

Analysez le pourcentage de NULL pour une colonne afin de déterminer si elle est un bon candidat pour l’optimisation des colonnes clairsemées.

def analyze_sparseness(cursor, table: str, column: str) -> float:
    """Check if column is a good sparse candidate."""
    # Validate identifiers to prevent SQL injection
    import re
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_.]*$', table):
        raise ValueError(f"Invalid table name: {table}")
    if not re.match(r'^[A-Za-z_][A-Za-z0-9_]*$', column):
        raise ValueError(f"Invalid column name: {column}")

    cursor.execute(f"""
        SELECT 
            COUNT(*) AS TotalRows,
            SUM(CASE WHEN {column} IS NULL THEN 1 ELSE 0 END) AS NullRows
        FROM {table}
    """)
    
    row = cursor.fetchone()
    null_percentage = (row.NullRows / row.TotalRows * 100) if row.TotalRows > 0 else 0
    
    print(f"Column {column}: {null_percentage:.1f}% NULL")
    print(f"Recommendation: {'Good sparse candidate' if null_percentage > 60 else 'Keep as regular column'}")
    
    return null_percentage

Contenu connexe

• Mappages de types de données
• Données XML
• Opérations de copie en bloc
• Colonnes éparses de Microsoft SQL