Inspiracja , wiedza , realizacja
Jsystems

W przebudowie

Login



Java

Oracle

Linux

Android

PostgreSQL

Microsoft SQL Server

Tabele zewnętrzne

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

Dzięki tabelom zewnętrznym użytkownik może korzystać z zewnętrznych plików tak, japkby były one tabelami bazy danych. Strukturę i lokalizację tabeli zewnętrznej definiuje się w systemie Oracle. Podczas wykonania zapytania na tabeli zewnętrznej, wynik odczytywany jest w taki sposób jakby dany były przechowywane w bazie danych. Z uwagi na to, iż są one jednak zapisane poza bazą, nie trzeba się martwić procesem ich ładowania, co stanowi istotną korzyść dla jurtowni danych i dużych baz.


Mają one jednak istotne ograniczenia, nie można w nich modyfikować ani usuwać danych z poziomu systemu Oracle oraz nie można ich indeksować. Z uwagi na to, iż mogą one stanowić część bazy danych, nalezy pamietać aby uwzględniać je przy tworzeniu kopii zapasowych bazy danych jak i jej odtwarzaniu.


Aby móc z nich skorzystać należy zamapować katalog gdzie będą przechowywane te pliki, poprzez polecenie CREATE DIRECTORY (pamietamy oczywiście o tym, że katalog tworzymy sami fizycznie w systemie operacyjnym) a nastepnie nadać uzytkownikowi z którego będziemy chcieli korzystać możliwość zapisu i odczytu danych z tego katalogu.




W Oracle mamy dostępne dwa rodzaje tabel zewnętrznych.

Pierwszy opiera się o pliki płaskie (txt,csv itd), na podstawie których możemy utworzyć tabelę, zaczytać dane z takiego pliku i wykonywać po nim selecty, mając na uwadze to, że jeśli zawartość takiego pliku ulegnie zmianie, to my poprzez bazę tę zmianę też zobaczymy.

Drugi natomiast bazuje na plikach data pump, które możemy utworzyć na podstawie zapytania i praktycznie od razu wczytać je do innej bazy.


Oracle loader


Do utworzonego katalogu wrzucam wcześniej przygotowany plik z danymi, a następnie wydaję polecenie które w oparciu o ten plik utworzy mi tabelę i wyświetli mi w niej odpowiednie dane.


CREATE TABLE pracownicy_ext

(

id int,

imie varchar2(100),

nazwisko varchar2(100),

telefon varchar2(100),

mail varchar2(100),

adres varchar2(100),

miasto varchar2(100),

kraj varchar2(100),

kod varchar2(100),

pensja number(10,2),

data_zatrudnienia varchar2(100),

numer int

)

ORGANIZATION EXTERNAL

(

TYPE oracle_loader

DEFAULT DIRECTORY pliki

ACCESS PARAMETERS

(

FIELDS TERMINATED BY ';'

MISSING FIELD VALUES ARE NULL

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

)

LOCATION ('pracownicy_ext.dsv')

)

REJECT LIMIT UNLIMITED;




ORGANIZATION EXTERNAL – wskazujemy że dane do tej tabelki będą pochodziły z "zewnątrz"

TYPE – określamy typ tych danych

DEFAULT DIRECTORY – katalog w którym znajduje się nasz plik

FIELDS TERMINATED BY – informujemy jakiego separatora dla kolumn uzyto w pliku

MISSING FIELD VALUES ARE NULL – jeśli gdzieś pojawią się puste pola oznacza to, że mają to być wartości null

LOCATION – nazwa pliku z danymi wraz z rozszerzeniem

REJECT LIMIT – określamy ilość błędnych wierszy


Aby podejrzeć informacje na temat tabel zewnętrznych, włącznie z ich definicjami, należy wykonać zapytanie do widoku USER_EXTERNAL_TABLES




Jednakże w tym widoku nie znajdziemy informacji o pliku zewnętzrnym do którego ta tabela się odwołuje. Znajdują się one z kolei w widoku USER_EXTERNAL_LOCATIONS.




Podczas tworzenia tabeli zewnętrznej system Oracle tylko pobieżnie sprawdza poprawność definicji. Większość błędów nie widać do momentu wprowadzenia pierwszego zapytania. Składnia parametrów dostępowych jest na tyle specyficzna, iż nawet niewielka pomyłka, włącznie z kolejnością klauzul, może uniwmożliwić dostęp do wszystkich wierszy.


Sprawdzenie:


select * from pracownicy_ext;



Przy tworzeniu tabel zewnętrznym, mamy możliwość skorzystania z kilku opcji, które występują w programie SQL*Loader.

Możemy wprowadzić opcje pominięcia rekordów (SKIP) i ograniczyć zwracane rekordy (WHEN). Nalezy wtedy jednak dodatkowo zaznaczyć jak ograniczone są rekordy poprzez wpis - RECORDS DELIMITED BY NEWLINE, w przeciwnym razie przy próbie wykonania pierwszego selecta na tej tabeli otrzymamy błąd i nie zostaną zwrócone żadne wiersze.




