Discussion :

[StringBuilder]

Bonjour,

Dans le cadre d'une optimisation d'un outil d'import de données, je suis à la recherche d'une solution la plus efficace possible pour insérer en masse des données issues de fichiers CSV de taille très variable (certains peuvent faire près de 1 Go).

Au départ, je suis bêtement parti sur du BULK INSERT.
Sauf que daprès la documentation :
- Le CSV n'est pas pris en charge
- Visiblement les données non textuelles doivent être stockées en binaire dans le fichier (si j'ai bien compris)
Bref, ça n'a pas l'air d'être la bonne solution.

Reste donc le chargement des données à l'aide d'un programme, qui va parser le CSV et va l'importer sous forme de INSERT.

Sauf que SQL Server ne supporte pas les INSERT de plusieurs lignes (tout du moins, pas avec la clause VALUES).

Restent donc différentes solutions, et j'espère d'autres :
Passer par une table temporaire :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
insert into #tmp values (...);
[...]
insert into #tmp values (...);
 
insert into destination select * from #tmp;

Faire un INSERT de SELECT :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
with cte (...)
as
(
   select ...
   union all
   [...]
   select ...
)
insert into destination select * from cte;

Éventuellement un mix des deux (?).

Je souhaite ne pas passer par un fichier intermédiaire (genre pas moyen de recopier le CSV dans un format compatible avec le bulk insert).

Les éventuels triggers et autres contraintes doivent être respectés.

Il ne doit pas y avoir d'interruption de service au niveau de la base.

[SQLpro]

Bonjour,

Dans le cadre d'une optimisation d'un outil d'import de données, je suis à la recherche d'une solution la plus efficace possible pour insérer en masse des données issues de fichiers CSV de taille très variable (certains peuvent faire près de 1 Go).

Au départ, je suis bêtement parti sur du BULK INSERT.
Sauf que daprès la documentation :
- Le CSV n'est pas pris en charge

Ha bon... Montrez moi votre référence ! EN fait n'importe quel délimitation peut être prise en charge par le BULK INSERT, il suffit d'indiquer le bon FILEDTERMINATOR qui est la virgule et le bon ROWTERMINATOR qui est \n !

- Visiblement les données non textuelles doivent être stockées en binaire dans le fichier (si j'ai bien compris)

CSV concerne du texte.... S'il y a des données autres elles doivent être stockées sous forme d'octets, les octets représentant du texte dans un fichier texte... Aucune difficulté là dedans, sauf si par malheur vous avez un octet à 44 ! (virgule)

Bref, ça n'a pas l'air d'être la bonne solution.

Reste donc le chargement des données à l'aide d'un programme, qui va parser le CSV et va l'importer sous forme de INSERT.

Sauf que SQL Server ne supporte pas les INSERT de plusieurs lignes (tout du moins, pas avec la clause VALUES).

Là encore vous vous trompez, l'INSERT vectoriel est supporté mais limité (je crois à 10000 lignes)...

Restent donc différentes solutions, et j'espère d'autres :
Passer par une table temporaire :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
insert into #tmp values (...);
[...]
insert into #tmp values (...);
 
insert into destination select * from #tmp;

Faire un INSERT de SELECT :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
with cte (...)
as
(
   select ...
   union all
   [...]
   select ...
)
insert into destination select * from cte;

Éventuellement un mix des deux (?).

Je souhaite ne pas passer par un fichier intermédiaire (genre pas moyen de recopier le CSV dans un format compatible avec le bulk insert).

Les éventuels triggers et autres contraintes doivent être respectés.

Ca c'est la seule chose importante de votre demande.... Dans ce cas
1) insérer dans une table temporaire déstructurée (toutes les colonnes en VARCHAR ou NVARCHAR)
2) corriger les éventuelles anomalies
3) reporter l'insertion dans la table définitive

Il ne doit pas y avoir d'interruption de service au niveau de la base.

A +

[StringBuilder]

Bonsoir, je réponds un peu tard, désolé.

Ha bon... Montrez moi votre référence ! EN fait n'importe quel délimitation peut être prise en charge par le BULK INSERT, il suffit d'indiquer le bon FILEDTERMINATOR qui est la virgule et le bon ROWTERMINATOR qui est \n !

Pourtant, c'est bien écrit dans la documentation en ligne de la commande BULK INSERT de SQL Server 2014 :
https://msdn.microsoft.com/fr-fr/lib...=sql.120).aspx

Interopérabilité
Importation des données d'un fichier CSV
Les fichiers de valeurs séparées par des virgules (CSV, Comma-Separated Value) ne sont pas pris en charge par les opérations d'importation en bloc SQL Server. Toutefois, dans certains cas, un fichier CSV peut être utilisé comme fichier de données pour une importation en bloc de données dans SQL Server. Pour plus d'informations sur les conditions requises pour importer les données d'un fichier CSV, consultez Préparer des données en vue d'une exportation ou d'une importation en bloc (SQL Server).

