DBA: দক্ষতার সাথে সিঙ্ক্রোনাইজেশন এবং আমদানি সংগঠিত করুন

বড় ডেটা সেটের জটিল প্রক্রিয়াকরণের জন্য (ভিন্ন ETL প্রক্রিয়া: আমদানি, রূপান্তর এবং একটি বাহ্যিক উত্সের সাথে সিঙ্ক্রোনাইজেশন) প্রায়শই একটি প্রয়োজন হয় অস্থায়ীভাবে "মনে রাখবেন" এবং অবিলম্বে দ্রুত প্রক্রিয়া করুন বিশাল কিছু

এই ধরনের একটি সাধারণ কাজ সাধারণত এই মত কিছু শোনায়: "এখানেই অ্যাকাউন্টিং বিভাগ ক্লায়েন্ট ব্যাংক থেকে আনলোড সর্বশেষ প্রাপ্ত অর্থপ্রদান, আপনাকে দ্রুত ওয়েবসাইটে আপলোড করতে হবে এবং আপনার অ্যাকাউন্টে লিঙ্ক করতে হবে।"

কিন্তু যখন এই "কিছু" এর ভলিউম শত শত মেগাবাইটে পরিমাপ করা শুরু হয় এবং পরিষেবাটিকে অবশ্যই 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 - অর্থাৎ, লেনদেন সম্পূর্ণ হলে, টেবিলটি স্বয়ংক্রিয়ভাবে মুছে যাবে।

অ-প্রতিলিপি

যেহেতু তারা শুধুমাত্র একটি নির্দিষ্ট সংযোগের অন্তর্গত, অস্থায়ী টেবিল প্রতিলিপি করা হয় না। কিন্তু এটি ডেটার ডবল রেকর্ডিংয়ের প্রয়োজনীয়তা দূর করে হিপ + ওয়াল-এ, তাই এতে ঢোকান/আপডেট/মুছে ফেলা উল্লেখযোগ্যভাবে দ্রুত।

কিন্তু যেহেতু একটি অস্থায়ী টেবিল এখনও একটি "প্রায় সাধারণ" টেবিল, তাই এটি একটি প্রতিলিপিতেও তৈরি করা যায় না। অন্তত আপাতত, যদিও সংশ্লিষ্ট প্যাচটি দীর্ঘদিন ধরে প্রচারিত হচ্ছে।

1.2। আনলগ করা টেবিল

কিন্তু আপনার কি করা উচিত, উদাহরণস্বরূপ, আপনার যদি কিছু কষ্টকর ETL প্রক্রিয়া থাকে যা একটি লেনদেনের মধ্যে প্রয়োগ করা যায় না, কিন্তু আপনার কাছে এখনও আছে লেনদেন মোডে pgbouncer? ..

বা ডেটা প্রবাহ এত বড় যে একটি সংযোগে পর্যাপ্ত ব্যান্ডউইথ নেই একটি ডাটাবেস থেকে (পড়ুন, সিপিইউ প্রতি একটি প্রক্রিয়া)? ..

নাকি কিছু অপারেশন চলছে অ্যাসিঙ্ক্রোনাসভাবে বিভিন্ন সংযোগে? ..

এখানে শুধুমাত্র একটি বিকল্প আছে - অস্থায়ীভাবে একটি অস্থায়ী টেবিল তৈরি করুন. পুন, হ্যাঁ। এটাই:

• সর্বাধিক এলোমেলো নামের সাথে "আমার নিজের" টেবিল তৈরি করেছি যাতে কারও সাথে ছেদ না হয়
• নির্যাস: একটি বাহ্যিক উত্স থেকে তথ্য দিয়ে তাদের পূর্ণ
• রুপান্তর: রূপান্তরিত, কী লিঙ্কিং ক্ষেত্রগুলি পূরণ করা হয়েছে
• বোঝা: টার্গেট টেবিলের মধ্যে প্রস্তুত তথ্য ঢেলে
• "আমার" টেবিল মুছে ফেলা হয়েছে

এবং এখন - মলম মধ্যে একটি মাছি। আসলে, PostgreSQL এ সব লেখা দুবার হয় - ওয়াল-এ প্রথম, তারপর টেবিল/সূচক সংস্থায়। এই সব করা হয় 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.

সাধারণভাবে RTFM!

2. কিভাবে লিখতে হয়?

আমাকে শুধু বলতে দিন - এটি ব্যবহার করুন COPY- "প্যাক" এর পরিবর্তে প্রবাহ INSERT, মাঝে মাঝে ত্বরণ. এমনকি আপনি সরাসরি একটি প্রাক-উত্পাদিত ফাইল থেকেও করতে পারেন।

3. কিভাবে প্রক্রিয়া?

সুতরাং, আসুন আমাদের ভূমিকাটি এইরকম দেখতে দিন:

• আপনার ডাটাবেসে সংরক্ষিত ক্লায়েন্ট ডেটা সহ একটি টেবিল রয়েছে 1M রেকর্ড
• প্রতিদিন একজন ক্লায়েন্ট আপনাকে একটি নতুন পাঠায় সম্পূর্ণ "চিত্র"
• অভিজ্ঞতা থেকে আপনি সময়ে সময়ে জানেন যে 10K এর বেশি রেকর্ড পরিবর্তন করা হয় না

এই ধরনের পরিস্থিতির একটি ক্লাসিক উদাহরণ KLADR বেস — মোট অনেকগুলি ঠিকানা আছে, কিন্তু প্রতিটি সাপ্তাহিক আপলোডে খুব কম পরিবর্তন হয় (বসতির নাম পরিবর্তন, রাস্তার সংমিশ্রণ, নতুন বাড়ির চেহারা) এমনকি একটি জাতীয় স্কেলে।

3.1। সম্পূর্ণ সিঙ্ক্রোনাইজেশন অ্যালগরিদম

সরলতার জন্য, আসুন বলি যে আপনার ডেটা পুনর্গঠন করার দরকার নেই - কেবল টেবিলটিকে পছন্দসই আকারে আনুন, এটি হল:

• অপসারণ সবকিছু যা আর বিদ্যমান নেই
• হালনাগাদ ইতিমধ্যে বিদ্যমান এবং আপডেট করা প্রয়োজন সবকিছু
• সন্নিবেশ করা সবকিছু যা এখনও ঘটেনি

কেন এই ক্রমে অপারেশন করা উচিত? কারণ এইভাবে টেবিলের আকার সর্বনিম্ন বৃদ্ধি পাবে (MVCC মনে রাখবেন!).

dst থেকে মুছুন

না, অবশ্যই আপনি মাত্র দুটি অপারেশনের মাধ্যমে পেতে পারেন:

• অপসারণ (DELETE) সাধারণভাবে সবকিছু
• সন্নিবেশ করা নতুন ইমেজ থেকে সব

কিন্তু একই সাথে, MVCC কে ধন্যবাদ, টেবিলের আকার ঠিক দ্বিগুণ বৃদ্ধি পাবে! 1K আপডেটের কারণে সারণীতে রেকর্ডের +10M ছবি পাওয়া খুবই অপ্রয়োজনীয়...

ট্রাঙ্কেট dst

একজন আরও অভিজ্ঞ বিকাশকারী জানেন যে সম্পূর্ণ ট্যাবলেটটি বেশ সস্তায় পরিষ্কার করা যেতে পারে:

• পরিষ্কার করা (TRUNCATE) পুরো টেবিল
• সন্নিবেশ করা নতুন ইমেজ থেকে সব

পদ্ধতি কার্যকর, কখনও কখনও বেশ প্রযোজ্য, কিন্তু একটি সমস্যা আছে... আমরা দীর্ঘ সময়ের জন্য 1M রেকর্ড যোগ করব, তাই আমরা এই সমস্ত সময়ের জন্য টেবিলটি খালি রাখতে পারি না (যেমন এটি একটি একক লেনদেনে মোড়ানো ছাড়াই ঘটবে)৷

যার অর্থ:

• আমরা শুরু করছি দীর্ঘমেয়াদী লেনদেন
• TRUNCATE আরোপ করে এক্সক্লুসিভ অ্যাক্সেস-অবরোধ
• আমরা একটি দীর্ঘ সময়ের জন্য সন্নিবেশ না, এবং অন্য সবাই এই সময়ে এমনকি পারে না SELECT

কিছু ভালো যাচ্ছে না...

সারণী পরিবর্তন করুন... পুনরায় নাম দিন... / সারণী বাদ দিন...

একটি বিকল্প হল একটি পৃথক নতুন টেবিলে সবকিছু পূরণ করা এবং তারপরে পুরানোটির জায়গায় এটির নাম পরিবর্তন করা। কিছু বাজে ছোট জিনিস:

• এখনও খুব এক্সক্লুসিভ অ্যাক্সেস, যদিও উল্লেখযোগ্যভাবে কম সময়
• এই টেবিলের জন্য সমস্ত ক্যোয়ারী প্ল্যান/পরিসংখ্যান রিসেট করা হয়েছে, ANALYZE চালাতে হবে
• সমস্ত বিদেশী কীগুলি ভেঙে গেছে (FK) টেবিলে

সাইমন রিগসের একটি ডাব্লুআইপি প্যাচ ছিল যা তৈরির পরামর্শ দিয়েছিল 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