ProHoster >> Blog >> Administrimi >> DBA: organizojmë në mënyrë të duhur sinkronizimet dhe importet

DBA: organizojmë në mënyrë të duhur sinkronizimet dhe importet

Kur bëhet fjalë për përpunimin kompleks të grupeve të mëdha të dhënash (të ndryshme proceset ETL: importime, konvertime dhe sinkronizime me burime të jashtme) shpesh lind nevoja për të "mbajtur mend" përkohësisht dhe për t'i përpunuar shpejt diçka voluminoze.

Një detyrë tipike e këtij lloji zakonisht formulon si më poshtë: "Këtu financial po eksporton nga banka e klientit pagesat më të fundit të pranuara, duhet t'i ngarkojmë shpejt në faqe dhe t'i lidhim me llogaritë"

Por kur volumi i këtij "diçkaje" fillon të matet në qindra megabajt, dhe shërbimi duhet të vazhdojë të punojë me bazën si në modin 24x7, lindin shumë efekte anësore, të cilat do t'ju prishin jetën.
DBA: organizojmë në mënyrë të duhur sinkronizimet dhe importet
Për t'u përballur me ta në PostgreSQL (po ashtu edhe në të tjerët), mund të përdoren disa mundësi për optimizim, të cilat do të lejojnë të përpunoni gjithçka më shpejt dhe me më pak burime.

1. Ku të ngarkojmë?

Së pari, le të përcaktojmë se ku mund të ngarkojmë të dhënat që dëshirojmë "të procesojmë".

1.1. Tabela përkohshme (TEMPORARY TABLE)

Në princip, për PostgreSQL, tabelat e përkohshme janë si çdo tabelë tjetër. Prandaj, superstitions si "ato ruhen vetëm në memorie, dhe ajo mund të mbarojë"janë të pasakta. Po ashtu, ka disa dallime të rëndësishme.

Një "hapësirë emri" për çdo lidhje në BDB

Nëse dy lidhje përpiqen njëkohësisht të kryejnë KRIJO TABELA x, atëherë dikush patjetër do të marrë një gabim unikaliteti të objekteve të BDB.

Por nëse të dy përpiqen të kryejnë KRIJO TEMPORARY TABELA x, atëherë të dy e realizojnë normalisht, dhe secili merr ekzemplarin e vet tabelës. Dhe nuk ka asgjë të përbashkët midis tyre.

"Vetë-shkatërrimi" kur u ndale lidhja

Kur lidhja mbyllet, tĂ« gjitha tabelat pĂ«rkohshme fshihen automatikisht, prandaj nuk ka asnjĂ« kuptim tĂ« bĂ«ni DROP TABLE x pĂ«rveç


Nëse punoni përmes pgbouncer në modin e transaksionit, atëherë baza vazhdon të mendojë se kjo lidhje është ende aktive, dhe tabela përkohshme ende ekziston në të.

Prandaj, pĂ«rpjekja pĂ«r ta krijuar pĂ«rsĂ«ri, tashmĂ« nga njĂ« lidhje tjetĂ«r nĂ« pgbouncer, do tĂ« çojĂ« nĂ« njĂ« gabim. Por kjo mund tĂ« zgjidhet duke pĂ«rdorur KRIJONI TABELË TEMPORALE NËSE NUK EKZISTON x.

MegjithatĂ«, Ă«shtĂ« mĂ« mirĂ« ta bĂ«ni kĂ«shtu, sepse mund tĂ« "zbuloni papritur" ato tĂ« dhĂ«na tĂ« mbetura nga "pronari i mĂ«parshĂ«m". NĂ« vend tĂ« kĂ«saj, Ă«shtĂ« shumĂ« mĂ« mirĂ« tĂ« lexoni pĂ«rgjithĂ«sisht manualin, dhe tĂ« shikoni se gjatĂ« krijimit tĂ« tabelĂ«s ka mundĂ«sinĂ« tĂ« shtoni PËR ANGAZHIM DROP — pra, nĂ« pĂ«rfundim tĂ« transaksionit, tabela do tĂ« fshihet automatikisht.

