Ładowanie zawartości pliku tekstowego do bazy danych Oracle

post_img

Bardzo często spotykamy się z problemem zaimportowania do bazy danych zawartości dużych plików tekstowych, np. plików log. Chciałbym przedstawić kilka sposobów tej kwestii i porównać te rozwiązania pod kątem wydajności. Opiszę rozwiązania oparte na: pakiecie PL/SQL UTL_FILE, EXTERNAL TABLE oraz wykorzystujące narzędzia zewnętrzne.

Przygotowanie danych testowych

Aby przetestować proponowane rozwiązania, wygenerujemy plik tekstowy zawierający 1000000 wierszy. Każdy z nich ma dwa pola – numer i datę – oddzielone od siebie znakiem „#”. Na potrzeby testów utworzymy tablicę, do której będziemy importować dane.

Proces przygotowania testów

  1. Tworzymy w bazie danych obiekt Directory, który wskazuje katalog systemu operacyjnego na serwerze bazy danych do odczytu i zapisu plików.
    Do wykonania polecenia potrzebujemy uprawnień SYS.

    1
    2
    CREATE OR REPLACE DIRECTORY temp_dir AS '/home/mateuszla';
    GRANT READ, WRITE ON DIRECTORY temp_dir to USER_NAME;
  2. Wykonujemy skrypt generujący plik tekstowy.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    declare
      f utl_file.file_type;
      licznik number:=0;
    begin
      utl_file.fremove('TEMP_DIR', 'something.txt');
      f := utl_file.fopen('TEMP_DIR', 'something.txt', 'w');
      for i in 1..1000000 loop
        utl_file.put_line(f,licznik||'#'||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));
        licznik:=licznik+1;
      end loop;
      utl_file.fclose(f);
    end;
  3. Tworzymy tablicę w bazie danych, do której będziemy zapisywać zawartość pliku.

    1
    2
    3
    4
    5
    create table test_plik
    (
      numer number,
      data date
    );

Import zawartości pliku tekstowego do bazy danych ORACLE

