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

ZOZOTOWNのDWHをRedshiftからBigQueryにお引越しした話 / Moving ZOZOTOWN DWH from Redshift to BigQuery

ZOZOTOWNのDWHをRedshiftからBigQueryにお引越しした話 / Moving ZOZOTOWN DWH from Redshift to BigQuery

Takehiro Shiozaki

February 14, 2019
Tweet

More Decks by Takehiro Shiozaki

Other Decks in Technology

Transcript

  1. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    גࣜձࣾ;0;0ςΫϊϩδʔζ
    ϚʔέςΟϯάΦʔτϝʔγϣϯνʔϜ
    Ԙ࡚݈߂
    ;0;0508/ͷ%8)Λ
    3FETIJGU͔Β#JH2VFSZʹ
    ͓Ҿӽͨ͠͠࿩

    View full-size slide

  2. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ࣗݾ঺հ
    ৽ଔͰ7"4*-:ʹೖࣾޙɺ."Λܦͯ;0;0ςΫϊϩδʔζ΁ɻ
    ੲ͸3BJMTͰXFC "1*Λ࡞ͬͨΓɺݕࡧαʔόʔͷύϑΥʔϚϯ
    ενϡʔχϯάΛ΍ͬͨΓɻ࠷ۙͷ͓࢓ࣄ͸σʔλج൫ͷ੔ཧͱ
    ͔ɺϝʔϧɾϓογϡ഑৴ج൫ͷ੔ཧͱ͔ɻ
    ;0;0ςΫϊϩδʔζ
    ϚʔέςΟϯάΦʔτϝʔγϣϯνʔϜ
    Ԙ࡚݈߂

    View full-size slide

  3. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ໨࣍
    wαʔϏε঺հ
    w3FETIJGU࣌୅ͷ঺հ
    w#JH2VFSZҠߦͷಈػ
    wσʔλϨΠΫҠߦ
    wσʔλϚʔτҠߦ
    wΠϯϑϥ঺հ
    w৽ͨʹੜͨ͡໰୊఺ͱͦͷղܾࡦ
    w͜Ε͔Βͷల๬
    w·ͱΊ
    ˞ٕज़ͷ࿩ଟΊͰ͢ɻΤϞ࿩͸΄΅ͳ͍Ͱ͢ɻ

    View full-size slide

  4. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ˓೔ຊ࠷େڃͷϑΝογϣϯγϣοϐϯάαΠτΞϓϦ
    ˓ Ҏ্ͷγϣοϓɺ Ҏ্ͷϒϥϯυͷऔΓѻ͍

    ʢ೥݄຤࣌఺ʣ
    ˓ৗ࣌ສ఺Ҏ্ͷ঎඼ΞΠςϜ਺ͱ

    ɹຖ೔ฏۉ ఺Ҏ্ͷ৽ண঎඼Λܝࡌ
    ˓ଈ೔഑ૹαʔϏεΪϑτϥοϐϯάαʔϏεπέ෷͍ͳͲ
    IUUQ[P[PKQ

    View full-size slide

  5. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ˓೔ຊ࠷େڃͷϑΝογϣϯίʔσΟωʔτΞϓϦ
    ˓ ສμ΢ϯϩʔυಥഁɺίʔσΟωʔτ౤ߘ૯਺͸ສ݅Ҏ্

    ʢͱ΋ʹ೥݄຤࣌఺ʣ
    ˓શੈքʢ"QQ4UPSF(PPHMFQMBZ͕ར༻ՄೳͳશͯͷࠃʣͰ

    ɹμ΢ϯϩʔυ͕Մೳ
    ˓ສਓҎ্ͷϑΥϩϫʔΛ࣋ͭϢʔβʔʢ8&"3*45"ʣ΋஀ੜ
    IUUQTXFBSKQ

    View full-size slide

  6. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ˓౰͕ࣾಠࣗʹ։ൃͨ͠࠾ੇ༻ϘσΟʔεʔπ
    ˓શମʹࢪ͞ΕͨυοτϚʔΧʔΛεϚʔτϑΥϯΧϝϥͰ౓ࡱ
    Ө͢Δ͜ͱͰɺମܕσʔλΛܭଌ
    ˓ܭଌͨ͠ମܕσʔλ͸ɺॠ࣌ʹ%ϞσϧԽ͞Εɺ;0;0508/Ξ
    ϓϦʹอଘɻ%Ϟσϧ͸͋ΒΏΔ֯౓ʹಈ͔͢͜ͱ͕Ͱ͖ɺମܕΛ
    ౓νΣοΫ͢Δ͜ͱ͕Մೳ
    IUUQ[P[PKQ[P[PTVJU

    View full-size slide

  7. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ˓ʮ;0;046*5ʯͰܭଌͨ͠ମܕσʔλΛ΋ͱʹɺ

    ɹҰਓͻͱΓͷମܕʹ߹ͬͨʮ͋ͳͨαΠζʯͷΞΠςϜ
    ˓ʮڀۃͷϑΟοτײʯΛ࣮ݱͨ͠ϕʔγοΫΞΠςϜΛఏڙ
    ɹɹάϩʔόϧαΠτʮ;0;0DPNʯͰւ֎ల։
    ˓ΞΠςϜ̩γϟπɺσχϜύϯπɺγϟπɺϏδωεεʔπɺ
    ωΫλΠɺϘʔμʔ̩γϟπɺ௕କΫϧʔωοΫ̩γϟπͳͲ
    IUUQ[P[PKQQC

    View full-size slide

  8. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    3FETIJGU࣌୅ͷ঺հ σʔλϑϩʔਤ

    4
    %#
    3FETIJGU
    %BUB1JQFMJOF
    %#
    5BMFOEͰసૹ

    ೔࣍όον

    ɾɾɾ
    0O1SFN%#T %BUB-BLF
    %BUB8BSF)PVTF
    ҎԼͷͭΛ؅ཧ
    ɾ4ˠ3FETIJGUͷσʔλసૹ
    ɾͦͷޙͷूܭΫΤϦͷ࣮ߦ

    View full-size slide

  9. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    4ʹೖΕ͍ͯͨσʔλ
    w;0;0508/΍8&"3ͷϚελσʔλ
    w Ϣʔβʔ৘ใ
    w ঎඼৘ใ
    w ஫จ৘ใ
    w(PPHMF"OBMZUJDT͔Βऔಘͨ͠ΞΫηεϩά 8FCɾωΠςΟϒΞϓϦ

    wϝʔϧ΍ϓογϡͷ഑৴ϩά
    w૯ςʔϒϧ਺
    w૯σʔλαΠζ5#

    View full-size slide

  10. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    0O1SFN%#Tˠ4΁ͷసૹʹ͸5BMFOEΛ࢖༻
    w5BMFOE0QFO4UVEJPͱ͍͏ແঈͷ&5- &YUSBDU 5SBOTGFS -PBE
    πʔϧΛ࢖༻
    w(6*Ͱ&5-ॲཧΛهड़͢Δ
    w5BMFOEͰهड़ͨ͠&5-όονΛ8JOEPXTλεΫεέδϡʔϥʔͰىಈ
    ͭͭͷΞΠίϯ͕࣮ߦ͢ΔॲཧΛදݱ
    ɾϑΝΠϧೖग़ྗ $47 &YDFM FUD

    ɾ%#ೖग़ྗ 42-4FSWFS 1PTUHSF42- 0SBDMF FUD

    ɾσʔλฤू ஔ׵ɺιʔτɺूܭͳͲ

    &5-ॲཧͷྫ

    View full-size slide

  11. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    4ˠ3FETIJGU΁ͷసૹʹ͸%BUB1JQFMJOFΛ࢖༻
    wσʔλϫʔΫϑϩʔΦʔέετϨʔγϣϯαʔϏε
    w4ˠ3FETIJGUͱ3FETIJGU্ͰͷूܭΫΤϦͷ࣮ߦΛ୲౰
    w3FETIJGU$PQZ"DUJWJUZͰ4ˠ3FETIJGUͷసૹΛ࣮ݱ
    wసૹ׬ྃޙʹ4RM"DUJWJUZͰ3FETIJGU্Ͱͷσʔλूܭͷ42-จΛ࣮ߦ
    w౰࣌ͷ໰୊఺
    w5BMFOEʹΑΔసૹ͕׬ྃͨ͠ޙʹ%BUB1JQFMJOF͕ಈ͘ඞཁ͕͋Δ͕ɺىಈ࣌ࠁ͸ܾΊଧͪ
    wσʔλϚʔτूܭͷͨΊͷ4RM"DUJWJUZಉ࢜ͷґଘײؔ܎͸ਓ͕ؒొ࿥

    View full-size slide

  12. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    #JH2VFSZҠߦ΁ͷಈػ
    w3FETIJGUͰ͸ҎԼͷ΋ͷΛ؅ཧ͢Δඞཁ͋Γ ϊ΢ϋ΢΋ෆ଍

    w ෼ࢄΩʔɾιʔτΩʔ
    w ετϨʔδͷۭ͖༰ྔ
    w ϊʔυ਺ɾΠϯελϯελΠϓ
    w#JH2VFSZͷํ͕ΑΓϚωʔδυͳαʔϏε
    w ΠϯσοΫεͱ͍͏֓೦ͳ͘ɺϚγϯύϫʔΛڧҾʹ࢖ͬͯϑϧεΩϟϯ
    w ॠؒతʹ͸୆Ҏ্ͷͷϊʔυͰܭࢉ͞ΕΔ͜ͱ΋͋Γ
    w ετϨʔδ͸࣮࣭ແݶͰ͍҆ 5#NPOUI

    wͱ͜ΖͰɺ"UIFOB͸ʁ
    w ೥݄ࠒ͸౰࣌͸ొ৔ͨ͠͹͔Γ
    w ΫΤϦͷ࣮ߦ଎౓͸#JH2VFSZͷํ͕ߴ଎
    w ˞σʔλͷอଘܗࣜΛྻࢦ޲ʹͨ͠Βߴ଎ʹͳ͍͔ͬͯͨ΋ʢʁʣ
    3FETIJGU #JH2VFSZ

    View full-size slide

  13. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    %BUB-BLFҠߦ
    4
    %#
    3FETIJGU
    %#
    5BMFOEͰసૹ

    ೔࣍όον

    ɾɾɾ
    0O1SFN%#T %BUB-BLF
    #JH2VFSZ
    4ʹอଘ͞Ε͍ͯΔDTWH[Λ
    #JH2VFSZʹ೔࣍Ͱϩʔυ

    View full-size slide

  14. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    4ˠ#JH2VFSZͷͨΊʹ࢖༻ͨ͠ཁૉٕज़
    w&NCVML
    w 5SFBTVSF%BUB੡ͷ044
    w &5-πʔϧ
    w େྔͷσʔλΛόονసૹ͢Δ͜ͱʹಛԽ
    w σʔλͷೖྗɾՃ޻ɾग़ྗΛ͢Δ෦෼͸ϓϥάΠϯͱͯ͠ఏڙ
    w ઃఆϑΝΠϧ͸:".-Ͱॻ͔Ε͍ͯΔ
    w%JHEBH
    w ͪ͜Β΋5SFBTVSF%BUB੡ͷ044
    w ϫʔΫϑϩʔ؅ཧπʔϧ
    w େྔͷ&NCVMLδϣϒΛޮ཰తʹ؅ཧ
    w ฒྻ࣮ߦɾϦτϥΠɾλεΫͷ࣮ߦঢ়گͷՄࢹԽɾ࣮ߦϩάͷදࣔ
    w ઃఆϑΝΠϧ͸:".- ૬౰ͷϑΥʔϚοτ
    Ͱॻ͔Ε͍ͯΔ

    View full-size slide

  15. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    &NCVMLͷ:".-঺հ
    in:
    type: file
    path_prefix: /path/to/csv_files
    decoders:
    - {type: gzip}
    parser:
    charset: UTF-8
    newline: LF
    type: csv
    delimiter: ','
    quote: '"'
    escape: '"'
    null_string: 'NULL'
    trim_if_not_quoted: false
    skip_header_lines: 1
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
    - {name: id, type: long}
    - {name: account, type: long}
    - {name: time, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
    - {name: purchase, type: timestamp, format: '%Y%m%d'}
    - {name: comment, type: string}
    out: {type: stdout}
    $47ϑΝΠϧΛTUEPVUʹग़ྗ
    సૹઃఆ͸͢΂ͯ:".-Ͱॻ͔Ε͍ͯΔ

    ҎԼͷ෦෼ΛϓϥάΠϯͰॻ͘͜ͱ͕ग़དྷΔ
    ɾJOQVU ϑΝΠϧγεςϜ΍4ͳͲ͔ΒಡΈग़͠

    ɾEFDPEFS ѹॖϑΝΠϧͷղౚͳͲ

    ɾQBSTFS σγϦΞϥΠζ

    ɾGJMUFS ϋογϡԽͳͲͷσʔλͷม׵ॲཧ

    ɾPVUQVU ϑΝΠϧγεςϜ΍4΁ͷॻ͖ग़͠

    ɾFODPEFS ϑΝΠϧͷѹॖͳͲ

    ɾGPSNBUUFS γϦΞϥΠζ

    View full-size slide

  16. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    &NCVMLͷ:".-঺հ XJUI-JRVJE

    in:
    type: file
    path_prefix: { env.FILENAME } # ①
    decoders:
    - {type: gzip}
    parser:
    {% include 'common/csv_format' %} # ②
    columns:
    - {name: id, type: long}
    - {name: account, type: long}
    - {name: time, type: timestamp, format: '%Y-%m-%d %H:%M:%S'}
    - {name: purchase, type: timestamp, format: '%Y%m%d'}
    - {name: comment, type: string}
    out: {type: stdout}
    $47ϑΝΠϧΛTUEPVUʹग़ྗ
    &NCVML͸-JRVJEςϯϓϨʔτΤϯδϯΛ౥ࡌ
    ҎԼͷ޻෉ʹΑͬͯ:".-ϑΝΠϧΛ%3:ʹͰ͖Δ
    ᶃ ؀ڥม਺Λ:".-ϑΝΠϧʹల։
    ᶄ ڞ௨͢ΔॲཧΛଞͷϑΝΠϧʹॻ͖ग़ͯ͠ಡΈࠐΈ
    JGͱ͔GPSͱ͔ͷ੍ޚߏจΛ࢖͏͜ͱ΋ग़དྷΔ

    View full-size slide

  17. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    %JHEBHͷϫʔΫϑϩʔ঺հ
    +my_task_1:
    sh>: "echo this task runs first."
    +my_task_2:
    sh>: "echo this task runs next."
    ஞ࣮࣍ߦ
    +run_if_param_is_true:
    if>: ${param}
    _do:
    echo>: ${param} == true
    ৚݅෼ذ
    +repeat:
    for_each>:
    fruit: [apple, orange]
    _do:
    echo>: ${fruit}
    ϧʔϓ
    +repeat:
    for_each>:
    fruit: [apple, orange]
    _parallel: true
    _do:
    echo>: ${fruit}
    ฒྻ࣮ߦ
    :".- ˞ݫີʹ͸ҧ͏
    ͰϫʔΫϑϩʔΛهड़
    खଓ͖ܕݴޠʹࣅͨงғؾ
    γʔέϯγϟϧͳϧʔϓΛฒྻ࣮ߦʹ੾Γସ͑Δͷ͕؆୯

    View full-size slide

  18. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    %JHEBHͷϫʔΫϑϩʔ঺հ XJUI3VCZ

    timezone: UTC
    +set_tables:
    require: tasks.rb
    rb>: set_tables
    +transfer_tables:
    for_each>:
    table: ${tables}
    _parallel: true
    _do:
    sh>: embulk run embulk/${table}.yml
    def set_tables
    tables = Dir.glob('embulk/*.yml')
    Digdag.env.store(tables: tables)
    end
    TBNQMFEJH UBTLTSC
    FNCVMLҎԼʹ֨ೲ͞Ε͍ͯΔ͢΂ͯͷసૹఆٛΛಡΈग़ͯ͠
    ͦΕΒΛҾ਺ʹͯ͠FNCVMLΛฒྻʹ࣮ߦ

    View full-size slide

  19. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ͳͥ5BMFOEΛ࢖͍ଓ͚ͳ͔ͬͨͷ͔
    w&NCVML%JHEBHΛ࢖͏͜ͱͰʮී௨ʯͷιϑτ΢ΣΞ։ൃͰͷϊ΢ϋ΢Λ׆͔͔ͨͬͨ͠
    w సૹઃఆͷϫʔΫϑϩʔͷ:".-ΛHJUIVCͰόʔδϣϯ؅ཧ
    w ຊ൪؀ڥ΁ग़͢લʹνʔϜ಺ͰϨϏϡʔ
    w $JSDMF$*ͳͲͷ$*4BB4Ͱࣗಈςετ
    w ຊ൪൓ө࡞ۀͷࣗಈԽ
    w%JHEBHΛ࢖͏͜ͱͰλεΫͷฒྻ౓ͷௐ੔Λ͠΍͔ͨͬͨ͘͢͠
    w &NCVML͸ՄೳͳݶΓϚϧνεϨουͰಈ࡞ͯ͠$16ΛߜΓਚ͘͢
    w $16ͷڧ͍ΠϯελϯεΛ૿΍ͯ͠ෳ਺୆Ͱ෼ࢄ࣮ߦͤ͞Δ͜ͱͰશςʔϒϧͷసૹ࣌ؒΛ୹ॖԽ

    View full-size slide

  20. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    σʔλϨΠΫҠߦखॱ
    4
    %#
    3FETIJGU
    %#
    5BMFOEͰసૹ

    ೔࣍όον

    ɾɾɾ
    0O1SFN%#T
    ᶃ 4ͷશσʔλDTWϑΝΠϧʹରͯ͠

    FNCVMLHVFTTΛ࣮ߦ͠సૹઃఆͷ਽ܗΛ࡞Δ
    ᶄ͢΂ͯͷϑΝΠϧΛ#JH2VFSZʹసૹͯ͠ΈΔ
    ᶅ3FETIJGUͱͷࠩ෼͕ͳ͘ͳΔ·ͰసૹઃఆΛमਖ਼

    View full-size slide

  21. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    3FETIJGUͱ#JH2VFSZͰͷσʔλࠩҟͷݪҼ
    wଟ͘ͷ৔߹͸&NCVMLͷ:".-Ͱͷܕࢦఆϛε
    w &NCVMLHVFTTͰ͸DTWϑΝΠϧͷεΩʔϚΛͷਫ਼౓ͰਪଌͰ͖ͳ͍ ܕ৘ใ͕ͳ͍$47ͷݶք

    w ઌ಄ߦͷΈΛΈͯεΩʔϚͷਪଌΛߦ͏
    w ϑΝΠϧͷޙํͰ༧૝֎ͷσʔλ͕͋Δ͜ͱ΋͋Δ͜ͱ΋͋Δ
    w &NCVMLͷ࣮ߦϩάͰεΩοϓ͞ΕͨߦΛ֬ೝͯ͠ɺ3FETIJGUͷεΩʔϚͱಥ͖߹Θͤ
    w ˞ࠓʹࢥ͑͹࠷ॳʹ3FETIJGUͷεΩʔϚͱͷಥ͖߹ΘͤΛ͓͚ͯ͠͹ྑ͔ͬͨɾɾɾ
    w&NCVMLͷDTWύʔαʔ͸จࣈྻதͷaΛਖ਼͘͠ύʔεग़དྷͳ͍
    w ԿճϦτϥΠͯ͠΋ফ͑ͳ͍ΤϥʔΛ͔֬ΊΔͨΊʹόΠφϦΤσΟλͰ֬ೝ
    w &NCVMLͷιʔείʔυΛ֬ೝͯ͠ݪҼʹؾ෇͘
    w (JU)VCʹ*TTVF͸্͕͍ͬͯΔ͚Ͳ௚͞Ε͍ͯͳ͍ɾɾɾ

    View full-size slide

  22. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    4ˠ#JH2VFSZͷసૹͷߴ଎ԽͷͨΊʹߦͬͨ͜ͱ
    wFNCVMLPVUQVUCJHRVFSZϓϥάΠϯͷσʔλసૹͷ༷ࢠ͸Լਤͷͱ͓Γ
    w($4ʹ഑ஔ͢ΔϑΝΠϧΛແѹॖʹ͢Δͱసૹ଎౓͕਺ഒߴ଎ʹͳͬͨ ᶅͷ෦෼

    w($4͔Β#JH2VFSZ΁ͷσʔλϩʔυ͕ฒྻʹͳͬͨ͜ͱ͕ཁҼ
    ৄࡉͳ࿩͸ͪ͜Β
    IUUQTRJJUBDPNTIJP[BLJJUFNTCFDG

    View full-size slide

  23. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ࠩ෼ߋ৽ͷ࢓૊Έ 3FETIJGU%BUBQJQFMJOF

    wҰ෦ͷڊେͳςʔϒϧ͸ຖ೔શྔߋ৽Λ͍ͯ͠ͳ͍
    w࠷ۙʹมߋͷ͋ͬͨߦͷΈ͕4ʹ഑ஔ͞ΕΔ
    w3FETIJGU$PQZ"DUJWJUZͷJOTFSU.PEFΦϓγϣϯͰࠩ෼ߋ৽Λ࣮ݱ
    w "11&/% ୯७ʹςʔϒϧʹ௥ه

    w 07&383*5&@&9*45*/(Λ࢖༻ ओΩʔΛࢦఆ͠ɺओΩʔͷॏෳ͕͋ͬͨΒ্ॻ͖

    View full-size slide

  24. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ࠩ෼ߋ৽ͷ࢓૊Έ #JH2VFSZ

    wಉ༷ͷࠩ෼ߋ৽ͷ࢓૊ΈΛ#JH2VFSZͰ࡞Δඞཁ͕͋Γ
    w"11&/%ํࣜͷࠩ෼ߋ৽
    w 4%,͔ΒΫΤϦΛ౤͛Δ࣌ͷXSJUFΦϓγϣϯͰBQQFOEΛࢦఆ
    w07&383*5&ํࣜͷࠩ෼ߋ৽
    w 8*/%08ؔ਺Ͱچςʔϒϧͷσʔλ͔৽ςʔϒϧͷσʔλ͔Λ൑ผ
    w ॏෳ͢Δ৔߹͸৽ςʔϒϧͷσʔλͷΈΛ࢒͢
    w ࢀߟ਺ඦ(#ͷσʔλΛ.Z42-͔Β#JH2VFSZʹಉظ͢Δ
    w IUUQTUFDINFSDBSJDPNFOUSZ
    w͜Εͩͱߋ৽ॲཧ͕ႈ౳ʹͳΒͳ͍ͷͰɺ΋͏Ұ೧Γͨ͠

    View full-size slide

  25. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ႈ౳ͳࠩ෼ߋ৽ͷ࢓૊Έ #JH2VFSZ

    w೔ຖͷόοΫΞοϓςʔϒϧΛ࡞Δ͜ͱͰࠩ෼ߋ৽Λႈ౳ʹ
    wᶃʙᶅͷͲͷॲཧͰམͪͯ΋҆શʹ࠷ॳ͔Β΍Γ௚͢͜ͱ͕Ͱ͖Δ
    w#JH2VFSZʹ͸τϥϯβΫγϣϯ͕ͳ͍ͷͰɺΫΤϦΛ౤͛ΔଆͰௐ੔
    ᶃDPQZ ᶅDPQZ
    ᶄBQQFOE
    UBCMF@OBNF@ UBCMF@OBNF@ UBCMF@OBNF
    table_yesterday = table('table_name_20181201')
    table_yesterday.copy('table_name_20181202') # ①
    table_today = table('table_name_20181202')
    query("集計クエリ", destination_table: 'table_name_20181202', write: 'append') # ②
    table_today.copy('table_name') # ③

    View full-size slide

  26. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    σʔλϚʔτͷҠߦ
    4
    %#
    3FETIJGU
    %#
    5BMFOEͰసૹ

    ೔࣍όον

    ɾɾɾ
    0O1SFN%#T
    #JH2VFSZ
    %BUB1JQFMJOF
    %BUB1JQFMJOFͰ࣮ߦ͞Ε͍ͯͨ42-Λ
    #JH2VFSZʹҠ২

    View full-size slide

  27. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    σʔλϚʔτͷҠߦ
    w3FETIJGUͷ42-Ͱॻ͔ΕͨσʔλϚʔτूܭ༻ͷΫΤϦΛ#JH2VFSZͷ42-ʹม׵
    w1PTUHSF42-ํݴͷ42-Λ#JH2VFSZํݴͷ42-ʹॻ͖׵͑
    wඞཁͳ΋ͷࠜؾ
    w/6--ΛؚΜͩͱ͖ͷ+0*/ͷڍಈ͕ҧͬͨΓ
    wؔ਺ͷҾ਺ͷॱ൪͕ҧͬͨΓ
    wҰ෦ͷؔ਺͸#JH2VFSZʹͳ͔ͬͨΓ
    wFUD

    View full-size slide

  28. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    σʔλϚʔτͷूܭͷґଘؔ܎ղܾ
    w͋ΔσʔλϚʔτ͕ผͷσʔλϚʔτͷूܭ݁ՌΛ࢖͍ͬͯΔ৔߹ɺґଘؔ܎͕ൃੜ
    wॱ൪͸कΓͭͭɺՄೳͳݶΓฒྻ౓͸্͍͛ͨ
    wNBLFίϚϯυͷKΦϓγϣϯΈ͍ͨͳײ͡
    EBUBNBSU
    EBUBNBSU
    EBUBNBSU
    4&-&$5ʙ'30.EBUBNBSU
    +0*/EBUBNBSUʙ
    EBUBNBSUͷूܭΑΓ΋ઌʹूܭ

    View full-size slide

  29. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    σʔλϚʔτͷूܭͷґଘؔ܎ղܾ
    σʔλϚʔτͷ42-จΛղੳͯ͠ґଘؔ܎Λάϥϑߏ଄ %"(
    ʹ͢Δ

    sql.scan(/(?:FROM|JOIN)\s+`(.+?)`/i)
    %"(Λղੳ͍͔ͯͭ͘͠ͷฒྻ࣮ߦՄೳͳάϧʔϓʹ෼ׂ
    " #
    #EFQFOETPO"
    ᶃ ᶄ

    View full-size slide

  30. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    σʔλϚʔτͷूܭͷґଘؔ܎ղܾ
    σʔλϚʔτͷ42-จΛղੳͯ͠ґଘؔ܎Λάϥϑߏ଄ %"(
    ʹ͢Δ

    sql.scan(/(?:FROM|JOIN)\s+`(.+?)`/i)
    %"(Λղੳ͍͔ͯͭ͘͠ͷฒྻ࣮ߦՄೳͳάϧʔϓʹ෼ׂ
    " #
    #EFQFOETPO"
    ᶃ ᶄ
    ݱࡏͷґଘؔ܎άϥϑ
    ਺ݸͷσʔλϚʔτ

    View full-size slide

  31. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    Πϯϑϥ঺հ σʔλϑϩʔਤ

    4
    %#
    #JH2VFSZ
    %#
    5BMFOEͰసૹ

    ೔࣍όον

    ɾɾɾ
    σʔλूܭ
    ϫʔΫϑϩʔ؅ཧ

    View full-size slide

  32. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    Πϯϑϥ঺հ αʔόʔߏ੒ਤ

    %BUB$FOUFS
    %JSFDU$POOFDU
    71$1FFSJOH
    0UIFS71$
    ($4
    #JH2VFSZ
    %JHEBHNBTUFS
    "VSPSB
    %JHEBHXPSLFS
    "VUP4DBMJOH

    ɾɾɾ
    4
    "-#

    View full-size slide

  33. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    Πϯϑϥ঺հ αʔόʔߏ੒ਤ

    %BUB$FOUFS
    %JSFDU$POOFDU
    71$1FFSJOH
    0UIFS71$
    ($4
    #JH2VFSZ
    %JHEBHNBTUFS
    "VSPSB
    %JHEBHXPSLFS
    "VUP4DBMJOH

    ɾɾɾ
    4
    "-#
    δϣϒΩϡʔ
    λεΫͷ࣮ߦঢ়گ
    ࣮ߦϩά
    αʔόʔΛ૿΍ͯ͠
    ฒྻ౓Ξοϓ
    λεΫͷ࣮ߦ؀ڥ͸
    %PDLFSίϯςφ

    View full-size slide

  34. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ৽ͨʹൃੜͨ͠໰୊ͱͦͷରॲ
    wσʔλอଘྉۚ௓Ͷ্͕Γ

    ɹˠఆظతʹݹ͍σʔλΛ࡟আ
    wΫΤϦྉۚ௓Ͷ্͕Γ

    ɹˠ1BSUJUJPOFE5BCMF
    wຊ൪ʹग़͢·ͰམͪΔ͔ෆ໌ͳΫΤϦ

    ɹˠNBTUFSϚʔδલʹ$JSDMF$*Ͱͷࣗಈςετ

    View full-size slide

  35. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    σʔλอଘྉۚ௓Ͷ্͕Γ
    wࠩ෼ߋ৽ͷ࣌ʹաڈͷςʔϒϧ͕࢒Γଓ͚Δ
    wͭͷςʔϒϧͷαΠζ͕਺5#
    w#JH2VFSZ͸ετϨʔδ͕ແ੍ݶͱ͸͍͓͑ۚ͸͔͔Δ
    wରॲ೔෇ͷݹ͗͢Δςʔϒϧͷࣗಈ࡟আ
    ᶃDPQZ ᶅDPQZ
    ᶄBQQFOE
    UBCMF@OBNF@ UBCMF@OBNF@ UBCMF@OBNF
    ແݶʹ૿͑Δաڈςʔϒϧ

    View full-size slide

  36. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ΫΤϦྉۚ௓Ͷ্͕Γ
    w͋ΔσʔλϚʔτͷεΩϟϯྔ͕਺5#ʹͳ͍ͬͯͨ
    w4&-&$5ͳͲͷअѱͳ͜ͱ͸͍ͯ͠ͳ͍
    w୯ʹඇৗʹॎ௕ͳϩάςʔϒϧͩͬͨ ਺೥෼

    wඞཁͳσʔλ͸௚ۙͷ΋ͷ͚ͩ
    wରॲ5JNF1BSUJUJPOFEςʔϒϧ
    w5*.&45".1ܕͷྻΛࢦఆͯ͠ύʔςΟγϣϯ෼ׂ
    w8)&3&۟ͰߜΓࠐΉ͜ͱͰεΩϟϯྔΛ཈͑ΒΕΔ

    View full-size slide

  37. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ຊ൪ʹग़͢·ͰམͪΔ͔ෆ໌ͳΫΤϦ
    wσʔλϚʔτ࡞੒༻ͷΫΤϦͷϛε͕සൃ
    wจࣈίʔυ͕4+*4
    wςʔϒϧ໊Λࢀর͢Δͱ͖ͷϓϩδΣΫτ໊๨Ε
    w॥؀ࢀর͢ΔσʔλϚʔτ
    wFUD
    w$JSDMF$*ͰࣗಈςετΛͯ͠ຊ൪ʹग़͢લʹݕ஌
    164) */70,&
    $PEF%FQMPZ
    $PEF1JQFMJOF
    */70,&
    ϫʔΫϑϩʔͷຊ൪൓ө

    View full-size slide

  38. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    #JH2VFSZͷར༻Λ͓ߟ͑ͷํ΁ͷΞυόΠε
    w#JH2VFSZ͸ύϫϑϧ͗͢ΔͷͰɺ՝ֹۚʹ͝༻৺
    w ਺5#ͷσʔλΛҰॠͰεΩϟϯ͢Δ
    w 2VPUBͷઃఆ
    w ՝ֹۚΛຖ೔νΣοΫ
    w೔ຊޠυΩϡϝϯτΛ৴༻͠ͳ͍
    w ӳޠ൛ʹ͔͠ଘࡏ͠ͳ͍ػೳ
    w ӳޠ൛Ͱ͸#FUBͷදه͕औΕ͍ͯΔػೳ
    wӳޠυΩϡϝϯτ͢Β৴༻͠ͳ͍
    w ࠷ऴతʹҰ൪৴པͰ͖Δͷ͸4%,ͷιʔείʔυͱ3&45"1*ͷυΩϡϝϯτ
    w 4%,ͷ࢖͍ํΛ஌Γ͍ͨ৔߹͸ɺυΩϡϝϯτͰ͸ͳ͘6OJU5FTUΛಡΈ·͠ΐ͏
    w ˞3VCZ൛ͷ4%,͚ͩͷ໰୊͔΋͠Εͳ͍

    View full-size slide

  39. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ͜Ε͔Βͷల๬
    4
    %#
    #JH2VFSZ
    %#
    5BMFOEͰసૹ

    ೔࣍όον

    ɾɾɾ
    σʔλूܭ
    ϫʔΫϑϩʔ؅ཧ
    ͜ͷ෦෼΋&NCVMLʹஔ͖׵͑
    4Λհͣ͞ʹ௚઀#JH2VFSZʹసૹ
    ΦϯϓϨ%#Ͱߦ͍ͬͯΔूܭॲཧΛ
    #JH2VFSZʹΦϑϩʔυ

    View full-size slide

  40. Copyright © ZOZO Technologies, Inc. All Rights Reserved.
    ·ͱΊ
    w;0;0508/ͷ%8)Λ3FETIJGU͔Β#JH2VFSZʹҾͬӽͨ͠͠
    w#JH2VFSZʹͨ͜͠ͱʹΑͬͯӡ༻ෛՙͷ௿ݮʹܨ͕ͬͨ
    w3FETIJGU࣌୅ͱൺ΂ͯਓ͕ؒؾʹ͢Δඞཁͷ͋ΔʮϞϊʯ͕ݮͬͨ
    w׬શϚωʔδυαʔϏεͳͷͰྗٕΛ࢖͍΍͍͢
    w·ͩ·ͩ΍Γ͍ͨ͜ͱ͕ଟ͗ͯ͢ਓ͕଍Γͳ͍
    wϒʔε΋ग़͍ͯ͠ΔͷͰؾܰʹ༡ͼʹདྷ͍ͯͩ͘͞ʙ
    wIUUQTUFDI[P[PDPNSFDSVJU

    View full-size slide