Jo-replikimi

Për shkak të përkatësisë vetëm ndaj një lidhjeje të caktuar, tabelat përkohshme nuk replikohen. Për më tepër, kjo ndihmon për të shmangur nevojën për shkrim të dyfishtë të të dhënave në heap + WAL, kështu që INSERT/UPDATE/DELETE në to është shumë më i shpejtë.

Por për shkak se tabelat e përkohshme janë përfundimisht "dhe" tabelat "normale", ato nuk mund të krijohen as në replikë gjithashtu. Të paktën për momentin, megjithëse një patch përkatës qarkullon prej një kohe të gjatë.

1.2. Tabela të pa-journal (UNLOGGED TABLE)

Por, çfarë duhet të bëni, për shembull, nëse keni një proces ETL të rëndë, që nuk mund ta realizoni brenda një transaksioni, dhe ju keni pgbouncer në modin e transaksionit?..

Ose fluksi i të dhënave është aq i madh sa kapaciteti i një lidhje me BDB (lexo, një proces në CPU) nuk është i mjaftueshëm? ..

Ose disa operacione shkojnë asinhron në lidhje të ndryshme?..

NĂ« kĂ«tĂ« raste, njĂ« mundĂ«si e vetme mbetet — tĂ« krijoni pĂ«rkohĂ«sisht njĂ« tabelĂ« jo-pĂ«rkohĂ«she.NjĂ« ironia, po. DomethĂ«nĂ«,

  • krijoni "tabelat" tuaja me emra sa mĂ« rastĂ«sorĂ« dhe unikĂ«, pĂ«r tĂ« mos u ndĂ«rprerĂ«
  • Ekstrakt: ngarko nĂ« to tĂ« dhĂ«na nga burimi i jashtĂ«m
  • Transformo: transformuat, plotĂ«suan fushat kyçe lidhĂ«se
  • Ngarko: transferuan tĂ« dhĂ«nat e gatshme nĂ« tabelat pĂ«rkatĂ«se
  • fshini "tabelat" tuaja

Dhe tani — njĂ« lugĂ« helm. NĂ« thelb, i gjithĂ« shkrimi nĂ« PostgreSQL ndodh dy herĂ« — sĂ« pari nĂ« WAL, pastaj nĂ« trupat e tabelave/indekseve. TĂ« gjitha kĂ«to janĂ« bĂ«rĂ« pĂ«r tĂ« mbĂ«shtetur ACID dhe pĂ«r tĂ« siguruar dukshmĂ«rinĂ« e saktĂ« tĂ« tĂ« dhĂ«nave midis COMMIT'brendshme dhe RIVENDOS'brendshme transaksionet.

Por ne nuk e duam kĂ«tĂ«! TĂ« gjithĂ« procesi ose ka kaluar me sukses, ose jo.Nuk ka rĂ«ndĂ«si se sa transaksione ndĂ«rmjetĂ«sore do tĂ« ketĂ« — nuk na intereson "tĂ« vazhdojmĂ« procesin nga mesi", sidomos kur nuk Ă«shtĂ« e qartĂ« ku ishte.

Për këtë, zhvilluesit e PostgreSQL që në versionin 9.1 zbatuan një gjë si tabela të pa-journal (UNLOGGED):

Me kĂ«tĂ« pĂ«rcaktim, tabela krijohet si e pa-journal. TĂ« dhĂ«nat e shkruara nĂ« tabelat e pa-journal nuk kalojnĂ« pĂ«rmes regjistrit tĂ« parashkrimit (shih Kapitullin 29), si rezultat i sĂ« cilĂ«s kĂ«to tabela punojnĂ« shumĂ« mĂ« shpejt se tĂ« zakonshmet.MegjithatĂ«, ato nuk janĂ« tĂ« mbrojtura nga dĂ«shtimi; nĂ« rast dĂ«shtimi ose ndalimi tĂ« papritur tĂ« serverit, tabela e pa-journal automatikisht pritet.PĂ«r mĂ« tepĂ«r, pĂ«rmbajtja e tabelĂ«s sĂ« pa-journal nuk replikon nĂ« serverĂ« tĂ« drejtpĂ«rdrejtĂ«. Çdo indeks i krijuar pĂ«r njĂ« tabelĂ« tĂ« pasiguruar automatikisht bĂ«het i tillĂ«.

