Surrogate keys v DWH - 11. díl

V tomto článku se zabývám konceptem surrogate keys v datovém skladu. Definici, výhody oproti přirozeným klíčům a metody generování.

Surrogate key je unikátní identifikátor záznamu (id). Je uměle generovaný a nemá žádný vztah k obsahu. Oproti tomu přirozený (složený) klíč má vazbu na existující data. Na generovávání surrogate key existují různé techniky jako auto inkrement, sekvence, UUID, hash, a pod.

Generování surrogate key

Auto-Increment,Identity

Většina databází umožňuje generování pomocí auto-incrementu nebo identity automaticky. Na pozadí bude většinou použita sekvence. V Postgres lze použít GENERATED AS IDENTITY.

CREATE TABLE SURROGATE_KEY_AUTO (
    ID	INT 		GENERATED ALWAYS AS IDENTITY,
	VAL	VARCHAR(10)
);

Možnost ALWAYS zajistí to, že pokud zkusím do tabulky vložit ID, tak se mi to nepovede. Sloupec je pouze generovaný automaticky a nemůžu ho nijak ovlivnit. ALWAYS lze nahradit hodnotou DEFAULT, která mi umožní INSERT i UPDATE hodnot v ID.

Sekvence

Teď se podíváme do pozadí předchozího způsobu. K inkrementálnímu generování čísel slouží objekt typu SEQUENCE. Při vytváření sekvence řeknu jakého má být typu (jen rozsah čísel), kde má začínat a jak velký je inkrement. Nastavit lze také zda je sekvence cyklická, minimální a maximální hodnotu. Následně se jen použije nextval() na získání hodnoty ze sekvence a ta se použije při INSERTu do tabulky.

/* Vytvoření sekvence */
CREATE SEQUENCE SURROGATE_KEY_SEQUENCE AS INT
INCREMENT 1
START 1;
 
/* Dotaz na následující hodnotu */
SELECT NEXTVAL('SURROGATE_KEY_SEQUENCE');

UUID surrogate key

UUID mají několik způsobů generování a generují se tak, aby byly unikátní na světě. Jde o 32 hexadecimálních číslic, oddělené pomlčkami do pěti skupin: 8-4-4-4-12 znaků. Například: 2fae6597-e17b-4c68-a767-1d574d9d7679.

Verze 1 používá kombinaci času a MAC adresy, verze 4 je náhodná, verze 3 a 5 používají hash.

/* Nejdříve je nutné nainstalovat rozšíření pro podporu UUID */
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
/* Vytvoření tabulky s UUID generováním */
DROP TABLE SURROGATE_KEY_UUID;
CREATE TABLE SURROGATE_KEY_UUID (
    ID uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
	VAL VARCHAR(10)
);

Business Key Hashing

Tato varianta vychází z přirozeného klíče v datech. Na daný klíč se použije hashovací funkce. Toto není čístý surrogate key, ale uvádím sem tuto možnost pro úplnost.

CREATE TABLE SURROGATE_KEY_HASH (
    ID VARCHAR(255) PRIMARY KEY,
	BUSINESS_KEY VARCHAR(10)
);
 
INSERT INTO SURROGATE_KEY_HASH (ID, BUSINESS_KEY) VALUES (sha256('KOKOS'), 'KOKOS')