Tables externes [Introduction à l'ETL et application avec Oracle]

Tables externes sous Oracle

Définition : Table externe

Une table externe sous Oracle est une méthode d'accès sans copie à des fichiers CSV dynamiques exactement comme s'il s'agissait d'une table de la BD.

Syntaxe : Préambule : Déclaration des répertoires de travail

CREATE OR REPLACE DIRECTORY <nom du répertoire source> AS '<chemin du répertoire de la source>';
CREATE OR REPLACE DIRECTORY <nom du répertoire log> AS '<chemin du répertoire des fichiers de log>';

CREATE OR REPLACE DIRECTORY <nom du répertoire source> AS '<chemin du répertoire de la source>';
CREATE OR REPLACE DIRECTORY <nom du répertoire log> AS '<chemin du répertoire des fichiers de log>';

Attention : Accès aux répertoire de travail

Le répertoire de la source et le fichier source doivent être accessibles en lecture pour le processus Oracle.

Le répertoire des fichiers de log doit être accessible en lecture et écriture pour le processus Oracle.

Attention :

« All directories are created in a single namespace and are not owned by an individual schema »

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5007.htm

Syntaxe : Création d'une table externe

CREATE TABLE <nom de la table> (
<déclaration des attributs avec domaine mais sans contrainte>
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY <répertoire déclaré préalablement>
ACCESS PARAMETERS 
(
RECORDS DELIMITED BY '<caractère de fin de ligne>'
SKIP <nombre de lignes à ignorer>
CHARACTERSET <encodage des caractères>
BADFILE <répertoire>:'<fichier>'
LOGFILE <répertoire>:'<fichier>'
FIELDS TERMINATED BY '<séparateur de champ>'
OPTIONALLY ENCLOSED BY '<séparateur de chaîne>'
)
LOCATION ('<fichier source>'))
REJECT LIMIT UNLIMITED;

CREATE TABLE <nom de la table> (
<déclaration des attributs avec domaine mais sans contrainte>
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY <répertoire déclaré préalablement>
ACCESS PARAMETERS 
(
RECORDS DELIMITED BY '<caractère de fin de ligne>'
SKIP <nombre de lignes à ignorer>
CHARACTERSET <encodage des caractères>
BADFILE <répertoire>:'<fichier>'
LOGFILE <répertoire>:'<fichier>'
FIELDS TERMINATED BY '<séparateur de champ>'
OPTIONALLY ENCLOSED BY '<séparateur de chaîne>'
)
LOCATION ('<fichier source>'))
REJECT LIMIT UNLIMITED;

Attention : Ne pas utiliser de commentaire au sein la déclaration des paramètres d'accès

« One important point to remember is that comments must be placed before any access parameters. If you include comments in the access parameter sections, Oracle will throw an error when you query the external table but not when you are creating it. »

https://oracleappsnotes.wordpress.com/2012/02/10/oracle-external-tables-a-few-examples/

Exemple :

CREATE OR REPLACE DIRECTORY monRepertoireSrc AS '/user1c/nf26/nf26/projet/csv/';
CREATE OR REPLACE DIRECTORY monRepertoireLog AS '/volsme/user1x/uvs/nf26/nf26p099/test/';
/

CREATE OR REPLACE DIRECTORY monRepertoireSrc AS '/user1c/nf26/nf26/projet/csv/';
CREATE OR REPLACE DIRECTORY monRepertoireLog AS '/volsme/user1x/uvs/nf26/nf26p099/test/';
/

CREATE TABLE tImport (
a VARCHAR(50),
b NUMBER(10)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY monRepertoireSrc
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
SKIP 1
CHARACTERSET UTF8
BADFILE monRepertoireLog:'import.bad'
LOGFILE monRepertoireLog:'import.log'
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
)
LOCATION ('sources.csv'))
REJECT LIMIT UNLIMITED;

CREATE TABLE tImport (
a VARCHAR(50),
b NUMBER(10)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY monRepertoireSrc
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
SKIP 1
CHARACTERSET UTF8
BADFILE monRepertoireLog:'import.bad'
LOGFILE monRepertoireLog:'import.log'
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
)
LOCATION ('sources.csv'))
REJECT LIMIT UNLIMITED;

