Upgrade to Pro — share decks privately, control downloads, hide ads and more …

DRE/SREのプラクティス融合によるクラウドネイティブなデータ基盤作り / dre_sre

DRE/SREのプラクティス融合によるクラウドネイティブなデータ基盤作り / dre_sre

DRE / SREのプラクティスを理解し、どのように活動してデータとどう向き合っていくか
向き合うためにどのようにクラウドを利用していくのか
これから同じような取り組みにチャレンジしていく方向けにいくつかのヒントになれば幸いです

yuuki takezawa

December 28, 2023
Tweet

More Decks by yuuki takezawa

Other Decks in Technology

Transcript

  1. :VVLJ5BLF[BXBZUBLF
    %3&43&ͷϓϥΫςΟε༥߹ʹΑΔ
    Ϋϥ΢υωΠςΟϒͳσʔλج൫࡞Γ

    View full-size slide

  2. QSP
    fi
    MF

    • ελʔϑΣεςΟόϧגࣜձࣾ΄͔
    • σʔλॲཧ΍͍Ζ͍Ζ
    • (P4DBMB
    • ϚΠΫϩαʔϏεΞʔΩςΫνϟɾϦΞΫςΟϒγεςϜɺ
    ϨΨγʔվળ΍43&%3&ɺ֤छϞσϦϯάͷࢧԉͳͲ
    • 9IUUQTUXJUUFSDPNFY@UBLF[BXB

    View full-size slide

  3. "HFOEB

    • %3&ͬͯͲΜͳ͜ͱΛ͢Δͷʁ
    • Ϋϥ΢υͳΒͰ͸ͷσʔλج൫ͬͯʁ

    View full-size slide

  4. %3&ͬͯͳΜͰ͠ΐ͏ʁ

    View full-size slide

  5. %BUB3FMJBCJMJUZ&OHJOFFSJOH

    • 43&ͷϓϥΫςΟεΛσʔλ඼࣭؂ࢹͳͲʹద༻
    • Ϗδωε΍ҙࢥܾఆʹར༻͢Δ
    σʔλͷ৴པੑʹؔ͢Δ׆ಈ

    View full-size slide

  6. ͲΜͳ͜ͱΛਐΊ͍ͯ͘ͷ͔

    View full-size slide

  7. σʔλج൫ͬͯͳʹΛ͢Δͷʁ

    • ձࣾͷυϝΠϯʹΑͬͯ͸ଊ͑ํ͸ҟͳΓ·͢
    • σʔλ׆༻Λલఏʹσʔλऩू΍ɺ؅ཧɺ࡞੒ͳͲ
    σʔλʹؔ͢Δج൫
    • σʔλΛΞϓϦέʔγϣϯʹϦόʔε&5-ͨ͠Γɺ
    ػցֶशʹར༻͞ΕͨΓ

    View full-size slide

  8. σʔλ׆༻ͱ͸

    • ձࣾ׆ಈͳͲʹ͓͚Δʮҙࢥܾఆʯ΍ʮۀ຿ޮ཰Խʯɺ
    ʮϚʔέςΟϯάʯͳͲͷ޲্ʹ໾ཱͯΔ
    • σʔλυϦϒϯͳاۀจԽ࡞Γʹͳͯ͘͸ͳΒͳ͍΋ͷ
    • Ͳ͏͍͏σʔλΛΤϏσϯεʹ͍͚ͯ͠͹ྑ͍͔ɺ
    ͳͲ͸ձࣾʹΑͬͯશ͘ҧ͏ͨΊɺ
    Կ͔Λࢀߟʹ͢Δͱ׆༻͕Ͱ͖ΔΘ͚Ͱ͸ͳ͍

    View full-size slide

  9. ׆༻Ͱ͖ΔΑ͏ʹ͢ΔͨΊʹ͸

    View full-size slide

  10. ࣄۀɾձࣾΛ஌Δ

    • ࣄۀͷυϝΠϯΛ͔ͬ͠Γͱཧղ͢Δ͜ͱ
    • υϝΠϯΛཧղ͠ͳ͚Ε͹σʔλʹؔ࿈͢Δ׆ಈ͕೉͍͠
    • ୯७ʹσʔλΛू໿͢Δɺ͚ͩͰ͸׆༻͠ʹ͍͘

    View full-size slide

  11. ࣄۀɾձࣾΛ஌Δ

    • ࣄۀͷཧղ౓Λ্͛Δ͜ͱͰະདྷΛߟ͑ΒΕΔΑ͏ʹ
    • ະདྷʹߦͨ͘ΊʹݱࡏԿ͕଍Γ͍ͯͳ͍ͷ͔ɺ
    Ͳ͏͍͏ઓུΛཱͯΔඞཁ͕͋Δͷ͔
    • σʔλΛͲ͏׆༻͢ΔͱɾͲΜͳ΋ͷ͕͋Δͱ
    ໨ࢦ͍ͨ͠ํ޲ʹ޲͔͑Δͷ͔

    View full-size slide

  12. σʔλΛݟΔɺۀ຿Λ஌Δ

    • ݱঢ়ͷσʔλ͕ͲͷΑ͏ʹͳ͍ͬͯΔͷ͔
    • Ͳ͏͍ͬͨۀ຿ϑϩʔɺΞϓϦέʔγϣϯ͕͋Γ
    ͲͷλΠϛϯάͰσʔλ͕࡞ΒΕΔͷ͔
    • ͲͷλΠϛϯάͷσʔλ͕׆༻Ͱ͖Δͷ͔

    View full-size slide

  13. σʔλΛݟΔɺۀ຿Λ஌Δ

    • σʔλઃܭ͸ΞϓϦέʔγϣϯدΓͳͷ͔ɺ
    ׆༻ʹ΋଱͑͏Δઃܭʹͳ͍ͬͯΔͷ͔
    • อ؅͞ΕΔσʔλͷ඼࣭͸
    ҙࢥܾఆ΍ࣄۀ׆ಈʹରͯ͠༗༻͔Ͳ͏͔

    View full-size slide

  14. ͋Δ΋ͷΛ৴༻͠ͳ͍

    • ࠓ͋Δ΋ͷ͸ෆे෼ͳ΋ͷ͔΋͠Εͳ͍
    • ͜͏͍͏σʔλ͕ඞཁͩ
    • ׆༻͢ΔͨΊʹඞཁͳσʔλ͕ͳ͚Ε͹
    Ͳ͔͜Βͦͷσʔλ͕࡞ΒΕͦ͏͔ʢύΠϓϥΠϯʣ

    View full-size slide

  15. ͋Δ΋ͷΛ৴༻͠ͳ͍

    • ࣄۀυϝΠϯΛཧղ͍ͯ͠ͳ͚Ε͹ɺ
    σʔλ͕৴༻Ͱ͖Δ͔Ͳ͏͔ͷ൑அ͕͔ͭͳ͍
    • σʔλ͕ͲͷΑ͏ʹͳ͍ͬͯΔ΂͖͔
    • վળΛ଴ͭͷͰ͸ͳ͘ɺվળ͢ΔͨΊʹඞཁͳ΋ͷΛ
    ࣗΒ࣮ߦ͢Δ

    View full-size slide

  16. ྫ͑͹

    • $73ͳͲΛਖ਼͘͠ೝ஌Ͱ͖Δσʔλ͸ଗ͍ͬͯΔ͔ʁ
    • Χʔτམͪ͸Ͳͷ͘Β͍ʁ
    ͦͷ࣌Χʔτʹ͋Δ΋ͷ͸ʁ
    ߪೖ৘ใͱΧʔτ৘ใ͸ඥ͍ͮͯΔʁ
    • ར༻͍ͯ͠Δ4BBTαʔϏεͱσʔλ͸݁߹Ͱ͖Δʁ

    View full-size slide

  17. ݴΘΕͨ௨Γʹσʔλͷू໿ɺ
    సૹΛ͢Ε͹Α͍ɺͱࢥ͍ͬͯ·ͤΜ͔ʁ

    View full-size slide

  18. ϢʔβʔΠϯλϥΫγϣϯΛཧղ͢Δ

    • Ϣʔβʔͱ͸ࣾ಺֎໰Θͣར֐ؔ܎ऀશͯ
    • σʔλ͸Կ͔ͷࣄ৅ͷεφοϓγϣοτ
    • ͲͷΑ͏ͳ΋ͷ͕ࣄ৅ͱѻ͑Δͷ͔

    View full-size slide

  19. ΠϯλϥΫγϣϯΛཧղ͢Δʹ͸

    • ΞϓϦέʔγϣϯͷϑϩʔΛ໢ཏ͢Δ
    • υϝΠϯΠϕϯτΛݟ͚ͭग़͢
    • υϝΠϯΤΩεύʔτʹฒ૸ͯ͠΋Β͏
    • τϥοΩϯάͷઃܭͳͲʹ΋׆༻

    View full-size slide

  20. Ͳ͔͜Βੜ·ΕͯͲ͜ʹ͍͘ͷ͔

    View full-size slide

  21. Πϕϯτετʔϛϯά

    • αʔϏεͳͲʹؔΘΔϝϯόʔΛूΊ࣮ͯࢪ
    • ͲͷΑ͏ͳग़དྷࣄ͕ى͜Δ͔
    • ίϯςΩετ͸ͲͷΑ͏ʹͳ͍ͬͯΔ͔
    • γεςϜͱυϝΠϯͷؔ܎ੑΛඥղ͘

    View full-size slide

  22. Πϕϯτετʔϛϯά

    • %3&׆ಈΛߦ͏ϝϯόʔ͚ͩͰ΍Βͳ͍
    • ৴པੑΛ্͍͛ͯͨ͘Ίʹ͸จԽ࡞Γ
    ҙࣝ࡞Γ͕ඞཁෆՄܽ
    • ؔ܎ऀΛר͖ࠐΈೝࣝΛ߹ΘͤΔ͜ͱ

    View full-size slide

  23. σʔλʹؔ͢Δࢥߟ

    View full-size slide

  24. $3*41%.

    • σʔλ෼ੳͷϑϨʔϜϫʔΫ
    • σʔλʹؔ͢Δ৴པੑͱ͸ͳʹ͔Λཧղ͢Δ
    • ৴པੑΛվળɾҡ͍࣋ͯͨ͘͠Ίʹ͸ඞཁෆՄܽ

    View full-size slide

  25. Ϗδωεͷཧղ

    • ࣄۀͷํ޲ੑ΍੒௕ઓུɺϏδωεͷ໨ඪɾ໨తΛܾΊΔ
    • ݱࡏͱͷΪϟοϓ΍ঢ়گΛ೺Ѳ
    • ୡ੒ج४Λઃ͚ɺୡ੒ʹ͸ͲͷΑ͏ʹ͢΂͖͔ΛܾΊΔ

    View full-size slide

  26. σʔλͷཧղ

    • ݱࡏͲͷΑ͏ͳσʔλ͕͋Δ͔Λௐࠪ
    • 3%#.4͚ͩͰͳ͘ɺΤΫηϧͳͲͷιϑτ΢ΣΞ
    ֤छ4BB4ͳͲͷར༻ঢ়گ΋೺Ѳ
    • ۀ຿ϑϩʔͳͲ΋ཧղ

    View full-size slide

  27. σʔλͷ४උ

    • Ͳͷσʔλ͕༗༻͔ɺͲΜͳσʔλ͕ෆ଍͍ͯ͠Δ͔
    • ౷߹΍ू໿ɺσʔλΫϨϯδϯάͳͲΛߦ͏
    • ͲͷΑ͏ͳ΋ͷΛ༻͍ͯσʔλج൫Λ࡞Δ͔ʹؔΘΔ

    View full-size slide

  28. ϞσϦϯά

    • Ϗδωε໨ඪͳͲͷୡ੒ΛαϙʔτͰ͖Δ
    ϞσϦϯάͳͲΛߦ͏
    • ෼ੳ࣠΍ɺར༻ํ๏ʹԠͯ͡ෳ਺ͷ࣠ͰϞσϧ࡞੒
    • ࣌ܥྻ΍ूܭͳͷ͔ɺ͔ͬ͠Γͱཧղ͠·͠ΐ͏

    View full-size slide

  29. ධՁ

    • Ϗδωε໨ඪͳͲͷୡ੒ΛαϙʔτͰ͖Δ΋ͷ͔ධՁ
    • Ͳͷఔ౓ୡ੒Ͱ͖Δ͔ɺͲ͏͍ͬͨ੒ՌʹͳΓͦ͏͔
    • ࡞Δ͚ͩͰ͸ͳ͘Ξ΢τΧϜΛେࣄʹ

    View full-size slide

  30. ల։ɾڞ༗

    • े෼ͳධՁ͕ಘΒΕΕ͹ɺϑϩʔ΍
    γεςϜɾΞϓϦέʔγϣϯͳͲʹಋೖ
    • ܭଌՄೳͳޮՌଌఆͳͲ΋ߦ͍
    ઓུͳͲʹซͤͯϝϯςφϯεͳͲΛܧଓ͢Δ

    View full-size slide

  31. ར֐ؔ܎ऀΛཧղ͢Δ

    • σʔλ͕ؔ܎͢Δઓུ΍ϑϩʔΛཧղ͢Δ͜ͱͰ
    ৴པੑͱ͸Կ͔͕ཧղͰ͖Δ
    • ͲͷΑ͏ͳස౓ɺͲͷΑ͏ͳઓུͰσʔλʹؔ͢Δ
    ໰୊Λղܾ͍͔ͯ͘͠

    View full-size slide

  32. σʔλͷ४උ

    View full-size slide

  33. σʔλͷ४උ͸೉͍͠

    • ෳ਺ͷεςʔΫϗϧμ
    • αΠϩԽɾ͋ͪͪ͜ʹ͋Δσʔλ
    • ࿈ଓੑͷͳ͍σʔλ

    View full-size slide

  34. αΠϩԽ

    • ϓϩμΫτͳͲͷνʔϜͰ
    ͢Ͱʹσʔλநग़΍֦ு͕ߦΘΕ͍ͯΔ
    • ձࣾΛԣஅͨ͠ঢ়گ൑அ͕͍ͨ͠ɺͳͲ͕೉͍͠
    • ηΩϡϦςΟ΍Ψόφϯε΋ޮ͔ͤΒΕͳ͍

    View full-size slide

  35. ࿈ଓੑ͕ͳ͍

    • ϓϩμΫτಠࣗͷ*%؅ཧɺෳࡶԽͨ͠*%Ϛοϐϯά
    • ҰͭͷγεςϜͰ͸Ұ؏ੑ͕͋ͬͯ΋
    ԣஅతͳσʔλͱͯ͠ΈΔͱࠞࡏ
    • σʔλʹ͓͚ΔϥΠϑαΠΫϧͷҧ͍

    View full-size slide

  36. ղܾ͢Δʹ͸

    • ϑΟδΧϧ44P5PSϩδΧϧ44P5
    • αΠϩԽ͕ਐΜͰ͠·͍ͬͯΔ৔߹͸
    ϑΟδΧϧ44P5͔ΒͰͳ͚Ε͹೉͍͠
    • σʔλͷ৴པੑʹ௚݁͢Δ໰୊

    View full-size slide

  37. Ϋϥ΢υωΠςΟϒͳσʔλج൫

    View full-size slide

  38. "UIFOBϑΟδΧϧ44P5

    • &-5Ͱ4ʹͱʹ͔͘సૹ
    • υϝΠϯΠϕϯτͳͲΛཧղͨ͠͏͑Ͱ
    ΄ͱΜͲ͕݁Ռ੔߹Ͱ໰୊ͳ͍
    • νʔϜͷن໛͕খ͍͞ͱෆࣗ༝ͳ͠
    • ύʔςΟγϣϯઃܭ΋ࢥ͍ͷ··

    View full-size slide

  39. ϑΟδΧϧ4405

    σʔλιʔε

    View full-size slide

  40. ϑΟδΧϧ4405

    ίϨΫςΟϯά
    ϓϩηογϯά

    View full-size slide

  41. ϑΟδΧϧ4405

    ετϨʔδ

    View full-size slide

  42. ϑΟδΧϧ4405

    ΞΫηε

    View full-size slide

  43. ໰୊

    • ؔΘΔਓ͕૿͑Δͱίϯτϩʔϧ͕೉͍͠
    • σʔλج൫ʹணख͍ͯ͘͠ͱ
    ࢥͬͯ΋͍ͳ͔ͬͨࣄ৅ʹग़ձ͏͜ͱ΋
    • ͢ͰʹαΠϩԽ͕Ճ଎͍ͯ͠Δ৔߹
    ϑΟδΧϧ4405͚ͩͰ͸ݶք

    View full-size slide

  44. ໰୊

    • ݖݶ͕೉͍͠
    • ͪΐͬͱͨ͠ॲཧ͸4QBSLͳͲ͕ඞཁͳͨΊ
    ηϧϑαʔϏεԽͯ͠΋ɺਁಁ͕೉͍͠
    ʢ׳Εͳ͍ٕज़ελοΫ΁ͷڋ൱ʣ

    View full-size slide

  45. 4OPX
    fl
    BLF΁Ҡߦ

    • ϑΟδΧϧPSϩδΧϧͱ΋ʹղܾ͕Ͱ͖Δ
    • ඪ४తͳ42-͚ͩͰଟ͕͘ղܾͰ͖Δ
    • ֎෦ωοτϫʔΫΞΫηεՄೳ
    • "UIFOBͳͲͰར༻͍ͯͨ͠1BSRVFU͕ͦͷ··ར༻Ͱ͖Δ

    View full-size slide

  46. ϞσϦϯάɾධՁ

    View full-size slide

  47. σʔλͷϥΠϑαΠΫϧΛೝࣝ͢Δ

    • ྫ͑͹υϝΠϯΠϕϯτൃੜ͔Β͍ͭ·Ͱʹ
    σʔλ͕࿈ܞ͞ΕΔ΂͖͔
    • 4-*ϝχϡʔGSFTIOFTTͳͲΛࢀߟʹઓུɺ
    ར༻༻్ɺσʔλϞσϧʹซͤͯσʔλͷ઱౓Λܾఆ

    View full-size slide

  48. χΞϦΞϧλΠϜ

    3%4ͳͲͷϨίʔυมߋΛݕ஌ͯ͠
    %FCF[JVN͕ߦ৘ใΛ,BGLB΁సૹ
    ඞཁʹԠͯ͡0VUCPYύλʔϯͳͲΛಋೖ

    View full-size slide

  49. χΞϦΞϧλΠϜ

    4OPX
    fl
    BLF$POOFDUPSGPS,BGLB
    Λར༻͠ɺ
    ,BGLBͷτϐοΫʹ௥ه͞ΕͨλΠϛϯάͰ
    4OPX
    fl
    BLF΁࿈ܞ

    View full-size slide

  50. Ϩίʔυྫ

    create or replace view HOGE_DB.RAW_DATA.TEST_IU_VIEW (
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    SOURCE_PROCESSED_TS,
    SOURCE_SERVER,
    SOURCE_DB,
    SOURCE_TABLE,
    DML_OPERATOR
    ) as
    SELECT
    record_content:"after"."id"::NUMBER as ID,
    record_content:"after"."name"::NUMBER as NAME,
    record_content:"ts_ms"::STRING::DATETIME as DEBEZIUM_PROCESSED_TS,
    record_content:"source"."ts_usec"::STRING::DATETIME as SOURCE_PROCESSED_TS,
    record_content:"source"."name"::STRING as SOURCE_SERVER,
    record_content:"source"."db"::STRING as SOURCE_DB,
    record_content:"source"."table"::STRING as SOURCE_TABLE,
    record_content:"op"::STRING as DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.CDC_RAW_TESTS
    WHERE lower(DML_OPERATOR) in ('r', 'c', 'u');

    View full-size slide

  51. Ϩίʔυྫ

    create or replace view HOGE_DB.RAW_DATA.TEST_IU_VIEW (
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    SOURCE_PROCESSED_TS,
    SOURCE_SERVER,
    SOURCE_DB,
    SOURCE_TABLE,
    DML_OPERATOR
    ) as
    SELECT
    record_content:"after"."id"::NUMBER as ID,
    record_content:"after"."name"::NUMBER as NAME,
    record_content:"ts_ms"::STRING::DATETIME as DEBEZIUM_PROCESSED_TS,
    record_content:"source"."ts_usec"::STRING::DATETIME as SOURCE_PROCESSED_TS,
    record_content:"source"."name"::STRING as SOURCE_SERVER,
    record_content:"source"."db"::STRING as SOURCE_DB,
    record_content:"source"."table"::STRING as SOURCE_TABLE,
    record_content:"op"::STRING as DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.CDC_RAW_TESTS
    WHERE lower(DML_OPERATOR) in ('r', 'c', 'u');
    ෺ཧ࡟আͷ৔߹͸CFGPSFΛࢀর

    View full-size slide

  52. Ϩίʔυྫ

    create or replace view HOGE_DB.RAW_DATA.TEST_IU_VIEW (
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    SOURCE_PROCESSED_TS,
    SOURCE_SERVER,
    SOURCE_DB,
    SOURCE_TABLE,
    DML_OPERATOR
    ) as
    SELECT
    record_content:"after"."id"::NUMBER as ID,
    record_content:"after"."name"::NUMBER as NAME,
    record_content:"ts_ms"::STRING::DATETIME as DEBEZIUM_PROCESSED_TS,
    record_content:"source"."ts_usec"::STRING::DATETIME as SOURCE_PROCESSED_TS,
    record_content:"source"."name"::STRING as SOURCE_SERVER,
    record_content:"source"."db"::STRING as SOURCE_DB,
    record_content:"source"."table"::STRING as SOURCE_TABLE,
    record_content:"op"::STRING as DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.CDC_RAW_TESTS
    WHERE lower(DML_OPERATOR) in ('r', 'c', 'u');
    ෺ཧ࡟আͷ৔߹͸E

    View full-size slide


  53. MERGE INTO HOGE_DB.STAGING_DATA.TESTS as tgt
    USING (
    SELECT * FROM (
    SELECT *,
    ROW_NUMBER() over (
    PARTITION BY ID
    ORDER BY DEBEZIUM_PROCESSED_TS DESC
    ) as row_num
    FROM (
    SELECT
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.TEST_IU_VIEW
    UNION ALL
    SELECT
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.TEST_D_VIEW
    ) as u
    ) as t1 WHERE t1.row_num = 1
    ) as src
    ON tgt.ID = src.ID
    WHEN MATCHED AND src.DML_OPERATOR = 'd' THEN DELETE
    WHEN MATCHED AND src.DML_OPERATOR = 'u' THEN
    UPDATE SET
    tgt.ID = src.ID,
    tgt.NAME = src.NAME
    WHEN NOT MATCHED AND src.DML_OPERATOR IN ('c', 'r', 'u') THEN
    INSERT (
    ID,
    NAME
    )
    VALUES (
    src.ID,
    src.NAME
    );

    View full-size slide


  54. MERGE INTO HOGE_DB.STAGING_DATA.TESTS as tgt
    USING (
    SELECT * FROM (
    SELECT *,
    ROW_NUMBER() over (
    PARTITION BY ID
    ORDER BY DEBEZIUM_PROCESSED_TS DESC
    ) as row_num
    FROM (
    SELECT
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.TEST_IU_VIEW
    UNION ALL
    SELECT
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.TEST_D_VIEW
    ) as u
    ) as t1 WHERE t1.row_num = 1
    ) as src
    ON tgt.ID = src.ID
    WHEN MATCHED AND src.DML_OPERATOR = 'd' THEN DELETE
    WHEN MATCHED AND src.DML_OPERATOR = 'u' THEN
    UPDATE SET
    tgt.ID = src.ID,
    tgt.NAME = src.NAME
    WHEN NOT MATCHED AND src.DML_OPERATOR IN ('c', 'r', 'u') THEN
    INSERT (
    ID,
    NAME
    )
    VALUES (
    src.ID,
    src.NAME
    );
    3%4ͳͲ͔ΒϨίʔυ͕࡟আ͞Εͨ৔߹͸
    4OPX
    fl
    BLF͔Β΋࡟আ

    View full-size slide


  55. MERGE INTO HOGE_DB.STAGING_DATA.TESTS as tgt
    USING (
    SELECT * FROM (
    SELECT *,
    ROW_NUMBER() over (
    PARTITION BY ID
    ORDER BY DEBEZIUM_PROCESSED_TS DESC
    ) as row_num
    FROM (
    SELECT
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.TEST_IU_VIEW
    UNION ALL
    SELECT
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.TEST_D_VIEW
    ) as u
    ) as t1 WHERE t1.row_num = 1
    ) as src
    ON tgt.ID = src.ID
    WHEN MATCHED AND src.DML_OPERATOR = 'd' THEN DELETE
    WHEN MATCHED AND src.DML_OPERATOR = 'u' THEN
    UPDATE SET
    tgt.ID = src.ID,
    tgt.NAME = src.NAME
    WHEN NOT MATCHED AND src.DML_OPERATOR IN ('c', 'r', 'u') THEN
    INSERT (
    ID,
    NAME
    )
    VALUES (
    src.ID,
    src.NAME
    );
    Ϩίʔυ͕ߋ৽͞Εͨ৔߹͸
    4OPX
    fl
    BLFͷϨίʔυ΋ߋ৽

    View full-size slide


  56. MERGE INTO HOGE_DB.STAGING_DATA.TESTS as tgt
    USING (
    SELECT * FROM (
    SELECT *,
    ROW_NUMBER() over (
    PARTITION BY ID
    ORDER BY DEBEZIUM_PROCESSED_TS DESC
    ) as row_num
    FROM (
    SELECT
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.TEST_IU_VIEW
    UNION ALL
    SELECT
    ID,
    NAME,
    DEBEZIUM_PROCESSED_TS,
    DML_OPERATOR
    FROM HOGE_DB.RAW_DATA.TEST_D_VIEW
    ) as u
    ) as t1 WHERE t1.row_num = 1
    ) as src
    ON tgt.ID = src.ID
    WHEN MATCHED AND src.DML_OPERATOR = 'd' THEN DELETE
    WHEN MATCHED AND src.DML_OPERATOR = 'u' THEN
    UPDATE SET
    tgt.ID = src.ID,
    tgt.NAME = src.NAME
    WHEN NOT MATCHED AND src.DML_OPERATOR IN ('c', 'r', 'u') THEN
    INSERT (
    ID,
    NAME
    )
    VALUES (
    src.ID,
    src.NAME
    );
    4OPX
    fl
    BLFʹσʔλ͕ͳ͍͕ɺ
    ࡟আҎ֎ͷϨίʔυૢ࡞͕͋ͬͨ৔߹͸
    *OTFSU

    View full-size slide

  57. ઱౓͕ྑ͍৔Ͱ΋γϯϓϧʹ

    • ଟগͷ࿈ܞઃఆ͸͋Γ·͕͢ɺ42-͚ͩͰ׬݁
    • ࿈ܞ͕ࣦഊͯ͠΋σʔλ͸յΕͳ͍
    • ࿈ܞ͠௚͍ͨ͠৔߹͸%FCF[JVNͰ಄͔Β࠶࿈ܞ
    • ϦεΫ΋ཧղ͠ɺӨڹൣғΛ࠷খݶʹ

    View full-size slide

  58. ߟྀ

    • Ϩίʔυ਺͕ଟ͍৔߹΍ɺ
    ༨ܭͳ௨৴͕ൃੜ͠ͳ͍Α͏ʹ͍ͨ͠৔߹ͳͲ͸4ͳͲ΋ซ༻
    • ϦΞϧλΠϜੑ͕ຊ౰ʹඞཁ͔Ͳ͏͔
    ٻΊΒΕΔ઱౓ɾ৴པੑΛ໌Β͔ʹ͢Δ
    • ECUͳͲ΋ར༻

    View full-size slide

  59. ֎෦ωοτϫʔΫܦ༝ͷ࿈ܞ

    • "1*͕ެ։͞Ε͍ͯΔαʔϏεͳͲ͸ͦͷ··࿈ܞՄೳ
    • 6%'΍ετΞυϓϩγʔδϟͱͯ͠૊ΈࠐΊΔ΋ͷ
    • σʔλج൫ͳͲͷετϨʔδʹͳͯ͘΋
    ࿦ཧతʹ౷߹Մೳ

    View full-size slide

  60. τΠϧͷ࡟ݮ

    View full-size slide

  61. τΠϧͷ࡟ݮ

    • ηϧϑαʔϏεͷͨΊͷࣗಈԽ
    &-54OPX
    fl
    BLFͳͲ
    • ϓϩμΫτ͝ͱͷՄࢹԽπʔϧಋೖͳͲͷͨΊͷ
    ݖݶઃܭ
    • Ξϥʔτͷ௨஌

    View full-size slide

  62. τΠϧͷ࡟ݮ

    • 4DIFNB&WPMVUJPO
    • δϣϒεέδϡʔϧ؅ཧ
    • ϦτϥΠ

    View full-size slide

  63. ͳΜͰ΋ࣗಈԽ͢Ε͹ྑ͍Θ͚Ͱ͸ͳ͍

    • ࣗಈԽ͗ͨ͢͜͠ͱʹΑΔࣄނ
    • νʔϜͷೝ஌౓ෛՙ
    • νʔϜͷ݈߁ঢ়ଶɾϞνϕʔγϣϯ

    View full-size slide

  64. ඼࣭ςετ

    View full-size slide

  65. ਖ਼֬ੑ

    • ଞ؀ڥͰ࡞ΒΕͨσʔλͱࠩҟ͕ͳ͍͔Ͳ͏͔
    • ֹۚܭࢉͳͲͷζϨ͸ਖ਼֬ͳҙࢥܾఆʹͭͳ͕Βͳ͍
    • ܭࢉࣜ΍Ϩίʔυ਺ɺΧϥϜͳͲ΋ظ଴஋Λຬ͔ͨ͢
    • Ͳͷ͘Β͍ڐ༰͞ΕΔ΋ͷ͔ೝࣝΛ͋ΘͤΔ

    View full-size slide

  66. ਖ਼֬ੑ

    • 99͔Β࿈ܞ͞Εͨσʔλ͕೔ؒͰ
    औΓѻֹ͍ۚͱҰக͍ͯ͠Δ͜ͱͳͲ
    • ظؒ಺ͰɺఆΊͨ݅਺Ҏ্ະ࿈ܞ΍ͣΕ͕͋Δ৔߹͸
    ରԠΛ͠ͳ͚Ε͹ͳΒͳ͍
    • ೝ͕ࣝ߹্ͬͨͰܰඍͳ΋ͷͰ͋Ε͹࠶࿈ܞ࣮ߦͳͲ

    View full-size slide

  67. Ұ؏ੑ

    • Ұ؏ੑͷ͋ΔϑΥʔϚοτʹԊ͍ͬͯΔ͔Ͳ͏͔
    • ͓͔͠ͳσʔλϑΥʔϚοτ͕ࠞࡏ͍ͯ͠ͳ͍͔Ͳ͏͔
    ੔߹ੑʹ໰୊͕ͳ͍͔
    • ಉ༷ʹ4-0ͳͲΛ໌ه͠ɺ
    σʔλͷमਖ਼PSϑϩʔͳͲຊ࣭తͳվળΛߦ͏

    View full-size slide

  68. ͜͜ʹ͋Δ΋ͷ͸΄ΜͷҰ෦

    View full-size slide

  69. ·ͱΊ

    • ࣄۀཧղͱυϝΠϯΛ͔ͬ͠ΓͱטΈࡅ͘
    • ࣄۀͷະདྷΛ૝૾͠ɺͦ͜ʹඞཁͳσʔλΛཧղ͢Δ
    • 43&ͷϓϥΫςΟεΛऔΓೖΕɺଟ͘Λר͖ࠐΜͰਪਐ
    • Ϋϥ΢υΛ׆༻ͯ͠ར༻͠΍͍͢σʔλج൫࡞Γ

    View full-size slide