ڈی بی اے: ہم آہنگی اور درآمدات کو قابلیت سے منظم کریں۔

بڑے ڈیٹا سیٹس کی پیچیدہ پروسیسنگ کے لیے (مختلف ای ٹی ایل کے عمل: درآمدات، تبادلوں اور بیرونی ذریعہ کے ساتھ ہم آہنگی) اکثر ضرورت ہوتی ہے۔ عارضی طور پر "یاد رکھیں" اور فوری طور پر کارروائی کریں۔ کچھ بڑا.

اس قسم کا ایک عام کام عام طور پر کچھ اس طرح لگتا ہے: "یہیں پر اکاؤنٹنگ ڈیپارٹمنٹ کلائنٹ بینک سے اتارا گیا۔ آخری موصول شدہ ادائیگیاں، آپ کو انہیں جلدی سے ویب سائٹ پر اپ لوڈ کرنے اور اپنے اکاؤنٹس سے لنک کرنے کی ضرورت ہے۔"

لیکن جب اس "کچھ" کا حجم سینکڑوں میگا بائٹس میں ناپنا شروع ہو جائے، اور سروس کو ڈیٹا بیس کے ساتھ 24x7 کام جاری رکھنا چاہیے، تو بہت سے مضر اثرات پیدا ہوتے ہیں جو آپ کی زندگی کو برباد کر دیتے ہیں۔

PostgreSQL میں ان سے نمٹنے کے لیے (اور نہ صرف اس میں)، آپ کچھ ایسی اصلاحیں استعمال کر سکتے ہیں جو آپ کو ہر چیز پر تیزی سے اور کم وسائل کی کھپت کے ساتھ کارروائی کرنے کی اجازت دے گی۔

1. کہاں بھیجنا ہے؟

سب سے پہلے، آئیے فیصلہ کریں کہ ہم وہ ڈیٹا کہاں اپ لوڈ کر سکتے ہیں جسے ہم "پروسیس" کرنا چاہتے ہیں۔

1.1 عارضی میزیں (عارضی میز)

اصولی طور پر، PostgreSQL کے لیے عارضی میزیں کسی بھی دوسرے جیسی ہیں۔ لہذا، توہمات پسند "وہاں ہر چیز صرف میموری میں محفوظ ہے، اور یہ ختم ہوسکتی ہے". لیکن کئی اہم اختلافات بھی ہیں۔

ڈیٹا بیس سے ہر ایک کنکشن کے لیے آپ کی اپنی "نام کی جگہ"

اگر دو کنکشن ایک ہی وقت میں جڑنے کی کوشش کریں۔ CREATE TABLE x، پھر کسی کو ضرور ملے گا۔ غیر انفرادیت کی خرابی۔ ڈیٹا بیس اشیاء.

لیکن اگر دونوں پھانسی کی کوشش کریں۔ CREATE TEMPORARY TABLE x، پھر دونوں اسے عام طور پر کریں گے، اور سب کو ملے گا۔ آپ کی کاپی میزیں اور ان کے درمیان کوئی چیز مشترک نہیں ہوگی۔

منقطع ہونے پر "خود کو تباہ کرنا"

کنکشن بند ہونے پر، تمام عارضی میزیں خود بخود حذف ہو جاتی ہیں، اس طرح دستی طور پر DROP TABLE x اس کے علاوہ کوئی فائدہ نہیں ہے...

اگر آپ کام کر رہے ہیں۔ پی جی باؤنسر ٹرانزیکشن موڈ میں، پھر ڈیٹا بیس یہ مانتا رہتا ہے کہ یہ کنکشن اب بھی فعال ہے، اور اس میں یہ عارضی جدول اب بھی موجود ہے۔

اس لیے، اسے دوبارہ بنانے کی کوشش، pgbouncer سے مختلف کنکشن سے، ایک خرابی کا باعث بنے گی۔ لیکن اس کو استعمال کرکے روکا جاسکتا ہے۔ CREATE TEMPORARY TABLE IF NOT EXISTS x.

سچ ہے، بہرحال ایسا نہ کرنا ہی بہتر ہے، کیونکہ پھر آپ وہاں "پچھلے مالک" سے بچا ہوا ڈیٹا "اچانک" تلاش کر سکتے ہیں۔ اس کے بجائے، دستی کو پڑھنا اور یہ دیکھنا بہت بہتر ہے کہ ٹیبل بناتے وقت اسے شامل کرنا ممکن ہے۔ ON COMMIT DROP - یعنی، جب لین دین مکمل ہو جائے گا، ٹیبل خود بخود حذف ہو جائے گا۔

غیر نقل

