External Tables, un’introduzione

venerdì 24 agosto 2012 alle 24:30 | Pubblicato su 11g, Documentazione | 1 commento
Tag: , ,

Le External Tables sono una caratteristica introdotta per la prima volta con la versione 9i del database Oracle (quindi se non erro con la versione 9.0.1 ). Si tratta secondo me di uno strumento molto potente, fessibile e utile; esse sono molto simili a normali tabelle definite sul database, accessibili solo il modalità lettura (read-only) che  una volta create permettono di accedere a dati presenti su file esterni al database in modo trasparente. Fino alla versione 9i i file esterni potevano solo essere file di testo, poi con la versione 10g è stato aggiunto un nuovo access driver (chiamato ORACLE_DATAPUMP) che permette di accedere, sia in lettura che scrittura a file (con delle restrizioni) binari. Non ho mai usato questa soluzione, quindi non ne so molto. Il nome del driver mi aveva fatto pensare che il formato dei file binari fosse lo stesso dei dump creati con l’utility Oracle DataPump export, ma nella documentazione non ne parlano il che mi fa pensare che le due tipologie di file non siano interscambiabili.

Con la versione 11.2 è stata introdotta un’ulteriore funzionalità, il preprocessamento dei file esterni cioè la possibilità di aggiungere alla definizione della tabella esterna un clausola in cui si indica un programma esterno per preprocessare I file da leggere, l’esempio tipico è quello della decompressione di un file di testo compresso.

Le tabelle esterne forniscono funzionalità complementari (e in parte sovrapponibili)  a quelle fornite da SQL*Loader. Esse permettono di accedere a dati esterni come se fossero in tabelle del database con la limitazione che i dati sono accessibili in sola lettura. Il numero di righe che possono essere ritornate da una query dipende da che colonne vengono richieste perché le colonne di alcuni record possono dare errori di conversione e quindi causare il rigetto del record da parte del driver di accesso ai dati.

Il driver di accesso ai dati viene eseguito all’interno del database server a differenza di quanto accade per SQL*Loader che è un programma client a se stante. Questo ha  due implicazioni:

  1. il server deve avere accesso ai file da cui caricare i dati
  2. il server deve creare e scrivere il file creati dal file di accesso ai dati, ad esempio il file di log , il bad file e il discard file.

Non è possibile quindi indicare al server nomi di file qualunque, è necessario invece definire oggetti directory come locazioni da cui leggere e scrivere file. Ad esempio:

create directory load_src as ‘/usr/apps/datafiles’;

crea l’oggetto direcotory load_src.

Per creare un oggetto directory bisogna avere il ruolo di DBA oppure avere il privilegio “CREATE ANY DIRECTORY”. A questo punto chi crea l’oggetto directory deve anche concedere i privilegi READ e/o WRITE sulla directory ad eventuali altri utenti. Ad esempio:

grant read on directory load_src to scott;

L’utente SYS è l’unico utente che può possedere oggetti directory, ma l’utente SYS può concedere ad altri utenti il privilegio di creare oggetti directory.

Una volta definito un oggetto directory è possibile creare una tabella esterna ad esempio:

CREATE TABLE nome_tabella ( …)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER
DEFAULT DIRECTORY load_src
ACCESS PARAMETERS (
RECORD DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
(firs_name CHAR(7),
last_name CHAR(8),
year_of_birth CHAR(4))
)
LOCATION (‘file.dat’));

La parola chiave TYPE serve a indicare l’access driver per la tabella esterna. L’access driver è il modulo che si occupa per il database di interpretare i dati esterni, se viene omessa la specifica TYPE viene considerato come default il driver ORACLE_LOADER (quindi quello di accesso ai file di testo derivato direttamente da SQL*Loader).

Vengono poi speficiati gli ACCESS PARAMETERS che come indicato sulla documentazione sono opachi al database perché servono al driver. Quindi, mentre la prima lista di colonne definisce come viene vista lato database la tabella esterna (a tutti gli effetti analoga a una classica tabella HEAP), mentre la lista campi definita all’interno della sezione ACCESS PARAMETERS serve a indicare al driver come interpretare i dati sul file esterno e come mapparli sulla tabella oracle.

Per capire bene come definire una tabella esterna occorre quindi approfondire la definizione degli ACCESS PARAMETERS, qui mi occupo del driver ORACLE_LOADER, quindi la documentazione pertinente è questa : http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#g1031955

ACCESS_PARAMETERS per ORACLE_LOADER

commenti

Come prima parte e solo all’inizio è possibile inserire dei commenti, preceduti dal doppio trattino, poi seguono le altre parti.

record_format_info

