CHARGEMENT DES DONNEES AVEC ORACLE SQLLOADER Intgration des

CHARGEMENT DES DONNEES AVEC ORACLE SQL*LOADER Intégration des données 1

Sommaire • • Introduction Principes de chargement des données Lancement et manipulation de SQL*LOADER Structure du fichier de contrôle Les données externes Intégration des instructions SQL dans SQL*LOADER Description des éléments syntaxiques de chargement • Démonstration pratique • Pratique d’un exemple Intégration des données 2

CHARGEMENT DES DONNEES D'UNE BD AVEC SQL*LOADER Intégration des données 3

INTRODUCTION • Une des attributions affectées à un DBA est le chargement d'une BD de données à partir de fichier de données obtenus d'un autre environnement. • le DBA cherche à effectuer une opération qui soit sûre et rapide notamment lorsque le volume de la BD tend vers des milliards d'enregistrement • ce besoin est satisfait par l'utilitaire SQL*LOADER de Oracle Intégration des données 4

Principes de chargement • Principe : un module externe qui transforme les données externes lues dans un fichier source pour former les commandes INSERT sous contrôle d'un fichier. ctl Intégration des données 5

• Le chargement prend à son compte la transposition des données en format externe pour les insérer dans une table par l'intermédiaire des commandes INSERT généré automatiquement • l'ajout d'un tuple se fait via la ZMP (Zone de mémoire partagé) et fait appel au segment de Rollback , de journalisation et de vérification des Trigger et des contraintes d'attribut et à la m. a. j des index. • mode de chargement insert insère les datas dans une table vide append insère les datas à la suite des données existantes replace insère les datas en remplaçant les données existantes truncate insère les datas après un TRUNCATE Intégration des données 6

Description SQL*LOADER de Oracle est le chargeur de données qui utilise 2 fichiers en entrée : le fichier de contrôle et le fichier de données à charger et produit 3 fichiers : . LOG, . BAD, . DSC (DISCARD) Intégration des données 7

• fichier. BAD : pour ranger les records rejetés par le chargeur comme étant non conformes aux spécification du fichier de contrôle • fichier. LOG : pour avoir une trace des activités de chargement. Un rapport de déroulement de l'opération est enregistré dans ce fichier notamment : – le nombre d'enregistrements chargés – le nombre d'enregistrements non chargés vue des erreurs de données – le nombre d'enregistrements non chargés faute d'insatisfaction de la clause When • fichier. DSC : pour conserver les données non chargées parce qu'elles ne vérifient pas la clause When du fichier de contrôle Intégration des données 8

Lancement de SQL*LOADER le chargeur est appelé par l'intermédiaire de la commande DOS : sqlldr <nom user Oracle>/<MP user>, control=<nom fichier. ctl>, log=<nom fichier log> , errors=<nb erreurs max permises> Exemple : > sqlldr stk/stk, control=. ctlmonnaie. ctl, log=. logmonnaie. log, errors=100000 • permet de charger la table monnaie à partir du fichier monnaie. ctl • les arguments sont formé avec les fichiers essentiels : fichier de données et fichier de contrôle qui décrit les données du premier fichier. Intégration des données 9

Les données externes • ces données fournies aux chargeur pour créer les tables de la base de données peuvent se présenter de différentes types façon : – plusieurs fichiers différents de données peuvent être utilisés – plusieurs extensions de tables peuvent être créées dans un même chargement – les données d'entrée peuvent être traitées par des requêtes SQL avant qu'ils soient chargées dans les tables – les règles de transposition du format externe au format interne sont spécifiées dans le fichier de contrôle. Intégration des données 10

Format externe des données • les formats disponibles pour les données externes (EXTERNAL) sont différents de celles utilisées par le SGBD lors de l'implémentation des tables. la conversion est assurée par SQL*LOADER Intégration des données 11

Fichier de contrôle • Le fichier de contrôle permet d'effectuer correctement la lecture des données à partir des fichiers externes et les transposer en format interne conformément au format spécifié par le schéma BD de la table identifiée dans le fichier de contrôle Intégration des données 12

Exemple • Fichier ouvrier. ctl : load data infile '. dataouvrier. txt' badfile '. badouvrier. bad' discardfile '. discouvrier. dsc' discardmax 300 APPEND --INSERT ou REPLACE ou TRUNCATE into table ouvrier (nas position(01: 03) INTEGER EXTERNAL, nom position(05: 20) CHAR, no_atelier position(30: 33) INTEGER EXTERNAL NULLIF no_atelier = BLANKS ) Intégration des données 13

• contenu du fichier de données : ouvrier. txt • Le résultat est récupéré dans la table ouvrier Intégration des données 14

• Le chargement de la table ouvrier a été réussi • En cas d'erreur de chargement un fichier ouvrier. bad sera crée et les lignes non chargées y sont rangées • Le fichier ouvrier. log est crée pour donner un rapport de déroulement de l'opération de chargement Intégration des données 15

Chargement des données en Format libre et délimité • les données peuvent être créées dans un fichier séquentiel avec des champs délimités par une virgule et insérés entre guillemets. Les données sont lues comme des chaînes de caractère et leur transposition dans le format de l'attribut correspondant est automatique. Chaque champ est séparé du suivant par une virgule. Les données sont précédées par des directives de chargement. Intégration des données 16

Exemple Intégration des données 17

• après le chargement la lecture de la table ouvrier permet de confirmer l'opération de chargement tout comme il est possible de faire un examen du fichier. LOG • les champs externes sont des chaînes de caractère et ne subissent aucune modification lors de leurs chargement. Intégration des données 18

Intégration des instructions SQL dans les fichiers de contrôle • Principe : pouvoir modifier les données avant leur insertion dans une table avec des fonctions SQL insérées dans la spécification du champ impliqué dans la transformation à faire lors du chargement. Cette transformation par une requête SQL , peut faire appel à des fonctions prédéfinies ou des opérateurs arithmétiques. La transformation doit être entre les guillemets. Intégration des données 19

Exemple 1: intégration SQL code_prix position(1: 1) char "(select nvl(POSITION_PMP, POSITION_PRIX_ACHAT), '01') from PARAMETRE_SOCIETE_STOCK where (code_soc = : code_soc)) " Intégration des données 20

Exemple 2: intégration SQL Intégration des données 21

Description des éléments syntaxiques du fichier de contrôle • La clause LOAD DATA est la première ligne de commande et elle sert aussi à annoncer la ligne de commande • Identification de la source de données : – * : si les données incluses dans le fichier sinon nom du fichier (ex. txt) • Badfile : chemin de génération du fichier. bad • Discardfile : chemin de génération du fichier. dsc Intégration des données 22

• Discardmax : nb max d’enregistrements discard • When : clause d’expression conditionnelle de chargement. – Exemple : • when (03: 04)='15‘ : condition de chargement des familles d’article à partir de stfnom. txt • when (03: 04)<>'05' and (22: 22)='1' Intégration des données 23

• Replace |Insert|Append into table « nom table » – Replace : remplace les tuples présents dans la table – Insert : insère des tuples dans une table obligatoirement vide – Append : ajoute des tuples dans une table à la suite de son contenu • FIELDS TERMINATED BY ‘, ’ : indique tous les champs externes sont en positions variables et terminés par ‘, ’. Intégration des données 24

• Spécification de chaque champs dont le type peut être binaire, caractère, date, integer external ou décimal external – Integer (decimal) external : indique le champ est un entier (décimal) sous la forme d’une chaîne de caractère. – Un champ peut être l’objet d’une Intégration des données 25

– Format de données : à spécifier en cas de besoin entre guillemets • Exemple : date_piece position(: ) date "yyyymmdd" – BEGINDATA : indique le début des données incluses dans le fichier de contrôle. – Constante : utile pour inserer une valeur contante à une colonne : • Exemple : unite constant 'US‘ Intégration des données 26

– Affectation par formule : permet d’affecter une valeur à une colonne en fonction des autres colonnes sans tenir compte des positions début et fin. • Exemple : RANG_CALCUL position(1: 1) char "nvl((select decode(: CODE_RUB, '12', '02', '13', '02', '01') from dual where : CODE_RUB >='01' and : CODE_RUB <= '19'), '')" Intégration des données 27

Chargement avec un fichier externe • Le chargement sera fait avec un fichier externe • Devient obligatoire si les données sont volumineuse • La réussite exige plusieurs itération de chargement • L’étude des fichiers. log et. bad devient plus approfondie • L’opération a été réalisée pour toute entreprise voulant faire migrer leur système d’une ancienne plateforme vers une nouvelle Intégration des données 28

EXEMPLE : chargement des données article load data infile '. datastfart. txt' badfile '. badarticle 1. bad' discardfile '. discarticle 1. dsc' discardmax 300000 append into table article (code_art position(03: 14) char, code_famille_art position(03: 14) char "substr(: code_famille_art, 1, 2)", design_art position(43: 82) char, code_simple position(83: 90) char, unit_achat position(91: 92) char, unit_stockage position(93: 94) char, unit_livraison position(391: 392) char "nvl(: unit_livraison, : unit_stockage)", ua_us position(95: 105) DECIMAL external "decode(: ua_us, 0, 100000, : ua_us)/100000", ul_us position(367: 372) DECIMAL external "decode(: ul_us, 0, 1000, : ul_us)/1000", alert position(118: 128) DECIMAL external ": alert/1000 , date_saisie_art position(311: 318) date "yyyymmdd") Intégration des données 29

PRATIQUE SQL*LOADER Intégration des données 30