Zarejestruj się na BitBay.net
Home > Linux, Oracle > Pompowanie danych do Oracla, SQL *Loader, dbf2sql, dbf2cvs, dbf2ascii

Pompowanie danych do Oracla, SQL *Loader, dbf2sql, dbf2cvs, dbf2ascii

oracle Podczas migracji różnego rodzaju danych do Oracla, szczególnie ze starych systemów opartych o pliki DBF, stajemy przed pytaniem: Jak najszybciej przepompować duże ilości danych do nowej bazy?

Jednym ze sposobów jest napisanie aplikacji która odczyta dane z pliku i za pomocą tradycyjnych instrukcji INSERT dopisze do odpowiednich tabel w Oraclu. Gdy tych danych nie ma dużo jest to jakieś wyjście, ale co zrobić gdy firma pracuje 24/h na dobę, wolne ma tylko w święta, a my właśnie wtedy musimy to zrobić ? Przede wszystkim jeśli już dopisujemy dane za pomocą insertów nie róbmy commita po każdym insercie. To najbezpieczniejsze, ale strasznie opóźnia ponieważ po każdym insercie mechanizm bazodanowy musi (nie wgłębiając się w szczegóły) wykonać szereg czynności związanych z zatwierdzeniem (commit) transakcji i zapisaniem zatwierdzonych danych (tak robi np MySQL i przez to jest wolniejszy). Commita w tym przypadku najlepiej robić co np. 10000 rekordów.

Inną metoda pompowania danych do Oracla są instrukcje INSERT z hintem /*+ APPEND */. Instancja Oracla w dużej mierze sama dba o to, żeby dane były właściwie poukładane. Mimo tego fragmentacja tabel i tak następuje. Najczęściej pojawia się, właśnie przy insertach z opcją /*+ APPEND */ (tzw. direct load). APPEND oznaczą, ze Oracle przy insertach korzysta wyłącznie z pustych bloków, co jest dużo szybsze. Nie mniej jednak bloki te zdarzają się zazwyczaj na samym końcu za tak zwanym HWM (High Water Mark), dlatego tez następuje duża fragmentacja danych (wolne obszary wcześniej nie są wykorzystywane). Jeśli do bazy inserty idą zawsze w trybie append, oraz pojawiają się częste delete-y to wielkość tabeli będzie wzrastać, pomimo tego, że ilość danych będzie na podobnym poziomie. Aby odzyskać wolne miejsce możemy wykonać „alter table shrink space” lub reorganizację danych i odbudowę indeksów.

Kolejnym sposobem jest SQL*Loader – narzędzie do szybkiego pompowania danych z plików tekstowych txt,cvs itp. do bazy danych Oracle. Przejrzałem dużo narzędzi i nie znalazłem niczego co by działało szybciej niż SQL*Loader. SQL*Loader dostarcza dwie metody pompowania danych:

  1. Conventional Path Load – Metoda podobna do „SQL insert”, przy której generowane są dane REDO oraz UNDO
  2. Direct Path Load – Metoda nie używająca SQL’a. Dane są dopisywane blokowo bezpośrednio do tabel. Jest to dużo szybsza metoda. Nie są generowane dane REDO & UNDO. W przypadku tej metody dostępne są trzy sposoby pompowania danych:
    • Simple Direct Path Load – No Parallel
    • Parallel Direct Path Load with multiple data files
    • Parallel Direct Path Load with single data file

Podczas pracy, SQL*Loader produkuje trzy rodzaje plików:

  • śladowy (ang. log file), w którym zapisywane są informacje o przebiegu ładowania,
  • ze złymi wierszami (ang. bad file), w którym są umieszczane wiersze nie załadowane ze względu na niepoprawność danych,
  • z wierszami pominiętymi (ang. discard file), które nie mogły być wstawione do bazy, ponieważ nie zostały spełnione żadne kryteria wstawienia.

Wyświetlanie pomocy: sqlldr help=y
Składnia: sqlldr user/password  control=plik.ctl log=plik.log bad=plik.bad opcje
Gdzie:

control – plik w którym zdefiniowana jest co ma być czytane, skąd do kąd i w jakis sposób.
log – plik w którym zapiszą się logi z importu
bad – plik w którym zapiszą się błady

Podstawowe opcje:

