DBA: सक्षम रूपमा सिंक्रोनाइजेसन र आयातहरू व्यवस्थित गर्नुहोस्

ठूला डाटा सेटहरूको जटिल प्रशोधनको लागि (भिन्न ETL प्रक्रियाहरू: आयात, रूपान्तरण र बाह्य स्रोतसँग सिंक्रोनाइजेसन) अक्सर त्यहाँ आवश्यकता छ अस्थायी रूपमा "सम्झना" र तुरुन्तै छिटो प्रक्रिया केहि ठूलो।

यस प्रकारको एक विशिष्ट कार्य सामान्यतया यो जस्तै केहि सुनिन्छ: "ठ्याक्क यहाँ ग्राहक बैंकबाट लेखा विभाग अनलोड गरियो अन्तिम प्राप्त भुक्तानीहरू, तपाईंले तिनीहरूलाई छिटो वेबसाइटमा अपलोड गर्न र तिनीहरूलाई तपाईंको खाताहरूमा लिङ्क गर्न आवश्यक छ।"

तर जब यो "केहि" को भोल्युम सयौं मेगाबाइटमा मापन गर्न थाल्छ, र सेवाले डाटाबेस 24x7 सँग काम गर्न जारी राख्नुपर्छ, धेरै साइड इफेक्टहरू उत्पन्न हुन्छन् जसले तपाईंको जीवनलाई बर्बाद गर्नेछ।

PostgreSQL मा तिनीहरूसँग सम्झौता गर्न (र यसमा मात्र होइन), तपाईंले केही अप्टिमाइजेसनहरू प्रयोग गर्न सक्नुहुन्छ जसले तपाईंलाई सबै कुरालाई छिटो र कम स्रोत खपतको साथ प्रशोधन गर्न अनुमति दिनेछ।

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 महत्त्वपूर्ण रूपमा छिटो छ।

तर अस्थायी तालिका अझै पनि "लगभग साधारण" तालिका भएकोले, यसलाई प्रतिकृतिमा पनि बनाउन सकिँदैन। कम्तिमा अहिलेको लागि, यद्यपि सम्बन्धित प्याच लामो समयदेखि चलिरहेको छ।

१.२। अनलग गरिएको तालिका

तर तपाईले के गर्नु पर्छ, उदाहरणका लागि, यदि तपाइँसँग केहि प्रकारको बोझिल ETL प्रक्रिया छ जुन एक लेनदेन भित्र लागू गर्न सकिँदैन, तर तपाइँसँग अझै छ। लेनदेन मोडमा pgbouncer? ..

वा डाटा प्रवाह यति ठूलो छ कि एउटा जडानमा पर्याप्त ब्यान्डविथ छैन डाटाबेसबाट (पढ्नुहोस्, प्रति CPU एक प्रक्रिया)?..

वा केही अपरेशनहरू भइरहेका छन् एसिन्क्रोनस रूपमा विभिन्न सम्बन्धमा?..

यहाँ एक मात्र विकल्प छ - अस्थायी रूपमा गैर-अस्थायी तालिका सिर्जना गर्नुहोस्। पुन, हो। त्यो हो:

• "मेरो आफ्नै" तालिकाहरू अधिकतम अनियमित नामहरूको साथ सिर्जना गरियो ताकि कसैसँग नमिलोस्
• निकाल्नुहोस्: तिनीहरूलाई बाह्य स्रोतबाट डाटा भरियो
• रूपान्तरण गर्नुहोस्: रूपान्तरित, कुञ्जी लिङ्किङ क्षेत्रहरूमा भरिएको
• लोड: लक्षित तालिकाहरूमा तयार डाटा खन्याइयो
• "मेरो" तालिकाहरू हटाइयो

र अब - मलम मा एक फ्लाई। वास्तबमा, PostgreSQL मा सबै लेख दुई पटक हुन्छ - WAL मा पहिलो, त्यसपछि तालिका/सूचकांक निकायहरूमा। यो सबै ACID लाई समर्थन गर्न र बीचको डाटा दृश्यता सही गर्न गरिन्छ COMMIT'अखरोट र ROLLBACK'शून्य लेनदेन।

तर हामीलाई यो आवश्यक छैन! हामीसँग सम्पूर्ण प्रक्रिया छ कि त यो पूर्ण रूपमा सफल भयो वा यो थिएन।। त्यहाँ कतिवटा मध्यवर्ती लेनदेनहरू हुनेछन् भन्ने कुराले फरक पार्दैन - हामी "बिचबाट प्रक्रिया जारी राख्न" मा रुचि राख्दैनौं, विशेष गरी जब यो कहाँ थियो स्पष्ट छैन।

यो गर्नको लागि, PostgreSQL विकासकर्ताहरू, संस्करण 9.1 मा फिर्ता, यस्तो चीज पेश गरे जस्तै अनलग गरिएका तालिकाहरू:

यो संकेत संग, तालिका अनलग को रूप मा सिर्जना गरिएको छ। अनलग गरिएको तालिकाहरूमा लेखिएको डाटा लेखन-अगाडि लग (अध्याय 29 हेर्नुहोस्) मार्फत जाँदैन, जसले गर्दा त्यस्ता तालिकाहरू सामान्य भन्दा धेरै छिटो काम। यद्यपि, तिनीहरू असफलताबाट मुक्त छैनन्; सर्भर विफलता वा आपतकालीन बन्दको अवस्थामा, अनलग गरिएको तालिका स्वचालित रूपमा काटियो। थप रूपमा, अनलग गरिएको तालिकाको सामग्रीहरू नक्कल गरिएको छैन दास सर्भरहरूमा। अनलग गरिएको तालिकामा सिर्जना गरिएका कुनै पनि अनुक्रमणिकाहरू स्वचालित रूपमा अनलग हुन्छन्।

छोटकरीमा यो धेरै छिटो हुनेछ, तर यदि डाटाबेस सर्भर "झर्छ", यो अप्रिय हुनेछ। तर यो कति पटक हुन्छ, र के तपाइँको ETL प्रक्रियाले डाटाबेसलाई "पुनरुत्थान" पछि यसलाई "बिचबाट" सही रूपमा कसरी सच्याउने भनेर थाहा छ?...

यदि होइन भने, र माथिको केस तपाईंको जस्तै छ, प्रयोग गर्नुहोस् UNLOGGEDतर कहिल्यै वास्तविक तालिकाहरूमा यो विशेषता सक्षम नगर्नुहोस्, जुन डाटा तपाईलाई प्रिय छ।

१.३। कमिटमा { पङ्क्तिहरू हटाउनुहोस् | ड्रप}

यो संरचनाले तालिका सिर्जना गर्दा लेनदेन पूरा हुँदा स्वचालित व्यवहार निर्दिष्ट गर्न अनुमति दिन्छ।

बारेमा 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 (हामीले यसको कल पनि सुरक्षित गर्यौं) अघिल्लो कारोबार पूरा गर्दा।

१.४। जस्तै... सहित...

मैले सुरुमा उल्लेख गरें कि अस्थायी तालिकाहरूको लागि सामान्य प्रयोग केसहरू मध्ये एक विभिन्न प्रकारका आयातहरू हुन् - र विकासकर्ताले थकित रूपमा लक्ष्य तालिकाको फिल्डहरूको सूचीलाई आफ्नो अस्थायी घोषणामा प्रतिलिपि टाँस्दछ ...

तर आलस्य प्रगतिको इन्जिन हो! त्यसैले होला "नमूनामा आधारित" नयाँ तालिका सिर्जना गर्नुहोस् यो धेरै सरल हुन सक्छ:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

यस तालिकामा तपाईले धेरै डाटा उत्पन्न गर्न सक्नु भएकोले, यसको माध्यमबाट खोजी कहिल्यै छिटो हुनेछैन। तर त्यहाँ यसको लागि परम्परागत समाधान छ - अनुक्रमणिकाहरू! र, हो, एक अस्थायी तालिका पनि अनुक्रमणिका हुन सक्छ.

किनकि, अक्सर, आवश्यक अनुक्रमणिकाहरू लक्ष्य तालिकाको अनुक्रमणिकासँग मेल खान्छ, तपाईं सजिलै लेख्न सक्नुहुन्छ LIKE target_table INCLUDING INDEXES.

यदि तपाईलाई पनि चाहिन्छ भने DEFAULT-values ​​(उदाहरणका लागि, प्राथमिक कुञ्जी मानहरू भर्न), तपाइँ प्रयोग गर्न सक्नुहुन्छ LIKE target_table INCLUDING DEFAULTS। वा सरल - LIKE target_table INCLUDING ALL - पूर्वनिर्धारित प्रतिलिपिहरू, अनुक्रमणिकाहरू, अवरोधहरू,...

तर यहाँ तपाईंले सिर्जना गर्नुभयो भने बुझ्न आवश्यक छ अनुक्रमणिकाको साथ तुरुन्तै तालिका आयात गर्नुहोस्, त्यसपछि डाटा लोड हुन लामो समय लाग्नेछयदि तपाईंले पहिले सबै कुरा भर्नुभयो भने, र त्यसपछि मात्र अनुक्रमणिकाहरू रोल गर्नुहोस् - उदाहरणको रूपमा यो कसरी गर्छ हेर्नुहोस्। pg_dump.

सामान्यमा RTFM प्रयोगकर्ता समीक्षाएँ, परीक्षण र राय!

2. कसरी लेख्ने?

मलाई मात्र भन्न दिनुहोस् - यसलाई प्रयोग गर्नुहोस् COPY- "प्याक" को सट्टा प्रवाह INSERT, पटक पटक गति। तपाइँ सिधै पूर्व-उत्पन्न फाइलबाट पनि सक्नुहुन्छ।

3. कसरी प्रशोधन गर्ने?

त्यसोभए, हाम्रो परिचयलाई यसरी हेरौं:

