19. November 2012

Externe Tabellen für eine entfernte(!) Datei erzeugen: How To

Create an external tables - based on a remote(!) file
In diesem Blog-Posting möchte ich mich dem Thema "Externe Tabellen" widmen und ein in Oracle11g neu eingeführtes Feature nochmals vorstellen: Den Präprozessor für externe Tabellen. Dieser ist - kurz gesagt - ein Executable, welches die Daten der externen Tabelle (also der Datei) quasi "vorbehandelt" - die externe Tabelle selbst basiert dann auf der Ausgabe des Präprozessors. Das "klassische" Beispiel ist somit eine externe Tabelle auf einer .gz-Datei - der Präprozessor wäre dann das gunzip Werkzeug.
So weit - so gut. Das Feature lässt sich aber auch anders nutzen - denn es kann ja ein beliebiges Executable hergenommen werden. Also könnte man sich ja auch das Kopieren der Datei vom entfernten Server zur Datenbankmaschine sparen und diese direkt vom Remote-Server lesen - und genau das möchte ich hier vorstellen. Wir wollen mit der Oracle-Datenbank eine externe Tabelle für die Datei error_log des Apache-Webservers auf einem entfernten Rechner erstellen. Basis für diess Blog-Posting ist der Tipp Externe Tabellen: Die Apache-Datei "error_log" als APEX-Bericht der deutschsprachigen APEX-Community.

Vorbereitungen

Zunächst müssen wir in die Lage kommen, auf Betriebssystem-Ebene des Datenbankservers die entfernte Datei anzeigen zu lassen. Heutzutage machen wir dazu kein rlogin oder telnet mehr, sondern wir verwenden ssh. Und damit keine Passwörter in die Skriptdateien geschrieben werden müssen, ist der erste Schritt das Austauschen der Schlüssel. Auf dem Datenbankserver wird ein Schlüsselpaar wie folgt generiert:
[oracle@databaseserver]$ ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa

Generating public/private dsa key pair.
Created directory '/home/oracle/.ssh'.
Your identification has been saved in /home/oracle/.ssh/id_dsa.
Your public key has been saved in /home/oracle/.ssh/id_dsa.pub.
The key fingerprint is:
32:b1:02:a2:55:94:d5:49:01:7b:51:db:b5:29:d8:d3 oracle@databaseserver.mydomain.com
In der Datei $HOME/.ssh/id_dsa.pub befindet sich nun der öffentliche Schlüssel des Users oracle auf dem Server databaseserver. Dieser muss nun in die Datei $HOME/.ssh/authorized_keys auf dem entfernten Recher (apacheserver.mydomain.com) kopiert werden. Diese Datei sollte dann in etwa so aussehen.
[oracle@apacheserver]$ cat ~/.ssh/authorized_keys
ssh-dss AAAAB3NzaC1kc3MAAACBAJrsXSk8zxAiGiAoEjbxYTCKoHgZCXT9+pfSgSnNAxHVaD1ND5UkOii2a0ySopS3arxHNiHXT/rU9sgstUtn+vkMPDFOHtoLOP5hJ6adaq7ed3xILeljgj21388432kjdnsakjfdsajfcbO1K5PYJ8oXhasIOX/lebKiRUfkMtAAAAFQDjsMdyn3dkIC7rZfmU5DdA3XfwBQAAAIAKCfCjqy4sQ/JlOHVoHw83amB4XzCcUTTKpa7X+f69zNxNa6Q9arG4VO0iODtiHiotzTlWaMsTPyrSgmhGPsYnaVLNyBR7o6a7YAWt1EvvxC8IM64RCG9U8owN2o5OXTsahd872909123ß219kjdsalk0943jHrPmECvdpwzo47jd9rFkgfqQAAAIAROytFKMsuhldyF7tPfcWywqDqzQDLjiyhm05eYUYsjerR6+LrAayRGbHzPPBjK5KAGBqBE2EYUtWQZrmqk226EFIT+KAxjHFxWtl3Rb6bfJSmWEQGcG3uD5gcNJ1yowHuvnLvfy1eUyTErGylAzCUO7pMbz4ZW3QXkg1Qzdfgkw== oracle@databaseserver.mydomain.com
Danach kann ein erster Test erfolgen: Öffnet man eine SSH-Verbindung von databaseserver zum apacheserver, dann darf kein Login-Dialog mehr kommen - die Remote-Shell erscheint sofort (beim allerersten Mal kommt aber noch die übliche Frage, ob der Schlüssel des entfernten Rechners in den Cache aufgenommen werden soll).
[oracle@databaseserver]$ ssh -l oracle spatial.de.oracle.com
Last login: Mon Nov 19 10:02:55 2012 from dhcp-u19825.mydomain.com
[oracle@apacheserver ~]$
Der nächste Schritt ist nun sehr einfach: Mit einem einfachen SSH-Kommando kann die ganze entfernte Datei auf der Konsole des Datenbankservers ausgegeben werden - dieses Kommando packen wir in eine Skriptdatei wie folgt:
#!/bin/sh
/usr/bin/ssh -l oracle apacheserver.mydomain.com cat /oracle/u01/app/oracle/product/ohs/ohs/logs/error_log
Diese Datei nennen wir dann get-exttab, machen sie ausführbar mit chmod +x, und legen Sie ins Verzeichnis $HOME/exttab/scripts. Daneben legen wir noch das Verzeichnis $HOME/exttab/data an. In letzteres legen wir eine Datei namens error_log - die Inhalte sind egal. Die Verzeichnisstruktur sollte dann so aussehen:
[oracle@databaseserver]$ touch $HOME/exttab/data/error_log
[oracle@databaseserver]$ find . 
./exttab
./exttab/scripts
./exttab/scripts/get-exttab
./exttab/data
./exttab/data/error_log
Ihr könnt das ganze noch testen: Ein Aufruf des Skripts get-exttab sollte nun die Inhalte der Datei error_log des entfernten Rechners apacheserver anzeigen.
[oracle@databaseserver]$ $HOME/exttab/scripts/get-exttab
[Thu Dec 17 13:33:57 2009] [error] [client X.X.X.X] File does not exist: /oracle/u01/app/oracle/product/ohs/ohs/htdocs/favicon.ico
[Thu Dec 17 13:34:21 2009] [error] [client X.X.X.X] mod_oc4j: request to OC4J localhost:8000 failed: Connect failed (errno=111), referer: http://apacheserver.mydomain.com/pls/apex/f?p=106:1:1472768466016248:::::
[Thu Dec 17 13:34:22 2009] [error] [client X.X.X.X] mod_oc4j: request to OC4J localhost:8000 failed: Connect failed (errno=111), referer: http://apacheserver.mydomain.com/pls/apex/f?p=106:1:1472768466016248:::::
:
Damit sind die Vorbereitungen abgeschlossen: Nun geht es zur Datenbank.

Einrichten der Directory-Objekte

Damit das Präprozessor-Feature nicht zum Sicherheits-Albtraum wird, ist es wichtig, die Directory-Objekte richtig anzulegen. Für das Präprozessor-Feature wurde eigens das neue Privileg EXECUTE ON DIRECTORY eingeführt - mit welchem naturgemäß sehr vorsichtig umgegangen werden sollte. Es sollte niemals gemeinsam mit dem WRITE ON DIRECTORY Privileg vergeben werden; denn der User, der diese beiden Privilegien hat, kann danach beliebige Betriebssystem-Kommandos ausführen. Das Datenbankschema, welches die externe Tabelle anlegen möchte, braucht also das READ- und EXECUTE-Privileg auf das Directory $HOME/exttab/scripts sowie READ und WRITE Privilegien auf $HOME/exttab/data. Letzteres enthält nur die Dummy-Datei error_log und wird außerdem als Ablage für entstehende Logdateien und Badfiles verwendet.
create directory scriptdir as '/home/oracle/exttab/scripts/'
/

create directory datadir as '/home/oracle/exttab/data'
/