PĂ«r tĂ« shpejtuar, do tĂ« jetĂ« shumĂ« mĂ« shpejt, por nĂ«se serveri i tĂ« dhĂ«nave "bjerĂ«" — do tĂ« jetĂ« e pakĂ«ndshme. Por a ndodh shpesh kjo, dhe a arrin procesi juaj ETL ta pĂ«rmirĂ«sojĂ« "nga mesi" pas "ringjalljes" sĂ« DB?..

NĂ«se jo, dhe rasti lart Ă«shtĂ« i ngjashĂ«m me tuajin — pĂ«rdorni UNLOGGED, por kurrĂ« mos e aktivizoni kĂ«tĂ« atribut nĂ« tabelat reale, tĂ« dhĂ«nat nga tĂ« cilat ju rĂ«ndojnĂ«.

1.3. ON COMMIT { DELETE ROWS | DROP }

Kjo ndërtim lejon që gjatë krijimit të tabelës të caktosh sjelljen automatike pas përfundimit të transaksionit.

PĂ«r PËR ANGAZHIM DROP e kam shkruar mĂ« parĂ«, ai gjeneron DROP TABLE, por me PËR ANGAZHIM Fshi Rreshtat situata Ă«shtĂ« mĂ« interesante — kĂ«tu gjenerohet TRUNCATE TABLE.

NĂ«se e gjithĂ« infrastruktura e ruajtjes sĂ« meta pĂ«rshkrimit tĂ« tabelĂ«s pĂ«rkohshme Ă«shtĂ« pikĂ«risht e njĂ«jtĂ« si ajo e normales, atĂ«herĂ« krijimi dhe fshirja e vazhdueshme e tabelave tĂ« pĂ«rkohshme çon nĂ« "shkĂ«mbim" tĂ« madh tĂ« tabelave sistemike pg_class, pg_attribute, pg_attrdef, pg_depend,


Tani imagjinoni se keni njĂ« punĂ«tor qĂ« ka njĂ« lidhje tĂ« drejtpĂ«rdrejtĂ« me DB, i cili çdo sekondĂ« hap njĂ« transaksion tĂ« ri, krijon, mbush, pĂ«rpunon dhe fshin njĂ« tabelĂ« tĂ« pĂ«rkohshme
 Do tĂ« akumulohet tepri nĂ« tabelat sistemike, dhe kjo do tĂ« shkaktojĂ« ngadalĂ«sime nĂ« çdo operacion.

NĂ« pĂ«rgjithĂ«si, mos e bĂ«ni kĂ«shtu! NĂ« kĂ«tĂ« rast, Ă«shtĂ« shumĂ« mĂ« efektive CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ta nxjerrĂ«sh jashtĂ« ciklit tĂ« transaksioneve — atĂ«herĂ« nĂ« fillim tĂ« çdo transaksioni tĂ« ri, tabelat do tĂ« ekzistojnĂ« (shkurtim i thirrjes KRIJO), por do tĂ« jenĂ« bosh, falĂ« TRUNCATE (thirrjen e tij gjithashtu e kursyem) pas pĂ«rfundimit tĂ« transaksionit tĂ« mĂ«parshĂ«m.

1.4. LIKE
 INCLUDING 


E pĂ«rmenda nĂ« fillim, se njĂ« nga rastet tipike pĂ«r tabelat e pĂ«rkohshme Ă«shtĂ« lloje tĂ« ndryshme importesh — dhe zhvilluesi ngre duar e kopjon listĂ«n e fushave tĂ« tabelĂ«s qĂ«llimore pĂ«r deklaratĂ«n e tabelĂ«s sĂ« tij tĂ« pĂ«rkohshme


Por lenia — Ă«shtĂ« motor progresi! Prandaj krijimi i njĂ« tabele tĂ« re "nĂ« bazĂ« tĂ« modelit" mund tĂ« bĂ«het shumĂ« mĂ« lehtĂ«:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Duke pasur parasysh se nĂ« kĂ«tĂ« tabelĂ« mund tĂ« gjenerohen shumĂ« tĂ« dhĂ«na, kĂ«rkimet nĂ« tĂ« do tĂ« jenĂ« aspak tĂ« shpejta. Por pĂ«r kĂ«tĂ« ka njĂ« zgjidhje tradicionale — indikes! Po, po ashtu, tabela e pĂ«rkohshme mund tĂ« ketĂ« indikes.

Duke qenë se, shpesh, indiket e nevojshme përputhen me indiket e tabelës së qëllimit, mund të shkruani thjesht SI target_table DUKE INDICES.

NĂ«se ju nevojiten edhe DEFAULT-vlerat (pĂ«r shembuj, pĂ«r tĂ« mbushur vlerat e çelĂ«sit primar), mund tĂ« pĂ«rdorni SI target_table PËRFSHIRË DHE TË DHËNAT E KUFIZUARA. Ose thjesht — SI target_table PËRFSHIJË GJITHA — do tĂ« kopjojĂ« defoltet, indiket, kufizimet,


Por nĂ« kĂ«tĂ« rast, duhet tĂ« kuptohet se nĂ«se e keni krijuar tabelĂ«n e importit menjĂ«herĂ« me indikes, tĂ« dhĂ«nat do tĂ« futen mĂ« ngadalĂ«, sesa nĂ«se fillimisht i futni tĂ« gjitha, dhe mĂ« pas i aplikoni indiket — shikoni pĂ«r tĂ« mĂ«suar se si e bĂ«n pg_dump.

Në përgjithësi, RTFM!

2. Si të shkruani?

Do ta them thjesht — pĂ«rdorni COPY-rrjedhĂ«n nĂ« vend tĂ« "kufizĂ«ve" INSERT, pĂ«rshpejtim me pĂ«rqindje. Mund tĂ« bĂ«het edhe direkt nga njĂ« skedar i formuar paraprakisht.

3. Si të përpunoni?

Pra, le të thjeshtojmë se këtu kemi një hyrje të ngjashme:

  • keni njĂ« tabelĂ« nĂ« bazĂ« tĂ« tĂ« dhĂ«nave me 1M regjistrime
  • çdo ditĂ« klienti ju dĂ«rgon njĂ« tĂ« plotĂ« "model"
  • sipĂ«r pĂ«rvojĂ«s, e dini se nga njĂ« herĂ« nĂ« tjetĂ«r ndryshojnĂ« jo mĂ« shumĂ« se 10K regjistrime

NjĂ« shembull klasik i njĂ« situate tĂ« tillĂ« Ă«shtĂ« baza KLA-DR — gjithsej adresa tĂ« shumta, por nĂ« çdo pĂ«rpunim tĂ« pĂ«rjavshĂ«m tĂ« ndryshimeve (ndryshimet e emrave tĂ« vendeve, bashkimeve tĂ« rrugĂ«ve, shfaqja e shtĂ«pive tĂ« reja) ka shumĂ« pak madje edhe nĂ« shkallĂ« tĂ« gjatĂ«.

3.1. Algoritmi i sinkronizimit të plotë

PĂ«r thjeshtĂ«si, le tĂ« themi se nuk Ă«shtĂ« e nevojshme ta rishtrosh tĂ« dhĂ«nat — thjesht t'i sillni tabelĂ«s pamjen e duhur, dmth:

  • tĂ« fshijĂ« gjithĂ« ato qĂ« nuk janĂ« mĂ«
  • pĂ«rditĂ«soni gjithĂ« ato qĂ« tashmĂ« ishin dhe nevojiten pĂ«r pĂ«rditĂ«sim
  • futni gjithĂ« ato qĂ« akoma nuk ishin

