In-band bulk import met SqlServer

Door GrimaceODespair op maandag 16 december 2013 01:53 - Reacties (4)
CategorieŽn: C#, SqlServer, Views: 2.725

Telkens als ik grote hoeveelheden data moet importeren in SQLServer, zoek ik het even opnieuw op, en kom ik er ook weer telkens opnieuw achter dat dat kan met BCP (command line) of met BULK INSERT (SQL commando). En beide opties hebben hetzelfde nadeel: het leent zich niet voor een dynamisch systeem, omdat je ( A ) het schema van de tabel in een format file nodig hebt en ( B ) de data in een bestand moet gooien. Bovendien duurt het altijd weer even voordat je de juiste configuratie-opties op de commandline hebt achterhaald.

Aangezien ik me niet zomaar kan neerleggen bij dergelijke beperkingen, vroeg ik me af of het misschien mogelijk was om de fysieke bestanden te vervangen door named pipes. Dat zou namelijk mogelijkheden openen om dergelijk imports niet "out-of-band" te doen, maar rechtstreeks in C#-code.

Een kleine speurtocht later bleek het antwoord op die vraag gedeeltelijk "ja". Dat antwoord was voldoende voor een proof of concept dat uiteindelijk resulteerde in een NuGet package.
Doel
Als we erin slagen streams aan te leveren aan SqlServer, kunnen we zonder tussenformaat data in bulk aanleveren aan SqlServer:

C#:
1
2
3
4
5
6
7
8
9
10
var values = new[]
                {
                  new[] {"value1", "value2"}, // Row 1
                  new[] {"value3", "value4"}  // Row 2
                };

  using (var importer = new Importer(SqlConnection, "MyTable", "Column1", "Column2"))
  {
    importer.Import(values);
  }



Daarvoor moeten we echter eerst een paar hordes overwinnen.
BULK INSERT
De syntax van een BULK INSERT in SQLServer ziet er als volgt uit:


code:
1
BULK INSERT MyTable FROM 'C:\Pad\Naar\Data.csv' WITH (C:\Pad\Naar\FormatFile.xml)



Zoals de thread op StackOverflow aangeeft, kan het databestand ook een stream/named pipe zijn:


code:
1
BULK INSERT MyTable FROM '\\.\pipe\MyPipe' WITH (C:\Pad\Naar\FormatFile.xml)



Veel moeilijker dan deze opdrachtregel wordt het in SQLServer niet meer. De crux zit hem in het aanleveren van zowel het schema als de data.
FormatFile
Voor het genereren van de FormatFile hebben we in een paar opties.
  • Reguliere FormatFile genereren met BCP
  • XML-FormatFile genereren met BCP
  • XML-FormatFile zelf genereren
Als het even kan zou ik graag geen afhankelijkheid van BCP introduceren, dus koos ik voor optie 3 en de heavy lifting zelf te doen om de XML te genereren. Gelukkig bestaat er een XSD van dit bestandsformaat dat we met behulp van XSD.EXE kunnen ombouwen tot een serializeerbare class.


XML:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<xsd:complexType name="bcpFormatType">
        <xsd:sequence>
            <xsd:element name="RECORD" type="bl:recordType">
            </xsd:element>
            <xsd:element name="ROW" type="bl:rowType">
                <xsd:unique name="oneColumnForEachField">
                    <xsd:selector xpath="bl:COLUMN"/>
                    <xsd:field xpath="@SOURCE"/>
                </xsd:unique>
                <xsd:unique name="uniqueColumnName">
                    <xsd:selector xpath="bl:COLUMN"/>
                    <xsd:field xpath="@NAME"/>
                </xsd:unique>
                <xsd:keyref name="columnReferencesField" refer="bl:uniqueFieldID">
                    <xsd:selector xpath="bl:COLUMN"/>
                    <xsd:field xpath="@SOURCE"/>
                </xsd:keyref>
            </xsd:element>
        </xsd:sequence>
    </xsd:complexType>



Een FormatFile bevat 2 belangrijke elementen: de structuur van de database en de structuur van de importgegevens:


C#:
1
2
3
4
5
var format = new bcpFormatType
   {
     RECORD = fieldTypeArray, // Beschrijving van de databasevelden
     ROW = columnTypeArray, // Beschrijving van de importvelden
   };



Om de generator te kunnen schrijven, hebbben we dus het database-schema nodig. In SqlServer kunnen we dat met volgende simpele query opvragen:


