db.dobo.sk

Postgres 9 ETL

3 komentárov

lets go back to roots because roots mean culture

pridat stlpec s autoinkrementalnou hodnotou a PK

ALTER TABLE tablename ADD COLUMN id_aut SERIAL PRIMARY KEY;

updajtni tabulku na zaklade 2 parametroveho subselectu

UPDATE tab_finds SET datation = 4000 WHERE id_aut IN
(SELECT id_aut FROM tab_finds where material = ‘stavebni keramika’ AND datation < 3100);

 texty, stringy char a varchar

lowercase/uppercase

UPDATE tabulka SET column=lower(column);

updatovanie tabuľky – concatenácia dvoch polí, s tým, že číselné polia sú prevedené na characters, všimni si parameter formátovania (FM)

UPDATE tab_polygony_sezony SET concat_season_polygon = (TO_CHAR(season,’FM9′) || TO_CHAR(r,’FM999′)) AS ddd  FROM probe;

prehľad stingových funkcií

matematické operácie

pomerne intuitívne

UPDATE tabulka SET calcul = datace/(hustota*koeficient);

UPDATE tab_polygony_sezony
SET koeficient_rozestupy = ROUND
(rozestup_linii/(SELECT AVG(rozestup_linii)
FROM tab_polygony_sezony),2)

window funkcie

SELECT distinct(id_polygon), sum(“vaha”) OVER (PARTITION BY id_polygon) AS Vaha_overall
FROM tab_finds
where season=1
ORDER BY id_polygon;

joiny

poznáme nasledovné typy joinov v postgrese:

postgresql-all-joins

left join abstraktny priklad

SELECT A.pka, A.c1, B.pkb, B.c2
FROM A
LEFT JOIN B ON A .pka = B.fka;

Lateral join

Chápem to tak, že je to nahradenie subselectu (resp. ešte lepšie – correlated subquery), ktorý má tú nevýhodu, že vracia len jeden record. Lateral join vracia sadu recordov, tak, akoby sa nad result setom loopovalo a zároveň hneď spojí (join) výsledky a hlavným dotazom. Pekné vysvetlenie na stackoverflow.

Fyzické joiny

Hore uvedené typy joinov sú logické typy – abstrakcie, ako spájať dáta v tabuľkách. Samotné joiny na fyzickej vrstve sa realizujú pomocou nested loop join, merge join, alebo hash join

Nested loop join

Nested lop join je operácia “každý s každým” – z jednej tabuľky sa vyberajú postupne hodnoty a loopom sa skúša, ktoré pasujú na hodnoty v druhej tabuľke. Náročné na CPU, pokiaľ je recordov v oboch tabuľkách hodne. Pekné vysvetlenie tu na YT.

Nested loop má niekoľko “podtypov”, podľa toho, aká presná implementácia sa použije (napr. materialized nested loop používa materialized views; indexed nested loop používa indexy a pod.)

Merge join

V princípe podobný ako nested loop join (porvnávanie zhody hodnôt), ale algoritmus nemusí iterovať (loopovať), pretože hodnoty sú zoradené. Z toho však vyplýva, že musí existovať buď index, alebo sa najprv musia hodnoty zoradiť. Pekné vysvetlenie tu na YT.

Merge join má tiež niekoľko podtypov v závislosti na presnej implementácii (materialized, indexed podľa toho, na ktorú tabuľku sa indexy využívajú a pod….).

Hash join

Hash join môže byť použitý len na equi-joins.

Podstatou je prehnanie hodnoty z 1. tabuľky hashovaciou funkciou a vytvorenie hash bucketu (chlieviky pre hashe, alebo hashová mapa). Následne sa hashovaciou funkciou porovnávajú hodnoty z 2. tabuľky a tým priradzujú do správnych bucketov. Je to pomerne rýchle a stavané aj na veľké tabuľky. Je to všem rýchle, len pokiaľ sa hashovacia tabuľka vojde do pamäte, inak sa bude tempovať na disk a komplikuje sa to. Vysvetlenie na YT tu.

Postgres to robí rýchlo, pokiaľ je to in memory a pokiaľ to nie je všetko in-memory, tak používa hybridnú hash table s šikmým vyhľadávaním dát. In-memory znamená, že sa to vojde do work_mem a postgres sa na začiatku rozhodne, že použije in-memory hash join vtedy, pokiaľ inner table je menšia, než 25% work_mem.

Efektivita rôznych typov joinov

Video s potrhlým chlapíkom od Oraclu ukazuje ešte raz a na príklade hracích kariet celý proces. Tento obrázok z videa jasne ukazuje efektivity jednotlivých typov joinov (počet fyzických operácií). No a prečo sa nepoužíva výlučne hash join? Pretože to platí len pre preliezanie celých oboch datasetov s operátorom “=”, čož nie je vždy náš prípad…

 

