BigData-JAWS #9: Data Analytics Infra for CrowdWorks

Transcript

1. Ϋϥ΢υϫʔΫεΛࢧ͑Δ
   σʔλ෼ੳج൫ #JH%BUB+"84
   
   
   !NJOBNJKPZP
2. ࣗݾ঺հ w ৿ా
   ਅ೭
   .BTBZVLJ
   .PSJUB
   w 5XJUUFS
   
   (JU)VC
   !NJOBNJKPZP
   w $SPXE8PSLT
   *OD
   w Πϯϑϥ୲౰
   • AWS/Terraform/Docker/Goͱ͔͍ͬͯ͡Δ
3. $SPXE8PSLTͱ͸ w ೔ຊ࠷େڃͷΫϥ΢υιʔγϯάαʔϏε
   w ೥݄ʙαʔϏε։࢝ɺձһ਺ສਓ
4. $SPXE8PSLTͱ͸
5. ࠓ೔࿩͢͜ͱ w σʔλ෼ੳج൫ͷγεςϜߏ੒
   w .Z42-
   
   3FETIJGU΁ͷσʔλಉظ
   w ߦಈϩάͷσʔλಉظ
   w όονूܭ
   w σʔλ෼ੳ
   w 3FETIJGUӡ༻5JQT
   w ϏϡʔΛར༻ͨ͠σʔλࢀরݖݶͷ؅ཧ
   w 3FETIJGUͷ؂ࢹ
6. σʔλ෼ੳج൫ 3FETIJGU SFQMJDB .Z42- "QQT ,JOFTJT
   4USFBNT -BNCEB 4 'JSFIPTF CSJDPMBHF αʔϏεຊମ .Z42-σʔλಉظ ߦಈϩάಉظ όονूܭ σʔλ෼ੳ
7. σʔλ෼ੳج൫ 3FETIJGU SFQMJDB .Z42- "QQT ,JOFTJT
   4USFBNT -BNCEB 4 'JSFIPTF CSJDPMBHF αʔϏεຊମ .Z42-σʔλಉظ ߦಈϩάಉظ όονूܭ σʔλ෼ੳ
8. .Z42-σʔλಉظ w ΍Γ͍ͨ͜ͱ
   w αʔϏεͷϝΠϯͷ%#͸.Z42-Λ࢖༻͍ͯ͠Δ ͕ɺ%#ͷσʔλΛ෼ੳ༻ʹ3FETIJGUʹಉظ͍ͨ͠
   w ΧϥϜͷܕ͕ҧ͏ͷͰσʔλม׵͕ඞཁ
   w ࣮૷
   w 'MZ%BUB
   4ZODͰ.Z42-
   
   3FETIJGUʹಉظ
9. 'MZ%BUB
   4ZODͱ͸ w IUUQXXXqZEBUBDPN
   w .Z42-΍1PTUHSF42-ͳͲͷ֤छσʔλιʔεΛ 3FETIJGUʹಉظͯ͘͠ΕΔ4BB4
   w "84
   %.4 %BUB
   .JHSBUJPO
   4FSWJDF Έ͍ͨͳ΍ͭ
   w ॳظઃఆ͸ඞཁ͕ͩɺҰ౓ઃఆ͢Ε͹ɺ͋ͱ͸8FC ؅ཧը໘Ͱ؆୯ʹಉظςʔϒϧΛ௥ՃͰ͖Δ
10. 'MZ%BUB
    4ZODͷςʔϒϧ௥Ճ
