komplexe Überwachungsabfrage

J

Joerg

Benutzer
Beiträge
7
Hallo, da meine SQL-Kenntnisse noch sehr eingeschränkt sind und mich die Problematik überfordert stelle ich mein Problem hier mal ein.

Ich möchte regemäßig per sql-Script-Abfrage die Veränderungen eines Feldwertes (Wert = Datum oder NULL) bestimmter Datensätze (Feld3 = ABC) in einer Tabelle überwachen und in einer zweiten Datenbank protokollieren.
In eine zweite Datenbank deshalb, weil die Datenbank1 eine komplexe Produktivdatenbankanwendung ist die ich nicht verändern möchte.
Datenbank1, Tabelle1, Feld2 soll überwacht werden.
Identifiziert wird jede Zeile über eine eindeutige ID (Feld1) und einen fixen Wert in Feld3 (z.B.: "ABC").
Protokolliert wird in Datenbank2, Tabelle2, Feld1, Feld2 + Datumsfeld (Änderungsdatum).

Beispiel:
Tabelle1.JPG

In Datenbank 1 ändert sich am 07.02.2015 bei ID2 und ID3 das Feld2, zusätzlich kommt Datensatz ID5 und ID6 dazu:

Tabelle2.JPG

Das Skript soll dies erkennen und in Datenbank2 folgende Änderungen / Ergänzungen vornehmen
(natürlich nur bei/mit den "ABC"- Datensätzen):

Tabelle3.JPG
Meine angedachte Vorgehensweise wäre folgende:
Zuerst (einmalig) in Datenbank2 die betreffende Tabelle anlegen und mit allen Daten aus Datenbank1 (Feld3 = ABC) füllen (Datum = 01.02.2015).
Danach per Script regelmäßig alle betreffenden Datensätze aus Datenbank1 (Feld3 = ABC) in eine (ggf. temporäre) Tabelle3 in Datenbank2 kopieren (um nicht in 2 Datenbanken arbeiten zu müssen).
Danach die "Überwachungsaktion" inkl. Ergänzung der neuen Datensätze in Tabelle2 durchführen. Anschließend alle Datensätze in der Tabelle3 löschen.

Ziel ist wie gesagt, alle Veränderungen in Feld2 bei Datensätzen mit Feld3 = "ABC" zu protokollieren.
Über eine gefilterte Abfrage (über Feld1) kann ich mir dann spezielle Datensätze heraussuchen.

Das Erstellen der Datenbank / Tabelle bzw. das Füllen der (ggf. temporären) Tabelle ist nicht das Problem. Mein Problem ist die Umsetzung der Erkennung der Veränderung bzw. der neuen Datensätze.
Wäre diese Vorgehensweise richtig oder gibt es einfachere Vorschläge (und Lösungen) ?
Vielen Dank schon mal vorab.
 
Werbung:
Joerg schrieb:
Danach per Script regelmäßig alle betreffenden Datensätze aus Datenbank1 (Feld3 = ABC) in eine (ggf. temporäre) Tabelle3 in Datenbank2 kopieren (um nicht in 2 Datenbanken arbeiten zu müssen).
Danach die "Überwachungsaktion" inkl. Ergänzung der neuen Datensätze in Tabelle2 durchführen. Anschließend alle Datensätze in der Tabelle3 löschen.
Zum Vergrößern anklicken....

Dummer Ansatz. Datenbanken kennen TRIGGER. Diese 'feuern' bei Events wie INSERT, UPDATE und DELETE.

Das wäre die eine Lösung, in PostgreSQL ab 9.4 käme noch eine andere Lösung, die noch deutlich effizienter wäre: logical Replication Slots. Das hat aber M$SQL nicht, falls dazu Du oder jemand anders Fragen hat kann ich das gern näher erleutern.
 
"Dummer Ansatz" akzeptiere ich kleinlaut - da ich es nicht besser weiß.
Ansonsten hilft mir PostgreSQL nicht weiter ... und der Rest auch nicht.
Sorry - aber trotzdem vielen Dank.
 
Das man mit einem Trigger eine "Ereignisüberwachung" machen kann, ist mir bekannt.
Wie ich es in diesem Fall aber speziell einsetze leider nicht so richtig.

Mein Skript soll einmal täglich die Veränderungen erkennen und protokollieren.
 
Ich wollte in Datenbank2 alle identischen Einträge (ID + Feld1 + Feld2 identisch) in der temporären Tabelle3, die auch in Tabelle2 vorhanden sind löchen und anschließend die verbleibenden Datensätze in Tabelle 2 verschieben und das Änderungsdatum (Datum) setzen.
Dafür müsste ich natürlich den ID noch mit übernehmen.
 
Du kannst dir auch einfach die ganze Arbeit ersparen und nen Trigger verwenden... Aber hey... Warum einfach wenns auch schwierig geht :)
Wenn du die RICHTIGE Antwort nicht aktzeptieren willst, warum überhaupt erst fragen? ;)
 
Joerg schrieb:
Das man mit einem Trigger eine "Ereignisüberwachung" machen kann, ist mir bekannt.
Wie ich es in diesem Fall aber speziell einsetze leider nicht so richtig.

Mein Skript soll einmal täglich die Veränderungen erkennen und protokollieren.
Zum Vergrößern anklicken....

Ich zeig es mal ganz trivial, aber mit PostgreSQL.

Code: 
test=# create table daten (id int primary key, val text);
CREATE TABLE   
Time: 8,623 ms   
test=*# create table daten_log (id serial primary key, ts timestamp default now(), trigger text, daten_id int, daten_val text);
CREATE TABLE   
Time: 30,234 ms   
test=*# create or replace function my_trigger() returns trigger as
test-#   
test-# $$begin   
test$#  insert into daten_log(trigger, daten_id, daten_val)   
test$#  select   
test$#  TG_OP,   
test$#  case   
test$#  when TG_OP = 'DELETE' then old.id   
test$#  when TG_OP = 'INSERT' then new.id   
test$#  when TG_OP = 'UPDATE' then new.id   
test$#  end,   
test$#  case   
test$#  when TG_OP = 'INSERT' then new.val   
test$#  when TG_OP = 'UPDATE' then old.val || '---' || new.val
test$#  when TG_OP = 'DELETE' then old.val   
test$#  end;   
test$#  return new;   
test$# end; $$language plpgsql;   
CREATE FUNCTION   
Time: 0,610 ms   
test=*# create trigger trg_log after insert or update or delete on daten for each row execute procedure my_trigger();
CREATE TRIGGER   
Time: 0,476 ms

Damit hast erst einmal 2 leere Tabellen und einen TRIGGER auf der Tabelle daten, der bei allen 3 Events feuert.

Nun spielen wir damit:

Code: 
test=*# select * from daten;   
 id | val   
----+-----   
(0 rows)   

Time: 0,468 ms
test=*# select * from daten_log;
 id | ts | trigger | daten_id | daten_val
----+----+---------+----------+-----------
(0 rows)   

Time: 0,436 ms
test=*# insert into daten values (1, 'eins');
INSERT 0 1   
Time: 0,866 ms   
test=*# insert into daten values (2, 'zwei');
INSERT 0 1   
Time: 0,379 ms   
test=*# select * from daten;   
 id | val
----+------
  1 | eins
  2 | zwei
(2 rows)

Time: 0,271 ms
test=*# select * from daten_log;
 id |  ts  | trigger | daten_id | daten_val
----+----------------------------+---------+----------+-----------
  1 | 2015-02-07 15:12:26.394147 | INSERT  |  1 | eins
  2 | 2015-02-07 15:12:26.394147 | INSERT  |  2 | zwei
(2 rows)

Time: 0,265 ms
test=*# update daten set val = 'zwei-neu' where id = 2;
UPDATE 1
Time: 0,513 ms
test=*# select * from daten_log;
 id |  ts  | trigger | daten_id |  daten_val
----+----------------------------+---------+----------+-----------------
  1 | 2015-02-07 15:12:26.394147 | INSERT  |  1 | eins
  2 | 2015-02-07 15:12:26.394147 | INSERT  |  2 | zwei
  3 | 2015-02-07 15:12:26.394147 | UPDATE  |  2 | zwei---zwei-neu
(3 rows)

Time: 0,257 ms
test=*# delete from daten where id=1;
DELETE 1
Time: 0,541 ms
test=*# select * from daten_log;
 id |  ts  | trigger | daten_id |  daten_val
----+----------------------------+---------+----------+-----------------
  1 | 2015-02-07 15:12:26.394147 | INSERT  |  1 | eins
  2 | 2015-02-07 15:12:26.394147 | INSERT  |  2 | zwei
  3 | 2015-02-07 15:12:26.394147 | UPDATE  |  2 | zwei---zwei-neu
  4 | 2015-02-07 15:12:26.394147 | DELETE  |  1 | eins
(4 rows)

Time: 0,213 ms
test=*#

Das ist jetzt sehr einfach, es gibt umfangreichere Lösungen z.B. hier

http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
http://www.postgresonline.com/journal/archives/330-Using-HStore-for-Archiving.html
 
Wenn ich es einfach bzw. per Trigger wüsste, würde ich nicht fragen. PostgreSQL verwende ich zwar nicht, werde aber versuchen es nachzuvollziehen.
Wenn es aber nicht in MSSQL genutzt werden kann, nützt mir der Ansatz auch nichts.
Trotzdem Danke für die Hilfe.
 
Ich glaube, dafür reichen meine Kenntnisse bei weitem nicht. Den Trigger müßte ich ja vermutlich direkt in der Datenbank anlegen. Das kann / möchte / darf ich aber nicht. Daher der Gedanke es per externen Scriptaufruf zu machen - auch wenn es umständicher und weniger professionell ist.
 
Hier mal ein paar Ansätze...

Um deine Tabelle einmal täglich zu füllen:
Code: 
Insert into Tabelle2@datenbank2 (Select * From tabelle1 Where änderungsdatum > sysdate - 1);

Um deine Tabelle3 abzugleichen:
Code: 
Insert Into tabelle2 (Select t.*, sysdate as änderungsadtum From (Select * From tabelle3 Minus Select * From tabelle2) t)
 
Wenn du Schreibrechte auf die Quell-Datenbank hast und sich die Tabellenstruktur nicht ändert wäre eine Trigger-Lösung definitiv die elegantere Wahl. Wenn das nicht geht, kannst du wie von Distrilec beschrieben auch einfach die Tabelle kopieren und die Datensätze mit Datum versehen.

Das werden natürlich viele Datensätze, daher würde ich mit EXCEPT die Filtern, die zum Vortag unverändert geblieben sind. Wenn zu deinen zu protokolliernden "Veränderungen" auch das Löschen von Datensätzen in der Quell-Tabelle gehört, musst du das dann natürlich auch abbilden. Ich habe es bei uns genauso gemacht und bin nicht sehr begeistert weil es sehr komplex ist, denn aktuellen Stand aus den gesicherten Daten zu ermitteln.
 
Werbung:
Vielen Dank für die weiteren Anregungen und Unterstützung.
Momentan bin ich in der Sache noch nicht weitergekommen - mangels Zeit.
Lösen muss ich die Aufgabe aber noch.

Löschen (an der Quelldatenbank) will ich nichts, die Zieldatenbank soll natürlich immer nur die Veränderungen speichern. Ob meine Rechte ausreichen um einen Trigger anzulegen, muss ich prüfen.
 
Zurück
Oben