Introduction à l`ETL et application avec Oracle

Introduction à
l'ETL et application
avec Oracle
Stéphane Crozat
Data warehouse
http://dwh.crzt.fr
1 septembre 2016

Table des
matières
Objectifs 5
Introduction 6
I - Principes généraux d'un processus ETL 7
1. Principe de l'ETL ...................................................................................................................... 7
2. ETL ex nihilo ou outil d'ETL ................................................................................................ 7
3. ETL en mode batch ou en mode flux .................................................................................. 8
4. ETL incrémental ......................................................................................................................... 9
II -
Proposition d'architecture simplifiée pour un ETL ex nihilo, batch, non
incrémental
10
1. Architecture d'ETL à trois zones .......................................................................................... 10
2. Conseils méthodologiques .......................................................................................................... 11
3. Résumé ETL en image ............................................................................................................ 12
4. Carte des données .................................................................................................................... 12
III - Projet Fantastic: Rappel 14
IV - Implémentation simplifiée d'une zone d'extraction avec Oracle 15
1. Zone E: Extraction ................................................................................................................. 15
2. Sources de données ................................................................................................................... 16
3. Tables externes sous Oracle .................................................................................................... 17
4. Exemple de chargement de données depuis un CSV par une table externe .................. 20
5. Insertion CSV manuelle avec SQL Developer ...................................................................... 21
V - Exercice: Projet Fantastic: Mise en place de la zone d'extraction
22

VI -
Implémentation simplifiée d'une zone de transformation avec Oracle
25
1. Zone T: Transformation ......................................................................................................... 25
2. Implémentation de la zone T en RO ................................................................................... 27
3. Désactivation et réactivation de contraintes ......................................................................... 28
4. Processus de chargement BDE->BDT .................................................................................. 29
VII -
Exercice: Projet Fantastic: Mise en place de la zone de traitement
31
VIII - Implémentation simplifiée d'un data warehouse avec Oracle 34
1. Zone L: Loading ...................................................................................................................... 34
2. Implémentation du data warehouse en R ............................................................................. 34
3. Processus de chargement BDT->DW .................................................................................... 36
IX -
Exercice: Projet Fantastic: Mise en place de la zone d'exploitation
37
X - Exercice: Projet Fantastic: Implémentation des transformations
38
XI - Rappels 39
1. Rappels Oracle pour l'ETL ..................................................................................................... 39
1.1. Création de vues ............................................................................................................................................... 39
1.2. Structure d'un bloc PL/SQL ........................................................................................................................... 40
1.3. Blocs PL/SQL: Procédure, fonction, bloc anonyme ................................................................................... 40
1.4. Exécution de fichiers SQL ............................................................................................................................... 42
1.5. Fonctions SQL ................................................................................................................................................... 42
1.6. Insertion de dates avec TO_DATE .............................................................................................................. 44
1.7. Affichage à l'écran ........................................................................................................................................... 44
1.8. Transactions en SQL ....................................................................................................................................... 45
2. Rappels triggers pour l'ETL ................................................................................................... 45
2.1. Principes des triggers ....................................................................................................................................... 45
2.2. Prédicats d'événement au sein des triggers .................................................................................................. 47
2.3. Manipulation des anciennes et nouvelles valeurs dans les triggers (:old et:new) ............................... 48
2.4. Quelques règles à respecter pour les triggers .............................................................................................. 49
3. Rappels Oracle RO .................................................................................................................. 49
3.1. Création de type en SQL3 sous Oracle (extension au LDD) .................................................................. 49
3.2. Création de table objet (modèles et LDD) .................................................................................................. 50

3.2. Création de table objet (modèles et LDD) ................................................................................................. 50
3.3. Méthodes de table d'objets ............................................................................................................................... 51
3.4. Méthodes et SELF ............................................................................................................................................ 52
XII - Compléments 54
1. Script de remise à zéro d'un schéma Oracle ....................................................................... 54
2. Éléments avancés pour l'ETL ................................................................................................. 55
2.1. Gestion des erreurs .......................................................................................................................................... 55
2.2. Clés artificielles ................................................................................................................................................. 55
2.3. Éléments pour l'ETL incrémental ................................................................................................................... 56
2.4. Intégration des dimensions multi-sources ...................................................................................................... 58
2.5. Performance et maintenance ............................................................................................................................ 58
3. Exercice: Gestion des erreurs ................................................................................................. 58
4. Exercice: Surveillance des données ........................................................................................ 59
Solutions des exercices 60
Abréviations 62
Bibliographie 63
Webographie 64

5
Stéphane Crozat
Savoir implémenter un data warehouse avec un SGBD relationnel
Savoir implémenter un processus ETL vers un data warehouse
Objectifs