23. April 2014

Tricks mit externen Tabellen: Unix "ps" per SELECT ausführen

External table tricks: Execute Unix "ps" command with SELECT
Heute geht es nochmals um die Möglichkeiten, die sich durch die Verwendung von externen Tabellen in der Datenbank ergeben. Mit Version 11.2 wurde ja die Möglichkeit geschaffen, ein "Präprozessor"-Kommando zu hinterlegen, welches ausgeführt wird, bevor die externe Datei tatsächlich als Tabelle ausgelesen wird. Die Standardanwendung hierfür wäre das Unix Utility gunzip - damit können externe Tabellen direkt auf .gz-Dateien erstellt werden; der Präprozessor packt sie unmittelbar vor dem Auslesen aus. So weit, so gut.
Ende 2012 hatte ich ein Blog-Posting veröffentlicht, welches zeigt, wie man mit dem Präprozessor auch eine Datei von einem Remote-System (per SSH) als externe Tabelle in die Datenbank einbinden kann; die Datei muss also gar nicht eigens auf den Datenbankserver kopiert werden.
Heute zeige ich, wie man diese Architektur nutzen kann, um Informationen als externe Tabelle aufzubereiten, die gar nicht als Datei vorliegen. Als Beispiel soll die Unix-Prozessliste dienen ...
$ ps -ef
UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 Apr08 ?        00:01:58 init [3]
root         2     0  0 Apr08 ?        00:00:00 [kthreadd]
root         3     2  0 Apr08 ?        00:00:06 [migration/0]
root         4     2  0 Apr08 ?        00:00:31 [ksoftirqd/0]
root         5     2  0 Apr08 ?        00:00:00 [watchdog/0]
root         6     2  0 Apr08 ?        00:00:05 [migration/1]
:            :     :  : :     :               : :
Es ist nun schon recht naheliegend, dass dieses Kommando der "Präprozessor" sein soll - eine Datei, die vom Präprozessor umgewandelt werden soll, ist hier gar nicht nötig. Nun muss nur noch das Ausgabeformat geparst werden - und das sollte ja einfach sein ... ist es aber nicht ganz - denn das "ps"-Kommando nutzt Leerzeichen, um diese tabellenartige Formatierung zu generieren; das sieht zwar gut aus, ist aber als externe Tabelle nicht einfach zu interpretieren (die Anzahl der Leerzeichen zwischen zwei Feldern ist immer anders - und auch die "Feldpositionen" sind nicht zwingend fix). Besser wäre es, wenn die Felder durch Kommas oder Tab-Zeichen (\t) getrennt wären. Das folgende Kommando macht genau das.
$ ps -ef | awk 'OFS="\t" {$1=$1; print}'
Auf der Unix-Shell sieht die Ausgabe genauso aus. Schaut man sich den Output aber genau an, so erkennt man, dass die Felder nun durch Tabulatoren getrennt werden; und genau das brauchen wir für die externe Tabelle. Legt nun also zwei Verzeichnisse auf dem Datenbankserver an: oradir_exe und oradir_file.
$ mkdir /home/oracle/oradir_exe
$ mkdir /home/oracle/oradir_file
In das Verzeichnis oradir_exe kommt nun eine Datei namens getps mit unten stehendem Inhalt (cat getps). Im wesentlichen ist es das obige Kommando ps - aber nochmals leicht verändert, damit einige Details mehr angezeigt werden. Außerdem werden die Executables ps und awk mit ihrem kompletten Pfad angegeben. Zum Abschluß nicht vergessen, die Datei ausführbar zu machen.
$ cd /home/oracle/oradir_exe
$ cat getps
#!/bin/sh
/bin/ps -eo euser,pid,ppid,c,size,tty,time,cmd | /bin/awk 'OFS="\t" {$1=$1; print}'

$ chmod +x getps
$ ls -l
total 4
-rwxr-xr-x 1 oracle oinstall 92 Apr 23 14:47 getps
Ins Verzeichnis oradir_file kommt eine Datei namens file.txt - diese könnt Ihr mit touch erzeugen; die Inhalte (in dem Falle keine) sind völlig egal.
$ cd /home/oracle/oradir_file
$ touch file.txt
$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Apr 23 14:34 file.txt
Damit sind die Vorbereitungen außerhalb der Datenbank abgeschlossen. Nun geht es in die Datenbank, um die externe Tabelle zu erzeugen. Der Einfachheit halber mache ich hier alles als SYS - eine Unix-Prozessliste ist sicherlich ohnehin nur für den DBA interessant. Zuerst also die Directory-Objekte erzeugen ...
drop directory oradir_exe
/

drop directory oradir_file
/

create directory oradir_exe as '/home/oracle/oradir_exe'
/

