=====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:

<code bash>

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

</code>

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.

<code bash>
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

</code>


External Table anlegen:


<code sql>

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!


</code>


=== Fehlersuche bzgl. dem richtigen Umgebungsvariablen ===

<code sql>

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.

</code>


====Database Vault nicht kompatibel zu External Table ====

<code sql>

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


</code>

Lösung:

Diese Option daher deinstallieren => [[dba:drop_db_options&#oracle_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