Upgrade to Pro — share decks privately, control downloads, hide ads and more …

bqv速習会

 bqv速習会

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

Hazumi Ichijo

April 22, 2020
Tweet

More Decks by Hazumi Ichijo

Other Decks in Programming

Transcript

  1. ©2020 Wantedly, Inc.
    bqv଎शձ
    Apr 22, 2020 - Hazumi Ichijo
    എܠɾ֓ཁ

    View Slide

  2. ©2020 Wantedly, Inc.
    ࣗݾ঺հ
    Ұᑍ୺੅
    .BUDIJOH4RVBE%BUB&OHJOFFSJOH5FBN
    ೥৽ଔೖࣾ
    ๺ւಓग़਎δϟεί·Ͱ௚ਐLN

    View Slide

  3. ©2020 Wantedly, Inc.
    ΤϯδχΞ
    #*νʔϜ
    ΤϯδχΞ
    ՄࢹԽπʔϧ
    σʔλϩάΛ࢒͢
    ՄࢹԽ͢Δ ϑΟʔυόοΫ

    View Slide

  4. ©2020 Wantedly, Inc.
    ΤϯδχΞ
    #*νʔϜ
    ΤϯδχΞ
    ՄࢹԽπʔϧ
    σʔλͷղऍ͕ζϨΔ
    ͍͍ͪͪΤϯδχΞʹਘͶΔ͔͠ͳ͍
    ΫΤϦ͕࠶ར༻Ͱ͖ͳ͍
    ίϐϖͩΒ͚ʹ
    ҙਤͤͣյ͢
    ා͍ͷͰϩάपΓ͸͍͡Βͳ͍

    View Slide

  5. ©2020 Wantedly, Inc.
    ΤϯδχΞ
    #*νʔϜ
    ΤϯδχΞ
    ՄࢹԽπʔϧ
    σʔλͷղऍ͕ζϨΔ
    ͍͍ͪͪΤϯδχΞʹਘͶΔ͔͠ͳ͍
    ΫΤϦ͕࠶ར༻Ͱ͖ͳ͍
    ίϐϖͩΒ͚ʹ
    ҙਤͤͣյ͢
    ා͍ͷͰϩάपΓ͸͍͡Βͳ͍
    w ಉҰςʔϒϧʹผछͷ
    σʔλ͕͍ࠞͬͯͨ͡
    w ҉໧஌ͩΒ͚

    View Slide

  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 ...

    View Slide

  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಺ʹදࣔ͞ΕΔهࣄ

    View Slide

  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ͰΫΤϦͷ࠶ར༻ੑΛߴΊΔ
    ΫΤϦ໊Λࣾ಺ͰҰҙʹͳΔΑ͏ʹ

    View Slide

  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 σʔλʹؔ͢Δ஌͕ࣝ޿·Β
    ͳ͍

    View Slide

  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 σʔλʹؔ͢Δ஌͕ࣝ޿·Β
    ͳ͍
    ΫΤϦ͕ݕࡧҰཡͰ͖Δ
    ΫΤϦΛࢀর͢Δ͚ͩʹ͢Δ

    View Slide

  11. ©2020 Wantedly, Inc.
    ࣍Λຬͨ͢πʔϧ͕ඞཁ
    w ϨϏϡʔΛཔΊΔ࢓૊Έ
    w ΫΤϦͷ࠶ར༻ੑΛߴΊΔ
    w ΫΤϦ໊Λࣾ಺ͰҰҙʹͳΔΑ͏ʹ͢Δ
    w #*πʔϧ͔ΒΫΤϦΛࢀর͢Δ
    w ΫΤϦ͕ҰཡݕࡧͰ͖Δ

    View Slide

  12. ©2020 Wantedly, Inc.
    ࣍Λຬͨ͢πʔϧ͕ඞཁ
    w ϨϏϡʔΛཔΊΔ࢓૊Έ
    w ΫΤϦͷ࠶ར༻ੑΛߴΊΔ
    w ΫΤϦ໊Λࣾ಺ͰҰҙʹͳΔΑ͏ʹ͢Δ
    w #*πʔϧ͔ΒΫΤϦΛࢀর͢Δ
    w ΫΤϦ͕ҰཡݕࡧͰ͖Δ
    WJFXͰ؅ཧ

    View Slide

  13. ©2020 Wantedly, Inc.
    ࣍Λຬͨ͢πʔϧ͕ඞཁ
    w ϨϏϡʔΛཔΊΔ࢓૊Έ
    w ΫΤϦͷ࠶ར༻ੑΛߴΊΔ
    w ΫΤϦ໊Λࣾ಺ͰҰҙʹͳΔΑ͏ʹ͢Δ
    w #*πʔϧ͔ΒΫΤϦΛࢀর͢Δ
    w ΫΤϦ͕ҰཡݕࡧͰ͖Δ
    (JU)VCͰ؅ཧ

    View Slide

  14. ©2020 Wantedly, Inc.
    CRWͰղܾ

    View Slide

  15. ©2020 Wantedly, Inc.
    CRW
    WJFXΛίʔυͰ؅ཧ͢ΔͨΊͷπʔϧ
    ͦͷଞͷWJFXΛαϙʔτ͢Δػೳ

    View Slide

  16. ©2020 Wantedly, Inc.
    SELECT id
    FROM UNNEST([1,2,3,4,5,6]) as id
    [email protected]

    View Slide

  17. ©2020 Wantedly, Inc.
    1FSTJTUFOU6%'
    EFTDSJQUJPOMBCFM
    UFNQMBUF
    ͦͷଞศརͳػೳ

    View Slide

  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
    [email protected]
    w WJFX಺Ͱ͸6%'͕࢖͑ͳ͍ͨΊ
    w ࣾ಺ͷศར6%'Λ޿ΊΔͨΊ

    View Slide

  19. ©2020 Wantedly, Inc.
    EFTDSJQUJPOMBCFM
    ͦͷଞศརͳػೳ
    metadata:
    description: |
    ࣾ಺଎शձ༻ͷΫΤϦɻBIπʔϧ͔Β͸ࢀর͞Ε͍ͯͳ͍ɻ
    label:
    foo: bar
    [email protected]
    w ରԠ͢ΔWJFXͷEFTDSJQUJPOMBCFMʹ൓ө͞ΕΔ
    w %BUB$BUBMPHͳͲͰݕࡧՄೳʹ

    View Slide

  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(
    "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
    [email protected]@DPVOUTRM

    View Slide

  21. ©2020 Wantedly, Inc.
    ௥Ճ༧ఆͷػೳ
    WJFX͝ͱͷ࢖ΘΕΔස౓Λग़͢
    ࣮ࡍʹWJFXΛ࡞੒͢Δલͷ֬ೝػೳ
    ςετΛ͔͚ΔΑ͏ʹ͢Δ

    View Slide