Adattárház datavault töltés alapok

1. Data Vault alapok röviden

A Data Vault egy adattárház-modellezési módszertan, amelyet arra terveztek, hogy rugalmas, skálázható és auditálható legyen. Három fő építőeleme van:

HUB: Az üzleti kulcsok (business keys) tárolására szolgál, amelyek egyedileg azonosítanak egy entitást (pl. ügyfél, szerződés).
LINK: Az entitások közötti kapcsolatokat modellezi (pl. ügyfél és szerződés közötti kapcsolat).
SAT (Satellite): Az entitásokhoz vagy kapcsolatokhoz tartozó leíró adatokat (attribútumokat) tárolja, időbélyeggel együtt, hogy a változásokat követni lehessen.

2. Forrásadatok és példa

Tegyük fel, hogy a következő forrás tábláink vannak:

Ügyfél (Customer): customer_id, name, birth_date, address
Szerződés (Contract): contract_id, customer_id, start_date, amount
Tranzakció (Transaction): transaction_id, contract_id, date, amount
Partner (Partner): partner_id, name, type

Célunk ezek betöltése a Data Vault struktúrába, majd az adatpiacra történő továbbítás.

3. Data Vault modellezés és betöltési logika

HUB táblák

A HUB táblák az üzleti kulcsokat tartalmazzák, és nem tárolnak leíró adatokat. Minden entitáshoz külön HUB-ot hozunk létre. A forrás rendszeri kulcsok mellet csak technikai mezőket tartalmaz.

HUB_Customer:
- customer_hub_key (surrogate key, pl. hash(customer_id))
- customer_id (üzleti kulcs)
- load_dts (betöltés időpontja)
- record_source (forrásrendszer neve)
- Példa sor: H001, C123, 2025-03-24 10:00:00, ERP
HUB_Contract:
- contract_hub_key, contract_id, load_dts, record_source
- Példa sor: H002, CTR001, 2025-03-24 10:00:00, CRM
HUB_Transaction:
- transaction_hub_key, transaction_id, load_dts, record_source
- Példa sor: H003, TXN001, 2025-03-24 10:00:00, TXN_SYS
HUB_Partner:
- partner_hub_key, partner_id, load_dts, record_source
- Példa sor: H004, P001, 2025-03-24 10:00:00, PARTNER_SYS

Betöltési logika: Ellenőrizzük, hogy az üzleti kulcs már létezik-e a HUB-ban. Ha nem, új rekordot szúrunk be a hash-elt kulccsal és az aktuális betöltési idővel.

LINK táblák

A LINK táblák a HUB-ok közötti kapcsolatokat tárolják.

LINK_Customer_Contract:
- link_key (surrogate key, pl. hash(customer_hub_key + contract_hub_key))
- customer_hub_key
- contract_hub_key
- load_dts
- record_source
- Példa sor: L001, H001, H002, 2025-03-24 10:00:00, CRM
LINK_Contract_Transaction:
- link_key, contract_hub_key, transaction_hub_key, load_dts, record_source
- Példa sor: L002, H002, H003, 2025-03-24 10:00:00, TXN_SYS

Betöltési logika: A forrásadatokból (pl. Szerződés tábla) kiolvassuk a kapcsolatokat (customer_id és contract_id), megkeressük a megfelelő HUB kulcsokat, és ha a kapcsolat még nem létezik, új LINK rekordot hozunk létre.

SAT táblák

A SAT táblák tartalmazzák az attribútumokat és azok időbeli változásait.

SAT_Customer:
- customer_hub_key
- load_dts (érvényesség kezdete)
- load_end_dts (érvényesség vége, NULL, ha aktuális)
- name, birth_date, address
- Példa sor: H001, 2025-03-24 10:00:00, NULL, "Kovács János", "1980-01-01", "Budapest"
SAT_Contract:
- contract_hub_key, load_dts, load_end_dts, start_date, amount
- Példa sor: H002, 2025-03-24 10:00:00, NULL, "2025-01-01", 50000
SAT_Transaction:
- transaction_hub_key, load_dts, load_end_dts, date, amount
- Példa sor: H003, 2025-03-24 10:00:00, NULL, "2025-03-20", 1000
SAT_Partner:
- partner_hub_key, load_dts, load_end_dts, name, type
- Példa sor: H004, 2025-03-24 10:00:00, NULL, "ABC Kft.", "Szállító"

Betöltési logika: Ha egy attribútum változik (pl. az ügyfél címe), az aktuális SAT rekord load_end_dts mezőjét frissítjük az aktuális időponttal, és új rekordot szúrunk be az új adatokkal.

Összegfogalalva

HUB betöltés: Az üzleti kulcs hash-alapú egyedisége biztosított. LINK betöltés: Kapcsolatok hash key-kkel, duplikáció elkerülésével. SAT betöltés: Hash diff alapján változásérzékelés, historizálás.

4. SCD (Slowly Changing Dimension) a Data Vault-ban

A Data Vault-ban az SCD-t a SAT táblák kezelik, mivel ezek időbélyegekkel követik a változásokat. Nézzük meg az SCD típusokat és kezelésüket:

SCD1 (Felülírás): A Data Vault nem támogatja közvetlenül az SCD1-et, mert az auditálhatóság miatt minden változást megőrzünk. Ha SCD1-et akarunk szimulálni, az adatpiacon történik a felülírás.
SCD2 (Új sor hozzáadása): Ez a Data Vault alapértelmezett működése. Ha pl. az ügyfél címe változik:
- A régi SAT rekord load_end_dts-ét beállítjuk az aktuális időpontra.
- Új SAT rekordot szúrunk be az új címmel, load_dts az aktuális időpont, load_end_dts pedig NULL.
- Példa:
  - Régi: H001, 2025-03-24 10:00:00, 2025-03-25 09:00:00, "Kovács János", "Budapest"
  - Új: H001, 2025-03-25 09:00:00, NULL, "Kovács János", "Debrecen"

5. Adatpiacra (Data Mart) továbbítás

Az adatpiac általában dimenziós modellt (csillag séma) használ, ahol a ténytáblák (pl. tranzakciók) és dimenziók (pl. ügyfél, szerződés) vannak.

Dimenzió tábla (Dim_Customer):
- Összekombináljuk a HUB_Customer és SAT_Customer adatait, az aktuális rekordokat kiválasztva (load_end_dts IS NULL).
- Példa: customer_key, customer_id, name, address → D001, C123, "Kovács János", "Debrecen"
Tény tábla (Fact_Transaction):
- A HUB_Transaction, SAT_Transaction és LINK-ek alapján töltjük fel.
- Példa: transaction_key, contract_key, customer_key, date, amount → T001, C002, D001, "2025-03-20", 1000

Logika: SQL lekérdezésekkel vagy ETL eszközzel (pl. dbt, Informatica) aggregáljuk és transzformáljuk az adatokat a Data Vault-ból a csillag sémába.

6. Összegzés

A HUB-ok az azonosítók, a LINK-ek a kapcsolatok, a SAT-ok pedig a leíró adatok és változások tárolására szolgálnak.
Az SCD2-t a SAT táblák természetesen támogatják az időbélyegekkel.
Az adatpiacra történő továbbítás során a nyers Data Vault adatokat üzleti igények szerint transzformáljuk.

1. Hub táblák létrehozása és töltése

Hub_Customer

Tárolja az ügyfél üzleti kulcsát (CustomerID).

CREATE TABLE Hub_Customer (
    Customer_HashKey CHAR(32) PRIMARY KEY, -- MD5 hash az egyedi azonosításhoz
    CustomerID VARCHAR(10) NOT NULL,
    LoadDate DATETIME NOT NULL,
    RecordSource VARCHAR(50)
);

INSERT INTO Hub_Customer (Customer_HashKey, CustomerID, LoadDate, RecordSource)
SELECT 
    MD5(CustomerID) AS Customer_HashKey,
    CustomerID,
    LoadDate,
    'raw_customer_orders' AS RecordSource
FROM raw_customer_orders
WHERE NOT EXISTS (
    SELECT 1 FROM Hub_Customer hc WHERE hc.CustomerID = raw_customer_orders.CustomerID
);

Hub_Order

Tárolja a rendelés üzleti kulcsát (OrderID).

CREATE TABLE Hub_Order (
    Order_HashKey CHAR(32) PRIMARY KEY,
    OrderID VARCHAR(10) NOT NULL,
    LoadDate DATETIME NOT NULL,
    RecordSource VARCHAR(50)
);