USERID={username[/password][@net_service_name]|/} – wyspecyfikowanie nazwy użytkownika/hasło
CONTROL – wyspecyfikowanie nazwy pliku sterującego
LOG – wyspecyfikowanie nazwy pliku śladowego
BAD – wyspecyfikowanie nazwy pliku ze złymi wierszami
DISCARD – wyspecyfikowanie nazwy pliku z wierszami pominiętymi
DATA – wyspecyfikowanie nazwy pliku z danymi do załadowania
DISCARDMAX – maksymalna ilość odrzuconych wierszy, nie powodująca przerwania ładowania danych
SKIP Błąd: Nie znaleziono źródła odwołania – ilość wierszy do pominięcia
SKIP_INDEX_MAINTENANCE={TRUE | FALSE}
SKIP_UNUSABLE_INDEXES={TRUE | FALSE}
LOAD – ilość rekordów (logicznych), które zostaną załadowane zewnętrznymi danymi
ERRORSr – dopuszczalna ilość błędów nie przerywających procesu ładowania
ROWS Błąd: Nie znaleziono źródła odwołania – określenie momentu wystąpienia punktu zachowania
BINDSIZE – rozmiar tablicy wiązania
SILENT – zablokowanie kierowania komunikatów na ekran podczas ładowania
DIRECT={TRUE | FALSE} – bezpośrednia ścieżka ładowania
PARFILE – wyspecyfikowanie nazwy pliku z parametrami
PARALLEL={TRUE | FALSE}

Przykład:
Zawartośc pliku control:

LOAD DATA
infile real_sp.dat
REPLACE
INTO TABLE REAL_SP
FIELDS TERMINATED BY x'09' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
        TOW_KOD CHAR,
        ROK CHAR,
        MIES POSITION(14:15) CHAR,
        OPER_TYP CHAR,
        FIR_KOD CHAR,
        ILO_TOW DECIMAL EXTERNAL,
        ILO_ZAK DECIMAL EXTERNAL,
        WAR_SP_PL CHAR "TO_NUMBER(:WAR_SP_PL,'9999999999.99')",
        WAR_ZAK_PL CHAR "TO_NUMBER(:WAR_ZAK_PL,'9999999999.99')",
        IE$0 CHAR "(:TOW_KOD||:ROK||:MIES||:OPER_TYP||:FIR_KOD)",
        IE$1 CHAR "(:ROK||:MIES||:FIR_KOD)",
        IS_DELETED CHAR "('N')"
)

gdzie:
INFILE – wskazanie, w jakim pliku są dane do ładowania (jeżeli są w pliku sterującym po frazie BEGIN DATA, wówczas po słowie INFILE umieszczany jest znak *)
INTO TABLE – wskazanie, które tabele zostaną załadowane danymi
REPLACE – usuwa istniejące wiersze tabeli i ładuje na ich miejsce nowe
APPEND – rozbudowuje tabelę, dodając do niej nowe wiersze
INSERT – ładuje dane tylko do pustej tabeli (jeżeli tabela nie będzie pusta, to wystąpi błąd)
LOAD DATA – informacja o niezależności między danymi a kolumnami w tabeli (tabelach)
CONCATENATE – ilość fizycznych rekordów składanych w jeden rekord logiczny, który zostanie wstawiony do bazy
ROWS – określenie momentu wystąpienia punktu zachowania
CONTINUE LOAD – liczba rekordów do przeskoczenia
FIELDS TERMINATED BY – znak oddzielający dane mające wejść w skład poszczególnych kolumn
ENCLOSED BY – znak (-i), będący ogranicznikiem pól tekstowych
BAD FILE – wyspecyfikowanie nazwy zbioru, w którym znajdą się złe dane
TRAILING NULLCOLS – ciąg spacji nie jest ładowany do tabeli jako odrębne kolumny z wartościami NULL
BEGIN DATA – słowo kluczowe, będące nagłówkiem dla danych przeznaczonych do ładowania, umieszczonych bezpośrednio w pliku sterującym

Przykładowe polecenie:

sqlldr userid=TEST/TEST control=TEST.CTL log=REAL_SP.log bad=REAL_SP.bad direct=true

jeśli chcemy pompować dane do innego hosta:

sqlplus TEST1/TEST1@172.21.0.13:1521/hart @mag_prac.SQL
sqlldr userid=TEST1/TEST1@172.21.0.13:1521/hart control=mag_prac1.CTL log=mag_prac.log bad=mag_prac.bad

Po wpompowaniu danych za pomocą SQL *Loadera także wskazana jest defragmentacja, czyli reorganizacja danych i odbudowa indeksów.

polskie znaczki w konsoli linux:

LANG=pl_PL
LC_ALL=pl_PL.UTF-8
export LC_ALL LANG
export NLS_LANG=NLS_LANG=American_America.UTF8

konwertowanie plików na inne standardy kodowania pod linuxem:

iconv -f IBM852 -t UTF-8 -o test.txt mag_prac.txt

W sieci:

Darmowe narzędzia DBF2SQL:

W przypadku pompowania danych z DBF’ów do Oracla możemy użyć gotowych do tego programów, lub przekonwertować dane na postać TXT i wpompować za pomocą SQL *Loadera.

Windows:

Linux:

  1. Brak komentarzy
  1. Brak jeszcze trackbacków

*