bqv速習会

https://github.com/rerost/bqv についてざっくり紹介する速習会用のスライド

Transcript

1. ©2020 Wantedly, Inc. bqv଎शձ Apr 22, 2020 - Hazumi Ichijo എܠɾ֓ཁ
2. ©2020 Wantedly, Inc. ࣗݾ঺հ Ұᑍ
   ୺੅
   .BUDIJOH
   4RVBE
   %BUB
   &OHJOFFSJOH
   5FBN
   ೥৽ଔೖࣾ
   ๺ւಓग़਎
   δϟεί·Ͱ௚ਐLN
3. ©2020 Wantedly, Inc. ΤϯδχΞ #*νʔϜ
   ΤϯδχΞ ՄࢹԽπʔϧ σʔλϩάΛ࢒͢ ՄࢹԽ͢Δ ϑΟʔυόοΫ
4. ©2020 Wantedly, Inc. ΤϯδχΞ #*νʔϜ
   ΤϯδχΞ ՄࢹԽπʔϧ σʔλͷղऍ͕ζϨΔ
   
   ͍͍ͪͪΤϯδχΞʹਘͶΔ͔͠ͳ͍ ΫΤϦ͕࠶ར༻Ͱ͖ͳ͍
   
   ίϐϖͩΒ͚ʹ ҙਤͤͣյ͢
   
   ා͍ͷͰϩάपΓ͸͍͡Βͳ͍
5. ©2020 Wantedly, Inc. ΤϯδχΞ #*νʔϜ
   ΤϯδχΞ ՄࢹԽπʔϧ σʔλͷղऍ͕ζϨΔ
   
   ͍͍ͪͪΤϯδχΞʹਘͶΔ͔͠ͳ͍ ΫΤϦ͕࠶ར༻Ͱ͖ͳ͍
   
   ίϐϖͩΒ͚ʹ ҙਤͤͣյ͢
   
   ා͍ͷͰϩάपΓ͸͍͡Βͳ͍ w ಉҰςʔϒϧʹผछͷ σʔλ͕͍ࠞͬͯͨ͡
   w ҉໧஌ͩΒ͚
6. ©2020 Wantedly, Inc. WITH people_posts AS ( SELECT ... FROM ... INNER JOIN ... WHERE deleted_at IS NOT NULL AND ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ) SELECT COUNT(*) OVER ... AS daily_count, IF(week = day, COUNT(*) OVER ..., NULL) AS weekly_count, IF(month = day, COUNT(*) OVER ..., NULL) AS monthly_count, COUNT(DISTINCT user_id) OVER ... AS daily_users, IF(week = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS weekly_users, IF(month = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS monthly_users FROM people_posts INNER JOIN ...
7. ©2020 Wantedly, Inc. WITH people_posts AS ( SELECT ... FROM ... INNER JOIN ... WHERE deleted_at IS NOT NULL AND ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ) SELECT COUNT(*) OVER ... AS daily_count, IF(week = day, COUNT(*) OVER ..., NULL) AS weekly_count, IF(month = day, COUNT(*) OVER ..., NULL) AS monthly_count, COUNT(DISTINCT user_id) OVER ... AS daily_users, IF(week = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS weekly_users, IF(month = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS monthly_users FROM people_posts INNER JOIN ... w ຊ౰ʹϢʔβͷ౤ߘʹߜΓࠐΊ͍ͯ Δ͔Θ͔Βͳ͍
   w ίϐϖ஍ࠈ
   w ͜ͷ໊લʹΑΓࣾ಺Ͱ΋ղऍ͕όϥ όϥʹͳͬͯ͠·͏
   w ྫ
   w Ϣʔβ͕ϑΟʔυʹ౤ߘͨ͠هࣄ
   w QFPQMF಺ʹදࣔ͞ΕΔهࣄ
8. ©2020 Wantedly, Inc. WITH people_posts AS ( SELECT ... FROM ... INNER JOIN ... WHERE deleted_at IS NOT NULL AND ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ) SELECT COUNT(*) OVER ... AS daily_count, IF(week = day, COUNT(*) OVER ..., NULL) AS weekly_count, IF(month = day, COUNT(*) OVER ..., NULL) AS monthly_count, COUNT(DISTINCT user_id) OVER ... AS daily_users, IF(week = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS weekly_users, IF(month = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS monthly_users FROM people_posts INNER JOIN ... w ຊ౰ʹϢʔβͷ౤ߘʹߜΓࠐΊ͍ͯ Δ͔Θ͔Βͳ͍
   w ίϐϖ஍ࠈ
   w ͜ͷ໊લʹΑΓࣾ಺Ͱ΋ղऍ͕όϥ όϥʹͳͬͯ͠·͏
   w ྫ
   w Ϣʔβ͕ϑΟʔυʹ౤ߘͨ͠هࣄ
   w QFPQMF಺ʹදࣔ͞ΕΔهࣄ ϨϏϡʔΛཔΊΔ࢓૊Έ WJFXͰΫΤϦͷ࠶ར༻ੑΛߴΊΔ ΫΤϦ໊Λࣾ಺ͰҰҙʹͳΔΑ͏ʹ
9. ©2020 Wantedly, Inc. WITH people_posts AS ( SELECT ... FROM ... INNER JOIN ... WHERE deleted_at IS NOT NULL AND ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ) SELECT COUNT(*) OVER ... AS daily_count, IF(week = day, COUNT(*) OVER ..., NULL) AS weekly_count, IF(month = day, COUNT(*) OVER ..., NULL) AS monthly_count, COUNT(DISTINCT user_id) OVER ... AS daily_users, IF(week = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS weekly_users, IF(month = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS monthly_users FROM people_posts INNER JOIN ... #*πʔϧͷதͷΈʹ͜ͷΫΤϦ ͕͋ΔͷͰɺ
   w σʔλͷมߋ͕ࢥΘͳ͍ͱ͜ Ζ·ͰӨڹͯ͠͠·͏
   w σʔλʹؔ͢Δ஌͕ࣝ޿·Β ͳ͍
10. ©2020 Wantedly, Inc. WITH people_posts AS ( SELECT ... FROM ... INNER JOIN ... WHERE deleted_at IS NOT NULL AND ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ), ... AS ( SELECT ... FROM ... INNER JOIN ... WHERE ... ) SELECT COUNT(*) OVER ... AS daily_count, IF(week = day, COUNT(*) OVER ..., NULL) AS weekly_count, IF(month = day, COUNT(*) OVER ..., NULL) AS monthly_count, COUNT(DISTINCT user_id) OVER ... AS daily_users, IF(week = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS weekly_users, IF(month = day, COUNT(DISTINCT user_id) OVER ..., NULL) AS monthly_users FROM people_posts INNER JOIN ... #*πʔϧͷதͷΈʹ͜ͷΫΤϦ ͕͋ΔͷͰɺ
    w σʔλͷมߋ͕ࢥΘͳ͍ͱ͜ Ζ·ͰӨڹͯ͠͠·͏
    w σʔλʹؔ͢Δ஌͕ࣝ޿·Β ͳ͍ ΫΤϦ͕ݕࡧҰཡͰ͖Δ ΫΤϦΛࢀর͢Δ͚ͩʹ͢Δ
11. ©2020 Wantedly, Inc. ࣍Λຬͨ͢πʔϧ͕ඞཁ
    w ϨϏϡʔΛཔΊΔ࢓૊Έ
    w ΫΤϦͷ࠶ར༻ੑΛߴΊΔ
    w ΫΤϦ໊Λࣾ಺ͰҰҙʹͳΔΑ͏ʹ͢Δ
    w #*πʔϧ͔ΒΫΤϦΛࢀর͢Δ
    w ΫΤϦ͕ҰཡݕࡧͰ͖Δ
12. ©2020 Wantedly, Inc. ࣍Λຬͨ͢πʔϧ͕ඞཁ
    w ϨϏϡʔΛཔΊΔ࢓૊Έ
    w ΫΤϦͷ࠶ར༻ੑΛߴΊΔ
    w ΫΤϦ໊Λࣾ಺ͰҰҙʹͳΔΑ͏ʹ͢Δ
    w #*πʔϧ͔ΒΫΤϦΛࢀর͢Δ
    w ΫΤϦ͕ҰཡݕࡧͰ͖Δ WJFXͰ؅ཧ
13. ©2020 Wantedly, Inc. ࣍Λຬͨ͢πʔϧ͕ඞཁ
    w ϨϏϡʔΛཔΊΔ࢓૊Έ
    w ΫΤϦͷ࠶ར༻ੑΛߴΊΔ
    w ΫΤϦ໊Λࣾ಺ͰҰҙʹͳΔΑ͏ʹ͢Δ
    w #*πʔϧ͔ΒΫΤϦΛࢀর͢Δ
    w ΫΤϦ͕ҰཡݕࡧͰ͖Δ (JU)VCͰ؅ཧ
14. ©2020 Wantedly, Inc. CRWͰղܾ
15. ©2020 Wantedly, Inc. CRW
     WJFXΛίʔυͰ؅ཧ͢ΔͨΊͷπʔϧ
     ͦͷଞͷWJFXΛαϙʔτ͢Δػೳ
16. ©2020 Wantedly, Inc. SELECT id FROM UNNEST([1,2,3,4,5,6]) as id FYBNQMFEBUBTFUFYBNQMF@WJFXTRM
17. ©2020 Wantedly, Inc.  1FSTJTUFOU
    6%'
     EFTDSJQUJPOMBCFM
     UFNQMBUF ͦͷଞศརͳػೳ
18. ©2020 Wantedly, Inc.  1FSTJTUFOU
    6%' ͦͷଞศརͳػೳ CREATE FUNCTION dataset.test_udf(x FLOAT64, y FLOAT64) RETURNS FLOAT64 LANGUAGE js AS """ return x*y; """; https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions?hl=ja VEGEBUBTFUUFTU@VEGTRM w WJFX಺Ͱ͸6%'͕࢖͑ͳ͍ͨΊ
    w ࣾ಺ͷศར6%'Λ޿ΊΔͨΊ
19. ©2020 Wantedly, Inc.  EFTDSJQUJPOMBCFM ͦͷଞศརͳػೳ metadata: description: | ࣾ಺଎शձ༻ͷΫΤϦɻBIπʔϧ͔Β͸ࢀর͞Ε͍ͯͳ͍ɻ label: foo: bar VEGEBUBTFUFYBNQMF@WJFXZNM w ରԠ͢ΔWJFXͷEFTDSJQUJPOMBCFMʹ൓ө͞ΕΔ
    w %BUB$BUBMPHͳͲͰݕࡧՄೳʹ
20. ©2020 Wantedly, Inc.  UFNQMBUF ͦͷଞศརͳػೳ CREATE TEMP FUNCTION render_query(tablename STRING) RETURNS STRING LANGUAGE js AS """ return ` SELECT COUNT(1) FROM ${tablename} `; """; SELECT CONCAT(name, "_count") AS name, render_query(tablename) AS query, FROM UNNEST(ARRAY<STRUCT<name string, tablename string>>[ STRUCT( "311_request" AS name, """`bigquery-public-data.austin_311.311_request`""" AS tablename ) ]) SELECT COUNT(1) FROM `bigquery-public-data.austin_311.311_request` UFNQMBUFEBUBTFUDPVOUTTRM FYBNQMFEBUBTFU@SFRVFTU@DPVOUTRM
21. ©2020 Wantedly, Inc. ௥Ճ༧ఆͷػೳ
     WJFX͝ͱͷ࢖ΘΕΔස౓Λग़͢
     ࣮ࡍʹWJFXΛ࡞੒͢Δલͷ֬ೝػೳ
     ςετΛ͔͚ΔΑ͏ʹ͢Δ