DBA: kompetenti organizē sinhronizāciju un importēšanu

Lielu datu kopu kompleksai apstrādei (dažādi ETL procesi: importēšana, konvertēšana un sinhronizācija ar ārēju avotu) bieži rodas nepieciešamība īslaicīgi “atcerēties” un nekavējoties ātri apstrādāt kaut kas apjomīgs.

Tipisks šāda veida uzdevums parasti izklausās apmēram šādi: "Tieši šeit grāmatvedības nodaļa izkrauta no klienta bankas pēdējie saņemtie maksājumi, jums tie ātri jāaugšupielādē vietnē un jāsaista ar saviem kontiem"

Bet, kad šī “kaut kā” apjoms sāk mērīties simtos megabaitu un pakalpojumam jāturpina strādāt ar datubāzi 24x7, rodas daudzas blakusparādības, kas sabojās jūsu dzīvi.

Lai ar tiem tiktu galā PostgreSQL (un ne tikai tajā), var izmantot dažas optimizācijas, kas ļaus visu apstrādāt ātrāk un ar mazāku resursu patēriņu.

1. Kur nosūtīt?

Vispirms izlemsim, kur mēs varam augšupielādēt datus, kurus vēlamies “apstrādāt”.

1.1. Pagaidu galdi (TEMPORARY TABLE)

Principā PostgreSQL pagaidu tabulas ir tādas pašas kā jebkura cita. Tāpēc māņticībai patīk "Viss tur tiek saglabāts tikai atmiņā, un tas var beigties". Bet ir arī vairākas būtiskas atšķirības.

Katram savienojumam ar datu bāzi ir sava "nosaukumvieta".

Ja divi savienojumi mēģina izveidot savienojumu vienlaikus CREATE TABLE x, tad kāds noteikti dabūs neunikalitātes kļūda datu bāzes objekti.

Bet, ja abi mēģina izpildīt CREATE TEMPORARY TABLE x, tad abi to darīs normāli, un visi dabūs jūsu kopija tabulas. Un starp viņiem nebūs nekā kopīga.

"Pašiiznīcināšana", atvienojot

Kad savienojums tiek aizvērts, visas pagaidu tabulas tiek automātiski izdzēstas, tātad manuāli DROP TABLE x nav jēgas, izņemot...

Ja strādājat cauri pgbouncer darījuma režīmā, tad datu bāze turpina uzskatīt, ka šis savienojums joprojām ir aktīvs, un tajā joprojām pastāv šī pagaidu tabula.

Tāpēc, mēģinot to izveidot vēlreiz, izmantojot citu savienojumu ar pgbouncer, radīsies kļūda. Bet to var apiet, izmantojot CREATE TEMPORARY TABLE IF NOT EXISTS x.

Tiesa, labāk to nedarīt, jo tad tur var “pēkšņi” atrast datus, kas palikuši no “iepriekšējā īpašnieka”. Tā vietā daudz labāk ir izlasīt rokasgrāmatu un redzēt, ka, veidojot tabulu, ir iespējams pievienot ON COMMIT DROP - tas ir, kad darījums ir pabeigts, tabula tiks automātiski dzēsta.

Nereplicēšana

Tā kā pagaidu tabulas pieder tikai noteiktam savienojumam, tās netiek replicētas. Bet tas novērš nepieciešamību pēc dubultās datu reģistrēšanas kaudzē + WAL, tāpēc INSERT/UPDATE/DELETE tajā ir daudz ātrāk.

Bet, tā kā pagaidu tabula joprojām ir “gandrīz parasta” tabula, to nevar izveidot arī uz replikas. Vismaz pagaidām, lai gan attiecīgais plāksteris cirkulē jau sen.

1.2. NEPIETEIKTĀS GALDS

Bet kas jums jādara, piemēram, ja jums ir kaut kāds apgrūtinošs ETL process, ko nevar ieviest viena darījuma ietvaros, bet jums joprojām ir pgbouncer darījuma režīmā? ..

Vai arī datu plūsma ir tik liela, ka Vienam savienojumam nav pietiekami daudz joslas platuma no datu bāzes (lasīt, viens process uz CPU)?..

Vai arī notiek kādas operācijas asinhroni dažādos savienojumos?..

Šeit ir tikai viena iespēja - īslaicīgi izveidot nepagaidu tabulu. Vārds, jā. Tas ir:

• izveidoju “savas” tabulas ar maksimāli nejaušiem nosaukumiem, lai ne ar vienu nekrustos
• Ekstrakts: aizpildīja tos ar datiem no ārēja avota
• Pārveidot: pārveidots, aizpildīts atslēgu saistīšanas lauki
• Slodze: ielej gatavus datus mērķa tabulās
• dzēstas “manas” tabulas

Un tagad - muša ziedē. Patiesībā, visi raksti PostgreSQL notiek divreiz Sākot no vispirms WAL, pēc tam tabulas/rādītāja pamattekstā. Tas viss tiek darīts, lai atbalstītu ACID un labotu datu redzamību starp COMMIT'riekstu un ROLLBACK'nulles darījumi.

Bet mums tas nav vajadzīgs! Mums ir viss process Vai nu tas bija pilnīgi veiksmīgs, vai arī nē.. Nav svarīgi, cik starpposma darījumu būs - mēs neesam ieinteresēti “turpināt procesu no vidus”, it īpaši, ja nav skaidrs, kur tas bija.

Lai to izdarītu, PostgreSQL izstrādātāji jau 9.1 versijā ieviesa tādu lietu kā NEPIEEJĒTI galdi:

Ar šo norādi tabula tiek izveidota kā nereģistrēta. Dati, kas ierakstīti nereģistrētās tabulās, neiziet cauri priekšrakstīšanas žurnālam (skatiet 29. nodaļu), izraisot šādu tabulu strādāt daudz ātrāk nekā parasti. Tomēr viņi nav imūni pret neveiksmēm; servera kļūmes vai avārijas izslēgšanas gadījumā nereģistrēta tabula automātiski saīsināts. Turklāt nereģistrētās tabulas saturs nav replicēts uz vergu serveriem. Jebkurš indeksi, kas izveidots nereģistrētā tabulā, automātiski tiek atcelts.

Īsāk sakot, tas būs daudz ātrāk, bet, ja datu bāzes serveris “nokrīt”, tas būs nepatīkami. Bet cik bieži tas notiek, un vai jūsu ETL process zina, kā to pareizi labot “no vidus” pēc datu bāzes “atdzīvināšanas”?

Ja nē un iepriekš minētais gadījums ir līdzīgs jūsu, izmantojiet UNLOGGEDbet nekad neiespējot šo atribūtu reālās tabulās, no kura dati jums ir dārgi.

1.3. ON IESNIEGT { DZĒST RINDAS | DROP}

Šī konstrukcija ļauj norādīt automātisku darbību, kad darījums tiek pabeigts, veidojot tabulu.

uz ON COMMIT DROP Es jau rakstīju iepriekš, tas ģenerē DROP TABLE, bet ar ON COMMIT DELETE ROWS situācija ir interesantāka - tā šeit tiek ģenerēta TRUNCATE TABLE.

Tā kā visa infrastruktūra pagaidu tabulas metaapraksta glabāšanai ir tieši tāda pati kā parastajai tabulai, tad Pastāvīga pagaidu tabulu izveide un dzēšana izraisa nopietnu sistēmas tabulu "pietūkumu". pg_class, pg_attribute, pg_attrdef, pg_depend,…

Tagad iedomājieties, ka jums ir strādnieks tiešā savienojumā ar datu bāzi, kurš katru sekundi atver jaunu transakciju, izveido, aizpilda, apstrādā un dzēš pagaidu tabulu... Sistēmas tabulās būs uzkrāts pārāk daudz atkritumu, un tas radīs papildu bremzes katrai darbībai.

Vispār, nedariet to! Šajā gadījumā tas ir daudz efektīvāk CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS izņemt to no darījumu cikla - tad katra jauna darījuma sākumā tabulas jau ir pastāvēs (saglabājiet zvanu CREATE), bet būs tukšs, Pateicoties TRUNCATE (mēs saglabājām arī tā zvanu), pabeidzot iepriekšējo darījumu.

1.4. PATĪK...IESKAITOT...

Sākumā minēju, ka viens no tipiskiem pagaidu tabulu lietošanas gadījumiem ir dažāda veida importēšana - un izstrādātājs noguris kopē-ielīmē mērķa tabulas lauku sarakstu savā pagaidu tabulu deklarācijā...

Bet slinkums ir progresa dzinējspēks! Tāpēc izveidot jaunu tabulu "pamatojoties uz paraugu" tas var būt daudz vienkāršāk:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Tā kā pēc tam šajā tabulā varat ģenerēt daudz datu, meklēšana tajā nekad nebūs ātra. Bet tam ir tradicionāls risinājums - indeksi! Un jā, pagaidu tabulā var būt arī indeksi.

Tā kā bieži nepieciešamie indeksi sakrīt ar mērķa tabulas indeksiem, varat vienkārši rakstīt LIKE target_table INCLUDING INDEXES.

Ja arī vajag DEFAULT-vērtības (piemēram, lai aizpildītu primārās atslēgas vērtības), varat izmantot LIKE target_table INCLUDING DEFAULTS. Vai vienkārši - LIKE target_table INCLUDING ALL — kopē noklusējuma iestatījumus, indeksus, ierobežojumus,...

Bet šeit jums ir jāsaprot, ka, ja jūs izveidojāt nekavējoties importējiet tabulu ar indeksiem, tad datu ielāde prasīs ilgāku laikunekā tad, ja jūs vispirms aizpildāt visu un tikai pēc tam apkopojat indeksus - skatiet piemēru, kā tas tiek darīts pg_dump.

Kopumā, RTFM!

2. Kā rakstīt?

Ļaujiet man tikai teikt - izmantojiet to COPY-plūsma, nevis "iepakojums" INSERT, brīžiem paātrinājums. Varat pat tieši no iepriekš ģenerēta faila.

3. Kā apstrādāt?

Tātad, ļaujiet mūsu ievadam izskatīties apmēram šādi:

• jums ir tabula ar jūsu datubāzē saglabātajiem klienta datiem 1 miljons ierakstu
• katru dienu klients nosūta jums jaunu pilns "attēls"
• no pieredzes jūs to zināt laiku pa laikam tiek mainīti ne vairāk kā 10 XNUMX ierakstu

Klasisks šādas situācijas piemērs ir KLADR bāze — adrešu kopumā ir daudz, taču katrā iknedēļas augšupielādē ir ļoti maz izmaiņu (apdzīvotu vietu pārdēvēšana, ielu apvienošana, jaunu māju parādīšanās) pat valsts mērogā.

3.1. Pilns sinhronizācijas algoritms

Vienkāršības labad pieņemsim, ka jums pat nav nepieciešams pārstrukturēt datus — vienkārši izveidojiet tabulu vēlamajā formā, tas ir:

• noņemt viss, kas vairs nepastāv
• uzlabot viss, kas jau pastāvēja un ir jāatjaunina
• ievietot viss, kas vēl nav noticis

Kāpēc operācijas jāveic šādā secībā? Jo šādi galda izmērs pieaugs minimāli (atceries MVCC!).

DZĒST NO dst

Nē, protams, jūs varat iztikt tikai ar divām darbībām:

• noņemt (DELETE) viss kopumā
• ievietot viss no jaunā attēla

Bet tajā pašā laikā, pateicoties MVCC, Tabulas izmērs palielināsies tieši divas reizes! +1 miljona ierakstu attēlu iegūšana tabulā 10 XNUMX atjauninājuma dēļ ir tik dublējoša...

TRUNCATE dst

Pieredzējušāks izstrādātājs zina, ka visu planšetdatoru var iztīrīt diezgan lēti:

• skaidrs (TRUNCATE) visu tabulu
• ievietot viss no jaunā attēla

