DBA: синхрондау мен импорттауды сауатты ұйымдастыру

Үлкен деректер жиынын кешенді өңдеу үшін (әртүрлі ETL процестері: импорттау, түрлендіру және сыртқы көзбен синхрондау) жиі қажеттілік туындайды уақытша «есте сақтау» және дереу жылдам өңдеу көлемді нәрсе.

Мұндай типтік тапсырма әдетте келесідей естіледі: «Осы жерде бухгалтерлік бөлім клиент банктен түсірілген соңғы алынған төлемдер үшін сіз оларды веб-сайтқа жылдам жүктеп салуыңыз және оларды есептік жазбаларыңызбен байланыстыруыңыз керек».

Бірақ бұл «бір нәрсенің» көлемі жүздеген мегабайтпен өлшене бастағанда және қызмет 24x7 деректер қорымен жұмыс істеуді жалғастыру керек, сіздің өміріңізді бұзатын көптеген жанама әсерлер пайда болады.

Олармен PostgreSQL-де (тек онда ғана емес) жұмыс істеу үшін сіз бәрін тезірек және ресурстарды аз тұтынумен өңдеуге мүмкіндік беретін кейбір оңтайландыруларды пайдалана аласыз.

1. Қайда жөнелту керек?

Алдымен, «өңдегіміз» келетін деректерді қайда жүктеп салуға болатынын шешейік.

1.1. Уақытша кестелер (Уақытша кесте)

Негізінде PostgreSQL үшін уақытша кестелер кез келген басқа кестелермен бірдей. Сондықтан, ырымдар ұнайды «Онда бәрі тек жадта сақталады және ол аяқталуы мүмкін». Бірақ бірнеше маңызды айырмашылықтар да бар.

Дерекқорға әрбір қосылым үшін жеке «аттар кеңістігі».

Екі қосылым бір уақытта қосылуға тырысса CREATE TABLE x, сонда біреу міндетті түрде алады бірегейлік қатесі дерекқор объектілері.

Бірақ егер екеуі де орындауға тырысса CREATE TEMPORARY TABLE x, содан кейін екеуі де мұны әдеттегідей жасайды және барлығы алады сіздің көшірмеңіз кестелер. Және олардың арасында ортақ ештеңе болмайды.

Ажырату кезінде «өзін-өзі жою».

Байланыс жабылған кезде, барлық уақытша кестелер автоматты түрде жойылады, сондықтан қолмен DROP TABLE x басқа мағынасы жоқ...

Егер сіз жұмыс істеп жатсаңыз pgbouncer транзакция режимінде, содан кейін дерекқор бұл қосылым әлі де белсенді деп есептейді және онда бұл уақытша кесте әлі де бар.

Сондықтан, оны басқа қосылымнан pgbouncerге қайта жасау әрекеті қатеге әкеледі. Бірақ оны пайдалану арқылы айналып өтуге болады CREATE TEMPORARY TABLE IF NOT EXISTS x.

Рас, мұны бәрібір жасамағаныңыз жөн, өйткені сіз «бұрынғы иесінен» қалған деректерді «кенеттен» таба аласыз. Оның орнына нұсқаулықты оқып, кестені құру кезінде қосуға болатынын көрген әлдеқайда жақсы ON COMMIT DROP - яғни транзакция аяқталған кезде кесте автоматты түрде жойылады.

Көшірмеу

Олар тек белгілі бір қосылымға жататындықтан, уақытша кестелер қайталанбайды. Бірақ бұл деректерді қосарлы жазу қажеттілігін жояды үймеде + WAL, сондықтан оған INSERT/UPDATE/DELETE айтарлықтай жылдамырақ.

Бірақ уақытша кесте әлі де «кәдімгі дерлік» кесте болғандықтан, оны көшірмеде де жасау мүмкін емес. Кем дегенде, әзірге, сәйкес патч ұзақ уақыт бойы айналымда болғанымен.

1.2. БӨЛІМДЕГЕН КЕСТЕЛ

Бірақ, мысалы, сізде бір транзакцияда орындалмайтын ауыр ETL процесі болса, бірақ сізде әлі де бар болса, не істеу керек. pgbouncer транзакция режимінде? ..

Немесе деректер ағыны соншалықты үлкен Бір қосылымда өткізу қабілеті жеткіліксіз дерекқордан (оқылатын, бір процессорға бір процесс)?..

Немесе кейбір операциялар жүріп жатыр асинхронды түрде әртүрлі байланыстарда?..

Мұнда бір ғана нұсқа бар - уақытша емес кестені уақытша жасаңыз. Пун, иә. Яғни:

• ешкіммен қиылыспау үшін максималды кездейсоқ атаулары бар «өзімнің» кестелерін жасады
• сіріндісі: оларды сыртқы көзден алынған деректермен толтырды
• Трансформация: түрлендірілген, негізгі байланыстыру өрістері толтырылған
• жүк: дайын деректерді мақсатты кестелерге құйды
• «менің» кестелерім жойылды

Ал енді – шыбын-шіркей. Негізінде, PostgreSQL-дегі барлық жазулар екі рет орындалады - WAL-да бірінші, содан кейін кесте/индекс денелеріне. Мұның бәрі ACID-ті қолдау және деректер арасындағы дұрыс көріну үшін жасалады COMMIT'жаңғақ және ROLLBACK'нөлдік транзакциялар.

Бірақ бұл бізге керек емес! Бізде бүкіл процесс бар Ол толығымен сәтті болды немесе олай болмады.. Қанша аралық транзакциялар болатыны маңызды емес - біз «процесті ортасынан жалғастыруға» мүдделі емеспіз, әсіресе оның қайда болғаны белгісіз болса.

Бұл үшін PostgreSQL әзірлеушілері 9.1 нұсқасында осындай нәрсені енгізді БОЙЫНША Кестелер:

Осы көрсеткішпен кесте журналдан шығарылған ретінде жасалады. Тіркелмеген кестелерге жазылған деректер алдын ала жазу журналынан өтпейді (29-тарауды қараңыз), бұл кестелердің әдеттегіден әлдеқайда жылдам жұмыс істейді. Дегенмен, олар сәтсіздікке қарсы емес; сервер істен шыққан немесе төтенше жағдайда өшірілген жағдайда, журналдан шығарылған кесте автоматты түрде қысқартылады. Сонымен қатар, журналдан шығарылған кестенің мазмұны қайталанбаған құл серверлерге. Тіркелмеген кестеде жасалған кез келген индекстер автоматты түрде журналдан шығарылады.

Бір сөзбен айтқанда, бұл әлдеқайда жылдам болады, бірақ дерекқор сервері «құлап кетсе», бұл жағымсыз болады. Бірақ бұл қаншалықты жиі болады және сіздің ETL процессіңіз дерекқорды «жандандырғаннан» кейін оны «ортаңғыдан» қалай дұрыс түзету керектігін біледі ме?..

Егер жоқ болса және жоғарыдағы жағдай сіздікіне ұқсас болса, пайдаланыңыз UNLOGGEDбірақ ешқашан бұл төлсипатты нақты кестелерде қоспаңыз, деректер сіз үшін қымбат.

1.3. КОММЕНТТЕУ { ЖОЛДАРДЫ ЖОЮ | TROP}

Бұл құрылым кестені құру кезінде транзакция аяқталған кезде автоматты әрекетті көрсетуге мүмкіндік береді.

туралы ON COMMIT DROP Мен жоғарыда жаздым, ол жасайды DROP TABLE, бірақ бірге ON COMMIT DELETE ROWS жағдай анағұрлым қызық – ол осында қалыптасады TRUNCATE TABLE.

Уақытша кестенің мета-сипаттамасын сақтауға арналған барлық инфрақұрылым кәдімгі кестемен бірдей болғандықтан, онда Уақытша кестелерді үнемі жасау және жою жүйелік кестелердің қатты «ісінуіне» әкеледі pg_class, pg_attribute, pg_attrdef, pg_depend,…

Енді сізде әр секунд сайын жаңа транзакцияны ашатын, уақытша кестені жасайтын, толтыратын, өңдейтін және жойатын дерекқорға тікелей қосылымда жұмысшы бар деп елестетіп көріңіз... Жүйелік кестелерде жиналған қоқыстың артық мөлшері болады және бұл әрбір операция үшін қосымша тежегіштерді тудырады.

