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

BigData-JAWS #9: Data Analytics Infra for CrowdWorks

BigData-JAWS #9: Data Analytics Infra for CrowdWorks

Masayuki Morita

October 11, 2017
Tweet

More Decks by Masayuki Morita

Other Decks in Technology

Transcript

  1. Ϋϥ΢υϫʔΫεΛࢧ͑Δ
    σʔλ෼ੳج൫
    #JH%BUB+"84

    !NJOBNJKPZP

    View full-size slide

  2. ࣗݾ঺հ
    w ৿ాਅ೭ .BTBZVLJ.PSJUB

    w 5XJUUFS(JU)VC!NJOBNJKPZP
    w $SPXE8PSLT*OD
    w Πϯϑϥ୲౰
    • AWS/Terraform/Docker/Goͱ͔͍ͬͯ͡Δ

    View full-size slide

  3. $SPXE8PSLTͱ͸
    w ೔ຊ࠷େڃͷΫϥ΢υιʔγϯάαʔϏε
    w ೥݄ʙαʔϏε։࢝ɺձһ਺ສਓ

    View full-size slide

  4. $SPXE8PSLTͱ͸

    View full-size slide

  5. ࠓ೔࿩͢͜ͱ
    w σʔλ෼ੳج൫ͷγεςϜߏ੒
    w .Z42-3FETIJGU΁ͷσʔλಉظ
    w ߦಈϩάͷσʔλಉظ
    w όονूܭ
    w σʔλ෼ੳ
    w 3FETIJGUӡ༻5JQT
    w ϏϡʔΛར༻ͨ͠σʔλࢀরݖݶͷ؅ཧ
    w 3FETIJGUͷ؂ࢹ

    View full-size slide

  6. σʔλ෼ੳج൫
    3FETIJGU
    SFQMJDB
    .Z42-
    "QQT
    ,JOFTJT4USFBNT
    -BNCEB
    4
    'JSFIPTF CSJDPMBHF
    αʔϏεຊମ .Z42-σʔλಉظ
    ߦಈϩάಉظ
    όονूܭ
    σʔλ෼ੳ

    View full-size slide

  7. σʔλ෼ੳج൫
    3FETIJGU
    SFQMJDB
    .Z42-
    "QQT
    ,JOFTJT4USFBNT
    -BNCEB
    4
    'JSFIPTF CSJDPMBHF
    αʔϏεຊମ .Z42-σʔλಉظ
    ߦಈϩάಉظ
    όονूܭ
    σʔλ෼ੳ

    View full-size slide

  8. .Z42-σʔλಉظ
    w ΍Γ͍ͨ͜ͱ
    w αʔϏεͷϝΠϯͷ%#͸.Z42-Λ࢖༻͍ͯ͠Δ
    ͕ɺ%#ͷσʔλΛ෼ੳ༻ʹ3FETIJGUʹಉظ͍ͨ͠
    w ΧϥϜͷܕ͕ҧ͏ͷͰσʔλม׵͕ඞཁ
    w ࣮૷
    w 'MZ%BUB4ZODͰ.Z42-3FETIJGUʹಉظ

    View full-size slide

  9. 'MZ%BUB4ZODͱ͸
    w IUUQXXXqZEBUBDPN
    w .Z42-΍1PTUHSF42-ͳͲͷ֤छσʔλιʔεΛ
    3FETIJGUʹಉظͯ͘͠ΕΔ4BB4
    w "84%.4 %BUB.JHSBUJPO4FSWJDF
    Έ͍ͨͳ΍ͭ
    w ॳظઃఆ͸ඞཁ͕ͩɺҰ౓ઃఆ͢Ε͹ɺ͋ͱ͸8FC
    ؅ཧը໘Ͱ؆୯ʹಉظςʔϒϧΛ௥ՃͰ͖Δ

    View full-size slide

  10. 'MZ%BUB4ZODͷςʔϒϧ௥Ճ

    View full-size slide

  11. 'MZ%BUB4ZODͷಛ௃
    w σʔλྲྀྔʹ΋ΑΔͱࢥ͏͕ɺಉظ஗Ԇ͸௨ৗʙ෼͙Β͍
    w ಺෦తʹ͸.Z42-ͷCJOMPHΛಡΜͰɺσʔλม׵ͯ͠4ʹอଘ
    ͠ɺͦΕΛఆظతʹ3FETIJGUʹ$01:*/4&35*/50ʙ4&-&$5
    ͰऔΓࠐΜͰΔΑ͏
    w 3FETIJGUͷఆظϝϯςͳͲͰ઀ଓΤϥʔʹͳͬͯ΋ɺΑ͠ͳʹϦ
    τϥΠͯ͘͠ΕΔͷͰӡ༻ָ͕ɻ
    w ։ൃڌ఺͸γϦίϯόϨʔ͚ͩͲɺ೔ຊਓ͕ىۀͨ͠ελʔτΞο
    ϓͰɺ೔ຊޠͷαϙʔτରԠ΋Մೳɻ࠷ۙ೔ຊΦϑΟε΋Ͱ͖ͨɻ

    View full-size slide

  12. 'MZ%BUB4ZODͷ஫ҙ఺
    w ϚΠάϨʔγϣϯͰςʔϒϧߏ଄͕มΘͬͨ৔߹ʹɺ
    ࣗಈͰରԠͰ͖Δ΋ͷͱͰ͖ͳ͍΋ͷ͕͋Δ
    w ΧϥϜ௥Ճ͸0,͚ͩͲɺΧϥϜͷܕมߋ͸ςʔϒ
    ϧ࠶ಉظ͕ඞཁͳͲ
    w ͦͷଞ੍ݶࣄ߲͸υΩϡϝϯτࢀরIUUQ
    XXXqZEBUBDPNKBSFTPVSDFTqZEBUBTZOD
    TZTUFNTQFDJpDBUJPOT

    View full-size slide

  13. σʔλ෼ੳج൫
    3FETIJGU
    SFQMJDB
    .Z42-
    "QQT
    ,JOFTJT4USFBNT
    -BNCEB
    4
    'JSFIPTF CSJDPMBHF
    αʔϏεຊମ .Z42-σʔλಉظ
    ߦಈϩάಉظ
    όονूܭ
    σʔλ෼ੳ

    View full-size slide

  14. ߦಈϩάͷಉظ
    w ΍Γ͍ͨ͜ͱ
    w ಛఆϖʔδͷӾཡͳͲߦಈϩάΛɺϝΠϯͷ%#ʹॻ͔ͣʹ෼ੳ༻ͷ3FETIJGU
    ʹඇಉظʹऔΓࠐΈ͍ͨ
    w ࣮૷
    w ΞϓϦέʔγϣϯͰॻ͍ͨϩάϑΝΠϧΛqVFOUQMVHJOLJOFTJTͰ,JOFTJT
    4USFBNTʹॻ͖ࠐΈ
    w -BNCEBͰϩάϑΝΠϧͷόϦσʔγϣϯͯ͠ɺਖ਼ৗͳϨίʔυͷΈ,JOFTJT
    'JSFIPTF΁࿈ܞͭͭ͠ɺੜϩάΛ4ʹόοΫΞοϓ
    w ,JOFTJT'JSFIPTFͰ3FETIJGUʹ$01:
    w ΞϓϦέʔγϣϯ͔ΒϩάΛ௚઀'JSFIPTFʹૹΒͣʹ4UFBNT-BNCEBΛڬΜ
    Ͱ͍Δͷ͸ɺϩάͷόϦσʔγϣϯ͍ͨ͠ͷͱɺ'JSFIPTF͸ετϦʔϜ͋ͨ
    ΓѼઌͷςʔϒϧ͕ͭͰɺߏ੒ͷॊೈੑ͕͋Μ·Γͳ͍ͷͰɻ

    View full-size slide

  15. ,JOFTJT4USFBNTͱ͸
    w ϚωʔδυͳετϦʔϛϯάσʔλͷόοϑΝ
    w Ϩίʔυ௕͸࠷େ.#·Ͱొ࿥Մೳ
    w σϑΥϧτͰ࣌ؒอଘɺઃఆͰ೔·ͰԆ௕Մೳ
    w γϟʔυΛ૿΍͢͜ͱͰεέʔϧՄೳ

    View full-size slide

  16. ,JOFTJT-BNCEB࿈ܞ
    w ,JOFTJT4USFBNT͔Β-BNCEBʹ؆୯ʹ࿈ܞՄೳ
    w ࣗલͰϙʔϦϯάͨ͠ΓɺͲ͜·ͰಡΜ͔ͩ؅ཧ͠ͳͯ͘Α͍ͷ
    ͰɺϨίʔυ୯ҐͰԿ͔ॲཧΛڬΈ͍ͨ৔߹ʹ࣮૷ָ͕
    w ࣮ࡍʹ͸Ϩίʔυͣͭॲཧ͢ΔͱΦʔόʔϔου͕σΧΠͷ
    Ͱɺෳ਺Ϩίʔυ·ͱΊͯ΋Βͬͯόονॲཧ͢Δ
    w -BNCEBΛτϦΨ͢Δઃఆͷ#BUDI4J[F σϑΥϧτ
    Ͱௐ੔
    Մೳ
    w -BNCEBͷσϓϩΠͳͲͷ؅ཧʹ͸BQFYΛ࢖༻

    View full-size slide

  17. BQFYͱ͸
    w IUUQBQFYSVO
    w αʔόϨεϑϨʔϜϫʔΫͷͭ
    w ґଘϥΠϒϥϦͳͲΛύοέʔδϯάͯ͠ɺ-BNEBʹ؆
    ୯ʹσϓϩΠͨ͠ΓɺϩάΛUBJMͨ͠ΓͰ͖Δ
    w BQFYରԠݴޠ /PEFKT1ZUIPO+BWB(P3VTU$MPKVSF

    w ͭ·Γ-BNCEBؔ਺͕ެࣜʹαϙʔτͯ͠ͳ͍(PͰॻ͚Δ

    View full-size slide

  18. ,JOFTJT'JSFIPTFͱ͸
    w ετϦʔϛϯάσʔλΛ3FETIJGU4&MBTUJD4FBSDIʹอଘͯ͘͠ΕΔϚωʔδ
    υαʔϏε
    w ࣗલͰఆظతʹ3FETIJGUʹ$01:ίϚϯυୟ͍ͨΓ͠ͳͯ͘Α͍
    w ࠷ۙ౦ژϦʔδϣϯʹདྷͨ
    w ಺෦తʹ͸ɺ4USFBNT-BNCEB43FETIJGUΛ΍ͬͯΔΑ͏Ͱɺ"84
    ͕؅ཧ͢Δ'JSFIPTFͷ*1ΞυϨεΛ3FETIJGUͷ4FDVSJUZ(SPVQͰڐՄͯ͋͛͠Δ
    ඞཁ͕͋Δͷʹ஫ҙ
    w ౦ژϦʔδϣϯͷ*1ΞυϨε͸ެࣜυΩϡϝϯτࢀর
    w IUUQEPDTBXTBNB[PODPNpSFIPTFMBUFTUEFWDPOUSPMMJOHBDDFTTIUNM

    View full-size slide

  19. σʔλ෼ੳج൫
    3FETIJGU
    SFQMJDB
    .Z42-
    "QQT
    ,JOFTJT4USFBNT
    -BNCEB
    4
    'JSFIPTF CSJDPMBHF
    αʔϏεຊମ .Z42-σʔλಉظ
    ߦಈϩάಉظ
    όονूܭ
    σʔλ෼ੳ

    View full-size slide

  20. όονूܭ
    w ΍Γ͍ͨ͜ͱ
    w ֤छ,1*ͷूܭ
    w ΞυϗοΫͳ෼ੳʹศརͳதؒςʔϒϧͷ࡞੒
    w ࣮૷
    w 42-όονϑϨʔϜϫʔΫCSJDPMBHFͰूܭ

    View full-size slide

  21. CSJDPMBHF
    w IUUQTHJUIVCDPNCSJDPMBHFTCSJDPMBHF
    w $PPLQBEͰ࡞ΒΕͨ42-όονϑϨʔϜϫʔΫ
    w ॱ൪ʹ࣮ߦ͢΂͖42-จͷґଘؔ܎Λಠࣗͷ%4-Ͱ
    δϣϒδϣϒωοτͱͯ͠ఆ࣮ٛߦͰ͖Δ

    View full-size slide

  22. CSJDPMBHFͷδϣϒఆٛ

    w DUϑΝΠϧʹूܭ݁ՌΛ֨ೲ͢ΔςʔϒϧΛఆٛ
    w ΄΅$3&"5&5"#-&͚ͩͲ42-จʹม਺Λࠩ͠ࠐΉ͜ͱ΋Ͱ͖Δ
    w ೔࣍ͱ݄࣍Ͱ΄ͱΜͲಉ͡ΫΤϦΛ࢖͍·Θͤͯ௒ศར
    CREATE TABLE $dest_table (
    ${term} date not null,
    user_id integer not null,
    ..
    )
    DISTKEY (user_id) SORTKEY (user_id)
    ;

    View full-size slide

  23. CSJDPMBHFͷδϣϒఆٛ

    w TRMϑΝΠϧʹूܭΫΤϦΛఆٛ
    w ूܭΫΤϦʹ΋ม਺͕ຒΊࠐΊΔͷͰ࢖͍ճ͕͠Ͱ͖Δ
    INSERT INTO $dest_table
    SELECT
    date_trunc('${term}', jst_visited_on)::date AS ${term},
    v.user_id AS user_id,
    ..
    FROM daily_active_user_visits AS v
    GROUP BY ${term}, v.user_id
    ;

    View full-size slide

  24. CSJDPMBHFͷδϣϒఆٛ

    w KPCϑΝΠϧʹδϣϒΛఆٛ
    w ಉ͡42-ʹม਺Λઃఆͯ͠࢖͍ճ͠
    w SFCVJMESFOBNF͸ςʔϒϧͷDSFBUFTXBQܕͷચ͍ସ͑
    class: rebuild-rename
    sql-file: active_users.sql
    table-def: active_users.ct
    dest-table: active_users_daily
    term: day
    class: rebuild-rename
    sql-file: active_users.sql
    table-def: active_users.ct
    dest-table: active_users_monthly
    term: month

    View full-size slide

  25. CSJDPMBHFͷδϣϒఆٛ

    $ bundle exec bricolage --dry-run --job active_users_daily.job
    \timing on
    \set ON_ERROR_STOP false
    drop table active_users_daily_old cascade;
    ....
    -- active_users.ct
    CREATE TABLE active_users_daily_wk (
    day date not null,
    ....
    -- active_users.sql
    INSERT INTO active_users_daily_wk
    SELECT
    date_trunc('day', jst_visited_on)::date AS day,
    ....
    ESZSVOͰੜ੒͞ΕΔ42-จ͕֬ೝͰ͖Δ

    View full-size slide

  26. CSJDPMBHFͷδϣϒఆٛ

    w KPCOFUϑΝΠϧʹδϣϒωοτΛఆٛɻ͜ͷྫͩͱɺ
    w SFQPSUδϣϒ͸NPOUIMZ@TBMFTʹδϣϒʹґଘ
    w SFQPSUδϣϒ͸VTFS@SFHJTUSBUJPOTͷδϣϒʹґଘ
    w NPOUIMZ@TBMFTͱVTFS@SFHJTUSBUJPOT͸ؔ࿈͕ͳ͍ͷͰͲͬͪΛઌʹ࣮ߦ
    ͯ͠΋Α͍
    monthly_sales
    -> report
    user_registrations
    -> report

    View full-size slide

  27. CSJDPMBHFͷδϣϒఆٛ

    w δϣϒωοτ಺ͷδϣϒͷ࣮ߦॱংΛ֬ೝ
    $ bundle exec bricolage-jobnet -l examples/all.jobnet
    examples/monthly_sales
    examples/user_registrations
    examples/report

    View full-size slide

  28. δϣϒωοτͰ؅ཧ͢ΔϝϦοτ
    w ͭͭͷΫΤϦ͕ཧղɾ೺Ѳ͠΍͍͢αΠζʹͳΔ
    w ΫΤϦ͝ͱͷ࣮ߦ݁Ռ͕ςʔϒϧʹ࢒ΔͷͰݕࢉ͠΍͍͢
    w ूܭ݁ՌΛଞͷδϣϒ΍ΞυϗοΫͳ෼ੳͰ࠶ར༻͠΍͍͢
    w ࢀߟSVLBXBͱCSJDPMBHFͱσʔλूܭΫϥ΢υϫʔΫε
    ΤϯδχΞϒϩά
    w IUUQFOHJOFFSDSPXEXPSLTKQFOUSZ

    View full-size slide

  29. σʔλ෼ੳج൫
    3FETIJGU
    SFQMJDB
    .Z42-
    "QQT
    ,JOFTJT4USFBNT
    -BNCEB
    4
    'JSFIPTF CSJDPMBHF
    αʔϏεຊମ .Z42-σʔλಉظ
    ߦಈϩάಉظ
    όονूܭ
    σʔλ෼ੳ

    View full-size slide

  30. #*πʔϧΛ༻͍ͨσʔλ෼ੳ
    w ΍Γ͍ͨ͜ͱ
    w ओཁͳ,1*ͷਪҠΛɺ։ൃνʔϜ͕͍ͭͰ΋ݟ͑ΔΑ͏ʹՄࢹԽ͍ͨ͠
    w ಛఆͷΫΤϦΛఆظ࣮ߦͯ͠ɺଧͬͨࢪࡦͷޮՌΛ͙͢ʹ΢Υον͍ͨ͠
    w ΤϯδχΞ͚ͩͰͳ͘ɺϓϩμΫτΦʔφʔ΍ϚʔέςΟϯάͷϝϯόʔ
    ͳͲ΋ɺΞυϗοΫʹ42-Λ౤͛ͯ෼ੳ͍ͨ͠
    w ࣮૷
    w 3FEBTIʹΑΔσʔλՄࢹԽ
    w ,1*ఆৗϞχλϦϯά༻ͱΞυϗοΫͳ෼ੳ༻ʹ෼͚ͯӡ༻

    View full-size slide

  31. 3FEBTI
    w IUUQTSFEBTIJP
    w 1ZUIPO੡ͷσʔλՄࢹԽπʔϧ
    w 8FCϕʔεͷ6*ɺ༷ʑͳσʔλιʔεʹରԠ
    w ΫΤϦ࣮ߦ݁ՌͷάϥϑԽɺεέδϡʔϧ࣮ߦɺμο
    γϡϘʔυ࡞੒

    View full-size slide

  32. 3FEBTIμογϡϘʔυͷ༷ࢠ

    View full-size slide

  33. งғؾָ͚͓ͩ͠ΈԼ͍͞

    View full-size slide

  34. 3FETIJGUͷӡ༻5JQT
    w ϏϡʔΛར༻ͨ͠σʔλࢀরݖݶͷ؅ཧ
    w 3FETIJGUͷ؂ࢹ

    View full-size slide

  35. ϏϡʔΛར༻ͨ͠
    σʔλࢀরݖݶͷ؅ཧ
    w ΍Γ͍ͨ͜ͱ
    w ಛఆͷςʔϒϧΧϥϜ͸ݟ͑ΔϢʔβάϧʔϓΛ੍ݶ͍ͨ͠
    w ೔ʑαʔϏε։ൃͷ%#ϚΠάϨʔγϣϯͰςʔϒϧΧϥϜ͕૿͑ͨΓ͢Δ
    ͷͰɺͲΕ͕ݟ͑ͯΑ͍͔͸αʔϏεຊମͷίʔυϕʔεͰ؅ཧ͍ͨ͠
    w ࣮૷
    w ςʔϒϧͷ࣮ମ͸ݟͤͣʹɺϏϡʔ͚ͩݟͤΔΑ͏ݖݶௐ੔
    w ର৅ͷςʔϒϧΧϥϜ͸ΞϓϦέʔγϣϯ಺ͰΞϊςʔγϣϯͯ͠ɺϝλσʔ
    λςʔϒϧΛ࡞੒͠3FETIJGUʹಉظ
    w ϝλσʔλςʔϒϧ͔ΒϏϡʔΛੜ੒͢Δ಺੡πʔϧΛ࡞੒

    View full-size slide

  36. ςʔϒϧͷ࣮ମΛݟͤͣʹ
    Ϗϡʔ͚ͩݟͤΔ
    w VONBTLFEεΩʔϚʹςʔϒϧͷ࣮ମɺNBTLFEεΩʔϚʹϏϡʔΛ࡞੒
    w VONBTLFE@WJFXFSTͱNBTLFE@WJFXFSTͷάϧʔϓΛ࡞ͬͯݖݶௐ੔
    w ϋϚΓϙΠϯτ
    w (3"/54&-&$50/"--5"#-&4ʙ͸طʹଘࡏ͢ΔϏϡʔͷΈ༗ޮͰɺޙ͔Β૿
    ͑ͨϏϡʔ΋ࣗಈͰݟ͑ΔΑ͏ʹ͢Δʹ͸"-5&3%&'"6-513*7*-&(&4ʙ͕ඞཁ
    w NBTLFE@WJFXFST͸࣮ςʔϒϧͷଘࡏ͢ΔVONBTLFEεΩʔϚ΁ͷ64"(&͕ඞཁ
    w ۩ମతͳ42-͸एׯ൥ࡶͳͷͰɺ࣮૷ৄࡉ͸2JJUBʹॻ͍ͨ
    w 3FETIJGUͰςʔϒϧͷ࣮ମΛݟͤͣʹϏϡʔ͚ͩݟͤͯΞΫηε੍ݶ͍ͨ͠
    IUUQTRJJUBDPNNJOBNJKPZPJUFNTDCB

    View full-size slide

  37. ݟͤͯΑ͍ΧϥϜͷ؅ཧ
    w ೔ʑαʔϏε։ൃͷ%#ϚΠάϨʔγϣϯͰςʔϒϧΧϥϜ͕૿͑ͨΓ͢Δͷ
    ͰɺϏϡʔΛ࡞ͬͯ΋ɺ࠷৽ঢ়ଶʹอͭͷ͕೉͍͠
    w ৽͍͠ςʔϒϧΧϥϜ͕૿͑Δ৔߹ɺͦͷσʔλͷऔѻʹҰ൪ৄ͍͠ͷ͸ɺ
    ͦͷίʔυΛ௥Ճͨ͠ਓͳͷͰɺαʔϏεͷίʔυͷۙ͘ʹॻ͖͍ͨ
    w 3BJMTͷϞσϧʹ:"3%ͷΧελϜλάͱͯ͠ɺಉظର৅ςʔϒϧ͔Ͳ͏͔ɺͲ
    ͷΧϥϜ͸ݟͤͯΑ͍͔Ξϊςʔγϣϯ
    w :"3%λάΛղੳͯ͠ϝλσʔλςʔϒϧΛ࡞੒͠ɺ3FETIJGUʹಉظ
    w ϝλσʔλςʔϒϧͷ৘ใΛݩʹϏϡʔΛҰׅੜ੒͢Δ಺੡πʔϧΛ࡞੒

    View full-size slide

  38. 3FETIJGUͷ؂ࢹ
    w ΍Γ͍ͨ͜ͱ
    w 3FETIJGUͷ҆ఆՔಇͷͨΊɺՄ༻ੑɺੑೳʹؔ࿈͢ΔϝτϦΫεΛ؂ࢹ͍ͨ͠
    w 'MZ%BUB4ZODͷಉظ͕஗Ԇͯ͠ͳ͍͔؂ࢹ͍ͨ͠
    w ࣮૷
    w $MPVE8BUDI͔Β%BUBEPHͷެࣜΠϯςάϨʔγϣϯͰϝτϦΫε౤͛ͯ؂ࢹ
    w 3FETIJGUͷγεςϜϏϡʔʹఆظతʹ42-࣮ߦͨ݁͠ՌΛ%BUBEPHʹϝτϦΫε
    ౤͛ͯ؂ࢹ
    w 'MZ%BUBͷಉظݩ%#ʹγεςϜλΠϜελϯϓςʔϒϧΛ࡞ͬͯDSPOͰ෼ʹ
    ճ61%"5&ͯ͠ɺఆظతʹ3FETIJGUͰݱࡏ࣌ࠁͱൺֱͯ͠஗ԆΛ%BUBEPHͰ؂ࢹ
    w ఆظతʹ42-Λ࣮ߦͯ͠%BUBEPHʹ౤͚͛ͭΔDZRMEPHͱ͍͏πʔϧΛ࡞ͬͨ

    View full-size slide

  39. $MPVE8BUDIͷ؂ࢹ߲໨
    w ݟͨ΄͏͕Αͦ͞͏ͳ΋ͷ
    w $166UJMJ[BUJPO$16࢖༻཰
    w 1FSDFOUBHF%JTL4QBDF6TFEσΟεΫ࢖༻཰
    w )FBMUI4UBUVTϔϧενΣοΫ
    w .BJOUFOBODF.PEFϝϯςঢ়ଶ͔Ͳ͏͔
    w ͦͷଞऔಘͰ͖Δ߲໨
    w IUUQEPDTBXTBNB[PODPN"NB[PO$MPVE8BUDIMBUFTU
    NPOJUPSJOHSTNFUSJDTDPMMFDUFEIUNM

    View full-size slide

  40. γεςϜϏϡʔͷ؂ࢹ߲໨
    w ݟͨ΄͏͕Αͦ͞͏ͳ΋ͷ
    w 457@8-.@26&3:@45"5&Ωϡʔ͝ͱͷΫΤϦ࣮ߦ࣌ؒ
    w 457@8-.@4&37*$&@$-"44@45"5&Ωϡʔ͝ͱͷΫΤϦ࣮ߦ਺
    w 45-@26&3:Ϣʔβ͝ͱͷΫΤϦ࣮ߦ਺ɺࣦഊ਺ɺ࣮ߦ࣌ؒ
    w 45-@"-&35@&7&/5@-0(ύϑΥʔϚϯεܯࠂͷΠϕϯτϩά
    w ͦͷଞऔಘͰ͖Δ߲໨
    w IUUQEPDTBXTBNB[PODPNSFETIJGUMBUFTUEHDN@DIBQ@TZTUFN
    UBCMFTIUNM

    View full-size slide

  41. ݱࡏͷΩϡʔ͝ͱͷΫΤϦ࣮ߦ࣌ؒ
    SELECT TRANSLATE(TRIM(TRANSLATE(c.condition, '():', '')), ' ',
    '_') AS name, s.state, COUNT(*) AS count, NVL(MAX(s.queue_time),
    0) AS max_queue_time, NVL(MAX(s.exec_time), 0) AS max_exec_time
    FROM stv_wlm_classification_config c, stv_wlm_query_state s
    WHERE c.action_service_class = s.service_class GROUP BY s.state,
    c.condition;
    name | state | count | max_queue_time | max_exec_time
    --------------------+------------------+-------+----------------+---------------
    user_group_hoge | Running | 1 | 1129521 | 393674
    user_group_hoge | QueuedWaiting | 1 | 390360 | 0
    querytype_any | Running | 1 | 0 | 137126

    View full-size slide

  42. ݱࡏͷΩϡʔ͝ͱͷΫΤϦ࣮ߦ਺
    SELECT TRANSLATE(TRIM(TRANSLATE(c.condition, '():', '')), '
    ', '_') AS name, s.num_queued_queries AS queued,
    s.num_executing_queries AS executing, s.num_executed_queries
    AS executed FROM stv_wlm_classification_config c,
    stv_wlm_service_class_state s WHERE c.action_service_class =
    s.service_class;
    name | queued | executing | executed
    --------------------------------------+--------+-----------+----------
    querytype_any | 0 | 2 | 142534
    user_group_hoge | 0 | 1 | 1365805

    View full-size slide

  43. ௚ۙ෼ͷϢʔβ͝ͱͷΫΤϦ࣮ߦ࣌ؒ
    SELECT TRIM(u.usename) AS username, (CASE q.aborted WHEN 0 THEN 'no' WHEN 1
    THEN 'yes' END) AS aborted, COUNT(*) AS count, NVL(MIN(DATEDIFF(us,
    q.starttime, q.endtime)), 0) AS min, NVL(MAX(DATEDIFF(us, q.starttime,
    q.endtime)), 0) AS max, NVL(AVG(DATEDIFF(us, q.starttime, q.endtime)), 0) AS
    avg, NVL(MEDIAN(DATEDIFF(us, q.starttime, q.endtime))::integer, 0) AS median
    FROM stl_query q, pg_user u WHERE q.userid = u.usesysid AND q.endtime >
    (SYSDATE - interval '5m') GROUP BY q.userid, q.aborted, u.usename;
    username | aborted | count | min | max | avg | median
    -------------------------+---------+-------+----------+----------+----------+----------
    hoge | no | 1 | 12725416 | 12725416 | 12725416 | 12725416
    hoge | yes | 1 | 9176396 | 9176396 | 9176396 | 9176396

    View full-size slide

  44. DZRMEPH
    w IUUQTHJUIVCDPNDSPXEXPSLTDZRMEPH
    w ఆظతʹ42-Λ࣮ߦͯ͠%BUBEPHʹϝτϦΫεΛ౤͚͛ͭΔ؂ࢹ
    πʔϧ
    w ࣮૷͸(P
    w σʔλιʔε͸1PTUHSFT 3FETIJGUؚΉ
    ɺ.Z42-ʹରԠ
    w ෳ਺ͷ42-ΛҟͳΔִؒͰ࣮ߦՄೳ
    w ؂ࢹΫΤϦͰ%#ෛՙΛ͔͚ͳ͍Α͏ʹಉ࣮࣌ߦΫΤϦ͸ͭʹ੍ޚ

    View full-size slide

  45. DZRMEPHZNMͷྫ
    ࢖͍ํ͸ΤϯδχΞϒϩάʹॻ͍ͨͷͰҎԼࢀরˣ
    ఆظతʹ42-Λ࣮ߦͨ݁͠ՌΛ%BUBEPHʹૹ৴͢ΔDZRMEPHͱ͍͏πʔϧΛ࡞ͬͨ
    IUUQFOHJOFFSDSPXEXPSLTKQFOUSZ

    View full-size slide

  46. %BUBEPHμογϡϘʔυ

    View full-size slide

  47. 8FBSF)JSJOH
    Ұॹʹʮσʔλ෼ੳج൫Λ࡞Γ͍ͨʯΤϯδχΞɺ
    ʮσʔλ෼ੳͯ͠αʔϏεΛվળ͍ͨ͠ʯΤϯδχΞΛืूதͰ͢
    IUUQTXXXXBOUFEMZDPNQSPKFDUT

    View full-size slide