Partant de là, j'ai hésité à utiliser cette méthode pour charger du CSV...

CSV concerne du texte.... S'il y a des données autres elles doivent être stockées sous forme d'octets, les octets représentant du texte dans un fichier texte... Aucune difficulté là dedans, sauf si par malheur vous avez un octet à 44 ! (virgule)

CSV, c'est "Coma Separated Values", sous forme d'un fichier TEXTE. Par définition, l'ensemble des VALEURS présentes dans le document sont forcément sous une forme textuelle et non binaire.
Ainsi, si j'ai le nombre "trente six", j'aurai dans mon fichier "36" et non "*00100100‬" en binaire (ou "$" une fois converti en binaire et affiché sous forme de texte ASCII).
Par conséquent, si dans mon fichier j'ai des valeurs numériques (ou date, etc.) elles seront sérialisées sous forme de texte en utilisant une locale déterminée : BULK INSERT ne saura visiblement pas relire ces informations autrement que sous forme de chaînes de caractères, obligeant à refaire des traitements lourds pour les transformer en données typées.

Là encore vous vous trompez, l'INSERT vectoriel est supporté mais limité (je crois à 10000 lignes)...

Alors ça, ça m'intéresse. Avez-vous un exemple de syntaxe ?
J'entends par insertion multiple à l'aide de l'instruction "insert values" une syntaxe du même style que celle de MySQL :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO matable (id, label) values (1, 'toto'), (2, 'titi'), (3, 'tata');

Ce qui permettrait d'insérer en lot plusieurs lignes sans devoir passer par des objets temporaires (CTE, sous-requête, etc.)
Je sais qu'en utilisant C# par exemple, on peut stocker par exemple un int[] dans un varbinary, mais on est loin de ce que je recherche.
Autre solution, faire en langage CLR une procédure qui prend un varchar(max) en paramètre, puis effectue les INSERT en passant par la connexion de contexte... Certainement à peine plus rapide que si on lance des lots d'INSERT, mais moins souple.

Ca c'est la seule chose importante de votre demande.... Dans ce cas
1) insérer dans une table temporaire déstructurée (toutes les colonnes en VARCHAR ou NVARCHAR)
2) corriger les éventuelles anomalies
3) reporter l'insertion dans la table définitive

A +

Ok, mais moi je cherche à optimiser l'étape 1.

J'ai par exemple un fichier de 10 000 000 lignes, et je souhaite qu'il soit inséré en moins de 1 minute.
=> On peut aisément faire un SELECT INTO de 10 000 000 lignes à partir d'une autre table dans ce délai, donc je cherche une solution pour faire la même chose. Jusqu'à présent, j'en suis plutôt à 100 000 lignes par minute, ce qui est loin d'être satisfaisant...

[iberserk]

INSERT INTO matable (id, label) values (1, 'toto'), (2, 'titi'), (3, 'tata');

Cette syntaxe existe belle et bien pour SQL SERVER...

Via du C# également vous pouvez aisement utiliser l'object SqlBulkCopy qui permet, à partir de n'importe quel objet en mémoire (par forcement du datareader...) insérer en bulk ....

Voir ce lien: https://msdn.microsoft.com/fr-fr/lib...v=vs.110).aspx

[iberserk]

Via du C# [...]

Avec du .net pour être précis :-)

[StringBuilder]

Merci pour ces précisions !

Depuis quelle version c'est supporté la syntaxe avec plusieurs séries de values ? (pourtant pas vu cette syntaxe dans la doc actuelle !)

Sinon, génial l'astuce de SqlBulkCopy, ça pourrait bien résoudre en grande partie ma problématique

-- Edit :
Ah, oui effectivement, j'avais pas du le [,... n] dans la syntaxe

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
[ WITH <common_table_expression> [ ,...n ] ]
INSERT 
{
        [ TOP ( expression ) [ PERCENT ] ] 
        [ INTO ] 
        { <object> | rowset_function_limited 
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ] 
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ] 
        | derived_table 
        | execute_statement
        | <dml_table_source>
        | DEFAULT VALUES 
        }
    }
}
[;]

[iberserk]

Pour le SqlBulkCopy vous pouvez ainsi aisément l'inclure dans une étape d'un lot SSIS par exemple...

[elsuket]

Effectivement le SqlBulkCopy est très performant puisqu'il utilise ... BULK INSERT !
Un petit coup de SQL Profiler vous le montre.

Il est tout à fait possible d'importer un fichier CSV dans une table, à l'aide du squelette d'instruction suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
BULK INSERT maTable
FROM 'C:\dossier\monFichier.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

Je l'ai encore fait pour un collègue cette semaine pour importer des fichiers de sortie JMeter, en utilisant la méthode décrite par SQLPro :

1) insérer dans une table temporaire déstructurée (toutes les colonnes en VARCHAR ou NVARCHAR)
2) corriger les éventuelles anomalies
3) reporter l'insertion dans la table définitive

@++