データ基盤の3分類と進化的データモデリング #DPCT / 20190213

Slide 1

Slide 1 text

σʔλج൫ͷ3෼ྨͱ  ਐԽతσʔλϞσϦϯά 2019-02-13  Data Pipeline Casual Talk #DPCT @ΤϜεϦʔ  presented by @yuzutas0   https://www.pexels.com/photo/grayscale-photo-of-metal-building-185039/

Slide 2

Slide 2 text

͜ͷεϥΠυ͸WEBʹެ։ࡁΈͰ͢ #DPCT  ɹࡱӨ΍ϝϞ͸ෆཁͰ͢ɻϦϥοΫεͯ͠ฉ͍͍͚ͯͨͩΕ͹ͱࢥ͍·͢ɻ   ͜ͷεϥΠυ͸KKDʢצͱܦݧͱ౓ڳʣͰग़དྷ͍ͯ·͢  ɹ͝ΊΜͳ͍͞ɻ    ઃܭͷώϯτΛఏڙ͢ΔͨΊͷࢿྉͰ͢  ɹʮࣗ෼͕Ͳ͏ΞΫγϣϯΛม͑Δ͔ʯΛߟ͑ͳ͕Βฉ͍͍͚ͯͨͩΔͱخ͍͠Ͱ͢ɻ  ɹಉ͡༻ޠͰ΋ݱ৔΍ϩʔϧ ( ΤϯδχΞ / ΞφϦετ ) Ͱղऍ͕ҧ͏͜ͱ΋͋Γ·͢ɻ  ɹ͝ཧղ͍͚ͨͩΔͱ޾͍Ͱ͢ɻͳͷͰʮTableau͸σʔλϚʔτͳͷ͔ʯ౳ͷٞ࿦ʹ͸෇͖߹͍·ͤΜɻ ɹ஫ҙɾ໔੹

Slide 3

Slide 3 text

1. ͸͡Ίʹ 2. σʔλج൫ͷ3෼ྨ 3. ࢲ͕ߟ͑Δσʔλج൫ͷఆٛ 4. ࢲ͕ߟ͑Δʮ͋Δ΂͖ߏ੒ʯ 5. ͓ΘΓʹ ɹΞδΣϯμ

Slide 4

Slide 4 text

ɹ@yuzutas0 ɹɹ    

Slide 5

Slide 5 text

ɹաڈͷࢿྉ σʔλج൫ͷϊ΢ϋ΢ɾ஌ݟΛఏڙ͍ͯ͠·͢   

Slide 6

Slide 6 text

ɹɹ    

Slide 7

Slide 7 text

ʮ࢖ΘΕΔσʔλج൫ʯ ͷ ࿦ཧઃܭ ʹ  ඞཁͳߟ͑ํΛֶͿ ※ٕज़બఆ΍։ൃମ੍ʹ΋࿈ಈ͠·͢ ɹຊ೔ͷΰʔϧ

Slide 8

Slide 8 text

ɾྫɿʮച্ʯͷσʔλϞσϧΛͲ͜ͰͲ͏؅ཧ͢Δ͔ʁ  ɹɹɾDBʹ͸ʮߪೖऀidʯʮൢചऀidʯʮ঎඼idʯʮചങֹۚʯetc…ʢܭࢉલͷߪങ৘ใ ≠ച্?ʣ  ɹɹɾ೔࣍μογϡϘʔυʹ͸ʮx࣠:೔෇ʯʮy࣠:SUM(ֹۚ)ʯʢଞͷΧϥϜ͸දࣔ͠ͳ͍ ≠ച্?ʣ  ɾࠓճ͸࿦ཧઃܭΛ੾Γޱʹͯ͠ɺ෺ཧઃܭͷ͋Δ΂͖૾Λߟ͑Δ ɹ࿦ཧઃܭ is Կʁ (͜ͷࢿྉʹ͓͚Δݺͼํ) ෺ཧઃܭ γεςϜߏ੒ ʢ)BEPPQͱ͔ʣ ࿦ཧઃܭ σʔλϞσϧ ʢച্ςʔϒϧͱ͔ʣ

Slide 9

Slide 9 text

σʔλج൫ ͷ ϓϩμΫτϚωʔδϟʔ  ͳΒͼʹ ςοΫϦʔυ    ※΋͠1ਓΤϯδχΞମ੍ͳΒɺࣄ্࣮ͷPdM & TL ݉຿ͳͷͰ  ͋ͳ͕ͨλʔήοτͱͳΓ·͢ʂ ɹ૝ఆ͢Δର৅

Slide 10

Slide 10 text

݁࿦ʢ࠶ܝ͠·͢ʣ .BSU ར༻ऀ޲͚*' 㲈σʔλϚʔτ 8BSFIPVTF υϝΠϯ஌ࣝΛද͢σʔλ 㲈σʔλ΢ΣΞϋ΢ε 4PVSDF ݩσʔλͷίϐʔ 㲈σʔλϨΠΫ σʔλύΠϓϥΠϯʹ͓͍ͯػց͕ॲཧ͢ΔྲྀΕ ࣄۀ΍γεςϜͷ  શσʔλΛίϐʔ͢Δ μογϡϘʔυ΍  ϨϙʔτͱରʹͳΔ ։ൃϓϩηεʹ͓͍ͯਓ͕ؒઃܭ͢ΔྲྀΕ ूܭɾ෼ੳύλʔϯΛ੔ཧ  㲈8*5)۟ͷසग़σʔλ ᶃ ᶄ ᶅ ᶃ ᶃ ᶄ

