ProHoster > Блог > Administrado > DBA: kompetente organizi sinkronigojn kaj importadojn

DBA: kompetente organizi sinkronigojn kaj importadojn

Por kompleksa prilaborado de grandaj datenoj (malsamaj ETL-procezoj: importado, konvertiĝo kaj sinkronigo kun ekstera fonto) ofte estas bezono provizore "memori" kaj tuj rapide procesi io volumena.

Tipa tasko de ĉi tiu speco kutime sonas io tiel: "Ĉi tie kontada fako malŝarĝita de la klienta banko la lastaj ricevitaj pagoj, vi devas rapide alŝuti ilin al la retejo kaj ligi ilin al viaj kontoj."

Sed kiam la volumo de ĉi tiu "io" komencas mezuri en centoj da megabajtoj, kaj la servo devas daŭre labori kun la datumbazo 24x7, multaj kromefikoj ŝprucas, kiuj ruinigos vian vivon.
DBA: kompetente organizi sinkronigojn kaj importadojn
Por trakti ilin en PostgreSQL (kaj ne nur en ĝi), vi povas uzi iujn optimumojn, kiuj permesos vin prilabori ĉion pli rapide kaj kun malpli da konsumo de rimedoj.

1. Kien sendi?

Unue, ni decidu kie ni povas alŝuti la datumojn, kiujn ni volas "prilabori".

1.1. Provizoraj tabeloj (TEMPORARY TABLE)

Principe, por PostgreSQL provizoraj tabeloj estas la samaj kiel ajna alia. Tial, superstiĉoj ŝatas "Ĉio tie estas konservita nur en memoro, kaj ĝi povas finiĝi". Sed estas ankaŭ pluraj gravaj diferencoj.

Via propra "nomspaco" por ĉiu konekto al la datumbazo

Se du konektoj provu konektiĝi samtempe CREATE TABLE x, tiam iu certe ricevos ne-unikeca eraro datumbazaj objektoj.

Sed se ambaŭ provas ekzekuti CREATE TEMPORARY TABLE x, tiam ambaŭ faros ĝin normale, kaj ĉiuj ricevos via kopio tabloj. Kaj estos nenio komuna inter ili.

"Memdetruo" kiam malkonekti

Kiam la konekto estas fermita, ĉiuj provizoraj tabeloj estas aŭtomate forigitaj, do mane DROP TABLE x estas nenio krom...

Se vi laboras pgbouncer en transakcia reĝimo, tiam la datumbazo daŭre kredas, ke tiu ĉi konekto ankoraŭ estas aktiva, kaj en ĝi ĉi tiu provizora tabelo ankoraŭ ekzistas.

Tial, provi krei ĝin denove, de malsama konekto al pgbouncer, rezultos en eraro. Sed ĉi tio povas esti evitita per uzado CREATE TEMPORARY TABLE IF NOT EXISTS x.

Vere, estas pli bone ne fari ĉi tion ĉiuokaze, ĉar tiam vi povas "subite" trovi tie la datumojn restantajn de la "antaŭa posedanto". Anstataŭe, estas multe pli bone legi la manlibron kaj vidi, ke kreante tabelon eblas aldoni ON COMMIT DROP - tio estas, kiam la transakcio finiĝas, la tablo estos aŭtomate forigita.

Ne-reproduktado

Ĉar ili apartenas nur al specifa konekto, provizoraj tabeloj ne estas reproduktitaj. Sed tio forigas la bezonon de duobla registrado de datumoj en amaso + WAL, do INSERT/UPDATE/DELETE en ĝi estas multe pli rapida.

Sed ĉar provizora tablo ankoraŭ estas "preskaŭ ordinara" tablo, ĝi ankaŭ ne povas esti kreita sur kopio. Almenaŭ nuntempe, kvankam la responda flikaĵo cirkulas delonge.

1.2. NELOGIGITA TABLO

Sed kion vi faru, ekzemple, se vi havas ian ĝenan ETL-procezon kiu ne povas esti efektivigita ene de unu transakcio, sed vi ankoraŭ havas pgbouncer en transakcia reĝimo? ..

Aŭ la datumfluo estas tiel granda ke Ne estas sufiĉe da bendolarĝo sur unu konekto de datumbazo (legu, unu procezo per CPU)?...

Aŭ kelkaj operacioj okazas nesinkrone en malsamaj rilatoj?..

Estas nur unu opcio ĉi tie - provizore krei ne-provizoran tablon. Vortludo, jes. Tio estas:

  • kreis "miajn" tabelojn kun maksimume hazardaj nomoj por ne kruciĝi kun iu ajn
  • eltiraĵo: plenigis ilin per datumoj de ekstera fonto
  • Transformi: konvertita, plenigita en ŝlosilaj ligaj kampoj
  • ŝarĝo: verŝis pretajn datumojn en celtabelojn
  • forigis "miajn" tabelojn

Kaj nun - muŝo en la ungvento. Fakte, ĉiuj skriboj en PostgreSQL okazas dufoje - unue en WAL, poste en la tabelajn/indeksajn korpojn. Ĉio ĉi estas farita por subteni ACID kaj korekti datuman videblecon inter COMMIT— nuksa kaj ROLLBACK'nulaj transakcioj.

Sed ni ne bezonas ĉi tion! Ni havas la tutan procezon Aŭ ĝi estis tute sukcesa aŭ ĝi ne estis.. Ne gravas kiom da mezaj transakcioj estos - ni ne interesiĝas pri "daŭrigi la procezon de la mezo", precipe kiam ne estas klare kie ĝi estis.

Por fari tion, la programistoj de PostgreSQL, reen en versio 9.1, enkondukis tian aferon kiel NELOGEGITAJ tabeloj:

Kun ĉi tiu indiko, la tabelo estas kreita kiel neregistrita. Datumoj skribitaj al neregistritaj tabeloj ne pasas tra la antaŭskriba protokolo (vidu Ĉapitro 29), kaŭzante tiajn tabelojn. labori multe pli rapide ol kutime. Tamen, ili ne estas imunaj kontraŭ fiasko; en kazo de servilo fiasko aŭ kriz-halto, neregistrita tablo aŭtomate detranĉita. Aldone, la enhavo de la neregistrita tabelo ne reproduktita al sklavaj serviloj. Ajnaj indeksoj kreitaj sur neregistrita tabelo aŭtomate fariĝas neregistrita.

Mallonge ĝi estos multe pli rapida, sed se la datumbaza servilo "falos", ĝi estos malagrabla. Sed kiom ofte tio okazas, kaj ĉu via ETL-procezo scias kiel korekti ĉi tion ĝuste "de la mezo" post "revigligado" de la datumbazo?...

Se ne, kaj la supra kazo estas simila al via, uzu UNLOGGEDsed neniam ne ebligu ĉi tiun atributon sur realaj tabeloj, la datumoj de kiuj estas kara al vi.

1.3. ON COMMIT { FORIGI VICojn | FALI}

Ĉi tiu konstruo permesas vin specifi aŭtomatan konduton kiam transakcio estas finita dum kreado de tabelo.

pri ON COMMIT DROP Mi jam skribis supre, ĝi generas DROP TABLE, sed kun ON COMMIT DELETE ROWS la situacio estas pli interesa - ĝi estas generita ĉi tie TRUNCATE TABLE.

Ĉar la tuta infrastrukturo por stoki la metapriskribon de provizora tabelo estas ĝuste la sama kiel tiu de regula tabelo, tiam Konstanta kreado kaj forigo de provizoraj tabeloj kondukas al severa "ŝveliĝo" de sistemaj tabeloj pg_class, pg_attribute, pg_attrdef, pg_depend,...

Nun imagu, ke vi havas laboriston en rekta konekto al la datumbazo, kiu malfermas novan transakcion ĉiun sekundon, kreas, plenigas, prilaboras kaj forigas provizoran tabelon... Estos troo da rubo akumulita en la sistemaj tabeloj, kaj ĉi tio kaŭzos ekstrajn bremsojn por ĉiu operacio.

Ĝenerale, ne faru ĉi tion! En ĉi tiu kazo ĝi estas multe pli efika CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS elprenu ĝin el la transakcia ciklo - tiam je la komenco de ĉiu nova transakcio la tabloj jam estas ekzistos (konservu vokon CREATE), sed estos malplena, danke al TRUNCATE (ni ankaŭ konservis ĝian vokon) kiam kompletigante la antaŭan transakcion.

1.4. KAJ...INKLUDE...

Mi menciis komence, ke unu el la tipaj uzkazoj por provizoraj tabeloj estas diversaj specoj de importado - kaj la programisto lace kopias-algluas la liston de kampoj de la celtabelo en la deklaron de sia provizora...

Sed maldiligento estas la motoro de progreso! Tial krei novan tabelon "surbaze de specimeno" ĝi povas esti multe pli simpla:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Ĉar vi tiam povas generi multajn datumojn en ĉi tiun tabelon, serĉi tra ĝi neniam estos rapida. Sed ekzistas tradicia solvo al tio - indeksoj! Kaj, jes, provizora tabelo ankaŭ povas havi indeksojn.

Ĉar, ofte, la bezonataj indeksoj koincidas kun la indeksoj de la celtabelo, vi povas simple skribi LIKE target_table INCLUDING INDEXES.

Se vi ankaŭ bezonas DEFAULT-valoroj (ekzemple, por plenigi la primarajn ŝlosilvalorojn), vi povas uzi LIKE target_table INCLUDING DEFAULTS. Aŭ simple - LIKE target_table INCLUDING ALL — kopias defaŭltojn, indeksojn, limojn,...

Sed ĉi tie vi devas kompreni tion se vi kreis importi tabelon tuj kun indeksoj, tiam la datumoj daŭros pli longe por ŝargiol se vi unue plenigas ĉion, kaj nur poste ruligu la indeksojn - rigardu kiel ĝi faras tion kiel ekzemplon pg_dump.

Ĝenerale RTFM!

2. Kiel skribi?

Mi nur diru - uzu ĝin COPY-fluo anstataŭ "paki" INSERT, akcelo kelkfoje. Vi povas eĉ rekte de antaŭgenerita dosiero.

3. Kiel procesi?

Do, ni lasu nian enkondukon aspekti kiel ĉi tio:

  • vi havas tabelon kun klientaj datumoj konservitaj en via datumbazo 1M rekordoj
  • ĉiutage kliento sendas al vi novan plena "bildo"
  • el sperto vi scias tion de tempo al tempo ne pli ol 10K rekordoj estas ŝanĝitaj

Klasika ekzemplo de tia situacio estas KLADR bazo — estas entute multe da adresoj, sed en ĉiu semajna alŝuto estas tre malmultaj ŝanĝoj (alinomado de loĝlokoj, kunigo de stratoj, apero de novaj domoj) eĉ je nacia skalo.

3.1. Plena sinkroniga algoritmo

Por simpleco, ni diru, ke vi eĉ ne bezonas restrukturi la datumojn - nur alportu la tabelon en la deziratan formon, tio estas:

  • forigi ĉio, kio ne plu ekzistas
  • ĝisdatigo ĉio, kio jam ekzistis kaj devas esti ĝisdatigita
  • enmeti ĉio, kio ankoraŭ ne okazis

Kial la operacioj estu faritaj en ĉi tiu ordo? Ĉar jen kiel la tablograndeco kreskos minimume (memoru MVCC!).

FORIGI EL dst

Ne, kompreneble vi povas elteni per nur du operacioj:

  • forigi (DELETE) ĉio ĝenerale
  • enmeti ĉio el la nova bildo

Sed samtempe, danke al MVCC, La grandeco de la tablo pliiĝos ĝuste dufoje! Akiri +1M-bildojn de rekordoj en la tabelo pro 10K ĝisdatigo estas tiel tiel redunda...

TUNKCI dst

Pli sperta programisto scias, ke la tuta tablojdo povas esti purigita sufiĉe malmultekoste:

  • klara (TRUNCATE) la tuta tablo
  • enmeti ĉio el la nova bildo