grant read,execute on directory scriptdir to testit
/

grant read, write on directory datadir to testit
/

Erzeugen und testen der externen Tabelle

Als nächstes kann die externe Tabelle angelegt werden:
drop table ohs_error_log
/

CREATE TABLE ohs_error_log (
  zeitstempel date,
  schweregrad varchar2(200),
  client_ip   varchar2(200),
  error_modul varchar2(200),
  error_text  varchar2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY datadir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR SCRIPTDIR:'get-exttab'
    nobadfile nologfile
    fields MISSING FIELD VALUES ARE NULL(
      zeitstempel date "DY MON DD HH24:MI:SS YYYY" terminated by whitespace enclosed by '[' and ']'  ,
      schweregrad char terminated by whitespace enclosed by '[' and ']',
      client_ip   char terminated by whitespace enclosed by '[client' and ']' ltrim,
      error_modul char terminated by ':',
      error_text  char (100000) ltrim
    )
   )
   LOCATION ('error_log') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
Das interessante ist die PREPROCESSOR-Klausel: Sie besagt, dass die Inhalte der Datei error_log im Verzeichnis DATADIR (DEFAULT DIRECTORY) vom Skript get-exttab im Directory SCRIPTDIR aufbereitet werden sollen. Die Datei error_log ist, wie schon gesagt, natürlich leer - das Skript get-exttab holt einfach die Inhalte vom entfernten Server ab. Man sieht deutlich die Trennung der Directory-Objekte. Das DEFAULT DIRECTORY namens DATADIR wird zum Lesen der Pseudodatei und zur Ablage etwaiger Log- und Badfiles verwendet - das Directory SCRIPTDIR, welches das Shellskript enthält und auf welches die Execute-Privilegien eingeräumt wurden, steht in der PREPROCESSOR-Klausel. Nun wird jedesmal, wenn diese externe Tabelle selektiert wird ...
  • die Pseudodatei error_log aus dem DATADIR gelesen (diese ist leer)
  • deren Inhalte (in diesem Fall also "nichts") wird an das Skript get-exttab im SCRIPTDIR übergeben
  • get_exttab holt die Dateiinhalte vom Rechner apacheserver
  • die abgerufenen Inhalte werden mit Hilfe der SQL*Loader-Spezifikation verarbeitet und als externe Tabelle aufbereitet.
Also müsste man die Tabelle ja nun selektieren können ...
SQL> select * from ohs_error_log where rownum <20;

23.09.2009 17:40:31
error
10.165.250.114
File does not exist
/oracle/u01/app/oracle/product/ohs/ohs/htdocs/favicon.ico

23.09.2009 17:40:34
error
10.165.250.114
mod_plsql
/pls/apex/apex HTTP-503 ORA-28000 ORA-28000: the account is locked\n

:
Für externe Tabellen öffnen sich damit völlig neue Möglichkeiten - so kann man mit dem Shellskript natürlich nicht nur eine Datei von einem externen Server abrufen, sondern auch die Ausgabe eines Programms als externe Tabelle anbinden - alles ist möglich.
In this blog posting I'd like to write a bit about external tables - and particularly about the preprocessor feature, which was introduced in Oracle11g. Briefly, the proprocessor is an executable which processes the file contents before these are being passed to the database. The table data - in the database - is then based on the proprocessed file contents. The "classic" example is an external table based on a .gz file: gunzip would be the preprocessor in that case.
So far - so good. Given that the preprocessor can be any executable, we can use that feature in far more cases than just processing the contents of a file in the database server's filesystem. When it's about providing contents from a file on a remote server as an external table, we could omit copying the file to the database server and use the preprocessor to grab the file contents directly. And such an example is the topic of this blog posting: We'll create an external table in the database, based on the Apache Webservers' error_log file. But the Apache Webserver resides an a remote server - so we'll use the preprocessor feature.

Prerequisites