Slide 11

Slide 11 text

1. ͸͡Ίʹ 2. σʔλج൫ͷ3෼ྨ 3. ࢲ͕ߟ͑Δσʔλج൫ͷఆٛ 4. ࢲ͕ߟ͑Δʮ͋Δ΂͖ߏ੒ʯ 5. ͓ΘΓʹ ɹΞδΣϯμ

Slide 12

Slide 12 text

ɹσʔλج൫ͷ3෼ྨ σʔλج൫ͷ෼ྨ Ґஔ෇͚ ʢҰൠతͳʣٕज़ཁૉ σʔλϨΠΫ ݩσʔλΛͦͷ··  ίϐʔͨ͠΋ͷ 4ͳͲ σʔλ  ΢ΣΞϋ΢ε υϝΠϯ஌ࣝΛ൓өͨ͠΋ͷ )BEPPQͳͲ σʔλϚʔτ ಛఆ༻్޲͚ʹ  Ճ޻ɾ੔ཧͨ͠΋ͷ 5BCMFBVͳͲ

Slide 13

Slide 13 text

ιʔε  ਫݯ ϨΠΫ  ބ஝ੵ͢Δ ΢ΣΞϋ΢ε  ૔ݿ؅ཧ͢Δ Ϛʔτ  ࢢ৔ചΔ Ϣʔβʔ  ར༻ऀ ɹύΠϓϥΠϯʹ͓͚Δσʔλϑϩʔ

Slide 14

Slide 14 text

ɹɹσʔλϨΠΫʢData Lakeʣᶃ ɾݩͷσʔλΛՃ޻ͤͣʹɺͦͷ··1ͭͷγεςϜʹू໿ͨ͠΋ͷ    ɾιʔεʢਫݯʣ͔ΒྲྀΕ͖ͯͨσʔλΛ஝͑Δ৔ॴͳͷͰϨΠΫʢބʣ

Slide 15

Slide 15 text

ɹɹσʔλϨΠΫʢData Lakeʣᶄ ɾͨͩͷίϐʔͰ͋Δ͜ͱ͕ॏཁ    ɾ΋͠Ճ޻ࡁΈσʔλ͔͠࢒͍ͬͯͳ͚Ε͹ɺޙͰʮ͜ͷՃ޻ํ๏͸ؒҧ͍ͩʯ  ɹʮผͷ੾ΓޱͰ࠶ूܭ͍ͨ͠ʯͱ͍͏έʔεʹରԠͰ͖ͳ͍    ɾ σʔλϨΠΫʹݩͷঢ়ଶ͕࢒͍ͬͯΕ͹ɺௐࠪ΍ϩδοΫमਖ਼͕༰қʹͳΔ    ɾαΠΤϯςΟετ΍MLΤϯδχΞͳΒɺத్൒୺ʹ༻ҙ͞ΕͨՃ޻ࡁσʔλ͕  ɹ࢖͍෺ʹͳΒͣɺݩσʔλΛدӽͤͱݴ͍ͨ͘ͳͬͨ͜ͱ͸ଟʑ͋Δ͸ͣ

Slide 16

Slide 16 text

ɹɹσʔλϨΠΫʢData Lakeʣᶅ ɾʢҰൠతͳٕज़ཁૉͱͯ͠͸ʣS3΍GCSͳͲͷετϨʔδαʔϏεΛ  ɹ࢖͏͜ͱ͕ଟ͍    ɾϩάϑΝΠϧ΍DB DumpΛίϐʔͯ͠ετϨʔδʹ഑උ͍ͯ͠ΔͳΒɺ  ɹͦΕ͕σʔλϨΠΫͩͱݴ͑Δ

Slide 17

Slide 17 text

ιʔε  ਫݯ ϨΠΫ  ބ஝ੵ͢Δ ΢ΣΞϋ΢ε  ૔ݿ؅ཧ͢Δ Ϛʔτ  ࢢ৔ചΔ Ϣʔβʔ  ར༻ऀ ɹύΠϓϥΠϯʹ͓͚Δσʔλϑϩʔʢ࠶ܝʣ

Slide 18

Slide 18 text

ɹɹσʔλ΢ΣΞϋ΢εʢData Warehouseʣᶃ ɾෳ਺ͷσʔλΛ౷߹ˍ஝ੵͯ͠ɺҙࢥܾఆʹ࢖͑ΔΑ͏੔ཧͨ͠΋ͷ ɾେྔͷσʔλΛҙຯͷ͋ΔܗͰ؅ཧ͢ΔͷͰ΢ΣΞϋ΢εʢ૔ݿʣ

Slide 19

Slide 19 text