INSERT INTO Hub_Order (Order_HashKey, OrderID, LoadDate, RecordSource)
SELECT 
    MD5(OrderID) AS Order_HashKey,
    OrderID,
    LoadDate,
    'raw_customer_orders' AS RecordSource
FROM raw_customer_orders
WHERE NOT EXISTS (
    SELECT 1 FROM Hub_Order ho WHERE ho.OrderID = raw_customer_orders.OrderID
);

Megjegyzés: Az MD5 hash-t használjuk surrogate key-ként, hogy garantáljuk az egyediséget. A WHERE NOT EXISTS biztosítja, hogy csak új rekordok kerüljenek be.

2. Link tábla létrehozása és töltése

Link_Customer_Order

Összeköti az ügyfeleket és a rendeléseket.

CREATE TABLE Link_Customer_Order (
    Link_HashKey CHAR(32) PRIMARY KEY,
    Customer_HashKey CHAR(32) NOT NULL,
    Order_HashKey CHAR(32) NOT NULL,
    LoadDate DATETIME NOT NULL,
    RecordSource VARCHAR(50),
    FOREIGN KEY (Customer_HashKey) REFERENCES Hub_Customer(Customer_HashKey),
    FOREIGN KEY (Order_HashKey) REFERENCES Hub_Order(Order_HashKey)
);

INSERT INTO Link_Customer_Order (Link_HashKey, Customer_HashKey, Order_HashKey, LoadDate, RecordSource)
SELECT 
    MD5(CONCAT(hc.Customer_HashKey, ho.Order_HashKey)) AS Link_HashKey,
    hc.Customer_HashKey,
    ho.Order_HashKey,
    rco.LoadDate,
    'raw_customer_orders' AS RecordSource
FROM raw_customer_orders rco
JOIN Hub_Customer hc ON hc.CustomerID = rco.CustomerID
JOIN Hub_Order ho ON ho.OrderID = rco.OrderID
WHERE NOT EXISTS (
    SELECT 1 
    FROM Link_Customer_Order lco 
    WHERE lco.Customer_HashKey = hc.Customer_HashKey 
    AND lco.Order_HashKey = ho.Order_HashKey
);

Megjegyzés: A Link_HashKey a két HashKey kombinációjának hash-e, így egyedi kapcsolatot biztosít.

3. Satellite táblák létrehozása és töltése

Sat_Customer_Details

Az ügyfél leíró adatait (pl. név) historizálja.

CREATE TABLE Sat_Customer_Details (
    Customer_HashKey CHAR(32) NOT NULL,
    LoadDate DATETIME NOT NULL,
    LoadEndDate DATETIME,
    CustomerName VARCHAR(50),
    RecordSource VARCHAR(50),
    HashDiff CHAR(32), -- Az attribútumok változásának ellenőrzésére
    PRIMARY KEY (Customer_HashKey, LoadDate),
    FOREIGN KEY (Customer_HashKey) REFERENCES Hub_Customer(Customer_HashKey)
);

INSERT INTO Sat_Customer_Details (Customer_HashKey, LoadDate, LoadEndDate, CustomerName, RecordSource, HashDiff)
SELECT 
    hc.Customer_HashKey,
    rco.LoadDate,
    NULL AS LoadEndDate, -- Aktív rekord, nincs vége
    rco.CustomerName,
    'raw_customer_orders' AS RecordSource,
    MD5(CONCAT(rco.CustomerName)) AS HashDiff
FROM raw_customer_orders rco
JOIN Hub_Customer hc ON hc.CustomerID = rco.CustomerID
WHERE NOT EXISTS (
    SELECT 1 
    FROM Sat_Customer_Details scd 
    WHERE scd.Customer_HashKey = hc.Customer_HashKey 
    AND scd.HashDiff = MD5(CONCAT(rco.CustomerName))
);

Sat_Order_Details

A rendelés részleteit (pl. dátum, termék) historizálja.

CREATE TABLE Sat_Order_Details (
    Order_HashKey CHAR(32) NOT NULL,
    LoadDate DATETIME NOT NULL,
    LoadEndDate DATETIME,
    OrderDate DATE,
    Product VARCHAR(50),
    RecordSource VARCHAR(50),
    HashDiff CHAR(32),
    PRIMARY KEY (Order_HashKey, LoadDate),
    FOREIGN KEY (Order_HashKey) REFERENCES Hub_Order(Order_HashKey)
);

INSERT INTO Sat_Order_Details (Order_HashKey, LoadDate, LoadEndDate, OrderDate, Product, RecordSource, HashDiff)
SELECT 
    ho.Order_HashKey,
    rco.LoadDate,
    NULL AS LoadEndDate,
    rco.OrderDate,
    rco.Product,
    'raw_customer_orders' AS RecordSource,
    MD5(CONCAT(rco.OrderDate, rco.Product)) AS HashDiff
FROM raw_customer_orders rco
JOIN Hub_Order ho ON ho.OrderID = rco.OrderID
WHERE NOT EXISTS (
    SELECT 1 
    FROM Sat_Order_Details sod 
    WHERE sod.Order_HashKey = ho.Order_HashKey 
    AND sod.HashDiff = MD5(CONCAT(rco.OrderDate, rco.Product))
);

Megjegyzés: A HashDiff segít azonosítani, ha az attribútumok változtak. Ha változás történik, új rekord kerül be, és a régi rekord LoadEndDate-je frissül.

Töltési logika összefoglalása

Hub: Új üzleti kulcsok betöltése (csak ha még nem léteznek).
Link: Kapcsolatok létrehozása a Hub-ok között (duplikációk kizárása).
Satellite: Leíró adatok és változásaik historizálása időbélyegekkel és HashDiff ellenőrzéssel.

Ez a megközelítés biztosítja, hogy az adatok gyorsan betölthetők legyenek, és a historikus változások nyomon követhetők maradjanak. Ha további példát vagy részletet szeretnél, jelezd!

Hub_Address: Új entitás a szállítási címhez.

CREATE TABLE Hub_Address (
    Address_HashKey CHAR(32) PRIMARY KEY,
    ShippingAddress VARCHAR(100) NOT NULL,
    LoadDate DATETIME NOT NULL,
    RecordSource VARCHAR(50)
);

INSERT INTO Hub_Address (Address_HashKey, ShippingAddress, LoadDate, RecordSource)
SELECT 
    MD5(ShippingAddress) AS Address_HashKey,
    ShippingAddress,
    LoadDate,
    'raw_ecommerce_data' AS RecordSource
FROM raw_ecommerce_data
WHERE NOT EXISTS (
    SELECT 1 FROM Hub_Address ha WHERE ha.ShippingAddress = raw_ecommerce_data.ShippingAddress
);

Link tábla: Link_Order_Delivery

Ez összeköti a rendelést, az ügyfelet és a szállítási címet.

CREATE TABLE Link_Order_Delivery (
    Link_HashKey CHAR(32) PRIMARY KEY,
    Customer_HashKey CHAR(32) NOT NULL,
    Order_HashKey CHAR(32) NOT NULL,
    Address_HashKey CHAR(32) NOT NULL,
    LoadDate DATETIME NOT NULL,
    RecordSource VARCHAR(50),
    FOREIGN KEY (Customer_HashKey) REFERENCES Hub_Customer(Customer_HashKey),
    FOREIGN KEY (Order_HashKey) REFERENCES Hub_Order(Order_HashKey),
    FOREIGN KEY (Address_HashKey) REFERENCES Hub_Address(Address_HashKey)
);

INSERT INTO Link_Order_Delivery (Link_HashKey, Customer_HashKey, Order_HashKey, Address_HashKey, LoadDate, RecordSource)
SELECT 
    MD5(CONCAT(hc.Customer_HashKey, ho.Order_HashKey, ha.Address_HashKey)) AS Link_HashKey,
    hc.Customer_HashKey,
    ho.Order_HashKey,
    ha.Address_HashKey,
    red.LoadDate,
    'raw_ecommerce_data' AS RecordSource
FROM raw_ecommerce_data red
JOIN Hub_Customer hc ON hc.CustomerID = red.CustomerID
JOIN Hub_Order ho ON ho.OrderID = red.OrderID
JOIN Hub_Address ha ON ha.ShippingAddress = red.ShippingAddress
WHERE NOT EXISTS (
    SELECT 1 
    FROM Link_Order_Delivery lod 
    WHERE lod.Customer_HashKey = hc.Customer_HashKey 
    AND lod.Order_HashKey = ho.Order_HashKey 
    AND lod.Address_HashKey = ha.Address_HashKey
);

Satellite: Sat_Address_Details