Ładowanie zawartości pliku utworzonego w punkcie I do bazy danych wykonamy kilkoma sposobami:

  1. Ładowanie zawartości pliku do tablicy z wykorzystaniem pakietu PL/SQL UTL_FILE oraz pojedynczych insertów do bazy danych.
    Procedura ładowania:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    DECLARE
      f UTL_FILE.FILE_TYPE;
      s VARCHAR2(2000);
      c number := 0;
    BEGIN
      f := utl_file.fopen('TEMP_DIR', 'something.txt', 'r');
      execute immediate 'delete from test_plik';
      LOOP
        UTL_FILE.GET_LINE(f, s);
        insert into test_plik
          (numer, data)
        values
          (to_number(substr(s, 1, instr(s, '#', 1) - 1)),
           to_date(substr(s, instr(s, '#', 1) + 1, length(s)),
                   'YYYY-MM-DD HH24:MI:SS'));
        c := c + 1;
      END LOOP;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        UTL_FILE.FCLOSE(f);
    END;

    Wnioski:
    Powyższe rozwiązanie nie jest uniwersalne i dostosowane do jednolitej struktury pliku tekstowego. Programista musi przeanalizować strukturę pliku oraz przygotować odpowiednią funkcję parsującą. Taki sposób ładowania danych do bazy jest bardzo wolny i pracochłonny. Plik tekstowy, który chcemy załadować, musi znajdować się na serwerze. Załadowanie 1000000 rekordów do bazy danych zajęło w przybliżeniu 120 sekund.

  2. Ładowanie zawartości pliku do tablicy z wykorzystaniem pakietu PL/SQL UTL_FILE oraz kolekcji danych.
    Procedura ładowania:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    DECLARE
      f UTL_FILE.FILE_TYPE;
      s VARCHAR2(2000);
      c number:=0;
      zapytanie clob;
      TYPE typ_tablica_number is table of number index by binary_integer;
      TYPE typ_tablica_data is table of date index by binary_integer;
      tablica_number typ_tablica_number;
      tablica_data typ_tablica_data;
    BEGIN
      f := utl_file.fopen('TEMP_DIR', 'something.txt', 'r');
      execute immediate 'delete from test_plik';
      LOOP
        BEGIN  
          UTL_FILE.GET_LINE(f,s);
          c:=c+1;
          tablica_number(c) := to_number(substr(s,1,instr(s,'#',1)-1));
          tablica_data(c) := to_date(substr(s,instr(s,'#',1)+1,length(s)),'YYYY-MM-DD HH24:MI:SS');
        EXCEPTION
          WHEN NO_DATA_FOUND THEN
            UTL_FILE.FCLOSE(f);
            exit;
        END;
      END LOOP;  
      FORALL i IN tablica_number.FIRST .. tablica_number.LAST
      INSERT INTO TEST_PLIK(numer,data) values (tablica_number(i),tablica_data(i));
    END;

    Wnioski:
    Rozwiązanie to jest również bardzo pracochłonne i nie uniwersalne, ale stanowczo szybsze od poprzedniego. Import 1000000 rekordów zajął w przybliżeniu 63 sekundy. Różnica tempa wynika ze zmiany sposobu zapisu danych do bazy, a mianowicie z zastosowania kolekcji danych oraz funkcji FORALL. To wystarczyło, by czas wykonania operacji skrócił się o połowę. W tym rozwiązaniu plik tekstowy również musi znajdować się na serwerze.

  3. Ładowanie danych z pliku z wykorzystaniem tablic zewnętrznych.
    Tablice zewnętrzne pozwalają na odczyt danych przechowywanych poza bazą danych w plikach. Sterownik ORACLE_LOADER może być używany do załadowania danych w dowolnym formacie. Na tablicach zewnętrznych nie możemy wykonywać operacji DML, ale możemy wykonać „query”, „join” oraz „sort”. Nie zaleca się używania tablic zewnętrznych jako tablic częstego użytku.
    Procedura ładowania danych polega na stworzeniu odpowiednio zdefiniowanej tablicy zewnętrznej.

    Utworzenie tablicy zewnętrznej dostosowanej do formatu naszego pliku tekstowego:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    CREATE TABLE test_plik2(
      numer      number,
      data_do       date
    )
    ORGANIZATION EXTERNAL (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY temp_dir
      ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY '#'
        MISSING FIELD VALUES ARE NULL
        (
          numer    char(20),
          data_do CHAR(21) DATE_FORMAT DATE MASK 'YYYY-MM-DD HH24:MI:SS'
        ))
      LOCATION ('something.txt')
    )
    PARALLEL 5
    REJECT LIMIT UNLIMITED;

    Wykonanie skryptu tworzącego tablicę zewnętrzną trwało 0.18 sekundy, a następnie zapytanie o liczbę jej wierszy 0.67 sekundy. Rozwiązanie to zapewnia minimalny czas ładowania danych z pliku do tablicy oraz jest bardzo uniwersalne. Plik musi znajdować się na serwerze.

    Po stworzeniu tablicy zewnętrznej zawierającej dane pliku tekstowego możemy skopiować jej zawartość do naszej tablicy, a tablicę zewnętrzna usunąć.

    1
    insert into test_plik(data,numer) select tp2.data_do,tp2.numer from test_plik2 tp2;

    1000000 rows inserted in 4,446 seconds.

  4. Ładowanie zawartości pliku do bazy z wykorzystaniem narzędzi zewnętrznych.

    Narzędzia zewnętrzne, jak np. PL/SQL Developer, posiadają funkcje pozwalające na ładowanie danych z pliku tekstowego do bazy. Plik musi znajdować się po stronie klienta. PL/SQL Developer umożliwia import pliku poprzez wykorzystanie modułu „TEXT IMPORTER”.

    Zalety:

    1. prosty sposób konfiguracji formatu ładowanych danych;
    2. intuicyjny interfejs użytkownika;
    3. możliwość importu pliku znajdującego się po stronie klienta;
    4. bezpośrednie ładowanie danych do bazy lub wykorzystanie skryptów;
    5. ciągły podgląd formatu załadowanych danych.

    Wady:

    1. narzędzie płatne;
    2. dane zapisywane do bazy poprzez pojedyncze operacje insert, co wydłuża czas zapisu;
    3. ograniczone możliwości formatowania.

Podsumowanie

Najlepszym sposobem na załadowanie danych z dużych plików tekstowych do bazy danych jest wykorzystanie tablic zewnętrznych (EXTERNAL TABLES). Rozwiązanie to jest najszybsze i daje szerokie możliwości dostosowania formatu danych.

Porównanie czasów operacji ładowania danych (w sekundach)

UTL_FILE, pojedyncze inserty UTL_FILE, FORALL External Tables
120 s. 63 s. 0.18 s.

Źródła

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *