Inhaltsverzeichnis
CSV Dateien in Oracle als external Table lesen und importieren - Daten aus Hadoop lesen
Da schon an vielen Stellen im Netz ausführlich über das Thema External Table in Oracle geschrieben wurde, hier einige gute Links zu dem Thema als Einstieg:
Oracle Doku:
Externe Websites:
Oracle External Table mit der "PREPROCESSOR" Anweisung verwenden
Mit der „PREPROCESSOR“ steht ein mächtiges Werkzeug zur Verfügung um Daten VOR dem einlesen zu entpacken oder zu konvertieren.
Für jede Datei, die in der „LOCATION“ Description beschrieben wird, wird das Script in der „PREPROCESSOR“ aufgerufen. Der Dateiname wird als %1% bzw. %1 übergeben.
Mit dieser Logik können zwei Use Cases implementiert werden:
- Die Daten liegen in einem anderen Format lokal vor ⇒
- „LOCATION“ zeigt auf das echte Datenfile
- Script verarbeitet diese Daten
- spoolt die Daten an Standard Out
- Die Daten befinden sich nicht auf dem System, müssen zum Beispiel zuvor von einem Hadoop Cluster gelesen werden ⇒
- „LOCATION“ zeigt auf eine Parameterdatei
- Script verwendet diese Parameter um die Daten zu holen/zu erzeugen
- Spoolt das Ergebnis wieder an Standard Out.
Simpelstes Beispiel um vom einem Hadoop Cluster Daten zu lesen:
Beispiel Daten anlegen und auf das HDFS kopieren:
echo "Wert1;Wert2;Wert3" > test_ext_data.dat echo "Wert4;Wert5;Wert6" >> test_ext_data.dat cat test_ext_data.dat Wert1;Wert2;Wert3 Wert4;Wert5;Wert6 #Daten auf das HDFS legen hdfs dfs -put test_ext_data.dat /tmp #Testen hdfs dfs -cat /tmp/test_ext_data.dat Wert1;Wert2;Wert3 Wert4;Wert5;Wert6
Ein Shell Skript zum lesen der Daten vom HDFS anlegen.
Dabei beachten, das diese Skript im Scope des DB Server Prozesses ausgeführt wird, d.h. alle notwendigen Umgebungsvariablen setzen und komplette Pfadangaben werden.
Je nach Distribution kann sich das als schwieriger als erwartet herausstellen! Hier im Beispiel für Cloudera basierte Distributionen, wie die Oracle BigDataVM.
cd ~/hadoop_ext_tab vi readHadoopTab.sh #!/bin/sh #Laufzeitumgebung setzen export PATH=$PATH:/bin:/sbin/:/usr/bin export HADOOP_LIBEXEC_DIR=/usr/lib/hadoop/libexec export HADOOP_CONF_DIR=/etc/hadoop/conf.bigdatalite # Übergabe Parameter Datei auslesen und Inhalt als Parameter verwerten FILENAME=`/bin/cat $1` #Mit hdfs Bordmitteln die Datei auswerten /usr/lib/hadoop-hdfs/bin/hdfs dfs -cat $FILENAME #--- #Rechte setzen chmod 776 readHadoopTab.sh # Pfad zu den Daten hinterlegen echo /tmp/test_ext_data.dat > hadoop_locator.dat Testen: ./readHadoopTab.sh /home/oracle/hadoop_ext_tab/hadoop_locator.dat Wert1;Wert2;Wert3 Wert4;Wert5;Wert6
External Table anlegen:
sqlplus scott/tiger CREATE OR REPLACE directory HADOOP_EXT_TABS AS '/home/oracle/hadoop_ext_tab/'; CREATE TABLE MY_HADOOP_EXT( wert1 VARCHAR2(2000) ,wert2 VARCHAR2(2000) ,wert3 VARCHAR2(2000) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY HADOOP_EXT_TABS ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE PREPROCESSOR HADOOP_EXT_TABS:'readHadoopTab.sh' SKIP 0 LOGFILE HADOOP_EXT_TABS:'data_load.log' BADFILE HADOOP_EXT_TABS:'data_load.bad' NODISCARDFILE FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL ) LOCATION (HADOOP_EXT_TABS:'hadoop_locator.dat') ) REJECT LIMIT 0 NOPARALLEL NOMONITORING / #test SELECT * FROM scott.MY_HADOOP_EXT; #Mit den Exports im readHadoopTab.sh funktioniert das jetzt!
Fehlersuche bzgl. dem richtigen Umgebungsvariablen
SELECT * FROM scott.MY_HADOOP_EXT; ERROR at line 1: ORA-29913: error IN executing ODCIEXTTABLEFETCH callout ORA-29400: DATA cartridge error KUP-04095: preprocessor command /home/oracle/hadoop_ext_tab/readHadoopTab.sh encountered error "/usr/bin/env: bash: No such file or directory " #Path / umgebungsvariaben passt nicht! # im readHadoopTab.sh #export PATH=$PATH:/bin:/sbin/:/usr/bin #im hadoop.sh , hdfs.sh Scripte # Bash im ! Part des Script vollqualifiziernden mit /bin/bash angeben! #Test mit /usr/bin/env /bin/bash /home/oracle/hadoop_ext_tab/readHadoopTab.sh hadoop_locator.dat #funktioniert, etwas stimmt nicht mit dem Oracle sever Prozesse?? test mit einem ganz einfachen Script: vi readHadoopTab.sh /bin/echo "wert1;wert2;wert3" # Funktioniert Nächste Variante vi readHadoopTab.sh #!/bin/sh FILENAME=`/bin/cat $1` /bin/echo "$FILENAME;wert2;wert3" # Funktioniert! #es muss also an dem Hdfs script liegen! #Ursache, im Script werden die linux Commandos nicht voll referenziet, daher muss der orginal aufrufpfad ermittelt werden: #Test mit bash -x ./readHadoopTab.sh /home/oracle/hadoop_ext_tab/hadoop_locator.dat # Script ausgaben analysieren um einen gültigen Java Aufruf zu ermitteln java -Dproc_dfs -Dhadoop.security.logger=INFO,NullAppender org.apache.hadoop.fs.FsShell -cat /tmp/test_ext_data.dat Exception IN thread "main" java.lang.RuntimeException: core-site.xml NOT found #Leider noch kein Lösung, jetzt müssen erstmal alles Settings im Detail per Hand gesetzt werden. export HADOOP_LIBEXEC_DIR=/usr/lib/hadoop/libexec export HADOOP_CONF_DIR=/etc/hadoop/conf.bigdatalite /usr/java/latest/bin/java -Dproc_dfs -Xmx1000m -Dhadoop.log.dir=/usr/lib/hadoop/logs -Dhadoop.log.file=hadoop.log -Dhadoop.home.dir=/usr/lib/hadoop -Dhadoop.id.str= -Dhadoop.root.logger=INFO,console -Djava.library.path=/usr/lib/hadoop/lib/native -Dhadoop.policy.file=hadoop-policy.xml -Djava.net.preferIPv4Stack=TRUE -Djava.net.preferIPv4Stack=TRUE -Dhadoop.security.logger=INFO,NullAppender org.apache.hadoop.fs.FsShell -cat /tmp/test_ext_data.dat Exception IN thread "main" java.lang.RuntimeException: core-site.xml NOT found # leider keine Lösung gefunden, dem Java Aufruf den Pfad zur Hadoop Konfiguration mitzugeben.
Database Vault nicht kompatibel zu External Table
SQL>SELECT * FROM PREPREP_EXT; SELECT * FROM PREPREP_EXT * FEHLER IN Zeile 1: ORA-29913: Fehler bei der Ausf³hrung von Aufruf ODCIEXTTABLEOPEN ORA-29400: DATA Cartridge-Fehler KUP-04094: Vorverarbeitung kann nicht ausgef³hrt werden wõhrend DATABASE Vault installiert wird SQL>SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault'; ... Oracle DATABASE Vault TRUE
Lösung:
Diese Option daher deinstallieren ⇒ Optionen in der Datenbank deinstallieren 10g/11g
Quellen
Oracle:
External Table API für eigene Zwecke einsetzen: