CSV vers SQL Server: cauchemar d'importations en masse (T-SQL et / ou Pandas)

Oct 19 2020

J'essaye d'insérer en bloc un .CSVfichier dans SQL Server sans grand succès.

Un peu de contexte:

1. J'avais besoin d'insérer 16 millions d'enregistrements dans une base de données SQL Server (2017). Chaque enregistrement a 130 colonnes. J'ai un champ dans le .CSVrésultat d'un appel API de l'un de nos fournisseurs que je ne suis pas autorisé à mentionner. J'avais des types de données entiers, flottants et chaînes.

2. J'ai essayé l'habituel: BULK INSERTmais je n'ai pas pu passer les erreurs de type de données. J'ai posté une question ici mais je n'ai pas pu la faire fonctionner.

3. J'ai essayé d'expérimenter avec python et j'ai essayé toutes les méthodes que je pouvais trouver, mais pandas.to_sqlpour tout le monde, cela était très lent. Je suis resté coincé avec des erreurs de type de données et de chaîne tronquée. Différent de ceux de BULK INSERT.

4. Sans beaucoup d'options, j'ai essayé pd.to_sqlet même s'il n'a soulevé aucune erreur de type de données ou de troncature, il a échoué en raison du manque d'espace dans ma base de données SQL tmp. Je ne pouvais pas non plus passer cette erreur même si j'avais beaucoup d'espace et que tous mes fichiers de données (et fichiers journaux) étaient définis sur une croissance automatique sans limite.

Je suis resté coincé à ce stade. Mon code (pour la pd.to_sqlpièce) était simple:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://@myDSN")

df.to_sql('myTable', engine, schema='dbo', if_exists='append',index=False,chunksize=100)

Je ne sais pas vraiment quoi essayer, tout conseil est le bienvenu. Tous les codes et exemples que j'ai vus traitent de petits ensembles de données (pas beaucoup de colonnes). Je suis prêt à essayer toute autre méthode. J'apprécierais tous les pointeurs.

Merci!

Réponses

2 Wilmar Oct 18 2020 at 23:10

Je voulais juste partager ce sale morceau de code juste au cas où cela aiderait quelqu'un d'autre. Notez que je suis bien conscient que ce n'est pas du tout optimal, c'est lent mais j'ai pu insérer environ 16 millions d'enregistrements en dix minutes sans surcharger ma machine.

J'ai essayé de le faire par petits lots avec:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("mssql+pyodbc://@myDSN")

a = 1
b = 1001

while b <= len(df):
    try:
        df[a:b].to_sql('myTable', engine, schema='dbo', if_exists='append',index=False,chunksize=100)
        a = b + 1
        b = b + 1000
    except:
        print(f'Error between {a} and {b}')
        continue

Moche comme l'enfer mais a travaillé pour moi.

Je suis ouvert à tous les critiques et conseils. Comme je l'ai mentionné, je publie ceci au cas où cela aiderait quelqu'un d'autre, mais j'ai également hâte de recevoir des commentaires constructifs.

1 DashrathChauhan Oct 18 2020 at 23:38

Le chargement des données de la trame de données pandas vers la base de données SQL est très lent et, lorsqu'il s'agit de grands ensembles de données, manquer de mémoire est un cas courant. Vous voulez quelque chose de beaucoup plus efficace que cela lorsqu'il s'agit de grands ensembles de données.

d6tstack est quelque chose qui pourrait résoudre vos problèmes. Parce qu'il fonctionne avec les commandes d'importation DB natives. Il s'agit d'une bibliothèque personnalisée spécialement conçue pour traiter les schémas ainsi que les problèmes de performances. Fonctionne pour XLS, CSV, TXT qui peuvent être exportés vers CSV, Parquet, SQL et Pandas.

1 ASH Jan 24 2021 at 11:30

Je pense que df.to_sqlc'est assez génial! Je l'utilise beaucoup ces derniers temps. C'est un peu lent, lorsque les ensembles de données sont vraiment énormes. Si vous avez besoin de vitesse, je pense que Bulk Insert sera l'option la plus rapide. Vous pouvez même faire le travail par lots, afin de ne pas manquer de mémoire et peut-être de surcharger votre machine.