ɹɹσʔλ΢ΣΞϋ΢εʢData Warehouseʣᶄ ɾۀ຿υϝΠϯ஌ࣝΛσʔλϞσϦϯάʹ൓ө͢Δ͜ͱ͕ॏཁ    ɾݩσʔλͷίϐʔͰ͸࢖͍উख͕ѱ͘ɺ͔ͱ͍ͬͯಛఆ༻్ʹݶఆ͢Δͱ  ɹԠ༻Ͱ͖ͳ͍ɺͱ͍͏ઈົ͕ͩॏཁͳχʔζΛຬͨ͢    ɾσʔλΤϯδχΞ͕ʮ͜͏͢Ε͹͍͍ͩΖ͏ʯͱ҆қʹઃܭ͢Δͱഁ୼͢Δ    ɾ͔ͱ͍ͬͯ࠷ॳ͔ΒࣄۀυϝΠϯ஌ࣝΛશͯ೺ѲͰ͖ΔΘ͚Ͱ͸ͳ͍ͷͰɺ  ɹܧଓతʹ࠶ઃܭ͠ଓ͚Δ͜ͱ͕ٻΊΒΕΔ

Slide 20

Slide 20 text

ɹɹσʔλ΢ΣΞϋ΢εʢData Warehouseʣᶅ ɾʢҰൠతͳٕज़ཁૉͱͯ͠͸ʣHadoopʢhdfsʣ΍RedshiftɺBigQuery  ɹͳͲͷ෼ࢄσʔλετΞΛ࢖͏͜ͱ͕ଟ͍    ɾຊࢿྉʹ͓͚ΔʮσʔλϨΠΫʯ΍ʮσʔλϚʔτʯΛ·ΔͬͱؚΊͯ  ʮσʔλ΢ΣΞϋ΢εʯͱݺͿਓ΋͍ΔͷͰɺͦͷ఺͸͝஫ҙ͍ͩ͘͞

Slide 21

Slide 21 text

ιʔε  ਫݯ ϨΠΫ  ބ஝ੵ͢Δ ΢ΣΞϋ΢ε  ૔ݿ؅ཧ͢Δ Ϛʔτ  ࢢ৔ചΔ Ϣʔβʔ  ར༻ऀ ɹύΠϓϥΠϯʹ͓͚Δσʔλϑϩʔʢ࠶ܝʣ

Slide 22

Slide 22 text

ɹɹσʔλϚʔτʢData Martʣᶃ ɾಛఆͷར༻ऀˍ༻్޲͚ʹσʔλΛՃ޻ˍ੔ཧͨ͠΋ͷ    ɾ͙͢ʹ࢖͑Δ׬੒඼ΛऔΓἧ͍͑ͯΔͷͰϚʔτʢࢢ৔ʣ

Slide 23

Slide 23 text

ɹɹσʔλϚʔτʢData Martʣᶄ ɾϢʔεέʔε͝ͱʹ࠷దԽ͞Ε͍ͯΔ͜ͱ͕ॏཁ   ɾσʔλϚʔτ͕੔උ͞Ε͍ͯΕ͹ɺύϑΥʔϚϯεΛؾʹͤͣʹେྔͷ  ɹσʔλΛॲཧͨ͠ΓɺෳࡶͳूܭΛͤͣʹର৅σʔλΛݟΔ͜ͱ͕Ͱ͖Δ    ɾϚʔέλʔ΍ܦӦاըͳΒ͹ɺݟ͍ͨ਺ࣈ͸γϯϓϧͳ͸ͣͳͷʹɺ  ɹ΍ͨΒͱूܭ͕େมͰർฐͨ͜͠ͱ͸ଟʑ͋Δ͸ͣ

Slide 24

Slide 24 text

ɹɹσʔλϚʔτʢData Martʣᶅ ɾʢҰൠతͳٕज़ཁૉͱͯ͠͸ʣRe:dash΍TableauͳͲͷBIπʔϧΛ  ɹ࢖͏͜ͱ͕ଟ͍    ɾམͱͨ͠σʔλΛ࢖ͬͯExcelͰෳࡶͳूܭΛߦ͍ͬͯΔͷͰ͋Ε͹ɺ  ɹͦͷExcelγʔτ͕ͦ͜ਅͷσʔλϚʔτͩͱݴ͑Δ ɹɹ ※ΤϯδχΞ͕Ͳ͏γεςϜΛ࡞Ζ͏͕ɺσʔλϞσϧͷ࣮ଶ͸Excelଆʹଘࡏ͢Δ͜ͱʹͳΔ  ɹɹ ※஫໨͢΂͖͸෺ཧઃܭͰ͸ͳ͘࿦ཧઃܭ / BIπʔϧ͕ϩδοΫΛ࣋ͭͳΒͦ͜ʹ஫໨͢Δ  ɹɹ ※ར༻ऀͷπʔϧʹ࠷దԽ͞Ε͕ͪʢʹίϯ΢ΣΠͷ๏ଇʣ

Slide 25

Slide 25 text

ιʔε  ਫݯ ϨΠΫ  ބ஝ੵ͢Δ ΢ΣΞϋ΢ε  ૔ݿ؅ཧ͢Δ Ϛʔτ  ࢢ৔ചΔ Ϣʔβʔ  ར༻ऀ ɹύΠϓϥΠϯʹ͓͚Δσʔλϑϩʔʢ࠶ܝʣ