Firstly, we'll need to be able to grab the remote file's contents at the operating system level. Expressed otherwise: We need the executable which gets the remote file contents for us and will be used as the preprocessor afterwards. Since we write the year 2012, we'll use ssh for the communication from the database server (databaseserver.mydomain.com) to the machine hosting the Apache Webserver (apacheserver.mydomain.com).
So we'll have to configure ssh in order to be able to execute the cat command on apacheserver - from databaseserver. First step is to generate a key pair on databaseserver and to provide the public key to apacheserver. After this, apacheserver is able to verify the identity of databaseserver based on the shared public key.
[oracle@databaseserver]$ ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa

Generating public/private dsa key pair.
Created directory '/home/oracle/.ssh'.
Your identification has been saved in /home/oracle/.ssh/id_dsa.
Your public key has been saved in /home/oracle/.ssh/id_dsa.pub.
The key fingerprint is:
32:b1:02:a2:55:94:d5:49:01:7b:51:db:b5:29:d8:d3 oracle@databaseserver.mydomain.com
Now oracle's public key is contained in the file $HOME/.ssh/id_dsa.pub. We'll copy this to apacheserver and append it to the file $HOME/.ssh/authorized_keys. This file should then look as follows:
[oracle@apacheserver]$ cat ~/.ssh/authorized_keys
ssh-dss AAAAB3NzaC1kc3MAAACBAJrsXSk8zxAiGiAoEjbxYTCKoHgZCXT9+pfSgSnNAxHVaD1ND5UkOii2a0ySopS3arxHNiHXT/rU9sgstUtn+vkMPDFOHtoLOP5hJ6adaq7ed3xILeljgj21388432kjdnsakjfdsajfcbO1K5PYJ8oXhasIOX/lebKiRUfkMtAAAAFQDjsMdyn3dkIC7rZfmU5DdA3XfwBQAAAIAKCfCjqy4sQ/JlOHVoHw83amB4XzCcUTTKpa7X+f69zNxNa6Q9arG4VO0iODtiHiotzTlWaMsTPyrSgmhGPsYnaVLNyBR7o6a7YAWt1EvvxC8IM64RCG9U8owN2o5OXTsahd872909123ß219kjdsalk0943jHrPmECvdpwzo47jd9rFkgfqQAAAIAROytFKMsuhldyF7tPfcWywqDqzQDLjiyhm05eYUYsjerR6+LrAayRGbHzPPBjK5KAGBqBE2EYUtWQZrmqk226EFIT+KAxjHFxWtl3Rb6bfJSmWEQGcG3uD5gcNJ1yowHuvnLvfy1eUyTErGylAzCUO7pMbz4ZW3QXkg1Qzdfgkw== oracle@databaseserver.mydomain.com
Then we can perform a first test: Opening an ssh connection from databaseserver to apacheserver should result in a shell prompt directly - without any password login dialog. Authentication must be performed just with the keys exchanged. When opening the connection for the very first time, the usual dialog about adding the server's key to the cache will appear.
[oracle@databaseserver]$ ssh -l oracle spatial.de.oracle.com
Last login: Mon Nov 19 10:02:55 2012 from dhcp-u19825.mydomain.com
[oracle@apacheserver ~]$
The next step is rather simple: The remote file can be listed on the local console with a simple SSH command:
#!/bin/sh
/usr/bin/ssh -l oracle apacheserver.mydomain.com cat /oracle/u01/app/oracle/product/ohs/ohs/logs/error_log
The script file is named get-exttab and then placed in the directory $HOME/exttab/scripts (don't forget to make it executable with chmod +x). Beyond this, we'll create the directory $HOME/exttab/data, into which we'll place a file named error_log. The contents of that file don't matter - it's just a dummy file for the external table mechanism in Oracle. The "real" file contents are being grabbed with the shell script. After that we should habe the following file/folder structure.
[oracle@databaseserver]$ touch $HOME/exttab/data/error_log
[oracle@databaseserver]$ find . 
./exttab
./exttab/scripts
./exttab/scripts/get-exttab
./exttab/data
./exttab/data/error_log
Now we can do another test: Calling the script get-exttab should result in the contents of the remote file being listed on the console.
[oracle@databaseserver]$ $HOME/exttab/scripts/get-exttab
[Thu Dec 17 13:33:57 2009] [error] [client X.X.X.X] File does not exist: /oracle/u01/app/oracle/product/ohs/ohs/htdocs/favicon.ico
[Thu Dec 17 13:34:21 2009] [error] [client X.X.X.X] mod_oc4j: request to OC4J localhost:8000 failed: Connect failed (errno=111), referer: http://apacheserver.mydomain.com/pls/apex/f?p=106:1:1472768466016248:::::
[Thu Dec 17 13:34:22 2009] [error] [client X.X.X.X] mod_oc4j: request to OC4J localhost:8000 failed: Connect failed (errno=111), referer: http://apacheserver.mydomain.com/pls/apex/f?p=106:1:1472768466016248:::::
:
Now we have met all prerequisites. The next steps are being performed in the database.

Creating directory objects

When using the preprocessor feature, one should take particular care about security. Executing the preprocessor means executing an operating system command on the database server. Oracle introduced a new privilege EXECUTE ON DIRECTORY for that purpose. A user having that privilege can execute any file residing in the given directory. So having both WRITE ON DIRECTOTY and EXECUTE ON DIRECTORY privileges might result in a security nightmare: that user now can execute any operating system command on the database server. Take care!
So we have seen the reason for placing the script get-exttab into a separate directory. Our database schema will only get READ and EXECUTE privileges on that directory. For the other directory $HOME/exttab/data the database schema gets READ and WRITE privileges: It needs to "read" the dummy file error_log from there and to write Logfiles and Badfiles.
create directory scriptdir as '/home/oracle/exttab/scripts/'
/

create directory datadir as '/home/oracle/exttab/data'
/

grant read,execute on directory scriptdir to testit
/

grant read, write on directory datadir to testit
/

Erzeugen und testen der externen Tabelle

Now we can create the external table as follows. I'll not elaborate on external tables themselves and the SQL*Loader syntax - there's plenty of documentation and other blog postings about this.
drop table ohs_error_log
/

CREATE TABLE ohs_error_log (
  zeitstempel date,
  schweregrad varchar2(200),
  client_ip   varchar2(200),
  error_modul varchar2(200),
  error_text  varchar2(4000)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY datadir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR SCRIPTDIR:'get-exttab'
    nobadfile nologfile
    fields MISSING FIELD VALUES ARE NULL(
      zeitstempel date "DY MON DD HH24:MI:SS YYYY" terminated by whitespace enclosed by '[' and ']'  ,
      schweregrad char terminated by whitespace enclosed by '[' and ']',
      client_ip   char terminated by whitespace enclosed by '[client' and ']' ltrim,
      error_modul char terminated by ':',
      error_text  char (100000) ltrim
    )
   )
   LOCATION ('error_log') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
The PREPROCESSOR clause is the most interesting part. It states that the file contents are being preprocessed by the script get-exttab in the directory SCRIPTDIR. The file contents come from the file error_log (LOCATION) in the directory DATADIR (DEFAULT DIRECTORY). So when the table is being selected ...
  • the dummy file error_log within DATADIR is being read (the file is empty in our case)
  • then the executable script get-exttab within SCRIPTDIR will be called
  • get_exttab grabs the file contents from the remote server
  • the file contents will then be processed according to the SQL*Loader specification and prepared as an external table.
So we can select the table now:
SQL> select * from ohs_error_log where rownum <20;

23.09.2009 17:40:31
error
10.165.250.114
File does not exist
/oracle/u01/app/oracle/product/ohs/ohs/htdocs/favicon.ico

23.09.2009 17:40:34
error
10.165.250.114
mod_plsql
/pls/apex/apex HTTP-503 ORA-28000 ORA-28000: the account is locked\n

:
The preprocessor clause for external tables is a very powerful new feature: This use cases range from simple 'gunzipping' files over grabbing contents from remote servers up to even creating external tables based on application output. There are no limits.

Keine Kommentare:

Beliebte Postings