SQL:
1
2
3
SELECT column_name, data_type, is_nullable, character_maximum_length, collation_name
  FROM information_schema.COLUMNS
  WHERE table_name = 'MyTable'



Vervolgens kunnen we met de verkregen kolomgegevens zowel het RECORD- als het ROW-veld van het de FormatFile vullen.

Aan het eind van de rit hebben we dus een FormatFile-generator die een stuk handzamer is dan een Processaanroep die er ongeveer zo uitziet:


code:
1
bcp.exe import format nul -f "formatfile" -c -t \0 -r \0\0 -x -T -d "database" -S "server"



Verder nog de opmerking dat we voor het scheiden van velden het 0-karakter gebruiken. Doorgaans is het onhandig om dit als scheidingsteken te gebruiken binnen tekstbestanden, maar aangezien we zelf deze "tekstbestanden" (lees: streams) aanmaken, hebben we hier volledige controle over. De keuze voor 0 dient ook om de kans te reduceren dat we ooit data genereren waarbij het scheidingsteken in de data voorkomt.
Data stream
Tenslotte moeten we nog de data aanleveren, wat makkelijker gezegd is dan gedaan, want SqlServer is redelijk kieskeurig wat betreft randvoorwaarden betreft, waardoor je tijdens het ontwikkelen regelmatig tegen een broken pipe aankijkt zonder verdere info.

Belangrijkste pijnpunten:
  • SqlServer sluit de eerste pipe die hij tegenkomt, en doet niets (?) met de gelezen data. Je moet dan de volgende pipe hebben klaarstaan die vervolgens de data serveert.
  • SqlServer sluit ook de tweede pipe wanneer de data geflusht wordt. Alle gegevens moeten dus eerst gebufferd worden in de pipe, en dan pas doorgegeven.
Als we deze code runnen voordat we de IMPORT aanroepen op SqlServer, zal deze laatste verbinding maken met onze named pipe, en zijn we eindelijk klaar om onze data te streamen.

Vanaf dan is het enkel een kwestie van het ongebufferd aanleveren van 0-karakter-gescheiden gegevens:

C#:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
using (var stringWriter = new StringWriter())
  {
    foreach (var dataValues in _dataValueArrays)
    {
      foreach (var dataValue in dataValues)
      {
        stringWriter.Write(dataValue + "\0");
      }
      stringWriter.Write("\0");
    }

    var buffer = Encoding.UTF8.GetBytes(stringWriter.ToString());
    Stream.Write(buffer, 0, buffer.Length);
  }

Performance
Enkele niet-gestructureerde premature testen lijken erop te wijzen dat dit snellere imports mogelijk maakt dan hetgeen met transactionele INSERTs mogelijk is, maar structureel onderzoek zal hier meer duidelijkheid moeten brengen ;)

Volgende: Wordpress en private forks 11-'14 Wordpress en private forks
Volgende: Performance counters in C# 10-'13 Performance counters in C#

Reacties


Door Tweakers user Motrax, maandag 16 december 2013 10:02

Sprekend voor Oracle heeft een bulk load wel impact op je indexen: deze worden aan het einde gedropt en opnieuw aangemaakt (of was het van unusable naar usable)? Dus voor een online transactie systeem heeft dit wel wat impact...

Verder werk ik dagelijks met ETL tools en daar kan dit allang automatisch, maar vanuit een programmeertaal denkend is it een interessante benadering :)

Door Tweakers user GrimaceODespair, maandag 16 december 2013 21:49

Qua bulk import optimalisaties suggereert MS een TABLOCK en het gebruik van de ORDER hint bij lege tabellen.

Maar daar spelen dus ook de indexen op:
"Generally, it is faster to bulk import into a table without any indexes, than to bulk import into a table with indexes. Therefore, if an empty table has any indexes, you should drop them before you import data into the table and re-create them afterwards. If the data is not sorted on a clustered-key column and if the table is empty, drop all indexes, import the data, and then create new indexes."
En wat betreft ETL heb ik dan weer weinig ervaring. Vandaar dat deze "interessante benadering" eerder komt van een developer-reflex dan een DB Admin.

Door Tweakers user firefly112, dinsdag 17 december 2013 09:17

zelf doe ik dit altijd met openxml waarmee ik een temptable maak. en deze rechtstreek insert in de database.
http://technet.microsoft.com/en-us/library/ms186918.aspx

Door Tweakers user intGod, dinsdag 17 december 2013 11:34

Daar hebben ze dus SSIS voor uitgevonden.

Reageren is niet meer mogelijk