Slide 26

Slide 26 text

1. ͸͡Ίʹ 2. σʔλج൫ͷ3෼ྨ 3. ࢲ͕ߟ͑Δσʔλج൫ͷఆٛ 4. ࢲ͕ߟ͑Δʮ͋Δ΂͖ߏ੒ʯ 5. ͓ΘΓʹ ɹΞδΣϯμ

Slide 27

Slide 27 text

͜ͷ3෼ྨ͸σʔλ׆༻ͷۀ຿ϑϩʔͷͲ͔͜ʹଘࡏ͢Δ ɹσʔλج൫ͷ3෼ྨʢ࠶ܝʣ σʔλج൫ͷ෼ྨ Ґஔ෇͚ ʢҰൠతͳʣٕज़ཁૉ σʔλϨΠΫ ݩσʔλΛͦͷ··  ίϐʔͨ͠΋ͷ 4ͳͲ σʔλ  ΢ΣΞϋ΢ε υϝΠϯ஌ࣝΛ  ൓өͨ͠΋ͷ )BEPPQͳͲ σʔλϚʔτ ಛఆ༻్޲͚ʹ  Ճ޻ɾ੔ཧͨ͠΋ͷ 5BCMFBVͳͲ

Slide 28

Slide 28 text

ɹQ. ͏ͪͷ૊৫ʹ͸͋Γ·ͤΜ͕ʁ A. Ғ͍ਓ΍ιϑτ΢ΣΞ։ൃऀ͕ؾ෇͍͍ͯͳ͍͚ͩʂ ۀ຿୲౰ऀͷϩʔΧϧPCʹ͋ΔExcelγʔτ͕  ࣮͸σʔλج൫ͷ໾ׂΛՌ͍ͨͯ͠Δ͔΋͠Εͳ͍

Slide 29

Slide 29 text

ɹɹۀ຿୲౰ऀͷExcelγʔτ ଟ෼͜͏ͳ͍ͬͯΔ ※࣮෺͸΋ͬͱԚ͍͔΋͠Εͳ͍͕ ɹɹɹɹrawσʔλΛίϐʔͨ͠γʔτʢσʔλϨΠΫʣ  ɹɹɹɹूܭͷͨΊͷγʔτʢσʔλ΢ΣΞϋ΢εʣ  ɹɹɹɹάϥϑදࢴͷͨΊͷγʔτʢσʔλϚʔτʣ  ɹɹɹɹɹɹɹɹɹɹɹάϥϑදࣔ༻ͷूܭ݁Ռσʔλ͕άϥϑͱಉ͡γʔτʹ͋ΔΠϝʔδ    ݱ৔Ͱػೳ͍ͯ͠ΔਅͷσʔλύΠϓϥΠϯ  ʢͨͩ͠ΤϯδχΞ͸ݟ͕ͨΒͳ͍ʣ

Slide 30

Slide 30 text

ɹͦͷσʔλ͸ར༻ऀʹಧ͍͍ͯ·͔͢ʁ ɾ3෼ྨͷશͯΛͻͬ͘ΔΊͯσʔλج൫ͱݺশ͍ͨ͠    ɾͲΕ͔1ͭͷ໾ׂΛ୲͏͚ͩͰ͸ෆे෼

Slide 31

Slide 31 text

ɹΞϯνύλʔϯᶃɿΤϯδχΞνʔϜ AirﬂowΛಋೖ͢Δ࣌ʹۭճΓͨ͜͠ͱ͕͋ΔͷͰ  1ਓͰউखʹAirʢۭʣﬂowʢճΓʣύλʔϯͱݺশ͍ͯ͠Δ Ϟμϯͳٕज़Ͱ  ”͍͢͝ج൫”Λߏங  ΤϯδχΞʢDataʣ ࢖͍ํ͕෼͔Βͳ͍  ॏ͍ExcelͰటष͘ӡ༻  ར༻ऀʢOpsʣ

Slide 32

Slide 32 text

ɹΞϯνύλʔϯᶄɿۀ຿վળνʔϜ ྑ͔Εͱࢥͬͯઃܭͯ͠΋ɺఏڙ෦ॺʹͱͬͯͷσʔλϚʔτͰ͔͠ͳ͍  ɹ૊৫ԣஅͷσʔλج൫ͱ͸ݴ͑ͳ͍ ࠷ߴʹ࢖͍΍͍͢  Ճ޻ࡁσʔλΛఏڙ  ۀ຿վળTʢDataʣ ͓લʹ͸࢖͍΍͍͢  ͬͪ͜͸࢖͍ʹ͍͘  ଞνʔϜʢOpsʣ

Slide 33

Slide 33 text

σʔλج൫ͱ͸ݴ͑ͳ͍ ɹར༻ऀ·Ͱಧ͔ͳ͍ύΠϓϥΠϯ

Slide 34

Slide 34 text

σʔλج൫ͱ͸ݴ͑ͳ͍ ɹఏڙऀ͔͠࢖Θͳ͍σʔληοτ

Slide 35

Slide 35 text

ෳ਺ͷσʔλιʔεʢDataʣͱ ෳ਺ͷར༻ऀʢOpsʣΛ  ϦϘϯͷΑ͏ʹ݁ͼ෇͚Δ΋ͷ ※͜͜Ͱ͍͏ʮෳ਺ʯ͸ʮn=1ʯͷ৔߹ΛؚΉ ɹࢲ͕ߟ͑Δσʔλج൫ͷఆٛ σʔλج൫

Slide 36

Slide 36 text

ɾར༻ऀʹ݁ͼ͔ͭͳ͍έʔεͩͱ……  → HadoopͰ΋ʮσʔλج൫ʯͱ͸ݴ͑ͳ͍ → Fluentd͸ِΓͷσʔλύΠϓϥΠϯɿػೳ͍ͯ͠ͳ͍   ɾར༻ऀ͕࢖͍ͬͯΔέʔεͩͱ……  → ΧΦεͳExcelγʔτͰ΋ʮσʔλج൫ʯͱݴ͑Δ  → ਓྗίϐϖ࡞ۀ͕ਅͷσʔλύΠϓϥΠϯɿػೳ͍ͯ͠Δ ɹ͜ͷఆ͔ٛΒݴ͑Δ͜ͱ

Slide 37

Slide 37 text

σʔλιʔεʢDataʣͱར༻ऀʢOpsʣΛ݁ͿσʔλͷྲྀΕͷͲ͔͜Ͱ  ඞવతʹ 3෼ྨͷ໾ׂ ͕Ռͨ͞ΕΔ͜ͱʹͳΔ  ʢ˞ύΠϓϥΠϯઃܭʹ͓͚Δ༩݅ͱͯ͠ೝࣝͨ͠΄͏͕͍͍ʣ ࢖ΘΕΔσʔλج൫ ɹʮ࢖ΘΕΔσʔλج൫ʯͷ࿦ཧߏ੒

Slide 38

Slide 38 text

ɹ3૚͕ʮଘࡏ͠ͳ͍ʯΑ͏ʹݟ͑Δͷ͸ ·ͩσʔλ͕े෼ʹ׆༻͞Ε͍ͯͳ͍έʔε  or    Ϣʔεέʔε΍ۀ຿ϑϩʔʹݟམͱ͕͋͠Δέʔε

Slide 39

Slide 39 text

1. ͸͡Ίʹ 2. σʔλج൫ͷ3෼ྨ 3. ࢲ͕ߟ͑Δσʔλج൫ͷఆٛ 4. ࢲ͕ߟ͑Δʮ͋Δ΂͖ߏ੒ʯ 5. ͓ΘΓʹ ɹΞδΣϯμ

Slide 40

Slide 40 text

͜Εͩͱอक؍఺͕ܽ೗͍ͯ͠Δ ɹٕज़ཁૉΛ෼͚Δͷ͸Ξϯνύλʔϯ σʔλج൫ͷ෼ྨ Ґஔ෇͚ ʢҰൠతͳʣٕज़ཁૉ σʔλϨΠΫ ݩσʔλΛͦͷ··  ίϐʔͨ͠΋ͷ 4ͳͲ σʔλ  ΢ΣΞϋ΢ε υϝΠϯ஌ࣝΛ  ൓өͨ͠΋ͷ )BEPPQͳͲ σʔλϚʔτ ಛఆ༻్޲͚ʹ  Ճ޻ɾ੔ཧͨ͠΋ͷ 5BCMFBVͳͲ

Slide 41

Slide 41 text

ɹ3෼ྨͷத਎͸มΘΔ ͋ΔूܭॲཧɾՃ޻ޙσʔλ͕3෼ྨͷͲ͜ʹଐ͢Δͷ͔͸࣌ͱڞʹมΘΔ    ྫʣ ɾ10ͷ෦ॺ͕֤ExcelͰಉ͡Ճ޻Λ͍ͯ͠ΔͳΒ  ɹͦͷॲཧ͸σʔλ΢ΣΞϋ΢εʹ౷߹͢΂͖    ɾۀ຿վળʹΑͬͯ1ͭͷ෦ॺͷཉ͍͠ࢦඪ͕มΘΔͳΒ  ɹڞ௨ࢦඪʹनΘΕͣʹσʔλϚʔτʹ෼཭͢΂͖ σʔλϚʔτ σʔλ  ΢ΣΞϋ΢ε σʔλϚʔτ σʔλϚʔτ

Slide 42

Slide 42 text

ɹσʔλ࢓༷͸มΘΔ ※ඇΤϯδχΞʹͱͬͯ͸ಉ͡ڍಈͷΑ͏ʹݟ͑ͯ΋ ॲཧͷλΠϛϯά΍Ϩίʔυ൓өͷॱং͕ඍົʹมΘ͍ͬͯΔ γεςϜ͕೔ʑมΘΔ ɾ৽ػೳͷ௥Ճ ɾෆ۩߹ͷमਖ਼ σʔλ࢓༷΋೔ʑมΘΔ σʔλ׆༻ σʔλΛ ੜ੒ ूܭํ๏ʹ Өڹ

Slide 43

Slide 43 text

