=====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:
* 12c https://docs.oracle.com/cloud/latest/db121/SUTIL/et_concepts.htm#SUTIL011
* 11g r2 http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_concepts.htm#SUTIL011
* 11g r1 http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm#ADMIN01507
Externe Websites:
* http://www.oracle.com/webfolder/technetwork/de/community/dbadmin/tipps/external_tables/index.html
* http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php#ExternalTables
==== 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 => [[dba:drop_db_optionsoracle_database_vault_deinstallieren|Optionen in der Datenbank deinstallieren 10g/11g]]
=== Quellen ===
Oracle:
* http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf
External Table API für eigene Zwecke einsetzen:
* http://www.oracle-developer.net/display.php?id=516
* http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf
* http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21nanda-312277.html