Tweet
Tweet

Slide 1

Slide 1 text

1 σʔλϚωδϝϯτ͕ͪΐͬͱָʹͳΔ BigQuery؂ࠪϩάͷ࢖͍ํ 2022/07/27 Nagai Shinya (@__hiza__)

Slide 2

Slide 2 text

2 ● ӬҪ৳໻ (@__hiza__) ● גࣜձࣾϝϧΧϦ / Analytics Infra Team ॴଐ ● Analystʹཱ͍ۙ৔Ͱ෼ੳ؀ڥͷ੔උΛਐΊ͍ͯ·͢ ൃදऀ

Slide 3

Slide 3 text

3 ϝϧΧϦͷσʔλ׆༻ঢ়گ ར༻ऀ͕ଟ͘ɺ༻్͕෯޿͍ → σʔλΛͲ͏࢖͍ͬͯΔͷ͔೺Ѳ͠ʹ͍͘ ج൫ BigQuery ར༻ऀ਺ 900໊/݄ σʔληοτ਺ 1500+ ༻్ σʔλ෼ੳɺMLɺϚʔέςΟϯάɺΧε λϚʔαϙʔτͳͲͳͲ

Slide 4

Slide 4 text

4 ҎԼ2ͭͷϩάͷ͜ͱΛࢦͯ͠·͢ ● INFORMATION_SCHEMA.JOBS_BY_* ● BigQuery audit log (Data access) ڞ௨఺ ● ͩΕ͕ɺ͍ͭɺͲͷςʔϒϧʹΞΫηεͨ͠ͷ͔ɺϩά͕औΕ Δɻ ͦΕͧΕͷओͳϝϦοτ ● Audit log → view΁ͷΞΫηεϩά͕औΕΔɻྻ୯ҐͷΞΫηε ϩά͕औΕΔɻ ● JOBS_BY_* → schema͕୯७Ͱूܭ͠΍͍͢ɻ גࣜձࣾϞϊλϩ΢͞·ͷ 
 σʔλ؅ཧʹ໾ཱͭϝλσʔλʹؔ͢ΔษڧձΛࣾ಺֎ Ͱ։࠵͠·ͨ͠ - MonotaRO Tech Blogͷهࣄ΋͓͢͢Ί Ͱ͢ ؂ࠪϩάͱ͸?

Slide 5

Slide 5 text

5 සൟʹ࢖ΘΕ͍ͯΔςʔϒϧΛௐ΂Δ

Slide 6

Slide 6 text

6 ● ৭ʑվળ͍ͨ͠ςʔϒϧ͕͋Δ͕ɺશͯΛҰ౓ʹվળ͢Δࣄ͸Ͱ͖ͳ͍ɻ ● Ͳͷςʔϒϧ͕ॏཁͳͷ͔஌Γ͍ͨɻ Α͋͘Δ՝୊

Slide 7

Slide 7 text

7 ● ϝϧΧϦͷBigQueryʹ͋Δσʔληοτ਺ɻ ○ ͓Αͦ1500+ ○ શͯͷςʔϒϧΛҰ౓ʹ࢖͍΍͘͢վળ͢Δࣄ͸Ͱ ͖ͳ͍ɻ ● ςʔϒϧ͝ͱʹར༻ऀ਺Λूܭ͢Δͱ... ○ BigQueryϢʔβʔͷ1ׂҎ্͕࢖ͬͨςʔϒϧ͸40 ΄Ͳʹա͗ͳ͔ͬͨɻ ׆༻ྫ ςʔϒϧ͝ͱʹར༻ऀ਺Λूܭͯ͠ϥϯΩϯά ňͲ͜ͷվળ͔ΒऔΓ૊Ήͱྑ͍ͷ͔ʼnٞ࿦ͷ͖͔͚ͬʹͳΔ ͜ͷ෼ੳख๏͸2019೥ʹyuzutas0͞Μ͕঺հͨ͠΋ͷͰ͢ https://speakerdeck.com/yuzutas0/20190905

Slide 8

Slide 8 text

8 มߋͷӨڹΛௐ΂Δ

Slide 9

Slide 9 text

9 ● ʮςʔϒϧͷ࢓༷มߋͷӨڹൣғΛચ͍ग़͍ͨ͠ʯ ○ ྫ: ΧϥϜ໊ΛมߋˠͦͷΧϥϜΛࢀর͢ΔΫΤϦ͕ΤϥʔʹͳΔɻ
 ● BigQuery্ʹ͋Δσʔληοτʹ͸publish͞ΕͨAPIͷΑ͏ͳ৻ॏͳมߋ͕ٻΊΒ ΕΔɻ ○ ΞΫηεݖ͕͋Δਓ͢΂ͯʹӨڹ͕͋Γ͑ΔͨΊɻ Α͋͘Δ՝୊

Slide 10

Slide 10 text

10 ● ن໛ͷݟੵ΋Γ ○ มߋ͢ΔΧϥϜΛԿਓ͕ͲΜͳස౓Ͱ࢖ͬͯ ͍Δ͔ɻ ○ ར༻ऀ͕ଟ͍ςʔϒϧͰ͋ͬͯ΋ɺΧϥϜ୯Ґ ͰݟΔͱͦΕ΄Ͳ࢖ΘΕ͍ͯͳ͍৔߹΋͋ Δɻ ● ӨڹΛड͚Δਓͷಛఆ ○ มߋ͢ΔΧϥϜΛ࢖ͬͨࣄ͕͋ΔਓͷΞΧ΢ ϯτΛಛఆͰ͖Δɻ ׆༻ྫ ྻ͝ͱͷΞΫηεঢ়گΛௐ΂Δ Өڹͷن໛΍ɺӨڹΛड͚Δਓ͕ௐ΂΍͘͢ͳΔ

Slide 11

Slide 11 text

11 ● ૊৫৘ใͱͷඥ෇͚ ○ ؂ࠪϩάʹ͸ΞΫηεͨ͠ਓ ͷΞΧ΢ϯτ৘ใ(email)͕ ࢒Δɻ ○ ਓࣄDBΛleft join͢Δ͜ͱͰ νʔϜ୯Ґͷ෼ੳ͕Ͱ͖Δɻ ● ӨڹΛड͚ΔνʔϜΛಛఆͰ͖ Δɻ ○ ྫ: ໼ҹͰࣔͨ͠ྻ͸ओʹ Team D͕࢖͍ͬͯΔ ׆༻ྫ ྻ͝ͱͷΞΫηεঢ়گΛɺνʔϜ୯ҐͰूܭ ӨڹΛड͚ΔνʔϜΛௐ΂΍͘͢ͳΔ

Slide 12

Slide 12 text

12 ࡞ͬͨ෺͕࢖ΘΕ͍ͯΔͷ͔ௐ΂Δ

Slide 13

Slide 13 text

13 ● ࣦഊྫ ○ ར༻ऀ͕૿͑ͳ͍ɻ࢖ΘΕ͍ͯͳ͍ɻ ͋Βͨʹ࡞੒ͨ͠ςʔϒϧͷར༻ऀ਺Λ೔ຖʹूܭ ࣦഊʹؾ͚ͮΔɻňԿ͕μϝͩͬͨͷ͔?ʼnňࠓޙ΋ϝϯς͢΂͖͔?ʼnٞ࿦ͷ͖ͬ ͔͚ʹͳΔɻ ● ੒ޭྫ ○ ೔͕ܦͭʹͭΕͯར༻ऀ͕૿͍͑ͯΔ


Slide 14

Slide 14 text

14 ؾ͖ͮ ͢΂ͯͷςʔϒϧͷதͰ΋
 Α͘࢖ΘΕΔͷ͸͘͝Ұ෦ σʔλͷར༻ʹ͍ͭͯྑ͘ݱΕΔ܏޲ˠ ”ॏཁͳͷ͸શମͷதͷҰ෦” ӡ༻ʹ͓͍ͯ͸ɺ
 ॏཁͳσʔλΛಛఆͯ͠ɺ ूதతʹϝϯςφϯεɾվ ળ͢Δͷ͕ޮ཰త Α͘࢖ΘΕΔςʔϒϧͷதͰ΋
 Α͘࢖ΘΕΔΧϥϜ͸͘͝Ұ෦ <

Slide 15

Slide 15 text

15 ● Α͔ͬͨΒTwitter ϑΥϩʔ͓ئ͍͠·͢ ○ @__hiza__ Ξϯμʔόʔ͸2ͭͣͭ! ○ ϝϧΧϦͰͷ෼ੳ؀ڥ੔උʹ͍ͭͯϒϩάهࣄͷ౤ߘ΍ొஃͳͲΛࠂ஌͠·͢
 ● ϒϩά΋ݟͯݟ͍ͯͩ͘͞ ○ Mercari Analytics BlogͰ🔍 ○ σʔλͷ੔උ͚ͩͰͳ͘෼ੳͷهࣄ΋͋Γ·͢ɻ
 ● MeetyͰ͓࿩͠·͠ΐ͏ ○ اۀͷσʔλ෼ੳ΍σʔλͷ؅ཧʹ͍ͭͯΧδϡΞϧʹ࿩͍ͨ͠Ͱ͢! એ఻