Wie kann man beurteilen, ob ein ETL-Prozess erfolgreich gewesen ist? Alleine der Umstand, dass ein Prozess nicht mit einer Exception abgebrochen wurde, bedeutet nicht zwangsläufig, dass der Prozess auch das gemacht hat, was man von ihm erwartet hat. Eine les- und auswertbare Protokollierung eines ETL-Prozesses kann hier helfen. Sie ermöglicht eine sichere Beurteilung des Erfolgs eines ETL-Prozesses.
Dieser Artikel stellt eine Vorgehensweise für die Protokollierung eines ETL-Prozesses auf der Basis von gespeicherten Prozeduren vor, die Protokolldatensätze in Protokolltabellen einfügen und aktualisieren. Ziel ist es, ein les- und auswertbares Protokoll zu erstellen, das Antworten auf wesentliche Fragen zu der Ausführung eines ETL-Prozesses bereit hält:
- Wie lange dauert der ETL-Prozess insgesamt?
- Wie lange dauert die Ausführung einer gespeicherten Prozedur, eines SSIS-Paketes oder einer anderen Komponente?
- Wie lange dauert die Ausführung eines konkreten SQL-Statements?
- Wie viele Datensätze wurden durch ein SQL-Statement bearbeitet?
Natürlich wollen wir auch wissen, ob der Prozess als Ganzes, eine Prozedur oder auch ein konkretes SQL-Statement erfolgreich ausgeführt wurden.
Die Vorgehensweise ist eigentlich gerade heraus: Zu Beginn jeder Aktion wird ein Protokolldatensatz geschrieben und nach der Beendigung der Aktion wird dieser entweder mit dem Status Erfolg oder Misserfolg und gegebenenfalls auch noch anderen hilfreichen Informationen aktualisiert. That’s it! Ein bisschen mehr darf es dann aber doch noch sein…
Inhalt
Das Ergebnis
Und nun die Herleitung…
Dreistufige Protokollierung
[LL].[Execution]
[LL].[Component]
[LL].[Trace]
Der Schemaname LL
Tabellenbeschreibung, Deklaration und Datenmodell
[LL].[Execution]
[LL].[Component]
[LL].[Trace]
[LL].[Error]
Status der Ausführung des ETL-Prozesses
Prozeduren für die Protokollierung
[LL].[spInsertTrace]
[LL].[spUpdateTrace]
[LL].[spUpdateTraceSuccess]
Exception Handling
Beispiel für die Protokollierung und das Exception-Handling in einem ETL-Prozess
[T2].[spETLProcess]
[T2].[spDoSomething_2_2]
Fazit
Verwandte Artikel
Das Ergebnis
Auch wenn die Erstellung eines Protokolls an sich keine große Sache ist bzw. sein sollte, gibt es doch einiges dazu zu schreiben. Fangen wir aber mit dem Ergebnis an und zäumen das Pferd von hinten auf. Die hier vorgestellte Prozessprotokollierung ist dreistufig und verwendet die folgenden Protokoll-Tabellen:
- [LL].[Execution]
- [LL].[Component]
- [LL].[Trace]
In diesen Tabellen werden ein ETL-Prozess und die damit verbundenen Komponenten und Arbeitsschritte mit wachsender Granularität – von oben nach unten gelesen – protokolliert. Der ETL-Prozess, die Komponenten und die einzelnen Arbeitsschritte werden mit genau einem Datensatz protokolliert.
Eng mit der Erstellung der Protokollierung des Prozesses verbunden ist natürlich auch die Protokollierung von Fehlern. Die hier vorgestellte Vorgehensweise für die Protokollierung verwendet die folgenden Tabelle für die Protokollierung von Fehlern:
- [LL].[Error]
Die folgenden Abbildungen zeigen das Ergebnis der dreistufigen Protokollierung eines einfachen, kompakten, aber vollständigen ETL-Prozesses.
In der Tabelle [LL].[Trace] wird jede Aktion unter anderem mit dem Namen der Prozedur, der Zielentität, die von der Prozedur bearbeitet wird, einer kurzen Beschreibung, was konkret gemacht wurde und anderen Informationen, wie zum Beispiel die Anzahl der betroffenen Datensätze und die Dauer der Ausführung protokolliert.
In der Tabelle [LL].[Component] werden die Aufrufe von Prozeduren und SSIS-Paketen oder – allgemein ausgedrückt – Komponenten protokolliert. Auch hier wird eine kurze Beschreibung der Aufgabe der Komponente, die Zielentität und die Dauer der Ausführung protokolliert.
Auf der obersten Ebene wird jede Ausführung des ETL-Prozesses in der Tabelle [LL].[Execution] mit genau einem Datensatz protokolliert.
Alle Tabellen für die Prozessprotokollierung enthalten zwei Spalten [State] und [Success], in denen der Erfolg oder Misserfolg der Ausführung einer Aktion, einer Komponente oder des ETL-Prozesses gespeichert wird.
Und nun die Herleitung…
Nach der kurzen Einführung des Ergebnisses sind die folgenden Aspekte der Vorgehensweise zu klären:
- Dreistufige Protokollierung
- Tabellenbeschreibung, Deklaration und Datenmodell
- Status der Ausführung des ETL-Prozesses
- Prozeduren für die Protokollierung
- Exception-Handling
- Beispiel für die Protokollierung und das Exception-Handling in einem ETL-Prozess
Dreistufige Protokollierung
Die Vorgehensweise sieht die Protokollierung eines ETL-Prozesses in den drei Tabellen [LL].[Execution], [LL].[Component] und [LL].[Trace] vor. Jede der Tabellen ist für die Protokollierung von bestimmten Artefakten vorgesehen. Dieser Abschnitt stellt die Verwendung der jeweiligen Tabellen, ihre Spalten und den Code für ihre Erstellung vor.
[LL].[Execution]
In dieser Tabelle wird die Ausführung eines ETL-Prozesses mit genau einem Datensatz protokolliert. Ein ETL-Prozess hat immer eine Einstiegsfunktion. Das kann eine gespeicherte Prozedur, ein SSIS-Paket, ein Talend-Job oder aus ein SQL Server Agent Job sein. Zu Beginn der Ausführung der Einstiegsfunktion wird ein Protokolldatensatz in diese Tabelle eingefügt. Nach erfolgreicher Verarbeitung aller Aufgaben wird dieser Datensatz mit dem Status success aktualisiert, im Fehlerfall mit dem Status error. Damit enthält diese Tabelle eine Übersicht über alle Ausführungen des ETL-Prozesses und stellt Informationen wie zum Beispiel den Status und die Dauer der Ausführung bereit.
[LL].[Component]
In dieser Tabelle wird die Ausführung einer Komponente mit genau einem Datensatz protokolliert. Eine Komponente kann eine gespeicherte Prozedur, ein SSIS-Paket, ein Talendjob sein. Eine Komponente zeichnet sich dadurch aus, dass sie eine oder mehrere Datenmanipulationen steuert und ausführt. Wie schon bei der Tabelle [LL].[Execution] wird zu Beginn der Ausführung ein Protokolldatensatz in diese Tabelle eingefügt und nach Beendigung mit dem Status success oder dem Status error aktualisiert. Damit enthält diese Tabelle je Ausführung des ETL-Prozesses eine Liste der ausgeführten Komponenten und stellt Informationen wie den Status und die Dauer der Ausführung bereit.
[LL].[Trace]
Die Bezeichnung dieser Tabelle enthält schon den Hinweis, dass die Tabelle für die detailliertes Protokollierung der Aktionen des ETL-Prozesses vorgesehen ist, eben einen Trace erstellt. Welche Aktionen protokolliert werden ist eine Design-Entscheidung des Entwicklers. Es empfiehlt sich aber mindestens jedes INSERT- UPDATE und DELETE-Statement mit einem eigenen Protokolldatensatz zu protokollieren. Die Tabelle sieht neben den bereits oben erwähnten Status-Feldern auch die Speicherung der Anzahl der betroffenen Datensätze vor und gibt damit dem Entwickler die Möglichkeit zu beurteilen, ob die Statements genau das gemacht haben, was erwartet wurde.
[LL].[Error]
Fehler, die in einem ETL-Prozess erkannt wurden, sind natürlich zu protokollieren. Zu unterscheiden ist die Protokollierung von Exceptions und Datenfehlern. Die Struktur dieser Tabelle ist auf die Protokollierung von Datenfehlern ausgelegt und enthält Spalten, in denen jeder Datenfehler vollständig, les- und auswertbar protokolliert werden kann. Dieser Artikel legt den Fokus auf die Protokollierung eines ETL-Prozesses und nicht die Protokollierung von Datenfehlern. Die Protokollierung des Prozesses ist eng verbunden nicht einem expliziten Exception-Handling. Dem Exception-Handling und der Protokollierung von Exceptions ist ein separater Abschnitt gewidmet.
Der Schemaname LL
Der Schemaname LL steht für Logging Layer. In diesem Schema sind alle Protokolltabellen und die gespeicherten Prozeduren, die für die Protokollierung verwendet werden, gespeichert.
Tabellenbeschreibung, Deklaration und Datenmodell
Die folgenden Abschnitte beschreiben die Tabellen für die Protokollierung des Prozesses sowie die Tabelle für die Protokollierung von Fehlern. Abschließend findet sich ein Diagramm mit dem Datenmodell zu diesen Tabellen.
[LL].[Execution]
Spalten
Die Tabelle enthält die folgenden Spalten:
| Spalte | Beschreibung |
|---|---|
| Id | Die Spalte [Id] ist als IDENTITY deklariert und identifiziert einen Protokolldatensatz in dieser Tabelle. |
| Process | Die Tabellen für die Protokollierung können nicht nur von einem einzigen ETL-Prozess verwendet werden. Damit die Protokolldatensätze verschiedener ETL-Prozesse voneinander unterschieden werden können, ist in der Spalte [Process] ein eindeutiger Name des ETL-Prozesses zu anzugeben. |
| Start End | Diese beiden Spalten geben den Startzeitpunkt und das Ende der Ausführung eines ETL-Prozesses an. Beim Start des ETL-Prozesses wird in der Spalte [Start] der aktuelle Zeitstempel gespeichert. Die Spalte [End] wird beim Start mit einem NULL initialisiert. Der letzte Befehl eines ETL-Prozesses muss eine Aktualisierung der Datensatzes mit dem Status vornehmen (Spalten [State] und [Success]). Bei der Aktualisierung wird der aktuelle Zeitstempel in der Spalte [End] gespeichert. Ein NULL in der Spalte [End] zeigt entweder an, dass der Prozess noch ausgeführt wird, oder dieser mit einer Exception abgebrochen ist. Zeitangaben werden immer als UTC-Zeit gespeichert. |
| DeltaStart DeltaEnd | Diese Spalten legen das Zeitfenster für einen Delta-Load fest. [DeltaStart] legt die untere Zeitgrenze fest und [DeltaEnd] die obere. Das Zeitfenster wird von der Prozedurgespeicherten Prozedur [LL].[spInsertExecution], die den Protokolldatensatz in diese Tabelle einfügt, automatisch ermittelt. |
| User Machine Version | In diesen Spalten ist der Kontext anzugeben, in dem der ETL-Prozess ausgeführt wird. Der Benutzer, der in Spalte [User] angegeben ist, ist der Benutzer, unter dem der Prozess ausgeführt wird. Dieser ist maßgeblich für Berechtigungen an Datenbanken, Tabellen, File-Shares etc. Der Benutzer wird beim Einfügen des Protokolldatensatzes über die T-SQL Funktion SUSER_SNAME() als Default-Wert ermittelt. In der Spalte [Machine] wird der Name des Computers gespeichert, auf dem der ETL-Prozess ausgeführt wird. Der Name wird beim Einfügen des Protokolldatensatzes über die T-SQL Funktion HOST_NAME() ebenfalls als Default-Wert ermittelt. Die Spalte [Version] nimmt die Versionsnummer des ETL-Prozesses und ist gegebenenfalls bei der Fehlersuche nach einem fehlerhaften Deployment eines ETL-Prozesses hilfreich. |
| State | Die Spalte [State] zeigt den aktuellen Status des ETL-Prozesses an. Mögliche Werte sin processing, warning, success oder error. Initial wird der ETL-Prozess mit [State] gleich processing gestartet. Im Erfolgsfall wird [State] mit warning oder success aktualisiert. Im Fehlerfall wird [State] mit error aktualisiert. |
| Success | Die Spalte [Success] gibt an, ob der ETL-Prozess erfolgreich mit [State] gleich warning oder success beendet wurde. Initial wird der ETL-Prozess mit [Success] gleich 0 gestartet. Im Erfolgsfall wird [Success] mit 1 aktualisiert. |
| CreatedOn CreatedBy ModifiedOn ModifiedBy | Die Spalten [CreatedOn] und [ModifiedOn] enthalten einen UTC-Zeitstempel. Initial wird [CreatedOn] mit dem aktuellen Zeitstempel über einen Default-Wert belegt und [ModifiedOn] mit einem NULL. [ModifiedOn] wird bei der Aktualisierung des Status mit dem dann geltenden Zeitstempel aktualisiert. Die gleiche Systematik gilt für die Felder [CreatedBy] und [ModifiedBy]. Hier wird der Benutzer gespeichert, unter dem der Prozess ausgeführt wird. |
Deklaration
Die Tabelle wird über die folgende Anweisung erstellt:
CREATE TABLE [LL].[Execution]
(
[Id] bigint IDENTITY (1, 1) NOT NULL
,[Process] nvarchar (max) NOT NULL
,[Start] datetime NOT NULL
,[End] datetime NULL
,[DeltaStart] datetime NULL
,[DeltaEnd] datetime NULL
,[User] nvarchar(128) NULL
,[Machine] nvarchar(128) NULL
,[Version] int NULL
,[State] nvarchar(128) NULL
,[Success] bit NULL
,[CreatedOn] datetime
CONSTRAINT [DF_LL_Execution_CreatedOn]
DEFAULT (GETUTCDATE()) NOT NULL
,[CreatedBy] nvarchar(100)
CONSTRAINT [DF_LL_Execution_CreatedBy]
DEFAULT (SUSER_SNAME()) NOT NULL
,[ModifiedOn] datetime NULL
,[ModifiedBy] nvarchar(100) NULL
,CONSTRAINT [PK_LL_Execution]
PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE TRIGGER [LL].[TR_LL_Execution_Update]
ON [LL].[Execution]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE [LL].[Execution]
SET
[ModifiedOn] = GETUTCDATE()
,[ModifiedBy] = SUSER_SNAME()
FROM
[LL].[Execution]
INNER JOIN inserted
ON
inserted.[ID] = [LL].[Execution].[ID];
END;
GO
[LL].[Component]
Spalten
| Spalte | Beschreibung |
|---|---|
| Id | Die Spalte [Id] ist als IDENTITY deklariert und identifiziert einen Protokolldatensatz in dieser Tabelle. |
| ExecutionId | Über diesen Fremdschlüssel auf die Tabelle [LL].[Execution] können die Protokolldatensätze genau einer Ausführung des ETL-Prozesses zugeordnet werden. |
| Source | Der Text in dieser Spalt gibt an, von welchem Typ die Komponente ist, die den Protokolldatensatz geschrieben hat. Verwendete Werte sind zum Beispiel T-SQL oder SSIS. |
| Component | In dieser Spalte wird der Name der Komponente, die den Protokolldatensatz geschrieben hat, festgehalten. Bei Prozeduren ist dieses der Prozedurname, bei anderen Komponenten zum Beispiel der Dateiname der Komponente. |
| Version | Sofern zu der Komponente eine Versionsnummer verfügbar ist, kann und sollte diese in dieser Spalte ausgegeben werden. |
| Entity | In dieser Spalte ist der Name der Zieltabelle, oder – allgemeiner formuliert – Entität anzugeben, die durch die Komponente bearbeitet wird. Der Name ist frei wählbar. Es empfiehlt sich, den Namen systematisch zu wählen, um eine systematische Auswertung des Protokolls zu erleichtern. |
| Step | In der Spalte [Step] ist eine kurze Beschreibung der Aufgabe der Komponente anzugeben. Auch diese Beschreibung sollte einer strengen Systematik folgen, um eine leichte Auswertung des Protokolls zu ermöglichen. |
| FileId | Die Spalte [FileId] ist für die Verarbeitung von Dateien durch die Komponente vorgesehen. Für die Beschreibung der hier vorgestellten Vorgehensweise ist sie nicht von Bedeutung. |
| Description | In dieser Spalte kann eine weitergehende Beschreibung der Aufgabe der Komponente eingefügt werden. |
| State | Die Spalte [State] zeigt den aktuellen Status der Ausführung der Komponente an. Mögliche Werte sin processing, warning, success oder error. Initial wird die Komponente mit [State] gleich processing gestartet. Im Erfolgsfall wird [State] mit warning oder success aktualisiert. Im Fehlerfall wird [State] mit error aktualisiert. |
| Success | Die Spalte [Success] gibt an, ob die Komponente erfolgreich mit [State] gleich warning oder success beendet wurde. Initial wird der Komponente mit [Success] gleich 0 gestartet. Im Erfolgsfall wird [Success] mit 1 aktualisiert. |
| CreatedOn CreatedBy ModifiedOn ModifiedBy | Die Spalten [CreatedOn] und [ModifiedOn] enthalten einen UTC-Zeitstempel. Initial wird [CreatedOn] mit dem aktuellen Zeitstempel über einen Default-Wert belegt und [ModifiedOn] mit einem NULL. [ModifiedOn] wird bei der Aktualisierung des Status mit dem dann geltenden Zeitstempel aktualisiert. Die gleiche Systematik gilt für die Felder [CreatedBy] und [ModifiedBy]. Hier wird der Benutzer gespeichert, unter dem der Prozess ausgeführt wird. |
Deklaration
CREATE TABLE [LL].[Component]
(
[Id] bigint IDENTITY (1, 1) NOT NULL
,[ExecutionId] bigint NOT NULL
,[Source] nvarchar (5) NOT NULL
,[Component] nvarchar (128) NOT NULL
,[Version] int NULL
,[Entity] nvarchar (128) NOT NULL
,[Step] nvarchar (MAX) NOT NULL
,[Description] nvarchar (MAX) NULL
,[FileId] bigint NULL
,[State] nvarchar (128) NULL
,[Success] bit NULL
,[CreatedOn] datetime
CONSTRAINT [DF_LL_Execution_CreatedOn]
DEFAULT (GETUTCDATE()) NOT NULL
,[CreatedBy] nvarchar (100)
CONSTRAINT [DF_LL_Execution_CreatedBy]
DEFAULT (SUSER_SNAME()) NOT NULL
,[ModifiedOn] datetime NULL
,[ModifiedBy] nvarchar (100) NULL
,CONSTRAINT [PK_LL_Component]
PRIMARY KEY CLUSTERED ([Id] ASC),
,CONSTRAINT [FK_LL_Component_ExecutionId]
FOREIGN KEY ([ExecutionId])
REFERENCES [LL].[Execution] ([Id])
);
GO
CREATE TRIGGER [LL].[TR_LL_Component_Update]
ON [LL].[Component]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE [LL].[Component]
SET
[ModifiedOn] = GETUTCDATE()
,[ModifiedBy] = SUSER_SNAME()
FROM
[LL].[Component]
INNER JOIN inserted
ON
inserted.[ID] = [LL].[Component].[ID];
END;
GO
[LL].[Trace]
Spalten
| Spalte | Beschreibung |
|---|---|
| Id | Die Spalte [Id] ist als IDENTITY deklariert und identifiziert einen Protokolldatensatz in dieser Tabelle. |
| ExecutionId | Über diesen Fremdschlüssel auf die Tabelle [LL].[Execution] können die Protokolldatensätze genau einer Ausführung des ETL-Prozesses zugeordnet werden. |
| ComponentId | Über diesen Fremdschlüssel auf die Tabelle [LL].[Component] können die Protokolldatensätze genau einer Ausführung einer Komponente zugeordnet werden. |
| Source | Der Text in dieser Spalt gibt an, von welchem Typ die Komponente ist, die den Protokolldatensatz geschrieben hat. Verwendete Werte sind zum Beispiel T-SQL oder SSIS. |
| Component | In dieser Spalte wird der Name der Komponente, die den Protokolldatensatz geschrieben hat, festgehalten. Bei Prozeduren ist dieses der Prozedurname, bei anderen Komponenten zum Beispiel der Dateiname der Komponente. |
| Task | Werden die gespeicherten Prozeduren für die Protokollierung in einem SSIS-Paket oder einer anderen Komponentenart verwendet, ist in dieser Spalte der Name der folgenden Task, die die Aufgabe durchführt zu speichern. |
| Entity | In dieser Spalte ist der Name der Zieltabelle, oder – allgemeiner formuliert – Entität anzugeben, die durch die Komponente bearbeitet wird. Der Name ist frei wählbar. Es empfiehlt sich, den Namen systematisch zu wählen, um eine systematische Auswertung des Protokolls zu erleichtern. |
| Step | In der Spalte [Step] ist eine kurze Beschreibung der Aufgabe der Komponente anzugeben. Auch diese Beschreibung sollte einer strengen Systematik folgen, um eine leichte Auswertung des Protokolls zu ermöglichen. |
| Description | In dieser Spalte kann eine weitergehende Beschreibung der Aufgabe der Komponente eingefügt werden. |
| FileId | Die Spalte [FileId] ist für die Verarbeitung von Dateien durch die Komponente vorgesehen. Für die Beschreibung der hier vorgestellten Vorgehensweise ist sie nicht von Bedeutung. |
| Action | In dieser Spalte ist die ausgeführte Aktion mit einem Schlagwort zu benennen. Häufig verwendete Texte sind zum Beispiel insert, update, delete, copy, … Auch hier gilt: Eine strenge Systematik bei der Verwendung der Schlagworte erleichtert die Auswertung des Protokolls. |
| AffectedRows | Sofern die ausgeführte Aktion Datensätze einfügt, aktualisiert oder löscht, ist hier die Anzahl der betroffenen Datensätze zu speichern. Die Anzahl der betroffenen Datensätze kann zum Beispiel über die T-SQL-Systemfunktion @@ROWCOUNT abgefragt werden. |
| State | Die Spalte [State] zeigt den aktuellen Status der Aktion an. Mögliche Werte sind processing, warning, success oder error. Initial wird die Aktion mit [State] gleich processing gestartet. Im Erfolgsfall wird [State] mit warning oder success aktualisiert. Im Fehlerfall wird [State] mit error aktualisiert. |
| Success | Die Spalte [Success] gibt an, ob die Aktion erfolgreich mit [State] gleich warning oder success beendet wurde. Initial wird die Aktion mit [Success] gleich 0 gestartet. Im Erfolgsfall wird [Success] mit 1 aktualisiert. |
| CreatedOn CreatedBy ModifiedOn ModifiedBy | Die Spalten [CreatedOn] und [ModifiedOn] enthalten einen UTC-Zeitstempel. Initial wird [CreatedOn] mit dem aktuellen Zeitstempel über einen Default-Wert belegt und [ModifiedOn] mit einem NULL. [ModifiedOn] wird bei der Aktualisierung des Status mit dem dann geltenden Zeitstempel aktualisiert. Die gleiche Systematik gilt für die Felder [CreatedBy] und [ModifiedBy]. Hier wird der Benutzer gespeichert, unter dem die Aktion ausgeführt wird. |
Deklaration
CREATE TABLE [LL].[Trace]
(
[Id] bigint IDENTITY (1, 1) NOT NULL
,[ExecutionId] bigint NOT NULL
,[ComponentId] bigint NOT NULL
,[Source] nvarchar(5) NOT NULL
,[Component] nvarchar(128) NOT NULL
,[Task] nvarchar(128) NULL
,[Entity] nvarchar(128) NULL
,[Step] nvarchar(MAX) NOT NULL
,[Description] nvarchar(MAX) NULL
,[FileId] bigint NULL
,[Action] nvarchar(100) NULL
,[AffectedRows] int NULL
,[State] nvarchar(100) NOT NULL
,[Success] bit NOT NULL
,[CreatedOn] datetime
CONSTRAINT [DF_LL_Trace_CreatedOn]
DEFAULT (GETUTCDATE()) NOT NULL
,[CreatedBy] nvarchar(100)
CONSTRAINT [DF_LL_Trace_CreatedBy]
DEFAULT (SUSER_SNAME()) NOT NULL
,[ModifiedOn] datetime NULL
,[ModifiedBy] nvarchar(128) NULL
,CONSTRAINT [PK_LL_Trace]
PRIMARY KEY CLUSTERED ([Id] ASC)
,CONSTRAINT [FK_LL_Trace_ComponentId]
FOREIGN KEY ([ComponentId])
REFERENCES [LL].[Component] ([Id])
,CONSTRAINT [FK_LL_Trace_ExecutionId]
FOREIGN KEY ([ExecutionId])
REFERENCES [LL].[Execution] ([Id])
);
GO
CREATE TRIGGER [LL].[TR_LL_Trace_Update]
ON [LL].[Trace]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
UPDATE [LL].[Trace]
SET
[ModifiedOn] = GETUTCDATE()
,[ModifiedBy] = SUSER_SNAME()
FROM
[LL].[Trace]
INNER JOIN inserted
ON
inserted.[Id] = [LL].[Trace].[Id];
END;
GO
[LL].[Error]
Wie oben bereits erwähnt, ist die Struktur dieser Tabelle auf die Protokollierung von Datenfehlern ausgelegt, die nicht Gegenstand dieses Artikels ist. Die Tabelle wird jedoch auch für die Protokollierung von Exceptions verwendet. Die Beschreibung der Spalten berücksichtigt nur jene Spalten, die für die Protokollierung einer Exception erforderlich sind.
Spalten
| Spalte | Beschreibung |
|---|---|
| Id | Die Spalte [Id] ist als IDENTITY deklariert und identifiziert einen Protokolldatensatz in dieser Tabelle. |
| ExecutionId | Über diesen Fremdschlüssel auf die Tabelle [LL].[Execution] können protokollierte Fehler genau einer Ausführung des ETL-Prozesses zugeordnet werden. |
| ComponentId | Über diesen Fremdschlüssel auf die Tabelle [LL].[Component] können protokollierte Fehler genau einer Ausführung einer Komponente zugeordnet werden. Der Fremdschlüssel ist jedoch nicht als Constraint implementiert, um auch die Protokollierung eines Fehlers in Abwesenheit eines Protokolldatensatzes in der Tabelle [LL].[Component] zu ermöglichen. |
| TraceId | Über diesen Fremdschlüssel auf die Tabelle [LL].[Trace] können protokollierte Fehler genau einer Aktion zugeordnet werden. Der Fremdschlüssel ist jedoch nicht als Constraint implementiert, um auch die Protokollierung eines Fehlers in Abwesenheit eines Protokolldatensatzes in der Tabelle [LL].[Trace] zu ermöglichen. |
| ErrorType | Über die Spalte [ErrorType] wird der Schweregrad des Fehlers markiert. Mögliche Werte in dieser Spalte sind: E = Error, W = Warning und I = Information. |
| Source Component Task | Für diese Spalten gelten die gleichen Ausführung wie bei der Tabelle [LL].[Trace]. |
| Step SchemaName TableName FileId ID1Value ID1ColumnName ID2Value ID2ColumnName ID3Value ID3ColumnName ErrorValue ErrorColumnName FileName | Diese Spalten sind für die Protokollierung einer Exception nicht relevant. |
| Description | In dieser Spalte ist die Fehlermeldung zu speichern. Im Falle einer Exception, die in einer gespeicherten Prozedur ausgelöst wurde, kann die Fehlermeldung über die T-SQL BuiltIn-Funktion ERROR_MESSAGE abgefragt werden. |
| Number | In dieser Spalte ist die Fehlernummer zu speichern. Im Falle einer Exception, die in einer gespeicherten Prozedur ausgelöst wurde, kann die Fehlernummer über die T-SQL BuiltIn-Funktion ERROR_NUMBER abgefragt werden. |
| Line | In dieser Spalte ist die Zeile, in der die Exception ausgelöst wurde, zu speichern. Im Falle einer Exception, die in einer gespeicherten Prozedur ausgelöst wurde, kann die Zeile über die T-SQL BuiltIn-Funktion ERROR_LINE abgefragt werden. |
| State | In dieser Spalte ist ein Status-Code zu speichern. Im Falle einer Exception, die in einer gespeicherten Prozedur ausgelöst wurde, kann der Status-Code der Exception über die T-SQL BuiltIn-Funktion ERROR_STATE abgefragt werden. |
| CreatedOn CreatedBy | Die Spalte [CreatedOn] enthält einen UTC-Zeitstempel. Initial wird [CreatedOn] mit dem aktuellen Zeitstempel über einen Default-Wert belegt. In der Spalte [CreatedBy] wird der Benutzer gespeichert, unter dem Fehler protokolliert wurde. |
Deklaration
CREATE TABLE [LL].[Error]
(
[Id] bigint IDENTITY (1, 1) NOT NULL
,[ExecutionId] bigint NOT NULL
,[ComponentId] bigint NULL
,[TraceId] bigint NULL
,[ErrorType] char (1) NOT NULL
,[Source] nvarchar(5) NOT NULL
,[Component] nvarchar(128) NOT NULL
,[TaskName] nvarchar(128) NULL
,[Entity] nvarchar(128) NULL
,[Step] nvarchar(max) NULL
,[SchemaName] nvarchar(128) NULL
,[TableName] nvarchar(128) NULL
,[FileId] bigint NULL
,[ID1Value] nvarchar(max) NULL
,[ID1ColumnName] nvarchar(128) NULL
,[ID2Value] nvarchar(max) NULL
,[ID2ColumnName] nvarchar(128) NULL
,[ID3Value] nvarchar(max) NULL
,[ID3ColumnName] nvarchar(128) NULL
,[ErrorValue] nvarchar(max) NULL
,[ErrorColumnName] nvarchar(128) NULL
,[FileName] nvarchar(128) NULL
,[Description] nvarchar(max) NULL
,[Number] int NULL
,[Line] int NULL
,[State] nvarchar(max) NULL
,[CreatedOn] datetime
CONSTRAINT [DF_LL_Error_CreatedOn]
DEFAULT (GETUTCDATE() ) NOT NULL
,[CreatedBy] nvarchar(100)
CONSTRAINT [DF_LL_Error_CreatedBy]
DEFAULT (SUSER_SNAME()) NOT NULL
,CONSTRAINT [PK_LL_Error]
PRIMARY KEY CLUSTERED ([Id] ASC)
,CONSTRAINT [FK_LL_Error_ExecutionId]
FOREIGN KEY ([ExecutionId])
REFERENCES [LL].[Execution] ([Id])
);#
GO
Datemodell
Status der Ausführung des ETL-Prozesses
Alle Tabellen für die Prozessprotokollierung verfügen über zwei Spalten [State] und [Success], in denen der aktuelle Status des ETL-Prozesses, der Ausführung einer Komponente oder einer konkreten Aktion – zum Beispiel ein INSERT, UPDTA oder DELETE – gespeichert wird.
Der aktuelle Status wird in der Spalte [State] mit den Texten processing, warning und error festgehalten. Der Erfolg wird mit [Success] = 1 gespeichert. Erst durch die Kombination von beiden Status-Werten kann der aktuelle Status des Prozesses ermittelt werden. Folgende Kombinationen von Statuswerten sind zulässig:
| State | Success | Bedeutung |
|---|---|---|
| processing | 0 | Der Prozess wird aktuell ausgeführt – oder – Der Prozess ist hart abgebrochen und die Statusfelder konnten nicht mehr aktualisiert werden. |
| warning | 0 | Der Prozess wird aktuell ausgeführt und es wurde eine Warnung in der Fehlertabelle [LL].[Error] protokolliert. – oder – Der Prozess ist hart abgebrochen und die Statusfelder konnten nicht mehr aktualisiert werden. |
| warning | 1 | Der Prozess wurde erfolgreich ausgeführt. Es wurde jedoch eine Warnung in der Fehlertabelle [LL].[Error] protokolliert. |
| success | 1 | Der Prozess wurde erfolgreich ausgeführt. |
| error | 0 | Der Prozess wurde mit einem Fehler beendet. Der Fehler wurde in der Fehlertabelle [LL].[Error] protokolliert. |
Andere Kombinationen von Statuswerten sind nicht zulässig. Die Prozeduren, die für die Prozessprotokollierung verwendet werden, lösen im Falle einer ungültigen übergebenen Kombination eine Exception aus.
Prozeduren für die Protokollierung
Für das Einfügen von Protokolldatensätzen in die oben genannten Tabellen stehen (unter anderem) die folgenden Prozeduren zur Verfügung:
| Tabelle | Prozedur |
|---|---|
| [LL].[Execution] | [LL].[spInsertExecution] [LL].[spUpdateExecution] |
| [LL].[Component] | [LL].[spUpdateComponent] [LL].[spUpdateComponent] [LL].[spUpdateComponentSuccess] [LL].[spUpdateComponentWarning] [LL].[spUpdateComponentError] |
| [LL].[Trace] | [LL].[spInsertTrace] [LL].[spUpdateTrace] [LL].[spUpdateTraceSuccess] [LL].[spUpdateTraceWarning] [LL].[spUpdateTraceError] |
| [LL].[Error] | [LL].[spInsertError] [LL].[spInsertErrorException] |
Im Wesentlichen führen diese Prozeduren ein INSERT oder UPDATE auf den Protokolltabellen aus. Die zu protokollierenden Werte werden als Parameter an die Prozeduren übergeben. Die Prozeduren prüfen die übergebenen Parameter und lösen im Fall von ungültigen Parametern eine Exception aus.
Die folgenden drei Code-Beispiele zeigen die Prozeduren [LL].[spInsertTrace], [LL].[spUpdateTrace] und [LL].[spUpdateTraceSuccess]. Die übrigen Prozeduren sind analog entwickelt. Der Code zu allen Prozeduren kann über diesen Link heruntergeladen werden.
[LL].[spInsertTrace]
-- --------------------------------------------------------------------------------
-- Parameters
-- @p_executionId AS int
-- Execution Id of the current execution
-- @p_componentId AS int
-- Each procedure call gets a unique id that allows identifying all messages
-- that will be written by the procedure call
-- @p_traceId AS int OUT
-- Returns the Id of the new row in [LL].[Trace]
-- @p_source AS nvarchar(5)
-- Type of the calling source system (SSIS, T-SQL, ...)
-- @p_component AS nvarchar(128)
-- Name of the calling SSIS-Package
-- @p_task AS nvarchar(128) = NULL
-- SSIS-Task name the log entry refers to.
-- @p_entity AS nvarchar(128) = NULL
-- Entity name the log entry refers to.
-- @p_step AS nvarchar(max)
-- Description of the task in the calling object that will be logged
-- @p_description AS nvarchar(max) = NULL
-- Additional description of the task in the calling object that will be
-- logged
-- @p_fileId AS int = NULL
-- Foreign Key to [LL].[FileList].[Id]
-- @p_action AS nvarchar(max) = NULL
-- Specifiy any action that will be logged by this procedure call like
-- Insert, Delete, Update, ...
-- @p_affectedRows AS nvarchar(max) = NULL
-- Specifiy the number of rows/objects that were inserted, deleted, updated, ...
-- @p_state AS nvarchar(100)
-- State of the current task (processing, success, error, warning)
-- @p_success AS bit
-- Specifies whether the calling procedure succeded
-- 0 = processing, warning, error
-- 1 = success
-- --------------------------------------------------------------------------------
CREATE PROCEDURE [LL].[spInsertTrace]
@p_executionId AS int
,@p_componentId AS int
,@p_traceId AS int OUT
,@p_source AS nvarchar(5)
,@p_component AS nvarchar(128)
,@p_task AS nvarchar(128)
,@p_entity AS nvarchar(128)
,@p_step AS nvarchar(max)
,@p_description AS nvarchar(max)
,@p_fileId AS bigint = NULL
,@p_action AS nvarchar(100) = NULL
,@p_affectedRows AS int
,@p_state AS nvarchar(100)
,@p_success AS bit
AS
BEGIN
SET NOCOUNT ON;
-- --------------------------------------------------------------------------------
-- Declare variables
-- --------------------------------------------------------------------------------
DECLARE @component AS nvarchar(128);
DECLARE @table AS table([Id] int);
DECLARE @message AS nvarchar(max);
-- --------------------------------------------------------------------------------
-- Initialize variables
-- --------------------------------------------------------------------------------
SET @component = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
-- --------------------------------------------------------------------------------
-- Workload
-- --------------------------------------------------------------------------------
BEGIN TRY
-- --------------------------------------------------------------------------------
-- Check parameters
-- --------------------------------------------------------------------------------
BEGIN
-- --------------------------------------------------------------------------------
-- Check @p_executionId
-- --------------------------------------------------------------------------------
IF (@p_executionId IS NULL)
BEGIN
EXEC [dbo].[spRaiseError] N'The parameter ''p_ExecutionId'' is NULL.', @component;
RETURN 0;
END;
-- --------------------------------------------------------------------------------
-- Check @p_componentId
-- --------------------------------------------------------------------------------
IF (@p_componentId IS NULL)
BEGIN
EXEC [dbo].[spRaiseError] N'The parameter ''p_ComponentId'' is NULL.', @component;
RETURN 0;
END;
-- --------------------------------------------------------------------------------
-- Check @p_source
-- --------------------------------------------------------------------------------
IF ([dbo].[fnIsNullOrEmpty](@p_source, 1) <> 0 )
BEGIN
EXEC [dbo].[spRaiseError] N'The parameter ''p_source'' is either NULL or an empty string.', @component;
RETURN 1;
END;
-- --------------------------------------------------------------------------------
-- Check @p_component
-- --------------------------------------------------------------------------------
IF ([dbo].[fnIsNullOrEmpty](@p_component, 1) <> 0 )
BEGIN
EXEC [dbo].[spRaiseError] N'The parameter ''p_component'' is either NULL or an empty string.', @component;
RETURN 1;
END;
-- --------------------------------------------------------------------------------
-- Check @p_step
-- --------------------------------------------------------------------------------
IF ([dbo].[fnIsNullOrEmpty](@p_step, 1) <> 0)
BEGIN
EXEC [dbo].[spRaiseError] N'The parameter ''p_step'' is either NULL or an empty string.', @component;
RETURN 1;
END;
-- --------------------------------------------------------------------------------
-- Check @p_state
-- --------------------------------------------------------------------------------
IF ([dbo].[fnIsNullOrEmpty](@p_state, 1) <> 0)
BEGIN
EXEC [dbo].[spRaiseError] N'The parameter ''p_state'' is either NULL or an empty string', @component;
RETURN 1;
END;
-- --------------------------------------------------------------------------------
-- Check @p_success
-- --------------------------------------------------------------------------------
IF @p_success IS NULL
BEGIN
EXEC [dbo].[spRaiseError] N'The parameter ''p_success'' is either NULL.', @component;
RETURN 1;
END;
-- --------------------------------------------------------------------------------
-- Check combination of @p_success and @p_state
-- --------------------------------------------------------------------------------
IF (@p_success = 0 AND @p_state IN ('success')) OR (@p_success = 1 AND @p_state IN ('processing', 'error'))
BEGIN
SET @message = CONCAT('Invalid state ''', @p_state, ''' for p_success = ''', CAST(@p_success AS nvarchar(100)),'''.');
EXEC [dbo].[spRaiseError] @message, @component;
RETURN 1;
END;
END;
-- --------------------------------------------------------------------------------
-- Insert trace into [LL].[Trace]
-- --------------------------------------------------------------------------------
INSERT INTO [LL].[Trace]
(
[ExecutionId]
,[ComponentId]
,[Source]
,[Component]
,[Task]
,[Entity]
,[Step]
,[Description]
,[FileId]
,[Action]
,[AffectedRows]
,[State]
,[Success]
)
OUTPUT Inserted.Id INTO @table
VALUES
(
@p_executionId
,@p_componentId
,@p_source
,@p_component
,@p_task
,@p_entity
,@p_step
,CASE WHEN @p_description IS NULL OR DATALENGTH(@p_description) = 0 THEN NULL ELSE @p_description END
,@p_fileId
,@p_action
,@p_affectedRows
,@p_state
,@p_success
);
SELECT @p_traceId = [Id] FROM @table;
RETURN 0;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
-- [LL].[spInsertTrace]
-- DECLARE @ExecutionId AS int;
-- DECLARE @ComponentId AS int;
-- DECLARE @traceId AS int;
-- DECLARE @source AS nvarchar(5);
-- DECLARE @component AS nvarchar(128);
-- DECLARE @entity AS nvarchar(128);
-- DECLARE @step AS nvarchar(max);
-- DECLARE @description AS nvarchar(max);
-- DECLARE @fileId AS bigint
-- DECLARE @action AS nvarchar(100) = NULL
-- DECLARE @affectedRows AS int
-- DECLARE @state AS nvarchar(100);
-- DECLARE @success AS bit;
--
-- SET @ExecutionId = 1;
-- SET @ComponentId = 1;
-- SET @source = 'T-SQL';
-- SET @component = 'test script';
-- SET @entity = '[LL].[spInsertTrace]';
-- SET @step = '[LL].[spInsertTrace]';
-- SET @description = 'none';
-- SET @fileId = 456;
-- SET @action = 'Insert';
-- SET @affectedRows = 55;
-- SET @state = 'processing';
-- SET @success = 0;
--
-- EXEC [LL].[spInsertTrace]
-- @p_executionId = @ExecutionId
-- ,@p_componentId = @ComponentId
-- ,@p_traceId = @traceId OUTPUT
-- ,@p_source = @source
-- ,@p_component = @component
-- ,@p_entity = @entity
-- ,@p_step = @step
-- ,@p_description = @description
-- ,@p_fileId = @fileId
-- ,@p_action = @action
-- ,@p_affectedRows = @affectedRows
-- ,@p_state = @state
-- ,@p_success = @success;
--
-- SELECT @traceId;
-- SELECT * FROM [LL].[Trace] WHERE [Id] = @traceId;
[LL].[spUpdateTrace]
-- --------------------------------------------------------------------------------
-- Parameters :
-- @p_traceId AS int
-- ID of the row in [LL].[Trace], that is to be updated.
-- @p_description AS nvarchar(max)
-- Additional description of the task in the calling object that will be logged
-- @p_action AS nvarchar(max) = NULL
-- Specifiy any action that will be logged by this procedure call like Insert, Delete, Update, ...
-- @p_affectedRows AS nvarchar(max) = NULL
-- Specifiy the number of rows/objects that were inserted, deleted, updated, ...
-- @p_state AS nvarchar(100)
-- State of the current task (processing, success, error, warning)
-- @p_success AS bit
-- Specifies the success state of the execution
-- 0 = @p_state > processing, warning, error
-- 1 = @p_state > success, warning
-- --------------------------------------------------------------------------------
ALTER PROCEDURE [LL].[spUpdateTrace]
@p_traceId AS int
,@p_description AS nvarchar(max)
,@p_action AS nvarchar(100) = NULL
,@p_affectedRows AS int
,@p_state AS nvarchar(100)
,@p_success AS bit
AS
BEGIN
SET NOCOUNT ON;
-- --------------------------------------------------------------------------------
-- Declare variables
-- --------------------------------------------------------------------------------
DECLARE @component AS nvarchar(128);
DECLARE @tempuid AS int;
DECLARE @message AS nvarchar(max);
-- --------------------------------------------------------------------------------
-- Initialize variables
-- --------------------------------------------------------------------------------
SET @component = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
-- --------------------------------------------------------------------------------
-- Workload
-- --------------------------------------------------------------------------------
BEGIN TRY
-- --------------------------------------------------------------------------------
-- Check parameters
-- --------------------------------------------------------------------------------
BEGIN
-- --------------------------------------------------------------------------------
-- Check @p_traceId
-- --------------------------------------------------------------------------------
IF (@p_traceId IS NULL)
BEGIN
EXEC [dbo].[spRaiseError] N'The parameter ''p_traceid'' is NULL.', @component;
RETURN 1;
END;
-- --------------------------------------------------------------------------------
-- Check @p_success
-- --------------------------------------------------------------------------------
IF (@p_success IS NULL)
BEGIN
SET @message = 'The parameter ''p_success'' is NULL.';
EXEC [dbo].[spRaiseError] @message, @component;
RETURN 1;
END;
-- --------------------------------------------------------------------------------
-- Check combination of @p_success and @p_state
-- --------------------------------------------------------------------------------
IF (@p_success = 0 AND @p_state IN (N'success')) OR (@p_success = 1 AND @p_state IN (N'processing', N'error'))
BEGIN
SET @message = CONCAT(N'Invalid state ''', @p_state, N''' for p_success = ''', CAST(@p_success AS nvarchar(100)),'''.');
EXEC [dbo].[spRaiseError] @message, @component;
RETURN 1;
END;
-- --------------------------------------------------------------------------------
-- Check whether an trace log does exist in [LL].[Trace] with
-- [ID] = @p_traceId
-- --------------------------------------------------------------------------------
SELECT
@tempuid = [Id]
FROM
[LL].[Trace]
WHERE
[Id] = @p_traceId;
IF (@tempuid IS NULL)
BEGIN
SET @message = N'A record with [ID] = ''' + CAST(@p_traceId AS nvarchar(max)) + N''' could not be found.';
EXEC [dbo].[spRaiseError] @message, @component;
RETURN 1;
END;
END;
-- --------------------------------------------------------------------------------
-- Update trace log in [LL].[Trace]
-- --------------------------------------------------------------------------------
UPDATE [LL].[Trace]
SET
[Description] = CASE WHEN (@p_description IS NULL OR DATALENGTH(@p_description) = 0) AND ([Description] IS NULL OR DATALENGTH([Description]) = 0) THEN NULL ELSE @p_description END
,[Action] = @p_action
,[AffectedRows] = @p_affectedRows
,[State] = @p_state
,[Success] = @p_success
WHERE
[Id] = @p_traceId;
RETURN 0;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
-- [dbo].[spUpdateTrace]
-- EXEC [LL].[spUpdateTrace] 1, N'process successfully finished', N'Insert', 123, N'success', 1;
[LL].[spUpdateTraceSuccess]
-- --------------------------------------------------------------------------------
-- Parameters :
-- @p_traceId AS int
-- ID of the row in [LL].[Trace], that is to be updated.
-- @p_description AS nvarchar(max)
-- Additional description of the task in the calling object that will be logged
-- @p_action AS nvarchar(max) = NULL
-- Specifiy any action that will be logged by this procedure call like Insert, Delete, Update, ...
-- @p_affectedRows AS nvarchar(max) = NULL
-- Specifiy the number of rows/objects that were inserted, deleted, updated, ...
-- --------------------------------------------------------------------------------
ALTER PROCEDURE [LL].[spUpdateTraceSuccess]
@p_traceId AS int
,@p_description AS nvarchar(max)
,@p_action AS nvarchar(100) = NULL
,@p_affectedRows AS int
AS
BEGIN
SET NOCOUNT ON;
EXEC [LL].[spUpdateTrace]
@p_traceId
,@p_description
,@p_action
,@p_affectedRows
,'success'
,1;
END;
-- [LL].[spUpdateTraceSuccess]
-- EXEC [LL].[spUpdateTraceSuccess] 1, 'description', 'Insert', 11;
Exception Handling
Um sicherzustellen, dass ein ETL-Prozess geordnet beendet und nicht ohne entsprechende Protokollierung hart abgebrochen wird, ist ein explizites Exception-Handling erforderlich. Geordnet bedeutet in diesem Fall, dass der Prozess eine ausgelöste Exception abfängt, ein UPDATE auf den jeweiligen Protokolldatensatz in den Tabellen [LL].[Execution], [LL].[Component] und [LL].[Trace] – sofern jeweils anwendbar – mit [Status] = error und [Success] = 0 durchführt und die Exception in der Tabelle [LL].[Error] protokolliert. Das folgende Diagramm zeigt die grundlegende Systematik des Exception-Handlings:
Erst nach erfolgter Protokollierung kann der Prozess die Exception an den Aufrufer weiterreichen, so dass der aufrufende Prozess hart abgebrochen würde. Eine Weitergabe der Exception an den Aufrufer ist jedoch nicht erforderlich, wenn der Fehler protokoliert und die relevanten Protokolldatensätze mit [Status] = error und [Success] = 0 aktualisiert wurden.
Das Exception-Handling ist mindestens auf der obersten Ebene der Ausführung eines ETL-Prozesses, der in der Tabelle [LL].[Execution] protokolliert wird, erforderlich.
Das nachfolgende Code-Beispiel zeigt ein Exception-Handling inklusive der Protokollierung in den Tabellen [LL].[Execution] und [LL].[Error] entsprechend des obigen Diagramms:
-- Execution logging variables
DECLARE @executionId AS int;
DECLARE @processName AS varchar(max);
DECLARE @version AS int;
-- Error logging variables
DECLARE @componentId AS int;
DECLARE @traceId AS int;
DECLARE @source AS nvarchar(5);
DECLARE @component AS nvarchar(128);
DECLARE @task AS nvarchar(128);
DECLARE @entity AS nvarchar(128);
DECLARE @step AS nvarchar(128);
DECLARE @state AS nvarchar(128);
DECLARE @success AS bit;
-- Exception variables
DECLARE @error_message AS nvarchar(max);
DECLARE @error_number AS int;
DECLARE @error_line AS int;
DECLARE @error_state AS nvarchar(max);
BEGIN TRY
-- Initialize execution logging variables
SET @processName = 'Name of ETL-Process';
SET @version = 123;
-- Initialize error variables
SET @componentId = NULL;
SET @traceId = NULL;
SET @source = 'sql';
SET @component = 'Procedure Name';
SET @task = NULL;
SET @entity = 'Any Entity';
SET @step = 'Do something';
EXEC [LL].[spInsertExecution] @executionId OUTPUT, @processName, @version;
-- Do something
RAISERROR('Any Exception', 15, 1);
SET @state = 'success';
SET @success = 1;
EXEC [LL].[spUpdateExecution] @executionId, @state, @success;
END TRY
BEGIN CATCH
SET @error_message = ERROR_MESSAGE();
SET @error_number = ERROR_NUMBER();
SET @error_line = ERROR_LINE();
SET @error_state = ERROR_STATE();
SET @state = 'error';
SET @success = 0;
IF @executionId IS NOT NULL
BEGIN
EXEC [LL].[spInsertErrorException] @executionId, @componentId, @traceId, @source, @component, @task, @entity, @step, @error_number, @error_message, @error_line, @error_state;
EXEC [LL].[spUpdateExecution] @executionId, @state, @success;
END;
THROW;
END CATCH
Beispiel für die Protokollierung und das Exception-Handling in einem ETL-Prozess
Nach diesen Erläuterungen zu den Grundzügen des Exception-Handlings zeigt das folgende Diagramm das Exception-Handling eines einfachen, kompakten aber vollständigen ETL-Prozesses:
Dieses Beispiel zeigt die Protokollierung eines ETL-Prozesses der ausschließlich über gespeicherten Prozeduren entwickelt wurde und aus 5 gespeicherten Prozeduren besteht. Die Einstiegsprozedur [T2].[spETLProcess] ruft die beiden Prozeduren [T2].[spDoSomething_1] und [T2].[spDoSomething_2] auf. Die erste simuliert und protokolliert jeweils ein INSERT-, UPDATE- und DELETE-Statement, während die zweite Prozedur zwei weitere Prozeduren [T2].[spDoSomething_1_1] und [T2].[spDoSomething_1_2] ausführt. Diese Prozeduren simulieren und protokollieren ebenfalls jeweils ein INSERT-, UPDATE- und DELETE-Statement. Während der Ausführung des DELETE-Statements in der zweiten Prozedur, wird eine Exception ausgelöst, die zu einer geordneten Beendigung des ETL-Prozesses führt.
Der Beispiel-Code für die Erstellung einer Datenbank mit den benötigten gespeicherten Prozeduren für die Protokollierung und die in dem Diagramm genannten Prozeduren des ETL-Prozesses aus dem Schema T2 kann über diesen Link heruntergeladen werden.
Da die Abbildung aller Prozeduren zu Wiederholungen führen würde, werden hier nur die Einstiegsprozedur [T2].[spETLProcess] und die Prozedur [T2].[spDoSomething_2_2] abgebildet.
[T2].[spETLProcess]
CREATE PROCEDURE [T2].[spETLProcess] AS BEGIN SET NOCOUNT ON; -- -------------------------------------------------------------------------------- -- Declare variables -- -------------------------------------------------------------------------------- -- Error Variables DECLARE @error_message AS nvarchar(max); DECLARE @error_number AS int; DECLARE @error_line AS int; DECLARE @error_state AS nvarchar(max); -- Logging Variables DECLARE @component AS nvarchar(128); DECLARE @task AS nvarchar(128); DECLARE @schema AS nvarchar(128); DECLARE @table AS nvarchar(128); DECLARE @source AS nvarchar(5); DECLARE @step AS nvarchar(max); DECLARE @entity AS nvarchar(max); DECLARE @message AS nvarchar(max); DECLARE @traceId AS int; DECLARE @componentId AS int; DECLARE @executionId AS int; DECLARE @description AS nvarchar(max); DECLARE @affectedrows AS int; DECLARE @action AS varchar(100); DECLARE @state AS varchar(100); DECLARE @success AS int; -- -------------------------------------------------------------------------------- -- Initialize variables -- -------------------------------------------------------------------------------- -- Logging SET @message = NULL; SET @description = NULL; SET @affectedrows = 0; SET @component = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); SET @source = N'T-SQL'; SET @entity = N'[].[ ]'; -- -------------------------------------------------------------------------------- -- Workload -- -------------------------------------------------------------------------------- BEGIN TRY -- -------------------------------------------------------------------------------- -- Check parameters -- -------------------------------------------------------------------------------- EXEC [LL].[spInsertExecution] @executionId OUTPUT, N'ETL process', 123; -- -------------------------------------------------------------------------------- -- Insert component log -- -------------------------------------------------------------------------------- SET @step = N'Orchestrate ETL process'; SET @description = ''; EXEC [LL].[spInsertComponent] @executionId, @componentId OUTPUT, @source, @component, NULL, @entity, @step, @description; -- -------------------------------------------------------------------------------- -- Execute Procedure [T2].[spDoSomething_1] -- -------------------------------------------------------------------------------- SET @task = NULL; SET @step = N'Execute [T2].[spDoSomething_1]'; SET @action = 'execute'; SET @description = NULL; SET @state = 'processing'; SET @success = 0; EXEC [LL].[spInsertTrace] @executionId, @componentId, @traceId OUTPUT, @source, @component, @task, @entity, @step, @description, NULL, @action, NULL, @state, @success; EXEC [T2].[spDoSomething_1] @executionId; EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, @@ROWCOUNT; -- -------------------------------------------------------------------------------- -- Execute Procedure [T2].[spDoSomething_2] -- -------------------------------------------------------------------------------- SET @task = NULL; SET @step = N'Execute [T2].[spDoSomething_2]'; SET @action = 'execute'; SET @description = NULL; SET @state = 'processing'; SET @success = 0; EXEC [LL].[spInsertTrace] @executionId, @componentId, @traceId OUTPUT, @source, @component, @task, @entity, @step, @description, NULL, @action, NULL, @state, @success; EXEC [T2].[spDoSomething_2] @executionId; EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, @@ROWCOUNT; -- -------------------------------------------------------------------------------- -- Execute another procedure -- -------------------------------------------------------------------------------- -- ... -- -------------------------------------------------------------------------------- -- Update component log -- -------------------------------------------------------------------------------- EXEC [LL].[spUpdateComponentSuccess] @componentId, @description; -- -------------------------------------------------------------------------------- -- Update execution log -- -------------------------------------------------------------------------------- SET @state = 'success'; SET @success = 1; EXEC [LL].[spUpdateExecution] @executionId, @state, @success; END TRY BEGIN CATCH SET @error_message = ERROR_MESSAGE(); SET @error_number = ERROR_NUMBER(); SET @error_line = ERROR_LINE(); SET @error_state = ERROR_STATE(); IF @executionId IS NOT NULL BEGIN EXEC [LL].[spInsertErrorException] @executionId, @componentId, @traceId, @source, @component, NULL, NULL, @step, @error_number, @error_message, @error_line, @error_state; IF @traceId IS NOT NULL EXEC [LL].[spUpdateTraceError] @traceId, @description; IF @componentId IS NOT NULL EXEC [LL].[spUpdateComponentError] @componentId, @description; SET @state = 'error'; SET @success = 0; EXEC [LL].[spUpdateExecution] @executionId, @state, @success; END; END CATCH; END;
[T2].[spDoSomething_2_2]
CREATE PROCEDURE [T2].[spDoSomething_2_2]
(
@p_executionId AS int
)
AS
BEGIN
SET NOCOUNT ON;
-- --------------------------------------------------------------------------------
-- Declare variables
-- --------------------------------------------------------------------------------
-- Error Variables
DECLARE @error_message AS nvarchar(max);
DECLARE @error_number AS int;
DECLARE @error_line AS int;
DECLARE @error_state AS nvarchar(max);
-- Logging Variables
DECLARE @component AS nvarchar(128);
DECLARE @task AS nvarchar(128);
DECLARE @schema AS nvarchar(128);
DECLARE @table AS nvarchar(128);
DECLARE @source AS nvarchar(5);
DECLARE @step AS nvarchar(max);
DECLARE @entity AS nvarchar(max);
DECLARE @message AS nvarchar(max);
DECLARE @traceId AS int;
DECLARE @componentId AS int;
DECLARE @description AS nvarchar(max);
DECLARE @affectedrows AS int;
DECLARE @action AS varchar(100);
DECLARE @state AS varchar(100);
DECLARE @success AS int;
-- --------------------------------------------------------------------------------
-- Initialize variables
-- --------------------------------------------------------------------------------
-- Logging
SET @message = NULL;
SET @description = NULL;
SET @affectedrows = 0;
SET @component = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
SET @source = N'T-SQL';
SET @entity = N'[].[]';
-- --------------------------------------------------------------------------------
-- Workload
-- --------------------------------------------------------------------------------
BEGIN TRY
-- --------------------------------------------------------------------------------
-- Check parameters
-- --------------------------------------------------------------------------------
BEGIN
IF (@p_executionId IS NULL)
BEGIN
SET @message = N'The parameter ''p_executionId'' is NULL.';
EXEC [dbo].[spRaiseError] @message, @component;
RETURN -1;
END;
END;
-- --------------------------------------------------------------------------------
-- Insert component log
-- --------------------------------------------------------------------------------
SET @step = N'Do something';
SET @description = '';
EXEC [LL].[spInsertComponent] @p_executionId, @componentId OUTPUT, @source, @component, NULL, @entity, @step, @description;
-- --------------------------------------------------------------------------------
-- Insert data
-- --------------------------------------------------------------------------------
SET @task = NULL;
SET @step = N'Insert data';
SET @action = 'insert';
SET @description = NULL;
SET @state = 'processing';
SET @success = 0;
EXEC [LL].[spInsertTrace] @p_executionId, @componentId, @traceId OUTPUT, @source, @component, @task, @entity, @step, @description, NULL, @action, NULL, @state, @success;
-- Insert here a SQL Statement that inserts data into a table
EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, @@ROWCOUNT;
-- --------------------------------------------------------------------------------
-- Update data
-- --------------------------------------------------------------------------------
SET @task = NULL;
SET @step = N'Update data';
SET @action = 'update';
SET @description = NULL;
SET @state = 'processing';
SET @success = 0;
EXEC [LL].[spInsertTrace] @p_executionId, @componentId, @traceId OUTPUT, @source, @component, @task, @entity, @step, @description, NULL, @action, NULL, @state, @success;
-- Insert here a SQL Statement that updates data a table
EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, @@ROWCOUNT;
-- --------------------------------------------------------------------------------
-- Delete data
-- --------------------------------------------------------------------------------
SET @task = NULL;
SET @step = N'Delete data';
SET @action = 'delete';
SET @description = NULL;
SET @state = 'processing';
SET @success = 0;
EXEC [LL].[spInsertTrace] @p_executionId, @componentId, @traceId OUTPUT, @source, @component, @task, @entity, @step, @description, NULL, @action, NULL, @state, @success;
-- Insert here a SQL Statement that deletes data from a table
RAISERROR('Exception in [T2].[spDoSomething_2_2]', 15, 1);
EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, @@ROWCOUNT;
-- --------------------------------------------------------------------------------
-- Update component log
-- --------------------------------------------------------------------------------
EXEC [LL].[spUpdateComponentSuccess] @componentId, @description;
END TRY
BEGIN CATCH
SET @error_message = ERROR_MESSAGE();
SET @error_number = ERROR_NUMBER();
SET @error_line = ERROR_LINE();
SET @error_state = ERROR_STATE();
-- Write in Logging
IF @p_executionId IS NOT NULL
BEGIN
EXEC [LL].[spInsertErrorException] @p_executionId, @componentId, @traceId, @source, @component, NULL, NULL, @step, @error_number, @error_message, @error_line, @error_state;
IF @traceId IS NOT NULL EXEC [LL].[spUpdateTraceError] @traceId, @description;
IF @componentId IS NOT NULL EXEC [LL].[spUpdateComponentError] @componentId, @description;
END;
THROW;
END CATCH;
END;
Das folgende Skript enthält die Anweisung für die Ausführung des ETL-Prozesses. Um ein kompaktes Prozessprotokoll zu erhalten, werden vorher die Protokolltabellen geleert. Die abschließenden SELECT-Statement produzieren das eingangs gezeigte Ergebnis.
-- -------------------------------------------------------------------------------- -- 01: Delete data from logging tables -- -------------------------------------------------------------------------------- TRUNCATE TABLE [LL].[Error] TRUNCATE TABLE [LL].[Trace] DELETE FROM [LL].[Component] DELETE FROM [LL].[Execution] DBCC CHECKIDENT (N'[LL].[Component]', RESEED, 0); DBCC CHECKIDENT (N'[LL].[Execution]', RESEED, 0); -- -------------------------------------------------------------------------------- -- 02: Execute ETL Process -- -------------------------------------------------------------------------------- EXEC [T2].[spETLProcess]; -- -------------------------------------------------------------------------------- -- 03: Query logging tables -- -------------------------------------------------------------------------------- SELECT * FROM [LL].[Execution]; SELECT * FROM [LL].[Component]; SELECT * FROM [LL].[Trace];
Fazit
ETL-Prozesse sind Daten getriebene Prozesse. Werden Daten geliefert, die nicht erwartet werden, dann ist die Wahrscheinlichkeit hoch, dass in dem Zielsystem entweder nicht alle Daten oder sogar falsche Daten in das Zielsystem geschrieben werden. Was in diesem Artikel nur beiläufig erwähnt wird ist, dass auf der untersten Ebene der Protokollierung auch die Anzahl der betroffenen Datensätze protokolliert wird, bzw. protokolliert werden kann. Die Kenntnis der Anzahl der erwarteten und verarbeiteten Datensätze ist ein gutes Indiz für den Erfolg oder Nicht-Erfolg eines ETL-Prozesses.
Damit unterstützt diese Vorgehensweise die Entwicklung von robusten ETL-Prozessen, die eine Beurteilung auch der Datenqualität sicherstellt.
Was in der Software-Entwicklung ein Muss ist, wird bei der Entwicklung von ETL-Prozessen gerne vernachlässigt: ein explizites Exception-Handling. Das vorgestellte Exception-Handling stellt hier zunächst sicher, dass ein Prozess im Fehlerfall geordnet beendet wird. Wann aber liegt ein Fehler vor? Und muss ein Fehler tatsächlich immer zum Abbruch des Prozesses führen? In Verbindung mit der Kenntnis über die erwartete und tatsächlich verarbeitete Anzahl Datensätze kann eine echte Behandlung von Fehlern implementiert werden.
Die hier vorgestellte Vorgehensweise ist quasi eine Einladung sich mehr mit den Daten und dem erwarteten Ergebnis zu befassen. Das Protokoll und insbesondere die Kenntnis über die verarbeiteten Datensätze unterstützen bereits während der Entwicklung des ETL-Prozesses den Entwickler, die Richtigkeit der Entwicklung beurteilen zu können.
Die vorgestellten Prozeduren stellen lediglich einen Werkzeugkasten für die Protokollierung bereit. Bei richtiger Anwendung produziert es ein les- und auswertbares Protokoll, das die Beurteilung der Korrektheit der Ausführung des ETL-Prozesses und der verarbeiteten Daten ermöglicht.