A szállítási címhez nem sok attribútum tartozik ebben az esetben, de ha pl. később országot vagy irányítószámot adnánk hozzá, itt tárolnánk.

CREATE TABLE Sat_Address_Details (
    Address_HashKey CHAR(32) NOT NULL,
    LoadDate DATETIME NOT NULL,
    LoadEndDate DATETIME,
    ShippingAddress VARCHAR(100),
    RecordSource VARCHAR(50),
    HashDiff CHAR(32),
    PRIMARY KEY (Address_HashKey, LoadDate),
    FOREIGN KEY (Address_HashKey) REFERENCES Hub_Address(Address_HashKey)
);

INSERT INTO Sat_Address_Details (Address_HashKey, LoadDate, LoadEndDate, ShippingAddress, RecordSource, HashDiff)
SELECT 
    ha.Address_HashKey,
    red.LoadDate,
    NULL AS LoadEndDate,
    red.ShippingAddress,
    'raw_ecommerce_data' AS RecordSource,
    MD5(red.ShippingAddress) AS HashDiff
FROM raw_ecommerce_data red
JOIN Hub_Address ha ON ha.ShippingAddress = red.ShippingAddress
WHERE NOT EXISTS (
    SELECT 1 
    FROM Sat_Address_Details sad 
    WHERE sad.Address_HashKey = ha.Address_HashKey 
    AND sad.HashDiff = MD5(red.ShippingAddress)
);

Fejlettebb megoldások és elvek

1. Delta betöltés optimalizálása

Ahelyett, hogy minden betöltésnél az összes adatot újraellenőriznénk, használhatunk egy "staging" réteget, amely csak a változásokat tartalmazza. Példa:

-- Staging tábla a változásoknak
CREATE TABLE staging_ecommerce_data AS
SELECT * FROM raw_ecommerce_data
WHERE LoadDate > (SELECT MAX(LoadDate) FROM Hub_Customer);

-- Csak a stagingből töltünk
INSERT INTO Hub_Customer (Customer_HashKey, CustomerID, LoadDate, RecordSource)
SELECT 
    MD5(CustomerID),
    CustomerID,
    LoadDate,
    'staging_ecommerce_data'
FROM staging_ecommerce_data
WHERE NOT EXISTS (
    SELECT 1 FROM Hub_Customer hc WHERE hc.CustomerID = staging_ecommerce_data.CustomerID
);

2. Same-As-Link (SAL) használata

Ha azonosítjuk, hogy két üzleti kulcs (pl. két CustomerID) valójában ugyanazt az entitást jelenti (pl. duplikáció miatt), egy SAL táblát hozhatunk létre az összekapcsolásra.

3. Effectivity Satellite

Ha egy kapcsolat (pl. rendelés és cím) időbeli érvényességet igényel, egy Effectivity Satellite-et adhatunk a Link-hez:

CREATE TABLE Sat_Order_Delivery_Effectivity (
    Link_HashKey CHAR(32) NOT NULL,
    LoadDate DATETIME NOT NULL,
    LoadEndDate DATETIME,
    IsActive BOOLEAN,
    RecordSource VARCHAR(50),
    PRIMARY KEY (Link_HashKey, LoadDate),
    FOREIGN KEY (Link_HashKey) REFERENCES Link_Order_Delivery(Link_HashKey)
);

4. Zero Record koncepció

Ha egy Satellite-be nem érkezik adat, egy "zero recordot" illeszthetünk be alapértelmezett értékekkel, hogy az ETL folyamat ne szakadjon meg.

Összegzés és jobb megoldások

Elv: Mindig az üzleti logikát kövesd, ne a forrásrendszer struktúráját.
Szabály: Kerüld a túl sok Satellite létrehozását egy Hub-hoz; ha több kontextus van (pl. ügyfél neve vs. ügyfél hitelminősítése), külön Satellite-eket használj.
Optimalizáció: Használj indexeket a HashKey-ken és particionálást a nagy Satellite táblákhoz a lekérdezési teljesítmény növeléséért.

1. Delta betöltés (Delta Loading)

Mi az a delta betöltés?

A delta betöltés azt jelenti, hogy csak a forrásadatokban bekövetkezett változásokat (új rekordok, módosítások) töltjük be a Data Vault táblákba, ahelyett hogy az összes adatot minden alkalommal újra feldolgoznánk. Ez jelentősen csökkenti az ETL folyamatok futási idejét és az erőforrásigényt.

Példa forgatókönyv

Folytassuk az előző e-kereskedelmi példát. Tegyük fel, hogy a raw_ecommerce_data táblába új rekordok érkeztek, és egy meglévő ügyfél adatai módosultak:

Frissített forrás tábla: raw_ecommerce_data

CustomerID	CustomerName	OrderID	OrderDate	Product	ShippingAddress	LoadDate
C001	John Doe	O1001	2025-03-01	Laptop	123 Main St, NY	2025-03-24 10:00:00
C002	Jane Smith	O1002	2025-03-02	Phone	456 Oak Rd, CA	2025-03-24 10:00:00
C001	John A. Doe	O1003	2025-03-03	Tablet	789 Pine Ave, NY	2025-03-25 09:00:00
C003	Alice Brown	O1004	2025-03-04	Monitor	101 Elm St, TX	2025-03-25 09:00:00

Delta betöltési logika lépései

Staging réteg létrehozása: Kiszűrjük a legutóbbi betöltés óta érkezett vagy módosult rekordokat.
Hub betöltése: Csak az új üzleti kulcsokat adjuk hozzá.
Link betöltése: Csak az új kapcsolatokat töltjük fel.
Satellite betöltése: Ellenőrizzük a változásokat (HashDiff), és historizáljuk őket.

1. Staging tábla létrehozása

Először létrehozzuk a staging táblát, amely csak a változásokat tartalmazza:

CREATE TABLE staging_ecommerce_data AS
SELECT 
    CustomerID,
    CustomerName,
    OrderID,
    OrderDate,
    Product,
    ShippingAddress,
    LoadDate
FROM raw_ecommerce_data
WHERE LoadDate > (SELECT COALESCE(MAX(LoadDate), '1900-01-01') FROM Hub_Customer);

Eredmény:

CustomerID	CustomerName	OrderID	OrderDate	Product	ShippingAddress	LoadDate
C001	John A. Doe	O1003	2025-03-03	Tablet	789 Pine Ave, NY	2025-03-25 09:00:00
C003	Alice Brown	O1004	2025-03-04	Monitor	101 Elm St, TX	2025-03-25 09:00:00

2. Hub_Customer delta betöltése

Csak az új ügyfeleket (C003) töltjük be:

INSERT INTO Hub_Customer (Customer_HashKey, CustomerID, LoadDate, RecordSource)
SELECT 
    MD5(s.CustomerID) AS Customer_HashKey,
    s.CustomerID,
    s.LoadDate,
    'staging_ecommerce_data' AS RecordSource
FROM staging_ecommerce_data s
WHERE NOT EXISTS (
    SELECT 1 FROM Hub_Customer hc WHERE hc.CustomerID = s.CustomerID
);

3. Hub_Order delta betöltése

Új rendelések (O1003, O1004) betöltése:

INSERT INTO Hub_Order (Order_HashKey, OrderID, LoadDate, RecordSource)
SELECT 
    MD5(s.OrderID) AS Order_HashKey,
    s.OrderID,
    s.LoadDate,
    'staging_ecommerce_data' AS RecordSource
FROM staging_ecommerce_data s
WHERE NOT EXISTS (
    SELECT 1 FROM Hub_Order ho WHERE ho.OrderID = s.OrderID
);

4. Link_Order_Delivery delta betöltése

Új kapcsolatok betöltése:

INSERT INTO Link_Order_Delivery (Link_HashKey, Customer_HashKey, Order_HashKey, Address_HashKey, LoadDate, RecordSource)
SELECT 
    MD5(CONCAT(hc.Customer_HashKey, ho.Order_HashKey, ha.Address_HashKey)) AS Link_HashKey,
    hc.Customer_HashKey,
    ho.Order_HashKey,
    ha.Address_HashKey,
    s.LoadDate,
    'staging_ecommerce_data' AS RecordSource
FROM staging_ecommerce_data s
JOIN Hub_Customer hc ON hc.CustomerID = s.CustomerID
JOIN Hub_Order ho ON ho.OrderID = s.OrderID
JOIN Hub_Address ha ON ha.ShippingAddress = s.ShippingAddress
WHERE NOT EXISTS (
    SELECT 1 
    FROM Link_Order_Delivery lod 
    WHERE lod.Customer_HashKey = hc.Customer_HashKey 
    AND lod.Order_HashKey = ho.Order_HashKey 
    AND lod.Address_HashKey = ha.Address_HashKey
);

