SQL alapok 
Parquet
Az SQL (Structured Query Language) a data engineering legfontosabb alapnyelve – szinte minden adatfeldolgozási folyamat valamilyen formában SQL-t használ. Legyen szó adatkinyerésről, transzformációról vagy analitikáról, az SQL az a közös nyelv, amit minden adatbázis, data warehouse és big data rendszer ért.
Ebben a kurzusban a WebShop Pro projekten keresztül tanulod meg az SQL alapokat és az adatmodellezést. Egy fiktív webáruház teljes adatfolyamát fogjuk felépíteni: a táblák létrehozásától kezdve az összetett analitikai lekérdezéseken át a star schema adatmodellig. Minden fogalmat közvetlenül gyakorlati kódpéldákon keresztül fogsz megérni.
A kurzus három nagy blokkra épül: először az SQL lekérdező nyelv alapjait vesszük át (SELECT, JOIN, aggregáció, window functions), majd az adatmódosítást és a tranzakciókezelést vizsgáljuk, végül rátérünk az analitikai adatmodellezésre (star schema, SCD, normalizálás). A végére képes leszel önállóan felépíteni egy teljes analitikai adatmodellt.
SELECT, JOIN, Aggregáció, Window Functions, CTE, DML/DDL, Tranzakciók, Star Schema, SCD, Normalizálás, Parquet, Teljes analitikai modell
Felépítjük a WebShop Pro teljes analitikai adatmodelljét.
CTE · SCD · Star Schema
Táblák és adattípusok
A relációs adatbázisok az adatokat táblákban (tables) tárolják, ahol minden tábla egy adott entitást – például vásárlókat, termékeket vagy rendeléseket – ír le. Minden táblának van egy sémája (schema), ami meghatározza, milyen oszlopokból áll és milyen adattípusú értékeket tartalmazhatnak azok. A séma a tervrajz, a sorok (rows) pedig a konkrét adatok.
A fenti kódrészlet egy customers táblát hoz létre a WebShop Pro rendszerben. A CREATE TABLE utasítás határozza meg a tábla nevét és oszlopait. Minden oszlopnak meg kell adni az adattípust: INT egész számokhoz, VARCHAR(n) szövegekhez, DECIMAL(p,s) pénzértékekhez, TIMESTAMP dátum+idő kombinációkhoz, BOOLEAN pedig igaz/hamis értékekhez. A PRIMARY KEY biztosítja, hogy minden vásárló azonosítója egyedi legyen.
A NOT NULL megkötés garantálja, hogy a név mező nem maradhat üresen, az UNIQUE pedig megakadályozza, hogy két vásárló ugyanazzal az e-mail címmel regisztráljon. A DEFAULT kulcsszó automatikus értéket rendel: a created_at a rekord létrehozásának időpontját kapja, is_active pedig alapértelmezetten igaz lesz.
Gyakori hiba: ne felejts el PRIMARY KEY-t definiálni – enélkül nehéz lesz a táblát más táblákhoz kapcsolni, és az adatbázis sem tudja hatékonyan indexelni a sorokat.
| Típus | Leírás | Példa |
|---|---|---|
| INT | Egész szám | customer_id |
| VARCHAR(n) | Szöveg | name |
| DECIMAL(p,s) | Tizedes szám | price |
| TIMESTAMP | Dátum és idő | created_at |
| BOOLEAN | Igaz/hamis érték | is_active |
-- Create customers table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
city VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);Query OK, 0 rows affected
INSERT, SELECT, WHERE
Az INSERT INTO utasítás új sorokat ad hozzá egy táblához – ebben az esetben három vásárlót szúrunk be a customers táblába. Minden érték sorrendben kell, hogy megfeleljen a tábla oszlopainak: azonosító, név, e-mail, város, regisztráció dátuma és aktív státusz. A VALUES kulcsszó után vesszővel elválasztva több sort is felvihetünk egyetlen utasítással.
A SELECT az SQL leggyakoribb utasítása – ezzel kérdezünk le adatokat. A SELECT name, city azt mondja meg, hogy csak a név és a város oszlopot szeretnénk látni. A FROM customers határozza meg a forrástáblát, a WHERE city = 'Budapest' pedig egy szűrőfeltétel: csak azokat a sorokat adja vissza, ahol a város pontosan Budapest. Az eredményből jól látszik, hogy két budapesti vásárlónk van.
Ez a két művelet – adatfelvitel és adatlekérdezés – a data engineering mindennapi alapja. Az ETL folyamatokban is ezek kellenek: a nyers adatokat először betöltjük (INSERT), utána transzformáljuk és lekérdezzük (SELECT). A WHERE feltétel kulcsfontosságú, mert ritkán van szükségünk az összes adatra – általában egy részét akarjuk csak feldolgozni.
Praktikus tipp: az INSERT-nél mindig ellenőrizd, hogy az értékek száma megegyezik-e az oszlopok számával – a leggyakoribb hiba az, amikor egy oszlopot kihagysz vagy feleslegesen beírsz.
INSERT INTO customers VALUES
(1, 'Kovacs Anna', 'anna@email.hu', 'Budapest', '2024-01-15', TRUE),
(2, 'Nagy Peter', 'peter@email.hu', 'Debrecen', '2024-02-20', TRUE),
(3, 'Szabo Kati', 'kati@email.hu', 'Budapest', '2024-03-10', FALSE);
SELECT name, city FROM customers WHERE city = 'Budapest';name | city ------------+---------- Kovacs Anna | Budapest Szabo Kati | Budapest 2 rows
ORDER BY, LIMIT, DISTINCT
A ORDER BY utasítás rendezni tudja a lekérdezés eredményét egy vagy több oszlop alapján. Alapértelmezetten növekvő (ASC) sorrendben rendez, de a DESC kulcsszóval csökkenő sorrendet is kérhetünk. A példában a legdrágább termékeket keressük, ezért az árat csökkenő sorrendbe rendezzük, a LIMIT 5 pedig csak az első 5 sort tartja meg – így megkapjuk a „top 5 legdrágább termék" listát.
A DISTINCT kulcsszó egyedi (ismétlődés nélküli) értékeket ad vissza. Amikor a SELECT DISTINCT category FROM products lekérdezést futtatjuk, az adatbázis minden kategóriát csak egyszer listáz – akkor is, ha több száz termék tartozik ugyanabba a kategóriába. Ez nagyon hasznos, ha meg akarjuk tudni, milyen értékek szerepelnek egy oszlopban, vagy ha egyedi értékek számát akarjuk megtudni (a COUNT(DISTINCT category) kombinációval).
A LIMIT (vagy TOP SQL Serverben) a data engineeringben az egyik leggyakrabban használt eszköz az adatfeltárás (data exploration) során. Mielőtt egy több millió soros táblán futtatsz nehéz lekérdezést, érdemes LIMIT 10-nel megnézni, hogy az adatok struktúrája megfelelő-e. A ORDER BY és LIMIT együttes használata tökéletes „top N" jellegű kérdések megválaszolására: ki a legjobb vevő, melyik a legnépszerűbb termék, stb.
Figyelem: ha ORDER BY nélkül használod a LIMIT-et, az adatbázis tetszőleges sorokat adhat vissza – a sorrend nem garantált explicit rendezés nélkül!
SELECT name, price FROM products ORDER BY price DESC LIMIT 5; SELECT DISTINCT category FROM products ORDER BY category;
name | price ----------------+------- Laptop Pro 16 | 549.99 Phone Ultra | 449.99 Audio, Laptop, Phone, Tablet
Aggregáció: GROUP BY, HAVING
Az aggregáció azt jelenti, hogy több sor adataiból egyetlen összesített értéket készítünk. A leggyakoribb aggregáló függvények: COUNT(*) (sorok száma), SUM() (összeg), AVG() (átlag), MIN() és MAX() (minimum és maximum). Ezek önmagukban az egész táblára vonatkoznak, de a GROUP BY utasítással csoportonként tudjuk alkalmazni őket.
A példában kategóriánként számoljuk ki a rendelések számát (COUNT(*)), az összes bevételt (SUM(oi.quantity * oi.unit_price)) és az átlagos egyséárat (AVG(oi.unit_price)). A GROUP BY p.category mondja meg, hogy a products tábla category oszlopa szerint kell csoportosítani. A ROUND(..., 2) két tizedesjegyre kerekít, hogy az eredmény olvashatóbb legyen.
A HAVING olyan, mint a WHERE, de a csoportosítás után szűr. A WHERE még az aggregáció előtt fut (egyes sorokat szűri), a HAVING pedig a már aggregált eredményeken. A példában HAVING revenue > 1000 csak azokat a kategóriákat tartja, ahol a bevétel meghaladja az 1000-et. A végén ORDER BY revenue DESC csökkenő sorrendbe rendezi az eredményt.
Gyakori hiba: ha a SELECT-ben egy nem aggregált oszlop szerepel (pl. p.category), annak szerepelnie kell a GROUP BY-ban is – különben az adatbázis nem tudja, melyik csoport értékét mutassa.
SELECT p.category, COUNT(*) AS order_count,
SUM(oi.quantity * oi.unit_price) AS revenue,
ROUND(AVG(oi.unit_price), 2) AS avg_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
HAVING revenue > 1000
ORDER BY revenue DESC;category | order_count | revenue | avg_price ---------+-------------+----------+---------- Laptop | 45 | 24749.55 | 549.99 Phone | 62 | 18949.38 | 399.99
JOIN: Táblák összekapcsolása
A JOIN az SQL egyik legfontosabb művelete: táblákat kapcsol össze közös kulcsok alapján. A való életben az adatok szét vannak osztva több táblába (ez a normalizálás lényege), de a lekérdezéskor össze kell fűzni őket. A WebShop Pro rendszerben például a vásárlók adatai a customers táblában vannak, a rendeléseik a orders táblában, a tételek az order_items táblában, és a termékek a products táblában – a JOIN segítségével mindezt egyetlen eredménytáblává fűzhetjük össze.
A fenti lekérdezés négy táblát kapcsol össze. Az INNER JOIN csak azokat a sorokat tartja meg, amelyekhez van pár a másik táblában. Az ON feltétel adja meg, mely oszlopok egyezését vizsgálja: a customers.customer_id összeköti a rendelésekkel, a orders.order_id a tételsorokkal, és a order_items.product_id a termékekkel. A c, o, oi, p aliasok (rövidítések) teszik olvashatóbbá a kódot – ahelyett, hogy mindenhol kiírnánk a teljes táblanevet.
A lekérdezés eredménye megmutatja, melyik vásárló melyik terméket vette, mennyiért és milyen mennyiségben. A line_total egy számított oszlop: oi.quantity * oi.unit_price – azaz a tétel teljes értéke. Ezt az adatbázis „menet közben" számolja ki, nem kell külön tárolni.
Fontos szabály: ha LEFT JOIN-t használsz, a bal oldali tábla összes sora megmarad – még akkor is, ha a jobb oldalon nincs egyezés (ilyenkor NULL értékeket kapsz). Ezt gyakran használják arra, hogy megtaláljuk azokat a vásárlókat, akik még nem rendeltek semmit.
| JOIN típus | Leírás |
|---|---|
| INNER JOIN | Csak azok a sorok jelennek meg, amelyek mindkét táblában egyeznek. |
| LEFT JOIN | A bal oldali tábla összes sora megmarad, a jobb oldali táblából pedig az egyező sorok kerülnek mellé. |
| FULL JOIN | Mindkét tábla összes sora megmarad; ahol nincs pár, ott NULL értékek jelennek meg. |
SELECT c.name AS customer, p.name AS product,
oi.quantity, oi.unit_price,
oi.quantity * oi.unit_price AS line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
ORDER BY c.name;customer | product | quantity | unit_price | line_total ------------+---------------+----------+------------+----------- Kovacs Anna | Laptop Pro 16 | 1 | 549.99 | 549.99
Subquery és CTE (WITH)
A CTE (Common Table Expression) a WITH kulcsszóval definiált névleges allekérdezés, amely olvashatóbbá teszi az összetett SQL kódot. Ahelyett, hogy egy hatalmas, egymásba ágyazott lekérdezést írnánk, a CTE-vel logikai blokkokra bonthatjuk a kódot – mintha változókat hoznánk létre a lekérdezésen belül. A CTE eredménye csak az azt követő fő lekérdezésben használható, nem tárolódik el tartósan.
A példában a customer_revenue CTE kiszámítja minden vásárló teljes költését úgy, hogy a customers, orders és order_items táblákat összekapcsolja és csoportosít. Ezt az eredményt a fő SELECT utasítás úgy használja, mintha egy valódi tábla lenne – de valójában csak egy átmeneti, memóriában lévő eredményhalmaz. A WHERE feltétel egy allekérdezéssel (subquery) szűri ki az átlag felettieket: SELECT AVG(total_spent) FROM customer_revenue.
A CTE-k különösen fontosak a data engineeringben, ahol a lekérdezések gyakran több száz sorosak. A WITH blokkok segítségével a kód modularizálható: minden transzformációs lépés saját CTE-be kerülhet (stg_orders, int_customer, mart_revenue stb.), ami megkönnyíti a hibakeresést és a karbantartást is.
Tipp: ha egy allekérdezést több helyen is használsz, mindenképpen emeld ki CTE-be – nemcsak olvashatóbb lesz, de bizonyos adatbázisok a CTE eredményt cache-elik, így gyorsabb is lehet.
Ha egy részlekérdezést több helyen használsz, vagy nehezen olvashatóvá válik a query, emeld ki CTE-be.
WITH customer_revenue AS (
SELECT c.customer_id, c.name,
SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name
)
SELECT name, total_spent
FROM customer_revenue
WHERE total_spent > (SELECT AVG(total_spent) FROM customer_revenue)
ORDER BY total_spent DESC;name | total_spent -------------+------------ Kovacs Anna | 1349.97
Window Functions
A window function (ablakfüggvény) olyan függvény, amely soronként eredményt ad, de a számítás egy kapcsolódó sorhalmazt (az „ablakot") vesz figyelembe – ellentétben a GROUP BY-val, amely a sorokat összevonja egyetlen értékké. Ez azt jelenti, hogy minden sor megmarad az eredményben, de kiegészül egy számított értékkel (pl. futó összeg, sorszám, rangsor).
A példában a SUM(SUM(total_amount)) OVER (ORDER BY ...) egy futó összeget (running total) számol havi bontásban. A belső SUM(total_amount) a GROUP BY miatt havonta összegzi a bevételelt, a külső SUM(...) OVER (...) pedig ezeket a havi összegeket halmozottan összeadja. Januárban a futó összeg megegyezik a havi bevétellel, februárban már a január+február összeg jelenik meg, és így tovább.
Az OVER() határozza meg az ablakot: ORDER BY DATE_TRUNC('month', order_date) mondja meg, hogy milyen sorrendben halmozunk. A DATE_TRUNC('month', ...) függvény a dátumot havi szintre kerekíti, így minden hónap egyetlen csoportot képez. A PARTITION BY kulcsszóval további csoportosítás is lehetséges – például termékkategóriánkénti futó összeg számításához.
Ablakfüggvények a data engineeringben: a ROW_NUMBER(), RANK(), LAG(), LEAD() és SUM() OVER mind-mind elengedhetetlenek az analitikai ETL folyamatokban, például a deduplikáció, az SCD Type 2 kezelés vagy a cohort elemzések során.
SELECT DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS revenue,
SUM(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS running_total
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;month | revenue | running_total --------+---------+--------------- 2024-01 | 2349.96 | 2349.96 2024-02 | 3199.94 | 5549.90 2024-03 | 4299.91 | 9849.81
CASE WHEN és transzformáció
A CASE WHEN az SQL feltételes kifejezése – hasonló a programozási nyelvek if-else szerkezetéhez. Lehetővé teszi, hogy egy oszlop értékét feltételek alapján alakítsuk ki. A példában a vásárlókat szegmentáljuk aszerint, mennyit költöttek: VIP (2000 felett), Regular (500-2000 között) vagy Occasional (500 alatt).
A CASE WHEN sorrendben értékeli ki a feltételeket, és az első igaz feltételnek megfelelő THEN ágat adja vissza. A végén az ELSE az a „kapunyitás" ág, amely akkor fut, ha egyik feltétel sem teljesül. A kódban a SUM(oi.quantity * oi.unit_price) eredményét vizsgáljuk – ez a teljes költés összege vásárlónként.
A COALESCE(SUM(...), 0) függvény NULL értékeket kezel: ha egy vásárló nem rendelt semmit, a LEFT JOIN miatt a SUM eredménye NULL lenne – a COALESCE ezt nullára cseréli. Ez azért fontos, mert a CASE WHEN összehasonlítása NULL értékkel mindig hamis eredményt ad, és a vásárló „eltűnhetne" a szegmentálásból.
A LEFT JOIN-t használunk (nem INNER JOIN-t), hogy azok a vásárlók is megjelenjenek az eredményben, akik még nem rendeltek – náluk a total_spent nulla lesz, és az „Occasional" szegmensbe kerülnek.
SELECT c.name,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spent,
CASE WHEN SUM(oi.quantity * oi.unit_price) >= 2000 THEN 'VIP'
WHEN SUM(oi.quantity * oi.unit_price) >= 500 THEN 'Regular'
ELSE 'Occasional' END AS segment
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;name | total_spent | segment -------------+------------+---------- Kovacs Anna | 1349.97 | Regular Szabo Kati | 0.00 | Occasional
DML: MERGE, UPSERT
A MERGE (más néven upsert) a data engineering egyik legfontosabb művelete: egyetlen utasításban frissít vagy beszúr sortokat attól függően, hogy már léteznek-e a céltáblában. Ez különösen fontos az ETL folyamatokban, ahol a napi adatbetöltés során az új rekordokat be kell szúrni, a meglévőket pedig frissíteni kell.
A példa három lépést mutat. Az első az UPDATE, amely az SCD Type 2 (Slowly Changing Dimension) mintára lezárja a régi rekordot: a valid_to mezőt az aktuális dátumra állítja, az is_current flag-et pedig hamisra. A második lépés egy INSERT, amely új sort szúr be az új értékekkel (pl. a vásárló új városa: Szeged). Így a dimenziótáblában a vásárlónak két sora lesz – a régi (lezárt) és az új (aktuális) –, és a történeti változások nyomon követhetők.
A harmadik lépés a MERGE INTO utasítás: a USING határozza meg a forrásadatokat, az ON a kapcsolási feltételt. Ha talál egyezést (WHEN MATCHED), akkor UPDATE-eli a meglévő sort; ha nem talál (WHEN NOT MATCHED), akkor INSERT-tel új sort hoz létre. Ez az „insert or update" logika iparági szabvány a növekményes (incremental) adatbetöltésben.
Praktikus tipp: a MERGE előtt mindig teszteld le egy SELECT utasítással, hány sort fog érinteni – egy rossz ON feltétel miatt az egész táblát felülírhatod!
-- SCD Type 2: Close old + insert new UPDATE dim_customer SET valid_to = CURRENT_DATE, is_current = FALSE WHERE customer_id = 1 AND is_current = TRUE; INSERT INTO dim_customer VALUES (101, 1, 'Kovacs Anna', 'Szeged', 'Regular', CURRENT_DATE, '9999-12-31', TRUE); -- MERGE (upsert) MERGE INTO products AS target USING (VALUES (10, 'New Tablet', 'Tablet', 299.99, 50)) AS src (id, name, cat, price, stock) ON target.product_id = src.id WHEN MATCHED THEN UPDATE SET price = src.price WHEN NOT MATCHED THEN INSERT VALUES (src.id, src.name, src.cat, src.price, src.stock);
Query OK, 3 rows affected
DDL, Indexek, Partitioning
A DDL (Data Definition Language) a táblaszerkezetet módosító utasítások gyűjtőneve: ide tartozik a CREATE TABLE, ALTER TABLE, DROP TABLE és az CREATE INDEX. Míg a DML (INSERT, UPDATE, SELECT) az adatokkal dolgozik, a DDL magát a struktúrát definiálja – ez az adatbázis „alapja", amit az adatbetöltés előtt kell megtervezni.
Az indexek (indexes) jelentősen felgyorsítják a lekérdezéseket azáltal, hogy az adatbázisnak nem kell végigpásztáznia az egész táblát. A CREATE INDEX idx_orders_customer ON orders(customer_id) egy indexet hoz létre a customer_id oszlopra – ettől kezdve a WHERE customer_id = 1 jellegű lekérdezések sokkal gyorsabbak lesznek. Hasonlóan, a CREATE INDEX idx_orders_date ON orders(order_date) a dátum alapú szűréseket gyorsítja, ami gyakori az analitikai jelentésekben.
A particionálás (partitioning) nagy táblák esetén elengedhetetlen: a táblát logikai részekre (partíciókra) bontja, például hónap vagy régió szerint. Így a lekérdezések csak az érintett partíciókat olvassák, nem az egész táblát. A példa az information_schema.tables rendszertáblából kérdezi le a táblák méretét – ez hasznos a monitoring során, hogy lásd, melyik tábla fogyasztja a legtöbb tárhelyet.
Fontos: az indexek felgyorsítják az olvasást, de lassítják az írást (mert minden INSERT/UPDATE/DELETE esetén az indexet is frissíteni kell). Csak ott hozz létre indexet, ahol tényleg gyakori a szűrés!
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
SELECT table_name, table_rows,
ROUND(data_length/1024/1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'webshop_pro' ORDER BY data_length DESC;table_name | table_rows | size_mb ------------+-----------+-------- orders | 500000 | 125.50 order_items | 1850000 | 342.10
Tranzakciók: ACID
Az ACID rövidítés a megbízható tranzakciókezelés négy alapelve: Atomicity (atomosság), Consistency (konzisztencia), Isolation (izoláció) és Durability (tartósság). Ezek garantálják, hogy az adatbázis még hiba, összeomlás vagy párhuzamos hozzáférés esetén is helyes és tartós állapotban maradjon – kritikus követelmény minden pénzügyi és üzleti tranzakciónál.
A példa egy egyszerű átutalást mutat: az egyik számláról levonunk 500 dollárt, a másikra feltöltjük ugyanennyit. A BEGIN TRANSACTION elindítja a tranzakciót, a két UPDATE módosítja az egyenlegeket, a COMMIT pedig véglegesíti a változtatásokat. Ha a két UPDATE között hiba történik (pl. áramkimaradás), az Atomicity miatt az adatbázis automatikusan visszavonja (rollback) az első UPDATE-et is – tehát nem történik meg, hogy az egyik számláról levontuk a pénzt, de a másikra nem került rá.
A ROLLBACK utasítással manuálisan is visszavonható egy tranzakció – ezt gyakran használják hiba esetén, hogy az adatbázis ne maradjon inkonzisztens állapotban. A data engineeringben a tranzakciók különösen fontosak a dimenziótáblák frissítésekor (SCD Type 2), ahol a régi sor lezárása és az új sor beszúrása egyetlen atomi műveletként kell, hogy történjen.
Fontos: a modern big data rendszerek (pl. Spark, Kafka) gyakran csak eventual consistency-t garantálnak az ACID helyett – a tranzakciós garanciák a relációs adatbázisok erősségei.
| Tulajdonság | Leírás |
|---|---|
| Atomicity | A tranzakció vagy teljes egészében lefut, vagy semmi nem marad belőle. |
| Consistency | A tranzakció után az adatbázis érvényes állapotban marad. |
| Isolation | A párhuzamos tranzakciók nem zavarják össze egymás köztes állapotait. |
| Durability | A véglegesített adatok tartósan tárolódnak. |
BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 500 WHERE account_id = 1; UPDATE accounts SET balance = balance + 500 WHERE account_id = 2; COMMIT;
Query OK, COMMIT successful
VIEW, UDF
A VIEW (nézet) egy mentett lekérdezés, amit úgy használhatsz, mintha egy valódi tábla lenne. Nem tárol fizikailag adatot – minden alkalommal, amikor lekérdezed, a mögötte lévő SQL kód fut le. Ez nagyon hasznos az ismétlődő lekérdezések egyszerűsítésére, az adathozzáférések szabályozására és az üzleti logika központosítására.
A példában a vw_customer_orders nézet minden vásárló rendelésszámát és teljes költését (lifetime value) tartalmazza. A LEFT JOIN biztosítja, hogy a rendeléstelen vásárlók is megjelennek – náluk a COALESCE nullát ad a lifetime_value helyett. A nézet létrehozása után a SELECT * FROM vw_customer_orders WHERE order_count > 3 már sokkal egyszerűbb, mint az eredeti több táblás JOIN lekérdezés.
Az UDF (User Defined Function) egyedi függvény, amelyet te írsz és regisztrálsz az adatbázisban. Például egy calculate_discount(category, amount) függvényt létrehozva a kedvezényszámítási logikát egy helyen tarthatod, és minden lekérdezésből meghívhatod. A data engineeringben az UDF-ek segítenek a komplex üzleti szabályok (pl. adószámítás, konverzió) kiszervezésében.
Praktikus tipp: a nézeteket rétegenként érdemes szervezni – staging nézetek a nyers adatokhoz, intermediate nézetek a transzformált adatokhoz, mart nézetek a végleges riportokhoz. Ezt a „view-on-view" mintát az Section 18-ban részletesen is bemutatjuk.
CREATE VIEW vw_customer_orders AS
SELECT c.customer_id, c.name,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
SELECT * FROM vw_customer_orders WHERE order_count > 3;customer_id | name | order_count | lifetime_value ------------+-------------+-------------+--------------- 1 | Kovacs Anna | 5 | 1349.97
Adatmodellezés alapjai
Az adatmodellezés a folyamat, amelynek során a valós világ entitásait (vásárlók, termékek, rendelések) és kapcsolataikat adatbázis-struktúrává alakítjuk. Három szinten dolgozunk: a konceptuális szint az üzleti fogalmakat és kapcsolataikat írja le (üzleti elemzőknek), a logikai szint már táblák, oszlopok és kulcsok formájában jeleníti meg ezeket (adatmodellezőknek), a fizikai szint pedig az implementáció részleteit (indexek, partíciók, adattípusok) határozza meg (DBA-knak és data engineereknek).
A WebShop Pro rendszerben a fő entitások: Customers (vásárlók), Products (termékek), Orders (rendelések), Order Items (tételsorok), Categories (kategóriák), Payments (fizetések), Shipments (szállítások) és Reviews (értékelések). Ezek között a kapcsolat típusos: egy vásárló több rendelést adhat le, egy rendelés több tételt tartalmazhat, egy termék egy kategóriához tartozik.
A jó adatmodell alapja a helyes kapcsolatok meghatározása: 1:1 (egy ügyfél egy profil), 1:N (egy vásárló több rendelés), N:M (több termék több kategóriában – ezt köztáblával oldjuk meg). A kulcsok (primary key, foreign key) biztosítják a referenciális integritást – tehát nem lehet olyan rendelés, amelyhez nem létezik vásárló.
A legnagyobb hiba: rögtön a fizikai szinttel kezdeni, anélkül, hogy a konceptuális és logikai modellt tisztáztad volna. Mindig először értsd meg az üzleti logikát!
| Szint | Leírás | Kinek hasznos |
|---|---|---|
| Konceptuális | Üzleti entitások és kapcsolataik | Üzleti elemzők |
| Logikai | Táblák, oszlopok, kulcsok | Adatmodellezők |
| Fizikai | Indexek, partíciók, tárolási döntések | DBA-k és data engineerek |
Customers, Products, Orders, Order Items, Categories, Payments, Shipments, Reviews.
Star Schema
A Star Schema (csillagséma) az analitikai adatmodellezés leggyakoribb mintája. A középen helyezkedik el a fact tábla (tények táblája), amely a mérhető eseményeket tárolja (pl. rendelések, fizetések, kattintások), és körülötte a dimenzió táblák (dim) adják meg a kontextust (kicsoda, mi, mikor, hol). A sémának a neve azért „csillag", mert a fact tábla és a dimenziók kapcsolata csillag alakú mintát ad az ER diagramon.
A fenti kódban a fact_orders a központi fact tábla: rendelési tételeket tárol, és idegen kulcsokkal (foreign key) hivatkozik a dimenziókra – customer_sk, product_sk, date_sk. A dimenziók (dim_customer, dim_product, dim_date) a leíró információkat tartalmazzák. A mértékek (metrics) – quantity, unit_price, net_amount – a fact táblában vannak, mert ezek azok a számok, amiket aggregálni akarunk (összeg, átlag, stb.).
Fontos különbség: a dimenziók surrogate key-t (helyettes kulcs, sk) használnak, nem az eredeti üzleti azonosítót. Ez azért van, mert az SCD Type 2 miatt egy üzleti entitásnak (pl. vásárló) több sora lehet a dimenzióban – a surrogate key egyedileg azonosítja mindegyiket. A REFERENCES megkötés biztosítja a referenciális integritást a fact és a dimenziók között.
A star schema előnye: a lekérdezések egyszerűek és gyorsak – csak a fact táblát kell JOIN-olni a szükséges dimenziókkal, nincs bonyolult többutas kapcsolat. Ezt a mintát használják a Snowflake, BigQuery, Redshift és a legtöbb modern data warehouse.
-- Fact table
CREATE TABLE fact_orders (
order_sk BIGINT PRIMARY KEY,
customer_sk BIGINT REFERENCES dim_customer(sk),
product_sk BIGINT REFERENCES dim_product(sk),
date_sk BIGINT REFERENCES dim_date(sk),
quantity INT,
unit_price DECIMAL(10,2),
net_amount DECIMAL(10,2)
);
-- Dimension: dim_customer
CREATE TABLE dim_customer (
sk BIGINT PRIMARY KEY, customer_id INT,
name VARCHAR(100), city VARCHAR(50),
segment VARCHAR(20), is_current BOOLEAN
);Query OK, 4 tables created
SCD: Lassan változó dimenziók
Az SCD (Slowly Changing Dimension) stratégiák azt írják le, hogyan kezeljük a dimenziótáblákban az idővel változó adatokat. „Lassan változó" alatt azt értjük, hogy a változások ritkák (pl. a vásárló átköltözik egy másik városba), de nyomon kell követni őket – az analitikai jelentésekben gyakran kell tudni, hogy egy adott időpontban mi volt a vásárló címe vagy ügyfélszegmense.
A SCD Type 2 a leggyakoribb stratégia: minden változásnál új sort szúrunk be érvényességi időintervallummal (valid_from, valid_to) és egy is_current flaggel. A példában Kovacs Anna Budapestről Szegedre költözött: az UPDATE lezárja a régi sort (valid_to = '2024-06-01', is_current = FALSE), az INSERT pedig új sort hoz létre az új várossal és nyitott időintervallummal (valid_to = '9999-12-31'). Így az eredményben két sor látható: a régi (Budapest, lezárt) és az új (Szeged, aktuális).
A 9999-12-31 az úgynevezett „végtelen jövő" dátum – ezt használjuk, amíg a sor aktuális. Amikor változás történik, ezt felülírjuk a tényleges záródátummal. A SELECT utasítás a végén ellenőrzi az eredményt: mindkét sor megjelenik, és a is_current flag egyértelműen mutatja, melyik az érvényes.
Gyakori buktató: az SCD Type 2 implementálásakor gondoskodni kell arról, hogy a fact táblában a megfelelő surrogate key hivatkozzon a dimenzió megfelelő verziójára – különben a riportok téves kontextusban mutatják az adatokat.
| SCD típus | Stratégia |
|---|---|
| Type 0 | Az érték nem változik, a korábbi állapotot megőrizzük. |
| Type 1 | Felülírjuk a régi értéket, nem tartunk történetet. |
| Type 2 | Új sort szúrunk be érvényességi időintervallummal. |
| Type 3 | A régi és az új érték külön oszlopban szerepel. |
-- SCD Type 2 UPDATE dim_customer SET valid_to = '2024-06-01', is_current = FALSE WHERE customer_id = 1 AND is_current = TRUE; INSERT INTO dim_customer VALUES (101, 1, 'Kovacs Anna', 'Szeged', 'Regular', '2024-06-01', '9999-12-31', TRUE); SELECT customer_id, name, city, valid_from, is_current FROM dim_customer WHERE customer_id = 1;
customer_id | name | city | valid_from | is_current ------------+-----------+---------+------------+---------- 1 | Kovacs Anna| Budapest| 2024-01-15 | FALSE 1 | Kovacs Anna| Szeged | 2024-06-01 | TRUE
Normalizálás
A normalizálás az a folyamat, amely során az adatmodellt úgy szervezzük át, hogy minimalizáljuk a redundanciát és elkerüljük az anomáliákat (beszúrási, módosítási, törlési). A normálformák (NF) szabályrendszerek: az 1NF megköveteli, hogy minden mező atomi (egy értéket tartalmazzon, ne például egy listát), a 2NF kiküszöböli a részleges függőségeket (összetett kulcs esetén minden nem kulcs mező a teljes kulcstól függjön), a 3NF pedig megszünteti a tranzitív függőségeket (nem kulcs mezők ne függjenek egymástól).
A gyakorlatban a normalizálás azt jelenti, hogy a terméknév, kategória és ár nem szerepel minden rendelési tételben külön, hanem egy külön products táblában tároljuk őket, és a tételek csak egy product_id hivatkozással hivatkoznak rá. Ha a termék ára változik, csak egy helyen kell frissíteni – ez a normalizálás legnagyobb előnye. Normalizálatlan modellnél ugyanezt százával vagy millióval kellene frissíteni.
A denormalizálás a normalizálás ellentéte: szándékosan engedünk a redundanciából az olvasási teljesítmény javítása érdekében. Az analitikai rendszerekben (OLAP) gyakori, mert a jelentések gyors lekérdezéseket igényel – a star schema például denormalizált modell, ahol a dimenzióadatok (pl. város, szegmens) közvetlenül elérhetők a fact táblából, bonyolult JOIN nélkül.
Szabály: OLTP rendszerekben (tranzakciós, pl. webshop rendelésfelvétel) normalizálj (3NF), OLAP rendszerekben (analitikai jelentések) denormalizálj (star schema). A kettő között az ETL folyamat hidat képez.
| NF | Szabály |
|---|---|
| 1NF | Minden mező atomi, vagyis egyetlen értéket tartalmaz. |
| 2NF | Nincs részleges függőség összetett kulcs esetén. |
| 3NF | Nincs tranzitív függőség a nem kulcs mezők között. |
OLTP rendszerekben jellemzően normalizált modellt, OLAP rendszerekben gyakran denormalizált star schemát használunk.
Parquet és DuckDB Parquet
A Parquet egy oszlopos tárolási formátum, amelyet az Apache projekt fejlesztett. A hagyományos sor-alapú formátumokkal (pl. CSV) ellentétben a Parquet oszloponként tárolja az adatokat, ami két hatalmas előnnyel jár: (1) ha csak néhány oszlopra van szükséged, nem kell az összeset beolvasni, és (2) az azonos típusú adatok jobban tömöríthetők. Ez a data engineering alaptechnológiája – a legtöbb modern data lake Parquet fájlokat használ.
A DuckDB egy beágyazható, in-process analitikai adatbázis – olyan, mint az „SQLite analitikai feladatokra". Nem kell szerver telepíteni, egy Python importálással (import duckdb) azonnal használható, és natívan képes Parquet fájlokat olvasni. A read_parquet('gold/fact_orders.parquet') függvény egy Parquet fájlt úgy olvas be, mintha egy SQL tábla lenne – a DuckDB automatikusan felismeri a sémát és az adattípusokat.
A példában a conn.execute(...) egy SQL lekérdezést futtat közvetlenül Parquet fájlokon: a fact_orders.parquet és a dim_product.parquet fájlokat JOIN-olja, kategóriánként aggregál, és az eredményt egy Pandas DataFrame-ként (.fetchdf()) adja vissza. Ez az úgynevezett „lakehouse" megközelítés: a data lake fájlokon (Parquet) SQL lekérdezéseket futtatunk, dedikált data warehouse nélkül.
Miért fontos ez? A Parquet + DuckDB kombináció a modern data engineering egyik leggyakoribb eszköztára. Olcsó (nem kell szerver), gyors (oszlopos tárolás + vektorizált végrehajtás), és skálázható (a Parquet fájlok végtelenül particionálhatók). A dbt, a Spark és a legtöbb ETL eszköz is Parquet-ot használ köztes formátumként.
import duckdb
conn = duckdb.connect()
result = conn.execute("""
SELECT p.category, COUNT(*) AS orders,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM read_parquet('gold/fact_orders.parquet') oi
JOIN read_parquet('gold/dim_product.parquet') p ON oi.product_sk = p.sk
GROUP BY p.category ORDER BY revenue DESC
""").fetchdf()
print(result.to_string(index=False))category | orders | revenue ---------+--------+--------- Laptop | 45 | 24749.55 Phone | 62 | 18949.38
Teljes analitikai modell
A modern data engineeringben az adatfolyamatot rétegekbe (layers) szervezzük, amelyeket a Medallion architektúra (bronze-silver-gold) vagy a dbt stílusú elnevezés (staging-intermediate-mart) írnak le. Ez a megközelítés modularizálja a transzformációkat: minden réteg egy jól definiált feladatot lát el, és a hibakeresés, a tesztelés és a karbantartás sokkal könnyebb lesz.
A staging réteg (stg_orders) a nyers adatokat tisztítja és szűri: a példában a raw_orders táblából kiszűrjük a törölt rendeléseket (WHERE status != 'cancelled'), és csak a szükséges oszlopokat tartjuk meg. Ez a „bronze" szint – itt még csak szűrés és átnevezés történik, nincs komplex transzformáció.
Az intermediate réteg (int_customer_orders) összekapcsolja a staging adatokat a dimenziókkal: a stg_orders nézetet JOIN-olja a dim_customer táblával, így már rendelkezésre állnak az üzleti attribútumok (név, szegmens). Ez a „silver" szint – ahol az adatok már értelmezhetők, de még nem véglegesek.
A mart réteg (mart_monthly_revenue) a végleges, riportolásra kész adatokat tartalmazza: szegmensenként aggregálva a rendelések számát és az árbevételt. Ez a „gold" szint – ezeket a nézeteket közvetlenül a BI eszközök (Metabase, Tableau, Looker) olvassák.
-- Staging CREATE VIEW stg_orders AS SELECT order_id, customer_id, order_date, total_amount FROM raw_orders WHERE status != 'cancelled'; -- Intermediate CREATE VIEW int_customer_orders AS SELECT c.name, c.segment, o.order_date, o.total_amount FROM stg_orders o JOIN dim_customer c ON o.customer_id = c.customer_id; -- Mart CREATE VIEW mart_monthly_revenue AS SELECT segment, COUNT(*) AS orders, SUM(total_amount) AS revenue FROM int_customer_orders GROUP BY segment;
segment | orders | revenue -----------+--------+------- VIP | 28 | 18249.80 Regular | 67 | 23499.65
Összefoglalás
Gratulálunk! Végigcsináltad az SQL & Data Modeling kurzust – ez egy komoly eredmény. Végigmentünk az SQL lekérdező nyelv alapjaitól (SELECT, WHERE, JOIN, GROUP BY) a haladó technikákon át (window functions, CTE, CASE WHEN) egészen az adatmodellezésig (star schema, SCD, normalizálás). A végére már fel tudod építeni a WebShop Pro teljes analitikai adatmodelljét, és érted, hogyan működnek a réteges adatfolyamatok (staging → intermediate → mart).
A megtanult fogalmak nemcsak az SQL-re korlátozódnak: a JOIN logikája, az aggregáció, az ablakfüggvények és a star schema univerzális koncepciók, amelyeket minden adatbázisban és big data rendszerben (Spark, Snowflake, BigQuery, Databricks) megtalálsz. Az SQL a data engineering lingua franca – ha ezt jól érted, a többi eszközt is gyorsan el fogod sajátítani.
A következő lépés a Python for Data Engineering kurzus, ahol megtanulod, hogyan automatizáld ezeket a folyamatokat Pythonnal: Pandas DataFrame-ökkel dolgozol, Parquet fájlokat írsz és olvasol, Delta Lake-et használsz, és PyArrow-ral dolgozol nagy adathalmazokon. A Python + SQL kombináció a data engineer legfontosabb eszköztára.
Tanács: gyakorolj! Építs saját projekteket – például egy online store analitikai modelljét –, és használd a kurzusban tanult sémákat. A legjobb módja a tanulásnak, ha saját adatokon kísérletezel.
| Megtanultuk | Következő |
|---|---|
| SELECT, JOIN, GROUP BY | Python for DE |
| Window functions, CTE | Pandas DataFrame |
| Star schema, SCD | Delta Lake |
| DuckDB + Parquet | PyArrow |
Python for Data Engineering!