ዲቢኤ፡ ማመሳሰልን እና ማስመጣትን በብቃት ማደራጀት።

ለትልቅ የውሂብ ስብስቦች ውስብስብ ሂደት (የተለያዩ ETL ሂደቶችከውጪ ምንጭ ጋር ማስመጣት፣ መለወጥ እና ማመሳሰል) ብዙ ጊዜ ፍላጎት አለ። ለጊዜው "አስታውስ" እና ወዲያውኑ በፍጥነት ሂደቱን ጥራዝ የሆነ ነገር.

የዚህ ዓይነቱ የተለመደ ተግባር ብዙውን ጊዜ እንደዚህ ያለ ነገር ይመስላል- "እዚህ ጋ የሂሳብ ክፍል ከደንበኛው ባንክ ወረደ ለመጨረሻ ጊዜ የተቀበሉት ክፍያዎች በፍጥነት ወደ ድህረ ገጹ መስቀል እና ከመለያዎ ጋር ማገናኘት ያስፈልግዎታል"

ነገር ግን የዚህ "ነገር" መጠን በመቶዎች በሚቆጠሩ ሜጋባይት ውስጥ መለካት ሲጀምር እና አገልግሎቱ ከመረጃ ቋቱ 24x7 ጋር መስራቱን መቀጠል አለበት, ብዙ የጎንዮሽ ጉዳቶች ህይወቶን ያበላሻሉ.

በ 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 - ማለትም ግብይቱ ሲጠናቀቅ ሠንጠረዡ በራስ-ሰር ይሰረዛል።

የማይባዛ

እነሱ የአንድ የተወሰነ ግንኙነት ብቻ ስለሆኑ፣ ጊዜያዊ ሠንጠረዦች አይደገሙም። ግን ይህ የውሂብ ድርብ መቅረጽ አስፈላጊነትን ያስወግዳል በ heap + WAL፣ ስለዚህ አስገባ/አዘምን/ሰርዝ በጣም ፈጣን ነው።

ነገር ግን ጊዜያዊ ሠንጠረዥ አሁንም "ከሞላ ጎደል ተራ" ጠረጴዛ ስለሆነ, በተባዛ ላይም ሊፈጠር አይችልም. ቢያንስ ለአሁኑ ፣ ምንም እንኳን ተጓዳኝ ማጣበቂያው ለረጅም ጊዜ እየተሰራጨ ቢሆንም።

1.2. ያልተመዘገበ ጠረጴዛ

ግን ምን ማድረግ እንዳለቦት ለምሳሌ በአንድ ግብይት ውስጥ ሊተገበር የማይችል ከባድ የኢቲኤል ሂደት ካለህ ግን አሁንም አለህ። pgbouncer በግብይት ሁነታ? ..

ወይም የመረጃ ፍሰቱ በጣም ትልቅ ነው በአንድ ግንኙነት ላይ በቂ የመተላለፊያ ይዘት የለም። ከመረጃ ቋት (አንብብ፣ አንድ ሂደት በአንድ ሲፒዩ)?...

ወይም አንዳንድ ስራዎች እየተከናወኑ ነው። አልተመሳሰልም። በተለያዩ ግንኙነቶች? ..

እዚህ አንድ አማራጭ ብቻ አለ - ለጊዜው ጊዜያዊ ያልሆነ ጠረጴዛ ይፍጠሩ. ፑን፣ አዎ። ያውና:

• ከማንም ጋር ላለመገናኘት "የራሴ" ጠረጴዛዎችን በከፍተኛ የዘፈቀደ ስሞች ፈጠረ
• ማውጣትከውጭ ምንጭ በተገኘ መረጃ ሞላባቸው
• ለውጥ: ተለወጠ፣ በቁልፍ ማያያዣ መስኮች ተሞልቷል።
• ሸክምዝግጁ ውሂብ ወደ ኢላማ ሰንጠረዦች ፈሰሰ
• "የእኔ" ሰንጠረዦች ተሰርዘዋል

እና አሁን - በቅባት ውስጥ ዝንብ. በእውነቱ, ሁሉም በ PostgreSQL ውስጥ ይጽፋሉ ሁለት ጊዜ ይከሰታሉ - በመጀመሪያ በ WAL, ከዚያም ወደ ጠረጴዛ / ጠቋሚ አካላት. ይህ ሁሉ የሚደረገው ACIDን ለመደገፍ እና በመካከላቸው ያለውን የውሂብ ታይነት ለማስተካከል ነው። COMMIT'nutty እና ROLLBACKባዶ ግብይቶች።

ግን ይህ አያስፈልገንም! አጠቃላይ ሂደቱን አለን። ሙሉ በሙሉ የተሳካ ነበር ወይም አልነበረም።. ምን ያህል መካከለኛ ግብይቶች እንደሚኖሩ ምንም ችግር የለውም - በተለይም የት እንደነበረ ግልጽ በማይሆንበት ጊዜ "ሂደቱን ከመሃል ለመቀጠል" ፍላጎት የለንም.

ይህንን ለማድረግ፣ የ ​​PostgreSQL ገንቢዎች፣ ወደ ስሪት 9.1 ተመልሰው እንደዚህ ያለ ነገር አስተዋውቀዋል UNLOGGED ሰንጠረዦች:

በዚህ አመላካች, ሰንጠረዡ እንደ ተለቀቀ ተፈጠረ. ወደ ላልገቡ ሠንጠረዦች የተፃፈ መረጃ ወደፊት ይፃፋል (ምዕራፍ 29 ን ይመልከቱ) ይህም ሠንጠረዦችን ያስከትላል. ከወትሮው በበለጠ ፍጥነት መስራት. ይሁን እንጂ ከውድቀት ነፃ አይደሉም; የአገልጋይ ውድቀት ወይም የአደጋ ጊዜ መዘጋት ከሆነ፣ ያልተመዘገበ ጠረጴዛ በራስ-ሰር ተቆርጧል. በተጨማሪም ፣ ያልተመዘገበው ሰንጠረዥ ይዘቶች አልተደገመም። ወደ ባሪያ አገልጋዮች. ባልተመዘገበ ሠንጠረዥ ላይ የተፈጠሩ ማንኛቸውም ኢንዴክሶች በራስ ሰር ይከፈታሉ።

በአጭሩ፣ በጣም ፈጣን ይሆናል, ነገር ግን የውሂብ ጎታ አገልጋዩ "ከወደቀ" ከሆነ, ደስ የማይል ይሆናል. ግን ይህ ለምን ያህል ጊዜ ይከሰታል እና የ ETL ሂደትዎ የውሂብ ጎታውን "ከታደሰ" በኋላ እንዴት በትክክል "ከመሃል" ማስተካከል እንዳለበት ያውቃል?

ካልሆነ እና ከላይ ያለው ጉዳይ ከእርስዎ ጋር ተመሳሳይ ነው, ይጠቀሙ UNLOGGEDግን በጭራሽ ይህንን ባህሪ በእውነተኛ ጠረጴዛዎች ላይ አታድርጉ, ለእርስዎ ውድ የሆነበት ውሂብ.

1.3. በቁርጠኝነት { ረድፎችን ሰርዝ | መጣል}

ይህ ግንባታ ሠንጠረዥ ሲፈጥሩ ግብይት ሲጠናቀቅ አውቶማቲክ ባህሪን እንዲገልጹ ያስችልዎታል።

ላይ ON COMMIT DROP አስቀድሜ ከላይ ጽፌያለሁ, ያመነጫል DROP TABLE፣ ግን ከ ጋር ON COMMIT DELETE ROWS ሁኔታው የበለጠ አስደሳች ነው - እዚህ የተፈጠረ ነው TRUNCATE TABLE.

የጊዜያዊ ሰንጠረዥን ሜታ-ገለፃ ለማከማቸት አጠቃላይ መሠረተ ልማት ከመደበኛ ሠንጠረዥ ጋር ተመሳሳይ ስለሆነ ፣ ከዚያ ጊዜያዊ ሰንጠረዦችን የማያቋርጥ መፍጠር እና መሰረዝ የስርዓት ሰንጠረዦችን ወደ ከባድ "እብጠት" ያመራል pg_class፣ pg_ባህሪ፣ 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_መጣል.

በአጠቃላይ RTFM!

2. እንዴት መጻፍ?

በቃ ልበል - ተጠቀምበት COPY- ከ "ጥቅል" ይልቅ ፍሰት INSERT, አንዳንድ ጊዜ ማፋጠን. ቀድሞ ከተፈጠረ ፋይል በቀጥታም ቢሆን ይችላሉ።

3. እንዴት ማስኬድ ይቻላል?

ስለዚህ መግቢያችን ይህን ይመስላል።

• በውሂብ ጎታዎ ውስጥ የተከማቸ የደንበኛ ውሂብ ያለው ጠረጴዛ አለዎት 1M መዝገቦች
• በየቀኑ ደንበኛ አዲስ ይልክልዎታል ሙሉ "ምስል"
• ከጊዜ ወደ ጊዜ ከልምድ ታውቃለህ ከ10ሺህ የማይበልጡ መዝገቦች አልተቀየሩም።

የዚህ ዓይነቱ ሁኔታ ዓይነተኛ ምሳሌ ነው። KLADR መሠረት - በአጠቃላይ ብዙ አድራሻዎች አሉ፣ ነገር ግን በእያንዳንዱ ሳምንታዊ ሰቀላ ውስጥ በአገር አቀፍ ደረጃ እንኳን በጣም ጥቂት ለውጦች (የሰፈሮች ስም መቀየር፣ መንገዶችን ማጣመር፣ የአዳዲስ ቤቶች ገጽታ) አሉ።

3.1. ሙሉ የማመሳሰል አልጎሪዝም

ለቀላልነት ፣ ውሂቡን እንደገና ማዋቀር እንኳን አያስፈልግዎትም እንበል - ሰንጠረዡን ወደሚፈለገው ቅጽ ብቻ ያቅርቡ ፣ ማለትም

• አስወግድ ከአሁን በኋላ የሌለ ነገር ሁሉ
• አዘምን ቀደም ሲል የነበሩትን እና መዘመን ያለበት ነገር ሁሉ
• ያስገቡ እስካሁን ያልተከሰተ ሁሉ

ክዋኔዎቹ በዚህ ቅደም ተከተል ለምን መደረግ አለባቸው? ምክንያቱም የጠረጴዛው መጠን በትንሹ የሚያድገው በዚህ መንገድ ነው (MVCC አስታውስ!).

ከ dst ሰርዝ

አይ ፣ በእርግጥ በሁለት ክዋኔዎች ብቻ ማግኘት ይችላሉ-

• አስወግድ (DELETE) በአጠቃላይ ሁሉም ነገር
• ያስገቡ ሁሉም ከአዲሱ ምስል

ግን በተመሳሳይ ጊዜ ለ MVCC ምስጋና ይግባውና የጠረጴዛው መጠን በትክክል ሁለት ጊዜ ይጨምራል! በ1ሺህ ማሻሻያ ምክንያት በሠንጠረዡ ውስጥ የ+10ሚ መዛግብት ምስሎችን ማግኘት በጣም አድካሚነት ነው።

አቋራጭ dst

የበለጠ ልምድ ያለው ገንቢ መላውን ጡባዊ በጣም ርካሽ በሆነ መንገድ ማጽዳት እንደሚቻል ያውቃል።

• ግልጽ (TRUNCATE) መላውን ጠረጴዛ
• ያስገቡ ሁሉም ከአዲሱ ምስል

ዘዴው ውጤታማ ነው, አንዳንድ ጊዜ በጣም ተፈጻሚ ይሆናል, ነገር ግን ችግር አለ ... ለረጅም ጊዜ የ 1M መዛግብት እንጨምራለን, ስለዚህ ጠረጴዛውን በዚህ ጊዜ ሁሉ ባዶ መተው አንችልም (በአንድ ግብይት ሳይጠቀለል እንደሚከሰት).

ማ ለ ት:

• እየጀመርን ነው። ለረጅም ጊዜ የሚቆይ ግብይት
• TRUNCATE ያስገድዳል ልዩ መዳረሻ- ማገድ
• እኛ ማስገባትን ለረጅም ጊዜ እናደርጋለን ፣ እና በዚህ ጊዜ ሁሉም ሌሎች እንኳን አይችልም SELECT

የሆነ ነገር ጥሩ አይደለም...

ሠንጠረዥን ይቀይሩ… እንደገና ይሰይሙ… / ጠረጴዛን ጣል…

አንድ አማራጭ ሁሉንም ነገር በተለየ አዲስ ጠረጴዛ ውስጥ መሙላት ነው, እና ከዚያ በቀላሉ በአሮጌው ቦታ ላይ እንደገና ይሰይሙ. ሁለት መጥፎ ትናንሽ ነገሮች;

• አሁንም ቢሆን ልዩ መዳረሻምንም እንኳን ጊዜ በጣም ያነሰ ቢሆንም
• የዚህ ሠንጠረዥ ሁሉም የመጠይቅ እቅዶች/ስታቲስቲክስ ዳግም ተጀምረዋል፣ መተንተን ያስፈልጋል
• ሁሉም የውጭ ቁልፎች ተሰብረዋል (ኤፍ.ኬ.) ወደ ጠረጴዛው

መስራትን የሚጠቁም ከሲሞን ሪግስ የWIP patch ነበር። 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, ደረሰኙን የምናስገባበት.

ምንጭ: hab.com