Mamy również możliwość utworzenia pliku dziennika, gdzie znajdziemy szczegóły operacji ładowania danych, pliku "złych" danych, gdzie znajdą się wiersze niespełniające warunków klauzuli access parameters, oraz pliku odrzuconych rekordów, gdzie znajdą się wiersze, których ładowanie nie powiedzie się.




Tabele zewnętrzne mogą być zasilane z kilku plików naraz, wystarczy, że w klauzuli location wymienimy nazwy plików, z których chcemy załadować dane do tabeli. Należy jednak pamietać o tym, iż kolejność wymienienia tych plików ma znaczenie, chociażby na parametr SKIP, który będzie dotyczył tylko pierwszego pliku.




Oracle data pump


Przy użyciu polecenia CREATE TABLE AS istnieje mozliwość utworzenia tabeli zewnętrznej, która od razu będzie zasilona wierszami. Podczas tworzenia takiej tabeli, wykonywane jest podane zapytanie, które tworzy plik zewnętrzny z wynikami z tego zapytania, sformatowany w sposób zgodny z tymi wynikami. Jednakże konieczne jest wtedy użycie sterownika ORACLE_DATAPUMP zamiast pokazywanego do tej pory w przykładach ORACLE_LOADER.


create table pracownicy_ext2

ORGANIZATION EXTERNAL

(

TYPE oracle_datapump

DEFAULT DIRECTORY pliki

LOCATION ('pracownicy_ext2.dmp')

)

as select * from pracownicy_ext;



Jeszcze tylko szybkie sprawdzenie czy tabela funkcjonuje:


select * from pracownicy_ext2;



Przy okazji jej utworzenia, stworzony został również plik 'pracownicy_ext2.dmp', który można zamapować tabelą zewnętrzną na innym serwerze.


Teraz sprawdzimy, czy nie będzie problemu z utworzeniem kolejnej tabeli zewnętrznej, która wczyta dane z tego pliku (należy pamiętać, że nazwy kolumn muszą być identyczne jak te w pliku).


CREATE TABLE pracownicy_ext3

(

id int,

imie varchar2(100),

nazwisko varchar2(100),

telefon varchar2(100),

mail varchar2(100),

adres varchar2(100),

misto varchar2(100),

kraj varchar2(100),

kod varchar2(100),

pensja number(10,2),

data_zatrudnienia varchar2(100),

numer int

)

ORGANIZATION EXTERNAL (

TYPE oracle_datapump

DEFAULT DIRECTORY pliki

LOCATION ('pracownicy_EXT2.dmp')

);



Sprawdzenie:


select * from pracownicy_ext3;




W trakcie tworzenia tabeli zewnetrznej na podstawie zapytania, istnieje możliwość skompresowania danych, które będa zapisane w pliku zewnętrznym. Domyślnie opcja ta jest wyłączona.


create table pracownicy_komp

ORGANIZATION EXTERNAL

(

TYPE oracle_datapump

DEFAULT DIRECTORY pliki

ACCESS PARAMETERS (compression enabled)

LOCATION ('pracownicy_komp.dmp')

)

as select * from pracownicy_ext;




Porównanie z poprzednim plikiem zewnętrznym, który tworzony był bez kompresji.




Modyfikowanie tabel zewnętrznych


Użytkownik może modyfikować definicje tabel zewnętrznych, w celu zmiany sposobu interpretacji pliku zewnętrznego przez system Oracle.


Zmiana parametrów dostępu


Parametry, określone w klauzuli access parameters można modyfikować bez potrzeby usuwania i ponownego tworzenia tabeli zewnętrznej.

Dla przykładu została dodana opcja pominięcia pierwszych 10 rekordów.


alter table pracownicy_ext

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE

SKIP 10

FIELDS TERMINATED BY ';'

MISSING FIELD VALUES ARE NULL

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

);




Dodawanie kolumny


Składnia polecenia jest identyczna jak w przypadku tabel standardowych.


alter table pracownicy_ext add nowa_kolumna varchar2(50);




Modyfikacja kolumny


Składnia polecenia jest identyczna jak w przypadku tabel standardowych.


alter table pracownicy_ext modify nowa_kolumna varchar2(50);




Usuwanie kolumny


Składnia polecenia jest identyczna jak w przypadku tabel standardowych.


alter table pracownicy_ext drop column nowa_kolumna;




Zmiana katalogu domyślnego


Służy do zmiany katalogu domyślnego zewnętrznych plików wykorzystywanych przez tabelę. Katalog musi być utworzony za pomocą polecenia create directory.


alter table pracownicy_ext default directory dane;




Zmiana plików zewnętrznych


Służy do zamiany plików zewnetrznych, z których tabela odczytuje dane. Możemy dodać kolejne pliki, wymienić na inne, albo przynajmniej zamienić ich kolejność.


alter table pracownicy_ext location ('pracownicy_ext.dsv');