• तपाइँसँग तपाइँको डाटाबेसमा भण्डारण गरिएको क्लाइन्ट डाटाको साथ तालिका छ 1M रेकर्ड
• हरेक दिन ग्राहकले तपाईंलाई नयाँ पठाउँछन् पूर्ण "छवि"
• अनुभवबाट तपाईलाई समय समयमा थाहा छ 10K भन्दा बढी रेकर्डहरू परिवर्तन हुँदैनन्

यस्तो अवस्थाको उत्कृष्ट उदाहरण हो KLADR आधार — कुलमा धेरै ठेगानाहरू छन्, तर प्रत्येक साप्ताहिक अपलोडमा राष्ट्रिय स्तरमा पनि धेरै थोरै परिवर्तनहरू छन् (बस्तीहरूको नाम परिवर्तन, सडक संयोजन, नयाँ घरहरूको उपस्थिति)।

३.१। पूर्ण सिंक्रोनाइजेसन एल्गोरिथ्म

सरलताको लागि, मानौं कि तपाईले डेटालाई पुनर्संरचना गर्न आवश्यक पर्दैन - केवल तालिकालाई इच्छित फारममा ल्याउनुहोस्, त्यो हो:

• हटाउनुहोस् सबै कुरा जुन अब अवस्थित छैन
• अद्यावधिक गर्नुहोस् पहिले नै अवस्थित र अद्यावधिक गर्न आवश्यक सबै कुरा
• घुसाउनुहोस् सबै कुरा जुन अहिलेसम्म भएको छैन

किन यो क्रम मा सञ्चालन गर्नुपर्छ? किनभने यसरी तालिकाको आकार न्यूनतम रूपमा बढ्नेछ (MVCC सम्झनुहोस्!).

dst बाट मेटाउनुहोस्

होइन, निस्सन्देह तपाईले केवल दुई अपरेशनको साथ प्राप्त गर्न सक्नुहुन्छ:

• हटाउनुहोस् (DELETE) सबै सामान्य मा
• घुसाउनुहोस् सबै नयाँ छविबाट

तर एकै समयमा, MVCC लाई धन्यवाद, तालिकाको आकार ठीक दुई पटक बढ्नेछ! 1K अद्यावधिकको कारणले गर्दा तालिकामा रेकर्डहरूको +10M छविहरू प्राप्त गर्नु धेरै अनावश्यक छ...

TRUNCATE dst

एक अधिक अनुभवी विकासकर्तालाई थाहा छ कि सम्पूर्ण ट्याब्लेट सस्तोमा सफा गर्न सकिन्छ:

• स्पष्ट (TRUNCATE) सम्पूर्ण तालिका
• घुसाउनुहोस् सबै नयाँ छविबाट

विधि प्रभावकारी छ, कहिलेकाहीँ एकदम लागू हुन्छ, तर त्यहाँ एउटा समस्या छ... हामी लामो समयको लागि 1M रेकर्डहरू थप्दैछौं, त्यसैले हामी यति समयको लागि तालिकालाई खाली छोड्न सक्दैनौं (एउटै लेनदेनमा र्‍याप नगरिकन हुनेछ)।

त्यस्को मतलब:

• हामी सुरु गर्दैछौं लामो समय चलिरहेको लेनदेन
• TRUNCATE लगाउँछ पहुँच विशेष- अवरुद्ध
• हामी लामो समय सम्म सम्मिलन गर्छौं, र यस समयमा सबैजना पनि सक्दैन SELECT

केहि राम्रो भइरहेको छैन ...

तालिका परिवर्तन गर्नुहोस्... पुन: नामकरण गर्नुहोस्... / तालिका छोड्नुहोस्...

एउटा विकल्प भनेको सबै कुरालाई छुट्टै नयाँ तालिकामा भर्नु हो, र त्यसपछि पुरानोको सट्टा यसलाई पुन: नामाकरण गर्नु हो। केही नराम्रा साना चीजहरू:

• अझै पनि पहुँच विशेष, यद्यपि धेरै कम समय
• यस तालिकाका लागि सबै क्वेरी योजना/अंकहरू रिसेट गरिएका छन्, 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;

३.२। पोस्ट-प्रोसेसिङ आयात गर्नुहोस्

उही 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();

र त्यसपछि हामी शान्तपूर्वक लग तालिकाहरूबाट आवश्यक सबै परिवर्तनहरू निकाल्छौं र तिनीहरूलाई अतिरिक्त ह्यान्डलरहरू मार्फत चलाउँछौं।

३.३। लिङ्क गरिएका सेटहरू आयात गर्दै

माथि हामीले स्रोत र गन्तव्यको डेटा संरचनाहरू समान हुँदा केसहरू विचार गर्यौं। तर के हुन्छ यदि बाह्य प्रणालीबाट अपलोडको ढाँचा हाम्रो डाटाबेसमा भण्डारण संरचना भन्दा फरक छ?

उदाहरणका रूपमा ग्राहकहरूको भण्डारण र तिनीहरूको खाताहरू लिनुहोस्, क्लासिक "धेरै-देखि-एक" विकल्प:

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