Questa parte serve a indicare il formato dei record sul file di testo, per quanto mi riguarda il formato che uso sempre è quello delimitato da « accapo » quindi la clausola è :

RECORDS DELIMITED BY NEWLINE

come per sqlloader sono possibili anche altri formati di cui non parlo qui

poi sono definibili altri parametri come :

  • CHARACTERSET per indicare il charset del file
  • LANGUAGE/TERRITORY, qui la documentazione non è chiara e non ho ancora fatto delle prove, parrebero mutualmente esclusivi, comunque mi sembra possa tornare utile soprauttuo la TERRITORY per un caso neanche tanto particolare, quello dei numeri, infatti come indicato in seguito sulla documenazione per il parametro NLS_NUMERIC_CHARACTERS viene considerato il valore settato sul database server e non quello definito a livello di sessione.
  • BADFILE, file su cui vengono scritti record rigettati per errori
  • DISCARFILE file su cui vengo scritti record rigettati in base alla condizione speficata con la clausola LOAD WHEN
  • LOGFILE file di log
  • SKIP indica il numero di record da saltare dall’inizio del file. Su questa clausola occorre fare attenzione, perché a me in particolari casi non ha funzionato

field_definitions

Questa parte credo sia la più complessa e delicata. Se viene omessa vengono fatte le seguenti assunzioni da Oracle :

  • i campi sono delimitati dalla virgola « , »
  • i campi sono di tipo CHAR
  • la lunghezza massima del campo è 255
  • l’ordine dei campi nel file è nello stesso ordine in cui sono definiti i campi sulla tabella
  • gli spazi vuoti alla fine non vengono rimossi (no trim)

Prima della definizione dei singoli campi è possibile inserire alcune clausole :

  • delim_spec per questa riporto direttamente il grafo dalla documentazione :
  • trim_spec, può essere LRTRIM, NOTRIM, LTRIM, RTRIM, LDRTRIM
  • MISSING FIELD VALUES ARE NULL

Dopo le clausole precedenti si può mettere l’elenco dei campi, altrimenti valgono le seguenti regole :

  • se non viene specificato il tipo viene assounto CHAR(255) per campi delimitatati e CHAR(1) per campi non delimitati
  • se non vine specificata una lista campi allora oracle assume che i campi siano nello stesso ordine in cui sono definiti sulla tabella. I tipo dato viene assunto CHAR(255) se sulla tabella non è di tipo CHAR o VARCHAR2, altrimenti per la lunghezza viene preso il massimo fra 255 e la lunghezza definita per il campo sulla tabella.
  • Se non viene speficata ne’ la lista dei campi ne’ la clausola di specifica sulla delimitazione dei campi allora vale come sopra ma con tipo dato CHAR(255) per tutti e delimitatati da virgola.

