DBA: ஒத்திசைவுகள் மற்றும் இறக்குமதிகளை திறமையாக ஒழுங்கமைத்தல்

பெரிய தரவுத் தொகுப்புகளின் சிக்கலான செயலாக்கத்திற்கு (வெவ்வேறு ETL செயல்முறைகள்: இறக்குமதிகள், மாற்றங்கள் மற்றும் வெளிப்புற மூலத்துடன் ஒத்திசைத்தல்) அடிக்கடி தேவை உள்ளது தற்காலிகமாக "நினைவில்" மற்றும் உடனடியாக விரைவாக செயலாக்க மிகப்பெரிய ஒன்று.

இந்த வகையான ஒரு பொதுவான பணி பொதுவாக இதுபோன்றது: "இங்கே வாடிக்கையாளர் வங்கியிலிருந்து கணக்கியல் துறை இறக்கப்பட்டது கடைசியாகப் பெறப்பட்ட பணம், அவற்றை இணையதளத்தில் விரைவாகப் பதிவேற்றி அவற்றை உங்கள் கணக்குகளுடன் இணைக்க வேண்டும்."

ஆனால் இந்த "ஏதாவது" அளவு நூற்றுக்கணக்கான மெகாபைட்களில் அளவிடத் தொடங்கும் போது, ​​​​சேவை 24x7 தரவுத்தளத்துடன் தொடர்ந்து வேலை செய்ய வேண்டும், உங்கள் வாழ்க்கையை அழிக்கும் பல பக்க விளைவுகள் எழுகின்றன.

PostgreSQL இல் அவற்றைச் சமாளிக்க (அதில் மட்டும் அல்ல), நீங்கள் சில மேம்படுத்தல்களைப் பயன்படுத்தலாம், அவை அனைத்தையும் விரைவாகவும் குறைந்த வள நுகர்வுடன் செயல்படுத்த அனுமதிக்கும்.

1. எங்கு அனுப்புவது?

முதலில், நாம் "செயல்படுத்த" விரும்பும் தரவை எங்கு பதிவேற்றலாம் என்பதை முடிவு செய்வோம்.

1.1 தற்காலிக அட்டவணைகள் (TEMPORARY TABLE)

கொள்கையளவில், PostgreSQL க்கு தற்காலிக அட்டவணைகள் மற்றவற்றைப் போலவே இருக்கும். எனவே, மூடநம்பிக்கைகள் போன்றவை "அங்குள்ள அனைத்தும் நினைவகத்தில் மட்டுமே சேமிக்கப்படுகின்றன, அது முடிவடையும்". ஆனால் பல குறிப்பிடத்தக்க வேறுபாடுகளும் உள்ளன.

தரவுத்தளத்திற்கான ஒவ்வொரு இணைப்பிற்கும் உங்கள் சொந்த "பெயர்வெளி"

இரண்டு இணைப்புகள் ஒரே நேரத்தில் இணைக்க முயற்சித்தால் CREATE TABLE x, பின்னர் யாராவது நிச்சயமாக பெறுவார்கள் தனித்தன்மை இல்லாத பிழை தரவுத்தள பொருள்கள்.

ஆனால் இருவரும் செயல்படுத்த முயற்சித்தால் CREATE TEMPORARY TABLE x, பின்னர் இருவரும் அதை சாதாரணமாக செய்வார்கள், அனைவருக்கும் கிடைக்கும் உங்கள் நகல் அட்டவணைகள். மேலும் அவர்களுக்கு இடையே பொதுவான எதுவும் இருக்காது.

துண்டிக்கும்போது "சுய அழிவு"

இணைப்பு மூடப்பட்டால், அனைத்து தற்காலிக அட்டவணைகளும் தானாகவே நீக்கப்படும், எனவே கைமுறையாக DROP TABLE x தவிர எந்த பிரயோஜனமும் இல்லை...

நீங்கள் வேலை செய்கிறீர்கள் என்றால் பரிவர்த்தனை முறையில் pgbouncer, இந்த இணைப்பு இன்னும் செயலில் இருப்பதாக தரவுத்தளம் தொடர்ந்து நம்புகிறது, மேலும் அதில் இந்த தற்காலிக அட்டவணை இன்னும் உள்ளது.

எனவே, வேறு இணைப்பிலிருந்து pgbouncer க்கு மீண்டும் உருவாக்க முயற்சித்தால் பிழை ஏற்படும். ஆனால் இதைப் பயன்படுத்துவதன் மூலம் தவிர்க்கலாம் CREATE TEMPORARY TABLE IF NOT EXISTS x.

உண்மை, இதை எப்படியும் செய்யாமல் இருப்பது நல்லது, ஏனென்றால் "முந்தைய உரிமையாளரிடமிருந்து" மீதமுள்ள தரவை நீங்கள் "திடீரென்று" காணலாம். அதற்கு பதிலாக, கையேட்டைப் படித்து, அட்டவணையை உருவாக்கும்போது அதைச் சேர்க்க முடியும் என்பதைப் பார்ப்பது மிகவும் நல்லது ON COMMIT DROP - அதாவது, பரிவர்த்தனை முடிந்ததும், அட்டவணை தானாகவே நீக்கப்படும்.

பிரதி செய்யாதது

அவை ஒரு குறிப்பிட்ட இணைப்பிற்கு மட்டுமே சொந்தமானவை என்பதால், தற்காலிக அட்டவணைகள் நகலெடுக்கப்படுவதில்லை. ஆனாலும் இது தரவை இரட்டைப் பதிவு செய்வதற்கான தேவையை நீக்குகிறது குவியல் + WAL இல், அதில் செருகுதல்/புதுப்பித்தல்/நீக்குதல் ஆகியவை கணிசமாக வேகமாக இருக்கும்.

ஆனால் ஒரு தற்காலிக அட்டவணை இன்னும் "கிட்டத்தட்ட சாதாரண" அட்டவணையாக இருப்பதால், அதை ஒரு பிரதியிலும் உருவாக்க முடியாது. குறைந்தபட்சம் இப்போதைக்கு, தொடர்புடைய இணைப்பு நீண்ட காலமாக புழக்கத்தில் இருந்தாலும்.

1.2 இணைக்கப்படாத அட்டவணை

ஆனால் நீங்கள் என்ன செய்ய வேண்டும், எடுத்துக்காட்டாக, உங்களிடம் ஒரு பரிவர்த்தனைக்குள் செயல்படுத்த முடியாத சில சிக்கலான ETL செயல்முறை இருந்தால், ஆனால் உங்களிடம் இன்னும் உள்ளது பரிவர்த்தனை முறையில் pgbouncer? ..

அல்லது தரவு ஓட்டம் மிகவும் பெரியது ஒரு இணைப்பில் போதுமான அலைவரிசை இல்லை ஒரு தரவுத்தளத்திலிருந்து (படிக்க, ஒரு CPU ஒன்றுக்கு ஒரு செயல்முறை)?..

அல்லது சில செயல்பாடுகள் நடக்கின்றன ஒத்திசைவற்ற முறையில் வெவ்வேறு தொடர்புகளில்?..

இங்கே ஒரே ஒரு விருப்பம் உள்ளது - தற்காலிகமாக அல்லாத தற்காலிக அட்டவணையை உருவாக்கவும். புன், ஆமாம். அது:

• யாருடனும் குறுக்கிடாதபடி, "எனது சொந்த" அட்டவணைகளை அதிகபட்ச சீரற்ற பெயர்களுடன் உருவாக்கியது
• சாரம்: வெளிப்புற மூலத்திலிருந்து அவற்றை நிரப்பியது
• மாற்றும்: மாற்றப்பட்டது, முக்கிய இணைக்கும் புலங்களில் நிரப்பப்பட்டது
• சுமை: இலக்கு அட்டவணைகளில் தயாராக தரவு ஊற்றப்பட்டது
• "எனது" அட்டவணைகள் நீக்கப்பட்டன

இப்போது - களிம்பு ஒரு ஈ. உண்மையாக, PostgreSQL இல் எழுதப்பட்ட அனைத்தும் இரண்டு முறை நடக்கும் - WAL இல் முதலில், பின்னர் அட்டவணை/இண்டெக்ஸ் உடல்களுக்குள். இவை அனைத்தும் ACID ஐ ஆதரிக்கவும், இடையில் தரவுத் தெரிவுநிலையை சரிசெய்யவும் செய்யப்படுகிறது COMMIT'நட்டு மற்றும் ROLLBACK'பூஜ்ய பரிவர்த்தனைகள்.

ஆனால் எங்களுக்கு இது தேவையில்லை! எங்களிடம் முழு செயல்முறையும் உள்ளது ஒன்று அது முற்றிலும் வெற்றிகரமாக இருந்தது அல்லது அது இல்லை.. எத்தனை இடைநிலை பரிவர்த்தனைகள் இருக்கும் என்பது முக்கியமல்ல - "நடுவில் இருந்து செயல்முறையைத் தொடர்வதில்" நாங்கள் ஆர்வம் காட்டவில்லை, குறிப்பாக அது எங்கே இருந்தது என்பது தெளிவாகத் தெரியவில்லை.

இதைச் செய்ய, PostgreSQL டெவலப்பர்கள், பதிப்பு 9.1 இல், இது போன்ற ஒரு விஷயத்தை அறிமுகப்படுத்தினர் UNLOGGED அட்டவணைகள்:

இந்த அறிகுறியுடன், அட்டவணை பதிவு செய்யப்படாததாக உருவாக்கப்படுகிறது. பதிவு செய்யப்படாத அட்டவணைகளுக்கு எழுதப்பட்ட தரவு, எழுதுவதற்கு முன் பதிவின் வழியாக செல்லாது (அத்தியாயம் 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. எப்படி செயலாக்குவது?

எனவே, எங்கள் அறிமுகம் இப்படி இருக்கட்டும்:

• உங்கள் தரவுத்தளத்தில் சேமிக்கப்பட்ட கிளையன்ட் தரவுகளுடன் அட்டவணை உள்ளது 1 மில்லியன் பதிவுகள்
• ஒவ்வொரு நாளும் ஒரு வாடிக்கையாளர் உங்களுக்கு புதிய ஒன்றை அனுப்புகிறார் முழு "படம்"
• அனுபவத்தில் இருந்து அவ்வப்போது தெரியும் 10Kக்கு மேல் பதிவுகள் மாற்றப்படவில்லை

அத்தகைய சூழ்நிலைக்கு ஒரு சிறந்த உதாரணம் KLADR அடிப்படை - மொத்தம் நிறைய முகவரிகள் உள்ளன, ஆனால் ஒவ்வொரு வாராந்திர பதிவேற்றத்திலும் தேசிய அளவில் கூட மிகக் குறைவான மாற்றங்கள் (குடியிருப்புகளின் மறுபெயரிடுதல், தெருக்களை இணைத்தல், புதிய வீடுகளின் தோற்றம்) உள்ளன.

3.1 முழு ஒத்திசைவு அல்காரிதம்

எளிமைக்காக, நீங்கள் தரவை மறுகட்டமைக்க வேண்டிய அவசியமில்லை என்று சொல்லலாம் - அட்டவணையை விரும்பிய வடிவத்தில் கொண்டு வாருங்கள், அதாவது:

• நீக்க இனி இல்லாத அனைத்தும்
• புதுப்பிக்க ஏற்கனவே இருந்த மற்றும் புதுப்பிக்கப்பட வேண்டிய அனைத்தும்
• நுழைக்க இதுவரை நடக்காத அனைத்தும்

இந்த வரிசையில் செயல்பாடுகள் ஏன் செய்யப்பட வேண்டும்? ஏனென்றால், அட்டவணை அளவு குறைவாக வளரும் (MVCC ஐ நினைவில் கொள்க!).

dst இலிருந்து நீக்கு

இல்லை, நிச்சயமாக நீங்கள் இரண்டு செயல்பாடுகள் மூலம் பெறலாம்:

• நீக்க (DELETE) பொதுவாக எல்லாம்
• நுழைக்க அனைத்தும் புதிய படத்திலிருந்து

ஆனால் அதே நேரத்தில், MVCC க்கு நன்றி, அட்டவணையின் அளவு சரியாக இரண்டு மடங்கு அதிகரிக்கும்! 1K புதுப்பிப்பு காரணமாக அட்டவணையில் +10M பதிவுகளின் படங்களைப் பெறுவது மிகவும் பணிநீக்கம் ஆகும்...

டி.எஸ்.டி

முழு டேப்லெட்டையும் மிகவும் மலிவாக சுத்தம் செய்ய முடியும் என்பதை அனுபவம் வாய்ந்த டெவலப்பருக்குத் தெரியும்:

• очистить (TRUNCATE) முழு அட்டவணை
• நுழைக்க அனைத்தும் புதிய படத்திலிருந்து

முறை பயனுள்ளதாக இருக்கும், சில நேரங்களில் மிகவும் பொருந்தும், ஆனால் ஒரு சிக்கல் உள்ளது... நாங்கள் நீண்ட காலமாக 1M பதிவுகளைச் சேர்ப்போம், எனவே இவ்வளவு நேரம் அட்டவணையை காலியாக வைக்க முடியாது (ஒரு பரிவர்த்தனையில் அதை மூடாமல் நடக்கும்).

இதன் பொருள்:

• நாங்கள் தொடங்குகிறோம் நீண்ட கால பரிவர்த்தனை
• TRUNCATE சுமத்துகிறது பிரத்தியேக அணுகல்-தடுத்தல்
• நாங்கள் நீண்ட நேரம் செருகுவதைச் செய்கிறோம், மற்ற அனைவரும் இந்த நேரத்தில் கூட முடியாது SELECT

ஏதோ சரியாக நடக்கவில்லை...

அட்டவணையை மாற்று... மறுபெயரிடு... / அட்டவணையை இறக்கு...

ஒரு மாற்றாக, எல்லாவற்றையும் ஒரு தனி புதிய அட்டவணையில் நிரப்பவும், பின்னர் அதை பழைய இடத்திற்கு மாற்றவும். சில மோசமான சிறிய விஷயங்கள்:

• இன்னும் கூட பிரத்தியேக அணுகல், கணிசமாக குறைந்த நேரம் என்றாலும்
• இந்த அட்டவணைக்கான அனைத்து வினவல் திட்டங்கள்/புள்ளிவிவரங்களும் மீட்டமைக்கப்பட்டுள்ளன, பகுப்பாய்வு இயக்க வேண்டும்
• அனைத்து வெளிநாட்டு விசைகளும் உடைந்தன (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