BCP narzędzie do importu i eksportu masowego
Za pomocą narzędzia BCP, dostępnego z linii poleceń, możemy dokonywać importu / eksportu danych z bazy SQL Server do plików i odwrotnie. Korzystanie z tego narzędzia wymaga standardowych uprawnień, które trzeba posiadać aby móc pobrać dane z bazy i zapisać je w pliku. Będzie to możliwość połączenia się z serwerem (autentykacja) oraz do określonej bazy danych SQL Server (autoryzacja). Ponadto konieczny będzie dostęp do tabel lub widoków z których będziemy korzystali, oraz do systemu plików, w odpowiednim zakresie czytanie lub modyfikacji w zależności od kierunku operacji.
Narzędzie BCP (bulk copy) jest dostępne we wszystkich wersjach SQL Server (również Express). Dzięki temu że jest wywoływane z linii poleceń, wygodnie stosować je w skryptach automatyzujących pracę serwera. Jest też jedną z najbardziej wydajnych (najszybszych) metod przenoszenia danych pomiędzy bazą danych a systemem plików.
Eksport danych z bazy SQL Server do pliku
Przy eksporcie z bazy, konieczne będą uprawnienia do łączenia się z serwerem, bazą danych a także do wykonywania polecenia SELECT na obiektach z których chcemy pobierać dane. Dodatkowo potrzebować będziemy uprawnień do zapisu / modyfikacji plików w podanym katalogu docelowym. Poniżej zaprezentuję najważniejsze funkcjonalności BCP w praktycznych zastosowaniach – jeśli chcesz poznać wszystkie opcje – polecam MSDN / BOL.
Eksport całej zawartości tabeli do pliku CSV (tekstowego)
Pierwszym przykładem zastosowania bcp, będzie eksport całej zawartość tabeli Person.Person z bazy AdventureWorks2008. W tym celu, określam kierunek migracji danych – parametrem out (czyli export), z serwera, którego nazwę wskazuje flaga –S MyServer\SQLEXPRESS, do pliku tekstowego ExportedTable.csv w katalogu c:\temp. Do serwera połączymy się za pomocą Trusted Connection (flaga –T) czyli zintegrowanej autentykacji Windows.
C:\>bcp AdventureWorks2008.Person.Person out C:\temp\ExportedTable.csv -S MyServer\SQLEXPRESS -T –c
Jeśli na serwerze na którym wykonujemy powyższy skrypt, byłaby tylko jedna domyślna instancja SQL Server, moglibyśmy sobie podarować parametr –S.
Parametr –c, określa format w jakim będzie wykonywany eksport i określa on wszystkie wartości jako znakowe (typ CHAR).
Określenie separatora
Domyślnym separatorem wartości kolumn (atrybutów) dla każdego rekordu, będzie w przypadku użycia formatu -c, znak \t (tabulator). Zakończeniem wiersza złączony symbol \r\n oznaczający \n następny wiersz (next row) + \r od początku (carriage return inaczej CR). Jeśli nie zdefiniujemy ich jawnie (flagami separatora –t oraz końca rekordu –r) to będą stosowane w trakcie eksportu, w taki właśnie sposób.
Kolejny przykład pokazuje jak połączyć się z bazą za pomocą uwierzytelniania SQL oraz jak określić jawną definicję separatora (nadpisuje on domyślny tabulator).
Aby użyć konkretnego konta SQL (np. użytkownika ExpAcc z hasłem AlaM@K0ta), musimy określić nazwę konta –U (username) oraz jego hasło –P. Separator, tym razem będzie określony za pomocą znaku ‘,’ często stosowany w plikach CSV.
C:\>bcp AdventureWorks2008.Person.Person out C:\temp\ExportedTable.csv -S MyServer\SQLEXPRESS –U ExpAcc –P AlaM@K0ta –c –t,
Wybór separatora jest podyktowany środowiskiem do którego będziemy później importować te dane, ale także charakterem przechowywanych danych. Z punktu widzenia bcp, separatorem może być dowolny ciąg do 10 znaków. Równie dobrze moglibyśmy zapisać parametr –t jako –t@@@@.
Czasem stosując zwykły przecinek czy średnik, nie zapewnimy odpowiedniego oznaczenia końca wartości. Aby zobrazować problem, przeanalizuj poniższy przykład.
create table dbo.test
(
id int identity(1,1),
opis1 varchar(100),
opis2 varchar(100)
)
insert into dbo.test values ('jeden,dwa','trzy'),('cztery','pięć')
select * from dbo.test
Teraz wykonam eksport, określając separator jako przecinek. Zauważ, że pierwszy rekord zawiera ten znak w polu opis1.
C:\>bcp tempdb.dbo.test out c:\temp\test.txt -S MyServer\SQLEXPRESS -T -c -t, Starting copy... 2 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 Average : (2000.00 rows per sec.) C:\>bcp tempdb.dbo.test in c:\temp\test.txt -S MyServer\SQLEXPRESS -T -c -t, -E Starting copy... 2 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 Average : (2000.00 rows per sec.) C:\>
W poleceniu importu, zastosowałem dodatkowo flagę, której wcześniej nie omawiałem -E czyli keep identity, która pomimo istnienia kolumny IDENTITY, zachowuje wartości z eksportu. Sprwadźmy co znajduje się w testowej tabeli, po tak wykonanym procesie :
Ważne jest aby wybór separatora był przemyślany. Jeśli zastosowany znak, będzie występował w eksportowanych wartościach, wynik importu będzie błędny.
Kodowanie znaków UNICODE w BCP
Stosowana powyżej metoda, nie zapewnia odpowiedniej obsługi znaków kodowanych w UNICODE czyli typów nchar/nvarchar (kodowanych na dwóch bajtach). Jeśli chcesz wyeksportować takie dane, trzeba użyć flagi –w, która określa wynikowy format kodowania znaków na UNICODE.
W ramach testu utworzę tabelkę :
use tempdb;
Create table dbo.Unicode_polskie
(
opis nvarchar(10)
);
insert into dbo.Unicode_polskie values(N'Piękny'),(N'Dzień')
W przypadku eksportu znaków typu UICODE, musimy wykorzystać flagę -w określający kodowanie na dwóch bajtach :
C:\>bcp tempdb.dbo.Unicode_polskie out C:\temp\UniExport.csv -S MyServer\SQLEXPRESS -T -w
Eksport metadanych do pliku XML za pomocą BCP
W poprzednich przykładach wynikiem eksportu jest płaski plik z danymi w którym nie ma możliwości rozróżnienia typów danych (np. flaga –c, określała, że każda kolumna była typu znakowego). Informacje o strukturze, mogą być istotne w procesie odwrotnym – importu do innej bazy.
Pełen eksport – to dane oraz metadane, czyli informacji o strukturze zbioru, przechowywanego w pliku tj. typy danych atrybutów. Do tego celu musimy użyć dodatkowego pliku nazywanego plikiem formatu (format file).
Format file – zawiera opis struktury eksportowanego / importowanego zbioru. Narzędzie BCP umożliwia jego automatyczne utworzenie.
C:\>bcp AdventureWorks2008.Person.Person format nul -T -S MyServer\SQLEXPRESS -c -x -f C:\temp\FileFormatPersonPerson.xml
W ten sposób, pełna informacja o wyeksportowanym zbiorze zawarta jest w dwóch plikacj – pliku z danymi (csv) i pliku ze strukturą zbioru (xml).
Eksport wyniku kwerendy do pliku
Eksportując dane, zazwyczaj interesuje nas pewien fragment zbioru – czyli wynik określonej kwerendy. BCP oprócz pokazanych wcześniej możliwości eksportu całego zbioru, umożliwa określenie kwerendy za pomocą operatora queryout. Załóżmy, że chcemy zapisać w pliku CSV wynik następującej kwerendy :
select p.FirstName, p.LastName, e.JobTitle, e.HireDate
from Person.Person p inner join HumanResources.Employee e
on p.BusinessEntityId = e.BusinessEntityId
where LastName = 'Miller'
Poniższe użycie bcp, wyeksportuje do pliku wynik zapytania SQL.
C:\>bcp "select p.FirstName, p.LastName, e.JobTitle, e.HireDate from Person.Person p inner join HumanResources.Employee e on p.BusinessEntityId = e.BusinessEntityId where LastName='Miller'" queryout C:\temp\ExportedQuery.csv -S MyServer\SQLEXPRESS -T -d AdventureWorks2008 -c -t;
Wszystkie flagi zastosowane w tym poleceniu były omówione wcześnniej – poza flagą –d określająca w jakiej bazie danych ma zostać wykonana kwerenda. Moglibyśmy użyć trój-członowej nazwy obiektów w kwerenedzie (AdventureWorks2008.Person.Person) – wynik byłby taki sam.
Istenienie tej flagi wynika między innymi z faktu, że każdy użytkownik ma domyślnie ustawioną bazę danych do której łączy się podczas nawiązywania nowej sesji z serwerem.
Eksport danych do pliku XML za pomocą BCP
W poprzednim przykładzie pokazałem, że można eksportować wynik dowolnej kwerendy do pliku. Eksport danych w postaci XML z bazy SQL Server za pomocą bcp sprowadza się do wykorzystania rozszerzenia języka T-SQL, klauzuli FOR XML.
Polecenie FOR XML (w czterech trybach RAW,AUTO,EXPLICIT i PATH) służy do przekształcenia wyniku kwerendy z postaci relacyjnej (tabelarycznej), na postać hierarchiczną XML. Jej opis jest dobrym tematem na osobny artykuł, w tym przykładzie zastosouje jedynie jej najuboższy tryb (RAW). Definicja naszej kwerendy, kształtujaca XML będzie następująca :
SELECT ProductID, Name, ListPrice
FROM Production.Product
where Name like '%CHAIN%'
FOR XML RAW, root('Content')
W wyniku otrzymamy poprawny plik XML :
Pełna składnia polecenia bcp, która nam wyeksportuje ten plik automatycznie, będzie wyglądała tak :
bcp "SELECT ProductID, Name, ListPrice FROM Production.Product where Name like '%CHAIN%' FOR XML RAW, root('Content') " queryout "c:\temp\sample.xml" -S MyServer\SQLEXPRESS -T -d AdventureWorks2008 –w –r -t
Import danych z pliku do bazy SQL Server
Operacje w przeciwnym kierunku, importowania danych z pliku do bazy SQL, określa parametr in w poleceniu bcp. Składnia jest praktycznie identyczna.
Każda operacja importu jest realizowana jako dodawanie zbioru wierszy do już istniejącej tabeli (obiekt docelowy musi istnieć). W naszym przykładzie na początek utworzę identyczną strukturę tabeli docelowej jak ta, z której eksportowałem dane.
-- kopiowanie podstawowej struktury tabeli
SELECT * INTO AdventureWorks2008.Person.PersonArch
from AdventureWorks2008.Person.Person where 1=2
Następnie poleceniem BCP załadujemy do nowej tabeli całą zawartość pliku, wyeksportowanego w przykładzie pierwszym.
C:\>bcp AdventureWorks2008.Person.PersonArch in C:\temp\ExportedTable.csv -S MyServer\SQLEXPRESS -T –c
Wartości NULL
Istotną kwestią w operacjach typu import, jest zachowanie związane z wartościami pustymi oraz nieokreślonymi (NULL). Są one rozróżniane w eksportowanym pliku, choć na pierwszy rzut oka nie można stwierdzić (zajrzyj do zawartości eksportowanego pliku). Operacje OUT i IN w sumarycznym wyniku muszą być spójne. Sprawdźmy zatem czy faktycznie są.
USE tempdb
GO
CREATE TABLE dbo.test
(
id int identity(1,1),
opis varchar(10),
opis2 varchar(10),
);
INSERT INTO test VALUES('jeden','dwa'),('pięć','trzy'),('','cztery'),
(null,null),('',null),('',''),(' ','spacja')
-- skopiowanie struktury
SELECT * INTO dbo.test2 FROM dbo.test WHERE 1=2
-- co będziemy chcieli eksportować
SELECT * FROM dbo.test
Mamy zatem zdefniowaną tabelę dbo.test z której będziemy eksportowali oraz dbo.test2 w tej samej bazie, do której będziemy importować dane. Wykonajmy następujące operacje out oraz in .
Najpierw EXPORT.
C:\Users\jakub.kasprzak>bcp tempdb.dbo.test out c:\temp\test.csv -S MyServer\SQLEXPRESS -T -w -t;
Zawartość wyeksportowanego pliku nie zdradza informacji o NULLach, są one jednak odpowiednio kodowane jako znaki specjalne.
Teraz IMPORT :
C:\Users\jakub.kasprzak>bcp tempdb.dbo.test2 in c:\temp\test.csv -S MyServer\SQLEXPRESS -T -w -t;
Sprawdźmy teraz zawrtości tabel – wynik będzie identyczny.
-- stąd eksportowałem
select * from dbo.test
-- tutaj importowałem
select * from dbo.test2
Problemem, w tym procesie exportu/importu, mogą okazać się wartości domyślne zdefiniowane w kolumnach tabeli do której importujemy dane.
Przejdźmy jeszcze raz przez cały proces, ale zmodyfikujemy definicję kolumny opis2 w tabeli dbo.test2, dodając jej wartość domyślną.
-- na początek czyszczenie tabeli dbo.test2
TRUNCATE TABLE dbo.test2
-- dodanie wartości domyślnej dla kolumny opis2
ALTER TABLE dbo.test2 ADD DEFAULT ('DEFAULT') FOR opis2
Wynik operacji IMPORT/EXPORT, tym razem będzie różny. Wszędzie tam gdzie jest w kolumnie opis2 NULL, przypisana zostanie wartość domyślna 'DEFAULT’.
Jeśli chcemy zachować wartości NULL z eksportu, trzeba zastosować flagę -k (keep nulls) w poleceniu BCP. W ten sposób możemy zagwarantować poprawność procesu OUT/IN czyli EXPORT + IMPORT da ten sam rezultat.
C:\Users\jakub.kasprzak>bcp tempdb.dbo.test2 in c:\temp\test.csv -S MyServer\SQLEXPRESS -T -w -t; -k
BCP w praktyce – wskazówki
Wydajność
Przy dużych importach, kiedy zależy nam na czasie i minimalizacji okna serwisowego, warto wziąć pod uwagę szereg dodatkowych czynności, które mogą pomóc w optymalizacji procesu importu.
Rozważyć należy wyłączenie mechanizmów spowalniających operacje INSERT związanych z zapewnieniem integralności i spójności danych. Mam tu na myśli ograniczenia (CONSTRAINTS), procedury wyzwalane (TRIGGERS), czy klucze obce. Ich obecność i rola w bazach jest nie do przecenienia, jednak ich istnienie wpływa negatywnie na wydajność operacji związanych z importem.
Drugą kwestią są indeksy, które również bezpośrednio wpływają na degradację operacji INSERT, UPDATE i DELETE. Każda taka czynność pociąga za sobą konieczność aktualizacji danych w indeksie. Stosowanym w praktyce rozwiązaniem jest usuwanie indeksu i jego odbudowa. Ma to sens wtedy, gdy do przygotowanej struktury zawierającej niewielką liczbę danych importujemy duże ilości rekordów zasilających. Alternatywą do tego działanie jest import już posortowanych danych, zgodnie z definicją indeksu klastrowego, w celu minimalizacji wpływu na jego przebudowę i aktualizację.
Kolejna kwestia to potencjalne obciążenie serwera ze względu na zarządzanie wielodostępem do danych, które w SQL Server zapewniają blokady. Są one zakładane na różnych poziomach np. wiersza, strony, tabeli. Cel jest taki, żeby zminimalizować liczbę blokad, tym samym operacji które muszą być wykonane równolegle do importu. Zakładając blokadę na poziomie tabeli, możemy również zwiększyć wydajność operacji typu BULK. W tym celu możemy posłużyć się dodatkową flagę związaną ze wskazówkami dla silinika relacyjnego –h (hints).
Logowanie transakcji
Importując dane do bazy w modelu odzyskiwania Full Recovery Model, liczyć należy się z obciążeniem logu transakcyjnego. Każdy insert, będzie logowany. Trzeba to przewidzieć i zapewnić odpowiednią obsługę pliku logu (opcja Autogrow, wolne miejsce na dysku).
Jeśli nasze środowisko nie wymaga, tak precyzyjnego logowania, przy dużej liczbe masowych importów, sugerowana jest zmiana na tryb Bulk-ogged.