ɹมԽʹରԠ͠ଓ͚Δ σʔλ࢓༷΍Ϣʔεέʔε͸มΘΔ  ※Կ೥΋มΘ͍ͬͯͳ͍ͳΒϓϩμΫτ΍νʔϜ͕ߏ଄มԽΛ൐͏ਐԽΛ͍ͯ͠ͳ͍ͱ͍͏͜ͱʢΉ͠ΖܦӦ໰୊ʣ ɾʮσʔλιʔεͱར༻ऀΛϦϘϯͷΑ͏ʹ݁ͼ͚ͭΔʯ͜ͱͷ೉͕͜͜͠͞ʹ͋Δ ɾ֎తৼΔ෣͍Λม͑ͣͱ΋ɺύΠϓϥΠϯͷ࿦ཧߏ੒͸मਖ਼͠ଓ͚Δ͜ͱͷॏཁੑ σʔλج൫

Slide 44

Slide 44 text

ɹ෼཭ɾ݁߹Λ܁Γฦ͢ σʔλϨΠΫ΍σʔλϚʔτͷมԽʹ߹Θͤͯ  தؒςʔϒϧͰ͋Δσʔλ΢ΣΞϋ΢εΛܧଓతʹ෼཭ɾ݁߹

Slide 45

Slide 45 text

૚ߏ଄ʹ͓͚ΔͭͷྲྀΕ .BSU ར༻ऀ޲͚*' 㲈σʔλϚʔτ 8BSFIPVTF υϝΠϯ஌ࣝΛද͢σʔλ 㲈σʔλ΢ΣΞϋ΢ε 4PVSDF ݩσʔλͷίϐʔ 㲈σʔλϨΠΫ σʔλύΠϓϥΠϯʹ͓͍ͯػց͕ॲཧ͢ΔྲྀΕ ࣄۀ΍γεςϜͷ  શσʔλΛίϐʔ͢Δ μογϡϘʔυ΍  ϨϙʔτͱରʹͳΔ ։ൃϓϩηεʹ͓͍ͯਓ͕ؒઃܭ͢ΔྲྀΕ ूܭɾ෼ੳύλʔϯΛ੔ཧ  㲈8*5)۟ͷසग़σʔλ ᶃ ᶄ ᶅ ᶃ ᶃ ᶄ

Slide 46

Slide 46 text

ɹਐԽతσʔλϞσϦϯά ɾσʔλج൫ʹ͓͚ΔσʔλϞσϦϯάઃܭͷಛ௃Λ཈͑Δ   ɾσʔλϞσϧͷ෼཭ͱ݁߹Λ௨ͯ͠υϝΠϯ஌ࣝΛ൓ө͢Δ͜ͱ͕େࣄ    ɾσʔλϞσϧͷϨΨγʔԽΛ๷͙ / ରԠ͢ΔͨΊͷߟ͑ํ    ɾཁ͢ΔʹʮγεςϜΛอक͠·͠ΐ͏ʯͱ͍͏࿩  ɹɹɾ΍Δ͜ͱ͸ϦϑΝΫλϦϯάɺϦΞʔΩςΫςΟϯά  ɹɹɾϞμϯͳݴ͍ํΛ͢ΔͱʮਐԽతΞʔΩςΫνϟʯతͳൃ૝

Slide 47

Slide 47 text

σʔλϨΠΫɺσʔλ΢ΣΞϋ΢εɺσʔλϚʔτΛ  શͯBigQueryͷσʔληοτͰ؅ཧ͢Δ ɹྫɿBigQueryͰ3૚ߏ଄Λ؅ཧ ɹɹɹɹɹɹBigQuery - Google Cloud Platform Source  ݩσʔλͷίϐʔ Warehouse  ओཁࢦඪɾසग़σʔλ  தؒςʔϒϧ Mart  ֤πʔϧ͔Β௚઀ࢀর  ඇٕज़ऀ޲͚IF

Slide 48

Slide 48 text

ྫɿmyapp__source__db ΞϯμʔείΞ2ͭͰཁૉΛ۠੾Δ  BEMʢCSSͷઃܭख๏ʣϥΠΫͳنଇ ɹσʔληοτͷ໋໊نଇ αʔϏε໊ source warehouse mart ϝΠϯDB αʔόϩά ΞΫηεղੳπʔϧ

Slide 49

Slide 49 text

WITH۟ͷ1ͭ1ͭΛΦϒδΣΫτͷΑ͏ʹѻ͏  ωετͤͣ͞ʹWITH͚۟ͩͰෳࡶͳूܭϩδοΫΛ૊Έཱ͍ͯͯ͘ ɹΦϒδΣΫτࢦ޲ͳSQL

Slide 50

Slide 50 text

ɹSQL on ύΠϓϥΠϯ Ճ޻ॲཧ͸શͯSQLϑΝΠϧʹدͤΔ + ϫʔΫϑϩʔΤϯδϯͰґଘ؅ཧ   ɾ໋໊نଇσʔληοτ + ΦϒδΣΫτࢦ޲SQL → grepͰӨڹൣғΛચ͍ग़ͤΔˍमਖ਼Ͱ͖Δ  ɾϫʔΫϑϩʔΤϯδϯͰલޙؔ܎ΛՄࢹԽ͢Ε͹ɺਓ͕ؒ௚ײతʹಛఆͰ͖Δ ɹɹɹྫ:Cloud Composer (GCP Full Managed Airﬂow)

Slide 51

Slide 51 text

ɹσʔλϚʔτΛऔΓࠐΉ 1. ΞφϦετ͕TableauͰ࣮ݧతʹσʔλϚʔτΛ࡞Δ  2. ܧଓར༻͢Δ৔߹͸SQL on σʔλύΠϓϥΠϯʹϩδοΫΛऔΓࠐΉ  ɹʢ࢖Θͳ͚Ε͹আ٫ɾΫϦʔχϯά͢Δʣ .BSU ར༻ऀ޲͚*' 㲈σʔλϚʔτ

Slide 52

Slide 52 text

ɹ·ͱΊɿਐԽతσʔλϞσϦϯάͷ࣮ફ σʔλج൫ͷ3छผΛ͍͔ʹϚωδϝϯτ͢Δ͔ ᶃҟͳΔٕज़ཁૉ΍νʔϜͰ؅ཧ͢Δʁ Noʂ  → ͜Ε·Ͱͷৗ౟खஈ  → σʔλϞσϧ͕Ԛછ͞ΕΔˍอकੑ͕ܽ೗͠΍͍͢   ᶄٕज़ཁૉΛدͤΔʁ Yesʂ  → ϦϑΝΫλϦϯά͠΍͍͢ʢʹܧଓਐԽͤ͞΍͍͢ʣઃܭࢥ૝  → ྫ͑͹ BigQuery + SQL + Composer ʹ౷Ұ͢Δ

Slide 53

Slide 53 text

1. ͸͡Ίʹ 2. σʔλج൫ͷ3෼ྨ 3. ࢲ͕ߟ͑Δσʔλج൫ͷఆٛ 4. ࢲ͕ߟ͑Δʮ͋Δ΂͖ߏ੒ʯ 5. ͓ΘΓʹ ɹΞδΣϯμ

Slide 54

Slide 54 text

ɹݸਓతͳ؍ଌൣғͰͷ࿩ ɹɹɹɾσʔλαΠΤϯςΟετ΍ػցֶशΤϯδχΞ  ɹɹɹ →σʔλϨΠΫΛॏࢹ͕ͪ͠ʢ୳ࡧͷͨΊͷݩσʔλʣ    ɹɹɹɾܦӦاը୲౰΍ΞφϦετ΍Ϛʔέλʔ  ɹɹɹ → σʔλϚʔτΛॏࢹ͕ͪ͠ʢՄࢹԽͷͨΊͷूܭࡁσʔλʣ Q. ͳ͔ͥʁ  A. ׆༻ऀͰ͋ͬͯɺج൫ఏڙऀͰ͸ͳ͍͔Βɻ

Slide 55

Slide 55 text

ɹج൫ఏڙऀͷ໨ઢ ɾγεςϜͷશମ૾Λݟ౉͢΂͠  ɾσʔλج൫ͷ3෼ྨʢϨΠΫɾ΢ΣΞϋ΢εɾϚʔτʣ͕͋Δ͸ͣ  ɾਐԽతσʔλϞσϦϯάΛ౿·͑ͯઃܭˍอक͢΂͠  ɾୈҰݪଇ͸ʮෳ਺ͷ࣍ݩͰܧଓత͔ͭ઴ਐతͳมߋΛࢧԉ͢Δʯ͜ͱ  ɾͦΕ͕Ͱ͖Δͷ͸γεςϜͷ  ɹ৴པੑʹίϛοτ͢ΔSREਓࡐ  ɹʢnot Πϯϑϥਓࡐʣ

Slide 56

Slide 56 text

ɹνʔϜͱΞʔΩςΫνϟ͸ॏཁ ɾԾʹΞφϦετνʔϜ͕TableauͰԣஅσʔλΛ੔ཧ͢Δ໾ׂΛ୲͏ͱʁ    ɾTableauΛϝΠϯͰ࢖͏ΞφϦετʹͱͬͯ͸շదͳσʔλϞσϧʹͳΔ  ɹɹɾ෼͔Γ΍͍͢μογϡϘʔυͰܦӦਞ΍Ϛʔέολʔ΋تͿ  ɹɹɾԣஅσʔλΛ੔ཧͨ͠ؾ෼ʹͳΔ    ɾػցֶशΤϯδχΞʹͱͬͯ͸࠷ѱͷσʔλϞσϧͱΠϯλʔϑΣʔε  ɹɹɾະདྷᶃɿTableau Server͕ग़ྗͨ͠CSVΛऔಘ → σʔλΛՃ޻ͯ͠ར༻  ɹɹɾະདྷᶄɿ΋͘͠͸ ΞφϦετνʔϜͷߏ૝Λແࢹ ͯ͠S3͔ΒσʔλΛҾ͖ൈ͘  ɹɹɾ݁࿦ɿԣஅσʔλΛ੔ཧͰ͖͍ͯͳ͍ - ͨͩͷ “Big ball of mud”

Slide 57

Slide 57 text

ɹνʔϜͱΞʔΩςΫνϟΛਖ਼͘͠બͿ ɾSREʢ͋Δ͍͸σʔλج൫ઐ໳νʔϜʣ͕ओମͱͳͬͯ  ɾBigQuery + SQLͷΑ͏ͳࠎଠͷٕज़ཁૉͰ  ɾશମ࠷దͳγεςϜઃܭΛਪਐ͢΂͖

Slide 58

Slide 58 text

ɹΞϯνύλʔϯ σʔλج൫ͷ෼ྨΛผνʔϜ΍ผγεςϜͰ؅ཧ  ྫɿΠϯϑϥνʔϜͱΞφϦετνʔϜ  ྫɿ4ͱ)BEPPQͱ5BCMFBV σʔλϞσϧվम͕ߴίετʹͳΔʢͷͰഁ୼ʣ  ɾίϯϙʔωϯτԣஅͷϦΞʔΩςΫςΟϯά͕ඞཁʹͳΔ  ɾ࠷ѱͷ৔߹͸ϏοάϦϥΠτ૬౰ͷ࡞Γ௚͕͠ඞཁʹͳΔ σʔλϞσϧʢ࿦ཧઃܭʣ͸ہॴ࠷దԽ͢Δ ɾϩδοΫͷԚછɺٕज़తෛ࠴ͷ஝ੵ  ɾ͍ΘΏΔzίϯ΢ΣΠͷ๏ଇz͕ಇ͘

Slide 59

Slide 59 text

ɹσʔλج൫ͷAsIs ผγεςϜͰ؅ཧ͢Δ͜ͱ͕ଟ͍ S3ʢϨΠΫʣ → Hadoopʢ΢ΣΞϋ΢εʣ → TableauʢϚʔτʣ     ैདྷͷίϯςΩετ͕എܠʹ͋Δ  ɾ1౓࡞ͬͨΒऴΘΓ  ɾҟͳΔ෦ॺͰ؅ཧ͢Δ  ɾͦΕͧΕͷϨΠϠʔͰಘҙͳπʔϧʹدͤΕ͹ॳظߏங͕༰қʹͳΔ

Slide 60

Slide 60 text

ɹσʔλج൫ͷToBe …? ɾॳظߏஙʢDevʣ͚ͩʹ஫໨͢ΔͷͰ͸ͳ͘  ɾอकӡ༻ʢOpsʣ΋ࢹ໺ʹೖΕΔ    ࠷దͳσʔλج൫ͷ͋Γํ͸ɺ͖ͬͱ͜Ε·Ͱ  ੈؒҰൠͰݴΘΕ͖ͯͨ΋ͷͱҧͬͨ࢟ʹͳΔ͸ͣ

Slide 61

Slide 61 text

݁࿦ʢ࠶ܝʣ .BSU ར༻ऀ޲͚*' 㲈σʔλϚʔτ 8BSFIPVTF υϝΠϯ஌ࣝΛද͢σʔλ 㲈σʔλ΢ΣΞϋ΢ε 4PVSDF ݩσʔλͷίϐʔ 㲈σʔλϨΠΫ σʔλύΠϓϥΠϯʹ͓͍ͯػց͕ॲཧ͢ΔྲྀΕ ࣄۀ΍γεςϜͷ  શσʔλΛίϐʔ͢Δ μογϡϘʔυ΍  ϨϙʔτͱରʹͳΔ ։ൃϓϩηεʹ͓͍ͯਓ͕ؒઃܭ͢ΔྲྀΕ ूܭɾ෼ੳύλʔϯΛ੔ཧ  㲈8*5)۟ͷසग़σʔλ ᶃ ᶄ ᶅ ᶃ ᶃ ᶄ

Slide 62

Slide 62 text

ɹࢀߟࢿྉ ੨໦ๆ࿠ʰ10೥ઓ͑Δσʔλ෼ੳೖ໳ɹSQLΛ෢ثʹσʔλ׆༻࣌୅Λੜ͖ൈ͘ʱ  https://www.sbcr.jp/products/4797376272.html   ΫϦεɾόʔνϟϧʰϨΨγʔιϑτ΢ΣΞվળΨΠυʱ  https://www.shoeisha.co.jp/book/detail/9784798145143   Neal Ford΄͔ʰਐԽతΞʔΩςΫνϟ ― ઈ͑ؒͳ͍มԽΛࢧ͑Δʱ  https://www.oreilly.co.jp/books/9784873118567/   ʮϏοάσʔλॲཧσʔλϕʔεͷશମ૾ͱ࢖͍෼͚2018೥versionʯby @fetarodc  https://www.slideshare.net/tetsutarowatanabe/2018version-115735455

Slide 63

Slide 63 text

ɹ͍͞͝ʹ ͜ͷ಺༰͸ʰࣄۀͷάϩʔεΛࢧ͑ΔDataOpsͷݱ৔ʱͷ p165-p168 ʹ֘౰͠·͢ɻ  શ268ϖʔδதͨͬͨ4ϖʔδ෼ͷ಺༰Ͱ͔͋͠Γ·ͤΜɻ  ஌ݟΛଞʹ΋ެ։͍ͯ͠·͢ͷͰɺͥͻ͝ཡ͍͚ͨͩΔͱ޾͍Ͱ͢ɻ

Slide 64

Slide 64 text

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ presented by @yuzutas0  https://www.pexels.com/photo/grayscale-photo-of-metal-building-185039/