Inspiracja , wiedza , realizacja
Jsystems

W przebudowie

Login



Java

Oracle

Linux

Android

PostgreSQL

Microsoft SQL Server

SQL*Loader

Data dodania: Jun 17, 2016
Data aktualizacji: Jun 17, 2016

Program SQL*Loader ładuje dane z zewnętrznych plików do tabel w bazie danych Oracle. Wykorzystuje dwa zasadnicze pliki: plik danych zawierający informacje, które mają być załadowane, oraz plik sterujący z informacjami o formacie danych, rekordów i pól w pliku danych, porządku, w jakim mają zostać załadowane. Teoretycznie informacje z pliku sterującego można by było umieścić w samym pliku danych, jednakże w praktyce się tego nie używa z uwagi na wielkokrotne wykorzystywanie pliku sterującego.


Uruchomienie programu powoduje automatyczne utworzenie pliku dziennika oraz pliku "złych" danych. W pliku dziennika zapisywany jest status ładowania danych, na przykład liczba przetworzonych i zatwierdzonych wierszy. Plik "złych" danych zawiera z kolei wszystkie wiersze, których załadowanie nie powiodło się z powodu błędnych danych, jak na przykład powtarzające się wartości klucza głównego.


Plik sterujący tworzy się na poziomie systemu operacyjnego np. za pomocą notanika w systemie Windows.


Na potrzeby zaprezentowania działania programu, utworzona została tabelka WORKERS




I został przygotowany plik z danymi, które zostaną załadowane do tej tabeli.




Następnie utworzony został plik sterujący:


LOAD DATA

INFILE 'D:\WORKERS.DSV'

BADFILE 'D:\WORKERS.BAD'

APPEND

INTO TABLE WORKERS

FIELDS TERMINATED BY ';'

(id,imie,nazwisko,telefon,mail,adres,miasto,kraj,kod,pensja,data_zatrudnienia,numer)




INFILE – oznacza plik, z którego mają zostać załadowane dane

BADFILE – plik w którym będą informacje o tym co się nie udalo załadować

APPEND – oznacza dodanie wierszy do tabeli

(można również użyć:

INSERT – wprowadzenie wierszy do pustej tabeli, jednakże jeśli nie będzie ona pusta, ładowanie danych nie powiedzie się i pojawi się komunikat o błędzie,

REPLACE – opróżnienie tabeli z istniejących w niej wierszy przy użyciu delete, a następnie wprowadznie do niej nowch wierszy,

TRUNCATE – opróżnienie tabeli z istniejących w niej wierszy przy użyciu truncate, a następnie wprowadznie do niej nowch wierszy)

INTO – określa tabelę do której chcemy załadować dane

FIELDS TERMINATED BY – separator jakim są oddzielone kolumny w pliku z danymi (nalezy pamietać aby wybrać taki seprator, który nie występuje w ładowanych danych)


Uruchomienie SQL*Loadera ze wskazaniem pliku sterującego oraz pliku dziennika:


SQLLDR HR/hr CONTROL='D:\USTAWIENIA.TXT' LOG='D:\USTAWIENIA.LOG'



I sprawdzenie tabeli, czy została uzupełniona o nowe wiersze:


SELECT * FROM WORKERS;




Dodatkowo w pliku dziennika możemy podejrzeć informacje o przebiegu ładowania danych.




Dodatkowo w pliku sterującym możemy umieścić klauzulę when, dzięki której ograniczymy ładowanie wierszy, tylko do tych, które nas rzeczywiście interesują. W tym przykładzie został użyty warunek - WHEN KRAJ = 'Albania', co oznacza, że załadowane zostaną tylko te rekordy, dla których w kolumnie kraj wartość wynosi 'Albania'. Informacje o tym ile rekordów, w związku z uzyciem tego warunku zostało załadowanych znajdziemy w pliku LOG. Natomiast wiersze, które nie zostały załadowane zobaczymy w pliku DISCARD.



Plik sterujący:




Komenda polecenia:




I jeszcze wycinek informacji z pliku LOG.




Widok tego co zostało wrzucone do tabeli (wcześniejsze rekordy zniknęły ponieważ w pliku sterującym zamiast opcji APPEND został uzyty TRUNCATE) :




Podstawowe opcje jakie możemy użyć przy uruchamianiu SQL*Loadera:


USERID – nazwa użytkownika i hasło wykorzystywane podczas ładowania, oddzielone ukośnikiem

CONTROL – nazwa pliku sterującego

LOG nazwa pliku dziennika

BAD – nazwa pliku "złych danych"

DATA – nazwa pliku danych

DISCARD – nazwa pliku odrzuconych rekordów

DISCARDMAX – maksymalna ilość odrzuconych rekordów przedz zatrzymaniem ładowania. Domyślne dozwolone jest odrzucenie dowolnej liczby rekordów.

SKIP – liczba logicznych rekordów w pliku wejściowym, które mają być pominiete przedz rozpoczęciem ładowania danych. Domyślna wartość wynosi 0.

LOAD – liczba logicznych rekordów do załadowania. Domyślnie ładowane są wszystkie rekordy.

ERRORS – dopuszczalna ilość błędów nie przerywających procesu ładowania. Domyślmnie wartość tego argumentu wynosi 50.