11. 'MZ%BUB
    4ZODͷಛ௃ w σʔλྲྀྔʹ΋ΑΔͱࢥ͏͕ɺಉظ஗Ԇ͸௨ৗʙ෼͙Β͍
    w ಺෦తʹ͸.Z42-ͷCJOMPHΛಡΜͰɺσʔλม׵ͯ͠4ʹอଘ ͠ɺͦΕΛఆظతʹ3FETIJGUʹ$01:
    
    */4&35
    */50
    ʙ
    4&-&$5 ͰऔΓࠐΜͰΔΑ͏
    w 3FETIJGUͷఆظϝϯςͳͲͰ઀ଓΤϥʔʹͳͬͯ΋ɺΑ͠ͳʹϦ τϥΠͯ͘͠ΕΔͷͰӡ༻ָ͕ɻ
    w ։ൃڌ఺͸γϦίϯόϨʔ͚ͩͲɺ೔ຊਓ͕ىۀͨ͠ελʔτΞο ϓͰɺ೔ຊޠͷαϙʔτରԠ΋Մೳɻ࠷ۙ೔ຊΦϑΟε΋Ͱ͖ͨɻ
12. 'MZ%BUB
    4ZODͷ஫ҙ఺ w ϚΠάϨʔγϣϯͰςʔϒϧߏ଄͕มΘͬͨ৔߹ʹɺ ࣗಈͰରԠͰ͖Δ΋ͷͱͰ͖ͳ͍΋ͷ͕͋Δ
    w ΧϥϜ௥Ճ͸0,͚ͩͲɺΧϥϜͷܕมߋ͸ςʔϒ ϧ࠶ಉظ͕ඞཁͳͲ
    w ͦͷଞ੍ݶࣄ߲͸υΩϡϝϯτࢀর
    IUUQ XXXqZEBUBDPNKBSFTPVSDFTqZEBUBTZOD TZTUFNTQFDJpDBUJPOT
13. σʔλ෼ੳج൫ 3FETIJGU SFQMJDB .Z42- "QQT ,JOFTJT
    4USFBNT -BNCEB 4 'JSFIPTF CSJDPMBHF αʔϏεຊମ .Z42-σʔλಉظ ߦಈϩάಉظ όονूܭ σʔλ෼ੳ
14. ߦಈϩάͷಉظ w ΍Γ͍ͨ͜ͱ
    w ಛఆϖʔδͷӾཡͳͲߦಈϩάΛɺϝΠϯͷ%#ʹॻ͔ͣʹ෼ੳ༻ͷ3FETIJGU ʹඇಉظʹऔΓࠐΈ͍ͨ
    w ࣮૷
    w ΞϓϦέʔγϣϯͰॻ͍ͨϩάϑΝΠϧΛqVFOUQMVHJOLJOFTJTͰ,JOFTJT
    4USFBNTʹॻ͖ࠐΈ
    w -BNCEBͰϩάϑΝΠϧͷόϦσʔγϣϯͯ͠ɺਖ਼ৗͳϨίʔυͷΈ,JOFTJT
    'JSFIPTF΁࿈ܞͭͭ͠ɺੜϩάΛ4ʹόοΫΞοϓ
    w ,JOFTJT
    'JSFIPTFͰ3FETIJGUʹ$01:
    w ΞϓϦέʔγϣϯ͔ΒϩάΛ௚઀'JSFIPTFʹૹΒͣʹ4UFBNT-BNCEBΛڬΜ Ͱ͍Δͷ͸ɺϩάͷόϦσʔγϣϯ͍ͨ͠ͷͱɺ'JSFIPTF͸ετϦʔϜ͋ͨ ΓѼઌͷςʔϒϧ͕ͭͰɺߏ੒ͷॊೈੑ͕͋Μ·Γͳ͍ͷͰɻ
15. ,JOFTJT
    4USFBNTͱ͸ w ϚωʔδυͳετϦʔϛϯάσʔλͷόοϑΝ
    w Ϩίʔυ௕͸࠷େ.#·Ͱొ࿥Մೳ
    w σϑΥϧτͰ࣌ؒอଘɺઃఆͰ೔·ͰԆ௕Մೳ
    w γϟʔυΛ૿΍͢͜ͱͰεέʔϧՄೳ
16. ,JOFTJT
    
    -BNCEB࿈ܞ w ,JOFTJT
    4USFBNT͔Β-BNCEBʹ؆୯ʹ࿈ܞՄೳ
    w ࣗલͰϙʔϦϯάͨ͠ΓɺͲ͜·ͰಡΜ͔ͩ؅ཧ͠ͳͯ͘Α͍ͷ ͰɺϨίʔυ୯ҐͰԿ͔ॲཧΛڬΈ͍ͨ৔߹ʹ࣮૷ָ͕
    w ࣮ࡍʹ͸Ϩίʔυͣͭॲཧ͢ΔͱΦʔόʔϔου͕σΧΠͷ Ͱɺෳ਺Ϩίʔυ·ͱΊͯ΋Βͬͯόονॲཧ͢Δ
    w -BNCEBΛτϦΨ͢Δઃఆͷ#BUDI
    4J[F σϑΥϧτ Ͱௐ੔ Մೳ
    w -BNCEBͷσϓϩΠͳͲͷ؅ཧʹ͸BQFYΛ࢖༻
17. BQFYͱ͸ w IUUQBQFYSVO
    w αʔόϨεϑϨʔϜϫʔΫͷͭ
    w ґଘϥΠϒϥϦͳͲΛύοέʔδϯάͯ͠ɺ-BNEBʹ؆ ୯ʹσϓϩΠͨ͠ΓɺϩάΛUBJMͨ͠ΓͰ͖Δ
    w BQFYରԠݴޠ /PEFKT1ZUIPO+BWB(P3VTU$MPKVSF
    w ͭ·Γ-BNCEBؔ਺͕ެࣜʹαϙʔτͯ͠ͳ͍(PͰॻ͚Δ
18. ,JOFTJT
    'JSFIPTFͱ͸ w ετϦʔϛϯάσʔλΛ3FETIJGU4&MBTUJD4FBSDIʹอଘͯ͘͠ΕΔϚωʔδ υαʔϏε
    w ࣗલͰఆظతʹ3FETIJGUʹ$01:ίϚϯυୟ͍ͨΓ͠ͳͯ͘Α͍
    w ࠷ۙ౦ژϦʔδϣϯʹདྷͨ
    w ಺෦తʹ͸ɺ4USFBNT
    
    -BNCEB
    
    4
    
    3FETIJGU
    Λ΍ͬͯΔΑ͏Ͱɺ"84 ͕؅ཧ͢Δ'JSFIPTFͷ*1ΞυϨεΛ3FETIJGUͷ4FDVSJUZ(SPVQͰڐՄͯ͋͛͠Δ ඞཁ͕͋Δͷʹ஫ҙ
    w ౦ژϦʔδϣϯͷ*1ΞυϨε͸ެࣜυΩϡϝϯτࢀর
    w IUUQEPDTBXTBNB[PODPNpSFIPTFMBUFTUEFWDPOUSPMMJOHBDDFTTIUNM
19. σʔλ෼ੳج൫ 3FETIJGU SFQMJDB .Z42- "QQT ,JOFTJT
    4USFBNT -BNCEB 4 'JSFIPTF CSJDPMBHF αʔϏεຊମ .Z42-σʔλಉظ ߦಈϩάಉظ όονूܭ σʔλ෼ੳ
20. όονूܭ w ΍Γ͍ͨ͜ͱ
    w ֤छ,1*ͷूܭ
    w ΞυϗοΫͳ෼ੳʹศརͳதؒςʔϒϧͷ࡞੒
    w ࣮૷
    w 42-όονϑϨʔϜϫʔΫCSJDPMBHFͰूܭ
21. CSJDPMBHF w IUUQTHJUIVCDPNCSJDPMBHFTCSJDPMBHF
    w $PPLQBEͰ࡞ΒΕͨ42-όονϑϨʔϜϫʔΫ
    w ॱ൪ʹ࣮ߦ͢΂͖42-จͷґଘؔ܎Λಠࣗͷ%4-Ͱ δϣϒδϣϒωοτͱͯ͠ఆ࣮ٛߦͰ͖Δ
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) ;
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 ;
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
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-จ͕֬ೝͰ͖Δ
26. CSJDPMBHFͷδϣϒఆٛ  w KPCOFUϑΝΠϧʹδϣϒωοτΛఆٛɻ͜ͷྫͩͱɺ
    w SFQPSUδϣϒ͸NPOUIMZ@TBMFTʹδϣϒʹґଘ
    w SFQPSUδϣϒ͸VTFS@SFHJTUSBUJPOTͷδϣϒʹґଘ
    w NPOUIMZ@TBMFTͱVTFS@SFHJTUSBUJPOT͸ؔ࿈͕ͳ͍ͷͰͲͬͪΛઌʹ࣮ߦ ͯ͠΋Α͍ monthly_sales -> report user_registrations -> report
27. CSJDPMBHFͷδϣϒఆٛ  w δϣϒωοτ಺ͷδϣϒͷ࣮ߦॱংΛ֬ೝ $ bundle exec bricolage-jobnet -l examples/all.jobnet examples/monthly_sales examples/user_registrations examples/report
28. δϣϒωοτͰ؅ཧ͢ΔϝϦοτ w ͭͭͷΫΤϦ͕ཧղɾ೺Ѳ͠΍͍͢αΠζʹͳΔ
    w ΫΤϦ͝ͱͷ࣮ߦ݁Ռ͕ςʔϒϧʹ࢒ΔͷͰݕࢉ͠΍͍͢
    w ूܭ݁ՌΛଞͷδϣϒ΍ΞυϗοΫͳ෼ੳͰ࠶ར༻͠΍͍͢
    w ࢀߟ
    SVLBXB
    ͱ
    CSJDPMBHF
    ͱσʔλूܭ
    
    Ϋϥ΢υϫʔΫε ΤϯδχΞϒϩά
    
    w IUUQFOHJOFFSDSPXEXPSLTKQFOUSZ
    
29. σʔλ෼ੳج൫ 3FETIJGU SFQMJDB .Z42- "QQT ,JOFTJT
    4USFBNT -BNCEB 4 'JSFIPTF CSJDPMBHF αʔϏεຊମ .Z42-σʔλಉظ ߦಈϩάಉظ όονूܭ σʔλ෼ੳ
30. #*πʔϧΛ༻͍ͨσʔλ෼ੳ w ΍Γ͍ͨ͜ͱ
    w ओཁͳ,1*ͷਪҠΛɺ։ൃνʔϜ͕͍ͭͰ΋ݟ͑ΔΑ͏ʹՄࢹԽ͍ͨ͠
    w ಛఆͷΫΤϦΛఆظ࣮ߦͯ͠ɺଧͬͨࢪࡦͷޮՌΛ͙͢ʹ΢Υον͍ͨ͠
    w ΤϯδχΞ͚ͩͰͳ͘ɺϓϩμΫτΦʔφʔ΍ϚʔέςΟϯάͷϝϯόʔ ͳͲ΋ɺΞυϗοΫʹ42-Λ౤͛ͯ෼ੳ͍ͨ͠
    w ࣮૷
    w 3FEBTIʹΑΔσʔλՄࢹԽ
    w ,1*ఆৗϞχλϦϯά༻ͱΞυϗοΫͳ෼ੳ༻ʹ෼͚ͯӡ༻
31. 3FEBTI w IUUQTSFEBTIJP
    w 1ZUIPO੡ͷσʔλՄࢹԽπʔϧ
    w 8FCϕʔεͷ6*ɺ༷ʑͳσʔλιʔεʹରԠ
    w ΫΤϦ࣮ߦ݁ՌͷάϥϑԽɺεέδϡʔϧ࣮ߦɺμο γϡϘʔυ࡞੒
32. 3FEBTIμογϡϘʔυͷ༷ࢠ
33. งғؾָ͚͓ͩ͠ΈԼ͍͞
34. 3FETIJGUͷӡ༻5JQT w ϏϡʔΛར༻ͨ͠σʔλࢀরݖݶͷ؅ཧ
    w 3FETIJGUͷ؂ࢹ
35. ϏϡʔΛར༻ͨ͠
    σʔλࢀরݖݶͷ؅ཧ w ΍Γ͍ͨ͜ͱ
    w ಛఆͷςʔϒϧΧϥϜ͸ݟ͑ΔϢʔβάϧʔϓΛ੍ݶ͍ͨ͠
    w ೔ʑαʔϏε։ൃͷ%#ϚΠάϨʔγϣϯͰςʔϒϧΧϥϜ͕૿͑ͨΓ͢Δ ͷͰɺͲΕ͕ݟ͑ͯΑ͍͔͸αʔϏεຊମͷίʔυϕʔεͰ؅ཧ͍ͨ͠
    w ࣮૷
    w ςʔϒϧͷ࣮ମ͸ݟͤͣʹɺϏϡʔ͚ͩݟͤΔΑ͏ݖݶௐ੔
    w ର৅ͷςʔϒϧΧϥϜ͸ΞϓϦέʔγϣϯ಺ͰΞϊςʔγϣϯͯ͠ɺϝλσʔ λςʔϒϧΛ࡞੒͠3FETIJGUʹಉظ
    w ϝλσʔλςʔϒϧ͔ΒϏϡʔΛੜ੒͢Δ಺੡πʔϧΛ࡞੒
36. ςʔϒϧͷ࣮ମΛݟͤͣʹ Ϗϡʔ͚ͩݟͤΔ w VONBTLFEεΩʔϚʹςʔϒϧͷ࣮ମɺNBTLFEεΩʔϚʹϏϡʔΛ࡞੒
    w VONBTLFE@WJFXFSTͱNBTLFE@WJFXFSTͷάϧʔϓΛ࡞ͬͯݖݶௐ੔
    w ϋϚΓϙΠϯτ
    w (3"/5
    4&-&$5
    0/
    "--
    5"#-&4
    ʙ
    ͸طʹଘࡏ͢ΔϏϡʔͷΈ༗ޮͰɺޙ͔Β૿ ͑ͨϏϡʔ΋ࣗಈͰݟ͑ΔΑ͏ʹ͢Δʹ͸
    "-5&3
    %&'"6-5
    13*7*-&(&4
    ʙ͕ඞཁ
    w NBTLFE@WJFXFST͸࣮ςʔϒϧͷଘࡏ͢ΔVONBTLFEεΩʔϚ΁ͷ64"(&͕ඞཁ
    w ۩ମతͳ42-͸एׯ൥ࡶͳͷͰɺ࣮૷ৄࡉ͸2JJUBʹॻ͍ͨ
    w 3FETIJGUͰςʔϒϧͷ࣮ମΛݟͤͣʹϏϡʔ͚ͩݟͤͯΞΫηε੍ݶ͍ͨ͠
    IUUQTRJJUBDPNNJOBNJKPZPJUFNTDCB
    
37. ݟͤͯΑ͍ΧϥϜͷ؅ཧ w ೔ʑαʔϏε։ൃͷ%#ϚΠάϨʔγϣϯͰςʔϒϧΧϥϜ͕૿͑ͨΓ͢Δͷ ͰɺϏϡʔΛ࡞ͬͯ΋ɺ࠷৽ঢ়ଶʹอͭͷ͕೉͍͠
    w ৽͍͠ςʔϒϧΧϥϜ͕૿͑Δ৔߹ɺͦͷσʔλͷऔѻʹҰ൪ৄ͍͠ͷ͸ɺ ͦͷίʔυΛ௥Ճͨ͠ਓͳͷͰɺαʔϏεͷίʔυͷۙ͘ʹॻ͖͍ͨ
    w 3BJMTͷϞσϧʹ:"3%ͷΧελϜλάͱͯ͠ɺಉظର৅ςʔϒϧ͔Ͳ͏͔ɺͲ ͷΧϥϜ͸ݟͤͯΑ͍͔Ξϊςʔγϣϯ
    w :"3%λάΛղੳͯ͠ϝλσʔλςʔϒϧΛ࡞੒͠ɺ3FETIJGUʹಉظ
    w ϝλσʔλςʔϒϧͷ৘ใΛݩʹϏϡʔΛҰׅੜ੒͢Δ಺੡πʔϧΛ࡞੒
38. 3FETIJGUͷ؂ࢹ w ΍Γ͍ͨ͜ͱ
    w 3FETIJGUͷ҆ఆՔಇͷͨΊɺՄ༻ੑɺੑೳʹؔ࿈͢ΔϝτϦΫεΛ؂ࢹ͍ͨ͠
    w 'MZ%BUB
    4ZODͷಉظ͕஗Ԇͯ͠ͳ͍͔؂ࢹ͍ͨ͠
    w ࣮૷
    w $MPVE8BUDI͔Β%BUBEPHͷެࣜΠϯςάϨʔγϣϯͰϝτϦΫε౤͛ͯ؂ࢹ
    w 3FETIJGUͷγεςϜϏϡʔʹఆظతʹ42-࣮ߦͨ݁͠ՌΛ%BUBEPHʹϝτϦΫε ౤͛ͯ؂ࢹ
    w 'MZ%BUBͷಉظݩ%#ʹγεςϜλΠϜελϯϓςʔϒϧΛ࡞ͬͯDSPOͰ෼ʹ ճ61%"5&ͯ͠ɺఆظతʹ3FETIJGUͰݱࡏ࣌ࠁͱൺֱͯ͠஗ԆΛ%BUBEPHͰ؂ࢹ
    w ఆظతʹ42-Λ࣮ߦͯ͠%BUBEPHʹ౤͚͛ͭΔDZRMEPHͱ͍͏πʔϧΛ࡞ͬͨ
39. $MPVE8BUDIͷ؂ࢹ߲໨ w ݟͨ΄͏͕Αͦ͞͏ͳ΋ͷ
    w $166UJMJ[BUJPO
    $16࢖༻཰
    w 1FSDFOUBHF%JTL4QBDF6TFE
    σΟεΫ࢖༻཰
    w )FBMUI4UBUVT
    ϔϧενΣοΫ
    w .BJOUFOBODF.PEF
    ϝϯςঢ়ଶ͔Ͳ͏͔
    w ͦͷଞऔಘͰ͖Δ߲໨
    w IUUQEPDTBXTBNB[PODPN"NB[PO$MPVE8BUDIMBUFTU NPOJUPSJOHSTNFUSJDTDPMMFDUFEIUNM
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
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
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
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
44. DZRMEPH w IUUQTHJUIVCDPNDSPXEXPSLTDZRMEPH
    w ఆظతʹ42-Λ࣮ߦͯ͠%BUBEPHʹϝτϦΫεΛ౤͚͛ͭΔ؂ࢹ πʔϧ
    w ࣮૷͸(P
    w σʔλιʔε͸1PTUHSFT
    3FETIJGUؚΉ ɺ.Z42-ʹରԠ
    w ෳ਺ͷ42-ΛҟͳΔִؒͰ࣮ߦՄೳ
    w ؂ࢹΫΤϦͰ%#ෛՙΛ͔͚ͳ͍Α͏ʹಉ࣮࣌ߦΫΤϦ͸ͭʹ੍ޚ
45. DZRMEPHZNMͷྫ ࢖͍ํ͸ΤϯδχΞϒϩάʹॻ͍ͨͷͰҎԼࢀরˣ
    ఆظతʹ42-Λ࣮ߦͨ݁͠ՌΛ%BUBEPHʹૹ৴͢ΔDZRMEPHͱ͍͏πʔϧΛ࡞ͬͨ
    IUUQFOHJOFFSDSPXEXPSLTKQFOUSZ
46. %BUBEPHμογϡϘʔυ
47. 8F
    BSF
    )JSJOH
     Ұॹʹʮσʔλ෼ੳج൫Λ࡞Γ͍ͨʯΤϯδχΞɺ
    ʮσʔλ෼ੳͯ͠αʔϏεΛվળ͍ͨ͠ʯΤϯδχΞΛืूதͰ͢
    IUUQTXXXXBOUFEMZDPNQSPKFDUT