Description des paramètres

DEFAULT DIRECTORY : Le répertoire où se trouvent le fichier source
RECORDS DELIMITED BY : Séparateur d'enregistrements (newline est le caractère de fin de ligne standard du système)
SKIP 1 : permet d'ignorer la première ligne d'un fichier CSV lorsque celui-ci contient les entêtes de colonnes
CHARACTERSET : permet de spécifier l'encodage des caractères (UTF8...)
BADFILE : Fichier contenant les enregistrements rejetés à l'import (récréé à chaque exécution)
LOGFILE : Fichier contenant les traces d'exécution (traces ajoutées à chaque exécution)
FIELDS TERMINATED BY : Séparateur de champs (en général ; dans un CSV)
OPTIONALLY ENCLOSED BY : Séparateur de chaînes (en général " dans un CSV)
LOCATION : Nom du fichier dans le répertoire sélectionné
REJECT LIMIT : Nombre d'enregistrements pouvant être rejetés avant interruption de la requête (un entier ou UNLIMITED)

Syntaxe : Vérification de la déclaration de la table externe

DESCRIBE Timport;
SELECT TABLE_NAME, TYPE_NAME, DEFAULT_DIRECTORY_NAME FROM USER_EXTERNAL_TABLES;

DESCRIBE Timport;
SELECT TABLE_NAME, TYPE_NAME, DEFAULT_DIRECTORY_NAME FROM USER_EXTERNAL_TABLES;

Attention : Accès

L'accès à la source externe CSV par Oracle ne se fait en fait qu'à la première interrogation (SELECT), donc il est nécessaire d'exécuter un appel à la table pour valider cet accès (seule une vérification syntaxique est faite au moment du CREATE TABLE, le fichier CSV peut même ne pas exister).

En pratique si les données comportent des erreurs, les problèmes se déclareront à ce moment là. On consultera le fichiers de log et des enregistrements rejetés pour le savoir.

SELECT * FROM Timport;

SELECT * FROM Timport;

Attention : Gros fichiers

Si les volumes de donnée sont importants (s'il y a beaucoup de lignes), privilégiez l'usage de la clause ROWNUM pour éviter de faire transiter des méga-octets de données entre le serveur et le client. Les délais de réaction en seront améliorés.

Vous pouvez aussi utiliser des SUM, MIN, MAX, etc. pour être sûr que toutes les lignes et colonnes sont correctement lues.

Conseil : Tout recompter

Une fois la procédure terminée effectuer un count(*) pour déterminer si l'import a bien traité toutes les lignes du fichier source.

Remarque : NUMBER(X)

Pour spécifier des entiers dans les tables externes, utiliser NUMBER(X), avec le (X) obligatoire.

Remarque : Caractère de fin de ligne

RECORDS DELIMITED BY newline signifie que le caractère standard du système est utilisé comme caractère de fin de ligne.

Or :

sous Unix le caractère de fin de ligne est "\n"
sous Windows il est "\r\n"

Donc, si un fichier CSV est encodé sous un système et lu sous un autre, il y aura un problème. Par exemple si le fichier est encodé sous Windows il aura "\r\n" à la fin de chaque ligne, et s'il est lu sous Linux, Oracle cherchera uniquement un "\n", d'où un "\r" résiduel sera considéré comme faisant partie du dernier champ.

La solution la plus robuste est de spécifier en dur le caractère de fin de ligne :

Si le fichier source a été encodé sous Unix : RECORDS DELIMITED BY '\n'
Si le fichier source a été encodé sous Windows : RECORDS DELIMITED BY '\r\n'

Conseil :

Penser à gérer les fichiers de rejet et de log. Le plus simple est de les supprimer après une exécution incorrecte.

Les fichiers de rejet ne sont pas créés si l'exécution est correcte (et donc un éventuel fichier existant n'est pas modifiée par une exécution correcte)
Les fichiers de log grossissent à chaque exécution