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

σʔλϨΠΫߏஙޙͷ࢛ํࢁ࿩ 
2019-09-05 
data platform meetup #DPM 
presented by @yuzutas0
 
https://www.pexels.com/photo/autumn-clouds-conifers-daylight-221395/

View Slide

WEBʹެ։͠·͢ #DPM 
ɹࡱӨ΍ϝϞ͸ෆཁͰ͢ɻϦϥοΫεͯ͠ฉ͍͍͚ͯͨͩΕ͹ͱࢥ͍·͢ɻ 
εϥΠυ 140+ ຕ / 15min 
ɹؤுͬͯ෇͍͖͍ͯͯͩ͘͞ɻࠓ೔͸͚ͬ͜͏͍͚Δؾ͕͠·͢ɻ 
ɹखݩͷࢿྉ܈Λίϐϖ + ԥΓॻ͖ͨ͠εϥΠυͰ͢ɻաڈࢿྉ܈ͱಉ͡಺༰Ͱ͢ɻ 
ɹਓؒ͸௥͍٧ΊΒΕΔͱ͜͏͍͏εϥΠυΛ࡞ͬͯ͠·͏ऑ͍ੜ͖෺ͳͷͰ͢ɻ
Α΋΍·࿩Ͱ͢ʂϙΤϜ࿮Ͱ͢ʂ 
ɹ͓ञΛҿΈͳ͕Βগਓ਺Ͱμϥμϥͱ࿩ͨ͢ΊͷωλͰ͢ɻ 
ɹ࢒೦ͳ͕Βཱ೿ͳൃදࢿྉͰ͋Γ·ͤΜɻϙουΩϟετͩͱࢥͬͯฉ͍͍ͯͩ͘͞ɻ

ɹҿΜͰ͍͜͏ͥʂ
IUUQTXXXQFYFMTDPNQIPUPBMDPIPMJDCFWFSBHFCBSCFFSESJOL

View Slide

1. ͸͡Ίʹ
2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ 
3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ 
4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ 
5. Guide to Data Warehouse & Mart Designʢࣗ࿦ฤʣ 
6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ
7. ͜ͷઌੜ͖ͷ͜Δʹ͸

ɹΞδΣϯμ

View Slide

ɹ@yuzutas0
ɹɹ
 
 

View Slide

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

PyCon JP ’17 
ϕεττʔΫΞϫʔυ༏ल৆
σϒαϛՆ ’18 Ξϯέʔτຬ଍౓No.1 
ʢཌ೥ͷϕετεϐʔΧʔ৆ʹ૬౰ʣ

View Slide

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

View Slide

ɹനӋͷ໼

View Slide

಺ྠͰू·͓ͬͯञΛҿΈͳ͕ΒσΟεΧογϣϯ͢Δձ
ʢΏ͑ʹ30ਓͷืू࿮Ͱ։࢝ͨ͠ʣ
૿࿮ʹ͙࣍૿࿮Ͱɺ૝ఆͷ6.5ഒͷԠื͕͋ͬͨͱͯ͠΋οʂ 
͋͘·Ͱ΋ʮσΟεΧογϣϯͷࡐྉʯΛఏڙ͠·͢οʂ

ɹຊ೔ͷझࢫʢओ؍ʣ

View Slide

ʮΈΑ͏Έ·ͶͰσʔλΛૄ௨ͨ͠ʯ 
ʮ͜ͷ͋ͱج൫ΛͲ͏ਐԽ͍͚ͤͯ͞͹͍͍ͩΖ͏͔ʯ 
 
ͱࢥ͍೰ΜͰ͍ΔΞφϦετ
ʢγεςϜͷ࿩΋ੵۃతʹཧղ͍ͯ͜͠͏ͱ͍͏ํʑʣ 
ͱɺͦͷΧ΢ϯλʔύʔτʹ౰ͨΔSWΤϯδχΞ

ɹຊ೔ͷ૝ఆλʔήοτ

View Slide

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

View Slide

উͪʹෆࢥٞͷউͪ͋Γɻ 
ෛ͚ʹෆࢥٞͷෛ͚ͳ͠ɻ 
 
ʰ݋ஊʱΑΓҾ༻

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

View Slide

ʮΈΑ͏Έ·ͶͰσʔλΛૄ௨ͨ͠ʯ 
ͲͷΑ͏ͳܦҢͰ͋Εɺ͜ͷঢ়گ·Ͱ౸ୡͰ͖ͨͷ͸ɺؒҧ͍ͳ͘େ͖ͳୈҰา
ͦͷ࣮੷Λ࡞Δʹ͸ɺ຾Εͳ͍໷΋͋ͬͨͩΖ͏ 
 
ʮ͜ͷ͋ͱج൫ΛͲ͏ਐԽ͍͚ͤͯ͞͹͍͍ͩΖ͏͔ʯ
Ͳͷఔ౓ͷԹ౓ײͰ͋ΕɺઌΛݟਾ͑ͯ೰ΊΔͷ͸ɺจ۟ͳ͠ʹՁ஋ͷ͋Δ౰ࣄऀҙࣝ 
·͞ʹࠓɺوॏͳ࣌ؒΛ࢖ͬͯɺษڧձʹࢀՃͨ͠ΓɺεϥΠυΛಡΜͰ͍ͩͬͯ͘͞Δ 
 
͜ͷੈքઢΛબΜͰ͘Εͨ͋ͳͨͷʮԫۚͷਫ਼ਆʯʹࢲ͸ܦҢΛද͢Δοʂ
ͦͯ͠ɺͦ͏͍͏ਓ͔ͩΒͦؕͬͯ͜͠·͏᠘͕ɺ͜ͷੈքʹ͸୔ࢁ͋Δͱࢥ͍·͢

ɹ͋ͳͨ͸͍͢͝ʂ

View Slide

ද໘తͳख๏࿦΍Ωʔϫʔυʹ 
ৼΓճ͞Εͳ͍Ͱ΄͍͠
 
े෼ͳ৘ใऩूΛܦͯ
ΠγϡʔۦಈͷҙࢥܾఆΛͯ͠΄͍͠
ʮΞφϦετ͕ࢥ͍͍ͭͨཧ૝ͷج൫ʯΛ
ؔ܎ऀʹԡ͠෇͚ͳ͍Α͏ʹ৺ֻ͚ͯ΄͍͠

ɹ఻͍͑ͨ͜ͱ

View Slide

1. ͸͡Ίʹ

ɹΞδΣϯμ

View Slide

ूΊͨσʔλΛͲ͏׆༻͢Δ͔ 
 
ɾൃදऀݸਓ͕࠷΋ੜ࢈తͩͱࢥ͏໰͍ͷ1ͭ 
ɾ׆༻Λߟ͑Ε͹ج൫ͷ͋Γํ͸ࣗવͱܾ·Δ 
ɾ͔ͤͬ͘ૄ௨ͨ͠ͳΒͲΜͲΜ׆༻͍ͯ͜͠͏

ɹΑ͋͘Δ૬ஊᶃ

View Slide

ɾݱࡏͷԆ௕Ͱଊ͑ͨ΄͏͕ؔ܎ऀͷೲಘΛಘ΍͍͢ 
ɾ͍͖ͳΓToBeϕʔεͩͱص্ͷۭ࿦ʹؕΓ΍͍͢

ɹ࠷ॳʹ֬ೝ͢Δ͜ͱ
"T*Tͷձ࿩ɾߦಈ
ɾ্ҐϨΠϠʔͷҙ޲ 
ɾߏங࣌ࢿྉͰͷݟཱͯ 
ɾݱ৔Ͱطʹࢼͨ͜͠ͱ
ࠓޙͷ׆༻
ʁ

View Slide

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

View Slide

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

View Slide

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

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

View Slide

Ωʔͷͳ͍தؒςʔϒϧ

ɹΑ͋͘Δ૬ஊᶄ

View Slide

ɹ෼ੳऀ͕ݟ΍͍͢Α͏ʹઃܭ
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

ɹ݁ہ͸Ωʔ͕ཉ͘͠ͳΔ
ʮ໺ࡊJEʯͱʮ໺ࡊJEʯͰ 
දࣔը໘͕Ұ෦ҟͳΔͷͰ෼͚ͯूܭ͍ͨ͠ 
ʢσʔλϨΠΫΛࢀর͢Δ͜ͱʹͳΔʣ
౷ܭϓϩάϥϜͰ෼ੳ͢Δʹ͋ͨͬͯ 
ࣗવݴޠΑΓΩʔͷ΄͏͕ॲཧ͠΍͍͢ 
ʢ֤෦ॺͷεΫϦϓτͰಠࣗʹ࠾൪͢Δ͜ͱʹͳΔʣ

View Slide

ɾॳ৺ऀͷઃܭ͸ӡ༻։࢝௚ޙʹഁ୼͢Δ 
ɾ͔ͦ͜ΒֶͿ΂͖͸ʮΩʔΛೖΕ͓ͯ͜͏ʯ͚ͩͰͳ͘ʮΠςϨʔγϣϯΛճͦ͏ʯ 
ɾσʔλϨΠΫͷ࣌఺Ͱܽଛ͢Δͱ෮چࠔ೉ͳͷͰ͚ͦͩ͜͸࠷ॳʹ๷͙ 
ɾݱ৔ʹଇ͍ͯ͠ͳ͍தؒςʔϒϧ͕ੜ͖࢒Δ͜ͱ͸ͳ͍ 
ɾ͋͘·Ͱ΋σʔλϚʔτΛى఺ʹͯ͠෼཭ͱ݁߹Λ܁Γฦ͢

ɹʮ͜ΕͳΒݟ΍͍͢ʯ͸͋͘·ͰԾઆ
σʔλϚʔτ
σʔλ 
΢ΣΞϋ΢ε
σʔλϚʔτ
σʔλϚʔτ

View Slide

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

ɹΑ͋͘Δ૬ஊᶅ

View Slide

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

ɹσʔλͷຽओԽ͸ग़དྷ͍ͯΔ͔ʁ
νʔϜ" νʔϜ# νʔϜ$ νʔϜ% νʔϜ& νʔϜ' νʔϜ( νʔϜ) νʔϜ* νʔϜ+ νʔϜ,
ੜϩά 
ಠࣗར༻
σʔλ5ࢧԉ 
ۀ຿ґཔ
σʔλ5ࢧԉ 
σʔλग़ྗ
ࣗओత 
σʔλग़ྗ
୲౰ऀґଘ ୲౰ऀґଘ ୲౰ऀґଘ
ࣗओత 
σʔλੜ੒
ଞνʔϜґཔ
ج൫ߩݙʂ ୲౰ऀґଘ ୲౰ऀґଘ
ہॴԽͷน
ࣗ૸ͷน
վળͷน

View Slide

ɹσʔλͷຽओԽΛଅਐ͢Δࢪࡦ

View Slide

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

View Slide

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

View Slide

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

View Slide

ɹαϯϓϧ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

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

ɹσʔλۦಈʹΑΔϓϩμΫτվળ
ࢲ͸σʔλج൫ΛͭͷϓϩμΫτͩͱҐஔ෇͚͍ͯ·͢ɻ 
ࣾ֎޲͚ʹ8αʔϏεΛӡӦ͢Δͷͱಉ͡Ͱ͢ɻ 
΍Δ͜ͱ͸ʮࣗ෼͕ߏ૝ͨ͠ϓϩμΫτʯͷߏஙɾӡӦͰ͢ɻ

View Slide

ᶃूΊͨσʔλΛͲ͏׆༻͢Δ͔ 
ᶄΩʔͷͳ͍தؒςʔϒϧ 
ᶅதؒςʔϒϧΛἧ͑ͨͷʹ࢖ΘΕͳ͍

ɹ૬ஊᶃʙᶅ

View Slide

σʔλΛ׆༻͢ΔՕॴʹ͓͍ͯ 
ROIΛ࠷େԽ͢ΔͨΊͷҙࢥܾఆ͕Ͱ͖͍ͯΔ͔ʁ 
ΞφϦετͱͯ͠࠷ߴͷৼΔ෣͍͕Ͱ͖͍ͯΔ͔ʁ

ɹσʔλۦಈ for Output

View Slide

1. ͸͡Ίʹ

ɹΞδΣϯμ

View Slide

ར༻ऀ͕σʔλΛյͯ͠͠·͏

ɹΑ͋͘Δ૬ஊᶆ

View Slide

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

View Slide

ʮॻ͍ͯ͋Δ௨Γʹ΍Ζ͏ʯͱ͸ݴ͍ͬͯͳ͍
࠷ॳ͔ΒGCPϓϩδΣΫτΛ෼͚ΔͷͰ͸ͳ͘
Πγϡʔ͕ݱ৔Ͱڍ͕ͬͨஈ֊ͰਐԽΛݕ౼͢Δ
͋͘·Ͱ΋ʮࠓͷʯʮݱ৔ͷʯʮΠγϡʔʯʹج͍ͮͯ 
ଥ౰ͳଧͪखΛ1ͭ1ͭΠϯΫϦϝϯλϧʹ࠾୒͢Δ

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

View Slide

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

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

View Slide

ɹࣗಈԽͳΒ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

Ϧʔϯ΍ΞδϟΠϧ΍εΫϥϜͷىݯʹ౰ͨΔେ໺଱Ұࢯ͕࠷ॳʹ΍ͬͨ͜ͱ 
ʹυΩϡϝϯτԽʢۀ຿ͷඪ४Խʣ

ɹखಈӡ༻ͳΒखॱॻ͸ෆՄܽ
τϤλࣗ޻ʹసग़͔ͯ͠Βɺࢲ͕·ͣඪ४࡞ۀදΛͭ͘Εͱ 
ݺͼ͔͚ͨ͜ͱ͸͍͏·Ͱ΋ͳ͍ɻ
ੜ࢈ݱ৔ͷجຊͱ΋͍͏΂͖ඪ४࡞ۀදͮ͘Γ͔ΒɺτϤλੜ࢈ํࣜ 
ͮ͘Γ΁ͷࡾޒ೥ʹΘͨΔಓఔΛา·ͤΔ΋ͱΛͳ͍ͯ͠ΔΑ͏ʹࢥ͏ɻ 
ʰτϤλੜ࢈ํࣜ - ୤ن໛ͷܦӦΛΊͯ͟͠ʱΑΓҾ༻

View Slide

ɹखॱॻ͸Ձ஋ʹ௚݁͢Δ
ɹɹɹɹར༻Ҋ಺ɾखॱॻʢ0QTࢹ఺ʣ
#BE (PPE
πʔϧ 
ʢ%FWࢹ఺ʣ
(PPE
࡞ͬͨਓ͔͠࢖͑ͳ͍ 
ʢຊਓʹ͸ศརʣ
࠷ߴ
#BE
࡞ͬͨਓ͔͠࢖͑ͳ͍ 
ʢຊਓʹ΋ෆศʣ
·͊࢖͑Δ
͕Μ͹ͬͯͬͪ͜Λ 
໨ࢦ͕ͪ͠
ͬͪ͜ͷ΄͏͕؆୯Ͱ 
ޮՌ΋ߴ͍ʢ͔΋ʣ

View Slide

ɹυΩϡϝϯτΛίʔυͷΑ͏ʹѻ͏16ͷύλʔϯ (Document Design Pattern)
 
● Ϣʔεέʔε෼ੳ 
● ϦϑΝΫλϦϯά 
● ϖΞϓϩάϥϛϯά 
● Ϟϒϓϩάϥϛϯά 
● όʔδϣϯ؅ཧ 
 
● ΞʔΩςΫτνʔϜ 
● ίϯ΢ΣΠͷ๏ଇ 
ମ੍ͷύλʔϯ
 
● ٕज़తෛ࠴ 
● ϙʔτϑΥϦΦ 
● ίʔσΟϯάΛଅ͢ 
● ίʔυϨϏϡʔ 
● ϘʔΠεΧ΢τ 
● Rule of Three
จԽͷύλʔϯ
 
● MVCϞσϧ 
● GoFσβΠϯύλʔϯ 
● ܧଓతσϦόϦʔ / 
ΠςϨʔγϣϯ
ߏ੒ͷύλʔϯ ࡞ۀͷύλʔϯ
˞ମ੍΍จԽ͸σβΠϯʢઃܭʣʹӨڹΛ༩͑ΔͷͰσβΠϯύλʔϯʹؚΊ͍ͯ·͢
ࢀরʰDevOpsͱυΩϡϝϯτσβΠϯύλʔϯʱ http://yuzutas0.hatenablog.com/entry/2017/07/06/083000

View Slide

σʔλͷߋ৽͕஗͍

ɹΑ͋͘Δ૬ஊᶇ

View Slide

ʮQ. Ͳͷॲཧ͕஗͍ʁʯ ʮA. ෼͔Βͳ͍ʯ͕ѹ౗తେଟ਺ 
צͱܦݧͱ౓ڳͰ͸ͳ͘ɺσʔλۦಈͰ໰୊Λ෼ੳɾղܾ͠Α͏

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

View Slide

͜ͷॱ൪Ͱࢥߟ͠·͠ΐ͏

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

View Slide

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

ɹܭଌ͠ʹ͍͘γεςϜ
ݩσʔλ
֤෦ॺ͕σʔλࢀরγεςϜΛܧ͗଍͠

View Slide

ɹද໘͸ҧ͑Ͳ՝୊͸ಉ͡ ※10ࣾҎ্ͰݟͨΞϯνύλʔϯ

View Slide

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

View Slide

SREνʔϜ* ͕શମઃܭ͔Βݟ௚ͯ͠σʔλج൫Λ੔උ 
Modelʢऩूɾ஝ੵɾՃ޻ʣͱ Viewʢࢀরɾར༻ʣ

ɹγϯϓϧʹͯ͠γεςϜશମΛ࠷దԽ
*͜ͷ࿦఺ͷऔΓ·ͱΊ͸SRE͕๬·͍͠ɻϏδωεܦӦʹཱ͍ۙ৔ͷϩʔϧ͕൑அͯ͠΋ 
৘ใγεςϜઃܭͷઐ໳ՈͰͳ͍ͱɺσʔλϞσϦϯά΍γεςϜ͸ઃܭഁ୼͕ͪ͠ɻ

View Slide

σʔλ͕ະߋ৽ˍܽམͨ͠··์ஔ

ɹΑ͋͘Δ૬ஊᶈ

View Slide

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

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

View Slide

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

ɹςετίʔυ

View Slide

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

ɹߋ৽؂ࢹ

View Slide

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

ɹ஗Ԇঢ়گՄࢹԽ

View Slide

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

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

ɹσʔλ඼࣭Λ୲อ͢Δ
඼࣭
ςετίʔυ
ߋ৽؂ࢹ ஗Ԇঢ়گՄࢹԽ ݅਺Ұக؂ࢹ
σʔλϨΠΫ ੜ੒ςʔϒϧ
҆શੑ 
ʢϚΠφεˠʣ
ਖ਼֬ੑ
׬શੑ
Ұ؏ੑ
੔߹ੑ
ଥ౰ੑ
ద࣌ੑ
Ұҙੑ
༗ޮੑ
རศੑ 
ʢˠϓϥεʣ

View Slide

ද໘తʹπʔϧΛ࡞੒ɾಋೖ͢ΔͷͰ͸ͳ͘ 
ITαʔϏεϚωδϝϯτͷ࿮૊ΈΛݱ৔՝୊ʹ౰ͯ͸ΊΔ

ɹαʔϏεϨϕϧ

View Slide

ɹSLA
σʔλͷ༻్ɾར༻ऀ͝ͱʹظ଴͞Ε͍ͯΔαʔϏεϨϕϧΛՄࢹԽ
ɾ৴པੑ͸ʮ࣮ࡍʹγεςϜΛ࢖͏ਓɾ໨తɾ࣌ؒʯΛલఏʹ͢Δ 
ɾͳͷͰαʔϏεϨϕϧ͸ϢʔεέʔεۦಈͰઃܭ͢Δ͜ͱʹͳΔ 
ɾ୭΋࢖Θͳ͍ػೳ΍࣌ؒଳͰγεςϜՔಇ཰͕Ͱ͋ͬͯ΋࢓ํͳ͍
ྫ ༻్ ໿ଋ૬ख ࿈བྷઌɾप஌ઌ ར༻σʔλ ໿ଋࣄ߲ʢ4-"ʣ ҧ൓࣌ͷӨڹൣғ
೔࣍Ϩϙʔτ σΟϨΫλʔ
4MBDL 
NPOJUPSJOH
#JH2VFSZͷ 
ച্ςʔϒϧ
ຖӦۀ೔ͷޕલY࣌·Ͱʹ 
ܽଛͳ͘લ೔ͷച্͕ 
Ϩϙʔτ͞ΕΔ͜ͱ
ച্ঢ়گʹԠͨ͡ 
ࢪࡦ͕ଧͯͳ͘ͳΔ 
ʢػձଛࣦʣ
ʜ ʜ ʜ ʜ ʜ ʜ
ʜ ʜ ʜ ʜ ʜ ʜ
ʜ ʜ ʜ ʜ ʜ ʜ ʜ
࣮෺ˠ

View Slide

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

View Slide

ɹSLAˍOLAΛվળ͢Δᶃ
ຖεϓϦϯτऴྃ࣌ͷ;Γ͔͑ΓͰɺ࣮ଶʢ"T*Tʣͱظ଴஋ʢ5P#FʣΛൺֱ͢Δ
What
ԿΛ͢Δ͔
εϓϦϯτ 
ϨϏϡʔ
σΠϦʔ 
εΫϥϜ
How
Ͳ͏΍ͬͯ͢Δ͔
εϓϦϯτ 
ϓϥϯχϯά
Ϩτϩ 
εϖΫςΟϒ

View Slide

ɹSLAˍOLAΛվળ͢Δᶄ
վળΞΫγϣϯ
 
ɾ໨ඪΛຬͨͤΔΑ͏ʹվળ͢Δɿج൫γεςϜͷվमҊ݅Λىථ 
ɾ໨ඪࣗମΛमਖ਼͢Δɿա৒ͳΒ໨ඪΛԼํमਖ਼ɺ࢖༻ఀࢭ#*πʔϧ͸ΫϦʔχϯάɺ؍఺࿙Ε͸௥Ճ
ˣ࣮෺ʢϨτϩεϖΫςΟϒͷΞδΣϯμʣ

View Slide

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

ɹ૬ஊᶆʙᶈ

View Slide

σʔλΛૄ௨͢ΔՕॴʹ͓͍ͯ 

ɹσʔλۦಈ for Process

View Slide

1. ͸͡Ίʹ

ɹΞδΣϯμ

View Slide

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

ɹΑ͋͘Δ૬ஊᶉ

View Slide

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

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

View Slide

ΞφϦετʮ͜ͷσʔλ͸ෆ੔߹ͩʂʯ
↓
ΞφϦετʮΫϨϯδϯάͯ͠෼ੳ͠Α͏ʯ
↓ 
σʔλ෼ੳ͸ແࣄʹ׬ྃ
↓
ཌ݄
↓
DBͷσʔλෆ੔߹ʹΑΔ঎༻όονো֐

ɹຊ౰ʹ͋ͬͨා͍࿩

View Slide

ɹσʔλʹ͸ੜ੒ݩ͕͋Δ
ΞϓϦ
Ϣʔβʔ %# #2
͜͜ʹෆ੔߹͕͋ΔͳΒ
঎༻؀ڥ
Ͳ͔͜Ͱ໰୊͕ى͖͍ͯΔ 
ʢՄೳੑ͕͋Δʣ
͚ͩ͜͜Λ௚ͯ͠΋ຊ຤స౗ 
ͦͷҙࢥܾఆ͸ຊ౰ʹ,(*ʹج͍͍ͮͯΔ͔ 
σʔλۦಈͩͱݴ͑Δͷ͔
ఏڙՁ஋Λ࠷େԽ͢ΔͨΊʹ 
σʔλ෼ੳΛ࢝Ίͨ͸ͣ
ྑ͍ϓϩμΫτΛ࡞ΔͨΊʹ 
σʔλ෼ੳΛ࢝Ίͨ͸ͣ

View Slide

ʮ૬ஊ͍ͤͯͩ͘͞͞ʯʮDBͷσʔλʹෆ੔߹͕͋ΔΑ͏Ͱ͢ʯͷҰݴͰɺ 
1ճͷύον౰ͯͰɺ1ߦͷίʔυվमͰɺ1ਓͷ͓٬༷ͷਓੜ͕180౓มΘΔ͔΋͠Εͳ͍ 
ͦΕ͕ITαʔϏεʹܞΘΔ୉ޣຯʢͷ1ͭʣͳͷͰɺੵۃతʹຯΘ͍͖ͬͯ·͠ΐ͏

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

View Slide

ϓϩμΫτνʔϜ͕ͦ͜ओਓެύʔςΟʔ΍ͶΜʂ
ΞφϦετ͸ఢύʔςΟʔͷ2൪໨ʹڧ͍΍ͭΛͬͦ͜ΓཪͰ౗͢ϙδγϣϯ΍ͶΜʂ
ΜͰઓ͍ͷߦํΛࠨӈ͢ΔεΠονΛԡ͠ͱ͘ͶΜʂͦΕ͕͋Δ͔Β࠷ޙͷ࠷ޙͰओਓެ͸ٯస͢ΔͶΜʂ
ʮ;ͬɺ͔͜͜Βઌ͸͓લୡ࣍ୈͩʯͬͯݴͬͯҋͷதʹফ͍͑ͯ͘ϙδγϣϯ΍ͶΜʂ 
෢ಓձฤͩͬͨΒ෭কʹͳΔ΍ͭ΍ͶΜʂখઆ൛ͰΑ͏΍͘ϑΟʔνϟʔ͞Εͯաڈ͕໌͔͞ΕΔͶΜʂ 
ຊࢽͩͱʮ΍Ε΍Εʯͱ͔ݴ͍ͳ͕ΒओਓެύʔςΟʔʹύε͢Δͱ͜Ζ·Ͱ͕࢓ࣄ΍ͶΜʂ 
ʮ͓લ΄Ͳͷ࠽Λ࣋ͭऀ͕ͳͥ͋ΜͳΨΩͲ΋ʹྗΛି͢ͷͩʂʁʯͬͯݴΘΕͨΒ 
ʮ·͓ͬͨ͘ΊͰ͍ͨ΍ͭΒͩΑͳʯ͔Β࢝·ͬͯɺҒͦ͏ʹʢͰ΋ͪΐͬͱخͦ͠͏ʹʣओਓެͨͪͷັྗΛղઆ͢ΔΜ΍ʂ 
ͦ͏͍͏ϙδγϣϯͳͷʹ͞ʂ͏͔ͬΓαϙʔτ๨ΕͯͨΒ͞ʂ͍ͩ͞͡ΌΜʂ
͔ͩΒϓϩμΫτνʔϜʹ΋ͬͱΤεΧϨ͍ͯ͜͠ʂͶʂ 
େৎ෉ʂ͖ͬͱ܅ͷؾ࣋ͪ͸఻ΘΔ͞ʂ෼͔Δ΍ͭ͸෼͔ͬͯ͘ΕΔ͞ʂ 
͓΋͍͖ͬΓڳΛுͬͯԿճͩͬͯශ๡͘͡ΛҾ͍͍ͯ͜͏ͥʂपΓͷ୭ΑΓ΋ஏΛ͔͍͍ͯ͜͏ͥʂ 
͜ΜͳεϥΠυΛެ։͢ΔΞϗ͕͍ΔΜͩʂσʔλෆ੔߹Λ૬ஊ͢ΔͷʹڪΕΔඞཁͳΜͯͳ͍ͬͯʂ
ؾ෇͍ͪΌͬͨͳΒߦಈ͢Δ͔͠ͳ͍ͩΖʂଞͷ୭Ͱ΋ͳ͍ʂ܅ͳΜͩʂ܅͕܅ͷपΓͷੈքΛม͑ΔΜͩʂ

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

View Slide

ٖࣅతʹཤྺσʔλΛੜ੒͍ͨ͠

ɹΑ͋͘Δ૬ஊᶊ

View Slide

೔࣍όονʹΑΔεφοϓγϣοτ

ɹ؆୯ʹग़དྷͯ͠·͏ํ๏
VQEBUF͞ΕΔΧϥϜ
ྫQBZNFOU@TUBUVT 
୯ମͰ͸ਪҠΛ෼ੳͰ͖ͳ͍
ਖ਼֬Ͱ͸ͳ͍͕
೔୯Ґͷࠩ෼ɾਪҠ͸ 
ݟΔ͜ͱ͕Ͱ͖Δ

View Slide

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

ɹਫ਼౓͕ཉ͘͠ͳΔ

View Slide

ཤྺΛ࣋ͭσʔλͷઃܭ
https://speakerdeck.com/kawasima/lu-li-wochi-tudetafalseshe-ji
 
ΠϛϡʔλϒϧσʔλϞσϧ(ೖ໳ฤ)
https://www.slideshare.net/kawasima/ss-40471672

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

View Slide

߹ΘͤͯಡΈ͍ͨɿʮΞΠσΞΛग़͢͜ͱ͕اըͩͱࢥͬͯΔౕ͸100ສճࢮΜͰ͍͍ʯ 
http://dochikushow.blog3.fc2.com/blog-entry-3003.html

ɹཤྺ΋ؚΊͯϓϩμΫτϚωδϝϯτ
๏ྩ९कɾফඅऀอޢ ໰͍߹ΘͤରԠ
ߪೖ௚ޙʹ঎඼ͷઆ໌จ͕ 
Ξοϓσʔτ͞ΕͨΒʁ 
Ԡื௚ޙʹٻਓͷใुཝ͕ 
Ξοϓσʔτ͞ΕͨΒʁ 
 
ʮݴͬͨʯʮݴΘͳ͍ʯͷ 
໰୊͕ى͖͏ΔͷͰ 
ه࿥อ࣋Λٛ຿෇͚Δ 
๏཯ɾΨΠυϥΠϯ͸ଟ͍
ཤྺΛௐ্ࠪͨ͠Ͱ 
ճ౴Λฦ͢ػձ͕ଟ͍
 
ΧελϚʔαϙʔτπʔϧͷ 
ػೳཁ݅ͱͯ͠ 
ཤྺอ͕࣋ٻΊΒΕΔ 
 
໰͍߹ΘͤରԠ΋ؚΊͯ 
ϓϩμΫτͷར༻ମݧ

View Slide

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

ɹΑ͋͘Δ૬ஊᶋ

View Slide

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

View Slide

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

View Slide

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

ɹཁ݅ఆٛϑΥʔϚοτ
ɹ࠾൪@Ҋ໊݅@ཁ݅ϝϞ
ɹ໨త
ɹղফ͍ͨ͠ෆ 
ɹଧͪख 
ɹޮՌݟཱͯ 
ɹϏδωεཁٻɹ 
ɹػೳ֓ཁ 
ɹޮՌܭଌࢦඪ
ɹը໘Πϝʔδ
ɹӨڹൣғ
ɹγεςϜཁ݅ৄࡉ
ɹػೳཁ݅Ұཡ 
ɹޮՌܭଌϩάཁ݅
ɹର৅ը໘Ұཡ
ɹγʔέϯεɾσʔλϑϩʔ 
ɹཹҙࣄ߲
ɹΧελϚʔαϙʔτ΁ͷӨڹʹ͍ͭͯ
ɹ๏຿ɾফඅऀอޢɾݸਓ৘ใอޢʹ͍ͭͯ
ɹΠϯϑϥɾΩϟύγςΟʹ͍ͭͯ
ɹઃܭ؍఺ʹ͍ͭͯ
ɹςετ؍఺ʹ͍ͭͯ
ɹϦϦʔε࡞ۀ؍఺ʹ͍ͭͯ
ɹϦϦʔεޙ࡞ۀ؍఺ʹ͍ͭͯ

View Slide

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

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

View Slide

ɹ֤ϓϩμΫτνʔϜ಺ͷࣗ૸Λଅ͢
੡଄
ઃܭ ࢼݧ ϦϦʔε
ޮՌ 
ଌఆ
ཁ݅ 
ఆٛ
ཁٻ 
੔ཧ
੡଄
ޮՌ 
ଌఆ
ཁ݅ 
ఆٛ
ཁٻ 
੔ཧ
طʹ΍͍ͬͯΔ
෼ੳཁٻ ϩάཁ݅ ܭଌ
ΞΠσΞ
ΞφϦετͰ͸ͳ͘։ൃνʔϜ͕ࣗલͰ΍Δ͜ͱʹҙຯ͕͋Δ 
εΫϥϜϚελʔͷσʔλ׆༻൛ͱͯ͠ৼΔ෣͏
 
σʔλ࢓༷ʹৄ͍͠ʢগͳ͘ͱ΋ௐࠪ͢ΔεΩϧ͸΋͍ͬͯΔʣΤϯδχΞ͕ 
୲౰ྖҬΛ޿͛Δ͜ͱͰલޙ޻ఔͷϦʔυλΠϜΛ୹ॖ

View Slide

ɹҰ൪ͷ౰ࣄऀ͕PDSαΠΫϧΛճ͢
ɾSWΤϯδχΞ͕P/L੹೚Λ࣋ͬͯҊ݅ىҊ͔ΒޮՌଌఆ·ͰΛ୲͏ͷ͕ཧ૝ 
ɾࣗ෼ͰϞχλϦϯά͢Ε͹ɺϩάͷෆඋΛ࣮ײ͢ΔͷͰɺ͔࣍Βվળ͞ΕΔ
ɾ֎͔ΒҰํతʹԡ͠෇͚Δ͚ͩͩͱɺ͍ͭ·Ͱ΋ޓ͍ʹ෼͔Γ͋͑ͳ͍·· 
ɾݡऀ͸ྺ࢙͔Βֶͼɺ۪ऀ͸ܦݧ͔ΒֶͿ → զʑ͸۪ऀ → ·ͣܦݧ͠Α͏
https://speakerdeck.com/rtechkouhou/enziniadakedeqi-hua-kai-fa-fen-xi-subetewosui-xing-surutimuwoli-tishang-getahua

View Slide

 
·ͣछΛ·͖ɺ࣍ʹͦΕΛמΓऔΔͷ͕మଇͰ͋Δʢʰ෋Λஙٕ͘ज़ʱΑΓʣ

ɹ෺ࣄʹ͸ॱং͕͋Δ
ΤϯδχΞνʔϜʹ͓͚Δ 
DevˍOps҆ఆԽʢʹ૊৫ͷITԽʣ͕ઌ
ͦͷ͋ͱʹγεςϜ͔Βੜ੒͞ΕΔ 
σʔλΛ׆༻ͯ͠ࣄۀΛ͞Βʹ৳͹͢

View Slide

ʮࣗ෼͕ͨͪ෼ੳ͠΍͍͢ʯ͚ͩͷখखઌͷ޻෉ʹಀ͛ͯ΋ɺ८Γ८ͬͯࣗ෼ͷटΛకΊΔ͚ͩɻ 
ϓϩμΫτΛྑ͘͢ΔͨΊʹσʔλΛݟ͍ͯΔΜͩΑͳ͊Զͨͪ͸ʂ
উखʹઢΛҾ͘ͷ͸΋͏΍ΊΑ͏ɻࣗ෼Ͱࣗ෼ͷ৺Λด͡ࠐΊ͍ͯΔ͜ͱʹؾ෇͜͏ɻ 
ͳͥ෼ੳ͕ਐ·ͳ͍ʁϩά͕͓͔͍͠ʁͯΊ͕͐ϓϧϦΫΛग़ͤʂ
ͳͥͰ͖ͳ͍ʁؾܰʹϓϧϦΫΛग़ͤΔ૊৫͡Όͳ͍ʁͯΊ͕͐૊৫Λཱͯ௚ͤʂ 
ͳͥͰ͖ͳ͍ʁ૊৫Λཱͯ௚͢ݖݶ͕ͳ͍ʁݖݶ͕͋Δ΍ͭΛཪ͔ΒૢΕʂ
ͳͥͰ͖ͳ͍ʁૢΔํ๏͕෼͔Βͳ͍ʁʰਓΛಈ͔͢ʱʰ෦ԼྗʱΛಡΊʂ 
ͳͥͰ͖ͳ͍ʁͦΜͳʹ༏ल͡Όͳ͍͔Βʁ͡Ό͋༏लͳͻͱʹॿ͚ͯ΋Β͓͏ʂ
Ծʹ੹೚෼ղ఺Λଚॏ͢Δେਓͳੈքઢʹ͍Δͱͯ͠ɺϓϩμΫτΛվળͰ͖Δ͔΋͠Εͳ͍ΞΠσΞ͕͋ΔͳΒɺ 
ͤΊͯ୲౰ऀʹ૬ஊ͢Δ͘Β͍͸΍ͬͯΈͯ͸͍͔͕Ͱ͠ΐ͏͔ɻ 
τΠϨͰ΋λόί෦԰Ͱ΋څ౬ࣨͰ΋Կ͔͠Βνϟϯε͸͋Δ͸ͣͩΑͶϋϜଠ࿠ʢʰ૊৫ύλʔϯʱΑΓʣ 
෼͔Δɻ෼͔ΔΑɻͦ͜·Ͱ΍Γͨ͘ͳ͍ΑͶɻ໘౗ͩΑͶɻ 
Ͱ΋͞ɺจ۟Λݴ͍ଓ͚Δ͘Β͍ͩͬͨΒɺখͯ͘͞΋ྑ͍ͷͰҰา͚ͩߦಈͯ͠Έ·ͤΜ͔ɻ
ͨͱ͑ߑਫΛࢭΊΔ͜ͱ͕Ͱ͖ͳͯ͘΋ɺਫʹྲྀ͞Εͳ͍Ͱཱͪਚ͘͢Ұຊͷ໦Ͱ͋Γ͍ͨ͡Όͳ͍Ͱ͔͢ɻ 
શͯ͸͔ͦ͜ΒͰ͢Αɻ0.01cmͷް͞ͷࢴͩͬͯ25ճંΕ͹෋࢜ࢁʹͩͬͯಧ͘ΜͩͥοʂʢʰຐԦʱΑΓʣ

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

View Slide

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

ɹ૬ஊᶉʙᶋ

View Slide

σʔλΛੜ੒͢ΔՕॴʹ͓͍ͯ 

ɹσʔλۦಈ for Input

View Slide

ʮԿͷͨΊʹ͜Ε΍ͬͯΔΜ͚ͩͬʁʯʢΰʔϧʣ 
ʮࠓͰ͖Δ͜ͱͬͯͳΜ͚ͩͬʁʯʢΞΫγϣϯʣ
͜ͷ2ͭͷ࣭໰͔Β໨Λഎ͚ͨॠؒʹ
Ξϯνύλʔϯ΁ͱؕΔ
͜ͷ2ͭͷ࣭໰ʹ౴͑ଓ͚͍ͯΔͳΒ 
ඞͣಓ͸։͚Δ

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

View Slide

ʲҊ݅ΛϦετʹͯ͠༏ઌॱҐΛ෇͚Δʳ → ʲఆظతʹৼΓฦͬͯيಓमਖ਼͢Δʳ 
஍ຯͰ౰ͨΓલͳ͜ͱΛ೔ʑੵΈॏͶͨઌʹͦ͜উར͕͋Δ
Ώ͘Ώ͘͸ηϨϞχʔˍΞΠςϜ͕੔උ͞Ε͍ͯ͘͸ͣ

ɹ۩ମతʹԿΛͲ͏΍Ε͹͍͍ͷ͔

View Slide

ࠓΛੜ͖Α͏

ɹ݁࿦

View Slide

1. ͸͡Ίʹ

ɹΞδΣϯμ

View Slide

ɹ͓୊ʢ࠶ܝʣ

View Slide

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

View Slide

ɹઃܭ࿦ʢղઆهࣄʣ

View Slide

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

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

View Slide

ॳظ࣌఺ͷઃܭΛ׬ᘳʹ͢ΔΑΓ΋
 
ΠςϨʔγϣϯΛ܁Γฦ͢͜ͱͰ
“σʔλϞσϧΛվળ͠΍͍͢؀ڥ” ͷߏங͕伴
ʢਐԽతσʔλϞσϦϯάʣ

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

View Slide

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

ɹҙݟද໌

View Slide

ɹ࠷ॳ͸σʔλϨΠΫ͚ͩͰ͍͍

View Slide

1. ΞφϦετ͕TableauͳͲͷBIπʔϧͰ࣮ݧతʹσʔλϚʔτΛ࡞Δ 
ʢμογϡϘʔυΛ࡞ΔͨΊͷσʔλूܭϩδοΫʣ 
 
2. ܧଓར༻͢Δ৔߹͸SQL on σʔλύΠϓϥΠϯʹϩδοΫΛऔΓࠐΉ 
ɹʢ࢖Θͳ͚Ε͹আ٫ɾΫϦʔχϯά͢Δʣ

ɹ࠷খͷσʔλϚʔτ࡞੒खॱ
.BSU
ར༻ऀ޲͚*'
㲈σʔλϚʔτ

View Slide

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

ɹ൚༻తʢࣗশʣ͸ආ͚Α͏

View Slide

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

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

View Slide

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

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

View Slide

ɹઃܭख๏ʹ·ͭΘΔ਺ଟͷࢹ఺

View Slide

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

ɹཐટͷΑ͏ʹ܁Γฦ͢͜ͱͰ੒௕͢Δ
ݸผԽW
ʢ෼཭ʣ
ڞ௨ԽW
ʢ༥߹ʣ
ݸผԽW
ʢ෼཭ʣ
ڞ௨ԽW
ʢ༥߹ʣ
ݸผԽW
ʢ෼཭ʣ
ڞ௨ԽW
ʢ༥߹ʣ

View Slide

যΒͳͯ͘΋ڞ௨Խͨ͘͠ͳΔ͕࣌དྷΔ 
ͦͷ࣌ʹڞ௨Խ͢Ε͹͍͍ͱࢥ͍·͢ʂ 
 
ීஈ͔Β໨ͷલͷΠγϡʔͱਅ伨ʹ޲͖߹͍ͬͯΕ͹
ʮ͜͏͍͏՝୊͕͋Δ͔Βʯʢཁٻʣ
ʮ͜͏͍͏ڞ௨Խ͕͍ͨ͠ʯʢཁ݅ʣ 
ͱ͍࣮ͬͨײͷ൐͏ݴ༿͕ग़ͯ͘Δ͸ͣ
ඞཁͳͱ͖ʹػձ͸ࣗવͱ८ͬͯ͘Δ
͔ͩΒ৺ͷ··ʹਐΊ͹͍͍ͱࢥ͍·͢ʂ

ɹ͍ͣΕ࣌͸དྷΔ

View Slide

ɹ͚ͩ͜͜Λ཈͑Δ
҆શ໘
ࣦഊͯ͠΋΍Γ௚ͤΔΑ͏ʹ 
੔ܗલσʔλͷίϐʔΛ஝ੵ͢Δ
σʔλϨΠΫ
ޮ཰໘
ϦϑΝΫλϦϯά͠΍͍͢Α͏ʹ 
ಉҰͷٕज़ཁૉͰ؅ཧ͢Δ
SQL on Worﬂow Engine
ܧଓ໘
ʮվળʯΛश׳ԽͰ͖ΔΑ͏ʹ 
σʔλར༻ऀ͕ϓϩάϥϛϯάͱϦʔϯͷݪଇΛ 
ֶ΂ΔΑ͏ࢧԉ͢Δ
σʔλͷຽओԽ 
ʢΞδϟΠϧίʔνεΫϥϜϚελʔʣ

View Slide

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

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

View Slide

ɹ෼ੳ༻σʔλϞσϦϯάͷROIಛੑ
!
෼ੳۀ຿ͷ2$%4ʹϙδςΟϒӨڹ 
࢖͍ճ͠ͰޮՌ͕ੵΈॏͳΔ

៉ྷͳதؒςʔϒϧ͕͋ͬͯ΋ 
ച্͕૿͑ΔΘ͚Ͱ͸ͳ͍
Ұׅ౤ࢿͰϩʔυϚοϓΛඳ͘ͷͰ͸ͳ͘ʢճऩΛ࣮ײͰ͖ͳ͍ʣ 
ඞཁʹͳͬͨ࣌ʹͦͷ৔Ͱϓϧϕʔεʹ࡞੒ɾڞ௨Խɾ෼཭͢Δ

View Slide

ͦͷ࣌ʑͰ໨ͷલͷΠγϡʔΛ੔ཧͰ͖͍ͯͨΒ
͋Δ΂͖σʔλϞσϧʢͷԾઆʣ͸ࣗવͱݟ͑Δ͸ͣ
ܧଓతվળͷΧϧνϟʔɾϓϩηε͕ఆணͰ͖͍ͯͨΒ 
Ծʹࣦഊ͕͋ͬͯ΋ωΫετΞΫγϣϯʹ׆͔ͤΔ͸ͣ
΋͠ෆ҆Λ๊͍͑ͯΔͳΒɺਅҼ͸ͦ͜ʹ͋ΔͷͰ͸ʁ 
ʢͳͷͰ͜ΕҎ্ͷٞ࿦Λ͢Δͭ΋Γ͸ͳ͍ʣ 
ʢͳͷͰ։ൃͰ͸ͳ͘ίϯαϧͰҊ݅Λ੥͚͍ͯΔʣ

ɹ୭ͷͨΊͷઃܭʁ

View Slide

ΞφϦετ͕σʔλϞσϦϯάʹؔ৺Λ࣋ͭͱ͖ 
ΠγϡʔۦಈͰͳ͍͜ͱ͕ଟ͔ͬͨʢൃදऀݸਓͷҙݟͰ͢ʣ 
 
σʔλϞσϦϯάΛ੾Γޱʹͯ͠σΟεΧογϣϯ͢Δͱ 
खஈͱ໨త͕ೖΕସΘΓ΍͍͢Α͏ʹࢥ͑ΔʢൃදऀݸਓͷҙݟͰ͢ʣ
 
ࣗݾ౤ࢿͷֶशͳΒ͍͘ΒͰ΋পʹਁ͔Ε͹ྑ͍͕ 
ΞφϦετ͕ۀ຿ͰϑΥʔΧε͢Δͷ͸ඇਪ঑ʢൃදऀݸਓͷҙݟͰ͢ʣ

ɹσʔλϞσϦϯάͷপ

View Slide

1. ͸͡Ίʹ

ɹΞδΣϯμ

View Slide

Ͳ͏ͯ͠΋σʔλ΢ΣΞϋ΢ε΍
σʔλϚʔτΛઃܭ͍ͨ͠
 
ͱ͍͏ਓ޲͚ʹώϯτΛ͝঺հ 
ʢͪΌΜͱ஌͍ͬͯΔͱศརͳͷ͸ࣄ࣮ʣ 
ʢͦͷՁ஋͕͋Δ͔Βͦ͜਺ଟͷࢿྉ͕ੈʹग़͍ͯΔʣ

ɹͱ͸͍͑

View Slide

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

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

ɹInmon’s CPI & Kimball’s Dimensional Modeling

View Slide

 
 
“Star Schema The Complete Reference” Λ΋ͱʹղઆͨ͠ͱͷ͜ͱ 
ʢ೔ຊޠͰ୳ͨ͠ͱ͜Ζ͜ͷαΠτͷҰ࿈ͷهࣄ͕࠷΋ϑΣΞͰ؆ܿͩͬͨʣ 
http://www.dbstories.com/entry/datawarehouse_architecture_01

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

View Slide

ɾReadable & Practical ͳࢿྉ͕ଟ͍ 
ɾணख͠΍͍͢ “The Star Schema / Dimensional Modeling” 
 

ɹThe Kimball Architecture

View Slide

ɾཚ๫ʹݴ͏ͱʮୈೋਖ਼نܗʯͰʮJoin͠·ͬͯ͘ศརͳςʔϒϧΛ࡞Ζ͏ͥʯͷΨνٞ࿦ 
ɾݟΔࢦඪʢϑΝΫτʣʹ੾Δ࣠ʢσΟϝϯγϣϯʣΛ෇͚͍ͯ͘ 
ɾJoinͷදݱ͕੕ܕͰඳ͔ΕΔ͜ͱ͕ଟ͍ͷͰ “The Star Schema” 
 
ʰ10೥ઓ͑Δσʔλ෼ੳೖ໳ SQLΛ෢ثʹσʔλ׆༻࣌୅Λੜ͖ൈ͘ʱΑΓҾ༻

ɹThe Star Schema, Dimensional Modeling

View Slide

σʔλ΢ΣΞϋ΢εϞσϦϯάೖ໳ʢμΠδΣετ൛ʣ 
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

ɾஶऀ͸࣌୅എܠͷࠩҟ΍֤ࣾࣄ৘Λ 
ɹ౿·্͑ͨͰ࣮ફతͳελϯεΛఏࣔ 
 
ɾൃදऀʢࢲʣ͕ PyCon JP 2017 Ͱ 
ɹຊॻΛҾ༻͔ͯ͠Β2೥ 
 
ɾ͜ͷຊΛಡΊ͹ॻ͍ͯ͋Δ͜ͱΛ 
ɹࢲʹ࣭໰͞ΕΔ͜ͱ͕ଟʑ͋ͬͯ൵͍͠

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

View Slide

 
࢐৽ͳΞΠσΞΛࢥ͍͔ͭͳ͍͍ͯ͘
ແཧʹ஌͍ͬͯΔ৘ใΛ౰ͯ͸Ίͳ͍͍ͯ͘
ද໘తͳख๏࿦΍ΩʔϫʔυʹৼΓճ͞Εͳ͍͍ͯ͘
ΞφϦετ͕σʔλ෼ੳͰ஫ҙ͍ͯ͠Δ͜ͱΛ 
ಉ͡Α͏ʹ஫ҙͯ͠ɺ࢓ࣄΛਐΊΕ͹ͦΕͰ͍͍

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

View Slide

զʑຌਓ๊͕͑Δఔ౓ͷ೰Έ͸ 
ʢےͷྑ͍೰ΈͰ͋Ε͹ʣ
աڈʹڊਓୡ͕ٞ࿦͍ͯ͠Δ͸ͣ 

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

View Slide

ຊΛಡ΋͏

ɹ݁࿦

View Slide

1. ͸͡Ίʹ

ɹΞδΣϯμ

View Slide

ੜ͖࢒Δͷ͸͜ͷੈͷʮਅ࣮ʯ͚ͩͩ 
ਅ࣮͔Βग़ͨʮ੣ͷߦಈʯ͸ܾͯ͠໓ͼ͸͠ͳ͍
͓ͦͯ͠લͷߦಈ͕ਅ࣮͔Βग़ͨ΋ͷͳͷ͔
ͦΕͱ΋͏Θͬ໘͚ͩͷअѱ͔Βग़ͨ΋ͷͳͷ͔ 
ͦΕ͸͜Ε͔ΒΘ͔Δ
ʰδϣδϣͷحົͳ๯ݥʱΑΓ

ɹੜ͖࢒Δͷ͸ʁ

View Slide

When you have eliminated the impossible, 
whatever remains, however improbable, must be the truth.
“His Last Bow”
 
ෆՄೳΛফڈͯ͠࠷ޙʹ࢒ͬͨ΋ͷ͕ 
೗Կʹحົͳ͜ͱͰ͋ͬͯ΋ͦΕ͕ਅ࣮ͱͳΔ
 
ʰγϟʔϩοΫɾϗʔϜζ࠷ޙͷѫࡰʱΑΓ

ɹਅ࣮ͱ͸ʁ

View Slide

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

ɹෆՄೳΛফڈ͢Δʹ͸ʁ
͜ͷεςοϓΛվળ͢ΔࣄͰɺ 
શମͷίϯόʔδϣϯϨʔτ͕ 
վળ͢Δࣄ͸΄΅ͳ͍ͩΖ͏ɻ

View Slide

ࢹ࠲͕௿͍ूஂ͸ɺ͋͋ͩ͜͏ͩબ୒ࢶΛٞ࿦Ͱ͖Δ͕ɺ 
ͦΕ͸௿͍໨ඪʹࢸΔಓ͸ͨ͘͞Μ͋Δ͔Βʹա͗ͳ͍ɻ 
Ұ൪্ʹߦ͜͏ͱ͢Ε͹બ୒ࢶ͸΄ͱΜͲͳ͍ɻ
ࢲͷύϑΥʔϚϯεཧ࿦ vol.16 -ूஂʹ͍ͭͯ- 
http://tamesue.jp/blog/archives/think/20190422

ɹԿΛ΋ͬͯ൑அ͢Δʁ

View Slide

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

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

View Slide

ੈք࠷ݹͷσʔλج൫ [ཁग़య]

ɹ“ધ৐ΓͷḺ” ϝλϑΝʔ
ਫͷ࢒ྔ
໋ʹؔΘΔ࠷ॏཁ,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

 
ʮધ৐Γʹͱͬͯͷਫͷ࢒ྔʯʹϑΥʔΧε͠Α͏
໷΋຾Εͳ͘ͳΔΑ͏ͳ໰୊͸ԿͩΖ͏ʁ
ʢΠϯηϓγϣϯσοΩʣ
΋͠ΘΕΘΕ͕௥͍ग़͞Εɺऔక໾ձ͕৽͍͠CEOΛ 
೚໋ͨ͠ͱͨ͠Βɺͦͷਓ෺͸ɺ͍͍ͬͨͲΜͳࡦΛऔΔͱࢥ͏͔͍ʁ
ʢΞϯυϦϡʔɾSɾάϩʔϰʣ

ɹIssue Driven

View Slide

ɹData Lifecycle
ϓϩμΫτ
Ϣʔβʔ
DBɾϩά
ࢪࡦɾۀ຿
෼ࢄܕ 
σʔλετΞ 
e.g. BigQuery
ऩू ૄ௨
׆༻
Ձ஋
%BUB0QTʹ͓͍ͯ
࠷େԽ͢΂͖໨తม਺

View Slide

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

View Slide

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

View Slide

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

ʮ೔ຊͷDevOpsมֵΛଅਐ͢ΔόϦϡʔετϦʔϜϚοϐϯάʯΑΓҾ༻ 
http://gihyo.jp/dev/column/01/devops/2017/value-stream-mapping

View Slide

 
σʔλ࢓༷ʹৄ͍͠ʢগͳ͘ͱ΋ௐࠪ͢ΔεΩϧΛ΋͍ͬͯΔʣݱ৔୲౰ऀ͕ 
୲౰ྖҬΛ޿͛Δ͜ͱͰલޙ޻ఔͷϦʔυλΠϜΛ୹ॖ 

ɹݱ৔͕ओମతʹPDSαΠΫϧΛճ͢ʢ࠶ܝʣ
੡଄
ޮՌ 
ଌఆ
ཁ݅ 
ఆٛ
ཁٻ 
੔ཧ
੡଄
ޮՌ 
ଌఆ
ཁ݅ 
ఆٛ
ཁٻ 
੔ཧ
طʹ΍͍ͬͯΔ
෼ੳཁٻ ϩάཁ݅ ܭଌ
ΞΠσΞ

View Slide

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

View Slide

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

View Slide

ɹ͋ΒΏΔࢪࡦ͸KGIʹܨ͕Δ
૑଄͢Δ
Ձ஋ͷ࠷େԽ
੡඼ఏڙ
ू٬
Ξϩέʔγϣϯ࠷దԽ
αʔϏεϨϕϧͷҡ࣋
෼ੳۀ຿
தؒςʔϒϧ੔උ
σʔλෆ੔߹ͷղফ
ۀ຿ͷ2$%ޮ཰Λ 
վળ͢Δࢪࡦ
ଞͷࢪࡦ
ଞͷࢪࡦ
͜ͷࢪࡦͰಘΔ༧ఆͷReturn͸ʁ 
͜͜ʹ౤Լ͢΂͖Investment͸ʁ 
ΞφϦετͱͯ͠ΠϯύΫτΛ 
࠷େԽ͢Δҙࢥܾఆ͕Ͱ͖͍ͯΔ͔ʁ
બ୒ࢶΛͪΌΜͱݮΒ͍ͤͯΔ͔ʁ

View Slide

༨ܭͳબ୒ࢶʢʹϜμʣΛݮΒ͢ 
࡟͗མͱͨ͠ঢ়ଶʢʹϦʔϯʣͰۀ຿Λ਱ߦ͠Α͏
σΟεΧογϣϯλΠϜ͸ 
ʮ৽͘͠΍Δ͜ͱΛ୳͢ʯͷͰ͸ͳ͘ 
ʮ΍Βͳ͍͜ͱΛܾΊΔʯ৔ʹͯ͠΄͍͠
ʮֶͿ͜ͱʯΛ໨తԽ͢ΔͷͰ͸ͳ͘ 
ʮߦಈ͢Δ͜ͱʯΛݟਾ͑ͯ΄͍͠

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

View Slide

ɾYAGNIɿૣ͗͢Δ࠷దԽΛආ͚Δɻա৒࣮૷Λճආ͢Δɻ 
ɾϦʔϯ։ൃͷݪଇ4ɿܾఆΛͰ͖Δ͚ͩ஗ΒͤΔɻ 
ɾδϟετΠϯλΠϜɿඞཁͳͱ͖ʹɺඞཁͳ΋ͷΛɺඞཁͳ͚ͩ࡞Δɻ 
ɾRule of threeɿ3ճಉ͡࿩͕ग़ͨΒɺͦͷ3ճʹରԠͰ͖Δఔ౓ʹ൚༻తͳ࢓૊ΈΛ࡞Δɻ 
ɾεΫϥοϓˍϏϧυɿ࡞ͬͯ͸յ͢͏ͪʹܦݧ஋͕ཷ·Γɺ࣍ͷ࢓૊Έ͕ચ࿅͞ΕΔɻ 
ɾϘʔΠεΧ΢τݪଇɿͦͷ৔Ͱ͍ͭͰʹ௚͢ɻࢁΛ๚ΕͨΒɺΰϛ͸रͬͯɺ๚ΕΔલΑΓ៉ྷʹ͔ͯ͠ΒؼΔɻ 
ɾ৬ೳԣஅɿεΩϧͰ͸ͳ͘ΠγϡʔͰಈ͘ɻඞཁͳۀ຿Λɺඞཁͱ͢Δऀ͕ɺඞཁͳͱ͖ʹɺඞཁͳ͚ͩ਱ߦ͢Δɻ 
ɾϖΞϫʔΫɺϞϒϫʔΫɿྡͰҰॹʹ΍ΔɻεΩϧͷ͋ΔਓͱҰॹʹ࡞ۀ͢Ε͹ɺ͔࣍Βࣗ෼1ਓͰ࡞ۀͰ͖Δɻ

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

View Slide

ϜμΛল͜͏

ɹ݁࿦

View Slide

ද໘తͳख๏࿦΍Ωʔϫʔυʹ 
ৼΓճ͞Εͳ͍Ͱ΄͍͠
 
े෼ͳ৘ใऩूΛܦͯ
ΠγϡʔۦಈͷҙࢥܾఆΛͯ͠΄͍͠
ʮΞφϦετ͕ࢥ͍͍ͭͨཧ૝ͷج൫ʯΛ
ؔ܎ऀʹԡ͠෇͚ͳ͍Α͏ʹ৺ֻ͚ͯ΄͍͠

ɹ఻͍͑ͨ͜ͱʢ࠶ܝʣ

View Slide

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

View Slide

ͲͷϑΣʔζͰɺͲ͜Λ໨ࢦͯ͠ɺ 
Ͳͷ՝୊͕ੜͯ͡ɺͲͷଧͪखΛߨ͡Δ͔
੩తͳܭըॻͷࡦఆͱ९क͚ͩͰ͸ͳ͘
ಈతͳݕࠪͱదԠΛ܁Γฦ͢͜ͱ͕伴 
໨ͷલͷΠγϡʔΛݟۃΊͯղ͜͏
஍ಓʹϕετΛਚ͍ͯ͘͜͠͏

ɹ΍͍͖ͬͯ

View Slide

͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠
 
https://www.pexels.com/photo/autumn-clouds-conifers-daylight-221395/

View Slide

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

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

yuzutas0
PRO

More Decks by yuzutas0

Other Decks in Technology

Featured

Transcript

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

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

yuzutas0 PRO

More Decks by yuzutas0

Other Decks in Technology

Featured

Transcript

yuzutas0
PRO