ROWS – liczba rekordów ładowanych jednorazowo. Argument ten służy do podzielenia transakcji na kilka części podczas ładowania. Domyślna wartość dla konwencjonalnych ścieżek łądowania wynosi 64.

SILENT – wyłącza wyświetlanie komunikatów podczas ładowania.

Wartości jakie można podać:

HEADER - wyłączenie wyświetlania nagłówka programu SQL*Loader

FEEDBACK -wyłączenie wyświetlania komunikatów w punktach zatwierdzania

ERRORS – wyłączenie rejestrowania (w pliku dziennika) wszystkich rekordów, które spowodowały błąd (ich liczba jest dalej rejestrowana)

DISCARDS – wyłączenie rejestrowania (w pliku dziennika) wszytskich rekordów, które zostały odrzucone (ich liczba jest dalej rejestrowana)

ALL – wyłączenie wyświetlaniawszystkich wymienionych wcześniej informacji

DIRECT – zastosowanie ładowania Direct Path. Wartośc domyślna wynosi false.

PARFILE – nazwa pliku parametrów zawierającego specyfikacje dodatkowych parametrów ładowania.

PARALLEL - zastosowanie ładowania równoległego. Wartośc domyślna wynosi false.


Przykładowe zastosowanie przełączników:


Przełącznik SILENT – wyłączymy np. informacje o punktach zatwierdzania.


SQLLDR USERID=HR/hr CONTROL='D:\USTAWIENIA.TXT' LOG='D:\USTAWIENIA.LOG' SILENT=FEEDBACK




Przełącznik ROWS – wcześniej ładowanie byo zatwierdzane co 64 wiersze, ponieważ jest to wartość domyslna. Jak najbardziej możemy tę wartość ustawić na taką która będzie nam odpowiedała. Tutaj ustawienie tej wartości na 50.


SQLLDR USERID=HR/hr CONTROL='D:\USTAWIENIA.TXT' LOG='D:\USTAWIENIA.LOG' ROWS=50




Przełącznik SKIP – ile rekordów chcemy pominąć zanim zaczniemy ładować dane. Ustawienie na 1900.


SQLLDR USERID=HR/hr CONTROL='D:\USTAWIENIA.TXT' LOG='D:\USTAWIENIA.LOG' ROWS=50 SKIP=1900






Przełącznik LOAD – określany ile rekordów z naszego pliku tekstowego chcemy załadować. Ustawienie parametru na 500.


SQLLDR USERID=HR/hr CONTROL='D:\USTAWIENIA.TXT' LOG='D:\USTAWIENIA.LOG' ROWS=50 LOAD=500




Przełącznik DIRECT - przykłady które zostały pokazany wcześniej ładował wiersze za pomocą metody Conventional Path Load, jest ona podobna do zwykłego inserta w jezyku SQL, przy której generowane są dane REDO oraz UNDO.

Natomiast można skorzystać jeszcze z metody Direct Path Load, używając przełącznika DIRECT i ustawiając jego wartość na true. W tej metodzie dane są dopisywane blokowo bezpośrednio do tabel. Jest to dużo szybsza metoda. Nie są generowane dane REDO & UNDO.


Conventional Path Load

Direct Path Load

domyślna

tworzy bloki danych, które już są w formacie

bloków danych Oracle

używa SQL-owej komendy INSERT i bufora

budowanej tablicy wiązania (bind array) do

załadowania danych; w macierzy wiązania są

umieszczane wiersze czytane za jednym razem

nie używa SQL-owej komendy INSERT do

wstawiania bloków do bazy

może spowalniać ładowanie

jest szybsza od konwencjonalnej

konkuruje z innymi zasobami Oracle’a o zasoby

bazy

generowane bloki są bezpośrednio zapisywane

do bazy danych

generowane są dodatkowe nagłówki jako

komendy SQL-owe, następnie wysyłane do

Oracle’a i przetwarzane

używa procedur zarządzania przestrzenią do

uzyskania następnego obszaru (wskaźnik HWM)

czeka na wypełnienie bufora danymi lub na

koniec danych

wymusza zastosowanie niektórych więzów

integralności na wierszach tabeli (UNIQUE,

PRIMARY, NULL)

używane są tylko puste bloki

nie są wykonywane zapisy w plikach dziennika



SQLLDR USERID=HR/hr CONTROL='D:\USTAWIENIA.TXT' LOG='D:\USTAWIENIA.LOG' DIRECT=TRUE




I na koniec ciekawostka. Czy zastanawialiście się nad możliwością załadowania danych SQL*Loaderem do tabeli tymczasowej? Sprawdzamy.


Na początek utworzenie tabeli tymczasowej, na wzór tabeli do której wczesniej wciągane były dane.




Modyfikacja pliku kontrolnego poprzez wskazanie tabeli tymczasowej jako tabeli docelowej dla naszych danych.




I próba odpalenia SQL*Loadera.




Próba nieudana z bardzo prostej przyczyny,. Dane w tabeli tymczasowej są widoczne tylko w ramach tej sesji, która załadowała te dane. SQL*Loader nawiązuje sesję podczas ładowania danych po czym ją zamyka, w związku z czym, nawet gdyby ta próba się powiodła to i tak nikt by tych danych nie mógł odczytać.