چونکہ وہ صرف ایک مخصوص کنکشن سے تعلق رکھتے ہیں، عارضی میزیں نقل نہیں کی جاتی ہیں۔ لیکن یہ ڈیٹا کی ڈبل ریکارڈنگ کی ضرورت کو ختم کرتا ہے۔ ہیپ + وال میں، لہذا اس میں INSERT/UPDATE/DELETE بہت تیز ہے۔

لیکن چونکہ ایک عارضی میز اب بھی ایک "تقریباً عام" جدول ہے، اس لیے اسے نقل پر بھی نہیں بنایا جا سکتا۔ کم از کم ابھی کے لیے، اگرچہ متعلقہ پیچ کافی عرصے سے گردش کر رہا ہے۔

1.2 غیر لاگ شدہ ٹیبل

لیکن آپ کو کیا کرنا چاہیے، مثال کے طور پر، اگر آپ کے پاس ETL کا کچھ بوجھل عمل ہے جسے ایک لین دین میں لاگو نہیں کیا جا سکتا، لیکن آپ کے پاس پھر بھی پی جی باؤنسر ٹرانزیکشن موڈ میں؟ ..

یا ڈیٹا کا بہاؤ اتنا بڑا ہے کہ ایک کنکشن پر کافی بینڈوتھ نہیں ہے۔ ڈیٹا بیس سے (پڑھیں، ایک عمل فی 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-values ​​(مثال کے طور پر، بنیادی کلیدی اقدار کو بھرنے کے لیے)، آپ استعمال کر سکتے ہیں۔ LIKE target_table INCLUDING DEFAULTS. یا صرف - LIKE target_table INCLUDING ALL - پہلے سے طے شدہ کاپیاں، اشاریہ جات، رکاوٹیں،...

لیکن یہاں آپ کو یہ سمجھنے کی ضرورت ہے کہ اگر آپ نے تخلیق کیا ہے۔ اشاریہ جات کے ساتھ فوری طور پر ٹیبل درآمد کریں، پھر ڈیٹا کو لوڈ ہونے میں زیادہ وقت لگے گا۔اس کے مقابلے میں اگر آپ سب سے پہلے سب کچھ پُر کریں، اور تب ہی اشاریہ جات کو رول کریں - دیکھیں کہ یہ کیسے کرتا ہے مثال کے طور پر pg_dump.

عام طور پر آر ٹی ایف ایم۔!

2. کیسے لکھیں؟

مجھے صرف یہ کہنے دو - اسے استعمال کریں۔ COPY- "پیک" کے بجائے بہاؤ INSERT, اوقات میں سرعت. آپ پہلے سے تیار کردہ فائل سے بھی براہ راست کر سکتے ہیں۔

3. عمل کیسے کریں؟

تو آئیے اپنا تعارف کچھ اس طرح دیکھتے ہیں:

• آپ کے پاس اپنے ڈیٹا بیس میں کلائنٹ کے ڈیٹا کے ساتھ ایک ٹیبل موجود ہے۔ 1M ریکارڈ
• ہر روز ایک کلائنٹ آپ کو ایک نیا بھیجتا ہے۔ مکمل "تصویر"
• تجربے سے آپ جانتے ہیں کہ وقتاً فوقتاً 10K سے زیادہ ریکارڈز تبدیل نہیں ہوتے ہیں۔

ایسی صورت حال کی ایک کلاسک مثال ہے۔ KLADR بیس - مجموعی طور پر بہت سارے پتے ہیں، لیکن ہر ہفتہ وار اپ لوڈ میں قومی سطح پر بھی بہت کم تبدیلیاں ہوتی ہیں (بستیوں کا نام تبدیل کرنا، گلیوں کو ملانا، نئے مکانات کی شکل)۔

3.1 مکمل مطابقت پذیری الگورتھم

سادگی کے لیے، ہم کہتے ہیں کہ آپ کو ڈیٹا کو ری اسٹرکچر کرنے کی بھی ضرورت نہیں ہے - صرف ٹیبل کو مطلوبہ شکل میں لائیں، یعنی:

• ہٹائیں۔ ہر وہ چیز جو اب موجود نہیں ہے۔
• اپ ڈیٹ ہر وہ چیز جو پہلے سے موجود ہے اور اسے اپ ڈیٹ کرنے کی ضرورت ہے۔
• داخل کرنے کے لئے سب کچھ جو ابھی تک نہیں ہوا ہے

اس ترتیب میں کارروائیاں کیوں کی جائیں؟ کیونکہ اس طرح میز کا سائز کم سے کم بڑھے گا (MVCC یاد رکھیں!).

ڈی ایس ٹی سے حذف کریں۔

نہیں، یقیناً آپ صرف دو آپریشنز کے ذریعے حاصل کر سکتے ہیں:

• ہٹائیں۔ (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;

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