Pse pikërisht në këtë rend do të duhej të bënit operacionet? Sepse kështu do të rritet sa më pak madhësia e tabelës (mbani mend për MVCC!).

DELETE FROM dst

Jo, natyrisht mund ta përdorni vetëm dy operacione:

  • tĂ« fshijĂ« (DELETE) tĂ« gjithĂ«
  • futni tĂ« gjithĂ« nga modeli i ri

Por ndodhi qĂ« falĂ« MVCC, madhĂ«sia e tabelĂ«s do tĂ« rritet pikĂ«risht dyfish! TĂ« merrni +1M modele regjistrimesh nĂ« tabelĂ« pĂ«r shkak tĂ« pĂ«rditĂ«simit tĂ« 10K — nuk Ă«shtĂ« tĂ«rheqja mĂ« e madhe


TRUNCATE dst

Një zhvillues më me përvojë e di se është mjaft e lirë të fshini tërë tabelën:

  • pastroni (TRUNCATE) tabelĂ«n e tĂ«rĂ«
  • futni tĂ« gjithĂ« nga modeli i ri

Metoda Ă«shtĂ« efektive, ndonjĂ«herĂ« plotĂ«sisht e zbatueshme, por ka njĂ« shqetĂ«sim
 TĂ« futni 1M regjistrime do tĂ« zgjasĂ« shumĂ«, prandaj nuk mund ta lejojmĂ« tabelĂ«n tĂ« jetĂ« bosh pĂ«r gjithĂ« kĂ«tĂ« kohĂ« (siç do ndodhte pa u vendosur nĂ« njĂ« transaksion tĂ« vetĂ«m).

Dhe kështu:

  • jemi duke filluar transaksionin e gjatĂ«
  • TRUNCATE ngarkon bllokimin AccessExclusivene e bĂ«jmĂ« ngadalĂ« futjen, ndĂ«rsa tĂ« tjerĂ«t gjatĂ« kĂ«saj kohe
  • nuk mund as Nuk Ă«shtĂ« gjithçka nĂ« rregull... SELECT

Dicka nuk po shkon mirë...

ALTER TABLE
 RENAME
 / DROP TABLE 


Një mundësi është të ngarkoni gjithçka në një tabelë të re, dhe pastaj thjesht ta ribeni me emrin e tabelës së vjetër. Disa detaje të pakëndshme:

  • edhe kjo bllokimin AccessExclusive, ndonĂ«se ndjeshĂ«m mĂ« pak nĂ« kohĂ«
  • tĂ« gjithĂ« planet e kĂ«rkesave/statistikĂ«n e kĂ«saj tabele do tĂ« humbasin, duhet tĂ« ekzekutohet ANALYZE
  • tĂ« gjithĂ« çelĂ«sat e jashtĂ«m (FK) pĂ«r tabelĂ«n

Ishte një patch WIP nga Simon Riggs, i cili propozonte të bënte ALTER-operacion për të zëvendësuar trupin e tabelës në nivelin e skedarit, pa prekur statistikën dhe FK, por nuk arriti të mbledhë shumicën.

DELETE, UPDATE, INSERT

Pra, ndalojmë në variantin që nuk bllokon nga tre operacione. Pothuajse tre
 Si ta bëjmë këtë sa më efikasht?

-- gjithçka bëhet brenda transaksionit, në mënyrë që askush të mos shohë "gjendjet" e "ndërmjetme"
BEGIN;

-- krijojmë një tabelë përkohshme me të dhënat e importuara
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- sipas modelit, së bashku me indeksat
) ON COMMIT DROP; -- jashtë transaksionit nuk na nevojitet

-- shpejt e shpejt e derdhim imazhin e ri përmes COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- fshini ata që nuk mungojnë
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- fushat e çelësit të parë
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "anti-join"

