Externé tabuľky v Oracle

Externé tabuľky patria medzi dôležitý prostredník medzi externými prostrediami a databázou. V ORACLE sa bez nich nedajú predstaviť dátové pumpy,ani práca s ETL a DWH. Vo všeobecnosti sa jedná o tabuľku prístupnú iba na čítanie, ktorá je definovaná v databáze, ale existuje mimo databázy, čo znamená,že metadáta sú uložené v databáze a údaje, ktoré obsahuje, sú mimo databázy. Tabuľka, ktorá je napojená na externú tabuľku, vyzerá ako normálna tabuľka, ale nemá jej všetky funkcionality.Môže sa querovať príkazom SELECT, ale nedá sa robiť žiadne DML (update,insert,delete). Výhody:

1. hlavnou výhodou je vytvoriť ľahko použiteľný most medzi tabuľkami SQL a zdrojmi údajov, ktoré nie sú databázové. (rovnako ako v minulosti tool Oracle SQL*Loader alebo Data Pump)
2. Príkladom využitia je, keď máte nejaký zdroj údajov, ktorý nie je SQL, ktorý pravidelne produkuje informácie, kt. sú potrebné dať do DB, ako napríklad textovy subor, webová stránka, excel, DMP subor (súbor pre prácu s Datapumpom) a pod. ktorý je štruktúrovaný tak, aby ho bolo možné skopírovať priamo do súboru, ktorý externá tabuľka SQL okamžite rozpozná a bude môcť querovať. (pozn. Len txt funguje, pre ostatné súbory s výnimkou DMP treba si nainštalovať drivery)

Vytváranie externých tabuliek

1. deklarujete stĺpce a dátové typy
2. definujete parametre, podľa ktorých databáza SQL komunikuje s externou tabuľkou (parametre obsahujú typ loadu, prvý riadok na čítanie dát alebo miesto adresára, kde sa dáta nachádzajú)
3. treba vedieť čo sú a kde sa nacháduajú adresárové objekty (directory objects)
4. treba poznať funkcionalitu nástrojov -driverov pre prácu s externými tabuľkami:oracle_loader,oracle_datapump

!Ďalšie obmedzenia: Nedajú sa vytvoriť žiadne CONSTRAINTY a Stĺpec externej tabuľky nie je možné zmeniť na SET UNUSED!

DIRECTORY OBJECTS

Ak chcete vytvoriť externú tabuľku, budeme musieť identifikovať umiestnenie v operačnom systéme (v priečinku Oracle klienta $ORACLE_HOME-database), v ktorom sa bude nachádzať externý súbor obsahujúci tabuľku.

CREATE OR REPLACE DIRECTORY 
directory_name
AS
directory_reference;

Po vytvorení adresára musí vlastník udeliť READ alebo WRITE prístup pre USERA

Nástroje pre prácu s externými tabuľkami:

Databáza Oracle poskytuje množstvo nástrojov, ktoré su súčasťou rôznych balíčkov. Nástroje, ktoré sú dôležité pre externé tabuľky sú:

1. Oracle_Loader
2. Oracle_DataPump

Základný rozdiel medzi loaderom a Datapumpom je, že Loader len loaduje data a Datapump aj exportuje so špecifickou koncovkou DMP.

Postup pre vytvorenie

1. Vytvoríme adresár, ktorý bude obsahovať súbor
2. Pridelíme read/write access pre usera
3. Zvolíme externý súbor (či bude txt,excel, web)
4. Pripravíme si súbor/ očistíme/naformátujeme dáta
5. Uložíme súbor v adresári (viď bod 1)
6. Vyskladáme create table script

Create statement vyzerá nasledovne:

CREATE TABLE INVOICES_EXTERNAL
(   INV_ID       CHAR(3), 
   INVOICE_DATE  CHAR(11),
   PRODUCT_ID    CHAR(5)
) ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY t_orcl_ext
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  SKIP 2 FIELDS(
   INV_ID      CHAR(3),
   INVOICE_DATE  CHAR(11),
   PRODUCT_ID    CHAR(5)
  )
 )
 LOCATION ('inv.txt')
);
/* opis r.2-4 deklarácia stĺpcov
r.1-5 klasický create script + keyword “ORGANIZATION EXTERNAL”
Od 6 riadku definícia ohľadom externého súboru
r.6 Typ access drivera na import dát (oracle_loader) (MUST!)
r.7 odkazovaný adresár
r. 8-16 vstupné parametre
r.10 ako sú riadky oddelené
r.11 ktoré riadky sa preskočia
r.11-15 deklarácia stĺpcov (musia mať rovnaký názov a typ premennej ako riadok 2-4
r.17 odkazovaný súbor (MUST!)*/

Zhrnutie

Na príkladoch sme si ukázali ako fungujú externé tabuľky v Oracle. Zistili sme aké je ich využitie a akú úlohu zohrávajú v ETL procese a DWH. Viac príkladov môžete nájsť tu