Жалпы, мұны жасамаңыз! Бұл жағдайда бұл әлдеқайда тиімді CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS оны транзакция циклінен шығарыңыз - содан кейін әрбір жаңа транзакцияның басында кестелер қазірдің өзінде болады бар болады (қоңырауды сақтаңыз CREATE), бірақ бос болады, рахмет TRUNCATE (біз оның қоңырауын да сақтадық) алдыңғы транзакцияны аяқтаған кезде.

1.4. ҰНАЙДЫ...соның ішінде...

Мен басында уақытша кестелерді пайдаланудың әдеттегі жағдайларының бірі импорттың әртүрлі түрлері екенін айттым - және әзірлеуші ​​​​шаршағанша мақсатты кестенің өрістерінің тізімін өзінің уақытша ... декларациясына көшіріп қояды ...

Бірақ жалқаулық - прогрестің қозғалтқышы! Сондықтан «үлгіге негізделген» жаңа кесте жасаңыз бұл әлдеқайда қарапайым болуы мүмкін:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Содан кейін осы кестеде көптеген деректерді жасай алатындықтан, оны іздеу ешқашан жылдам болмайды. Бірақ бұл үшін дәстүрлі шешім бар - индекстер! Және, иә, уақытша кестеде де индекстер болуы мүмкін.

Көбінесе қажетті индекстер мақсатты кестенің индекстерімен сәйкес келетіндіктен, сіз жай ғана жаза аласыз LIKE target_table INCLUDING INDEXES.

Сізге де керек болса DEFAULT-мәндерді (мысалы, негізгі кілт мәндерін толтыру үшін) пайдалануға болады LIKE target_table INCLUDING DEFAULTS. Немесе жай ғана - LIKE target_table INCLUDING ALL — әдепкі мәндерді, индекстерді, шектеулерді,... көшіреді.

Бірақ мұнда сіз жасаған болсаңыз, мұны түсінуіңіз керек кестені индекстермен дереу импорттаңыз, содан кейін деректерді жүктеуге ұзағырақ уақыт кетедіЕгер сіз алдымен бәрін толтырсаңыз, содан кейін ғана индекстерді жинасаңыз - мысал ретінде мұны қалай жасайтынын қараңыз pg_dump.

Жалпы, RTFM!

2. Қалай жазу керек?

Мен жай ғана айтайын - оны қолданыңыз COPY- «пакет» орнына ағын INSERT, кейде жеделдету. Сіз тіпті алдын ала жасалған файлдан тікелей жасай аласыз.

3. Өңдеу қалай жүргізіледі?

Ендеше, кіріспеміз келесідей болсын:

• дерекқорда сақталған клиент деректері бар кесте бар 1 миллион жазба
• Күн сайын клиент сізге жаңасын жібереді толық «сурет»
• тәжірибеден сіз оны анда-санда білесіз 10 мың жазбадан артық өзгертілмейді

Мұндай жағдайдың классикалық мысалы болып табылады KLADR базасы — барлығында мекен-жайлар өте көп, бірақ апта сайынғы жүктеп салуда тіпті республикалық масштабта да өзгерістер (елді мекендердің атын өзгерту, көшелерді біріктіру, жаңа үйлердің пайда болуы) өте аз.

3.1. Толық синхрондау алгоритмі

Қарапайымдылық үшін, деректерді қайта құрылымдаудың қажеті жоқ делік - кестені қажетті пішінге келтіріңіз, яғни:

• жою енді жоқтың бәрі
• жаңарту бұрыннан бар және жаңартуды қажет ететін барлық нәрсе
• кірістіру әлі болмағанның бәрі

Неліктен операциялар осы ретпен жасалуы керек? Өйткені кесте өлшемі осылайша ең аз өседі (MVCC есте сақтаңыз!).

DELETE FROM dst

Жоқ, әрине, сіз тек екі операциямен қол жеткізе аласыз:

• жою (DELETE) жалпы барлығы
• кірістіру барлығы жаңа суреттен

Бірақ сонымен бірге MVCC арқасында, Үстелдің көлемі дәл екі есе артады! 1K жаңартуға байланысты кестедегі жазбалардың +10 миллион кескінін алу соншалықты артық...

TRUNCATE дст

Тәжірибелі әзірлеуші ​​​​барлық планшетті өте арзан тазалауға болатындығын біледі:

• анық (TRUNCATE) бүкіл кесте
• кірістіру барлығы жаңа суреттен