Metode ir efektīva, dažreiz diezgan piemērojams, bet ir problēma... Mēs vēl ilgi pievienosim 1M ierakstu, tāpēc nevaram atļauties atstāt tabulu tukšu uz visu šo laiku (kā tas notiks, neaptinot to vienā darījumā).

Kas nozīmē:

• mēs sākam ilgstošs darījums
• TRUNCATE uzliek Piekļuve ekskluzīva- bloķēšana
• mēs veicam ievietošanu ilgu laiku, un visi pārējie šajā laikā pat nevar SELECT

Kaut kas neiet labi...

MAINĪT TABLU… PĀRDĒVĒT… / NOMET TABULU…

Alternatīva ir aizpildīt visu atsevišķā jaunā tabulā un pēc tam vienkārši pārdēvēt to vecās tabulas vietā. Pāris nepatīkamas lietas:

• joprojām arī Piekļuve ekskluzīva, lai gan ievērojami mazāk laika
• visi šīs tabulas vaicājumu plāni/statistika ir atiestatīti, jāpalaiž ANALĪZE
• visas svešās atslēgas ir salauztas (FK) pie galda

Bija WIP ielāps no Simon Riggs, kas ieteica izgatavot ALTER-operācija, lai aizstātu tabulas pamattekstu faila līmenī, nepieskaroties statistikai un FK, bet nesavāca kvorumu.

DZĒST, ATJAUNINĀT, IEVIETOT

Tātad, mēs izvēlamies trīs darbību nebloķēšanas iespēju. Gandrīz trīs... Kā to izdarīt visefektīvāk?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Importēt pēcapstrādi

Tajā pašā KLADR visi mainītie ieraksti ir papildus jāpalaiž cauri pēcapstrādei – normalizēšanai, atslēgvārdu izcelšanai un reducēšanai līdz vajadzīgajām struktūrām. Bet kā tu zini - kas tieši mainījāsnesarežģot sinhronizācijas kodu, ideālā gadījumā to nepieskaroties vispār?

Ja sinhronizācijas laikā rakstīšanas piekļuve ir tikai jūsu procesam, varat izmantot trigeri, kas apkopos visas izmaiņas mūsu vietā:

-- целевые таблицы
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;

Tagad mēs varam lietot trigerus pirms sinhronizācijas sākšanas (vai iespējot tos, izmantojot 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();

Un tad mēs mierīgi izvelkam visas vajadzīgās izmaiņas no žurnālu tabulām un palaižam tās caur papildu apstrādātājiem.

3.3. Saistīto komplektu importēšana

Iepriekš mēs aplūkojām gadījumus, kad avota un galamērķa datu struktūras ir vienādas. Bet ko darīt, ja augšupielādei no ārējas sistēmas ir formāts, kas atšķiras no mūsu datu bāzē esošās krātuves struktūras?

Ņemsim kā piemēru klientu un viņu kontu glabāšanu, klasisko opciju “daudzi pret vienu”:

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

Bet lejupielāde no ārēja avota mums tiek veikta “viss vienā” formā:

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

Acīmredzot šajā versijā klientu datus var dublēt, un galvenais ieraksts ir “konts”:

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

Modelim mēs vienkārši ievietosim savus testa datus, taču atcerieties - COPY efektīvāks!

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

Vispirms izcelsim tos “izcirtņus”, uz kuriem attiecas mūsu “fakti”. Mūsu gadījumā rēķini attiecas uz klientiem:

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

Lai pareizi saistītu kontus ar klientu ID, mums vispirms ir jānoskaidro vai jāģenerē šie identifikatori. Pievienosim zem tiem laukus:

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

Izmantosim iepriekš aprakstīto tabulu sinhronizācijas metodi ar nelielu grozījumu - mēs neko neatjaunināsim un nedzēsīsim mērķa tabulā, jo mēs importējam klientus “tikai pievienošanai”:

-- проставляем в таблице импорта 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; -- прикладной ключ

Patiesībā viss ir iekšā invoice_import Tagad mums ir aizpildīts kontaktu lauks client_id, ar kuru ievietosim rēķinu.

Avots: www.habr.com