DBA: จัดระเบียบการซิงโครไนซ์และการนำเข้าอย่างมีความสามารถ

สำหรับการประมวลผลชุดข้อมูลขนาดใหญ่ที่ซับซ้อน (ต่างกัน กระบวนการ ETL: การนำเข้า การแปลง และการซิงโครไนซ์กับแหล่งภายนอก) มักมีความจำเป็น “จดจำ” ชั่วคราวและดำเนินการอย่างรวดเร็วทันที บางสิ่งบางอย่างมากมาย

งานทั่วไปประเภทนี้มักจะมีลักษณะดังนี้: "ที่นี่ แผนกบัญชีขนถ่ายจากธนาคารลูกค้า การชำระเงินที่ได้รับครั้งล่าสุด คุณจะต้องอัปโหลดไปยังเว็บไซต์อย่างรวดเร็วและเชื่อมโยงกับบัญชีของคุณ"

แต่เมื่อปริมาณของ "บางสิ่ง" นี้เริ่มวัดเป็นร้อยเมกะไบต์ และบริการจะต้องทำงานกับฐานข้อมูลต่อไปตลอด 24 ชั่วโมงทุกวัน ผลข้างเคียงมากมายก็เกิดขึ้นที่จะทำลายชีวิตของคุณ

หากต้องการจัดการกับสิ่งเหล่านี้ใน 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 ในโหมดธุรกรรม? ..

หรือกระแสข้อมูลมีขนาดใหญ่ขนาดนั้น มีแบนด์วิธไม่เพียงพอในการเชื่อมต่อครั้งเดียว จากฐานข้อมูล (อ่าน หนึ่งกระบวนการต่อ CPU)?..

หรือมีการดำเนินการบางอย่างเกิดขึ้น แบบอะซิงโครนัส ในการเชื่อมต่อที่แตกต่างกัน?..

มีทางเลือกเดียวเท่านั้นที่นี่ - สร้างตารางที่ไม่ใช่ชั่วคราวชั่วคราว. ปัน ใช่ครับ นั่นคือ:

• สร้างตาราง "ของฉันเอง" โดยมีชื่อแบบสุ่มสูงสุดเพื่อไม่ให้ตัดกับใครเลย
• สารสกัด: เติมข้อมูลจากแหล่งภายนอก
• แปลง: แปลงแล้ว กรอกข้อมูลในช่องลิงก์คีย์แล้ว
• โหลด: เทข้อมูลที่พร้อมลงในตารางเป้าหมาย
• ลบตาราง "ของฉัน"

และตอนนี้ - แมลงวันในครีม ในความเป็นจริง, การเขียนทั้งหมดใน PostgreSQL เกิดขึ้นสองครั้ง - ครั้งแรกใน WALจากนั้นลงในเนื้อหาตาราง/ดัชนี ทั้งหมดนี้ทำเพื่อรองรับ ACID และแก้ไขการมองเห็นข้อมูลระหว่างกัน COMMIT'กังวลและ ROLLBACK'ธุรกรรมที่เป็นโมฆะ

แต่เราไม่ต้องการสิ่งนี้! เรามีกระบวนการทั้งหมด ไม่ว่าจะประสบความสำเร็จอย่างสมบูรณ์หรือไม่ก็ตาม. มันไม่สำคัญว่าจะมีธุรกรรมตัวกลางจำนวนเท่าใด - เราไม่สนใจที่จะ "ดำเนินกระบวนการต่อจากตรงกลาง" โดยเฉพาะอย่างยิ่งเมื่อไม่ชัดเจนว่ามันอยู่ที่ไหน

ในการทำเช่นนี้นักพัฒนา PostgreSQL ในเวอร์ชัน 9.1 ได้แนะนำสิ่งนี้เช่น ตารางที่ไม่ได้บันทึก:

ด้วยข้อบ่งชี้นี้ ตารางจะถูกสร้างขึ้นเป็นไม่ได้เข้าสู่ระบบ ข้อมูลที่เขียนลงในตารางที่ไม่ได้บันทึกจะไม่ผ่านบันทึกการเขียนล่วงหน้า (ดูบทที่ 29) ทำให้ตารางดังกล่าว ทำงานเร็วกว่าปกติมาก. อย่างไรก็ตาม พวกเขาไม่สามารถต้านทานความล้มเหลวได้ ในกรณีที่เซิร์ฟเวอร์ขัดข้องหรือปิดระบบฉุกเฉิน ตารางที่ไม่ได้บันทึก ถูกตัดทอนโดยอัตโนมัติ. นอกจากนี้ เนื้อหาของตารางที่ไม่ได้บันทึก ไม่ได้ทำซ้ำ ไปยังเซิร์ฟเวอร์ทาส ดัชนีใดๆ ที่สร้างบนตารางที่ไม่ได้บันทึกจะถูกยกเลิกการบันทึกโดยอัตโนมัติ

ในระยะสั้น มันจะเร็วขึ้นมากแต่ถ้าเซิร์ฟเวอร์ฐานข้อมูล "ตก" ก็จะไม่เป็นที่พอใจ แต่สิ่งนี้เกิดขึ้นบ่อยแค่ไหน และกระบวนการ ETL ของคุณรู้วิธีแก้ไขอย่างถูกต้อง “จากตรงกลาง” หลังจาก “ฟื้นฟู” ฐานข้อมูลหรือไม่..

ถ้าไม่ และกรณีข้างต้นก็คล้ายกับของคุณ ให้ใช้ UNLOGGEDแต่ไม่เคย อย่าเปิดใช้งานคุณลักษณะนี้บนตารางจริงข้อมูลที่เป็นที่รักของคุณ

1.3. เมื่อกระทำการ { ลบแถว | หยด}

โครงสร้างนี้ช่วยให้คุณสามารถระบุพฤติกรรมอัตโนมัติเมื่อธุรกรรมเสร็จสมบูรณ์เมื่อสร้างตาราง

เกี่ยวกับ 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. จะดำเนินการอย่างไร?

มาดู intro ของเรากันดีกว่า:

• คุณมีตารางที่มีข้อมูลลูกค้าเก็บไว้ในฐานข้อมูลของคุณ 1M บันทึก
• ทุกวันลูกค้าจะส่งอันใหม่ให้คุณ "ภาพ" เต็มๆ
• จากประสบการณ์ท่านก็ทราบมาบ้างเป็นบางครั้งบางคราว มีการเปลี่ยนแปลงบันทึกไม่เกิน 10K

ตัวอย่างคลาสสิกของสถานการณ์เช่นนี้คือ ฐาน KLADR/คลาดร์ — มีที่อยู่ทั้งหมดจำนวนมาก แต่ในการอัปโหลดแต่ละครั้งในแต่ละสัปดาห์ มีการเปลี่ยนแปลงน้อยมาก (การเปลี่ยนชื่อการตั้งถิ่นฐาน การรวมถนน รูปลักษณ์ของบ้านใหม่) แม้แต่ในระดับประเทศ

3.1. อัลกอริธึมการซิงโครไนซ์แบบเต็ม

เพื่อความง่าย สมมติว่าคุณไม่จำเป็นต้องปรับโครงสร้างข้อมูลใหม่ด้วยซ้ำ เพียงนำตารางมาในรูปแบบที่ต้องการ ซึ่งก็คือ:

• เอาออก ทุกสิ่งที่ไม่มีอยู่อีกต่อไป
• เพื่ออัปเดต ทุกสิ่งที่มีอยู่แล้วและจำเป็นต้องได้รับการอัปเดต
• แทรก ทุกสิ่งที่ยังไม่เกิดขึ้น

เหตุใดจึงต้องดำเนินการตามลำดับนี้ เพราะนี่คือขนาดโต๊ะที่จะขยายน้อยที่สุด (จำ MVCC ไว้!).

ลบออกจากวันที่

ไม่ แน่นอน คุณสามารถดำเนินการได้เพียงสองขั้นตอนเท่านั้น:

• เอาออก (DELETE) ทุกอย่างโดยทั่วไป
• แทรก ทั้งหมดจากภาพใหม่

แต่ในขณะเดียวกัน ต้องขอบคุณ MVCC ขนาดของตารางจะเพิ่มขึ้นสองเท่าอย่างแน่นอน! การได้รับอิมเมจ +1 ล้านอิมเมจในตารางเนื่องจากการอัปเดต 10K ถือเป็นความซ้ำซ้อน...

ตัดทอน

นักพัฒนาที่มีประสบการณ์มากกว่ารู้ว่าสามารถทำความสะอาดแท็บเล็ตทั้งหมดได้ในราคาถูก:

• เพื่อล้าง (TRUNCATE) ทั้งตาราง
• แทรก ทั้งหมดจากภาพใหม่

วิธีการนี้ได้ผล บางครั้งก็ค่อนข้างใช้ได้แต่มีปัญหาเกิดขึ้น... เราจะเพิ่มบันทึก 1 ล้านรายการเป็นเวลานาน ดังนั้นเราจึงไม่สามารถปล่อยตารางให้ว่างไว้ตลอดเวลานี้ได้ (ดังที่จะเกิดขึ้นโดยไม่ต้องห่อไว้ในธุรกรรมเดียว)

ซึ่งหมายความว่า:

• เรากำลังเริ่มต้น การทำธุรกรรมระยะยาว
• TRUNCATE กำหนด การเข้าถึงพิเศษ-การปิดกั้น
• เราทำการแทรกมาเป็นเวลานานและทุกคนในเวลานี้ ไม่สามารถด้วยซ้ำ SELECT

มีบางอย่างไม่ค่อยดีนัก...

แก้ไขตาราง… เปลี่ยนชื่อ… / วางตาราง…

อีกทางเลือกหนึ่งคือการเติมทุกอย่างลงในตารางใหม่แยกกัน จากนั้นเปลี่ยนชื่อแทนตารางเก่า สิ่งเล็กๆ น้อยๆ ที่น่ารังเกียจสองสามอย่าง:

• ยังเหมือนกัน การเข้าถึงพิเศษแม้ว่าจะใช้เวลาน้อยกว่ามากก็ตาม
• แผนการสืบค้น/สถิติทั้งหมดสำหรับตารางนี้ถูกรีเซ็ต จำเป็นต้องเรียกใช้การวิเคราะห์
• กุญแจต่างประเทศทั้งหมดเสีย (FK) ไปที่โต๊ะ

มีแพตช์ WIP จาก Simon Riggs ที่แนะนำให้ทำ 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ซึ่งเราจะแทรกใบแจ้งหนี้

ที่มา: will.com