{"id":74953,"date":"2020-03-22T08:42:22","date_gmt":"2020-03-22T05:42:22","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy"},"modified":"2020-03-22T08:42:22","modified_gmt":"2020-03-22T05:42:22","slug":"dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","status":"publish","type":"post","link":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","title":{"rendered":"DBA: korraldame s\u00fcnergiate ja importide haldamise","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Suure andmehulkade keerulise t\u00f6\u00f6tlemise korral (erinevad <noindex><a rel=\"nofollow\" href=\"https:\/\/ru.wikipedia.org\/wiki\/ETL\">ETL-protsessid<\/a><\/noindex>: impordid, konvertimised ja s\u00fcnkroonimised v\u00e4lise allikaga) tekib sageli vajadus <b>ajutiselt \"m\u00e4letada\" ja kohe kiiresti t\u00f6\u00f6delda<\/b> midagi mahukat.<\/p>\n<p>Kliiniline \u00fclesanne sellistel juhtudel k\u00f5lab tavaliselt umbes nii: <i>\"Siin <noindex><a rel=\"nofollow\" href=\"https:\/\/sbis.ru\/accounting\">raamatupidamine ekstrakteeris kliendipangast<\/a><\/noindex> viimased sisse laekunud maksed, tuleb need kiiresti saidile \u00fcles laadida ja siduda kontodega\"<\/i><\/p>\n<p>\u041d\u043e \u043a\u043e\u0433\u0434\u0430 \u043e\u0431\u044a\u0435\u043c \u044d\u0442\u043e\u0433\u043e \u00ab\u0447\u0435\u0433\u043e-\u0442\u043e\u00bb \u043d\u0430\u0447\u0438\u043d\u0430\u0435\u0442 \u0438\u0437\u043c\u0435\u0440\u044f\u0442\u044c\u0441\u044f \u0441\u043e\u0442\u043d\u044f\u043c\u0438 \u043c\u0435\u0433\u0430\u0431\u0430\u0439\u0442, \u0430 \u0441\u0435\u0440\u0432\u0438\u0441 \u043f\u0440\u0438 \u044d\u0442\u043e\u043c \u0434\u043e\u043b\u0436\u0435\u043d \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u0441 \u0431\u0430\u0437\u043e\u0439 \u0432 \u0440\u0435\u0436\u0438\u043c\u0435 24&#215;7, \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u0435\u0442 \u043c\u043d\u043e\u0436\u0435\u0441\u0442\u0432\u043e side-\u044d\u0444\u0444\u0435\u043a\u0442\u043e\u0432, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0431\u0443\u0434\u0443\u0442 \u043f\u043e\u0440\u0442\u0438\u0442\u044c \u0432\u0430\u043c \u0436\u0438\u0437\u043d\u044c.<br \/>\n<img decoding=\"async\" alt=\"DBA: korraldame s\u00fcnergiate ja importide haldamise\" src=\"\/wp-content\/uploads\/2020\/03\/f74afb2cd6f5f8de26a0932166933c95.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nEt nendega toime tulla PostgreSQL-is (ja mitte ainult seal), saab kasutada teatud optimeerimise v\u00f5imalusi, mis v\u00f5imaldavad k\u00f5ik kiiremini t\u00f6\u00f6delda ja v\u00e4hem ressursse tarbida.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>1. Kuhu laadida?<\/h2>\n<p>\nEsiteks, m\u00e4\u00e4ratleme, kuhu me saame laadida andmeid, mida me tahame \"t\u00f6\u00f6delda\".<\/p>\n<h3>1.1. Ajutised tabelid (TEMPORARY TABLE)<\/h3>\n<p>\nP\u00f5him\u00f5tteliselt on PostgreSQL-is ajutised tabelid samasugused tabelid nagu k\u00f5ik teised. Seega on vale arvamus, et <i><b>\u00abseal k\u00f5ik on ainult m\u00e4lus, ja see v\u00f5ib otsa l\u00f5ppeda\u00bb<\/b><\/i>. Kuid on ka m\u00f5ned olulised erinevused.<\/p>\n<h4>Iga andmebaasi\u00fchenduse jaoks oma \u00abnimetust\u00bb<\/h4>\n<p>\nKui kaks \u00fchendust \u00fcritavad samaaegselt teostada <code>CREATE TABLE x<\/code>, siis keegi peab kindlasti saama <b>unikaalsuse viga<\/b> andmebaasiobjektide osas.<\/p>\n<p>Aga kui m\u00f5lemad \u00fcritavad teostada <code>CREATE <b>AJUTINE<\/b> TABEL x<\/code>, siis m\u00f5lemad teevad seda normaalselt, ja iga\u00fcks saab <b>oma eksemplari<\/b> tabelist. Ja neil ei ole omavahel midagi \u00fchist.<\/p>\n<h4>\u00abEnesetapu\u00bb disconnecti puhul<\/h4>\n<p>\n\u00dchenduse sulgemisel kustutatakse k\u00f5ik ajutised tabelid automaatselt, seega pole m\u00f5tet \u00abk\u00e4sitsi\u00bb teostada <code>DROP TABLE x<\/code> v\u00e4lja arvatud\u2026<\/p>\n<p>Kui te t\u00f6\u00f6tate <b>pgbounceri tehingure\u017eiimis<\/b>, siis andmebaas arvab ikkagi, et see \u00fchendus on endiselt aktiivne, ja selles ajutises tabelis on endiselt olemas.<\/p>\n<p>Seega toob selle uuesti loomine, juba teisest pgbounceri \u00fchendusest, kaasa veateate. Kuid seda saab ringiga minna, kasutades <code>LOO TEMPORARY TABLE <b>KUI EI OLE<\/b> x<\/code>.<\/p>\n<p>T\u00f5si, parem oleks seda siiski mitte teha, kuna v\u00f5id hiljem \u201ej\u00e4rsku\u201c avastada seal endiselt \u201eeelmise omaniku\u201c andmed. Selle asemel on palju parem lugeda juhendit ja n\u00e4ha, et tabeli loomisel on v\u00f5imalik juurde kirjutada. <code>KOHUSTUSEGA <b>DROP<\/b><\/code> \u2014 see t\u00e4hendab, et tehingu l\u00f5petamisel tabel eemaldatakse automaatselt.<\/p>\n<h4>Mitte-replikatsioon<\/h4>\n<p>\nAjutiste tabelite replikatsiooni ei toimu, kuna need kuuluvad ainult kindlale \u00fchendusele. Kuid see <b>vabastab vajadusest andmete kahekordse salvestamise j\u00e4rele<\/b> heap + WAL, seet\u00f5ttu on INSERT\/UPDATE\/DELETE selles tunduvalt kiirem.<\/p>\n<p>Kuna ajutine tabel on siiski \u201epeaaegu tavaline\u201c tabel, ei saa seda ka replikatsiooni luua. V\u00e4hemalt praegu, kuigi vastav patch on juba ammu olemas.<\/p>\n<h3>1.2. Mitte-logitud tabelid (UNLOGGED TABLE)<\/h3>\n<p>\nAga mis siis, kui teil on m\u00f5ni mahukas ETL-protsess, mida ei \u00f5nnestu teostada \u00fche tehingu raames, ja teil on tegelikult <b>pgbounceri tehingure\u017eiimis<\/b>?..<\/p>\n<p>V\u00f5i andmevoog on nii suur, et <b>\u00fche \u00fchenduse l\u00e4bilaskev\u00f5ime ei piisa<\/b> andmebaasi (loe: \u00fche CPU protsessi)?..<\/p>\n<p>V\u00f5i osa operatsioonidest toimub <b>as\u00fcnkroonselt<\/b> erinevates \u00fchendustes?..<\/p>\n<p>Siin on vaid \u00fcks v\u00f5imalus \u2014 <b>ajutiselt luua mitte-ajutisi tabeleid<\/b>. M\u00e4nguline v\u00e4ljend, eks. See t\u00e4hendab:<\/p>\n<ul>\n<li>loodud \u201eoma\u201d tabelitega maksimaalselt juhuslike nimedega, et mitte kellegagi kokku puutuda<\/li>\n<li><b>Ekstraheerimine<\/b>: laaditi neisse andmed v\u00e4lisest allikast<\/li>\n<li><b>Transformeerimine<\/b>: muundatud, t\u00e4ideti v\u00f5tme sidumise v\u00e4ljad<\/li>\n<li><b>Load<\/b>: valmitud andmed kanti sihttabelitesse<\/li>\n<li>kustutas \u201eoma\u201d tabelid<\/li>\n<\/ul>\n<p>\nAga n\u00fc\u00fcd \u2014 t\u00f5rvatilk. Tegelikult, <b>kogu salvestus PostgreSQL-is toimub kahel korral<\/b> \u2014 <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/postgrespro\/blog\/461523\/\">esmakordselt WAL-is<\/a><\/noindex>, seej\u00e4rel juba tabelite\/indeksite kehad. K\u00f5ik see on tehtud ACID toe ja andmete korrektse n\u00e4htavuse toetamiseks <code>COMMIT<\/code>&#8216;\u043d\u0443\u0442\u044b\u043c\u0438 \u0438 <code>RULLI TAGASI<\/code>&#8216;\u043d\u0443\u0442\u044b\u043c\u0438 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044f\u043c\u0438.<\/p>\n<p>Aga me ei vaja seda! Meie kogu protsess <b>kas toimis t\u00e4iesti v\u00f5i mitte.<\/b>Pole t\u00e4htis, kui palju keskseid tehinguid selle sees on \u2014 meid ei huvita \u201eprotsessi j\u00e4tkamine keskelt\u201d, eriti kui ei ole selge, kus see oli.<\/p>\n<p>Selle jaoks tutvustasid PostgreSQL arendajad juba versioonis 9.1 sellist asja nagu <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/sql-createtable#SQL-CREATETABLE-UNLOGGED\">mittelogitud (UNLOGGED) tabelid<\/a><\/noindex>:<\/p>\n<blockquote><p>Selle juhisega luuakse tabel mittelogitud tabelina. Mittelogitud tabelitesse kirjutatavad andmed ei l\u00e4bi eelj\u00e4lgimislogi (vt peat\u00fckk 29), mille tulemuseks on sellised tabelid <b>t\u00f6\u00f6tavad palju kiiremini kui tavaliselt<\/b>. Kuid nad ei ole kaitstud rike eest; kui server kukub v\u00f5i selline katkestus toimub, j\u00e4\u00e4b logimata tabel <b>automaatiliselt l\u00fchendatud<\/b>. Lisaks sellele, logimata tabeli sisu <b>ei replitseerita<\/b> aluselserveritesse. K\u00f5ik indeksid, mis luuakse logimata tabeli jaoks, muutuvad automaatselt logimata.<\/p><\/blockquote>\n<p>L\u00fchidalt, <b>on see palju kiirem<\/b>, kuid kui andmebaasi server \u201ekukub\u201d \u2013 v\u00f5ib see olla ebameeldiv. Kuid kui sageli see juhtub ja kas teie ETL-protsess suudab seda p\u00e4rast andmebaasi \u201eelustamist\u201d \u00f5igesti kohandada?...<\/p>\n<p>Kui ei, ja \u00fclaltoodud juhtum sarnaneb teie omaga \u2013 kasutage <code>UNLOGGED<\/code>, kuid \u00e4rge <b>kandke seda attribuuti t\u00f5elistele tabelitele<\/b>, mille andmed on teile kallid.<\/p>\n<h3>1.3. ON COMMIT { DELETE ROWS | DROP }<\/h3>\n<p>\nSeda konstruktsiooni saab tabeli loomisel kasutada automaatse k\u00e4itumise m\u00e4\u00e4ramiseks tehingu l\u00f5petamisel.<\/p>\n<p>Umbes <code>KOHUSTUSEGA <b>DROP<\/b><\/code> nagu ma varem mainisin, genereerib ta <code>DROP TABLE<\/code>, kuid siin on <code>KOHUSTUSEGA <b>KUSTUTA RIDADE<\/b><\/code> huvitavam olukord \u2014 siinkohal genereeritakse <code>TRUNCATE TABLE<\/code>.<\/p>\n<p>Kuna kogu ajutise tabeli metaandmete salvestamise infrastruktuur on t\u00e4pselt sama, mis tavalisel tabelil, siis <b>ajutiste tabelite pidev loomine ja kustutamine viib s\u00fcsteemitabelite tugeva \"paisumiseni\"<\/b> pg_class, pg_attribute, pg_attrdef, pg_depend,\u2026<\/p>\n<p>N\u00fc\u00fcd kujutage ette, et teil on t\u00f6\u00f6taja, kes on otse\u00fchenduses andmebaasiga, ja avab iga sekundi j\u00e4rel uue tehingu, loob, t\u00e4idab, t\u00f6\u00f6tleb ja kustutab ajutise tabeli\u2026 S\u00fcsteemitabelites koguneb liigset prahti, mis toob kaasa igasuguseid viivitusi iga toimingu juures.<\/p>\n<p>\u00dches\u00f5naga, nii ei tohiks teha! Sellisel juhul on palju efektiivsem <code>LOO AJUTINE TABEL x ... KOMITTEERIMISEL KUSTUTA R\u00dcNDAD<\/code> viia v\u00e4lja tehingute ts\u00fcklist \u2014 nii on igas uues tehingus tabelid juba <b>olemas<\/b> (s\u00e4\u00e4stame v\u00e4ljakutse <code>CREATE<\/code>), kuid <b>on t\u00fchi<\/b>, t\u00e4nu <code>TRUNCATE<\/code> (selle v\u00e4ljakutse me s\u00e4\u00e4stsime ka) eelmine tehing l\u00f5petades.<\/p>\n<h3>1.4. LIKE\u2026 INCLUDING &#8230;<\/h3>\n<p>\nMa mainisin alguses, et \u00fcks t\u00fc\u00fcpiline kasutusjuht ajutiste tabelite jaoks on erinevad impordid \u2014 ja arendaja v\u00e4sib, kopeerides sihttabeli v\u00e4lja v\u00e4ljade loetelu oma ajutisse tabelisse \u2026<\/p>\n<p>Aga laiskus on edasiviiv j\u00f5ud! Seet\u00f5ttu <b>uus tabel \"mooduli\" j\u00e4rgi<\/b> on palju lihtsam luua:<\/p>\n<pre><code class=\"sql\">LOO AJUTINE TABEL import_table(\n  NAGU sihttabel\n);<\/code><\/pre>\n<p>\nKuna sellele tabelisse genereerida saab v\u00e4ga palju andmeid, nende otsimine ei ole sugugi kiire. Kuid sellele on traditsiooniline lahendus \u2014 indeksid! Ja jah, <b>ajutistel tabelitel v\u00f5ivad samuti olla indeksid<\/b>.<\/p>\n<p>Kuna sageli vajavad vajalikud indeksid kokku langema sihttabeli indeksitega, saab lihtsalt kirjutada <code>NAGU target_table <b>KAASAS INDEKSD<\/b><\/code>.<\/p>\n<p>Kui vajate veel ka <code>DEFAULT<\/code>-v\u00e4\u00e4rtused (n\u00e4iteks esmase v\u00f5tme v\u00e4\u00e4rtuste t\u00e4itmiseks), saab kasutada <code>NAGU target_table <b>KAASAR KOHANDAMINE<\/b><\/code>. V\u00f5i lihtsalt \u2014 <code>NAGU target_table <b>KAASUTADES K\u00d5IKI<\/b><\/code> \u2014 kopeerib vaikeseaded, indeksid, piirangud,\u2026<\/p>\n<p>Kuid siin tuleb juba m\u00f5ista, et kui olete loonud <b>imporditabeli kohe indeksitega, siis andmete laadimine v\u00f5tab kauem aega<\/b>, kui k\u00f5igepealt k\u00f5ik laadida ja alles p\u00e4rast seda indekseid lisada \u2014 vaadake n\u00e4iteks, kuidas seda teeb <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/app-pgdump\">pg_dump<\/a><\/noindex>.<\/p>\n<p>\u00dcldiselt, <noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/sql-createtable\">RTFM<\/a><\/noindex>!<\/p>\n<h2>2. Kuidas kirjutada?<\/h2>\n<p>\n\u00dctlen lihtsalt \u2014 kasutage <code><noindex><a rel=\"nofollow\" href=\"https:\/\/postgrespro.ru\/docs\/postgresql\/12\/sql-copy\">COPY<\/a><\/noindex><\/code>-voogu, mitte \"partiide\" <code>INSERT<\/code>, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.citusdata.com\/blog\/2017\/11\/08\/faster-bulk-loading-in-postgresql-with-copy\/\">kiirus mitmekordne<\/a><\/noindex>. Saate isegi otse eelnevalt vormistatud failist.<\/p>\n<h2>3. Kuidas t\u00f6\u00f6delda?<\/h2>\n<p>\nNii, oletame, et meie sisend n\u00e4eb v\u00e4lja ligikaudu nii:<\/p>\n<ul>\n<li>teil on andmebaasis tabel kliendiandmetega <b>1M kirje<\/b><\/li>\n<li>iga p\u00e4ev saadab klient teile uue <b>t\u00e4ieliku \"pildi\"<\/b><\/li>\n<li>kogemuse p\u00f5hjal teate, et kord-korralt <b>muudetakse mitte rohkem kui 10 000 kirjet<\/b><\/li>\n<\/ul>\n<p>\nKlassikaline n\u00e4ide sellisest olukorrast on <noindex><a rel=\"nofollow\" href=\"https:\/\/www.gnivc.ru\/technical_support\/classifiers_reference\/kladr\/\">KLADR andmebaas<\/a><\/noindex> \u2014 aadresside kogus on suur, kuid igas n\u00e4dalas esitatavas muudatuste (asulate nimede muutmine, t\u00e4navate \u00fchendamine, uute majade ilmumine) v\u00e4ljav\u00f5ttes on neid l\u00e4bi kogu riigi v\u00e4ga v\u00e4he.<\/p>\n<h3>3.1. T\u00e4iss\u00fcnkroonimise algoritm<\/h3>\n<p>\nLihtsuse huvides oletame, et te ei pea andmeid restruktureerima \u2014 lihtsalt viige tabel soovitud vormi, see t\u00e4hendab:<\/p>\n<ul>\n<li><b>kustutage<\/b> k\u00f5ik, mis enam ei eksisteeri<\/li>\n<li><b>uuendada<\/b> k\u00f5ik, mis juba olemas oli, ja vajab uuendamist<\/li>\n<li><b>sisestage<\/b> k\u00f5ik, mida veel ei olnud<\/li>\n<\/ul>\n<p>\nMiks just sellises j\u00e4rjestuses tuleks operatsioone teha? Sest just nii kasvab tabeli suurus minimaalselt (<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/491366\/\">pea meeles MVCC-d!<\/a><\/noindex>).<\/p>\n<h4>DELETE FROM dst<\/h4>\n<p>\nJah, loomulikult saab hakkama ka ainult kahe operatsiooniga:<\/p>\n<ul>\n<li><b>kustutage<\/b> (<code>DELETE<\/code>) \u00fcldiselt k\u00f5ik<\/li>\n<li><b>sisestage<\/b> k\u00f5ik uue kujutise seast<\/li>\n<\/ul>\n<p>\nKuid t\u00e4nu MVCC-le <b>tabeli suurus kahekordistub<\/b>! Saada +1M kirjeid tabelisse 10K uuendamise t\u00f5ttu \u2014 see on k\u00fcllaltki liig.<\/p>\n<h4>TRUNCATE dst<\/h4>\n<p>\nKogenum arendaja teab, et kogu tabeli saab \u00fcsna odavalt puhastada:<\/p>\n<ul>\n<li><b>puhastage<\/b> (<code>TRUNCATE<\/code>) kogu tabel<\/li>\n<li><b>sisestage<\/b> k\u00f5ik uue kujutise seast<\/li>\n<\/ul>\n<p>\nT\u00f5hus meetod, <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/481866\/\">m\u00f5nikord t\u00e4iesti rakendatav<\/a><\/noindex>, aga on \u00fcks probleem\u2026 1M kirjeid lisades l\u00e4heb meil kaua aega, seega ei saa me endale lubada, et tabel j\u00e4\u00e4b kogu selle aja jooksul t\u00fchi (nagu juhtub, kui ei kasuta \u00fchte tehingut).<\/p>\n<p>See t\u00e4hendab:<\/p>\n<ul>\n<li>meie jaoks algab <b>pikk tehing<\/b><\/li>\n<li><code>TRUNCATE<\/code> kehtestab <b>AccessExclusive<\/b>-lukustuse<\/li>\n<li>me teeme kaua lisamist, samal ajal ei saa k\u00f5ik teised <b>isegi <code>SELECT<\/code><\/b><\/li>\n<\/ul>\n<p>\nEi n\u00e4e v\u00e4lja, et see h\u00e4sti l\u00e4heb\u2026<\/p>\n<h4>ALTER TABLE\u2026 RENAME\u2026 \/ DROP TABLE &#8230;<\/h4>\n<p>\n\u00dcks variant on k\u00f5ik eraldi uude tabelisse laadida ja seej\u00e4rel lihtsalt vana tabeli kohale \u00fcmber nimetada. Paar ebameeldivat asja:<\/p>\n<ul>\n<li>see on ikka ka samasugune <b>AccessExclusive<\/b>, kuigi m\u00e4rgatavalt v\u00e4hem aega<\/li>\n<li>k\u00f5ik p\u00e4ringute plaanid\/statistika selle tabeli kohta kustutatakse, <noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/479656\/\">peab jooksma ANALYZE<\/a><\/noindex><\/li>\n<li><b>k\u00f5ik v\u00e4lisv\u00f5tmed (FK) tabeli suhtes katkestatakse<\/b> Olemas oli WIP-patch Simon Riggsilt, mis pakkus<\/li>\n<\/ul>\n<p>\nALTER <code>-operatsiooni, et asendada tabeli keha failitasemel, kahjustamata statistikat ja FK, kuid ei saavutanud vajalikku toetust.<\/code>DELETE, UPDATE, INSERT<\/p>\n<h4>Nii et j\u00e4\u00e4me kolme operatsiooni mitte-blokeerimise variandi juurde. Peaaegu kolme\u2026 Kuidas seda k\u00f5ige t\u00f5husamalt teha?<\/h4>\n<p>\nNii et, peatume kolme toimingu mitte-blokeeriva valiku peal. Peaaegu kolme\u2026 Kuidas seda k\u00f5ige t\u00f5husamalt teha?<\/p>\n<pre><code class=\"sql\">-- \u0432\u0441\u0435 \u0434\u0435\u043b\u0430\u0435\u043c \u0432 \u0440\u0430\u043c\u043a\u0430\u0445 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438, \u0447\u0442\u043e\u0431\u044b \u043d\u0438\u043a\u0442\u043e \u043d\u0435 \u0432\u0438\u0434\u0435\u043b &quot;\u043f\u0440\u043e\u043c\u0435\u0436\u0443\u0442\u043e\u0447\u043d\u044b\u0445&quot; \u0441\u043e\u0441\u0442\u043e\u044f\u043d\u0438\u0439\nBEGIN;\n\n-- \u0441\u043e\u0437\u0434\u0430\u0435\u043c \u0432\u0440\u0435\u043c\u0435\u043d\u043d\u0443\u044e \u0442\u0430\u0431\u043b\u0438\u0446\u0443 \u0441 \u0438\u043c\u043f\u043e\u0440\u0442\u0438\u0440\u0443\u0435\u043c\u044b\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u043c\u0438\nCREATE TEMPORARY TABLE tmp(\n  LIKE dst INCLUDING INDEXES -- \u043f\u043e \u043e\u0431\u0440\u0430\u0437\u0443 \u0438 \u043f\u043e\u0434\u043e\u0431\u0438\u044e, \u0432\u043c\u0435\u0441\u0442\u0435 \u0441 \u0438\u043d\u0434\u0435\u043a\u0441\u0430\u043c\u0438\n) ON COMMIT DROP; -- \u0437\u0430 \u0440\u0430\u043c\u043a\u0430\u043c\u0438 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u043e\u043d\u0430 \u043d\u0430\u043c \u043d\u0435 \u043d\u0443\u0436\u043d\u0430\n\n-- \u0431\u044b\u0441\u0442\u0440\u043e-\u0431\u044b\u0441\u0442\u0440\u043e \u0432\u043b\u0438\u0432\u0430\u0435\u043c \u043d\u043e\u0432\u044b\u0439 \u043e\u0431\u0440\u0430\u0437 \u0447\u0435\u0440\u0435\u0437 COPY\nCOPY tmp FROM STDIN;\n-- ...\n-- .\n\n-- \u0443\u0434\u0430\u043b\u044f\u0435\u043c \u043e\u0442\u0441\u0443\u0442\u0441\u0442\u0432\u0443\u044e\u0449\u0438\u0435\nDELETE FROM\n  dst D\nUSING\n  dst X\nLEFT JOIN\n  tmp Y\n    USING(pk1, pk2) -- \u043f\u043e\u043b\u044f \u043f\u0435\u0440\u0432\u0438\u0447\u043d\u043e\u0433\u043e \u043a\u043b\u044e\u0447\u0430\nWHERE\n  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND\n  Y IS NOT DISTINCT FROM NULL; -- &quot;\u0430\u043d\u0442\u0438\u0434\u0436\u043e\u0439\u043d&quot;\n\n-- \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0435\u043c \u043e\u0441\u0442\u0430\u0432\u0448\u0438\u0435\u0441\u044f\nUPDATE\n  dst D\nSET\n  (f1, f2, f3) = (T.f1, T.f2, T.f3)\nFROM\n  tmp T\nWHERE\n  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND\n  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- \u043d\u0435\u0437\u0430\u0447\u0435\u043c \u043e\u0431\u043d\u043e\u0432\u043b\u044f\u0442\u044c \u0441\u043e\u0432\u043f\u0430\u0434\u0430\u044e\u0449\u0438\u0435\n\n-- \u0432\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u043c \u043e\u0442\u0441\u0443\u0442\u0441\u0442\u0432\u0443\u044e\u0449\u0438\u0435\nINSERT INTO\n  dst\nSELECT\n  T.*\nFROM\n  tmp T\nLEFT JOIN\n  dst D\n    USING(pk1, pk2)\nWHERE\n  D IS NOT DISTINCT FROM NULL;\n\nCOMMIT;\n<\/code><\/pre>\n<p><\/p>\n<h3>3.2. Postit\u00f6\u00f6tlus impordist<\/h3>\n<p>\nSamas KADRi k\u00f5iki muudetud kirjeid tuleb lisaks l\u00e4bi viia postit\u00f6\u00f6tlus \u2014 normaliseerida, eristada m\u00e4rks\u00f5nu, viia vajalikesse struktuuridesse. Aga kuidas teada \u2014 <b>mida t\u00e4pselt on muudetud<\/b>, keerukust suurendamata s\u00fcnkroniseerimise koodis, ideaalis, mitte puutuda sellesse \u00fcldse?<\/p>\n<p>Kui kirjutamis\u00f5igus on s\u00fcnkroniseerimise ajal ainult teie protsessil, saate kasutada k\u00e4ivitusmehhanismi, mis kogub k\u00f5ik muudatused kokku:<\/p>\n<pre><code class=\"sql\">-- siht tabelid\nCREATE TABLE kladr(...);\nCREATE TABLE kladr_house(...);\n\n-- muudatuste ajalooga tabelid\nCREATE TABLE kladr$log(\n  ro kladr, -- siin on vanade\/uutest kirjetest t\u00e4is kujutised\n  rn kladr\n);\n\nCREATE TABLE kladr_house$log(\n  ro kladr_house,\n  rn kladr_house\n);\n\n-- muudatuste logimise \u00fcldine funktsioon\nCREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$\nDECLARE\n  dst varchar = TG_TABLE_NAME || '$log';\n  stmt text = '';\nBEGIN\n  -- kontrollime, kas logimine on vajalik kirje uuendamisel\n  IF TG_OP = 'UPDATE' THEN\n    IF NEW IS NOT DISTINCT FROM OLD THEN\n      RETURN NEW;\n    END IF;\n  END IF;\n  -- loome logikirje\n  stmt = 'INSERT INTO ' || dst::text || '(ro,rn) VALUES(';\n  CASE TG_OP\n    WHEN 'INSERT' THEN\n      EXECUTE stmt || 'NULL,$1)' USING NEW;\n    WHEN 'UPDATE' THEN\n      EXECUTE stmt || '$1,$2)' USING OLD, NEW;\n    WHEN 'DELETE' THEN\n      EXECUTE stmt || '$1,NULL)' USING OLD;\n  END CASE;\n  RETURN NEW;\nEND;\n$$ LANGUAGE plpgsql;\n<\/code><\/pre>\n<p>\nN\u00fc\u00fcd saame enne s\u00fcnkroniseerimise alustamist k\u00e4ivitusmehhanismid aktiveerida (v\u00f5i lubada nende kaudu <code>ALTER TABLE ... ENABLE TRIGGER ...<\/code>):<\/p>\n<pre><code class=\"sql\">CREATE TRIGGER log\n  AFTER INSERT OR UPDATE OR DELETE\n  ON kladr\n    FOR EACH ROW\n      EXECUTE PROCEDURE diff$log();\n\nCREATE TRIGGER log\n  AFTER INSERT OR UPDATE OR DELETE\n  ON kladr_house\n    FOR EACH ROW\n      EXECUTE PROCEDURE diff$log();\n<\/code><\/pre>\n<p>\nJa v\u00f5ime rahulikult logitabelitest v\u00e4lja v\u00f5tta k\u00f5ik vajalikud muudatused ja edastada need t\u00e4iendavatele t\u00f6\u00f6tlejatele.<\/p>\n<h3>3.3. Seotud komplektide importimine<\/h3>\n<p>\n\u00dclalpool k\u00e4sitlesime juhtumeid, kus sisendi ja v\u00e4ljundi andmestruktuurid on samad. Kuid mis juhtub, kui v\u00e4listest s\u00fcsteemidest saadud eksport on struktuurilt erinev meie andmebaasi salvestamisest?<\/p>\n<p>V\u00f5tame n\u00e4iteks klientide ja nende arvete s\u00e4ilitamise, klassikalise \u201epalju-\u00fchele\u201d variandi:<\/p>\n<pre><code class=\"sql\">CREATE TABLE client(\n  client_id\n    serial\n      PRIMARY KEY\n, inn\n    varchar\n      UNIQUE\n, name\n    varchar\n);\n\nCREATE TABLE invoice(\n  invoice_id\n    serial\n      PRIMARY KEY\n, client_id\n    integer\n      REFERENCES client(client_id)\n, number\n    varchar\n, dt\n    date\n, sum\n    numeric(32,2)\n);<\/code><\/pre>\n<p>\nKuid v\u00e4listest allikatest saadud eksport on meil vormis \u201e k\u00f5ik \u00fches\u201d:<\/p>\n<pre><code class=\"sql\">CREATE TEMPORARY TABLE invoice_import(\n  client_inn\n    varchar\n, client_name\n    varchar\n, invoice_number\n    varchar\n, invoice_dt\n    date\n, invoice_sum\n    numeric(32,2)\n);<\/code><\/pre>\n<p>\nOn ilmne, et klientide andmed v\u00f5ivad sellisel juhul dubleerida, kusjuures p\u00f5hikirje on \u201earve\u201d:<\/p>\n<pre><code class=\"plaintext\">0123456789; Vasilij; A-01; 2020-03-16; 1000.00\n9876543210; Peeter; A-02; 2020-03-16; 666.00\n0123456789; Vasilij; B-03; 2020-03-16; 9999.00\n<\/code><\/pre>\n<p>\nMudeli jaoks sisestame lihtsalt meie testandmed, kuid peame meeles pidama \u2014 <code>COPY<\/code> efektiivsem!<\/p>\n<pre><code class=\"sql\">INSERT INTO invoice_import\nVALUES\n  ('0123456789', 'Vassilis', 'A-01', '2020-03-16', 1000.00)\n, ('9876543210', 'Petja', 'A-02', '2020-03-16', 666.00)\n, ('0123456789', 'Vassilis', 'B-03', '2020-03-16', 9999.00);<\/code><\/pre>\n<p>\nEsimene samm on tuvastada need 'kategooriad', millele meie 'faktid' viitavad. Meie puhul viitavad arved klientidele:<\/p>\n<pre><code class=\"sql\">CREATE TEMPORARY TABLE client_import AS\nSELECT DISTINCT ON(client_inn)\n-- \u043c\u043e\u0436\u043d\u043e \u043f\u0440\u043e\u0441\u0442\u043e SELECT DISTINCT, \u0435\u0441\u043b\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u0437\u0430\u0432\u0435\u0434\u043e\u043c\u043e \u043d\u0435\u043f\u0440\u043e\u0442\u0438\u0432\u043e\u0440\u0435\u0447\u0438\u0432\u044b\n  client_inn inn\n, client_name &quot;name&quot;\nFROM\n  invoice_import;<\/code><\/pre>\n<p>\nKuna peame arved \u00f5igesti seostama klientide ID-dega, peame k\u00f5igepealt need identifikaatorid v\u00e4lja selgitama v\u00f5i genereerima. Lisame nende jaoks v\u00e4ljad:<\/p>\n<pre><code class=\"sql\">ALTER TABLE invoice_import ADD COLUMN client_id integer;\nALTER TABLE client_import ADD COLUMN client_id integer;<\/code><\/pre>\n<p>\nKasutame eespool kirjeldatud tabelite s\u00fcnkroniseerimise meetodit v\u00e4ikese muudatusega \u2014 me ei uuenda ega kustuta midagi sihttabelis, kuna klientide importimine on meil 'append-only':<\/p>\n<pre><code class=\"sql\">-- impordime tabelisse juba olemasolevate kirje ID-d\nUPDATE\n  client_import T\nSET\n  client_id = D.client_id\nFROM\n  client D\nWHERE\n  T.inn = D.inn; -- unikaalne v\u00f5ti\n\n-- lisame puuduvad kirjed ja m\u00e4\u00e4rame nende ID-d\nWITH ins AS (\n  INSERT INTO client(\n    inn\n  , name\n  )\n  SELECT\n    inn\n  , name\n  FROM\n    client_import\n  WHERE\n    client_id IS NULL -- kui ID pole m\u00e4\u00e4ratud\n  RETURNING *\n)\nUPDATE\n  client_import T\nSET\n  client_id = D.client_id\nFROM\n  ins D\nWHERE\n  T.inn = D.inn; -- unikaalne v\u00f5ti\n\n-- m\u00e4\u00e4rame klientide ID-d arvete kirjestes\nUPDATE\n  invoice_import T\nSET\n  client_id = D.client_id\nFROM\n  client_import D\nWHERE\n  T.client_inn = D.inn; -- rakendatav v\u00f5ti\n<\/code><\/pre>\n<p>\nKokkuv\u00f5ttes, k\u00f5ik \u2014 <code>invoice_import<\/code> n\u00fc\u00fcd on meil seotud v\u00e4lja t\u00e4idetud <code>client_id<\/code>, millega me ka arve sisestame.<br \/>\n<br \/>Allikas: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/tensor\/blog\/492464\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0438 \u0441\u043b\u043e\u0436\u043d\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0435 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043d\u0430\u0431\u043e\u0440\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0440\u0430\u0437\u043d\u044b\u0435 ETL-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b: \u0438\u043c\u043f\u043e\u0440\u0442\u044b, \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0430\u0446\u0438\u0438 \u0438 \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0441 \u0432\u043d\u0435\u0448\u043d\u0438\u043c \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u043e\u043c) \u0447\u0430\u0441\u0442\u043e \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u0435\u0442 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c \u0432\u0440\u0435\u043c\u0435\u043d\u043d\u043e \u00ab\u0437\u0430\u043f\u043e\u043c\u043d\u0438\u0442\u044c\u00bb, \u0438 \u0441\u0440\u0430\u0437\u0443 \u0431\u044b\u0441\u0442\u0440\u043e \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u0447\u0442\u043e-\u0442\u043e \u043e\u0431\u044a\u0435\u043c\u043d\u043e\u0435. \u0422\u0438\u043f\u043e\u0432\u0430\u044f \u0437\u0430\u0434\u0430\u0447\u0430 \u043f\u043e\u0434\u043e\u0431\u043d\u043e\u0433\u043e \u0440\u043e\u0434\u0430 \u0437\u0432\u0443\u0447\u0438\u0442 \u043e\u0431\u044b\u0447\u043d\u043e \u043f\u0440\u0438\u043c\u0435\u0440\u043d\u043e \u0442\u0430\u043a: \u00ab\u0412\u043e\u0442 \u0442\u0443\u0442 \u0431\u0443\u0445\u0433\u0430\u043b\u0442\u0435\u0440\u0438\u044f \u0432\u044b\u0433\u0440\u0443\u0437\u0438\u043b\u0430 \u0438\u0437 \u043a\u043b\u0438\u0435\u043d\u0442-\u0431\u0430\u043d\u043a\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0435 \u043f\u043e\u0441\u0442\u0443\u043f\u0438\u0432\u0448\u0438\u0435 \u043e\u043f\u043b\u0430\u0442\u044b, \u043d\u0430\u0434\u043e \u0438\u0445 \u0431\u044b\u0441\u0442\u0440\u0435\u043d\u044c\u043a\u043e \u0432\u043a\u0430\u0447\u0430\u0442\u044c \u043d\u0430 \u0441\u0430\u0439\u0442 \u0438 \u043f\u0440\u0438\u0432\u044f\u0437\u0430\u0442\u044c \u043a \u0441\u0447\u0435\u0442\u0430\u043c\u00bb \u041d\u043e \u043a\u043e\u0433\u0434\u0430 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":74954,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-74953","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 4.9.10 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041f\u0440\u0438 \u0441\u043b\u043e\u0436\u043d\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0435 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043d\u0430\u0431\u043e\u0440\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0440\u0430\u0437\u043d\u044b\u0435 ETL-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b: \u0438\u043c\u043f\u043e\u0440\u0442\u044b, \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0430\u0446\u0438\u0438 \u0438 \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0441 \u0432\u043d\u0435\u0448\u043d\u0438\u043c \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u043e\u043c) \u0447\u0430\u0441\u0442\u043e \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u0435\u0442 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c \u0432\u0440\u0435\u043c\u0435\u043d\u043d\u043e \u00ab\u0437\u0430\u043f\u043e\u043c\u043d\u0438\u0442\u044c\u00bb, \u0438 \u0441\u0440\u0430\u0437\u0443 \u0431\u044b\u0441\u0442\u0440\u043e \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u0447\u0442\u043e-\u0442\u043e \u043e\u0431\u044a\u0435\u043c\u043d\u043e\u0435. \u0422\u0438\u043f\u043e\u0432\u0430\u044f \u0437\u0430\u0434\u0430\u0447\u0430 \u043f\u043e\u0434\u043e\u0431\u043d\u043e\u0433\u043e \u0440\u043e\u0434\u0430 \u0437\u0432\u0443\u0447\u0438\u0442 \u043e\u0431\u044b\u0447\u043d\u043e \u043f\u0440\u0438\u043c\u0435\u0440\u043d\u043e \u0442\u0430\u043a: \u00ab\u0412\u043e\u0442 \u0442\u0443\u0442 \u0431\u0443\u0445\u0433\u0430\u043b\u0442\u0435\u0440\u0438\u044f \u0432\u044b\u0433\u0440\u0443\u0437\u0438\u043b\u0430 \u0438\u0437 \u043a\u043b\u0438\u0435\u043d\u0442-\u0431\u0430\u043d\u043a\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0435 \u043f\u043e\u0441\u0442\u0443\u043f\u0438\u0432\u0448\u0438\u0435 \u043e\u043f\u043b\u0430\u0442\u044b, \u043d\u0430\u0434\u043e \u0438\u0445 \u0431\u044b\u0441\u0442\u0440\u0435\u043d\u044c\u043a\u043e \u0432\u043a\u0430\u0447\u0430\u0442\u044c \u043d\u0430 \u0441\u0430\u0439\u0442 \u0438 \u043f\u0440\u0438\u0432\u044f\u0437\u0430\u0442\u044c \u043a \u0441\u0447\u0435\u0442\u0430\u043c\u00bb \u041d\u043e \u043a\u043e\u0433\u0434\u0430\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 4.9.10\" \/>\n\t\t<meta property=\"og:locale\" content=\"et_EE\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47DBA: \u0433\u0440\u0430\u043c\u043e\u0442\u043d\u043e \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u043e\u0432\u044b\u0432\u0430\u0435\u043c \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0438 \u0438\u043c\u043f\u043e\u0440\u0442\u044b | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0440\u0438 \u0441\u043b\u043e\u0436\u043d\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0435 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043d\u0430\u0431\u043e\u0440\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0440\u0430\u0437\u043d\u044b\u0435 ETL-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b: \u0438\u043c\u043f\u043e\u0440\u0442\u044b, \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0430\u0446\u0438\u0438 \u0438 \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0441 \u0432\u043d\u0435\u0448\u043d\u0438\u043c \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u043e\u043c) \u0447\u0430\u0441\u0442\u043e \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u0435\u0442 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c \u0432\u0440\u0435\u043c\u0435\u043d\u043d\u043e \u00ab\u0437\u0430\u043f\u043e\u043c\u043d\u0438\u0442\u044c\u00bb, \u0438 \u0441\u0440\u0430\u0437\u0443 \u0431\u044b\u0441\u0442\u0440\u043e \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u0447\u0442\u043e-\u0442\u043e \u043e\u0431\u044a\u0435\u043c\u043d\u043e\u0435. \u0422\u0438\u043f\u043e\u0432\u0430\u044f \u0437\u0430\u0434\u0430\u0447\u0430 \u043f\u043e\u0434\u043e\u0431\u043d\u043e\u0433\u043e \u0440\u043e\u0434\u0430 \u0437\u0432\u0443\u0447\u0438\u0442 \u043e\u0431\u044b\u0447\u043d\u043e \u043f\u0440\u0438\u043c\u0435\u0440\u043d\u043e \u0442\u0430\u043a: \u00ab\u0412\u043e\u0442 \u0442\u0443\u0442 \u0431\u0443\u0445\u0433\u0430\u043b\u0442\u0435\u0440\u0438\u044f \u0432\u044b\u0433\u0440\u0443\u0437\u0438\u043b\u0430 \u0438\u0437 \u043a\u043b\u0438\u0435\u043d\u0442-\u0431\u0430\u043d\u043a\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0435 \u043f\u043e\u0441\u0442\u0443\u043f\u0438\u0432\u0448\u0438\u0435 \u043e\u043f\u043b\u0430\u0442\u044b, \u043d\u0430\u0434\u043e \u0438\u0445 \u0431\u044b\u0441\u0442\u0440\u0435\u043d\u044c\u043a\u043e \u0432\u043a\u0430\u0447\u0430\u0442\u044c \u043d\u0430 \u0441\u0430\u0439\u0442 \u0438 \u043f\u0440\u0438\u0432\u044f\u0437\u0430\u0442\u044c \u043a \u0441\u0447\u0435\u0442\u0430\u043c\u00bb \u041d\u043e \u043a\u043e\u0433\u0434\u0430\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-03-22T05:42:22+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-03-22T05:42:22+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47DBA: korraldame s\u00fcnkroonimised ja impordid professionaalselt | ProHoster","description":"Suuremate andmehulkade keerulise t\u00f6\u00f6tlemise korral (erinevad ETL-protsessid: impordid, konverteerimised ja s\u00fcnkroonimine v\u00e4lise allikaga) tekib sageli vajadus ajutiselt \"m\u00e4letada\" ja kiiresti t\u00f6\u00f6tleda midagi mahukat. T\u00fc\u00fcpiline \u00fclesanne sellelaadses olukorras k\u00f5lab tavaliselt umbes nii: \"Siin raamatupidamine laadis kliendibankast v\u00e4lja viimased laekumised, need tuleb kiiresti veebile peale laadida ja arvetele siduda.\" Kuid kui","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47DBA: \u0433\u0440\u0430\u043c\u043e\u0442\u043d\u043e \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u043e\u0432\u044b\u0432\u0430\u0435\u043c \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0438 \u0438\u043c\u043f\u043e\u0440\u0442\u044b | ProHoster","og:description":"\u041f\u0440\u0438 \u0441\u043b\u043e\u0436\u043d\u043e\u0439 \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0435 \u0431\u043e\u043b\u044c\u0448\u0438\u0445 \u043d\u0430\u0431\u043e\u0440\u043e\u0432 \u0434\u0430\u043d\u043d\u044b\u0445 (\u0440\u0430\u0437\u043d\u044b\u0435 ETL-\u043f\u0440\u043e\u0446\u0435\u0441\u0441\u044b: \u0438\u043c\u043f\u043e\u0440\u0442\u044b, \u043a\u043e\u043d\u0432\u0435\u0440\u0442\u0430\u0446\u0438\u0438 \u0438 \u0441\u0438\u043d\u0445\u0440\u043e\u043d\u0438\u0437\u0430\u0446\u0438\u0438 \u0441 \u0432\u043d\u0435\u0448\u043d\u0438\u043c \u0438\u0441\u0442\u043e\u0447\u043d\u0438\u043a\u043e\u043c) \u0447\u0430\u0441\u0442\u043e \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u0435\u0442 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e\u0441\u0442\u044c \u0432\u0440\u0435\u043c\u0435\u043d\u043d\u043e \u00ab\u0437\u0430\u043f\u043e\u043c\u043d\u0438\u0442\u044c\u00bb, \u0438 \u0441\u0440\u0430\u0437\u0443 \u0431\u044b\u0441\u0442\u0440\u043e \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u0447\u0442\u043e-\u0442\u043e \u043e\u0431\u044a\u0435\u043c\u043d\u043e\u0435. \u0422\u0438\u043f\u043e\u0432\u0430\u044f \u0437\u0430\u0434\u0430\u0447\u0430 \u043f\u043e\u0434\u043e\u0431\u043d\u043e\u0433\u043e \u0440\u043e\u0434\u0430 \u0437\u0432\u0443\u0447\u0438\u0442 \u043e\u0431\u044b\u0447\u043d\u043e \u043f\u0440\u0438\u043c\u0435\u0440\u043d\u043e \u0442\u0430\u043a: \u00ab\u0412\u043e\u0442 \u0442\u0443\u0442 \u0431\u0443\u0445\u0433\u0430\u043b\u0442\u0435\u0440\u0438\u044f \u0432\u044b\u0433\u0440\u0443\u0437\u0438\u043b\u0430 \u0438\u0437 \u043a\u043b\u0438\u0435\u043d\u0442-\u0431\u0430\u043d\u043a\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0435 \u043f\u043e\u0441\u0442\u0443\u043f\u0438\u0432\u0448\u0438\u0435 \u043e\u043f\u043b\u0430\u0442\u044b, \u043d\u0430\u0434\u043e \u0438\u0445 \u0431\u044b\u0441\u0442\u0440\u0435\u043d\u044c\u043a\u043e \u0432\u043a\u0430\u0447\u0430\u0442\u044c \u043d\u0430 \u0441\u0430\u0439\u0442 \u0438 \u043f\u0440\u0438\u0432\u044f\u0437\u0430\u0442\u044c \u043a \u0441\u0447\u0435\u0442\u0430\u043c\u00bb \u041d\u043e \u043a\u043e\u0433\u0434\u0430","og:url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/dba-gramotno-organizovyvaem-sinhronizaczii-i-importy","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-03-22T05:42:22+00:00","article:modified_time":"2020-03-22T05:42:22+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"74953","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 18:04:26","updated":"2022-09-30 13:25:20"},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/74953","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=74953"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/74953\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/74954"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=74953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=74953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=74953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}