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

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

yuzutas0
September 05, 2019

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

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

yuzutas0

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/  
  2. WEBʹެ։͠·͢ #DPM
 ɹࡱӨ΍ϝϞ͸ෆཁͰ͢ɻϦϥοΫεͯ͠ฉ͍͍͚ͯͨͩΕ͹ͱࢥ͍·͢ɻ
 εϥΠυ 140+ ຕ / 15min
 ɹؤுͬͯ෇͍͖͍ͯͯͩ͘͞ɻࠓ೔͸͚ͬ͜͏͍͚Δؾ͕͠·͢ɻ
 ɹखݩͷࢿྉ܈Λίϐϖ

    + ԥΓॻ͖ͨ͠εϥΠυͰ͢ɻաڈࢿྉ܈ͱಉ͡಺༰Ͱ͢ɻ
 ɹਓؒ͸௥͍٧ΊΒΕΔͱ͜͏͍͏εϥΠυΛ࡞ͬͯ͠·͏ऑ͍ੜ͖෺ͳͷͰ͢ɻ Α΋΍·࿩Ͱ͢ʂϙΤϜ࿮Ͱ͢ʂ
 ɹ͓ञΛҿΈͳ͕Βগਓ਺Ͱμϥμϥͱ࿩ͨ͢ΊͷωλͰ͢ɻ
 ɹ࢒೦ͳ͕Βཱ೿ͳൃදࢿྉͰ͋Γ·ͤΜɻϙουΩϟετͩͱࢥͬͯฉ͍͍ͯͩ͘͞ɻ   ɹҿΜͰ͍͜͏ͥʂ IUUQTXXXQFYFMTDPNQIPUPBMDPIPMJDCFWFSBHFCBSCFFSESJOL
  3. 1. ͸͡Ίʹ 2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ
 3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ
 4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ
 5. Guide

    to Data Warehouse & Mart Designʢࣗ࿦ฤʣ
 6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ͸   ɹΞδΣϯμ
  4.   ɹྫ: ਐԽతσʔλϞσϦϯά .BSU ར༻ऀ޲͚*' 㲈σʔλϚʔτ 8BSFIPVTF υϝΠϯ஌ࣝΛද͢σʔλ 㲈σʔλ΢ΣΞϋ΢ε

    4PVSDF ݩσʔλͷίϐʔ 㲈σʔλϨΠΫ σʔλύΠϓϥΠϯʹ͓͍ͯػց͕ॲཧ͢ΔྲྀΕ ࣄۀ΍γεςϜͷ
 શσʔλΛίϐʔ͢Δ μογϡϘʔυ΍
 ϨϙʔτͱରʹͳΔ ։ൃϓϩηεʹ͓͍ͯਓ͕ؒઃܭ͢ΔྲྀΕ ूܭɾ෼ੳύλʔϯΛ੔ཧ
 㲈8*5)۟ͷසग़σʔλ ᶃ ᶄ ᶅ ᶃ ᶃ ᶄ
  5.   ɹຊ೔࿩͢͜ͱ Ͷ͐ɺࠓ͔Β࿩͢Αʂ ֓ཁ ݁࿦  ߏங௚ޙʹΑ͋͘Δࣦഊࣄྫ΍ઃܭϛε ࠓΛੜ͖Α͏ 

    σʔλ΢ΣΞϋ΢εɾϚʔτͷσβΠϯ ຊΛಡ΋͏  ͜ͷઌੜ͖ͷ͜Δʹ͸ ϜμΛল͜͏
  6. 1. ͸͡Ίʹ 2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ
 3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ
 4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ
 5. Guide

    to Data Warehouse & Mart Designʢࣗ࿦ฤʣ
 6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ͸   ɹΞδΣϯμ
  7. ͜ͷਤΛॻ্͍ͨͰ߈ΊΔॱ൪ΛܾΊΔ / ࢒Δબ୒ࢶ͸ଟ͘ͳ͍͸ͣ   ɹࠜڌΛ΋ͬͯҙࢥܾఆ͠Α͏ ଧͪखᶃ YYYYY ଧͪखᶄ YYYYY

    ଧͪखᶅ YYYYY ޮՌ਺ࣈ ίετ ϦεΫ σϦόϦ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ޮՌମݧ ̋˚✕ ̋˚✕ ̋˚✕ ޮՌ࣋ଓ ̋˚✕ ̋˚✕ ̋˚✕
  8.   ɹ෼ੳऀ͕ݟ΍͍͢Α͏ʹઃܭ 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  Ωʔ৘ใΛ
 ࡟͗མͱͨ͠
  9. தؒςʔϒϧͷར༻ऀ਺ = σʔλར༻ऀ਺ * தؒςʔϒϧར༻཰   ɹσʔλͷຽओԽ͸ग़དྷ͍ͯΔ͔ʁ νʔϜ" νʔϜ#

    νʔϜ$ νʔϜ% νʔϜ& νʔϜ' νʔϜ( νʔϜ) νʔϜ* νʔϜ+ νʔϜ, ੜϩά
 ಠࣗར༻ σʔλ5ࢧԉ
 ۀ຿ґཔ σʔλ5ࢧԉ
 σʔλग़ྗ ࣗओత
 σʔλग़ྗ ୲౰ऀґଘ ୲౰ऀґଘ ୲౰ऀґଘ ࣗओత
 σʔλੜ੒ ଞνʔϜґཔ ج൫ߩݙʂ ୲౰ऀґଘ ୲౰ऀґଘ ہॴԽͷน ࣗ૸ͷน վળͷน https://speakerdeck.com/yuzutas0/20180402
  10.   ɹαϯϓϧ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
  11. 1. ͸͡Ίʹ 2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ
 3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ
 4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ
 5. Guide

    to Data Warehouse & Mart Designʢࣗ࿦ฤʣ
 6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ͸   ɹΞδΣϯμ
  12.   ɹݖݶ؅ཧͱϙϦγʔఏࣔʢྫʣ jobUser N/A ˚ ̋ dataViewer N/A ̋

    ̋ dataEditor N/A ✕ ̋ γεςϜࢀর Read Only Read & Write ౿Έ୆ σʔλϨΠΫ σʔλϚʔτ GCS BigQuery Project A BigQuery
 Project B
  13.   ɹࣗಈԽͳΒ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]”,] }

  14.   ɹखॱॻ͸Ձ஋ʹ௚݁͢Δ ɹɹɹɹར༻Ҋ಺ɾखॱॻʢ0QTࢹ఺ʣ #BE (PPE πʔϧ
 ʢ%FWࢹ఺ʣ (PPE ࡞ͬͨਓ͔͠࢖͑ͳ͍


    ʢຊਓʹ͸ศརʣ ࠷ߴ #BE ࡞ͬͨਓ͔͠࢖͑ͳ͍
 ʢຊਓʹ΋ෆศʣ ·͊࢖͑Δ ͕Μ͹ͬͯͬͪ͜Λ
 ໨ࢦ͕ͪ͠ ͬͪ͜ͷ΄͏͕؆୯Ͱ
 ޮՌ΋ߴ͍ʢ͔΋ʣ
  15.   ɹυΩϡϝϯτΛίʔυͷΑ͏ʹѻ͏16ͷύλʔϯ (Document Design Pattern) 
 • Ϣʔεέʔε෼ੳ
 •

    ϦϑΝΫλϦϯά
 • ϖΞϓϩάϥϛϯά
 • Ϟϒϓϩάϥϛϯά
 • όʔδϣϯ؅ཧ
 
 • ΞʔΩςΫτνʔϜ
 • ίϯ΢ΣΠͷ๏ଇ
 ମ੍ͷύλʔϯ 
 • ٕज़తෛ࠴
 • ϙʔτϑΥϦΦ
 • ίʔσΟϯάΛଅ͢
 • ίʔυϨϏϡʔ
 • ϘʔΠεΧ΢τ
 • Rule of Three จԽͷύλʔϯ 
 • MVCϞσϧ
 • GoFσβΠϯύλʔϯ
 • ܧଓతσϦόϦʔ /
 ΠςϨʔγϣϯ ߏ੒ͷύλʔϯ ࡞ۀͷύλʔϯ ˞ମ੍΍จԽ͸σβΠϯʢઃܭʣʹӨڹΛ༩͑ΔͷͰσβΠϯύλʔϯʹؚΊ͍ͯ·͢ ࢀরʰDevOpsͱυΩϡϝϯτσβΠϯύλʔϯʱ http://yuzutas0.hatenablog.com/entry/2017/07/06/083000
  16. ͜ͷॱ൪Ͱࢥߟ͠·͠ΐ͏   ɹΞφϦετ΋SWΤϯδχΞ΋ಉ͡ Ұൠతͳ࿦఺ ࠓճͷ͓୊ 8IBU ԿΛୡ੒͍͔ͨ͠ʁ ٻΊΔߋ৽ස౓͸ʁ 8IFSF

    Ͳ͜ʹ໰୊͕͋Δ͔ʁ ͲͷՕॴͷॲཧ͕஗͍͔ʁ 8IZ ͳͥ໰୊͕ى͖͍ͯΔ͔ʁ ͳͥͦͷॲཧ͕஗͍͔ʁ )PX ͲͷΑ͏ʹղܾ͢Δ͔ʁ ͲͷΑ͏ʹνϡʔχϯά͢Δ͔ʁ
  17. 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)

  18. ࢀߟʰσʔλϚωδϝϯτ஌ࣝମܥΨΠυʱ   ɹσʔλ඼࣭Λ୲อ͢Δ ඼࣭ ςετίʔυ ߋ৽؂ࢹ ஗Ԇঢ়گՄࢹԽ ݅਺Ұக؂ࢹ σʔλϨΠΫ

    ੜ੒ςʔϒϧ ҆શੑ
 ʢϚΠφεˠʣ ਖ਼֬ੑ ׬શੑ Ұ؏ੑ ੔߹ੑ ଥ౰ੑ ద࣌ੑ Ұҙੑ ༗ޮੑ རศੑ
 ʢˠϓϥεʣ
  19.   ɹSLA σʔλͷ༻్ɾར༻ऀ͝ͱʹظ଴͞Ε͍ͯΔαʔϏεϨϕϧΛՄࢹԽ ɾ৴པੑ͸ʮ࣮ࡍʹγεςϜΛ࢖͏ਓɾ໨తɾ࣌ؒʯΛલఏʹ͢Δ
 ɾͳͷͰαʔϏεϨϕϧ͸ϢʔεέʔεۦಈͰઃܭ͢Δ͜ͱʹͳΔ
 ɾ୭΋࢖Θͳ͍ػೳ΍࣌ؒଳͰγεςϜՔಇ཰͕Ͱ͋ͬͯ΋࢓ํͳ͍ ྫ ༻్ ໿ଋ૬ख

    ࿈བྷઌɾप஌ઌ ར༻σʔλ ໿ଋࣄ߲ʢ4-"ʣ ҧ൓࣌ͷӨڹൣғ  ೔࣍Ϩϙʔτ σΟϨΫλʔ 4MBDL
 NPOJUPSJOH #JH2VFSZͷ
 ച্ςʔϒϧ ຖӦۀ೔ͷޕલY࣌·Ͱʹ
 ܽଛͳ͘લ೔ͷച্͕
 Ϩϙʔτ͞ΕΔ͜ͱ ച্ঢ়گʹԠͨ͡
 ࢪࡦ͕ଧͯͳ͘ͳΔ
 ʢػձଛࣦʣ  ʜ ʜ ʜ ʜ ʜ ʜ  ʜ ʜ ʜ ʜ ʜ ʜ ʜ ʜ ʜ ʜ ʜ ʜ ʜ ࣮෺ˠ
  20. 1. ͸͡Ίʹ 2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ
 3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ
 4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ
 5. Guide

    to Data Warehouse & Mart Designʢࣗ࿦ฤʣ
 6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ͸   ɹΞδΣϯμ
  21.   ɹσʔλʹ͸ੜ੒ݩ͕͋Δ ΞϓϦ Ϣʔβʔ %# #2 ͜͜ʹෆ੔߹͕͋ΔͳΒ ঎༻؀ڥ Ͳ͔͜Ͱ໰୊͕ى͖͍ͯΔ


    ʢՄೳੑ͕͋Δʣ ͚ͩ͜͜Λ௚ͯ͠΋ຊ຤స౗
 ͦͷҙࢥܾఆ͸ຊ౰ʹ,(*ʹج͍͍ͮͯΔ͔
 σʔλۦಈͩͱݴ͑Δͷ͔ ఏڙՁ஋Λ࠷େԽ͢ΔͨΊʹ
 σʔλ෼ੳΛ࢝Ίͨ͸ͣ ྑ͍ϓϩμΫτΛ࡞ΔͨΊʹ
 σʔλ෼ੳΛ࢝Ίͨ͸ͣ
  22. ߹ΘͤͯಡΈ͍ͨɿʮΞΠσΞΛग़͢͜ͱ͕اըͩͱࢥͬͯΔౕ͸100ສճࢮΜͰ͍͍ʯ
 http://dochikushow.blog3.fc2.com/blog-entry-3003.html   ɹཤྺ΋ؚΊͯϓϩμΫτϚωδϝϯτ ๏ྩ९कɾফඅऀอޢ ໰͍߹ΘͤରԠ ߪೖ௚ޙʹ঎඼ͷઆ໌จ͕
 Ξοϓσʔτ͞ΕͨΒʁ
 Ԡื௚ޙʹٻਓͷใुཝ͕


    Ξοϓσʔτ͞ΕͨΒʁ
 
 ʮݴͬͨʯʮݴΘͳ͍ʯͷ
 ໰୊͕ى͖͏ΔͷͰ
 ه࿥อ࣋Λٛ຿෇͚Δ
 ๏཯ɾΨΠυϥΠϯ͸ଟ͍ ཤྺΛௐ্ࠪͨ͠Ͱ
 ճ౴Λฦ͢ػձ͕ଟ͍ 
 ΧελϚʔαϙʔτπʔϧͷ
 ػೳཁ݅ͱͯ͠
 ཤྺอ͕࣋ٻΊΒΕΔ
 
 ໰͍߹ΘͤରԠ΋ؚΊͯ
 ϓϩμΫτͷར༻ମݧ
  23. Ҋ݅ͷ໨త → ର৅ࢦඪ → ܭଌํ๏ → ϩάཁ݅ ͸࠷ॳʹ཈͑Δ  

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


    ఆٛ ཁٻ
 ੔ཧ ੡଄ ઃܭ ࢼݧ ϦϦʔε ޮՌ
 ଌఆ ཁ݅
 ఆٛ ཁٻ
 ੔ཧ طʹ΍͍ͬͯΔ ෼ੳཁٻ ϩάཁ݅ ܭଌ ΞΠσΞ ΞφϦετͰ͸ͳ͘։ൃνʔϜ͕ࣗલͰ΍Δ͜ͱʹҙຯ͕͋Δ
 εΫϥϜϚελʔͷσʔλ׆༻൛ͱͯ͠ৼΔ෣͏ 
 σʔλ࢓༷ʹৄ͍͠ʢগͳ͘ͱ΋ௐࠪ͢ΔεΩϧ͸΋͍ͬͯΔʣΤϯδχΞ͕
 ୲౰ྖҬΛ޿͛Δ͜ͱͰલޙ޻ఔͷϦʔυλΠϜΛ୹ॖ
  25. 1. ͸͡Ίʹ 2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ
 3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ
 4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ
 5. Guide

    to Data Warehouse & Mart Designʢࣗ࿦ฤʣ
 6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ͸   ɹΞδΣϯμ
  26.   ɹઃܭ࿦ʢ࠶ܝʣ .BSU ར༻ऀ޲͚*' 㲈σʔλϚʔτ 8BSFIPVTF υϝΠϯ஌ࣝΛද͢σʔλ 㲈σʔλ΢ΣΞϋ΢ε 4PVSDF

    ݩσʔλͷίϐʔ 㲈σʔλϨΠΫ σʔλύΠϓϥΠϯʹ͓͍ͯػց͕ॲཧ͢ΔྲྀΕ ࣄۀ΍γεςϜͷ
 શσʔλΛίϐʔ͢Δ μογϡϘʔυ΍
 ϨϙʔτͱରʹͳΔ ։ൃϓϩηεʹ͓͍ͯਓ͕ؒઃܭ͢ΔྲྀΕ ूܭɾ෼ੳύλʔϯΛ੔ཧ
 㲈8*5)۟ͷසग़σʔλ ᶃ ᶄ ᶅ ᶃ ᶃ ᶄ
  27.   ɹ͚ͩ͜͜Λ཈͑Δ ҆શ໘ ࣦഊͯ͠΋΍Γ௚ͤΔΑ͏ʹ
 ੔ܗલσʔλͷίϐʔΛ஝ੵ͢Δ σʔλϨΠΫ ޮ཰໘ ϦϑΝΫλϦϯά͠΍͍͢Α͏ʹ
 ಉҰͷٕज़ཁૉͰ؅ཧ͢Δ

    SQL on Worflow Engine ܧଓ໘ ʮվળʯΛश׳ԽͰ͖ΔΑ͏ʹ
 σʔλར༻ऀ͕ϓϩάϥϛϯάͱϦʔϯͷݪଇΛ
 ֶ΂ΔΑ͏ࢧԉ͢Δ σʔλͷຽओԽ
 ʢΞδϟΠϧίʔνεΫϥϜϚελʔʣ
  28. ͜ͷਤΛॻ্͍ͨͰձ࿩Ͱ͖͍ͯΔ͔ʁ   ɹ͋͘·Ͱଧͪखͷ1ͭ *TTVF ଧͪखᶃ YYYYY ଧͪखᶄ YYYYY ଧͪखᶅ

    தؒςʔϒϧ ޮՌ ίετ ϦεΫ σϦόϦ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕ ̋˚✕
  29. 1. ͸͡Ίʹ 2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ
 3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ
 4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ
 5. Guide

    to Data Warehouse & Mart Designʢࣗ࿦ฤʣ
 6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ͸   ɹΞδΣϯμ
  30. 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
  31. ɾஶऀ͸࣌୅എܠͷࠩҟ΍֤ࣾࣄ৘Λ
 ɹ౿·্͑ͨͰ࣮ફతͳελϯεΛఏࣔ
 
 ɾൃදऀʢࢲʣ͕ PyCon JP 2017 Ͱ
 ɹຊॻΛҾ༻͔ͯ͠Β2೥
 


    ɾ͜ͷຊΛಡΊ͹ॻ͍ͯ͋Δ͜ͱΛ
 ɹࢲʹ࣭໰͞ΕΔ͜ͱ͕ଟʑ͋ͬͯ൵͍͠   ɹ͜ͷ͋ͨΓͷ࿩ɺ͍͍ͩͨॻ͍ͯ͋Δ
  32. 1. ͸͡Ίʹ 2. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢOutputฤʣ
 3. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢProcessฤʣ
 4. ߏங௚ޙʹΑ͋͘Δ૬ஊτϐοΫʢInputฤʣ
 5. Guide

    to Data Warehouse & Mart Designʢࣗ࿦ฤʣ
 6. Guide to Data Warehouse & Mart DesignʢҰൠ࿦ฤʣ 7. ͜ͷઌੜ͖ͷ͜Δʹ͸   ɹΞδΣϯμ
  33. When you have eliminated the impossible,
 whatever remains, however improbable,

    must be the truth. “His Last Bow” 
 ෆՄೳΛফڈͯ͠࠷ޙʹ࢒ͬͨ΋ͷ͕
 ೗Կʹحົͳ͜ͱͰ͋ͬͯ΋ͦΕ͕ਅ࣮ͱͳΔ 
 ʰγϟʔϩοΫɾϗʔϜζ࠷ޙͷѫࡰʱΑΓ   ɹਅ࣮ͱ͸ʁ
  34. ੈք࠷ݹͷσʔλج൫ [ཁग़య]   ɹ“ધ৐ΓͷḺ” ϝλϑΝʔ ਫͷ࢒ྔ ໋ʹؔΘΔ࠷ॏཁ,1* ʮҰʯͷઢ ଈ࠲ʹಡΊΔ௚ײతͳ6*

    અ໿PSফඅ Ξϩέʔγϣϯͷҙࢥܾఆ ۩ମతͳߦಈʹ௚݁ https://www.irasutoya.com/2015/12/blog-post_694.htmlɹhttps://www.irasutoya.com/2013/05/blog-post_2062.html
  35.   ɹData Lifecycle ϓϩμΫτ Ϣʔβʔ DBɾϩά ࢪࡦɾۀ຿ ෼ࢄܕ
 σʔλετΞ


    e.g. BigQuery ऩू ૄ௨ ׆༻ Ձ஋ %BUB0QTʹ͓͍ͯ ࠷େԽ͢΂͖໨తม਺
  36. ޻ఔʢδϣϒʣ ʹ͓͍ͯ “ϦλʔϯΛ্͛Δ” or “ίετΛԼ͛Δ” ɹData Driven Development / Ұൠݪଇ

    ۀ຿" ۀ຿# ۀ຿$ ΦϖϨʔγϣϯ ΦϖϨʔγϣϯ ΦϖϨʔγϣϯ ҙࢥܾఆ ҙࢥܾఆ ҙࢥܾఆ Ձ஋ #*.-Ҋ݅
 ΞφϦςΟΫε "1.-Ҋ݅
 ιϦϡʔγϣϯ  
  37. ΧελϚʔδϟʔχʔ ʹ͓͍ͯ “ϦλʔϯΛ্͛Δ” or “ίετΛԼ͛Δ” ɹData Driven Development for User

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

    ྫɿϚʔέςΟϯάۀ຿΍ΧελϚʔαϙʔτۀ຿ͷҰ෦ࣗಈԽͳͲ   ʮ೔ຊͷDevOpsมֵΛଅਐ͢ΔόϦϡʔετϦʔϜϚοϐϯάʯΑΓҾ༻
 http://gihyo.jp/dev/column/01/devops/2017/value-stream-mapping
  39. 
 σʔλ࢓༷ʹৄ͍͠ʢগͳ͘ͱ΋ௐࠪ͢ΔεΩϧΛ΋͍ͬͯΔʣݱ৔୲౰ऀ͕
 ୲౰ྖҬΛ޿͛Δ͜ͱͰલޙ޻ఔͷϦʔυλΠϜΛ୹ॖ
   ɹݱ৔͕ओମతʹPDSαΠΫϧΛճ͢ʢ࠶ܝʣ ੡଄ ઃܭ ࢼݧ ϦϦʔε

    ޮՌ
 ଌఆ ཁ݅
 ఆٛ ཁٻ
 ੔ཧ ੡଄ ઃܭ ࢼݧ ϦϦʔε ޮՌ
 ଌఆ ཁ݅
 ఆٛ ཁٻ
 ੔ཧ طʹ΍͍ͬͯΔ ෼ੳཁٻ ϩάཁ݅ ܭଌ ΞΠσΞ
  40.   ɹ͋ΒΏΔࢪࡦ͸KGIʹܨ͕Δ ૑଄͢Δ Ձ஋ͷ࠷େԽ ੡඼ఏڙ ू٬ Ξϩέʔγϣϯ࠷దԽ αʔϏεϨϕϧͷҡ࣋ ෼ੳۀ຿

    தؒςʔϒϧ੔උ σʔλෆ੔߹ͷղফ ۀ຿ͷ2$%ޮ཰Λ
 վળ͢Δࢪࡦ ଞͷࢪࡦ ଞͷࢪࡦ ͜ͷࢪࡦͰಘΔ༧ఆͷReturn͸ʁ
 ͜͜ʹ౤Լ͢΂͖Investment͸ʁ
 ΞφϦετͱͯ͠ΠϯύΫτΛ
 ࠷େԽ͢Δҙࢥܾఆ͕Ͱ͖͍ͯΔ͔ʁ બ୒ࢶΛͪΌΜͱݮΒ͍ͤͯΔ͔ʁ