La metodo estas efika, foje sufiĉe aplikebla, sed estas problemo... Ni aldonos 1M-rekordojn dum longa tempo, do ni ne povas permesi lasi la tablon malplena dum ĉi tiu tuta tempo (kiel okazos sen envolvi ĝin en ununura transakcio).

Kio signifas:

  • ni komencas longdaŭra transakcio
  • TRUNCATE trudas Aliro Ekskluziva-blokado
  • ni faras la enmeton dum longa tempo, kaj ĉiuj aliaj ĉi-momente eĉ ne povas SELECT

Io ne iras bone...

ALTER TABLO... RENOMIGO... / FORIGI TABLON...

Alternativo estas plenigi ĉion en apartan novan tabelon, kaj poste simple renomi ĝin anstataŭ la malnova. Paro da aĉaj aferoj:

  • ankoraŭ ankaŭ Aliro Ekskluziva, kvankam signife malpli da tempo
  • ĉiuj demandplanoj/statistikoj por ĉi tiu tabelo estas rekomencigitaj, bezonas ruli ANALYZE
  • ĉiuj fremdaj ŝlosiloj estas rompitaj (FK) al la tablo

Estis WIP-peceto de Simon Riggs kiu sugestis fari ALTER-operacio por anstataŭigi la tabelkorpon ĉe la dosiernivelo, sen tuŝi statistikon kaj FK, sed ne kolektis kvorumon.

FORIGI, ĜISdatigi, Enmeti

Do, ni aranĝas la ne-blokan opcion de tri operacioj. Preskaŭ tri... Kiel fari tion plej efike?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
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); -- незачем обновлять совпадающие

-- вставляем отсутствующие
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. Importi post-traktadon

En la sama KLADR, ĉiuj ŝanĝitaj registroj devas esti aldone rulitaj per post-traktado - normaligitaj, ŝlosilvortoj elstarigitaj, kaj reduktitaj al la bezonataj strukturoj. Sed kiel vi scias - kio ĝuste ŝanĝiĝissen kompliki la sinkronigan kodon, ideale tute sen tuŝi ĝin?

Se nur via procezo havas skriban aliron en la momento de sinkronigo, tiam vi povas uzi ellasilon, kiu kolektos ĉiujn ŝanĝojn por ni:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

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

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  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;

Nun ni povas apliki ellasilon antaŭ ol komenci sinkronigon (aŭ ebligi ilin per 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();

Kaj tiam ni trankvile ĉerpas ĉiujn ŝanĝojn, kiujn ni bezonas el la protokolaj tabeloj kaj kuras ilin per pliaj pritraktiloj.

3.3. Importado de Ligitaj Aroj

Supre ni konsideris kazojn kiam la datumstrukturoj de la fonto kaj celo estas la samaj. Sed kio se la alŝuto de ekstera sistemo havas formaton malsaman al la stoka strukturo en nia datumbazo?

Ni prenu kiel ekzemplon la stokadon de klientoj kaj iliaj kontoj, la klasikan opcion "multaj-al-unu":

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)
);

Sed la elŝuto de ekstera fonto venas al ni en la formo de "ĉio en unu":

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

Evidente, klientdatenoj povas esti duobligitaj en ĉi tiu versio, kaj la ĉefa rekordo estas "konto":

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

Por la modelo, ni simple enmetos niajn testajn datumojn, sed memoru - COPY pli efika!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

Unue, ni reliefigu tiujn "tranĉojn" al kiuj rilatas niaj "faktoj". En nia kazo, fakturoj rilatas al klientoj:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Por ĝuste asocii kontojn kun klientaj identigiloj, ni unue devas eltrovi aŭ generi ĉi tiujn identigilojn. Ni aldonu kampojn sub ili:

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

Ni uzu la tabelan sinkronigan metodon priskribitan supre kun malgranda amendo - ni ĝisdatigos aŭ forigos ion ajn en la cela tabelo, ĉar ni importas klientojn "nur almetas":

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

Fakte, ĉio estas en invoice_import Nun ni havas la kontaktokampon plenigita client_id, per kiu ni enigos la fakturon.

fonto: www.habr.com

Aldoni komenton