Rafał Gogłoza

Problem

Usuń dane z tabeli bez DELETE. Użytkownik nie chce widzieć danych starszych niż 30 dni.

Tło biznesowe

Do tabeli faktów masz już załadowane 2 lata danych. Biznes dochodzi do wniosku, że z nie sięga tak daleko w historię. Nie są im te dane potrzebne. Albo na potrzeby zgodności z audytem (audit complience) powinny zostać usunięte.

Tło techniczne

Użycie polecenia DELETE nie wchodzi w grę, ponieważ:

  • Nie jest efektywne
  • Niektóre silniki bigdata nie wspierają operacji DELETE. Do data laka możesz dołączać dane. Usuwanie trzeba zaimplementować w inny sposób.

To jak zaimplementować usuwanie bez DELETE?

Przykładowe dane

Wyobraź sobie, że w hurtowni danych masz tabelę f_order:

dwh_id snapshot_date order_id order_status gross_amount ... dwh_deleted_ind
1 2020-01-01 20901 10 100 ... N
2 2020-01-02 20901 10 100 ... N
3 2020-01-03 20901 15 100 ... N
... ... ... ... ... ... ...
700 2021-03-01 20901 90 110 ... N
701 2021-03-02 20901 90 110 ... N
... ... ... ... ... ... ...
730 2021-03-30 20901 90 110 ... N

W cyklu dziennym dane dla zamówień są tylko dodawane.
Kolejne dni to kolejne wpisy w tabeli. Liczba zamówień rośnie, rozmiar tabeli również.

Dla potrzeb tego przykładu załóżmy, że mamy 30 marca 2021.

Proszę, nie skupiaj się na składni. Jest tu mieszanka z Netezza, SQL Server, Oracle i Impala. Żeby pokazać różne możliwości rozwiązania tematu.

Logiczny DELETE - ujęcie pierwsze

Logiczny DELETE to nic innego jak ustawienie flagi dwh_deleted_ind na 'Y' dla rekordów z poprzedniego roku.

Składnia UPDATE jest dość prosta:

UPDATE f_order 
SET dwh_deleted_ind = 'Y'
WHERE snapshot_date < date_add(day, -30, getdate());

W wyniku dostaniesz taką tabelę:

dwh_id snapshot_date order_id order_status gross_amount ... dwh_deleted_ind
1 2020-01-01 20901 10 100 ... Y
2 2020-01-02 20901 10 100 ... Y
3 2020-01-03 20901 15 100 ... Y
... ... ... ... ... ... ...
700 2021-03-01 20901 90 110 ... N
701 2021-03-02 20901 90 110 ... N
... ... ... ... ... ... ...
730 2021-03-30 20901 90 110 ... N

Wszystkim wierszom starszym niż marzec flaga dwh_deleted_ind została zmieniona na Y.

Niestety UPDATE może nie być efektywnym rozwiązaniem. Może zająć dużo czasu, pochłonąć wiele zasobów. Dodatkowo, też może nie być obsługiwany przez motor bazy danych, który używasz.

Logiczny DELETE - ujęcie drugie

Wyobraź sobie, że dodatkowo masz jeszcze jedną tabelę.

d_snapshot

dwh_id snapshot_date ... dwh_deleted_ind
1 2019-01-01 ... Y
2 2019-01-02 ... Y
... ... ... ...
1000 2021-03-01 ... N
1001 2021-03-02 ... N
... ... ... ...
1030 2021-03-30 ... N

W warstwie gdzie eksponujesz dane dla użytkowników łączysz te dwie tabele i robisz filtrowanie. Dla użytkowników, widoczne są tylko rekordy spełniające kryteria.

CREATE OR REPLACE VIEW v_f_order AS
SELECT a.* FROM f_order a
INNER JOIN d_snapshot b ON (a.snapshot_date = b.snapshot_date and b.dwh_deleted_ind = 'N');

Tabela d_snapshot jest niewielka. Zawiera tylko dane snapshotów. Łączenie dwóch tabel też powinno być efektywne. Dodatkowo tabela d_snapshot wymaga codziennych aktualizacji ale aktualizujesz tam tylko jeden wiersz.

Niestety przeszkodą może być jeżeli nie możesz stworzyć dodatkowej struktury w postaci widoku.

CREATE, TRUNCATE, INSERT i DROP

