12.05.2016

Deltabewirtschaftung beim Datentyp „Timestamp“

Technical Value

In einem aktuellen Projekt galt es, große Datenmengen zu laden, durch die es theoretisch in der gesamten Tabelle zu Änderungen kommen konnte. Eine tägliche Komplettbewirtschaftung der Faktentabelle war aufgrund der Masse an Daten ausgeschlossen. Ebenso wenig war eine Abgrenzung über ein Datum - wie zum Beispiel „das letzte halbe Jahr neu laden“ - möglich. Infolgedessen konnte die übliche Deltabewirtschaftung nicht vorgenommen werden. Die Lösung hat indes ein Timestamp in der Quelltabelle gebracht.

Vorteile und Nachteile bei der Deltabewirtschaftung

Bei einem Timestamp handelt es sich nicht um einen echten Zeitstempel, sondern eher um ein Row- bzw. Zeilenversion. In der MSDN werden diese auch gleich gesetzt. Der Datentyp „Timestamp“ oder „Rowversion“ ist zwar laut Microsoft abgekündigt, allerdings wird dies erfahrungsgemäß nicht allzu bald erfolgen. Intern wird der Timestamp als "binary(8)" bzw. "varbinary(8)" gespeichert. Ein Vorteil des Timestamps ist, dass bei jeder Änderung die betreffende Spalte automatisch aktualisiert wird und eindeutig ist. Dadurch lassen sich Änderungen für eine Deltabewirtschaftung sehr leicht umsetzen. Als Nachteil erweist sich bei dieser Form der Deltabewirtschaftung die Verwendung im SSIS. Hier wird der Datentyp ohne Konvertierungen als "DB_Bytes" interpretiert. Diesen kann man denkbar schlecht vergleichen. Bei der Verwendung in Queries muss der Datentyp in einen String (varchar) konvertiert und ohne "`" verwendet werden. Darüber hinaus macht es meist keinen Sinn, in den Quelltabellen nach einer solchen Spalte zu sortieren oder gar zu partitionieren, wie es üblicherweise bei monatsweise getrennten Daten der Fall ist.

Daten selektieren, Mehrfachbeladung vermeiden

Der folgende Ansatz beschreibt, wie sich die Daten dennoch selektieren und Mehrfach- oder Doppelbeladung einzelner Datensätze bei etwaigen Abbrüchen vermeiden lassen. In einem ersten Schritt haben wir in unserem Paket einen SQL-Script-Task eingebaut, der uns den letzten geladenen Timestamp aus einer Steuerungstabelle holt: Deltabewirtschaftung mit Timestamp: SQL-Task-Script für den letzten geladenen Timestamp.   Um hier immer einen gültigen Wert zu haben, wird mit dem kleinstmöglichen Timestamp vereint:

  1. SELECT TOP 1  deltavalue FROM (
  2. SELECT [deltavalue]
  3. FROM   [Control].[tabledeltavalues]
  4. WHERE  [tablename] = '[dbo].[MeineDeltaTabelle]'
  5. UNION
  6. SELECT '0x0000000000000001')ORDER BY 1 DESC

  Im zweiten Schritt löscht man in der Zieltabelle alle Werte, die größer als der zuletzt gespeicherten Timestamp sind: Löschen aller Werte, die größer als der zuletzt gespeicherte Timestamp sind. Auf diese Weise wird eine Mehrfachbeladung einzelner Datensätze vermieden. Wer häufig Abbrüche im Paket hat, der sollte sich im Übrigen Gedanken über die Kompressionmethode der Zieltabelle machen.   Bei der Selektion der Daten für die Beladung ist dann die entsprechende Where-Bedingung hinzuzufügen: WHERE  zeitstempel > "mintimestamp"   Zuletzt muss nur noch der zuletzt gelesene Timestamp weggeschrieben werden:

  1. MERGE [Control].[tabledeltavalues] AS target
  2. using (SELECT '[dbo].[MeineDeltaTabelle]'
  3. AS
  4. tablename,
  5. CONVERT(NVARCHAR (50), Cast(Max (zeilenversion) AS VARBINARY(8)),1) AS Deltavalue
  6. FROM   [dbo].[meinedeltatabelle]) AS Source
  7. ON target.tablename = source.tablename
  8. WHEN matched THEN
  9. UPDATE SET target.deltavalue = source.deltavalue
  10. WHEN NOT matched BY target THEN
  11. INSERT (tablename,
  12. deltavalue )
  13. VALUES ('[dbo].[MeineDeltaTabelle]',
  14.            source.deltavalue );

 

Kommentare

Jörg Menker
Mi, 15.06.2016 - 16:53

Nette Idee, aber anstelle des Unions reicht auch ein min(Timestamp), bei dem man den NULL-Fall mit ISNULL() abfängt. Und theoretisch ist Union all besser (aber bei einem Satz...). Das Order by ist aber überflüssig, denn Union impliziert ein order by.
Guter Blogbeitrag!

A Förster
Alexander
Förster
Mi, 15.06.2016 - 18:48

Hallo Jörg,
vielen Dank erst einmal für dein aufmerksames lesen und dein Lob. Die Idee mit dem IsNull hatte ich in meinen ersten Anläufen auch. Wenn allerdings noch kein Datensatz in der Tabelle ist bekomme ich keine Zeile zurück, dadurch funktioniert in diesem Fall die Lösung mit IsNull dann leider nicht. Das Order By mache ich absteigend da ich immer den höchsten Wert oben haben möchte, dies kann sicherlich entfallen, allerdings möchte ich da nichts dem Zufall oder dem SQL Server überlassen.

Lieben Gruß

Alexander Förster

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.

Klartext

  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden

Unsere Website benutzt Cookies, um bestmögliche Funktionalitäten bieten zu können. Durch die Nutzung unserer Website, erklären Sie sich mit der Verwendung von Cookies einverstanden. In unserer Datenschutzerklärung finden Sie unter §5 Informationen und Links, wie Sie diesen Maßnahmen jederzeit widersprechen können.

Datenschutzerklärung