BigData-JAWS #9: Data Analytics Infra for CrowdWorks

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

$SPXE8PSLTͱ͸

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

'MZ%BUB4ZODͷςʔϒϧ௥Ճ

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

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) ;

Slide 23

Slide 23 text

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 ;

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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-จ͕֬ೝͰ͖Δ

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

ςʔϒϧͷ࣮ମΛݟͤͣʹ Ϗϡʔ͚ͩݟͤΔ 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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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ͱ͍͏πʔϧΛ࡞ͬͨ

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

γεςϜϏϡʔͷ؂ࢹ߲໨ 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

Slide 41

Slide 41 text

ݱࡏͷΩϡʔ͝ͱͷΫΤϦ࣮ߦ࣌ؒ 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

Slide 42

Slide 42 text

ݱࡏͷΩϡʔ͝ͱͷΫΤϦ࣮ߦ਺ 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

Slide 43

Slide 43 text

௚ۙ෼ͷϢʔβ͝ͱͷΫΤϦ࣮ߦ࣌ؒ 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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

%BUBEPHμογϡϘʔυ

Slide 47

Slide 47 text

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