Połączenie CREATE, TRUNCATE, INSERT i DROP i stworzenie jednego skryptu może przynieść wymagany rezultat.
Wyobraź sobie taką sekwencję:

  1. Tworzenie tabeli pomocniczej tylko z danymi, które będziesz chciał mieć w tabeli docelowej.
CREATE TABLE aux_f_order AS 
SELECT * FROM f_order
WHERE snapshot_date >= date_add(day, -30, getdate());
  1. Usuwanie danych z tabeli docelowej
TRUNCATE TABLE f_order;
  1. Ponowne wstawianie do tabeli docelowej, już tylko danych spełniających kryteria
INSERT INTO f_order
SELECT * FROM aux_f_order;
  1. Sprzątamy po sobie:
DROP TABLE aux_f_order

Wadą tego podejścia jest to, że przez pewien czas w tabeli docelowej nie masz danych.
Gdy coś pójdzie nie tak z insertem wtedy masz wielki problem. Dodatkowo do rozważenia co zrobić z indexami na tabeli docelowej (jeżeli takie posiadasz). Jako optymalizacje tego elementu, możesz wstawiać danych do tabeli w pominięciem logów.

CREATE, DROP i RENAME

Nawet prościej można osiągnąć to samo bez ponownego wstawiania.

  1. Tworzenie tabeli pomocniczej:
CREATE TABLE aux_f_order AS 
SELECT * FROM f_order
WHERE snapshot_date >= date_add(day, -30, getdate());
  1. Usuwanie tabeli docelowej:
DROP TABLE f_order;
  1. Zmiana nazwy tabeli pomocniczej na tabelę docelową
RENAME TABLE aux_f_order TO f_order;

W silnikach takich jak Netezza pamiętaj po takiej operacji policzyć statystyki na tabeli f_order.

DROP PARTITION

Jak to działa?

Przygotowujesz strukturę tabeli i każde dzienne ładowanie wstawiasz do jednej partycji. Tym zarządza silnik bazy danych, więc jeżeli stworzysz odpowiednio strukturę tabeli nie musisz się martwić o wstawianie danych.

Potem w cyklu dziennym wykonujesz ładowanie niepotrzebnej partycji:

To składnia, której użyłbyś na Oraclu:

ALTER TABLE f_order DROP PARTITION part_snap_20210228;

Niestety usuwanie partycji możliwe jest tylko w niektórych silnikach baz danych. Niektóre nie wspierają partycji, tak jak na przykład Netezza.
Możesz też rozwarzyć czy nie tworzyć partycji tygodniowych albo miesięcznych. I wtedy opowiednio usuwać niepotrzebne partycje.

Impala - INSERT OVERWRITE

Impala udostępnia inną ciekawą możliwość: INSERT OVERWRITE.

Możesz na to rozwiązanie patrzeć też jak na TRUNCATE / INSERT.

Co ciekawe możesz to zapytanie uruchomić na jednej tabeli i wykonać operację czyszczenia bez potrzeby tworzenia dodatkowych struktur.

INSERT OVERWRITE f_order
SELECT * FROM f_order
WHERE snapshot_date >= date_add(day, -30, getdate());

Po tej operacji możesz swobodnie używać danych w tabeli. Spełniają już wszystkie wymagania. Nie ma ich fizycznie w tabeli, więc możesz udostępniać ją dla innych konsumentów.

Warto rozważyć

OK, mam nadzieję, że powyżej znalazłeś odpowiedź na swój problem.
Pytania pomocnicze, które pomogą dodać kontekstu albo wykluczyć niektóre opcje:

  1. Jak często ma być uruchamiany algorytm czyszczenia danych?
    a. W cyklu dziennym?
    b. Tygodniowym
    c. Miesięcznym
  2. W jaki sposób klienci dostają się do danych?
    a. Odpytują bezpośrednio tabelę faktów?
    b. Mają udostępnione widoki, które wystawiają dane?
  3. Czy istnieje potrzeba fizycznego usuwania danych czy usuwanie logiczne wystarczy?
  4. Czy istnieje inny sposób, żeby to zrobić?

Sprawdź czy zapamiętałeś

  1. Dlaczego użycie DELETE przy usuwaniu danych z wielkiej tabeli to nie najlepszy pomysł?
  2. Co to jest logiczny delete?
  3. Jak zaimplementować fizyczny delete?