create directory oradir_file as '/home/oracle/oradir_file'
/
Dann die externe Tabelle. Man beachte die Zeile mit der PREPROCESSOR-Klausel. Hier wird eigens das Directory-Objekt ORADIR_EXE verwendet. Die Datei file.txt wird als LOCATION, also als Quelle der externen Tabelle angegeben; eine externe Tabelle wird eben so definiert. De-Facto ist diese Datei aber völlig uninteressant, denn das Executable kann seinen Output auch ohne erzeugen. Man kann auch deutlich den Vorteil erkennen, der sich dadurch ergibt, dass die Felder der Ausgabe nun durch Tabs getrennt werden: die externe Tabelle enthält nun nur noch die Klausel FIELDS TERMINATED BY '\t' .
drop table unix_ps
/

create table unix_ps(
  userid     varchar2(30),
  pid        number,
  ppid       number, 
  cpu_util   number,
  "SIZE"     number,
  tty        varchar2(10),
  "TIME"     varchar2(20),
  cmd        varchar2(500)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY oradir_file
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR ORADIR_EXE:'getps'
    nobadfile nologfile
    fields terminated by '\t'(
      userid   char,
      pid      char,
      ppid     char,
      cpu_util char,
      "SIZE"   char,
      tty      char,
      "TIME"   char,
      cmd      char
    )
   )
   LOCATION ('file.txt') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
Und das war's auch schon. Ihr könnt die externe Tabelle nun selektieren ...
select "CMD", "PID", "SIZE", "CPU_UTIL" 
from "UNIX_PS"
where "USERID" = 'oracle' and "CMD" like 'ora_%' 
order by "SIZE" desc;

CMD                         PID       SIZE   CPU_UTIL
-------------------- ---------- ---------- ----------
ora_dbw0_orcl              1803      10072          0
oracleorcl                 2477       5412          0
ora_pmon_orcl              1785       5116          0
ora_mmnl_orcl              1815       5056          0
oracleorcl                29308       4244          0
:                             :          :          :
Nach gleichem Schema können auch andere Informationen für SQL-Abfragen in der Datenbank verfügbar gemacht werden. Viel Spaß beim Ausprobieren.
In this blog posting, I will (again) talk about external tables and how these can be used within the database. Oracle 11.2 introduced the preprocessing capability for external tables - the external table definition is being extended with a preprocessor clause: The executable specified here will be executed, just before the external file is being read. The standard use case for this is the Unix gunzip command - using this as the executable, we can create external tables directly on compressed .gz files without unpacking them first.
In 2012, I published a blog posting which desribes how the preprocessor can be used in order to create an external table for a file residing on a remote server. In this example, the preprocessor grabs the file from the remote site using SSH. Thus, the files for external tables do not need to be placed on the database server itself.
Today I'll show, how the proprocessor feature for external tables can be used to create external tables based on the output of a Unix executable - with no static file at all. As example, I'll use the Unix ps command.
$ ps -ef
UID        PID  PPID  C STIME TTY          TIME CMD
root         1     0  0 Apr08 ?        00:01:58 init [3]
root         2     0  0 Apr08 ?        00:00:00 [kthreadd]
root         3     2  0 Apr08 ?        00:00:06 [migration/0]
root         4     2  0 Apr08 ?        00:00:31 [ksoftirqd/0]
root         5     2  0 Apr08 ?        00:00:00 [watchdog/0]
root         6     2  0 Apr08 ?        00:00:05 [migration/1]
:            :     :  : :     :               : :
It's obvious, that the "ps" command will be the "preprocessor" in this case. We'll just have no file to be preprocessed; the executable will generate all the output itself. The output of the "ps" command looks nicely like a table ... but the formatting is done only with blanks - and this makes it really hard to create an external table definition for. The amount of blanks varies with the output itself and the field positions are variable as well. It would be much easier to have commas or Tab ( \t) characters as field separators - the following Unix sequence does the job for us.
$ ps -ef | awk 'OFS="\t" {$1=$1; print}'
Executed on the Unix shell, we hardly see a difference - but since the fields are not being separated by "tab", our external table definition will be much more easy. Now, let's create the required structures on the operating system side. First, we need to directories: oradir_exe and oradir_file.
$ mkdir /home/oracle/oradir_exe
$ mkdir /home/oracle/oradir_file
In oradir_exe we'll place a file named getps with the content shown below (cat getps). Basically it is the above mentioned ps command, but modified a bit in order to see more information. The used executables ps and awk are furthermore extended to their full path (which is required for the external table preprocessor). Finally, don't forget to make the file executable (chmod +x).
$ cd /home/oracle/oradir_exe
$ cat getps
#!/bin/sh
/bin/ps -eo euser,pid,ppid,c,size,tty,time,cmd | /bin/awk 'OFS="\t" {$1=$1; print}'

$ chmod +x getps
$ ls -l
total 4
-rwxr-xr-x 1 oracle oinstall 92 Apr 23 14:47 getps
In oradir_file we place a file named file.txt - you can create it with the touch command. This file must just exist - its contents are totally irrelevant.
$ cd /home/oracle/oradir_file
$ touch file.txt
$ ls -l
total 0
-rw-r--r-- 1 oracle oinstall 0 Apr 23 14:34 file.txt
This concludes the preparations on the operating system side. The next steps are being executed within the database. For simplicily I have done all steps as SYS. First, we'll create the Oracle directory objects corresponding to the Unix folders.
drop directory oradir_exe
/

drop directory oradir_file
/

create directory oradir_exe as '/home/oracle/oradir_exe'
/

create directory oradir_file as '/home/oracle/oradir_file'
/
Then the external table itself is being created. Note the PREPROCESSOR clause. It references the getps script we created before and the directory object it resides in. The other directory object and the file file.txt are referenced in the LOCATION clause, since an external table requires a file to read from. But in our case, the preprocessor does all the work. We can also see the benefit we have gained, by changing the field separators to tabs: we can just declare the FIELDS to be TERMINATED BY '\t' . The plain ps -ef output would be much more difficult to parse.
drop table unix_ps
/

create table unix_ps(
  userid     varchar2(30),
  pid        number,
  ppid       number, 
  cpu_util   number,
  "SIZE"     number,
  tty        varchar2(10),
  "TIME"     varchar2(20),
  cmd        varchar2(500)
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY oradir_file
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE 
    PREPROCESSOR ORADIR_EXE:'getps'
    nobadfile nologfile
    fields terminated by '\t'(
      userid   char,
      pid      char,
      ppid     char,
      cpu_util char,
      "SIZE"   char,
      tty      char,
      "TIME"   char,
      cmd      char
    )
   )
   LOCATION ('file.txt') 
)
REJECT LIMIT UNLIMITED PARALLEL
/
And that's it - the external table can be used (as always) by selecting it ...
select "CMD", "PID", "SIZE", "CPU_UTIL" 
from "UNIX_PS"
where "USERID" = 'oracle' and "CMD" like 'ora_%' 
order by "SIZE" desc;

CMD                         PID       SIZE   CPU_UTIL
-------------------- ---------- ---------- ----------
ora_dbw0_orcl              1803      10072          0
oracleorcl                 2477       5412          0
ora_pmon_orcl              1785       5116          0
ora_mmnl_orcl              1815       5056          0
oracleorcl                29308       4244          0
:                             :          :          :
And with the same approach, much more operating system information can be made available to SQL queries. Have fun trying it out.

2. April 2014

Veranstaltung "Unstrukturierte Daten in Oracle" im Mai 2014

Im Mai 2014 führen meine Kollegin Ulrike Schwinn und ich eine Veranstaltung (nicht nur) für SQL und PL/SQL Entwickler statt. Diesmal geht es um unstrukturierte Daten in der Oracle-Datenbank - das bedeutet, es werden Large Objects (BLOB, CLOB), XML und Oracle TEXT betrachtet - alles inkl. Oracle12c Update. 

Vielleicht sieht man sich ...? Wir freuen uns drauf.


Mai 2014: SQL and beyond

Unstrukturierte Daten in Oracle12c


Unstrukturierte Daten wie Bilder, Dokumente und Dateien sind neben den relationalen Tabellendaten normaler Bestandteil nahezu jeder Anwendung. Meist werden diese der Einfachheit halber ins Dateisystem gelegt, obwohl die Speicherung in der Datenbank viele Vorteile mit sich bringt.

Erfahren Sie auf diesem Oracle Database Day, welche Möglichkeiten Ihnen die neue Datenbankversion 12c zur Speicherung von und zum Umgang mit unstrukturierten Daten bietet. Dabei wird auf grundsätzliche Dinge wie das Large-Object-Management ebenso eingegangen wie auf die konkrete Nutzung der Daten durch XML-Funktionen, Volltextindizierung und mehr ...

Die Teilnahme an der Veranstaltung ist kostenlos. Melden Sie sich am besten gleich an.


Termine

  • 06.05.2014:
    Oracle Hauptverwaltung München
    Riesstr. 25
    D-80992 München (Anfahrt)

    [Anmeldung]
  • 07.05.2014:
    Oracle Niederlassung Frankfurt
    Robert-Bosch-Str. 5
    D-63303 Dreieich (Anfahrt)

    [Anmeldung]
  • 08.05.2014:
    Oracle Niederlassung in Hamburg
    Kühnehöfe 5
    D-22761 Hamburg (Anfahrt)

    [Anmeldung]

Agenda


11:30Registrierung & Kaffee
12:00Beginn der Veranstaltung
Unstrukturierte Daten in Oracle12c speichern
LOB-Management, Kompression, Performanceaspekte, ...
Mittagspause
Umgang mit XML in der Oracle-Datenbank
XMLTYPE in Oracle12c, Performance, XML-Standards (XQuery Fulltext), ...
Oracle TEXT: Neues in Oracle12c
Near Realtime Index, Automatische Spracherkennung, neue Query Operatoren, ...
16:00Fragen/Diskussion & Ende der Veranstaltung
This blog posting is about an event in Germany in german language ... and therefore in german only.

Beliebte Postings