Efektywne ładowanie danych do bazy – klasa SqlBulkCopy

2012-04-26 

Od .NET 2.0 dostępna jest klasa System.Data.SqlClient.SqlBulkCopy, która pozwala na efektywne ładowanie dużych zbiorów danych do bazy SQL Server. Główną zaletą tej klasy jest wydajność operacji kopiowania danych porównywalna z instrukcją bulk insert dostępną z poziomu języka T-SQL. Bezpośrednim źródłem danych (przekazywanym do metody WriteToServer) może być tablica obiektów DataRow, obiekt DataTable lub obiekt implementujący interfejs IDataReader. Parametry operacji kopiowania ustawiamy poprzez następujące właściwości:

  • BatchSize – liczba rekordów w paczce danych (dane do bazy wysyłane są paczkami)
  • BulkCopyTimeout – maksymalny czas wykonywania operacji podany w sekundach
  • ColumnMappings – kolekcja obiektów SqlBulkCopyColumnMapping, które definiują mapowanie kolumn źródła danych na kolumny docelowej tabeli w bazie danych
  • DestinationTableName – nazwa docelowej tabeli w bazie danych
  • NotifyAfter – liczba rekordów, po przetworzeniu których wywoływane jest zdarzenie SqlRowsCopied

Konstruktor klasy SqlBulkCopy oprócz obiektu SqlConnection lub connection stringa do docelowej bazy danych może przyjąć także obiekt SqlBulkCopyOptions pozwalający na określenie dodatkowych opcji. Jest to enum z następującymi elementami:

  • Default – domyślne ustawienia wszystkich opcji
  • CheckConstraints – włączenie sprawdzania constraintów, domyślnie nie są sprawdzane
  • FireTriggers – wywoływanie triggerów po załadowaniu każdej paczki danych, domyślnie triggery nie są wywoływane
  • KeepIdentity – wstawianie w kolumnę ustawioną jako Identity wartości ze źródła danych (analogicznie do instrukcji T-SQL: SET IDENTITY_INSERT ON), domyślnie wartości źródłowe zastępowane są numeratorem kolumny
  • KeepNulls – wstawianie wartości NULL ze źródła w kolumnę z wartością Default, domyślnie w przypadku NULL-a wstawiane są wartości Default
  • TableLock – blokowanie tabeli na czas ładowania danych, domyślnie blokady zakładane są na rekordy
  • UseInternalTransaction – ładowanie każdej paczki danych w transakcji

Działanie omawianej klasy pokażę na przykładzie własnej metody wstawiającej dane z pliku tekstowego do tabeli w bazie SQL Server. Metoda jako parametry przyjmuje obiekt SqlConnection do bazy danych, nazwę tabeli docelowej oraz ścieżkę do pliku źródłowego. W wyniku zwraca ilość załadowanych rekordów. Zakładam, że wartości w pliku rozdzielane są tabulacją. Metoda na początku odczytuje dane z pliku i umieszcza je w obiekcie DataTable, następnie poprzez metodę WriteToServer ładuje dane z obiektu DataTable do bazy. W tym przykładzie nie użyłem właściwości ColumnMappings co oznacza, że kolumny ze źródła mapowane są na odpowiadające im w kolejności kolumny tabeli docelowej. Dodatkowo ustawiłem opcje obiektu SqlBulkCopy na KeepIdentity i FireTriggers.

private int BulkCopy(SqlConnection sqlConnection, string destinationTable, string fileName)
{
    DataTable sourceTable = new DataTable();
    string fileLine;
    string[] rowData;
    bool firstRow = true;
    int recordCount = 0;
    try
    {
        using (System.IO.StreamReader sr = System.IO.File.OpenText(fileName))
        {
            while ((fileLine = sr.ReadLine()) != null)
            {
                rowData = fileLine.Split('\t');
                if (rowData.Length > 0)
                {
                    if (firstRow)
                    {
                        foreach (var item in rowData)
                        {
                            sourceTable.Columns.Add(new DataColumn());
                        }
                        firstRow = false;
                    }
                    DataRow row = sourceTable.NewRow();
                    row.ItemArray = rowData;
                    sourceTable.Rows.Add(row);
                }
            }
        }

        SqlBulkCopyOptions bulkOptions =
            SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.FireTriggers;

        using (SqlBulkCopy bulkCopy =
            new SqlBulkCopy(sqlConnection.ConnectionString, bulkOptions))
        {
            bulkCopy.DestinationTableName = destinationTable;
            bulkCopy.BulkCopyTimeout = 300;
            bulkCopy.BatchSize = 1000;
            bulkCopy.WriteToServer(sourceTable);
            recordCount = sourceTable.Rows.Count;
        }
    }
    catch (Exception e)
    {
        //Obsługa błędów
    }
    return recordCount;
}

Kilka uwag odnośnie stosowania klasy SqlBulkCopy:

  1. W przypadku kopiowania danych z jednej bazy do tabeli w innej bazie, jako źródło danych najlepiej przekazać obiekt SqlDataReader zwracany przez metodę SqlCommand.ExecuteReader.
  2. W przypadku domyślnego mapowania pól, źródło może mieć mniej kolumn niż tabela docelowa (wówczas w dodatkowe kolumny wstawione zostaną wartości domyślne lub NULL), większa liczba kolumn w źródle niż w tabeli docelowej nie jest dopuszczalna.
  3. Podczas próby ładowania danych może pojawić się następujący błąd:
    "Bulk copy failed. User does not have ALTER TABLE permission on table 'DestinationTable'. ALTER TABLE permission is required on the target table of a bulk copy operation if the table has triggers or check constraints, but 'FIRE_TRIGGERS' or 'CHECK_CONSTRAINTS' bulk hints are not specified as options to the bulk copy command."
    Wystąpi on w sytuacji gdy docelowa tabela posiada triggery lub constrainty, a użytkownik, w kontekście którego łączymy się z bazą nie ma uprawnień ALTER TABLE wymaganych do wyłączenia ich działania. Próba ich wyłączenia wynika z domyślnych ustawień SqlBulkCopyOptions. Rozwiązaniem jest albo nadanie użytkownikowi odpowiednich uprawnień do tabeli (jeżeli nie chcemy wywoływać triggerów lub constraintów), albo ustawienie opcji CheckConstraints i/lub FireTriggers (wówczas triggery lub constrainty będą działały).
  4. Z mojego doświadczenia wynika, że ustawienie opcji UseInternalTransaction nie ma żadnego znaczenia. Zawsze w przypadku błędu podczas kopiowania wszystkie dane załadowane we wcześniejszych paczkach pozostają w tabeli, dane z bieżącej paczki są anulowane, a proces dalszego kopiowania jest przerywany. Nawet MSDN podaje identyczne zachowanie w obu przypadkach:
    "Performing a Non-transacted Bulk Copy Operation:
    In this first example, the bulk copy operation is non-transacted. All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches.
    Performing a Dedicated Bulk Copy Operation in a Transaction:
    In this example, the bulk copy operation manages its own transactions. All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches."

Więcej o klasie SqlBulkCopy: MSDN

Reklamy

Posted on 2012-04-26, in .NET/C# and tagged , , . Bookmark the permalink. Dodaj komentarz.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s

%d blogerów lubi to: