Die Aufgabe bestand darin, einen Prozess zu implementieren, der nach dem täglichen Update in der Zieldatenbank dynamisch aus allen Tabellen das endgültige Ladedatum ausliest und die Liste der betroffenen Tabellen mit ihrem letzten Aktualisierungsdatum per E-Mail an die DWH-Kollegen sendet.

Voraussetzungen

  • Als Basis wird ein Ubuntu-Server in der Version 22.04 verwendet.
  • Außerdem ist ein SSH-Zugang zum Server erforderlich.
  • Es wird ein Benutzer mit sudo-Rechten benötigt.
  • Zugang zur PostgreSQL-Datenbank mit Rechten zur Erstellung von DB-Funktionen

Basis SQL-Skript erstellen

Das Skript muss alle Tabellen in der PostgreSQL-Datenbank auflisten, die eine Spalte mit dem Namen „Ladedatum“ enthalten.

select
	table_name, 
	column_name, 
	data_type   
from 
	information_schema.columns 
where
	table_name in 
		(
			select 
				table_name 
			from 
				information_schema.tables 
			where 
				table_schema = 'public'
		)
	and (column_name like '%ldat%' or column_name like '%lade%')
	and data_type <> 'double precision'
order by 
	table_name,
	column_name
;

DB-Funktion 1: gibt eine dynamische Abfrage zurück

In diesem Schritt erstellen wir eine Datenbankfunktion, die auf der oberen Abfrage basiert und aus deren Ergebnis einen SQL-Befehl zusammenstellt. Dieser Befehl fragt aus allen betroffenen Tabellen das Maximum vom Ladedatum ab und führt diese Zeilen mit „union“ zusammen.

CREATE or replace FUNCTION fn_SQLSkript_All_Ldat() 
RETURNS text
			
LANGUAGE plpgsql as
$func$

DECLARE    
	rec record;
	strSQL text;

BEGIN
	strSQL := '';
FOR rec IN  
	SELECT s.table_schema, s.table_name, s.column_name 
	FROM information_schema.columns s
	WHERE 
	s.table_name in (select table_name from information_schema.tables WHERE table_schema = 'public')
	and (s.column_name like '%ldat%' or s.column_name like '%lade%')
	and s.data_type <> 'double precision'
	order by s.table_name, s.column_name
LOOP
	strSQL := strSQL || 'SELECT cast(''' || rec.table_schema || ''' as varchar) as schem_name, cast(''' || rec.table_name || ''' as varchar) as tab_name, ' || 'cast(max(' || rec.column_name || ') as date) max_ldat FROM ' || rec.table_schema || '.' || rec.table_name || ' UNION ';
END LOOP;

strSQL := substr(strSQL,1,length(strSQL)-7);
strSQL:= strSQL || ' order by max_ldat, schem_name, tab_name';

RETURN strSQL;
END
$func$;

select fn_SQLSkript_All_Ldat();

DB-Funktion 2: führt die dynamische Abfrage aus.

Die zweite Datenbank-Funktion ruft die erste Funktion auf und führt deren Ergebnis als SQL-Befehl aus. So erhalten Sie ein tabellarisches Ergebnis mit den Namen der betroffenen DB-Tabellen und deren letztem Aktualisierungsdatum.

CREATE or replace FUNCTION fn_All_Tables_Ldat()
RETURNS table (schem_name varchar(500), tab_name varchar(500), max_ldat date)
			
LANGUAGE plpgsql as
$func$

DECLARE    
	strSQL text;

BEGIN
	strSQL := '';
	select fn_SQLSkript_All_Ldat() into strSQL;

RETURN QUERY EXECUTE strSQL;
END
$func$;

select * from fn_All_Tables_Ldat()

Bash-Skript ruft die DB-Funktion auf.

Mit den folgenden Befehlen richten Sie ein Bash-Skript ein, das die oben erstellte Funktion in der PostgreSQL-Datenbank ausführt und das Ergebnis in tabellarischer Form zurückgibt.

$ cd /opt
$ mkdir Meine_Skripte
$ sudo nano email_notifi.sh
	#!/bin/bash

	# Taegliche Abfrage die Ladedaten der PostgreSQL Tabellen
	# und Vorbereitung fuers  Senden per Email als Info

	betreff="PostgreSQL Datenstand der Tabellen"
	PGPASSWORD= <Kennwort_des_postgres_db_users>
	export PGPASSWORD
	psql --port=5432 --host host_name --user db_user --no-password --dbname db_name  -c "select * from fn_All_Tables_Ldat();"
	unset PGPASSWORD

Cron-Job: führt das Skript täglich aus.

Es wird ein Cron-Job eingerichtet, der das zuvor erstellte Skript täglich um 8:35 Uhr ausführt und das Ergebnis per E-Mail an die angegebene E-Mail-Adresse sendet.

$ sudo -s
Passwort:
# crontab -e

# Taegliches Email-Senden mit PostgreSQL Tabellen Ladedaten
35 8 * * * bash /opt/Meine_Skripte/email_notifi.sh | mail -s "PostgreSQL Tabellen Ladedaten" empfaenger@email.adr

Nützliche Links