-- përditësojmë të mbeturit
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- nuk ka nevojë të përditësojmë përputhjet

-- shtojmë të munguarit
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2. Pastrimi i importit

Në të njëjtin KLADr, të gjithë regjistrat e ndryshuar duhet të kalojnë përmes pastrimit - të normalizohen, të nxjerrin fjalë kyçe, të sjellin në struktura të nevojshme. Por si mund ta dimë - çfarë është ndryshuar, pa e komplikuar kodin e sinkronizimit, në mënyrë ideale, madje pa e prekur atë?

Nëse akseset për shkrim gjatë sinkronizimit janë vetëm për procesin tuaj, mund të përdorni një trigger, i cili do të mbledhë të gjitha ndryshimet për ne:

-- tabelat e synuara
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- tabelat me historinë e ndryshimeve
CREATE TABLE kladr$log(
  ro kladr, -- këtu ruajmë imazhet e plota të regjistrave të vjetër/të rinj
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- funksioni i përbashkët për logimin e ndryshimeve
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- kontrollojmë nevojën për logimin kur përditësohet një regjistër
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- krijojmë një regjistër logu
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Tani ne mund tĂ« aplikojmĂ« triggers para fillimit tĂ« sinkronizimit (ose t’i aktivizojmĂ« pĂ«rmes ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

Dhe pastaj qetë nga tabelat log nxjerrim të gjitha ndryshimet që na nevojiten dhe i kalojmë përmes përpunuesve të tjerë.

3.3. Importi i grupeve të lidhura

Më sipër kemi shqyrtuar rastet kur strukturat e të dhënave të burimit dhe të pranimit bien ndesh. Por çfarë të bëjmë, nëse eksportimi nga një sistem të jashtëm ka një format ndryshe nga struktura që kemi në bazën tonë?

Merrni për shembull ruajtjen e klientëve dhe faturave për ta, një variant klasik 'shumë-në-një':

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

Dhe kështu eksportimi nga një burim të jashtëm vjen në formën e 'gjithçka në një':

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

E qartë se të dhënat për klientët mund të dyfishohen në këtë variant, dhe regjistri kryesor është 'fatura':

0123456789;Vasia;A-01;2020-03-16;1000.00
9876543210;Petya;A-02;2020-03-16;666.00
0123456789;Vasia;B-03;2020-03-16;9999.00

PĂ«r modelin thjesht do tĂ« fusim tĂ« dhĂ«nat tona tĂ« testimit, por mbajmĂ« mend— COPY mĂ« efikas!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Vasia', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Petya', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Vasia', 'B-03', '2020-03-16', 9999.00);

Së pari, do të nxjerrim ato 'segmentet', të cilat referojnë 'faktet' tona. Në rastin tonë, faturat referojnë tek klientët:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- mund të jetë thjesht SELECT DISTINCT, nëse të dhënat janë të përmbledhura
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Për të lidhur fatura me ID e klientëve, na duhet së pari t'i njohim ose t'i gjenerojmë këto identifikues. Ta shtojmë atyre fushat:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

Do të shfrytëzojmë metodën e përshkruar më lart për sinkronizimin e tabelave me disa ndryshime - nuk do të përditësojmë dhe hiqnim asgjë nga tabela e destinacionit, pasi importi i klientëve është "append-only":

-- vendosim në tabelën e importit ID-të e regjistrimeve ekzistuese
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- çelësi unik

-- Shtojmë regjistrimet që mungojnë dhe vendosim ID-të e tyre
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- nëse ID nuk është vendosur
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- çelësi unik

-- vendosim ID-të e klientëve për regjistrimet e faturave
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- çelësi aplikativ

Pra, gjithçka - në invoice_import tani kemi mbushur fushën lidhëse client_id, me të cilën do të vendosim faturën.

Burimi: habr.com

Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« đŸ”„ Bli njĂ« hosting tĂ« besueshĂ«m pĂ«r faqet me mbrojtje DDoS, VPS VDS serverĂ« | ProHoster