$30 off During Our Annual Pro Sale. View Details »

データレイク構築後の四方山話 #DPM / 20190905

yuzutas0
PRO
September 05, 2019

データレイク構築後の四方山話 #DPM / 20190905

Data Platform Meetup の登壇資料です。
https://data-platform-meetup.connpass.com/event/142822/

yuzutas0
PRO

September 05, 2019
Tweet

More Decks by yuzutas0

Other Decks in Technology

Transcript

  1. σʔλϨΠΫߏஙޙͷ࢛ํࢁ࿩

    2019-09-05

    data platform meetup #DPM

    presented by @yuzutas0

    https://www.pexels.com/photo/autumn-clouds-conifers-daylight-221395/


    View Slide

  2. WEBʹެ։͠·͢ #DPM

    ɹࡱӨ΍ϝϞ͸ෆཁͰ͢ɻϦϥοΫεͯ͠ฉ͍͍͚ͯͨͩΕ͹ͱࢥ͍·͢ɻ

    εϥΠυ 140+ ຕ / 15min

    ɹؤுͬͯ෇͍͖͍ͯͯͩ͘͞ɻࠓ೔͸͚ͬ͜͏͍͚Δؾ͕͠·͢ɻ

    ɹखݩͷࢿྉ܈Λίϐϖ + ԥΓॻ͖ͨ͠εϥΠυͰ͢ɻաڈࢿྉ܈ͱಉ͡಺༰Ͱ͢ɻ

    ɹਓؒ͸௥͍٧ΊΒΕΔͱ͜͏͍͏εϥΠυΛ࡞ͬͯ͠·͏ऑ͍ੜ͖෺ͳͷͰ͢ɻ
    Α΋΍·࿩Ͱ͢ʂϙΤϜ࿮Ͱ͢ʂ

    ɹ͓ञΛҿΈͳ͕Βগਓ਺Ͱμϥμϥͱ࿩ͨ͢ΊͷωλͰ͢ɻ

    ɹ࢒೦ͳ͕Βཱ೿ͳൃදࢿྉͰ͋Γ·ͤΜɻϙουΩϟετͩͱࢥͬͯฉ͍͍ͯͩ͘͞ɻ


    ɹҿΜͰ͍͜͏ͥʂ
    IUUQTXXXQFYFMTDPNQIPUPBMDPIPMJDCFWFSBHFCBSCFFSESJOL

    View Slide

  3. 1. ͸͡Ίʹ
    2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ

    3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ

    4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ

    5. Guide to Data Warehouse & Mart Designʢࣗ࿦ฤʣ

    6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ
    7. ͜ͷઌੜ͖ͷ͜Δʹ͸


    ɹΞδΣϯμ

    View Slide

  4. ɹ@yuzutas0
    ɹɹ




    View Slide

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




    PyCon JP ’17

    ϕεττʔΫΞϫʔυ༏ल৆
    σϒαϛՆ ’18 Ξϯέʔτຬ଍౓No.1

    ʢཌ೥ͷϕετεϐʔΧʔ৆ʹ૬౰ʣ

    View Slide



  6. ɹྫ: ਐԽతσʔλϞσϦϯά
    .BSU
    ར༻ऀ޲͚*'
    㲈σʔλϚʔτ
    8BSFIPVTF
    υϝΠϯ஌ࣝΛද͢σʔλ
    㲈σʔλ΢ΣΞϋ΢ε
    4PVSDF
    ݩσʔλͷίϐʔ
    㲈σʔλϨΠΫ
    σʔλύΠϓϥΠϯʹ͓͍ͯػց͕ॲཧ͢ΔྲྀΕ
    ࣄۀ΍γεςϜͷ

    શσʔλΛίϐʔ͢Δ
    μογϡϘʔυ΍

    ϨϙʔτͱରʹͳΔ
    ։ൃϓϩηεʹ͓͍ͯਓ͕ؒઃܭ͢ΔྲྀΕ
    ूܭɾ෼ੳύλʔϯΛ੔ཧ

    㲈8*5)۟ͷසग़σʔλ
    ᶃ ᶄ ᶅ
    ᶃ ᶃ

    View Slide



  7. ɹനӋͷ໼

    View Slide

  8. ಺ྠͰू·͓ͬͯञΛҿΈͳ͕ΒσΟεΧογϣϯ͢Δձ
    ʢΏ͑ʹ30ਓͷืू࿮Ͱ։࢝ͨ͠ʣ
    ૿࿮ʹ͙࣍૿࿮Ͱɺ૝ఆͷ6.5ഒͷԠื͕͋ͬͨͱͯ͠΋οʂ

    ͋͘·Ͱ΋ʮσΟεΧογϣϯͷࡐྉʯΛఏڙ͠·͢οʂ


    ɹຊ೔ͷझࢫʢओ؍ʣ

    View Slide

  9. ʮΈΑ͏Έ·ͶͰσʔλΛૄ௨ͨ͠ʯ

    ʮ͜ͷ͋ͱج൫ΛͲ͏ਐԽ͍͚ͤͯ͞͹͍͍ͩΖ͏͔ʯ


    ͱࢥ͍೰ΜͰ͍ΔΞφϦετ
    ʢγεςϜͷ࿩΋ੵۃతʹཧղ͍ͯ͜͠͏ͱ͍͏ํʑʣ

    ͱɺͦͷΧ΢ϯλʔύʔτʹ౰ͨΔSWΤϯδχΞ


    ɹຊ೔ͷ૝ఆλʔήοτ

    View Slide



  10. ɹຊ೔࿩͢͜ͱ Ͷ͐ɺࠓ͔Β࿩͢Αʂ
    ֓ཁ ݁࿦
    ߏங௚ޙʹΑ͋͘Δࣦഊࣄྫ΍ઃܭϛε ࠓΛੜ͖Α͏
    σʔλ΢ΣΞϋ΢εɾϚʔτͷσβΠϯ ຊΛಡ΋͏
    ͜ͷઌੜ͖ͷ͜Δʹ͸ ϜμΛল͜͏

    View Slide

  11. উͪʹෆࢥٞͷউͪ͋Γɻ

    ෛ͚ʹෆࢥٞͷෛ͚ͳ͠ɻ


    ʰ݋ஊʱΑΓҾ༻


    ɹͳࣦͥഊࣄྫʁ
    https://www.pexels.com/photo/brown-concrete-bridge-between-trees-2091009/

    View Slide

  12. ʮΈΑ͏Έ·ͶͰσʔλΛૄ௨ͨ͠ʯ

    ͲͷΑ͏ͳܦҢͰ͋Εɺ͜ͷঢ়گ·Ͱ౸ୡͰ͖ͨͷ͸ɺؒҧ͍ͳ͘େ͖ͳୈҰา
    ͦͷ࣮੷Λ࡞Δʹ͸ɺ຾Εͳ͍໷΋͋ͬͨͩΖ͏


    ʮ͜ͷ͋ͱج൫ΛͲ͏ਐԽ͍͚ͤͯ͞͹͍͍ͩΖ͏͔ʯ
    Ͳͷఔ౓ͷԹ౓ײͰ͋ΕɺઌΛݟਾ͑ͯ೰ΊΔͷ͸ɺจ۟ͳ͠ʹՁ஋ͷ͋Δ౰ࣄऀҙࣝ

    ·͞ʹࠓɺوॏͳ࣌ؒΛ࢖ͬͯɺษڧձʹࢀՃͨ͠ΓɺεϥΠυΛಡΜͰ͍ͩͬͯ͘͞Δ


    ͜ͷੈքઢΛબΜͰ͘Εͨ͋ͳͨͷʮԫۚͷਫ਼ਆʯʹࢲ͸ܦҢΛද͢Δοʂ
    ͦͯ͠ɺͦ͏͍͏ਓ͔ͩΒͦؕͬͯ͜͠·͏᠘͕ɺ͜ͷੈքʹ͸୔ࢁ͋Δͱࢥ͍·͢


    ɹ͋ͳͨ͸͍͢͝ʂ

    View Slide

  13. ද໘తͳख๏࿦΍Ωʔϫʔυʹ

    ৼΓճ͞Εͳ͍Ͱ΄͍͠

    े෼ͳ৘ใऩूΛܦͯ
    ΠγϡʔۦಈͷҙࢥܾఆΛͯ͠΄͍͠
    ʮΞφϦετ͕ࢥ͍͍ͭͨཧ૝ͷج൫ʯΛ
    ؔ܎ऀʹԡ͠෇͚ͳ͍Α͏ʹ৺ֻ͚ͯ΄͍͠


    ɹ఻͍͑ͨ͜ͱ

    View Slide

  14. 1. ͸͡Ίʹ
    2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ

    3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ

    4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ

    5. Guide to Data Warehouse & Mart Designʢࣗ࿦ฤʣ

    6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ
    7. ͜ͷઌੜ͖ͷ͜Δʹ͸


    ɹΞδΣϯμ

    View Slide

  15. ूΊͨσʔλΛͲ͏׆༻͢Δ͔


    ɾൃදऀݸਓ͕࠷΋ੜ࢈తͩͱࢥ͏໰͍ͷ1ͭ

    ɾ׆༻Λߟ͑Ε͹ج൫ͷ͋Γํ͸ࣗવͱܾ·Δ

    ɾ͔ͤͬ͘ૄ௨ͨ͠ͳΒͲΜͲΜ׆༻͍ͯ͜͠͏


    ɹΑ͋͘Δ૬ஊᶃ

    View Slide

  16. ɾݱࡏͷԆ௕Ͱଊ͑ͨ΄͏͕ؔ܎ऀͷೲಘΛಘ΍͍͢

    ɾ͍͖ͳΓToBeϕʔεͩͱص্ͷۭ࿦ʹؕΓ΍͍͢


    ɹ࠷ॳʹ֬ೝ͢Δ͜ͱ
    "T*Tͷձ࿩ɾߦಈ
    ɾ্ҐϨΠϠʔͷҙ޲

    ɾߏங࣌ࢿྉͰͷݟཱͯ

    ɾݱ৔Ͱطʹࢼͨ͜͠ͱ
    ࠓޙͷ׆༻
    ʁ

    View Slide



  17. ɹணख͠΍͍͢ˍޮՌΛग़͠΍͍͢ࢪࡦᶃ
    ϞχλϦϯάޮ཰Խ ػೳ։ൃͷޮՌଌఆ
    طଘۀ຿ͷϦϓϨΠε 1%4αΠΫϧΛճͨ͢ΊͷୈҰา
    https://speakerdeck.com/yuzutas0/20170909

    View Slide



  18. ɹணख͠΍͍͢ˍޮՌΛग़͠΍͍͢ࢪࡦᶄ
    ू٬ɾ$3. ΠϯϑΥάϥϑΟοΫ
    ୹ظతʹ਺ࣈ͕৳ͼ΍͍͢ ϩΠϠϦςΟࢪࡦˍऔࡐɾόζʹܨ͕Γ͏Δ
    https://pitpa.jp/anniversary/half
    https://prtimes.jp/main/html/rd/p/000000126.000025184.html

    View Slide

  19. ͜ͷਤΛॻ্͍ͨͰ߈ΊΔॱ൪ΛܾΊΔ / ࢒Δબ୒ࢶ͸ଟ͘ͳ͍͸ͣ


    ɹࠜڌΛ΋ͬͯҙࢥܾఆ͠Α͏
    ଧͪखᶃ
    YYYYY
    ଧͪखᶄ
    YYYYY
    ଧͪखᶅ
    YYYYY
    ޮՌ਺ࣈ ίετ ϦεΫ σϦόϦ
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ޮՌମݧ
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ޮՌ࣋ଓ
    ̋˚✕
    ̋˚✕
    ̋˚✕

    View Slide

  20. Ωʔͷͳ͍தؒςʔϒϧ


    ɹΑ͋͘Δ૬ஊᶄ

    View Slide



  21. ɹ෼ੳऀ͕ݟ΍͍͢Α͏ʹઃܭ
    JUFNUBCMF
    DPMVNO TBNQMF
    JE
    OBNF ʹΜ͡Μ
    UZQF@JE
    UZQFUBCMF
    DPMVNO TBNQMF
    JE
    OBNF ໺ࡊ
    QBZNFOUUBCMF
    DPMVNO TBNQMF
    JE BCDEF
    JUFN@JE
    DSFBUFE@BU
    QBZNFOUJOUFSNFEJBUFUBCMF
    DPMVNO TBNQMF
    JUFN@OBNF ʹΜ͡Μ
    UZQF@OBNF ໺ࡊ
    DSFBUFE@BU
    Ωʔ৘ใΛ

    ࡟͗མͱͨ͠

    View Slide



  22. ɹ݁ہ͸Ωʔ͕ཉ͘͠ͳΔ
    ʮ໺ࡊJEʯͱʮ໺ࡊJEʯͰ

    දࣔը໘͕Ұ෦ҟͳΔͷͰ෼͚ͯूܭ͍ͨ͠

    ʢσʔλϨΠΫΛࢀর͢Δ͜ͱʹͳΔʣ
    ౷ܭϓϩάϥϜͰ෼ੳ͢Δʹ͋ͨͬͯ

    ࣗવݴޠΑΓΩʔͷ΄͏͕ॲཧ͠΍͍͢

    ʢ֤෦ॺͷεΫϦϓτͰಠࣗʹ࠾൪͢Δ͜ͱʹͳΔʣ

    View Slide

  23. ɾॳ৺ऀͷઃܭ͸ӡ༻։࢝௚ޙʹഁ୼͢Δ

    ɾ͔ͦ͜ΒֶͿ΂͖͸ʮΩʔΛೖΕ͓ͯ͜͏ʯ͚ͩͰͳ͘ʮΠςϨʔγϣϯΛճͦ͏ʯ

    ɾσʔλϨΠΫͷ࣌఺Ͱܽଛ͢Δͱ෮چࠔ೉ͳͷͰ͚ͦͩ͜͸࠷ॳʹ๷͙

    ɾݱ৔ʹଇ͍ͯ͠ͳ͍தؒςʔϒϧ͕ੜ͖࢒Δ͜ͱ͸ͳ͍

    ɾ͋͘·Ͱ΋σʔλϚʔτΛى఺ʹͯ͠෼཭ͱ݁߹Λ܁Γฦ͢


    ɹʮ͜ΕͳΒݟ΍͍͢ʯ͸͋͘·ͰԾઆ
    σʔλϚʔτ
    σʔλ

    ΢ΣΞϋ΢ε
    σʔλϚʔτ
    σʔλϚʔτ
    https://speakerdeck.com/yuzutas0/20190213

    View Slide

  24. தؒςʔϒϧΛἧ͑ͨͷʹ࢖ΘΕͳ͍


    ɹΑ͋͘Δ૬ஊᶅ

    View Slide

  25. தؒςʔϒϧͷར༻ऀ਺ = σʔλར༻ऀ਺ * தؒςʔϒϧར༻཰


    ɹσʔλͷຽओԽ͸ग़དྷ͍ͯΔ͔ʁ
    νʔϜ" νʔϜ# νʔϜ$ νʔϜ% νʔϜ& νʔϜ' νʔϜ( νʔϜ) νʔϜ* νʔϜ+ νʔϜ,
    ੜϩά

    ಠࣗར༻
    σʔλ5ࢧԉ

    ۀ຿ґཔ
    σʔλ5ࢧԉ

    σʔλग़ྗ
    ࣗओత

    σʔλग़ྗ
    ୲౰ऀґଘ ୲౰ऀґଘ ୲౰ऀґଘ
    ࣗओత

    σʔλੜ੒
    ଞνʔϜґཔ
    ج൫ߩݙʂ ୲౰ऀґଘ ୲౰ऀґଘ
    ہॴԽͷน
    ࣗ૸ͷน
    վળͷน
    https://speakerdeck.com/yuzutas0/20180402

    View Slide



  26. ɹσʔλͷຽओԽΛଅਐ͢Δࢪࡦ
    https://speakerdeck.com/yuzutas0/20180402

    View Slide



  27. ɹͦͷσʔλ͸࢖ΘΕ͍ͯΔ͔ʁ
    ΞφϦετ͕ʮઈର࢖͏͔Βʂʯͱ࡞ͬͨதؒςʔϒϧͷJoinݩ
    σʔλར༻ΛPVɾUUͰιʔτ͢ΔͱɺύϨʔτͷ๏ଇ͕ޮ͘͜ͱ͕෼͔Δ
    ར༻ස౓ͷগͳ͍σʔλΛ࢖͍΍ͯ͘͢͠΋ࢫຯ͸গͳ͍
    ͜ͷ΁Μ

    View Slide



  28. ɹ࢖ΘΕ͍ͯΔσʔλʹॏ఺Λஔ͘
    PV * UU ͷ2࣠ͰϚοϐϯά͢ΔͱҰ໨Ͱ෼͔Δɻ
    PV or UU ͷ΢ΤΠτΛՄࢹԽ͢ΔͱҰ໨Ͱ෼͔Δɻ

    View Slide



  29. ɹ10෼ͰՄࢹԽ͢Δ
    ͜ͷهࣄͷԠ༻Ͱ࠶ݱՄೳɹhttp://yuzutas0.hatenablog.com/entry/2018/12/18/160000

    View Slide



  30. ɹαϯϓϧSQL
    WITH tables AS (
    SELECT table_id
    FROM `{project_id}.{dataset_name}`.__TABLES__
    WHERE table_id NOT LIKE ‘LOAD_TEMP_%' AND table_id NOT LIKE ‘TMP_%'
    ),
    log AS (
    SELECT
    REGEXP_REPLACE(data.resource, ‘projects/{project_id}/datasets/{dataset_name}/tables/‘, '') AS table,
    protopayload_auditlog.authenticationInfo.principalEmail AS user,
    DATE(timestamp) AS day
    FROM
    `{project_id}.{source__cloudaudit__bigquery}.cloudaudit_googleapis_com_data_access_*`,
    UNNEST(protopayload_auditlog.authorizationInfo) AS data
    WHERE data.permission = 'bigquery.tables.getData'
    ),
    calc AS (
    SELECT table, day, COUNT(*) AS PV, COUNT(DISTINCT user) AS UU
    FROM log
    WHERE table NOT LIKE ‘LOAD_TEMP_%' AND table != ‘__TABLES__' AND table NOT LIKE 'TMP_%'
    GROUP BY 1, 2
    )
    SELECT tables.table_id, calc.PV, calc.UU, REGEXP_REPLACE(CAST(calc.day AS STRING), '-', '') AS day
    FROM tables
    LEFT JOIN calc
    ON tables.table_id = calc.table

    View Slide

  31. http://yuzutas0.hatenablog.com/entry/2019/06/05/233000


    ɹσʔλۦಈʹΑΔϓϩμΫτվળ
    ࢲ͸σʔλج൫ΛͭͷϓϩμΫτͩͱҐஔ෇͚͍ͯ·͢ɻ

    ࣾ֎޲͚ʹ8αʔϏεΛӡӦ͢Δͷͱಉ͡Ͱ͢ɻ

    ΍Δ͜ͱ͸ʮࣗ෼͕ߏ૝ͨ͠ϓϩμΫτʯͷߏஙɾӡӦͰ͢ɻ

    View Slide

  32. ᶃूΊͨσʔλΛͲ͏׆༻͢Δ͔

    ᶄΩʔͷͳ͍தؒςʔϒϧ

    ᶅதؒςʔϒϧΛἧ͑ͨͷʹ࢖ΘΕͳ͍


    ɹ૬ஊᶃʙᶅ

    View Slide

  33. σʔλΛ׆༻͢ΔՕॴʹ͓͍ͯ

    ROIΛ࠷େԽ͢ΔͨΊͷҙࢥܾఆ͕Ͱ͖͍ͯΔ͔ʁ

    ΞφϦετͱͯ͠࠷ߴͷৼΔ෣͍͕Ͱ͖͍ͯΔ͔ʁ


    ɹσʔλۦಈ for Output

    View Slide

  34. 1. ͸͡Ίʹ
    2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ

    3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ

    4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ

    5. Guide to Data Warehouse & Mart Designʢࣗ࿦ฤʣ

    6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ
    7. ͜ͷઌੜ͖ͷ͜Δʹ͸


    ɹΞδΣϯμ

    View Slide

  35. ར༻ऀ͕σʔλΛյͯ͠͠·͏


    ɹΑ͋͘Δ૬ஊᶆ

    View Slide



  36. ɹݖݶ؅ཧͱϙϦγʔఏࣔʢྫʣ
    jobUser N/A ˚ ̋
    dataViewer N/A ̋ ̋
    dataEditor N/A ✕ ̋
    γεςϜࢀর Read Only Read & Write
    ౿Έ୆ σʔλϨΠΫ σʔλϚʔτ
    GCS
    BigQuery
    Project A
    BigQuery

    Project B

    View Slide

  37. ʮॻ͍ͯ͋Δ௨Γʹ΍Ζ͏ʯͱ͸ݴ͍ͬͯͳ͍
    ࠷ॳ͔ΒGCPϓϩδΣΫτΛ෼͚ΔͷͰ͸ͳ͘
    Πγϡʔ͕ݱ৔Ͱڍ͕ͬͨஈ֊ͰਐԽΛݕ౼͢Δ
    ͋͘·Ͱ΋ʮࠓͷʯʮݱ৔ͷʯʮΠγϡʔʯʹج͍ͮͯ

    ଥ౰ͳଧͪखΛ1ͭ1ͭΠϯΫϦϝϯλϧʹ࠾୒͢Δ


    ɹྫͰ͋ͬͯɺਪ঑Ͱ͸ͳ͍

    View Slide

  38. https://cloud.google.com/docs/enterprise/best-practices-for-enterprise-organizations?hl=ja


    ɹࢀߟɿΫϥ΢υαʔϏεͷެࣜҊ಺

    View Slide



  39. ɹࣗಈԽͳΒTerraformͰίʔυ؅ཧ

    resource "google_project_iam_custom_role" “datalake_user” {
    project = “${module.xxx.gcp_project_id}”
    role_id = "DatalakeUser"
    title = “BigQuery Reader and Writer”
    description = “Can use job, read and write data”
    permissions = [“bigquery.jobUser”, "bigquery.dataViewer", "bigquery.dataEditor",]
    }
    resource "google_project_iam_member" “analytics_team_is_datalake_user” {

    project = "${module.xxx.gcp_project_id}"
    role = "projects/${module.xxx.gcp_project_id}/roles/${google_project_iam_custom_role.datalake_user.role_id}"
    count = “${length(var.analytics_team)}”

    member = "user:${var.analytics_team[count.index]}"
    }

    variable “analytics_team" {
    description = “Analytics team members"
    type = "list"
    default = [“[email protected]”,]
    }


    View Slide

  40. Ϧʔϯ΍ΞδϟΠϧ΍εΫϥϜͷىݯʹ౰ͨΔେ໺଱Ұࢯ͕࠷ॳʹ΍ͬͨ͜ͱ

    ʹυΩϡϝϯτԽʢۀ຿ͷඪ४Խʣ


    ɹखಈӡ༻ͳΒखॱॻ͸ෆՄܽ
    τϤλࣗ޻ʹసग़͔ͯ͠Βɺࢲ͕·ͣඪ४࡞ۀදΛͭ͘Εͱ

    ݺͼ͔͚ͨ͜ͱ͸͍͏·Ͱ΋ͳ͍ɻ
    ੜ࢈ݱ৔ͷجຊͱ΋͍͏΂͖ඪ४࡞ۀදͮ͘Γ͔ΒɺτϤλੜ࢈ํࣜ

    ͮ͘Γ΁ͷࡾޒ೥ʹΘͨΔಓఔΛา·ͤΔ΋ͱΛͳ͍ͯ͠ΔΑ͏ʹࢥ͏ɻ

    ʰτϤλੜ࢈ํࣜ - ୤ن໛ͷܦӦΛΊͯ͟͠ʱΑΓҾ༻
    https://speakerdeck.com/yuzutas0/20170703

    View Slide



  41. ɹखॱॻ͸Ձ஋ʹ௚݁͢Δ
    ɹɹɹɹར༻Ҋ಺ɾखॱॻʢ0QTࢹ఺ʣ
    #BE (PPE
    πʔϧ

    ʢ%FWࢹ఺ʣ
    (PPE
    ࡞ͬͨਓ͔͠࢖͑ͳ͍

    ʢຊਓʹ͸ศརʣ
    ࠷ߴ
    #BE
    ࡞ͬͨਓ͔͠࢖͑ͳ͍

    ʢຊਓʹ΋ෆศʣ
    ·͊࢖͑Δ
    ͕Μ͹ͬͯͬͪ͜Λ

    ໨ࢦ͕ͪ͠
    ͬͪ͜ͷ΄͏͕؆୯Ͱ

    ޮՌ΋ߴ͍ʢ͔΋ʣ

    View Slide



  42. ɹυΩϡϝϯτΛίʔυͷΑ͏ʹѻ͏16ͷύλʔϯ (Document Design Pattern)

    ● Ϣʔεέʔε෼ੳ

    ● ϦϑΝΫλϦϯά

    ● ϖΞϓϩάϥϛϯά

    ● Ϟϒϓϩάϥϛϯά

    ● όʔδϣϯ؅ཧ


    ● ΞʔΩςΫτνʔϜ

    ● ίϯ΢ΣΠͷ๏ଇ

    ମ੍ͷύλʔϯ

    ● ٕज़తෛ࠴

    ● ϙʔτϑΥϦΦ

    ● ίʔσΟϯάΛଅ͢

    ● ίʔυϨϏϡʔ

    ● ϘʔΠεΧ΢τ

    ● Rule of Three
    จԽͷύλʔϯ

    ● MVCϞσϧ

    ● GoFσβΠϯύλʔϯ

    ● ܧଓతσϦόϦʔ /

    ΠςϨʔγϣϯ
    ߏ੒ͷύλʔϯ ࡞ۀͷύλʔϯ
    ˞ମ੍΍จԽ͸σβΠϯʢઃܭʣʹӨڹΛ༩͑ΔͷͰσβΠϯύλʔϯʹؚΊ͍ͯ·͢
    ࢀরʰDevOpsͱυΩϡϝϯτσβΠϯύλʔϯʱ http://yuzutas0.hatenablog.com/entry/2017/07/06/083000

    View Slide

  43. σʔλͷߋ৽͕஗͍


    ɹΑ͋͘Δ૬ஊᶇ

    View Slide

  44. ʮQ. Ͳͷॲཧ͕஗͍ʁʯ ʮA. ෼͔Βͳ͍ʯ͕ѹ౗తେଟ਺

    צͱܦݧͱ౓ڳͰ͸ͳ͘ɺσʔλۦಈͰ໰୊Λ෼ੳɾղܾ͠Α͏


    ɹܭଌ͔Β࢝ΊΑ
    ॲཧ͕࣌ؒ࠷΋௕͍ՕॴʢʹϘτϧωοΫʣΛνϡʔχϯά͢Δ

    View Slide

  45. ͜ͷॱ൪Ͱࢥߟ͠·͠ΐ͏


    ɹΞφϦετ΋SWΤϯδχΞ΋ಉ͡
    Ұൠతͳ࿦఺ ࠓճͷ͓୊
    8IBU ԿΛୡ੒͍͔ͨ͠ʁ ٻΊΔߋ৽ස౓͸ʁ
    8IFSF Ͳ͜ʹ໰୊͕͋Δ͔ʁ ͲͷՕॴͷॲཧ͕஗͍͔ʁ
    8IZ ͳͥ໰୊͕ى͖͍ͯΔ͔ʁ ͳͥͦͷॲཧ͕஗͍͔ʁ
    )PX ͲͷΑ͏ʹղܾ͢Δ͔ʁ ͲͷΑ͏ʹνϡʔχϯά͢Δ͔ʁ

    View Slide

  46. ಛʹʮγεςϜ෦໳ͷରԠ͕஗͍ʯͱݴͬͯ෼ੳ෦໳͕ಠࣗʹܧ͗଍ͨ͠έʔε


    ɹܭଌ͠ʹ͍͘γεςϜ
    ݩσʔλ
    ֤෦ॺ͕σʔλࢀরγεςϜΛܧ͗଍͠
    https://speakerdeck.com/yuzutas0/20170909

    View Slide



  47. ɹද໘͸ҧ͑Ͳ՝୊͸ಉ͡ ※10ࣾҎ্ͰݟͨΞϯνύλʔϯ
    http://yuzutas0.hatenablog.com/entry/2018/12/04/190000

    View Slide



  48. ෳ਺ͷσʔλιʔεʢDataʣͱ ෳ਺ͷར༻ऀʢOpsʣΛ

    ϦϘϯͷΑ͏ʹ݁ͼ෇͚Δ΋ͷ ※͜͜Ͱ͍͏ʮෳ਺ʯ͸ʮn=1ʯͷ৔߹ΛؚΉ
    ɹࢲ͕ߟ͑Δσʔλج൫ͷఆٛ
    σʔλج൫
    https://speakerdeck.com/yuzutas0/20190213

    View Slide

  49. SREνʔϜ* ͕શମઃܭ͔Βݟ௚ͯ͠σʔλج൫Λ੔උ

    Modelʢऩूɾ஝ੵɾՃ޻ʣͱ Viewʢࢀরɾར༻ʣ


    ɹγϯϓϧʹͯ͠γεςϜશମΛ࠷దԽ
    *͜ͷ࿦఺ͷऔΓ·ͱΊ͸SRE͕๬·͍͠ɻϏδωεܦӦʹཱ͍ۙ৔ͷϩʔϧ͕൑அͯ͠΋

    ৘ใγεςϜઃܭͷઐ໳ՈͰͳ͍ͱɺσʔλϞσϦϯά΍γεςϜ͸ઃܭഁ୼͕ͪ͠ɻ
    https://speakerdeck.com/yuzutas0/20180727

    View Slide

  50. σʔλ͕ະߋ৽ˍܽམͨ͠··์ஔ


    ɹΑ͋͘Δ૬ஊᶈ

    View Slide

  51. ϫʔΫϑϩʔΤϯδϯ͔Βಠཱͨ͠QAγεςϜ܈


    ɹσʔλ඼࣭Λ୲อ͢Δ
    &5-
    ςετίʔυ
    ݅਺Ұக؂ࢹ
    ߋ৽؂ࢹ
    ஗Ԇঢ়گ

    ՄࢹԽ

    View Slide

  52. PythonͰSQLΛϥοϓͯ͠unittestͰexpectedͱactualΛൺֱ

    https://speakerdeck.com/yuzutas0/20170909


    ɹςετίʔυ

    View Slide

  53. BQͷϝλσʔλΛຖ࣌όονͰE2E؂ࢹ

    https://speakerdeck.com/yuzutas0/20190719


    ɹߋ৽؂ࢹ

    View Slide

  54. ೔࣍όονͳΒ “check_date" ϚΠφε “updated_at” >= 1 ͕஗Ԇର৅


    ɹ஗Ԇঢ়گՄࢹԽ

    View Slide

  55. MySQL Read ReplicaͱBigQueryͷ݅਺Λൺֱ

    ※୲౰Ҋ݅ͩͱෛՙܰݮ؍఺ͰʮϝλςʔϒϧΛࢀরͯ͠99.5%ҰகΛ໨ࢦ͢ʯͳͲͷ

    Τϯϋϯε։ൃΛ͍ͯ͠Δ͕ϕʔεͱͳΔίϯηϓτ͸্هαϯϓϧίʔυ


    ɹ݅਺Ұக؂ࢹ

    # BQ
    query = f"""
    SELECT COUNT(*) AS row_count FROM `xxxxx.xxxxx.xxxxx` WHERE CAST(created AS DATE) = DATE(‘2019-01-01')
    """
    result_bq = pd.read_gbq(query, project_id=‘xxxxx', dialect='standard')
    # MySQL
    con = mysql.connector.connect(host = ‘x.x.x.x’, port = xxxxx, user = ‘xxxxx’, password = 'xxxxx', database = 'xxxxx')
    read_sql = f"""

    SELECT COUNT(*) AS row_count FROM `xxxxx` WHERE created BETWEEN '2019-01-01 00:00:00' AND '2019-01-01 23:59:59'
    """
    result_mysql = psql.read_sql(read_sql, con)
    result_bq.equals(result_mysql)


    View Slide

  56. ࢀߟʰσʔλϚωδϝϯτ஌ࣝମܥΨΠυʱ


    ɹσʔλ඼࣭Λ୲อ͢Δ
    ඼࣭
    ςετίʔυ
    ߋ৽؂ࢹ ஗Ԇঢ়گՄࢹԽ ݅਺Ұக؂ࢹ
    σʔλϨΠΫ ੜ੒ςʔϒϧ
    ҆શੑ

    ʢϚΠφεˠʣ
    ਖ਼֬ੑ
    ׬શੑ
    Ұ؏ੑ
    ੔߹ੑ
    ଥ౰ੑ
    ద࣌ੑ
    Ұҙੑ
    ༗ޮੑ
    རศੑ

    ʢˠϓϥεʣ

    View Slide

  57. ද໘తʹπʔϧΛ࡞੒ɾಋೖ͢ΔͷͰ͸ͳ͘

    ITαʔϏεϚωδϝϯτͷ࿮૊ΈΛݱ৔՝୊ʹ౰ͯ͸ΊΔ
    https://speakerdeck.com/yuzutas0/20180402


    ɹαʔϏεϨϕϧ

    View Slide



  58. ɹSLA
    σʔλͷ༻్ɾར༻ऀ͝ͱʹظ଴͞Ε͍ͯΔαʔϏεϨϕϧΛՄࢹԽ
    ɾ৴པੑ͸ʮ࣮ࡍʹγεςϜΛ࢖͏ਓɾ໨తɾ࣌ؒʯΛલఏʹ͢Δ

    ɾͳͷͰαʔϏεϨϕϧ͸ϢʔεέʔεۦಈͰઃܭ͢Δ͜ͱʹͳΔ

    ɾ୭΋࢖Θͳ͍ػೳ΍࣌ؒଳͰγεςϜՔಇ཰͕Ͱ͋ͬͯ΋࢓ํͳ͍
    ྫ ༻్ ໿ଋ૬ख ࿈བྷઌɾप஌ઌ ར༻σʔλ ໿ଋࣄ߲ʢ4-"ʣ ҧ൓࣌ͷӨڹൣғ
    ೔࣍Ϩϙʔτ σΟϨΫλʔ
    4MBDL

    NPOJUPSJOH
    #JH2VFSZͷ

    ച্ςʔϒϧ
    ຖӦۀ೔ͷޕલY࣌·Ͱʹ

    ܽଛͳ͘લ೔ͷച্͕

    Ϩϙʔτ͞ΕΔ͜ͱ
    ച্ঢ়گʹԠͨ͡

    ࢪࡦ͕ଧͯͳ͘ͳΔ

    ʢػձଛࣦʣ
    ʜ ʜ ʜ ʜ ʜ ʜ
    ʜ ʜ ʜ ʜ ʜ ʜ
    ʜ ʜ ʜ ʜ ʜ ʜ ʜ
    ࣮෺ˠ

    View Slide



  59. ɹOLA
    αʔϏεϨϕϧʹର͢ΔڴҖʢʹΠϯγσϯτʣൃੜ࣌ͷΦϖϨʔγϣϯ
    ରԠεϐʔυ
    ิঈɾ୅ସखஈɹɹɹɹɹɹϫʔΫΞϥ΢ϯυʢճආࡦʣɹɹɹɹɹɹɹϨϙʔςΟϯάˍه࿥

    View Slide



  60. ɹSLAˍOLAΛվળ͢Δᶃ
    ຖεϓϦϯτऴྃ࣌ͷ;Γ͔͑ΓͰɺ࣮ଶʢ"T*Tʣͱظ଴஋ʢ5P#FʣΛൺֱ͢Δ
    What
    ԿΛ͢Δ͔
    εϓϦϯτ

    ϨϏϡʔ
    σΠϦʔ

    εΫϥϜ
    How
    Ͳ͏΍ͬͯ͢Δ͔
    εϓϦϯτ

    ϓϥϯχϯά
    Ϩτϩ

    εϖΫςΟϒ

    View Slide



  61. ɹSLAˍOLAΛվળ͢Δᶄ
    վળΞΫγϣϯ

    ɾ໨ඪΛຬͨͤΔΑ͏ʹվળ͢Δɿج൫γεςϜͷվमҊ݅Λىථ

    ɾ໨ඪࣗମΛमਖ਼͢Δɿա৒ͳΒ໨ඪΛԼํमਖ਼ɺ࢖༻ఀࢭ#*πʔϧ͸ΫϦʔχϯάɺ؍఺࿙Ε͸௥Ճ
    ˣ࣮෺ʢϨτϩεϖΫςΟϒͷΞδΣϯμʣ

    View Slide

  62. ᶆར༻ऀ͕σʔλΛյͯ͠͠·͏
    ᶇσʔλͷߋ৽͕஗͍
    ᶈσʔλ͕ະߋ৽ˍܽམͨ͠··์ஔ


    ɹ૬ஊᶆʙᶈ

    View Slide

  63. σʔλΛૄ௨͢ΔՕॴʹ͓͍ͯ

    ROIΛ࠷େԽ͢ΔͨΊͷҙࢥܾఆ͕Ͱ͖͍ͯΔ͔ʁ

    ΞφϦετͱͯ͠࠷ߴͷৼΔ෣͍͕Ͱ͖͍ͯΔ͔ʁ


    ɹσʔλۦಈ for Process

    View Slide

  64. 1. ͸͡Ίʹ
    2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ

    3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ

    4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ

    5. Guide to Data Warehouse & Mart Designʢࣗ࿦ฤʣ

    6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ
    7. ͜ͷઌੜ͖ͷ͜Δʹ͸


    ɹΞδΣϯμ

    View Slide

  65. ෆ੔߹σʔλΛͲ͜Ͱٵऩ͢Δ͔


    ɹΑ͋͘Δ૬ஊᶉ

    View Slide

  66. σʔλϨΠΫ → σʔλ΢ΣΞϋ΢ε ͷETLͰΫϨϯδϯά


    ɹੈͰଟ͍ճ౴
    ʰσʔλϚωδϝϯτ஌ࣝମܥΨΠυʱΑΓҾ༻

    View Slide

  67. ΞφϦετʮ͜ͷσʔλ͸ෆ੔߹ͩʂʯ

    ΞφϦετʮΫϨϯδϯάͯ͠෼ੳ͠Α͏ʯ
    ↓

    σʔλ෼ੳ͸ແࣄʹ׬ྃ

    ཌ݄

    DBͷσʔλෆ੔߹ʹΑΔ঎༻όονো֐


    ɹຊ౰ʹ͋ͬͨා͍࿩

    View Slide



  68. ɹσʔλʹ͸ੜ੒ݩ͕͋Δ
    ΞϓϦ
    Ϣʔβʔ %# #2
    ͜͜ʹෆ੔߹͕͋ΔͳΒ
    ঎༻؀ڥ
    Ͳ͔͜Ͱ໰୊͕ى͖͍ͯΔ

    ʢՄೳੑ͕͋Δʣ
    ͚ͩ͜͜Λ௚ͯ͠΋ຊ຤స౗

    ͦͷҙࢥܾఆ͸ຊ౰ʹ,(*ʹج͍͍ͮͯΔ͔

    σʔλۦಈͩͱݴ͑Δͷ͔
    ఏڙՁ஋Λ࠷େԽ͢ΔͨΊʹ

    σʔλ෼ੳΛ࢝Ίͨ͸ͣ
    ྑ͍ϓϩμΫτΛ࡞ΔͨΊʹ

    σʔλ෼ੳΛ࢝Ίͨ͸ͣ

    View Slide

  69. ʮ૬ஊ͍ͤͯͩ͘͞͞ʯʮDBͷσʔλʹෆ੔߹͕͋ΔΑ͏Ͱ͢ʯͷҰݴͰɺ

    1ճͷύον౰ͯͰɺ1ߦͷίʔυվमͰɺ1ਓͷ͓٬༷ͷਓੜ͕180౓มΘΔ͔΋͠Εͳ͍

    ͦΕ͕ITαʔϏεʹܞΘΔ୉ޣຯʢͷ1ͭʣͳͷͰɺੵۃతʹຯΘ͍͖ͬͯ·͠ΐ͏


    ɹސ٬ΛݟΑ͏ɺ੡඼Λຏ͜͏
    ΞϓϦ
    Ϣʔβʔ %# #2
    ঎༻؀ڥ

    View Slide

  70. ϓϩμΫτνʔϜ͕ͦ͜ओਓެύʔςΟʔ΍ͶΜʂ
    ΞφϦετ͸ఢύʔςΟʔͷ2൪໨ʹڧ͍΍ͭΛͬͦ͜ΓཪͰ౗͢ϙδγϣϯ΍ͶΜʂ
    ΜͰઓ͍ͷߦํΛࠨӈ͢ΔεΠονΛԡ͠ͱ͘ͶΜʂͦΕ͕͋Δ͔Β࠷ޙͷ࠷ޙͰओਓެ͸ٯస͢ΔͶΜʂ
    ʮ;ͬɺ͔͜͜Βઌ͸͓લୡ࣍ୈͩʯͬͯݴͬͯҋͷதʹফ͍͑ͯ͘ϙδγϣϯ΍ͶΜʂ

    ෢ಓձฤͩͬͨΒ෭কʹͳΔ΍ͭ΍ͶΜʂখઆ൛ͰΑ͏΍͘ϑΟʔνϟʔ͞Εͯաڈ͕໌͔͞ΕΔͶΜʂ

    ຊࢽͩͱʮ΍Ε΍Εʯͱ͔ݴ͍ͳ͕ΒओਓެύʔςΟʔʹύε͢Δͱ͜Ζ·Ͱ͕࢓ࣄ΍ͶΜʂ

    ʮ͓લ΄Ͳͷ࠽Λ࣋ͭऀ͕ͳͥ͋ΜͳΨΩͲ΋ʹྗΛି͢ͷͩʂʁʯͬͯݴΘΕͨΒ

    ʮ·͓ͬͨ͘ΊͰ͍ͨ΍ͭΒͩΑͳʯ͔Β࢝·ͬͯɺҒͦ͏ʹʢͰ΋ͪΐͬͱخͦ͠͏ʹʣओਓެͨͪͷັྗΛղઆ͢ΔΜ΍ʂ

    ͦ͏͍͏ϙδγϣϯͳͷʹ͞ʂ͏͔ͬΓαϙʔτ๨ΕͯͨΒ͞ʂ͍ͩ͞͡ΌΜʂ
    ͔ͩΒϓϩμΫτνʔϜʹ΋ͬͱΤεΧϨ͍ͯ͜͠ʂͶʂ

    େৎ෉ʂ͖ͬͱ܅ͷؾ࣋ͪ͸఻ΘΔ͞ʂ෼͔Δ΍ͭ͸෼͔ͬͯ͘ΕΔ͞ʂ

    ͓΋͍͖ͬΓڳΛுͬͯԿճͩͬͯශ๡͘͡ΛҾ͍͍ͯ͜͏ͥʂपΓͷ୭ΑΓ΋ஏΛ͔͍͍ͯ͜͏ͥʂ

    ͜ΜͳεϥΠυΛެ։͢ΔΞϗ͕͍ΔΜͩʂσʔλෆ੔߹Λ૬ஊ͢ΔͷʹڪΕΔඞཁͳΜͯͳ͍ͬͯʂ
    ؾ෇͍ͪΌͬͨͳΒߦಈ͢Δ͔͠ͳ͍ͩΖʂଞͷ୭Ͱ΋ͳ͍ʂ܅ͳΜͩʂ܅͕܅ͷपΓͷੈքΛม͑ΔΜͩʂ


    ɹগ೥ອըͰྫ͑Δͱ
    Inspired byʰԶ͔ɺԶҎ֎͔ɻϩʔϥϯυͱ͍͏ੜ͖ํʱ

    View Slide

  71. ٖࣅతʹཤྺσʔλΛੜ੒͍ͨ͠


    ɹΑ͋͘Δ૬ஊᶊ

    View Slide

  72. ೔࣍όονʹΑΔεφοϓγϣοτ


    ɹ؆୯ʹग़དྷͯ͠·͏ํ๏
    VQEBUF͞ΕΔΧϥϜ
    ྫQBZNFOU@TUBUVT

    ୯ମͰ͸ਪҠΛ෼ੳͰ͖ͳ͍
    ਖ਼֬Ͱ͸ͳ͍͕
    ೔୯Ґͷࠩ෼ɾਪҠ͸

    ݟΔ͜ͱ͕Ͱ͖Δ

    View Slide

  73. ߋ৽ස౓Λͻͨ͢Β޲্͢Δʁ


    ɹਫ਼౓͕ཉ͘͠ͳΔ

    View Slide

  74. ཤྺΛ࣋ͭσʔλͷઃܭ
    https://speakerdeck.com/kawasima/lu-li-wochi-tudetafalseshe-ji

    ΠϛϡʔλϒϧσʔλϞσϧ(ೖ໳ฤ)
    https://www.slideshare.net/kawasima/ss-40471672


    ɹDBͰཤྺΛ࣋ͭͷ͕ࠜຊղܾ
    ΞϓϦ
    Ϣʔβʔ %# #2

    View Slide

  75. ߹ΘͤͯಡΈ͍ͨɿʮΞΠσΞΛग़͢͜ͱ͕اըͩͱࢥͬͯΔౕ͸100ສճࢮΜͰ͍͍ʯ

    http://dochikushow.blog3.fc2.com/blog-entry-3003.html


    ɹཤྺ΋ؚΊͯϓϩμΫτϚωδϝϯτ
    ๏ྩ९कɾফඅऀอޢ ໰͍߹ΘͤରԠ
    ߪೖ௚ޙʹ঎඼ͷઆ໌จ͕

    Ξοϓσʔτ͞ΕͨΒʁ

    Ԡื௚ޙʹٻਓͷใुཝ͕

    Ξοϓσʔτ͞ΕͨΒʁ


    ʮݴͬͨʯʮݴΘͳ͍ʯͷ

    ໰୊͕ى͖͏ΔͷͰ

    ه࿥อ࣋Λٛ຿෇͚Δ

    ๏཯ɾΨΠυϥΠϯ͸ଟ͍
    ཤྺΛௐ্ࠪͨ͠Ͱ

    ճ౴Λฦ͢ػձ͕ଟ͍

    ΧελϚʔαϙʔτπʔϧͷ

    ػೳཁ݅ͱͯ͠

    ཤྺอ͕࣋ٻΊΒΕΔ


    ໰͍߹ΘͤରԠ΋ؚΊͯ

    ϓϩμΫτͷར༻ମݧ

    View Slide

  76. ೖͬͯ͘Δϩά͕ྑ͘ͳ͍


    ɹΑ͋͘Δ૬ஊᶋ

    View Slide



  77. ɹप஌͠ͳ͍ͱݕ஌Ͱ͖ͳ͍

    View Slide



  78. ɹ։ൃϓϩηεͷաఔͰ୲อ͢Δ

    View Slide

  79. Ҋ݅ͷ໨త → ର৅ࢦඪ → ܭଌํ๏ → ϩάཁ݅ ͸࠷ॳʹ཈͑Δ


    ɹཁ݅ఆٛϑΥʔϚοτ
    ɹ࠾൪@Ҋ໊݅@ཁ݅ϝϞ
    ɹ໨త
    ɹղফ͍ͨ͠ෆ

    ɹଧͪख

    ɹޮՌݟཱͯ

    ɹϏδωεཁٻɹ

    ɹػೳ֓ཁ

    ɹޮՌܭଌࢦඪ
    ɹը໘Πϝʔδ
    ɹӨڹൣғ
    ɹγεςϜཁ݅ৄࡉ
    ɹػೳཁ݅Ұཡ

    ɹޮՌܭଌϩάཁ݅
    ɹର৅ը໘Ұཡ
    ɹγʔέϯεɾσʔλϑϩʔ

    ɹཹҙࣄ߲
    ɹΧελϚʔαϙʔτ΁ͷӨڹʹ͍ͭͯ
    ɹ๏຿ɾফඅऀอޢɾݸਓ৘ใอޢʹ͍ͭͯ
    ɹΠϯϑϥɾΩϟύγςΟʹ͍ͭͯ
    ɹઃܭ؍఺ʹ͍ͭͯ
    ɹςετ؍఺ʹ͍ͭͯ
    ɹϦϦʔε࡞ۀ؍఺ʹ͍ͭͯ
    ɹϦϦʔεޙ࡞ۀ؍఺ʹ͍ͭͯ

    View Slide

  80. ࢼݧ؀ڥͷςετσʔλͰϞχλϦϯάΛ։࢝͠Α͏
    μϛʔσʔλΛμογϡϘʔυʹදࣔͯ͠νʔϜશһͰݟΑ͏
    ࣾ಺ϦϦʔεதʢࢼݧ޻ఔʣͰຊ൪ϦϦʔεޙʢӡ༻޻ఔʣͱಉ͡Α͏ʹৼΔ෣͓͏
    ϦϦʔεޙʹॳΊͯ֬ೝ͢ΔΑ͏Ͱ͸ʮϩά͕ྑ͘ͳ͍ʯͷ͸౰ͨΓલ
    લ޻ఔͰ඼࣭Λ୲อ͠Α͏ʢʰϦʔϯ։ൃͷຊ࣭ʱΑΓʣ


    ɹϩάɾϞχλϦϯάͷडೖࢼݧ

    View Slide



  81. ɹ֤ϓϩμΫτνʔϜ಺ͷࣗ૸Λଅ͢
    ੡଄
    ઃܭ ࢼݧ ϦϦʔε
    ޮՌ

    ଌఆ
    ཁ݅

    ఆٛ
    ཁٻ

    ੔ཧ
    ੡଄
    ઃܭ ࢼݧ ϦϦʔε
    ޮՌ

    ଌఆ
    ཁ݅

    ఆٛ
    ཁٻ

    ੔ཧ
    طʹ΍͍ͬͯΔ
    ෼ੳཁٻ ϩάཁ݅ ܭଌ
    ΞΠσΞ
    ΞφϦετͰ͸ͳ͘։ൃνʔϜ͕ࣗલͰ΍Δ͜ͱʹҙຯ͕͋Δ

    εΫϥϜϚελʔͷσʔλ׆༻൛ͱͯ͠ৼΔ෣͏

    σʔλ࢓༷ʹৄ͍͠ʢগͳ͘ͱ΋ௐࠪ͢ΔεΩϧ͸΋͍ͬͯΔʣΤϯδχΞ͕

    ୲౰ྖҬΛ޿͛Δ͜ͱͰલޙ޻ఔͷϦʔυλΠϜΛ୹ॖ

    View Slide



  82. ɹҰ൪ͷ౰ࣄऀ͕PDSαΠΫϧΛճ͢
    ɾSWΤϯδχΞ͕P/L੹೚Λ࣋ͬͯҊ݅ىҊ͔ΒޮՌଌఆ·ͰΛ୲͏ͷ͕ཧ૝

    ɾࣗ෼ͰϞχλϦϯά͢Ε͹ɺϩάͷෆඋΛ࣮ײ͢ΔͷͰɺ͔࣍Βվળ͞ΕΔ
    ɾ֎͔ΒҰํతʹԡ͠෇͚Δ͚ͩͩͱɺ͍ͭ·Ͱ΋ޓ͍ʹ෼͔Γ͋͑ͳ͍··

    ɾݡऀ͸ྺ࢙͔Βֶͼɺ۪ऀ͸ܦݧ͔ΒֶͿ → զʑ͸۪ऀ → ·ͣܦݧ͠Α͏
    https://speakerdeck.com/rtechkouhou/enziniadakedeqi-hua-kai-fa-fen-xi-subetewosui-xing-surutimuwoli-tishang-getahua

    View Slide


  83. ·ͣछΛ·͖ɺ࣍ʹͦΕΛמΓऔΔͷ͕మଇͰ͋Δʢʰ෋Λஙٕ͘ज़ʱΑΓʣ


    ɹ෺ࣄʹ͸ॱং͕͋Δ
    ΤϯδχΞνʔϜʹ͓͚Δ

    DevˍOps҆ఆԽʢʹ૊৫ͷITԽʣ͕ઌ
    ͦͷ͋ͱʹγεςϜ͔Βੜ੒͞ΕΔ

    σʔλΛ׆༻ͯ͠ࣄۀΛ͞Βʹ৳͹͢
    https://speakerdeck.com/yuzutas0/20170909
    https://speakerdeck.com/yuzutas0/20171013

    View Slide

  84. ʮࣗ෼͕ͨͪ෼ੳ͠΍͍͢ʯ͚ͩͷখखઌͷ޻෉ʹಀ͛ͯ΋ɺ८Γ८ͬͯࣗ෼ͷटΛకΊΔ͚ͩɻ

    ϓϩμΫτΛྑ͘͢ΔͨΊʹσʔλΛݟ͍ͯΔΜͩΑͳ͊Զͨͪ͸ʂ
    উखʹઢΛҾ͘ͷ͸΋͏΍ΊΑ͏ɻࣗ෼Ͱࣗ෼ͷ৺Λด͡ࠐΊ͍ͯΔ͜ͱʹؾ෇͜͏ɻ

    ͳͥ෼ੳ͕ਐ·ͳ͍ʁϩά͕͓͔͍͠ʁͯΊ͕͐ϓϧϦΫΛग़ͤʂ
    ͳͥͰ͖ͳ͍ʁؾܰʹϓϧϦΫΛग़ͤΔ૊৫͡Όͳ͍ʁͯΊ͕͐૊৫Λཱͯ௚ͤʂ

    ͳͥͰ͖ͳ͍ʁ૊৫Λཱͯ௚͢ݖݶ͕ͳ͍ʁݖݶ͕͋Δ΍ͭΛཪ͔ΒૢΕʂ
    ͳͥͰ͖ͳ͍ʁૢΔํ๏͕෼͔Βͳ͍ʁʰਓΛಈ͔͢ʱʰ෦ԼྗʱΛಡΊʂ

    ͳͥͰ͖ͳ͍ʁͦΜͳʹ༏ल͡Όͳ͍͔Βʁ͡Ό͋༏लͳͻͱʹॿ͚ͯ΋Β͓͏ʂ
    Ծʹ੹೚෼ղ఺Λଚॏ͢Δେਓͳੈքઢʹ͍Δͱͯ͠ɺϓϩμΫτΛվળͰ͖Δ͔΋͠Εͳ͍ΞΠσΞ͕͋ΔͳΒɺ

    ͤΊͯ୲౰ऀʹ૬ஊ͢Δ͘Β͍͸΍ͬͯΈͯ͸͍͔͕Ͱ͠ΐ͏͔ɻ

    τΠϨͰ΋λόί෦԰Ͱ΋څ౬ࣨͰ΋Կ͔͠Βνϟϯε͸͋Δ͸ͣͩΑͶϋϜଠ࿠ʢʰ૊৫ύλʔϯʱΑΓʣ

    ෼͔Δɻ෼͔ΔΑɻͦ͜·Ͱ΍Γͨ͘ͳ͍ΑͶɻ໘౗ͩΑͶɻ

    Ͱ΋͞ɺจ۟Λݴ͍ଓ͚Δ͘Β͍ͩͬͨΒɺখͯ͘͞΋ྑ͍ͷͰҰา͚ͩߦಈͯ͠Έ·ͤΜ͔ɻ
    ͨͱ͑ߑਫΛࢭΊΔ͜ͱ͕Ͱ͖ͳͯ͘΋ɺਫʹྲྀ͞Εͳ͍Ͱཱͪਚ͘͢Ұຊͷ໦Ͱ͋Γ͍ͨ͡Όͳ͍Ͱ͔͢ɻ

    શͯ͸͔ͦ͜ΒͰ͢Αɻ0.01cmͷް͞ͷࢴͩͬͯ25ճંΕ͹෋࢜ࢁʹͩͬͯಧ͘ΜͩͥοʂʢʰຐԦʱΑΓʣ


    ɹ໌೔͸΋ͬͱ͍͍೔ʹͳΔΑͶʂ

    View Slide

  85. ᶉෆ੔߹σʔλΛͲ͜Ͱٵऩ͢Δ͔
    ᶊٖࣅతʹཤྺσʔλΛੜ੒͍ͨ͠
    ᶋೖͬͯ͘Δϩά͕ྑ͘ͳ͍


    ɹ૬ஊᶉʙᶋ

    View Slide

  86. σʔλΛੜ੒͢ΔՕॴʹ͓͍ͯ

    ROIΛ࠷େԽ͢ΔͨΊͷҙࢥܾఆ͕Ͱ͖͍ͯΔ͔ʁ

    ΞφϦετͱͯ͠࠷ߴͷৼΔ෣͍͕Ͱ͖͍ͯΔ͔ʁ


    ɹσʔλۦಈ for Input

    View Slide

  87. ʮԿͷͨΊʹ͜Ε΍ͬͯΔΜ͚ͩͬʁʯʢΰʔϧʣ

    ʮࠓͰ͖Δ͜ͱͬͯͳΜ͚ͩͬʁʯʢΞΫγϣϯʣ
    ͜ͷ2ͭͷ࣭໰͔Β໨Λഎ͚ͨॠؒʹ
    Ξϯνύλʔϯ΁ͱؕΔ
    ͜ͷ2ͭͷ࣭໰ʹ౴͑ଓ͚͍ͯΔͳΒ

    ඞͣಓ͸։͚Δ


    ɹࠜڌͷ͋ΔҙࢥܾఆΛ͠Α͏

    View Slide

  88. ʲҊ݅ΛϦετʹͯ͠༏ઌॱҐΛ෇͚Δʳ → ʲఆظతʹৼΓฦͬͯيಓमਖ਼͢Δʳ

    ஍ຯͰ౰ͨΓલͳ͜ͱΛ೔ʑੵΈॏͶͨઌʹͦ͜উར͕͋Δ
    Ώ͘Ώ͘͸ηϨϞχʔˍΞΠςϜ͕੔උ͞Ε͍ͯ͘͸ͣ


    ɹ۩ମతʹԿΛͲ͏΍Ε͹͍͍ͷ͔
    https://speakerdeck.com/yuzutas0/20180727

    View Slide

  89. ࠓΛੜ͖Α͏


    ɹ݁࿦

    View Slide

  90. 1. ͸͡Ίʹ
    2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ

    3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ

    4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ

    5. Guide to Data Warehouse & Mart Designʢࣗ࿦ฤʣ

    6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ
    7. ͜ͷઌੜ͖ͷ͜Δʹ͸


    ɹΞδΣϯμ

    View Slide



  91. ɹ͓୊ʢ࠶ܝʣ

    View Slide



  92. ɹઃܭ࿦ʢ࠶ܝʣ
    .BSU
    ར༻ऀ޲͚*'
    㲈σʔλϚʔτ
    8BSFIPVTF
    υϝΠϯ஌ࣝΛද͢σʔλ
    㲈σʔλ΢ΣΞϋ΢ε
    4PVSDF
    ݩσʔλͷίϐʔ
    㲈σʔλϨΠΫ
    σʔλύΠϓϥΠϯʹ͓͍ͯػց͕ॲཧ͢ΔྲྀΕ
    ࣄۀ΍γεςϜͷ

    શσʔλΛίϐʔ͢Δ
    μογϡϘʔυ΍

    ϨϙʔτͱରʹͳΔ
    ։ൃϓϩηεʹ͓͍ͯਓ͕ؒઃܭ͢ΔྲྀΕ
    ूܭɾ෼ੳύλʔϯΛ੔ཧ

    㲈8*5)۟ͷසग़σʔλ
    ᶃ ᶄ ᶅ
    ᶃ ᶃ

    View Slide



  93. ɹઃܭ࿦ʢղઆهࣄʣ
    http://yuzutas0.hatenablog.com/entry/2018/12/02/180000
    https://speakerdeck.com/yuzutas0/20190213

    View Slide

  94. 1ɿਓؒ͸࠷ॳ͔Β׬ᘳͳγεςϜΛઃܭͰ͖ͳ͍
    2ɿϏδωεͷมԽʹ߹ΘͤͯγεςϜͷઃܭ͸มΘΔ


    ɹઃܭΛߟ͑ΔͨΊͷ༩݅

    View Slide

  95. ॳظ࣌఺ͷઃܭΛ׬ᘳʹ͢ΔΑΓ΋

    ΠςϨʔγϣϯΛ܁Γฦ͢͜ͱͰ
    “σʔλϞσϧΛվળ͠΍͍͢؀ڥ” ͷߏங͕伴
    ʢਐԽతσʔλϞσϦϯάʣ


    ɹઃܭʹ͓͚ΔΩʔίϯηϓτ

    View Slide

  96. ʮσʔλϨΠΫߏங௚ޙʯͷ૊৫ʹ͓͍ͯ͸
    σʔλϞσϧΛߟ͑Δલʹ΍Δ͜ͱ͕ࢁఔ͋Δ


    ɹҙݟද໌

    View Slide



  97. ɹ࠷ॳ͸σʔλϨΠΫ͚ͩͰ͍͍
    http://yuzutas0.hatenablog.com/entry/2018/12/08/235900

    View Slide

  98. 1. ΞφϦετ͕TableauͳͲͷBIπʔϧͰ࣮ݧతʹσʔλϚʔτΛ࡞Δ

    ʢμογϡϘʔυΛ࡞ΔͨΊͷσʔλूܭϩδοΫʣ


    2. ܧଓར༻͢Δ৔߹͸SQL on σʔλύΠϓϥΠϯʹϩδοΫΛऔΓࠐΉ

    ɹʢ࢖Θͳ͚Ε͹আ٫ɾΫϦʔχϯά͢Δʣ


    ɹ࠷খͷσʔλϚʔτ࡞੒खॱ
    .BSU
    ར༻ऀ޲͚*'
    㲈σʔλϚʔτ
    https://speakerdeck.com/yuzutas0/20190213

    View Slide

  99. ఏڙऀ͔͠࢖Θͳ͍σʔληοτʹͳΓ͕ͪ


    ɹ൚༻తʢࣗশʣ͸ආ͚Α͏
    https://speakerdeck.com/yuzutas0/20190213

    View Slide

  100. ܦӦͷΞδϦςΟΛࢧ͑ΔDevOpsͱ૊৫
    https://www.slideshare.net/recruitcojp/devops-51085988/52


    ɹ࠷ॳ͸൚༻తͰͳ͍͍ͯ͘

    View Slide

  101. ɾԿΛڞ௨Խ͢Δ͔ʁ
    ɾԿΛ෼͚Δ͔ʁ


    ɹιϑτ΢ΣΞ։ൃͷ໰͍
    https://github.com/yuzutas0/my100tales

    View Slide



  102. ɹઃܭख๏ʹ·ͭΘΔ਺ଟͷࢹ఺
    https://speakerdeck.com/yuzutas0/20170916

    View Slide

  103. γεςϜվળͷಛੑ
    ʢσʔλج൫ͳͲͷITγεςϜ΋ɺձࣾ૊৫ͳͲͷࣾձγεςϜ΋ɺಉ͡ߏ଄ಛੑΛ࣋ͭʣ


    ɹཐટͷΑ͏ʹ܁Γฦ͢͜ͱͰ੒௕͢Δ
    ݸผԽW
    ʢ෼཭ʣ
    ڞ௨ԽW
    ʢ༥߹ʣ
    ݸผԽW
    ʢ෼཭ʣ
    ڞ௨ԽW
    ʢ༥߹ʣ
    ݸผԽW
    ʢ෼཭ʣ
    ڞ௨ԽW
    ʢ༥߹ʣ
    http://yuzutas0.hatenablog.com/entry/2018/07/12/083000

    View Slide

  104. যΒͳͯ͘΋ڞ௨Խͨ͘͠ͳΔ͕࣌དྷΔ

    ͦͷ࣌ʹڞ௨Խ͢Ε͹͍͍ͱࢥ͍·͢ʂ


    ීஈ͔Β໨ͷલͷΠγϡʔͱਅ伨ʹ޲͖߹͍ͬͯΕ͹
    ʮ͜͏͍͏՝୊͕͋Δ͔Βʯʢཁٻʣ
    ʮ͜͏͍͏ڞ௨Խ͕͍ͨ͠ʯʢཁ݅ʣ

    ͱ͍࣮ͬͨײͷ൐͏ݴ༿͕ग़ͯ͘Δ͸ͣ
    ඞཁͳͱ͖ʹػձ͸ࣗવͱ८ͬͯ͘Δ
    ͔ͩΒ৺ͷ··ʹਐΊ͹͍͍ͱࢥ͍·͢ʂ


    ɹ͍ͣΕ࣌͸དྷΔ
    http://yuzutas0.hatenablog.com/entry/2018/07/08/083000

    View Slide



  105. ɹ͚ͩ͜͜Λ཈͑Δ
    ҆શ໘
    ࣦഊͯ͠΋΍Γ௚ͤΔΑ͏ʹ

    ੔ܗલσʔλͷίϐʔΛ஝ੵ͢Δ
    σʔλϨΠΫ
    ޮ཰໘
    ϦϑΝΫλϦϯά͠΍͍͢Α͏ʹ

    ಉҰͷٕज़ཁૉͰ؅ཧ͢Δ
    SQL on Worflow Engine
    ܧଓ໘
    ʮվળʯΛश׳ԽͰ͖ΔΑ͏ʹ

    σʔλར༻ऀ͕ϓϩάϥϛϯάͱϦʔϯͷݪଇΛ

    ֶ΂ΔΑ͏ࢧԉ͢Δ
    σʔλͷຽओԽ

    ʢΞδϟΠϧίʔνεΫϥϜϚελʔʣ

    View Slide

  106. ͜ͷਤΛॻ্͍ͨͰձ࿩Ͱ͖͍ͯΔ͔ʁ


    ɹ͋͘·Ͱଧͪखͷ1ͭ
    *TTVF
    ଧͪखᶃ
    YYYYY
    ଧͪखᶄ
    YYYYY
    ଧͪखᶅ
    தؒςʔϒϧ
    ޮՌ ίετ ϦεΫ σϦόϦ
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕
    ̋˚✕

    View Slide



  107. ɹ෼ੳ༻σʔλϞσϦϯάͷROIಛੑ
    !
    ෼ੳۀ຿ͷ2$%4ʹϙδςΟϒӨڹ

    ࢖͍ճ͠ͰޮՌ͕ੵΈॏͳΔ

    ៉ྷͳதؒςʔϒϧ͕͋ͬͯ΋

    ച্͕૿͑ΔΘ͚Ͱ͸ͳ͍
    Ұׅ౤ࢿͰϩʔυϚοϓΛඳ͘ͷͰ͸ͳ͘ʢճऩΛ࣮ײͰ͖ͳ͍ʣ

    ඞཁʹͳͬͨ࣌ʹͦͷ৔Ͱϓϧϕʔεʹ࡞੒ɾڞ௨Խɾ෼཭͢Δ

    View Slide

  108. ͦͷ࣌ʑͰ໨ͷલͷΠγϡʔΛ੔ཧͰ͖͍ͯͨΒ
    ͋Δ΂͖σʔλϞσϧʢͷԾઆʣ͸ࣗવͱݟ͑Δ͸ͣ
    ܧଓతվળͷΧϧνϟʔɾϓϩηε͕ఆணͰ͖͍ͯͨΒ

    Ծʹࣦഊ͕͋ͬͯ΋ωΫετΞΫγϣϯʹ׆͔ͤΔ͸ͣ
    ΋͠ෆ҆Λ๊͍͑ͯΔͳΒɺਅҼ͸ͦ͜ʹ͋ΔͷͰ͸ʁ

    ʢͳͷͰ͜ΕҎ্ͷٞ࿦Λ͢Δͭ΋Γ͸ͳ͍ʣ

    ʢͳͷͰ։ൃͰ͸ͳ͘ίϯαϧͰҊ݅Λ੥͚͍ͯΔʣ


    ɹ୭ͷͨΊͷઃܭʁ

    View Slide

  109. ΞφϦετ͕σʔλϞσϦϯάʹؔ৺Λ࣋ͭͱ͖

    ΠγϡʔۦಈͰͳ͍͜ͱ͕ଟ͔ͬͨʢൃදऀݸਓͷҙݟͰ͢ʣ


    σʔλϞσϦϯάΛ੾Γޱʹͯ͠σΟεΧογϣϯ͢Δͱ

    खஈͱ໨త͕ೖΕସΘΓ΍͍͢Α͏ʹࢥ͑ΔʢൃදऀݸਓͷҙݟͰ͢ʣ

    ࣗݾ౤ࢿͷֶशͳΒ͍͘ΒͰ΋পʹਁ͔Ε͹ྑ͍͕

    ΞφϦετ͕ۀ຿ͰϑΥʔΧε͢Δͷ͸ඇਪ঑ʢൃදऀݸਓͷҙݟͰ͢ʣ


    ɹσʔλϞσϦϯάͷপ

    View Slide

  110. 1. ͸͡Ίʹ
    2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ

    3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ

    4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ

    5. Guide to Data Warehouse & Mart Designʢࣗ࿦ฤʣ

    6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ
    7. ͜ͷઌੜ͖ͷ͜Δʹ͸


    ɹΞδΣϯμ

    View Slide

  111. Ͳ͏ͯ͠΋σʔλ΢ΣΞϋ΢ε΍
    σʔλϚʔτΛઃܭ͍ͨ͠

    ͱ͍͏ਓ޲͚ʹώϯτΛ͝঺հ

    ʢͪΌΜͱ஌͍ͬͯΔͱศརͳͷ͸ࣄ࣮ʣ

    ʢͦͷՁ஋͕͋Δ͔Βͦ͜਺ଟͷࢿྉ͕ੈʹग़͍ͯΔʣ


    ɹͱ͸͍͑

    View Slide

  112. Bill Inmon ࢯͱ Ralph Kimball ࢯ͕20೥Ҏ্ʹ౉ͬͯٞ࿦

    ʢData Warehouse ͸ Inmon ࢯͷ଄ޠʣ
    https://en.wikipedia.org/wiki/Bill_Inmonɹhttps://en.wikipedia.org/wiki/Ralph_Kimball


    ɹEnterprise Data Warehouse Architecture Design

    View Slide

  113. ʰσʔλϚωδϝϯτ஌ࣝମܥΨΠυʱΑΓҾ༻


    ɹInmon’s CPI & Kimball’s Dimensional Modeling

    View Slide



  114. “Star Schema The Complete Reference” Λ΋ͱʹղઆͨ͠ͱͷ͜ͱ

    ʢ೔ຊޠͰ୳ͨ͠ͱ͜Ζ͜ͷαΠτͷҰ࿈ͷهࣄ͕࠷΋ϑΣΞͰ؆ܿͩͬͨʣ

    http://www.dbstories.com/entry/datawarehouse_architecture_01


    ɹ30෼Ͱ֓ཁ͕෼͔Δهࣄ

    View Slide

  115. ɾReadable & Practical ͳࢿྉ͕ଟ͍

    ɾணख͠΍͍͢ “The Star Schema / Dimensional Modeling”




    ɹThe Kimball Architecture

    View Slide

  116. ɾཚ๫ʹݴ͏ͱʮୈೋਖ਼نܗʯͰʮJoin͠·ͬͯ͘ศརͳςʔϒϧΛ࡞Ζ͏ͥʯͷΨνٞ࿦

    ɾݟΔࢦඪʢϑΝΫτʣʹ੾Δ࣠ʢσΟϝϯγϣϯʣΛ෇͚͍ͯ͘

    ɾJoinͷදݱ͕੕ܕͰඳ͔ΕΔ͜ͱ͕ଟ͍ͷͰ “The Star Schema”


    ʰ10೥ઓ͑Δσʔλ෼ੳೖ໳ SQLΛ෢ثʹσʔλ׆༻࣌୅Λੜ͖ൈ͘ʱΑΓҾ༻


    ɹThe Star Schema, Dimensional Modeling

    View Slide

  117. σʔλ΢ΣΞϋ΢εϞσϦϯάೖ໳ʢμΠδΣετ൛ʣ

    https://www.slideshare.net/snaga/ss-135048443

    σΟϝϯγϣφϧϞσϦϯάͷ͢͢Ί

    https://speakerdeck.com/ojima_h/deimensiyonarumoderingufalsesusume


    DWH: ελʔεΩʔϚΛϕʔεʹ͋ΒͨΊͯߟ͑ͯΈͨσʔλϞσϦϯάͷͭ̕ͷ͜ͱ

    http://crmprogrammer38.hatenablog.com/entry/2017/02/21/155943


    σʔλ΢ΣΞϋ΢εʗBIٕज़Λֶ΅͏ʂ
    https://el.jibun.atmarkit.co.jp/dwbi/2011/05/post-da3f.html


    ɹThe Kimball Architecture / JP

    View Slide

  118. ɾஶऀ͸࣌୅എܠͷࠩҟ΍֤ࣾࣄ৘Λ

    ɹ౿·্͑ͨͰ࣮ફతͳελϯεΛఏࣔ


    ɾൃදऀʢࢲʣ͕ PyCon JP 2017 Ͱ

    ɹຊॻΛҾ༻͔ͯ͠Β2೥


    ɾ͜ͷຊΛಡΊ͹ॻ͍ͯ͋Δ͜ͱΛ

    ɹࢲʹ࣭໰͞ΕΔ͜ͱ͕ଟʑ͋ͬͯ൵͍͠


    ɹ͜ͷ͋ͨΓͷ࿩ɺ͍͍ͩͨॻ͍ͯ͋Δ

    View Slide


  119. ࢐৽ͳΞΠσΞΛࢥ͍͔ͭͳ͍͍ͯ͘
    ແཧʹ஌͍ͬͯΔ৘ใΛ౰ͯ͸Ίͳ͍͍ͯ͘
    ද໘తͳख๏࿦΍ΩʔϫʔυʹৼΓճ͞Εͳ͍͍ͯ͘
    ΞφϦετ͕σʔλ෼ੳͰ஫ҙ͍ͯ͠Δ͜ͱΛ

    ಉ͡Α͏ʹ஫ҙͯ͠ɺ࢓ࣄΛਐΊΕ͹ͦΕͰ͍͍


    ɹطଘͷઃܭख๏΍ࢿྉ͸ࢁఔ͋Δ
    https://www.pexels.com/photo/pile-of-books-in-shallow-focus-photography-264635/

    View Slide

  120. զʑຌਓ๊͕͑Δఔ౓ͷ೰Έ͸

    ʢےͷྑ͍೰ΈͰ͋Ε͹ʣ
    աڈʹڊਓୡ͕ٞ࿦͍ͯ͠Δ͸ͣ



    ɹແ஌ͷ஌ɾυϦϒϯɾϦαʔν
    https://www.pexels.com/photo/parthenon-greece-landmark-951531/

    View Slide

  121. ຊΛಡ΋͏


    ɹ݁࿦

    View Slide

  122. 1. ͸͡Ίʹ
    2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ

    3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ

    4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ

    5. Guide to Data Warehouse & Mart Designʢࣗ࿦ฤʣ

    6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ
    7. ͜ͷઌੜ͖ͷ͜Δʹ͸


    ɹΞδΣϯμ

    View Slide

  123. ੜ͖࢒Δͷ͸͜ͷੈͷʮਅ࣮ʯ͚ͩͩ

    ਅ࣮͔Βग़ͨʮ੣ͷߦಈʯ͸ܾͯ͠໓ͼ͸͠ͳ͍
    ͓ͦͯ͠લͷߦಈ͕ਅ࣮͔Βग़ͨ΋ͷͳͷ͔
    ͦΕͱ΋͏Θͬ໘͚ͩͷअѱ͔Βग़ͨ΋ͷͳͷ͔

    ͦΕ͸͜Ε͔ΒΘ͔Δ
    ʰδϣδϣͷحົͳ๯ݥʱΑΓ


    ɹੜ͖࢒Δͷ͸ʁ

    View Slide

  124. When you have eliminated the impossible,

    whatever remains, however improbable, must be the truth.
    “His Last Bow”

    ෆՄೳΛফڈͯ͠࠷ޙʹ࢒ͬͨ΋ͷ͕

    ೗Կʹحົͳ͜ͱͰ͋ͬͯ΋ͦΕ͕ਅ࣮ͱͳΔ

    ʰγϟʔϩοΫɾϗʔϜζ࠷ޙͷѫࡰʱΑΓ


    ɹਅ࣮ͱ͸ʁ

    View Slide

  125. “σʔλ෼ੳͰɺʮϦιʔεΛ౤͡ͳͯ͘ྑ͍৔ॴʯΛ୳͢”
    https://note.mu/shinya_nagai/n/n4d80f667dff1


    ɹෆՄೳΛফڈ͢Δʹ͸ʁ
    ͜ͷεςοϓΛվળ͢ΔࣄͰɺ

    શମͷίϯόʔδϣϯϨʔτ͕

    վળ͢Δࣄ͸΄΅ͳ͍ͩΖ͏ɻ

    View Slide

  126. ࢹ࠲͕௿͍ूஂ͸ɺ͋͋ͩ͜͏ͩબ୒ࢶΛٞ࿦Ͱ͖Δ͕ɺ

    ͦΕ͸௿͍໨ඪʹࢸΔಓ͸ͨ͘͞Μ͋Δ͔Βʹա͗ͳ͍ɻ

    Ұ൪্ʹߦ͜͏ͱ͢Ε͹બ୒ࢶ͸΄ͱΜͲͳ͍ɻ
    ࢲͷύϑΥʔϚϯεཧ࿦ vol.16 -ूஂʹ͍ͭͯ-

    http://tamesue.jp/blog/archives/think/20190422


    ɹԿΛ΋ͬͯ൑அ͢Δʁ

    View Slide

  127. ɹɹɹᶃߴ͍໨ඪΛઃఆ͢Δ

    ɹɹɹᶄϦιʔεΛ౤͡ͳͯ͘ྑ͍৔ॴΛ୳͢
    ɹɹɹˠ Ͳ͏͍͏ࢥߟϑϨʔϜϫʔΫ܈Λ࢖͏ʁ


    ɹ͜ͷઌੜ͖ͷ͜Δσʔλઃܭ

    View Slide

  128. ੈք࠷ݹͷσʔλج൫ [ཁग़య]


    ɹ“ધ৐ΓͷḺ” ϝλϑΝʔ
    ਫͷ࢒ྔ
    ໋ʹؔΘΔ࠷ॏཁ,1*
    ʮҰʯͷઢ
    ଈ࠲ʹಡΊΔ௚ײతͳ6*
    અ໿PSফඅ
    Ξϩέʔγϣϯͷҙࢥܾఆ
    ۩ମతͳߦಈʹ௚݁
    https://www.irasutoya.com/2015/12/blog-post_694.htmlɹhttps://www.irasutoya.com/2013/05/blog-post_2062.html

    View Slide


  129. ʮધ৐Γʹͱͬͯͷਫͷ࢒ྔʯʹϑΥʔΧε͠Α͏
    ໷΋຾Εͳ͘ͳΔΑ͏ͳ໰୊͸ԿͩΖ͏ʁ
    ʢΠϯηϓγϣϯσοΩʣ
    ΋͠ΘΕΘΕ͕௥͍ग़͞Εɺऔక໾ձ͕৽͍͠CEOΛ

    ೚໋ͨ͠ͱͨ͠Βɺͦͷਓ෺͸ɺ͍͍ͬͨͲΜͳࡦΛऔΔͱࢥ͏͔͍ʁ
    ʢΞϯυϦϡʔɾSɾάϩʔϰʣ


    ɹIssue Driven

    View Slide



  130. ɹData Lifecycle
    ϓϩμΫτ
    Ϣʔβʔ
    DBɾϩά
    ࢪࡦɾۀ຿
    ෼ࢄܕ

    σʔλετΞ

    e.g. BigQuery
    ऩू ૄ௨
    ׆༻
    Ձ஋
    %BUB0QTʹ͓͍ͯ
    ࠷େԽ͢΂͖໨తม਺

    View Slide

  131. ޻ఔʢδϣϒʣ ʹ͓͍ͯ “ϦλʔϯΛ্͛Δ” or “ίετΛԼ͛Δ”
    ɹData Driven Development / Ұൠݪଇ
    ۀ຿" ۀ຿# ۀ຿$
    ΦϖϨʔγϣϯ ΦϖϨʔγϣϯ ΦϖϨʔγϣϯ
    ҙࢥܾఆ ҙࢥܾఆ ҙࢥܾఆ
    Ձ஋
    #*.-Ҋ݅

    ΞφϦςΟΫε
    "1.-Ҋ݅

    ιϦϡʔγϣϯ


    View Slide

  132. ΧελϚʔδϟʔχʔ ʹ͓͍ͯ “ϦλʔϯΛ্͛Δ” or “ίετΛԼ͛Δ”
    ɹData Driven Development for User
    ྫɿϨίϝϯυʹΑͬͯߪങۀ຿ʢ৘ใऩूˠൺֱݕ౼ˠҙࢥܾఆˠߪങΞΫγϣϯʣΛࢧԉ͢Δ


    View Slide

  133. όϦϡʔετϦʔϜ ʹ͓͍ͯ “ϦλʔϯΛ্͛Δ” or “ίετΛԼ͛Δ”
    ɹData Driven Development for Company
    ྫɿϚʔέςΟϯάۀ຿΍ΧελϚʔαϙʔτۀ຿ͷҰ෦ࣗಈԽͳͲ


    ʮ೔ຊͷDevOpsมֵΛଅਐ͢ΔόϦϡʔετϦʔϜϚοϐϯάʯΑΓҾ༻

    http://gihyo.jp/dev/column/01/devops/2017/value-stream-mapping

    View Slide


  134. σʔλ࢓༷ʹৄ͍͠ʢগͳ͘ͱ΋ௐࠪ͢ΔεΩϧΛ΋͍ͬͯΔʣݱ৔୲౰ऀ͕

    ୲౰ྖҬΛ޿͛Δ͜ͱͰલޙ޻ఔͷϦʔυλΠϜΛ୹ॖ



    ɹݱ৔͕ओମతʹPDSαΠΫϧΛճ͢ʢ࠶ܝʣ
    ੡଄
    ઃܭ ࢼݧ ϦϦʔε
    ޮՌ

    ଌఆ
    ཁ݅

    ఆٛ
    ཁٻ

    ੔ཧ
    ੡଄
    ઃܭ ࢼݧ ϦϦʔε
    ޮՌ

    ଌఆ
    ཁ݅

    ఆٛ
    ཁٻ

    ੔ཧ
    طʹ΍͍ͬͯΔ
    ෼ੳཁٻ ϩάཁ݅ ܭଌ
    ΞΠσΞ

    View Slide

  135. ɾݱ৔ͷ1ਓ1ਓ͕ओ໾ͱͳΓɺσʔλΛ׆༻ ͯ͠ ϓϩμΫτ΍ۀ຿ Λຏ͖ࠐΉ
    ɾ͜ͷੵΈॏͶʹΑͬͯࣄۀ͕੒௕͠ɺސ٬ʹՁ஋Λಧ͚Δ͜ͱʹͳΔ
    ɹຊ౰ͷҙຯͰͷσʔλۦಈ૊৫
    4&$*Ϟσϧ
    ސ٬Ձ஋ উͪےɾUVP
    ΤϯδχΞ ू٬
    σβΠϯ ηΩϡϦςΟ
    ๏຿
    ΧελϚʔαϙʔτ ޿ใ
    ࢧԉऀɾ։୓ऀͱͯ͠ͷ

    αΠΤϯςΟετɾΞφϦετ
    σʔλ

    View Slide

  136. σʔλ ͱ ۀ຿ Λܨ͛Δ
    ςΫϊϩδʔ ͱ Ϗδωεݱ৔ Λܨ͛Δ
    σʔλΛ׆༻͢Δ͜ͱͰ ୭ͷͲΜͳ՝୊Λղܾ͢Δͷ͔ ͱ໰͍௚͢
    ɹDataͱOpsΛܨ͛Α͏

    View Slide



  137. ɹ͋ΒΏΔࢪࡦ͸KGIʹܨ͕Δ
    ૑଄͢Δ
    Ձ஋ͷ࠷େԽ
    ੡඼ఏڙ
    ू٬
    Ξϩέʔγϣϯ࠷దԽ
    αʔϏεϨϕϧͷҡ࣋
    ෼ੳۀ຿
    தؒςʔϒϧ੔උ
    σʔλෆ੔߹ͷղফ
    ۀ຿ͷ2$%ޮ཰Λ

    վળ͢Δࢪࡦ
    ଞͷࢪࡦ
    ଞͷࢪࡦ
    ͜ͷࢪࡦͰಘΔ༧ఆͷReturn͸ʁ

    ͜͜ʹ౤Լ͢΂͖Investment͸ʁ

    ΞφϦετͱͯ͠ΠϯύΫτΛ

    ࠷େԽ͢Δҙࢥܾఆ͕Ͱ͖͍ͯΔ͔ʁ
    બ୒ࢶΛͪΌΜͱݮΒ͍ͤͯΔ͔ʁ

    View Slide

  138. ༨ܭͳબ୒ࢶʢʹϜμʣΛݮΒ͢

    ࡟͗མͱͨ͠ঢ়ଶʢʹϦʔϯʣͰۀ຿Λ਱ߦ͠Α͏
    σΟεΧογϣϯλΠϜ͸

    ʮ৽͘͠΍Δ͜ͱΛ୳͢ʯͷͰ͸ͳ͘

    ʮ΍Βͳ͍͜ͱΛܾΊΔʯ৔ʹͯ͠΄͍͠
    ʮֶͿ͜ͱʯΛ໨తԽ͢ΔͷͰ͸ͳ͘

    ʮߦಈ͢Δ͜ͱʯΛݟਾ͑ͯ΄͍͠


    ɹ͍͔ʹબ୒ࢶΛݮΒ͔͢

    View Slide

  139. ɾYAGNIɿૣ͗͢Δ࠷దԽΛආ͚Δɻա৒࣮૷Λճආ͢Δɻ

    ɾϦʔϯ։ൃͷݪଇ4ɿܾఆΛͰ͖Δ͚ͩ஗ΒͤΔɻ

    ɾδϟετΠϯλΠϜɿඞཁͳͱ͖ʹɺඞཁͳ΋ͷΛɺඞཁͳ͚ͩ࡞Δɻ

    ɾRule of threeɿ3ճಉ͡࿩͕ग़ͨΒɺͦͷ3ճʹରԠͰ͖Δఔ౓ʹ൚༻తͳ࢓૊ΈΛ࡞Δɻ

    ɾεΫϥοϓˍϏϧυɿ࡞ͬͯ͸յ͢͏ͪʹܦݧ஋͕ཷ·Γɺ࣍ͷ࢓૊Έ͕ચ࿅͞ΕΔɻ

    ɾϘʔΠεΧ΢τݪଇɿͦͷ৔Ͱ͍ͭͰʹ௚͢ɻࢁΛ๚ΕͨΒɺΰϛ͸रͬͯɺ๚ΕΔલΑΓ៉ྷʹ͔ͯ͠ΒؼΔɻ

    ɾ৬ೳԣஅɿεΩϧͰ͸ͳ͘ΠγϡʔͰಈ͘ɻඞཁͳۀ຿Λɺඞཁͱ͢Δऀ͕ɺඞཁͳͱ͖ʹɺඞཁͳ͚ͩ਱ߦ͢Δɻ

    ɾϖΞϫʔΫɺϞϒϫʔΫɿྡͰҰॹʹ΍ΔɻεΩϧͷ͋ΔਓͱҰॹʹ࡞ۀ͢Ε͹ɺ͔࣍Βࣗ෼1ਓͰ࡞ۀͰ͖Δɻ


    ɹෆ࣮֬ੑΛΤϯδχΞϦϯά͢Δݪཧɾݪଇɾ఩ֶɾϓϥΫςΟεetc… / ओ؍Ͱ͢

    View Slide

  140. ϜμΛল͜͏


    ɹ݁࿦

    View Slide

  141. ද໘తͳख๏࿦΍Ωʔϫʔυʹ

    ৼΓճ͞Εͳ͍Ͱ΄͍͠

    े෼ͳ৘ใऩूΛܦͯ
    ΠγϡʔۦಈͷҙࢥܾఆΛͯ͠΄͍͠
    ʮΞφϦετ͕ࢥ͍͍ͭͨཧ૝ͷج൫ʯΛ
    ؔ܎ऀʹԡ͠෇͚ͳ͍Α͏ʹ৺ֻ͚ͯ΄͍͠


    ɹ఻͍͑ͨ͜ͱʢ࠶ܝʣ

    View Slide



  142. ɹຊ೔࿩͢͜ͱ
    ֓ཁ ݁࿦
    ߏங௚ޙʹΑ͋͘Δࣦഊࣄྫ΍ઃܭϛε ࠓΛੜ͖Α͏
    σʔλ΢ΣΞϋ΢εɾϚʔτͷσβΠϯ ຊΛಡ΋͏
    ͜ͷઌੜ͖ͷ͜Δʹ͸ ϜμΛল͜͏

    View Slide

  143. ͲͷϑΣʔζͰɺͲ͜Λ໨ࢦͯ͠ɺ

    Ͳͷ՝୊͕ੜͯ͡ɺͲͷଧͪखΛߨ͡Δ͔
    ੩తͳܭըॻͷࡦఆͱ९क͚ͩͰ͸ͳ͘
    ಈతͳݕࠪͱదԠΛ܁Γฦ͢͜ͱ͕伴

    ໨ͷલͷΠγϡʔΛݟۃΊͯղ͜͏
    ஍ಓʹϕετΛਚ͍ͯ͘͜͠͏


    ɹ΍͍͖ͬͯ

    View Slide



  144. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠

    https://www.pexels.com/photo/autumn-clouds-conifers-daylight-221395/

    View Slide