bqv速習会

 bqv速習会

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

9ec2ceee3f782e64fd67fa7d82b6c1b4?s=128

Hazumi Ichijo

April 22, 2020
Tweet

Transcript

  1. ©2020 Wantedly, Inc. bqv଎शձ Apr 22, 2020 - Hazumi Ichijo

    എܠɾ֓ཁ
  2. ©2020 Wantedly, Inc. ࣗݾ঺հ Ұᑍ୺੅ .BUDIJOH4RVBE%BUB&OHJOFFSJOH5FBN ೥৽ଔೖࣾ ๺ւಓग़਎δϟεί·Ͱ௚ਐ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.  1FSTJTUFOU6%'  EFTDSJQUJPOMBCFM  UFNQMBUF ͦͷଞศརͳػೳ

  18. ©2020 Wantedly, Inc.  1FSTJTUFOU6%' ͦͷଞศརͳػೳ 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Λ࡞੒͢Δલͷ֬ೝػೳ  ςετΛ͔͚ΔΑ͏ʹ͢Δ