select na hodnoty inej tabulky cez M:N relation

Pri normalizácii sa často stane, že máme tabuľky poprepájané cez M:N reláciu (“pomocnú tabuľku”). Ako selektovať všetky záznamy z druhej tabuľky pre špecifický záznam z prvej tabuľky? Typický príklad z archelogickej opraxe: stratigrafické jednotky a fotky, každá entita má svoju vlastnú tabuľku. . Stratigrafická jednotka može byť na viacerých fotkách a fotka može obsahovať viac stratigrafických jednotiek ergo M:N. Robí sa to cez inner joiny na intermediate tabuľku a pekný rozklad je tu.

 

konverzia datových typov medzi Oracle a Postgres

 

PostgreSQL Oracle
BIGINT 64-bit integer NUMBER(19)
BIGSERIAL 64-bit autoincrementing integer Sequence and trigger
BIT(n) Fixed-length bit string RAW(n/8)
BIT VARYING(n) Variable-length bit string RAW(n/8)
BOOLEAN, BOOL True, false or NULL CHAR(1)
BYTEA Variable-length binary data, ⇐ 2G BLOB
CHARACTER(n), CHAR(n) Fixed-length string, 1 ⇐ n ⇐ 1G CHARACTER(n), CHAR(n)
CHARACTER VARYING(n) Variable-length string, 1 ⇐ n ⇐ 1G VARCHAR2(n)
DATE Date (year, month and day) DATE Includes time part
DECIMAL(p,s), DEC(p,s) Fixed-point number NUMBER(p,s)
DOUBLE PRECISION Double-precision floating-point BINARY_DOUBLE
FLOAT4 Single-precision floating-point BINARY_FLOAT
FLOAT8 Double-precision floating-point BINARY_DOUBLE
INTEGER, INT 32-bit integer NUMBER(10)
INT2 16-bit integer NUMBER(5)
INT4 32-bit integer NUMBER(10)
INT8 64-bit integer NUMBER(20)
INTERVAL Date and time interval INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
INTERVAL unit Date and time interval NUMBER(5)
INTERVAL YEAR TO MONTH Date interval INTERVAL YEAR TO MONTH
INTERVAL DAY TO HOUR Day and time interval INTERVAL DAY(5) TO SECOND
INTERVAL DAY TO MINUTE Day and time interval INTERVAL DAY(5) TO SECOND
INTERVAL DAY TO SECOND[(p)] Day and time interval INTERVAL DAY(5) TO SECOND[(p)]
INTERVAL HOUR TO MINUTE Time interval INTERVAL DAY(5) TO SECOND
INTERVAL HOUR TO SECOND[(p)] Time interval INTERVAL DAY(5) TO SECOND[(p)]
INTERVAL MINUTE TO SECOND[(p)] Time interval INTERVAL DAY(5) TO SECOND[(p)]
MONEY Currency amount NUMBER(17,2)
NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
REAL Single-precision floating-point BINARY_FLOAT
SERIAL 32-bit autoincrementing integer Sequence and trigger
SERIAL2 16-bit autoincrementing integer Sequence and trigger
SERIAL4 32-bit autoincrementing integer Sequence and trigger
SERIAL8 64-bit autoincrementing integer Sequence and trigger
SMALLINT 16-bit integer NUMBER(5)
SMALLSERIAL 16-bit autoincrementing integer Sequence and trigger
TEXT Variable-length character data, ⇐ 1G CLOB
TIME(p) Time with fraction TIMESTAMP(p)
TIME(p) WITH TIME ZONE Time with fraction and time zone TIMESTAMP(p) WITH TIME ZONE
TIMETZ(p)
TIMESTAMP Date and time with fraction TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE Date and time with time zone TIMESTAMP(p) WITH TIME ZONE
TIMESTAMPTZ(p)
UUID Universally unique identifier CHAR(36)
VARBIT(n) Variable-length bit string RAW(n/8)
VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 1G VARCHAR2(n)
XML XML data XMLTYPE

pekný prehľad konverzií a funkcií i s linkami na konvertory tu

píše: ďobo

January 18th, 2018 o 11:46 am

chlievik: postgresql

3 odpovedí to 'Postgres 9 ETL'

Subscribe to comments with RSS or TrackBack to 'Postgres 9 ETL'.

  1. postgres

    25 Oct 18 at 23:06

  2. jak se pocita median, neni na to built-in agregacni funkce (aka AVG(), MIN()…)
    https://wiki.postgresql.org/wiki/Aggregate_Median

    postgres

    26 Oct 18 at 0:17

  3. Poznamka pre autoincrement (SERIAL):
    Od verzie 10 je tu nova syntax tohoto constraintu a to GENERATED AS IDENTITY
    https://www.postgresqltutorial.com/postgresql-identity-column/

    dobo

    7 Nov 20 at 1:35

okomentuj