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

5ade34f8bdf1b55c4ff82c11cf23cf42?s=128

Masayuki Morita

October 11, 2017
Tweet

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 ,JOFTJT4USFBNT -BNCEB 4 'JSFIPTF CSJDPMBHF

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

    αʔϏεຊମ .Z42-σʔλಉظ ߦಈϩάಉظ όονूܭ σʔλ෼ੳ
  8. .Z42-σʔλಉظ w ΍Γ͍ͨ͜ͱ w αʔϏεͷϝΠϯͷ%#͸.Z42-Λ࢖༻͍ͯ͠Δ ͕ɺ%#ͷσʔλΛ෼ੳ༻ʹ3FETIJGUʹಉظ͍ͨ͠ w ΧϥϜͷܕ͕ҧ͏ͷͰσʔλม׵͕ඞཁ w ࣮૷

    w 'MZ%BUB4ZODͰ.Z42-3FETIJGUʹಉظ
  9. 'MZ%BUB4ZODͱ͸ w IUUQXXXqZEBUBDPN w .Z42-΍1PTUHSF42-ͳͲͷ֤छσʔλιʔεΛ 3FETIJGUʹಉظͯ͘͠ΕΔ4BB4 w "84%.4 %BUB.JHSBUJPO4FSWJDF Έ͍ͨͳ΍ͭ

    w ॳظઃఆ͸ඞཁ͕ͩɺҰ౓ઃఆ͢Ε͹ɺ͋ͱ͸8FC ؅ཧը໘Ͱ؆୯ʹಉظςʔϒϧΛ௥ՃͰ͖Δ
  10. 'MZ%BUB4ZODͷςʔϒϧ௥Ճ

  11. 'MZ%BUB4ZODͷಛ௃ w σʔλྲྀྔʹ΋ΑΔͱࢥ͏͕ɺಉظ஗Ԇ͸௨ৗʙ෼͙Β͍ w ಺෦తʹ͸.Z42-ͷCJOMPHΛಡΜͰɺσʔλม׵ͯ͠4ʹอଘ ͠ɺͦΕΛఆظతʹ3FETIJGUʹ$01:*/4&35*/50ʙ4&-&$5 ͰऔΓࠐΜͰΔΑ͏ w 3FETIJGUͷఆظϝϯςͳͲͰ઀ଓΤϥʔʹͳͬͯ΋ɺΑ͠ͳʹϦ τϥΠͯ͘͠ΕΔͷͰӡ༻ָ͕ɻ

    w ։ൃڌ఺͸γϦίϯόϨʔ͚ͩͲɺ೔ຊਓ͕ىۀͨ͠ελʔτΞο ϓͰɺ೔ຊޠͷαϙʔτରԠ΋Մೳɻ࠷ۙ೔ຊΦϑΟε΋Ͱ͖ͨɻ
  12. 'MZ%BUB4ZODͷ஫ҙ఺ w ϚΠάϨʔγϣϯͰςʔϒϧߏ଄͕มΘͬͨ৔߹ʹɺ ࣗಈͰରԠͰ͖Δ΋ͷͱͰ͖ͳ͍΋ͷ͕͋Δ w ΧϥϜ௥Ճ͸0,͚ͩͲɺΧϥϜͷܕมߋ͸ςʔϒ ϧ࠶ಉظ͕ඞཁͳͲ w ͦͷଞ੍ݶࣄ߲͸υΩϡϝϯτࢀরIUUQ XXXqZEBUBDPNKBSFTPVSDFTqZEBUBTZOD

    TZTUFNTQFDJpDBUJPOT
  13. σʔλ෼ੳج൫ 3FETIJGU SFQMJDB .Z42- "QQT ,JOFTJT4USFBNT -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. ,JOFTJT4USFBNTͱ͸ w ϚωʔδυͳετϦʔϛϯάσʔλͷόοϑΝ w Ϩίʔυ௕͸࠷େ.#·Ͱొ࿥Մೳ w σϑΥϧτͰ࣌ؒอଘɺઃఆͰ೔·ͰԆ௕Մೳ w γϟʔυΛ૿΍͢͜ͱͰεέʔϧՄೳ

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

    -BNCEBΛτϦΨ͢Δઃఆͷ#BUDI4J[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-BNCEB43FETIJGUΛ΍ͬͯΔΑ͏Ͱɺ"84

    ͕؅ཧ͢Δ'JSFIPTFͷ*1ΞυϨεΛ3FETIJGUͷ4FDVSJUZ(SPVQͰڐՄͯ͋͛͠Δ ඞཁ͕͋Δͷʹ஫ҙ w ౦ژϦʔδϣϯͷ*1ΞυϨε͸ެࣜυΩϡϝϯτࢀর w IUUQEPDTBXTBNB[PODPNpSFIPTFMBUFTUEFWDPOUSPMMJOHBDDFTTIUNM
  19. σʔλ෼ੳج൫ 3FETIJGU SFQMJDB .Z42- "QQT ,JOFTJT4USFBNT -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 ,JOFTJT4USFBNT -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"/54&-&$50/"--5"#-&4ʙ͸طʹଘࡏ͢ΔϏϡʔͷΈ༗ޮͰɺޙ͔Β૿

    ͑ͨϏϡʔ΋ࣗಈͰݟ͑ΔΑ͏ʹ͢Δʹ͸"-5&3%&'"6-513*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%BUB4ZODͷಉظ͕஗Ԇͯ͠ͳ͍͔؂ࢹ͍ͨ͠ 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. 8FBSF)JSJOH Ұॹʹʮσʔλ෼ੳج൫Λ࡞Γ͍ͨʯΤϯδχΞɺ ʮσʔλ෼ੳͯ͠αʔϏεΛվળ͍ͨ͠ʯΤϯδχΞΛืूதͰ͢ IUUQTXXXXBOUFEMZDPNQSPKFDUT