5. Sat_Customer_Details delta betöltése

Ellenőrizzük, hogy a CustomerName változott-e (pl. C001 esetén), és historizáljuk:

-- Először lezárjuk a régi rekordot, ha van változás
UPDATE Sat_Customer_Details
SET LoadEndDate = s.LoadDate
FROM staging_ecommerce_data s
JOIN Hub_Customer hc ON hc.CustomerID = s.CustomerID
WHERE Sat_Customer_Details.Customer_HashKey = hc.Customer_HashKey
AND Sat_Customer_Details.LoadEndDate IS NULL
AND Sat_Customer_Details.HashDiff != MD5(s.CustomerName);

-- Új rekord beszúrása, ha van változás
INSERT INTO Sat_Customer_Details (Customer_HashKey, LoadDate, LoadEndDate, CustomerName, RecordSource, HashDiff)
SELECT 
    hc.Customer_HashKey,
    s.LoadDate,
    NULL AS LoadEndDate,
    s.CustomerName,
    'staging_ecommerce_data' AS RecordSource,
    MD5(s.CustomerName) AS HashDiff
FROM staging_ecommerce_data s
JOIN Hub_Customer hc ON hc.CustomerID = s.CustomerID
WHERE NOT EXISTS (
    SELECT 1 
    FROM Sat_Customer_Details scd 
    WHERE scd.Customer_HashKey = hc.Customer_HashKey 
    AND scd.HashDiff = MD5(s.CustomerName)
    AND scd.LoadEndDate IS NULL
);

Megjegyzés: A C001 esetében a név "John Doe"-ról "John A. Doe"-ra változott, így a régi rekord lezárul (LoadEndDate kitöltése), és új rekord kerül be.

2. Same-As-Link (SAL)

Mi az a Same-As-Link?

A SAL egy speciális Link tábla, amelyet arra használnak, hogy azonosítsuk és összekapcsoljuk azokat az üzleti kulcsokat, amelyek valójában ugyanazt az entitást reprezentálják (pl. duplikációk különböző forrásrendszerekből). Ez különösen hasznos, ha az adatok integritása nem tökéletes.

Példa forgatókönyv

Tegyük fel, hogy két forrásrendszerünk van, és az ügyfél azonosítója eltérő formátumú, de ugyanazt a személyt jelenti:

Forrás táblák

raw_system1:
CustID Name LoadDate
C001 John Doe 2025-03-24 10:00:00
raw_system2:
ClientCode FullName LoadDate
CLT-001 John A. Doe 2025-03-25 09:00:00

CustID	Name	LoadDate
C001	John Doe	2025-03-24 10:00:00

ClientCode	FullName	LoadDate
CLT-001	John A. Doe	2025-03-25 09:00:00

Feltételezzük, hogy üzleti logika vagy manuális ellenőrzés alapján tudjuk, hogy C001 és CLT-001 ugyanaz a személy.

SAL logika lépései

Hub betöltése: Mindkét kulcs külön Hub rekordként kerül be.
SAL tábla létrehozása: Összekapcsoljuk a két HashKey-t.
Használat: Az üzleti rétegben a SAL alapján egységesíthetjük az entitásokat.

1. Hub_Customer betöltése

Mindkét rendszerből betöltjük az ügyfeleket:

INSERT INTO Hub_Customer (Customer_HashKey, CustomerID, LoadDate, RecordSource)
SELECT 
    MD5(CustID),
    CustID,
    LoadDate,
    'raw_system1'
FROM raw_system1
WHERE NOT EXISTS (
    SELECT 1 FROM Hub_Customer hc WHERE hc.CustomerID = raw_system1.CustID
);

INSERT INTO Hub_Customer (Customer_HashKey, CustomerID, LoadDate, RecordSource)
SELECT 
    MD5(ClientCode),
    ClientCode,
    LoadDate,
    'raw_system2'
FROM raw_system2
WHERE NOT EXISTS (
    SELECT 1 FROM Hub_Customer hc WHERE hc.CustomerID = raw_system2.ClientCode
);

Eredmény Hub_Customer:

Customer_HashKey	CustomerID	LoadDate	RecordSource
hash(C001)	C001	2025-03-24 10:00:00	raw_system1
hash(CLT-001)	CLT-001	2025-03-25 09:00:00	raw_system2

2. SAL_Customer létrehozása és betöltése

Létrehozzuk a SAL táblát, amely összeköti a két azonosítót:

CREATE TABLE SAL_Customer (
    SAL_HashKey CHAR(32) PRIMARY KEY,
    Customer_HashKey_1 CHAR(32) NOT NULL,
    Customer_HashKey_2 CHAR(32) NOT NULL,
    LoadDate DATETIME NOT NULL,
    RecordSource VARCHAR(50),
    FOREIGN KEY (Customer_HashKey_1) REFERENCES Hub_Customer(Customer_HashKey),
    FOREIGN KEY (Customer_HashKey_2) REFERENCES Hub_Customer(Customer_HashKey)
);

INSERT INTO SAL_Customer (SAL_HashKey, Customer_HashKey_1, Customer_HashKey_2, LoadDate, RecordSource)
VALUES (
    MD5(CONCAT(MD5('C001'), MD5('CLT-001'))),
    MD5('C001'),
    MD5('CLT-001'),
    '2025-03-25 10:00:00',
    'manual_mapping'
);

3. Használat lekérdezésben

Ha az üzleti rétegben egységes ügyfélképet akarunk, a SAL alapján összekapcsolhatjuk:

SELECT 
    hc1.CustomerID AS System1_ID,
    hc2.CustomerID AS System2_ID,
    COALESCE(scd1.CustomerName, scd2.CustomerName) AS Unified_Name
FROM SAL_Customer sal
JOIN Hub_Customer hc1 ON hc1.Customer_HashKey = sal.Customer_HashKey_1
JOIN Hub_Customer hc2 ON hc2.Customer_HashKey = sal.Customer_HashKey_2
LEFT JOIN Sat_Customer_Details scd1 ON scd1.Customer_HashKey = hc1.Customer_HashKey AND scd1.LoadEndDate IS NULL
LEFT JOIN Sat_Customer_Details scd2 ON scd2.Customer_HashKey = hc2.Customer_HashKey AND scd2.LoadEndDate IS NULL;

Eredmény:

System1_ID	System2_ID	Unified_Name
C001	CLT-001	John A. Doe

Összegzés

Delta betöltés: Gyorsítja a folyamatokat azáltal, hogy csak a változásokat kezeli. Staging réteg és HashDiff kulcsfontosságú.
SAL: Rugalmasan kezeli a duplikációkat, lehetővé téve az entitások egységesítését anélkül, hogy a nyers adatokat módosítanánk.

HASH alapok

A Data Vault adattárházban a hash-alapú azonosítók kulcsszerepet játszanak a HUB, LINK és SAT táblákban. Az MD5 mellett több más hash-algoritmus is használható, amelyek különböző előnyökkel és hátrányokkal rendelkeznek. Nézzük meg a leggyakoribb opciókat, azok előnyeit, hátrányait és a kiválasztás szempontjait.

1. Hash-algoritmusok összehasonlítása Data Vault esetén

Hash algoritmus	Előnyök	Hátrányok
MD5	Gyors, rövid (128-bit), széleskörű támogatás, kompatibilis SQL rendszerekkel.	Ütközési kockázat (hash collision), nem kriptográfiailag biztonságos.
SHA-1	Jobb ütközésvédelem, hosszabb kulcs (160-bit).	Törhető (kriptoanalízis gyengeségek), lassabb, mint az MD5.
SHA-256	Erős biztonság, hosszú hash (256-bit), alacsony ütközési kockázat.	Lassabb, hosszabb kulcs miatt nagyobb tárhelyigény.
SHA-512	Nagyon erős biztonság, hosszú hash (512-bit), jövőbiztos.	Még lassabb és nagyobb tárolási igény.
XXHash	Nagyon gyors, kisebb CPU-terhelés, skálázható.	Nem kriptográfiai célú, esetenként kisebb ütközési ellenállás.
CityHash / FarmHash	Nagyon gyors, skálázható nagy adatmennyiségre, alacsony CPU-használat.	Nem minden adatbázis támogatja natívan.
BLAKE2	Biztonságos, gyorsabb, mint SHA-256.	Kevésbé elterjedt SQL-ben, nagyobb tárolási igény.

2. Hash kiválasztási szempontok Data Vault-ban

Adatbázis támogatás
- Ha natív SQL támogatás kell, akkor MD5, SHA-1, SHA-256 a legjobb választás.
- Ha BigQuery, Snowflake, Redshift vagy más modern felhős adattárházat használsz, ott SHA-256 jobban támogatott.
Ütközés elkerülése
- MD5 és SHA-1 esetében nagyobb az ütközési kockázat, így SHA-256 vagy BLAKE2 ajánlott, ha hosszú távon biztonság kell.
Sebesség vs. Biztonság
- XXHash, CityHash, FarmHash gyorsabbak, de nem kriptobiztonságosak. Ha gyors hash kell, ezeket használhatod, ha nincs szigorú biztonsági követelmény.
- SHA-256 vagy BLAKE2 jobb kompromisszum, mert biztonságos és nem extrém lassú.
Tárolási igény
- MD5 (16 byte) és SHA-1 (20 byte) kisebb helyet foglal, ezért ha tárolás optimalizálás fontos, ezeket lehet használni.
- SHA-256 (32 byte) és SHA-512 (64 byte) több helyet foglal, de biztonságosabb.
Kompatibilitás régi rendszerekkel
- Ha régebbi adatbázisokkal kell kompatibilisnek lenned, MD5 vagy SHA-1 lehet a megoldás.
- Ha új rendszert építesz, akkor SHA-256 vagy BLAKE2 jobb opció.

3. Javasolt hash-stratégia Data Vault-ban

Ha hagyományos adattárház és SQL-kompatibilitás kell → MD5 vagy SHA-256
Ha erős biztonság kell és ütközést kell kerülni → SHA-256 vagy BLAKE2
Ha nagy teljesítmény kell, de biztonság nem kulcsfontosságú → XXHash vagy CityHash
Ha modern felhőalapú adattárházat használsz (BigQuery, Snowflake) → SHA-256

🔹 Ajánlott kompromisszum: SHA-256, mert biztonságos, elérhető SQL-ben, és hosszú távon megbízható.

Mutatok konkrét SQL példákat a hash-algoritmusok használatára Data Vault adattárházban különböző adatbázisokban.

1. MD5 használata (pl. SQL Server, PostgreSQL, MySQL)

📌 HUB ügyfél tábla (HUB_CUSTOMER) betöltése

--- sql

INSERT INTO HUB_CUSTOMER (CUSTOMER_HK, CUSTOMER_ID, LOAD_DATE, RECORD_SOURCE)
SELECT 
    CONVERT(VARBINARY(16), HASHBYTES('MD5', CUSTOMER_ID)) AS CUSTOMER_HK,
    CUSTOMER_ID,
    CURRENT_TIMESTAMP AS LOAD_DATE,
    'CRM' AS RECORD_SOURCE
FROM STG_CUSTOMER;

👉 Magyarázat:

HASHBYTES('MD5', CUSTOMER_ID): MD5 hash generálása.
CONVERT(VARBINARY(16), ...): Tömörebb tárolás érdekében bináris formátumra alakítja az értéket.
STG_CUSTOMER: Az ügyfél adatok forrása.

📌 LINK ügyfél és szerződés kapcsolat (LINK_CONTRACT_CUSTOMER)

---sql

INSERT INTO LINK_CONTRACT_CUSTOMER (LINK_HK, CUSTOMER_HK, CONTRACT_HK, LOAD_DATE, RECORD_SOURCE)
SELECT 
    CONVERT(VARBINARY(16), HASHBYTES('MD5', CUSTOMER_ID + CONTRACT_ID)) AS LINK_HK,
    CONVERT(VARBINARY(16), HASHBYTES('MD5', CUSTOMER_ID)) AS CUSTOMER_HK,
    CONVERT(VARBINARY(16), HASHBYTES('MD5', CONTRACT_ID)) AS CONTRACT_HK,
    CURRENT_TIMESTAMP AS LOAD_DATE,
    'CRM' AS RECORD_SOURCE
FROM STG_CONTRACT;

👉 Üzleti kulcsok kombinációja alapján képzett hash biztosítja a kapcsolat egyediségét.

2. SHA-256 használata (pl. PostgreSQL, Snowflake, BigQuery, Redshift)

📌 HUB ügyfél tábla (PostgreSQL / Redshift / Snowflake)

--- sql

INSERT INTO HUB_CUSTOMER (CUSTOMER_HK, CUSTOMER_ID, LOAD_DATE, RECORD_SOURCE)
SELECT 
    ENCODE(DIGEST(CUSTOMER_ID, 'sha256'), 'hex') AS CUSTOMER_HK,
    CUSTOMER_ID,
    CURRENT_TIMESTAMP AS LOAD_DATE,
    'CRM' AS RECORD_SOURCE
FROM STG_CUSTOMER;

👉 Magyarázat:

DIGEST(CUSTOMER_ID, 'sha256'): SHA-256 hash generálása.
ENCODE(..., 'hex'): A bináris hash átalakítása hexadecimális formátumba.

📌 SAT ügyfél historizált attribútumok (változáskezelés)

--- sql

INSERT INTO SAT_CUSTOMER (CUSTOMER_HK, EFFECTIVE_FROM, EFFECTIVE_TO, CUSTOMER_NAME, HASH_DIFF, LOAD_DATE)
SELECT 
    ENCODE(DIGEST(CUSTOMER_ID, 'sha256'), 'hex') AS CUSTOMER_HK,
    CURRENT_TIMESTAMP AS EFFECTIVE_FROM,
    NULL AS EFFECTIVE_TO,
    CUSTOMER_NAME,
    ENCODE(DIGEST(CUSTOMER_NAME, 'sha256'), 'hex') AS HASH_DIFF,
    CURRENT_TIMESTAMP AS LOAD_DATE
FROM STG_CUSTOMER
WHERE NOT EXISTS (
    SELECT 1 FROM SAT_CUSTOMER SAT
    WHERE SAT.CUSTOMER_HK = ENCODE(DIGEST(CUSTOMER_ID, 'sha256'), 'hex')
    AND SAT.HASH_DIFF = ENCODE(DIGEST(CUSTOMER_NAME, 'sha256'), 'hex')
);

👉 Üzleti logika:

Ha az ügyfél neve változik, új rekordot hozunk létre az SAT táblában.
HASH_DIFF biztosítja, hogy csak változás esetén legyen új rekord.

3. XXHash használata (pl. BigQuery, Snowflake, Redshift, ClickHouse)

📌 HUB ügyfél tábla (BigQuery)

--- sql

INSERT INTO HUB_CUSTOMER (CUSTOMER_HK, CUSTOMER_ID, LOAD_DATE, RECORD_SOURCE)
SELECT 
    TO_HEX(FARM_FINGERPRINT(CUSTOMER_ID)) AS CUSTOMER_HK,
    CUSTOMER_ID,
    CURRENT_TIMESTAMP AS LOAD_DATE,
    'CRM' AS RECORD_SOURCE
FROM STG_CUSTOMER;

👉 Gyorsabb, de nem kriptográfiailag biztonságos.

📌 SAT ügyfél historizált attribútumok (BigQuery)

--- sql

INSERT INTO SAT_CUSTOMER (CUSTOMER_HK, EFFECTIVE_FROM, CUSTOMER_NAME, HASH_DIFF, LOAD_DATE)
SELECT 
    TO_HEX(FARM_FINGERPRINT(CUSTOMER_ID)) AS CUSTOMER_HK,
    CURRENT_TIMESTAMP AS EFFECTIVE_FROM,
    CUSTOMER_NAME,
    TO_HEX(FARM_FINGERPRINT(CUSTOMER_NAME)) AS HASH_DIFF,
    CURRENT_TIMESTAMP AS LOAD_DATE
FROM STG_CUSTOMER
WHERE NOT EXISTS (
    SELECT 1 FROM SAT_CUSTOMER SAT
    WHERE SAT.CUSTOMER_HK = TO_HEX(FARM_FINGERPRINT(CUSTOMER_ID))
    AND SAT.HASH_DIFF = TO_HEX(FARM_FINGERPRINT(CUSTOMER_NAME))
);

👉 Használat:

BigQuery FARM_FINGERPRINT: Rendkívül gyors, jól skálázódik nagy adatmennyiséggel.
Előny: Olcsóbb és gyorsabb, mint SHA-256, ha nem kell kriptobiztonság.
Hátrány: Nem garantált, hogy ütközésmentes nagy adathalmazoknál.

4. BLAKE2 használata (PostgreSQL, Snowflake, ClickHouse, modern adatbázisok)

📌 HUB ügyfél tábla BLAKE2 hash-sel (PostgreSQL)

--- sql

INSERT INTO HUB_CUSTOMER (CUSTOMER_HK, CUSTOMER_ID, LOAD_DATE, RECORD_SOURCE)
SELECT 
    ENCODE(DIGEST(CUSTOMER_ID, 'blake2b'), 'hex') AS CUSTOMER_HK,
    CUSTOMER_ID,
    CURRENT_TIMESTAMP AS LOAD_DATE,
    'CRM' AS RECORD_SOURCE
FROM STG_CUSTOMER;

👉 BLAKE2 előnyei:

Gyorsabb, mint SHA-256, de ugyanolyan biztonságos.
Alacsony CPU-terhelés, jól optimalizált.

Összegzés - Melyik hash-algoritmust érdemes választani?

Szenárió	Ajánlott hash-algoritmus
SQL Server, MySQL, PostgreSQL régi verziók	MD5, SHA-256
BigQuery, Snowflake, Redshift	SHA-256, FARM_FINGERPRINT (XXHash)
Ha fontos a sebesség	XXHash, CityHash, FarmHash
Ha fontos a biztonság és ütközésmentesség	SHA-256, BLAKE2
Ha modern felhős adattárházat használsz	SHA-256 vagy BLAKE2
Ha kisebb tárolási igény kell	MD5, SHA-1

🔹 Legjobb kompromisszum: SHA-256 vagy BLAKE2.
Ha nagyon nagy teljesítmény kell és nem számít a kriptobiztonság, akkor XXHash (FARM_FINGERPRINT) is jó választás.

Az SQL finomhangolása Data Vault esetén több tényezőtől függ, például:

✅ Indexek és petícionálás
✅ Tömörítés és tárolási optimalizálás
✅ Batch vs. stream feldolgozás
✅ Paralelizálás és optimalizált JOIN-ok
✅ Optimalizált hash-képzés

Lássuk részletesen, hogyan lehet az SQL-t optimalizálni és finomhangolni különböző adatbázisokban!

1. Indexek és particionálás – Gyorsabb keresés és csatlakozás

A hash-kulcsokra érdemes indexet vagy partitioning-et beállítani, hogy a keresések és csatlakozások gyorsabbak legyenek.

1.1. Indexek (PostgreSQL, SQL Server, MySQL)

📌 Példa egy HUB táblára, ahol indexeket használunk a gyors kereséshez:

--- sql

CREATE INDEX idx_hub_customer_hk ON HUB_CUSTOMER (CUSTOMER_HK);

📌 Ha egyedi kulcsokat akarunk garantálni:

--- sql

CREATE UNIQUE INDEX idx_link_customer_contract ON LINK_CONTRACT_CUSTOMER (LINK_HK);

✔ Előnyök:

Az index gyorsabbá teszi a keresést és a JOIN műveleteket.
A UNIQUE INDEX biztosítja, hogy ne legyen duplikáció a kapcsolatokat tároló LINK táblában.

1.2. Particionálás nagy táblákhoz (PostgreSQL, Redshift, BigQuery)

📌 Ha nagy mennyiségű adatunk van, érdemes particionálni pl. dátum szerint:

--- sql

CREATE TABLE SAT_CUSTOMER (
    CUSTOMER_HK VARCHAR(64),
    EFFECTIVE_FROM TIMESTAMP,
    EFFECTIVE_TO TIMESTAMP,
    CUSTOMER_NAME VARCHAR(255),
    HASH_DIFF VARCHAR(64),
    LOAD_DATE TIMESTAMP
)
PARTITION BY RANGE (LOAD_DATE);

✔ Előnyök:

Csak az adott dátumhoz tartozó adatokat olvassa be, gyorsabb keresés!
Hatalmas adattáblák esetén a teljesítmény 10-100x gyorsabb lehet.

2. Tömörítés és tárolási optimalizálás – Kevesebb tárhely, gyorsabb feldolgozás

Hash-ek és historizált adatok esetén sokat lehet spórolni a megfelelő tárolási formátummal.

📌 VARBINARY használata MD5 hash esetén (SQL Server, MySQL)

--- sql

ALTER TABLE HUB_CUSTOMER ALTER COLUMN CUSTOMER_HK SET DATA TYPE VARBINARY(16);

✔ Előnyök:

A hash tárolása kevesebb helyet foglal (pl. VARCHAR(32) helyett VARBINARY(16)).

📌 Tömörített tárolás BigQuery esetén

--- sql

CREATE TABLE HUB_CUSTOMER 
PARTITION BY DATE(LOAD_DATE)
CLUSTER BY CUSTOMER_HK;

✔ Előnyök:

CLUSTER BY segít, hogy a lekérdezések kisebb adathalmazt érjenek el.
PARTITION BY segít, hogy csak az adott dátumhoz tartozó adatokat olvassa be.

3. Batch vs. Stream feldolgozás – Hogyan töltsd be az adatokat hatékonyan?

3.1. Batch betöltés (PostgreSQL, SQL Server)

Ha napi vagy óránkénti betöltés kell, a MERGE vagy INSERT...SELECT hatékony.

📌 Példa ügyfél HUB tábla betöltésére:

--- sql

MERGE INTO HUB_CUSTOMER AS target
USING (SELECT CUSTOMER_ID, CURRENT_TIMESTAMP AS LOAD_DATE FROM STG_CUSTOMER) AS source
ON target.CUSTOMER_HK = ENCODE(DIGEST(source.CUSTOMER_ID, 'sha256'), 'hex')
WHEN NOT MATCHED THEN
    INSERT (CUSTOMER_HK, CUSTOMER_ID, LOAD_DATE, RECORD_SOURCE)
    VALUES (ENCODE(DIGEST(source.CUSTOMER_ID, 'sha256'), 'hex'), source.CUSTOMER_ID, source.LOAD_DATE, 'CRM');

✔ Előnyök:

Csak az új ügyfeleket tölti be → nincs duplikáció.
Hatékony összehasonlítás HASH segítségével.

3.2. Stream feldolgozás (BigQuery, Snowflake, Kafka)

📌 Ha real-time töltés kell, akkor BigQuery-ben vagy Snowflake-ben pl. INSERT INTO használható:

--- sql

INSERT INTO HUB_CUSTOMER 
SELECT DISTINCT
    TO_HEX(SHA256(CUSTOMER_ID)) AS CUSTOMER_HK,
    CUSTOMER_ID,
    CURRENT_TIMESTAMP AS LOAD_DATE,
    'CRM' AS RECORD_SOURCE
FROM STG_CUSTOMER;

✔ Előnyök:

Gyors, mert nem kell duplikációt ellenőrizni (ezt a stream processing kezeli).

4. Paralelizálás és optimalizált JOIN-ok – Hogyan csökkentsd a lekérdezési időt?

Ha nagy adathalmazokkal dolgozol, fontos, hogy a JOIN műveletek gyorsak legyenek.

4.1. JOIN optimalizálás a HUB és SAT között

📌 Ha van index, akkor a JOIN gyorsabb lesz:

--- sql

SELECT 
    HUB.CUSTOMER_HK, SAT.CUSTOMER_NAME, SAT.LOAD_DATE
FROM HUB_CUSTOMER HUB
JOIN SAT_CUSTOMER SAT ON HUB.CUSTOMER_HK = SAT.CUSTOMER_HK
WHERE SAT.LOAD_DATE >= '2025-01-01';

✔ Előnyök:

Az index miatt csak a szükséges sorokat olvassa be.

📌 Ha nagy az adat, érdemes HASH JOIN-t használni (pl. Snowflake, Redshift, BigQuery)

--- sql

SELECT /*+ HASH_JOIN(HUB, SAT) */
    HUB.CUSTOMER_HK, SAT.CUSTOMER_NAME, SAT.LOAD_DATE
FROM HUB_CUSTOMER HUB
JOIN SAT_CUSTOMER SAT ON HUB.CUSTOMER_HK = SAT.CUSTOMER_HK
WHERE SAT.LOAD_DATE >= '2025-01-01';

✔ Előnyök:

HASH JOIN gyorsabb, ha nincs index.
OLAP adatbázisoknál (BigQuery, Snowflake, Redshift) hatékonyabb.

5. Optimalizált hash-képzés – Hogyan csökkentsd a CPU-terhelést?

Hash-függvények optimalizálása nagy adatmennyiség esetén csökkenti a CPU-használatot.

📌 Ha gyors hash kell, használj FARM_FINGERPRINT-et BigQuery-ben:

--- sql

SELECT TO_HEX(FARM_FINGERPRINT(CUSTOMER_ID)) AS CUSTOMER_HK FROM STG_CUSTOMER;

✔ Előnyök:

Gyorsabb, mint SHA-256, de nem kriptobiztonságos.

📌 Ha biztonság kell, használd a megfelelő algoritmust:

---sql

SELECT TO_HEX(SHA256(CUSTOMER_ID)) AS CUSTOMER_HK FROM STG_CUSTOMER;

✔ Előnyök:

Biztonságosabb, mint MD5 és hosszabb távon megbízhatóbb.

Összegzés – Hogyan finomhangold az SQL-t?

✅ Használj indexeket és particionálást a gyorsabb keresésekhez.
✅ Batch vs. Stream feldolgozást optimalizáld az igényeid szerint.
✅ JOIN műveleteknél HASH JOIN vagy megfelelő indexelés legyen.
✅ Optimalizáld a hash-képzést, hogy csökkentsd a CPU-használatot.

Oracle adatbázis hash képzés alapok

Az Oracle adatbázisban a hash-algoritmusok és optimalizálási lehetőségek kritikus szerepet játszanak Data Vault modellben, különösen HUB, LINK, SAT táblák betöltésekor. Nézzük meg részletesen:

1. Hash-algoritmusok Oracle-ben

Oracle adatbázisban többféle beépített hash-függvény érhető el, például:

MD5 → DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(data), DBMS_CRYPTO.HASH_MD5)
SHA-1, SHA-256, SHA-512 → DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(data), DBMS_CRYPTO.HASH_SH256)
ORACLE STANDARD_HASH (Ajánlott!) → STANDARD_HASH(data, 'SHA256')

🔹 Ajánlott: STANDARD_HASH, mert gyorsabb, egyszerűbb és Oracle optimalizálta.

📌 Példa HUB ügyfél hash-képzésre:

--- sql

SELECT STANDARD_HASH('123456', 'SHA256') FROM dual;

👉 Előnyök:

Gyorsabb, mint DBMS_CRYPTO (nincs szükség RAW konverzióra).
Tömörebb kód (könnyebb olvasni és karbantartani).
Támogatott algoritmusok: MD5, SHA1, SHA256, SHA384, SHA512.

📌 Alternatíva – DBMS_CRYPTO használata:

--- sql

SELECT RAWTOHEX(DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW('123456'), DBMS_CRYPTO.HASH_SHA256)) FROM dual;

👉 Előnyök:

Biztonságosabb (bármilyen adatot kezel).
Kompatibilis régebbi verziókkal.

2. HUB tábla optimalizált betöltése hash kulcsokkal

📌 HUB_CUSTOMER betöltés optimalizált hash-sel és indexekkel:

--- sql

INSERT INTO HUB_CUSTOMER (CUSTOMER_HK, CUSTOMER_ID, LOAD_DATE, RECORD_SOURCE)
SELECT 
    STANDARD_HASH(CUSTOMER_ID, 'SHA256') AS CUSTOMER_HK,
    CUSTOMER_ID,
    SYSTIMESTAMP AS LOAD_DATE,
    'CRM' AS RECORD_SOURCE
FROM STG_CUSTOMER;

👉 Optimalizálás:

HASH kulcs indexelése:

--- sql

CREATE INDEX IDX_HUB_CUSTOMER_HK ON HUB_CUSTOMER (CUSTOMER_HK);

Particionálás dátum szerint (gyorsabb keresés):

--- sql

CREATE TABLE HUB_CUSTOMER (
    CUSTOMER_HK VARCHAR2(64),
    CUSTOMER_ID VARCHAR2(50),
    LOAD_DATE TIMESTAMP,
    RECORD_SOURCE VARCHAR2(50)
)
PARTITION BY RANGE (LOAD_DATE)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
    PARTITION p20240324 VALUES LESS THAN (TO_DATE('2024-03-25', 'YYYY-MM-DD'))
);

✔ Előnyök:

Csak az új rekordokat keresed ki gyorsan.
Nem kell teljes táblát bejárni minden JOIN-nál.
Index gyorsítja a hash alapú csatlakozásokat.

3. LINK táblák optimalizálása – Több hash kombinációja

📌 LINK ügyfél és szerződés kapcsolat betöltése optimalizált módon:

--- sql

INSERT INTO LINK_CONTRACT_CUSTOMER (LINK_HK, CUSTOMER_HK, CONTRACT_HK, LOAD_DATE, RECORD_SOURCE)
SELECT 
    STANDARD_HASH(CUSTOMER_ID || CONTRACT_ID, 'SHA256') AS LINK_HK,
    STANDARD_HASH(CUSTOMER_ID, 'SHA256') AS CUSTOMER_HK,
    STANDARD_HASH(CONTRACT_ID, 'SHA256') AS CONTRACT_HK,
    SYSTIMESTAMP AS LOAD_DATE,
    'CRM' AS RECORD_SOURCE
FROM STG_CONTRACT;

👉 Optimalizálás:

Több kulcs kombinálása hash-ben (CUSTOMER_ID || CONTRACT_ID).
Rövidebb storage footprint → VARCHAR2(64) helyett RAW(32):

--- sql

ALTER TABLE LINK_CONTRACT_CUSTOMER MODIFY LINK_HK RAW(32);

✔ Előnyök:

Gyorsabb keresések (RAW kevesebb helyet foglal, kisebb indexméret).
Csökkenti az I/O műveleteket.

4. SAT tábla optimalizálása – Hash alapú változásdetektálás (SCD-2)

📌 Ügyfél historizált adatok betöltése változás detektálással:

--- sql

INSERT INTO SAT_CUSTOMER (CUSTOMER_HK, EFFECTIVE_FROM, CUSTOMER_NAME, HASH_DIFF, LOAD_DATE)
SELECT 
    STANDARD_HASH(CUSTOMER_ID, 'SHA256') AS CUSTOMER_HK,
    SYSTIMESTAMP AS EFFECTIVE_FROM,
    CUSTOMER_NAME,
    STANDARD_HASH(CUSTOMER_NAME, 'SHA256') AS HASH_DIFF,
    SYSTIMESTAMP AS LOAD_DATE
FROM STG_CUSTOMER SC
WHERE NOT EXISTS (
    SELECT 1 FROM SAT_CUSTOMER SAT
    WHERE SAT.CUSTOMER_HK = STANDARD_HASH(SC.CUSTOMER_ID, 'SHA256')
    AND SAT.HASH_DIFF = STANDARD_HASH(SC.CUSTOMER_NAME, 'SHA256')
);

👉 Optimalizálás:

Csak akkor tölt új rekordot, ha változás történt (HASH_DIFF eltér).
Index HASH_DIFF mezőn:

--- sql

CREATE INDEX IDX_SAT_CUSTOMER_HASH_DIFF ON SAT_CUSTOMER (HASH_DIFF);

✔ Előnyök:

Minimalizálja az új rekordok számát (csak változás esetén új sor).
Gyorsabb összehasonlítás HASH_DIFF alapján.

5. Optimalizált JOIN-ok hash kulcsokkal

Hash-kulcsok használata gyorsíthatja a csatlakozásokat HUB és SAT között.

📌 Optimalizált JOIN a HUB és SAT között

--- sql

SELECT /*+ USE_HASH(SAT) */
    HUB.CUSTOMER_HK, SAT.CUSTOMER_NAME, SAT.LOAD_DATE
FROM HUB_CUSTOMER HUB
JOIN SAT_CUSTOMER SAT 
ON HUB.CUSTOMER_HK = SAT.CUSTOMER_HK
WHERE SAT.LOAD_DATE >= TO_DATE('2025-01-01', 'YYYY-MM-DD');

👉 Optimalizálás:

/*+ USE_HASH(SAT) */ → HASH JOIN kényszerítése, ha a tábla nagy.
Partition Pruning használata:

--- sql

ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 4;

✔ Előnyök:

Nagyobb táblák esetén gyorsabb, mint NESTED LOOP JOIN.
Csak a releváns particiókat olvassa be.

6. Hash-képzés finomhangolása – CPU és I/O optimalizálás

🔹 Tippek a hash-képzés gyorsítására Oracle-ben:

Használj STANDARD_HASH-t DBMS_CRYPTO.HASH helyett – gyorsabb!

VARBINARY helyett RAW(32)-t használj a hash tárolására:

---sql

ALTER TABLE HUB_CUSTOMER MODIFY CUSTOMER_HK RAW(32);

Használj particionálást (LOAD_DATE) és clusterezett indexeket.

Tuning: optimalizált paraméterek beállítása:

--- sql

ALTER SYSTEM SET "_hash_join_enabled"=TRUE;
ALTER SYSTEM SET "_optimizer_use_feedback"=TRUE;

Parallel betöltés gyorsításához:

--- sql
 
ALTER SESSION ENABLE PARALLEL DML;

Összegzés

✅ Használj STANDARD_HASH-t a gyorsabb és optimalizált hash-képzéshez.
✅ Indexeld és particionáld a hash mezőket a gyors keresésekhez.
✅ Használj HASH JOIN-t és RAW(32) tárolást a jobb teljesítmény érdekében.
✅ Csak a változott rekordokat töltsd be (HASH_DIFF ellenőrzéssel).

Oracle hangolása

Az Oracle adatbázis finomhangolása kulcsfontosságú a teljesítmény maximalizálásához, különösen nagy adattárházak esetében. Az alábbiakban részletesen bemutatom az SQL lekérdezések, indexek, memóriahasználat, paralelizmus és egyéb finomhangolási technikák optimalizálását.

1. SQL finomhangolása

A teljesítmény legfontosabb tényezője a hatékony SQL írás és a megfelelő végrehajtási terv kiválasztása.

1.1. EXPLAIN PLAN és SQL Profile használata

📌 Nézd meg, hogyan tervezi végrehajtani az SQL-t az Oracle:

--- sql

EXPLAIN PLAN FOR 
SELECT * FROM TRANSACTIONS WHERE TRANSACTION_DATE > SYSDATE - 30;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

🔹 Mit cimkékre figyelj:

FULL TABLE SCAN → Kerüld! Használj indexeket, ha kell!
HASH JOIN vs. NESTED LOOPS → Nagy adatoknál HASH JOIN jobb!
INDEX RANGE SCAN → Jó választás keresésekhez!

📌 SQL Profile generálás automatikusan:

--- sql

EXEC DBMS_SQLTUNE.CREATE_SQL_PROFILE(sql_id => 'your_sql_id');

👉 Előny: Az Oracle automatikusan optimalizálja a végrehajtási tervet.

1.2. Indexek finomhangolása

Indexek segítenek a keresési teljesítményben, de nem mindig!

📌 Milyen indexeket használj?

B-Tree index – Gyors keresés egyedi értékeknél.
Bitmap index – Ha kevés egyedi érték van (YES/NO, STATUS oszlopok).
Function-Based Index – Ha keresés során átalakítod az oszlop értékét:
```
--- sql

CREATE INDEX IDX_UPPER_NAME ON CUSTOMERS (UPPER(CUSTOMER_NAME));
```
Particionált Index – Nagy adattábláknál jobb teljesítményt adhat.

📌 Indexek gyors felmérése:

--- sql

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'TRANSACTIONS';

🔹 Optimalizálás:

Túl sok index → Lassítja az INSERT/UPDATE műveleteket.
Ha indexet ritkán használnak → DROP INDEX lehet hasznos.

2. Memória és cache optimalizálása

Az Oracle rendelkezik SGA (System Global Area) és PGA (Program Global Area) memóriaterületekkel. Ezek finomhangolása javítja az SQL végrehajtás sebességét.

2.1. SGA és PGA memória beállítása

📌 Ellenőrizd a jelenlegi memóriahasználatot:

--- sql

SELECT component, current_size FROM v$sga_dynamic_components;
SELECT name, value FROM v$pgastat;

📌 Memóriabeállítás automatikusan:

--- sql

ALTER SYSTEM SET MEMORY_TARGET = 8G;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 2G;

🔹 Tipp: SGA_TARGET és PGA_TARGET megfelelő méretezése csökkenti az I/O műveleteket.

2.2. Result Cache bekapcsolása

A Result Cache segítségével az Oracle gyorsítótárba menti a lekérdezés eredményét, így ha többször futtatod ugyanazt a SQL-t, sokkal gyorsabb lesz.

📌 Engedélyezés:

--- sql

ALTER SYSTEM SET RESULT_CACHE_MODE = FORCE;

📌 Használata egy SQL-ben:

--- sql

SELECT /*+ RESULT_CACHE */ COUNT(*) FROM TRANSACTIONS WHERE STATUS = 'APPROVED';

👉 Előnyök:

Lassú aggregációk gyorsítása (pl. COUNT, SUM, AVG).
Több user ugyanazt az adatot kéri le → gyorsítja a válaszidőt.

3. Paralelizmus optimalizálása (Parallel Query)

A paralelizmus kihasználása óriási teljesítménynövekedést hozhat!

📌 Parallel Query bekapcsolása:

--- sql

ALTER SESSION ENABLE PARALLEL DML;
SELECT /*+ PARALLEL(TRANSACTIONS, 4) */ * FROM TRANSACTIONS;

📌 Automatikus paralelizmus:

--- sql

ALTER TABLE TRANSACTIONS PARALLEL 8;

🔹 Tipp: Ha az adatok nagyméretűek, használj PARALLEL hintet a gyorsítás érdekében!

4. Adattáblák és particionálás finomhangolása

Ha az adattábla nagyon nagy, particionálás segíthet csökkenteni az I/O műveleteket.

📌 Példa havi particionálásra:

--- sql

CREATE TABLE TRANSACTIONS (
    ID NUMBER PRIMARY KEY,
    TRANSACTION_DATE DATE NOT NULL
)
PARTITION BY RANGE (TRANSACTION_DATE) (
    PARTITION p202401 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
    PARTITION p202402 VALUES LESS THAN (TO_DATE('2024-03-01', 'YYYY-MM-DD'))
);

👉 Előnyök:

Gyorsabb keresés: Az Oracle csak a releváns partíciót olvassa be.
Partition Pruning → Csak a szükséges partíciót dolgozza fel.

5. Log fájlok és redo/undo kezelés optimalizálása

Ha az INSERT/UPDATE/DELETE műveletek lassúak, a redo/undo log kezelés optimalizálható.

📌 Redo log méretének növelése:

--- sql

ALTER DATABASE ADD LOGFILE GROUP 4 ('/oracle/logs/redo04.log') SIZE 100M;

📌 UNDO tablespace méretének növelése:

---sql

ALTER DATABASE SET UNDO_TABLESPACE = UNDO_TBS2;

🔹 Tipp:

Ha nagy INSERT-eket végzel, kapcsold ki a redo logot ideiglenesen:
```
--- sql

ALTER SESSION DISABLE COMMIT IN PROCEDURE;
```

A Data Vault betöltési logikában gyakran fontos, hogy az adatbetöltés (pl. HUB, LINK, SAT táblák feltöltése) egyetlen tranzakció részeként történjen, hogy az adatok konzisztensek maradjanak. Ha egy alprocedure önállóan commitolna (pl. egy segédeljárás), az megszakíthatná a betöltési folyamatot, és inkonzisztens állapotot eredményezhetne. Az ALTER SESSION DISABLE COMMIT IN PROCEDURE használata itt garantálja, hogy a tranzakcióvezérlés a fő betöltési folyamat kezében maradjon.

Ha SELECT INTO TEMP tábla lassú, növeld a TEMP méretét:

--- sql

ALTER DATABASE TEMPFILE '/oracle/temp01.dbf' RESIZE 4G;

Összegzés – Oracle finomhangolási tippek

✅ EXPLAIN PLAN + SQL Profile használata → Megérted, miért lassú az SQL.
✅ Indexek optimalizálása → Ne használj feleslegesen sok indexet.
✅ SGA/PGA memória optimalizálása → Kevesebb I/O, gyorsabb végrehajtás.
✅ Parallel Query bekapcsolása → Nagy adatoknál sokkal gyorsabb.
✅ Particionálás → Gyorsabb keresések és jobb teljesítmény.
✅ Redo/Undo optimalizálás → Gyorsabb INSERT/UPDATE/DELETE műveletek.

Miért RAW(32) az oracle adatbázisban a javasolt mező tipus hash-hoz

Az Oracle-ben a RAW egy bináris adattípus, amelyet nyers bájtsorozatok tárolására terveztek, és nem végez rajta semmilyen karakterkódolási vagy konverziós műveletet. A RAW(32) konkrétan 32 bájtos (256 bites) bináris adat tárolására alkalmas, ami ideális a hash kulcsokhoz, például az SHA-256 algoritmus kimenetének tárolására.

IT, BI, DWH, DM, AI