Se invece viene specificato l’elenco dei campi il formato è sinteticamente (come riportato dalla documentazione ufficiale :

(field_name [pos_spec] [datatype_spec] [init_spec]) [,  (field_name [pos_spec] [datatype_spec] [init_spec])]…

Il « field_name » viene maiuscolizzato e confrontato  con in nomi dei campi nella tabella, se non ci sono corrispondenze il campo non viene caricato ma può essere usato in altre clausole di valutazione (ad esempio WHEN o NULLIF)

Per quanto riguarda il tipo, personalmente uso praticamente sempre il tipo CHAR, gli altri sono tipi dato in formato interno Oracle, quindi poco usati da me.

Nel caso di date è possibile usare la specifica « date_format », associata al tipo dato CHAR, ad esempio, sempre dal manuale :

employee_hire_date CHAR(10) date_format DATE mask « mm/dd/yyyy »

Qui è possibile vedere il grafo della specifica del date_format

Infine è possibile specificare quella che sul manuale chiama « init_spec Clause » che può essere :

  • DEFAULTIF condition_spec
  • NULLIF condition_spec

ad esempio : NULLIF campox=BLANKS per ulteriori dettagli  rimando sempre al manuale.

Dopo la definizione dei campi (field_definitions) è possibile aggiungere una ulteriore parte opzionale definta « column_trasforms » (http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#i1012621)

che permette di specificare come caricare colonne sulla tabella esterna che non mappano direttamente sui campi del file esterno ad esempio :

COLUMN TRANSFORMS (
campoy FROM CONSTANT 'NOT DEF',
campoz FROM NULL,
campog FROM CONCAT(campoa,CONSTANT 'stringa')
)

Un esempio :

drop table exttable1;
create table exttable1
   (
  pk_id	number(5),
  localita VARCHAR2(100),
  xcoord	number(10,8),
  ycoord    number(10,8),
  data_field	date,
  time_field date,
  test_number number(10),
  dummy_field varchar2(20)
)
   organization external (
   type oracle_loader
   default directory UNLOAD
   access parameters (
   records delimited by newline
   BADFILE 'exttable1.bad'
   LOGFILE 'exttable1.log'
   skip 0
   fields terminated by ";"  optionally enclosed by "'"
   MISSING FIELD VALUES ARE NULL
   (
	pk_id char(5),
	data_field char(10) date_format DATE mask 'dd/mm/yyyy',
	time_field char(8) date_format DATE mask 'hh24:mi:ss',
	localita char(100),
	ycoord,
	xcoord,
	test_number
   )
   COLUMN TRANSFORMS (
    dummy_field FROM CONSTANT 'NOT DEF'
   )
   )
   location
   (
   'exttable1.csv'
   )
);
select * from exttable1;

il contenuto di exttable1.csv è :

82;’25/12/2009′;’15:25:31′;’Udine’;13,230234;46,068812;’99’

80;’23/12/2009′;’15:19:19′;’Udine’;13,230134;46,119412;’99’

81;’24/12/2009′;’15:20:16′;’Udine’;13,240134;46,068812;’99’

82;’25/12/2009′;’15:21:12′;’Udine’;13,230234;46,068812;’99’

83;’26/12/2009′;’15:22:52′;’Udine’;13,231134;46,059412;’99’

84;’27/12/2009′;’15:24:22′;’Udine’;13,222134;46,068412;’99’

85;’28/12/2009′;’15:25:31′;’Udine’;13,211134;46,079412;’99’

L’output è :


PK_ID LOCALITA XCOORD YCOORD DATA_FIELD TIME_FIELD TEST_NUMBER DUMMY_FIELD
---------- -------------------- ---------- ---------- ------------------- ------------------- ----------- ------------
 82 Udine 46,068812 13,230234 25-12-2009 00:00:00 01-08-2012 15:25:31 99 NOT DEF
 NOT DEF
 80 Udine 46,119412 13,230134 23-12-2009 00:00:00 01-08-2012 15:19:19 99 NOT DEF
 81 Udine 46,068812 13,240134 24-12-2009 00:00:00 01-08-2012 15:20:16 99 NOT DEF
 82 Udine 46,068812 13,230234 25-12-2009 00:00:00 01-08-2012 15:21:12 99 NOT DEF
 83 Udine 46,059412 13,231134 26-12-2009 00:00:00 01-08-2012 15:22:52 99 NOT DEF
 84 Udine 46,068412 13,222134 27-12-2009 00:00:00 01-08-2012 15:24:22 99 NOT DEF
 85 Udine 46,079412 13,211134 28-12-2009 00:00:00 01-08-2012 15:25:31 99 NOT DEF

Da notare che nel file i numeri hanno come separatore decimale, in questo caso il mio database è configurato in italiano, territorio italiano, se però nel file ho come separatore decimale il punto l’unica soluzione è aggiungere la specifica TERRITORY :

DROP TABLE EXTTABLE2;
create table exttable2
   (
  pk_id	number(5),
  localita VARCHAR2(100),
  xcoord	number(10,8),
  ycoord    number(10,8),
  data_field	date,
  time_field date,
  test_number number(10),
  dummy_field varchar2(20)
)
   organization external (
   type oracle_loader
   default directory UNLOAD
   access parameters (
   records delimited by newline
   TERRITORY AMERICA
   BADFILE 'exttable2.bad'
   LOGFILE 'exttable2.log'
   skip 0
   fields terminated by ";"  optionally enclosed by "'"
   MISSING FIELD VALUES ARE NULL
   (
	pk_id char(5),
	data_field char(10) date_format DATE mask 'dd/mm/yyyy',
	time_field char(8) date_format DATE mask 'hh24:mi:ss',
	localita char(100),
	ycoord,
	xcoord,
	test_number
   )
   COLUMN TRANSFORMS (
    dummy_field FROM CONSTANT 'NOT DEF'
   )
   )
   location
   (
   'exttable2.csv'
   )
);
select * from exttable2;

Perché anche settando a livello di session il parametro NLS_TERRITORY non funziona.

Riferimenti:

Oracle 9i Utilities, Cap. 11;

Oracle 11gR2 Administrator’s Guide (http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables013.htm#i1007424 );

Oracle 11gR2 Utilities (http://docs.oracle.com/cd/E11882_01/server.112/e22490/part_et.htm#i436567);

1 commento »

RSS feed for comments on this post. TrackBack URI

  1. Conosco l’argomento ma grazie per la rinfrescata, soprattutto perchè nella nostra lingua e ben esposto come il resto degli altri articoli.

    Bye,
    Antonio.


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Blog su WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: