BigData-JAWS #9: Data Analytics Infra for CrowdWorks

Transcript

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

   View Slide

2. ࣗݾ঺հ
   w ৿ాਅ೭ .BTBZVLJ.PSJUB
   
   w 5XJUUFS(JU)VC!NJOBNJKPZP
   w $SPXE8PSLT*OD
   w Πϯϑϥ୲౰
   • AWS/Terraform/Docker/Goͱ͔͍ͬͯ͡Δ
   

   View Slide

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

   View Slide

4. $SPXE8PSLTͱ͸
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View 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 Slide

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

    View Slide

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

    View Slide

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

    View 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 Slide

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

    View Slide

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

    View Slide

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

    View 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 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 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 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 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 Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View 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 Slide

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

    View 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 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 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 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 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 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 Slide

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

    View Slide

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

    View Slide

46. %BUBEPHμογϡϘʔυ
    

    View Slide

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

    View Slide