Әдіс тиімді, кейде өте қолайлы, бірақ мәселе бар... Біз 1 миллион жазбаны ұзақ уақыт қосатын боламыз, сондықтан кестені осы уақыт бойы бос қалдыра алмаймыз (бір транзакцияға орамай-ақ солай болады).

Білдіреді:

• бастаймыз ұзақ мерзімді транзакция
• TRUNCATE жүктейді AccessExclusive-блоктау
• біз кірістіруді ұзақ уақыт жасаймыз, ал қалғандары осы уақытта тіпті алмайды SELECT

Бірдеңе дұрыс болмай жатыр...

КЕСТЕНДІ ӨЗГЕРТУ… АТЫН ӨЗГЕРТУ… / КЕСТЕНДІ ТАРТУ…

Балама - барлығын бөлек жаңа кестеге толтыру, содан кейін оны ескінің орнына қайта атау. Бірнеше жағымсыз кішкентай нәрселер:

• әлі де AccessExclusiveуақыт айтарлықтай аз болса да
• осы кесте үшін барлық сұрау жоспарлары/статистика қалпына келтірілді, ANALYZE іске қосу керек
• барлық сыртқы кілттер бұзылған (FK) үстелге

Саймон Риггс жасауды ұсынған WIP патч болды ALTER-статистикаға және FK-ға тимей, файл деңгейінде кестенің негізгі бөлігін ауыстыру операциясы, бірақ кворум жиналмаған.

ЖОЮ, ЖАҢАРТУ, ЕНГІЗУ

Сонымен, біз үш операцияның блокталмаған нұсқасына тоқталамыз. Үш дерлік... Мұны қалай тиімді жасауға болады?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Импорттан кейінгі өңдеу

Сол KLADR-де барлық өзгертілген жазбалар қосымша өңдеуден кейінгі өңдеу арқылы іске қосылуы керек - қалыпқа келтіріліп, кілт сөздер бөлектеліп, қажетті құрылымдарға дейін азайтылады. Бірақ сіз қайдан білесіз - нақты не өзгердісинхрондау кодын қиындатпай, ең дұрысы оған мүлде тимей ме?

Синхрондау кезінде тек сіздің процесс жазу рұқсаты болса, біз үшін барлық өзгерістерді жинайтын триггерді пайдалануға болады:

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

Енді біз синхрондауды бастамас бұрын триггерлерді қолдана аламыз (немесе оларды 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();

Содан кейін біз журнал кестелерінен барлық қажетті өзгерістерді тыныштандырып, оларды қосымша өңдеушілер арқылы іске қосамыз.

3.3. Байланыстырылған жиындарды импорттау

Жоғарыда біз дереккөздің және тағайындалған жердің деректер құрылымдары бірдей болатын жағдайларды қарастырдық. Бірақ сыртқы жүйеден жүктеп салу біздің дерекқордағы сақтау құрылымынан басқа пішімге ие болса ше?

Мысал ретінде клиенттерді және олардың шоттарын сақтауды, классикалық «көптен бірге» опциясын алайық:

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

Бірақ сыртқы көзден жүктеу бізге «бәрі бірде» түрінде келеді:

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

Әлбетте, бұл нұсқада тұтынушы деректерін қайталауға болады, ал негізгі жазба «шот» болып табылады:

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

Модель үшін біз жай ғана сынақ деректерін енгіземіз, бірақ есте сақтаңыз - COPY тиімдірек!

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

Алдымен, біздің «фактілер» сілтеме жасайтын «қисықтарды» атап өтейік. Біздің жағдайда шот-фактуралар тұтынушыларға қатысты:

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

Тіркелгілерді тұтынушы идентификаторларымен дұрыс байланыстыру үшін алдымен осы идентификаторларды табу немесе жасау керек. Олардың астындағы өрістерді қосамыз:

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

Жоғарыда сипатталған кестені синхрондау әдісін кішігірім түзетулермен қолданайық - біз мақсатты кестеде ештеңені жаңартпаймыз немесе жоймаймыз, өйткені біз «тек қосу үшін» клиенттерді импорттаймыз:

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

Негізінде бәрі бар invoice_import Енді бізде контакт өрісі толтырылды client_id, оның көмегімен біз шот-фактураны енгіземіз.

Ақпарат көзі: www.habr.com