Support Table Output from Stored Procedures [190785546]

Assigned

Feature Request

Status Update

No update yet.

Description

de...@derekperkins.com

created issue #1

Jun 11, 2021 09:09PM

What you would like to accomplish:

We are really having a hard time making BigQuery / BI tools easy to use for our customers. Queries to our raw data are semi-slow / expensive, plus are complex. To date, we have given users 1000+ line queries for it, but those are very intimidating, plus prone to break.

For our dashboard users, we have a multi-stage caching setup where we hash the inputs of a query, write the output to a BigQuery table, then use that as the data source for further queries / pagination. I thought I could replace that system with a stored procedure, and I can get 99.9% of the way there.

The other benefit of using a script is that I can provide a stable api for our DataStudio (or whatever BI tool) CALL rollup_data_func(a, b, c), but not lock myself into implementation details. I can add/remove caching tiers, change source tables/fields, etc., as long as I maintain that api contract.

The problem is BigQuery doesn't allow you to interact a table response. When using the api, the script output is exactly what I would expect.

"The jobs.getQueryResults method returns the query results for the last SELECT, DML, or DDL statement to execute in the script, with no query results if none of the above statements have executed"

All that I'm looking for is for BigQuery to have the same semantics. Let me interact with that output, SELECT * FROM (CALL rollup_data_func(a, b, c)) ORDER BY col1.

If applicable, reasons why alternative solutions are not sufficient:

Without that, it is impossible to connect data directly from a stored procedure to DataStudio (or any other BigQuery connector). Writing to a table is not a workaround, as I'm already lazily creating a temporary table, then using that output. I still have to trigger the creation of that table, and using a cron to execute every possible combination of input parameters isn't possible, and I can't require my users to build in a custom "generate data" button that they have to click whenever the change parameters.

Another option is that instead of using pre-built BigQuery connectors, we would have to build our own custom integrations into every BI tool on the planet. We don't have the resources, plus that would reduce BigQuery's spend potential.

I've tried returning the query string and executing that inline, but that errors out on the DataStudio side because of the script declaration. That also rules out returning the table data in a predefined array of structs as an OUT variable.

SELECT t0.description, t0.name, t0.team_id, t0.team_slug FROM (DECLARE query string;
CALL project.dataset.rollup_by_brand('2021-05-01', '2021-06-01', ['Brand 1', 'Brand 2'], ['Unpaid'], ['- All Keywords -'], query);
EXECUTE IMMEDIATE query;
SELECT * FROM project.dataset.latest_brands) AS t0 LIMIT 100;

Sample Procedure

CREATE OR REPLACE PROCEDURE nozzledata.nozzle_customer.rollup_by_brand (
  IN requested_start TIMESTAMP,
  IN requested_end TIMESTAMP,
  IN brand_names ARRAY<STRING>,
  IN segment_names ARRAY<STRING>,
  IN keyword_groups ARRAY<STRING>,
  OUT query string
)

BEGIN

DECLARE dataset_id STRING DEFAULT 'nozzle_customer_raw';
DECLARE group_by STRING;
DECLARE rollup_by STRING;
DECLARE cache_table STRING DEFAULT 'rollup__by__brand__fresh';
DECLARE segment_sql STRING;
DECLARE brand_sql STRING;
DECLARE keyword_group_sql STRING;
DECLARE stmt STRING;
DECLARE stmt_hash INT64;

SET group_by = 'segment_id, brand_id, keyword_group_hash';
SET rollup_by = 'brand';

SET (segment_sql, brand_sql, keyword_group_sql) = (
  ' AND segment_id IN UNNEST((SELECT ARRAY_AGG(segment_id) FROM nozzledata.nozzle_customer.latest_segments WHERE name IN UNNEST(@segment_names))) -- ' || FARM_FINGERPRINT(ARRAY_TO_STRING(segment_names, '-')),
  ' AND brand_id IN UNNEST((SELECT ARRAY_AGG(brand_id) FROM nozzledata.nozzle_customer.latest_brands WHERE name IN UNNEST(@brand_names))) -- ' || FARM_FINGERPRINT(ARRAY_TO_STRING(brand_names, '-')),
  ' AND keyword_group_hash IN UNNEST((SELECT ARRAY_AGG(keyword_group_hash) FROM nozzledata.nozzle_customer.latest_keyword_groups WHERE keyword_group IN UNNEST(@keyword_groups))) -- ' || FARM_FINGERPRINT(ARRAY_TO_STRING(keyword_groups, '-'))
);

IF requested_start < TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 90 DAY)
  THEN SET cache_table = 'rollup__by__brand__historical';
END IF;

IF ARRAY_LENGTH(segment_names) = 0
  THEN SET segment_sql = '';
END IF;

IF ARRAY_LENGTH(brand_names) = 0
  THEN SET brand_sql = '';
END IF;

IF ARRAY_LENGTH(keyword_groups) = 0
  THEN SET keyword_group_sql = '';
END IF;

SET stmt = FORMAT("""
-- BQ FILL QUERY CACHE %s GROUP BY %s ROLLUP BY %s

WITH
-- get the average of all the minimum values
per_serp_metrics_aggregation AS (
  SELECT
    *,

    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__total) AS unique_keywords__count__total,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_1_3) AS unique_keywords__count__rank_group_1_3,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_1_5) AS unique_keywords__count__rank_group_1_5,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_1_10) AS unique_keywords__count__rank_group_1_10,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_1) AS unique_keywords__count__rank_group_1,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_2) AS unique_keywords__count__rank_group_2,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_3) AS unique_keywords__count__rank_group_3,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_4_6) AS unique_keywords__count__rank_group_4_6,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_7_10) AS unique_keywords__count__rank_group_7_10,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_11_15) AS unique_keywords__count__rank_group_11_15,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_16_20) AS unique_keywords__count__rank_group_16_20,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_21_50) AS unique_keywords__count__rank_group_21_50,
    HLL_COUNT.EXTRACT(unique_keywords__hll_sketch__rank_group_51) AS unique_keywords__count__rank_group_51,

    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__total) AS unique_urls__count__total,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_1_3) AS unique_urls__count__rank_group_1_3,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_1_5) AS unique_urls__count__rank_group_1_5,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_1_10) AS unique_urls__count__rank_group_1_10,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_1) AS unique_urls__count__rank_group_1,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_2) AS unique_urls__count__rank_group_2,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_3) AS unique_urls__count__rank_group_3,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_4_6) AS unique_urls__count__rank_group_4_6,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_7_10) AS unique_urls__count__rank_group_7_10,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_11_15) AS unique_urls__count__rank_group_11_15,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_16_20) AS unique_urls__count__rank_group_16_20,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_21_50) AS unique_urls__count__rank_group_21_50,
    HLL_COUNT.EXTRACT(unique_urls__hll_sketch__rank_group_51) AS unique_urls__count__rank_group_51,

  FROM nozzledata.%s.%s
  WHERE requested >= @requested_start AND requested <= @requested_end AND segment_id > 0
  %s
  %s
  %s
),

-- used for pagination
distinct_requested AS (
  SELECT DISTINCT requested FROM per_serp_metrics_aggregation
),

latest_requested AS (
  SELECT
    MAX(requested) AS requested
  FROM per_serp_metrics_aggregation
),

earliest_requested AS (
  SELECT
    MIN(requested) AS requested
  FROM per_serp_metrics_aggregation
),

-- calculate min metrics per keyword_id, group_by, and requested
group_by_totals AS (
  SELECT
    requested,
    %s,
    keyword_count,


    adwords_search_volume__sum__total AS adwords_search_volume__sum__total__value,
    adwords_search_volume__sum__total - FIRST_VALUE(adwords_search_volume__sum__total) OVER (change) AS adwords_search_volume__sum__total__change,

    summary_metrics__percentage_of_viewport__sum__total AS summary_metrics__percentage_of_viewport__sum__total__value,
    summary_metrics__percentage_of_viewport__sum__total - FIRST_VALUE(summary_metrics__percentage_of_viewport__sum__total) OVER (change) AS summary_metrics__percentage_of_viewport__sum__total_change,
    
    summary_metrics__percentage_of_dom__sum__total AS summary_metrics__percentage_of_dom__sum__total__value,
    summary_metrics__percentage_of_dom__sum__total - FIRST_VALUE(summary_metrics__percentage_of_dom__sum__total) OVER (change) AS summary_metrics__percentage_of_dom__sum__total_change,
    
    summary_metrics__click_through_rate__sum__total AS summary_metrics__click_through_rate__sum__total__value,
    summary_metrics__click_through_rate__sum__total - FIRST_VALUE(summary_metrics__click_through_rate__sum__total) OVER (change) AS summary_metrics__click_through_rate__sum__total_change,
    
    summary_metrics__estimated_traffic__sum__total AS summary_metrics__estimated_traffic__sum__total__value,
    summary_metrics__estimated_traffic__sum__total - FIRST_VALUE(summary_metrics__estimated_traffic__sum__total) OVER (change) AS summary_metrics__estimated_traffic__sum__total_change,
    
    summary_metrics__ppc_value__sum__total AS summary_metrics__ppc_value__sum__total__value,
    summary_metrics__ppc_value__sum__total - FIRST_VALUE(summary_metrics__ppc_value__sum__total) OVER (change) AS summary_metrics__ppc_value__sum__total_change,
    
    summary_metrics__results__count__total AS summary_metrics__results__count__total__value,
    summary_metrics__results__count__total - FIRST_VALUE(summary_metrics__results__count__total) OVER (change) AS summary_metrics__results__count__total_change,
    

    top_rank__avg__total AS top_rank__avg__total__value,
    top_rank__avg__total - FIRST_VALUE(top_rank__avg__total) OVER (change) AS top_rank__avg__total__change,


    top_stat_base_rank__avg__total AS top_stat_base_rank__avg__total__value,
    top_stat_base_rank__avg__total - FIRST_VALUE(top_stat_base_rank__avg__total) OVER (change) AS top_stat_base_rank__avg__total__change,
    

    top_pixels_from_top__avg__total AS top_pixels_from_top__avg__total__value,
    top_pixels_from_top__avg__total - FIRST_VALUE(top_pixels_from_top__avg__total) OVER (change) AS top_pixels_from_top__avg__total__change,


    click_through_rate__avg__total AS click_through_rate__avg__total__value,
    click_through_rate__avg__total - FIRST_VALUE(click_through_rate__avg__total) OVER (change) AS click_through_rate__avg__total__change,


    percentage_of_viewport__avg__total AS percentage_of_viewport__avg__total__value,
    percentage_of_viewport__avg__total - FIRST_VALUE(percentage_of_viewport__avg__total) OVER (change) AS percentage_of_viewport__avg__total__change,


    percentage_of_dom__avg__total AS percentage_of_dom__avg__total__value,
    percentage_of_dom__avg__total - FIRST_VALUE(percentage_of_dom__avg__total) OVER (change) AS percentage_of_dom__avg__total__change,


    estimated_traffic__sum__total AS estimated_traffic__sum__total__value,
    estimated_traffic__sum__total - FIRST_VALUE(estimated_traffic__sum__total) OVER (change) AS estimated_traffic__sum__total__change,

    estimated_traffic__sum__rank_group_1 AS estimated_traffic__sum__rank_group_1__value,
    estimated_traffic__sum__rank_group_1 - FIRST_VALUE(estimated_traffic__sum__rank_group_1) OVER (change) AS estimated_traffic__sum__rank_group_1__change,

    estimated_traffic__sum__rank_group_2 AS estimated_traffic__sum__rank_group_2__value,
    estimated_traffic__sum__rank_group_2 - FIRST_VALUE(estimated_traffic__sum__rank_group_2) OVER (change) AS estimated_traffic__sum__rank_group_2__change,

    estimated_traffic__sum__rank_group_3 AS estimated_traffic__sum__rank_group_3__value,
    estimated_traffic__sum__rank_group_3 - FIRST_VALUE(estimated_traffic__sum__rank_group_3) OVER (change) AS estimated_traffic__sum__rank_group_3__change,

    estimated_traffic__sum__rank_group_4_6 AS estimated_traffic__sum__rank_group_4_6__value,
    estimated_traffic__sum__rank_group_4_6 - FIRST_VALUE(estimated_traffic__sum__rank_group_4_6) OVER (change) AS estimated_traffic__sum__rank_group_4_6__change,

    estimated_traffic__sum__rank_group_7_10 AS estimated_traffic__sum__rank_group_7_10__value,
    estimated_traffic__sum__rank_group_7_10 - FIRST_VALUE(estimated_traffic__sum__rank_group_7_10) OVER (change) AS estimated_traffic__sum__rank_group_7_10__change,

    estimated_traffic__sum__rank_group_11_15 AS estimated_traffic__sum__rank_group_11_15__value,
    estimated_traffic__sum__rank_group_11_15 - FIRST_VALUE(estimated_traffic__sum__rank_group_11_15) OVER (change) AS estimated_traffic__sum__rank_group_11_15__change,

    estimated_traffic__sum__rank_group_16_20 AS estimated_traffic__sum__rank_group_16_20__value,
    estimated_traffic__sum__rank_group_16_20 - FIRST_VALUE(estimated_traffic__sum__rank_group_16_20) OVER (change) AS estimated_traffic__sum__rank_group_16_20__change,

    estimated_traffic__sum__rank_group_21_50 AS estimated_traffic__sum__rank_group_21_50__value,
    estimated_traffic__sum__rank_group_21_50 - FIRST_VALUE(estimated_traffic__sum__rank_group_21_50) OVER (change) AS estimated_traffic__sum__rank_group_21_50__change,

    estimated_traffic__sum__rank_group_51 AS estimated_traffic__sum__rank_group_51__value,
    estimated_traffic__sum__rank_group_51 - FIRST_VALUE(estimated_traffic__sum__rank_group_51) OVER (change) AS estimated_traffic__sum__rank_group_51__change,


    ppc_value__sum__total AS ppc_value__sum__total__value,
    ppc_value__sum__total - FIRST_VALUE(ppc_value__sum__total) OVER (change) AS ppc_value__sum__total__change,

    ppc_value__sum__rank_group_1 AS ppc_value__sum__rank_group_1__value,
    ppc_value__sum__rank_group_1 - FIRST_VALUE(ppc_value__sum__rank_group_1) OVER (change) AS ppc_value__sum__rank_group_1__change,

    ppc_value__sum__rank_group_2 AS ppc_value__sum__rank_group_2__value,
    ppc_value__sum__rank_group_2 - FIRST_VALUE(ppc_value__sum__rank_group_2) OVER (change) AS ppc_value__sum__rank_group_2__change,

    ppc_value__sum__rank_group_3 AS ppc_value__sum__rank_group_3__value,
    ppc_value__sum__rank_group_3 - FIRST_VALUE(ppc_value__sum__rank_group_3) OVER (change) AS ppc_value__sum__rank_group_3__change,

    ppc_value__sum__rank_group_4_6 AS ppc_value__sum__rank_group_4_6__value,
    ppc_value__sum__rank_group_4_6 - FIRST_VALUE(ppc_value__sum__rank_group_4_6) OVER (change) AS ppc_value__sum__rank_group_4_6__change,

    ppc_value__sum__rank_group_7_10 AS ppc_value__sum__rank_group_7_10__value,
    ppc_value__sum__rank_group_7_10 - FIRST_VALUE(ppc_value__sum__rank_group_7_10) OVER (change) AS ppc_value__sum__rank_group_7_10__change,

    ppc_value__sum__rank_group_11_15 AS ppc_value__sum__rank_group_11_15__value,
    ppc_value__sum__rank_group_11_15 - FIRST_VALUE(ppc_value__sum__rank_group_11_15) OVER (change) AS ppc_value__sum__rank_group_11_15__change,

    ppc_value__sum__rank_group_16_20 AS ppc_value__sum__rank_group_16_20__value,
    ppc_value__sum__rank_group_16_20 - FIRST_VALUE(ppc_value__sum__rank_group_16_20) OVER (change) AS ppc_value__sum__rank_group_16_20__change,

    ppc_value__sum__rank_group_21_50 AS ppc_value__sum__rank_group_21_50__value,
    ppc_value__sum__rank_group_21_50 - FIRST_VALUE(ppc_value__sum__rank_group_21_50) OVER (change) AS ppc_value__sum__rank_group_21_50__change,

    ppc_value__sum__rank_group_51 AS ppc_value__sum__rank_group_51__value,
    ppc_value__sum__rank_group_51 - FIRST_VALUE(ppc_value__sum__rank_group_51) OVER (change) AS ppc_value__sum__rank_group_51__change,


    unique_keywords__count__total AS unique_keywords__count__total__value,
    unique_keywords__count__total - FIRST_VALUE(unique_keywords__count__total) OVER (change) AS unique_keywords__count__total__change,

    unique_keywords__count__rank_group_1 AS unique_keywords__count__rank_group_1__value,
    unique_keywords__count__rank_group_1 - FIRST_VALUE(unique_keywords__count__rank_group_1) OVER (change) AS unique_keywords__count__rank_group_1__change,

    unique_keywords__count__rank_group_2 AS unique_keywords__count__rank_group_2__value,
    unique_keywords__count__rank_group_2 - FIRST_VALUE(unique_keywords__count__rank_group_2) OVER (change) AS unique_keywords__count__rank_group_2__change,

    unique_keywords__count__rank_group_3 AS unique_keywords__count__rank_group_3__value,
    unique_keywords__count__rank_group_3 - FIRST_VALUE(unique_keywords__count__rank_group_3) OVER (change) AS unique_keywords__count__rank_group_3__change,

    unique_keywords__count__rank_group_4_6 AS unique_keywords__count__rank_group_4_6__value,
    unique_keywords__count__rank_group_4_6 - FIRST_VALUE(unique_keywords__count__rank_group_4_6) OVER (change) AS unique_keywords__count__rank_group_4_6__change,

    unique_keywords__count__rank_group_7_10 AS unique_keywords__count__rank_group_7_10__value,
    unique_keywords__count__rank_group_7_10 - FIRST_VALUE(unique_keywords__count__rank_group_7_10) OVER (change) AS unique_keywords__count__rank_group_7_10__change,

    unique_keywords__count__rank_group_11_15 AS unique_keywords__count__rank_group_11_15__value,
    unique_keywords__count__rank_group_11_15 - FIRST_VALUE(unique_keywords__count__rank_group_11_15) OVER (change) AS unique_keywords__count__rank_group_11_15__change,

    unique_keywords__count__rank_group_16_20 AS unique_keywords__count__rank_group_16_20__value,
    unique_keywords__count__rank_group_16_20 - FIRST_VALUE(unique_keywords__count__rank_group_16_20) OVER (change) AS unique_keywords__count__rank_group_16_20__change,

    unique_keywords__count__rank_group_21_50 AS unique_keywords__count__rank_group_21_50__value,
    unique_keywords__count__rank_group_21_50 - FIRST_VALUE(unique_keywords__count__rank_group_21_50) OVER (change) AS unique_keywords__count__rank_group_21_50__change,

    unique_keywords__count__rank_group_51 AS unique_keywords__count__rank_group_51__value,
    unique_keywords__count__rank_group_51 - FIRST_VALUE(unique_keywords__count__rank_group_51) OVER (change) AS unique_keywords__count__rank_group_51__change,


    unique_urls__count__total AS unique_urls__count__total__value,
    unique_urls__count__total - FIRST_VALUE(unique_urls__count__total) OVER (change) AS unique_urls__count__total__change,

    unique_urls__count__rank_group_1 AS unique_urls__count__rank_group_1__value,
    unique_urls__count__rank_group_1 - FIRST_VALUE(unique_urls__count__rank_group_1) OVER (change) AS unique_urls__count__rank_group_1__change,

    unique_urls__count__rank_group_2 AS unique_urls__count__rank_group_2__value,
    unique_urls__count__rank_group_2 - FIRST_VALUE(unique_urls__count__rank_group_2) OVER (change) AS unique_urls__count__rank_group_2__change,

    unique_urls__count__rank_group_3 AS unique_urls__count__rank_group_3__value,
    unique_urls__count__rank_group_3 - FIRST_VALUE(unique_urls__count__rank_group_3) OVER (change) AS unique_urls__count__rank_group_3__change,

    unique_urls__count__rank_group_4_6 AS unique_urls__count__rank_group_4_6__value,
    unique_urls__count__rank_group_4_6 - FIRST_VALUE(unique_urls__count__rank_group_4_6) OVER (change) AS unique_urls__count__rank_group_4_6__change,

    unique_urls__count__rank_group_7_10 AS unique_urls__count__rank_group_7_10__value,
    unique_urls__count__rank_group_7_10 - FIRST_VALUE(unique_urls__count__rank_group_7_10) OVER (change) AS unique_urls__count__rank_group_7_10__change,

    unique_urls__count__rank_group_11_15 AS unique_urls__count__rank_group_11_15__value,
    unique_urls__count__rank_group_11_15 - FIRST_VALUE(unique_urls__count__rank_group_11_15) OVER (change) AS unique_urls__count__rank_group_11_15__change,

    unique_urls__count__rank_group_16_20 AS unique_urls__count__rank_group_16_20__value,
    unique_urls__count__rank_group_16_20 - FIRST_VALUE(unique_urls__count__rank_group_16_20) OVER (change) AS unique_urls__count__rank_group_16_20__change,

    unique_urls__count__rank_group_21_50 AS unique_urls__count__rank_group_21_50__value,
    unique_urls__count__rank_group_21_50 - FIRST_VALUE(unique_urls__count__rank_group_21_50) OVER (change) AS unique_urls__count__rank_group_21_50__change,

    unique_urls__count__rank_group_51 AS unique_urls__count__rank_group_51__value,
    unique_urls__count__rank_group_51 - FIRST_VALUE(unique_urls__count__rank_group_51) OVER (change) AS unique_urls__count__rank_group_51__change,


    results__count__total AS results__count__total__value,
    results__count__total - FIRST_VALUE(results__count__total) OVER (change) AS results__count__total__change,

    results__count__rank_group_1 AS results__count__rank_group_1__value,
    results__count__rank_group_1 - FIRST_VALUE(results__count__rank_group_1) OVER (change) AS results__count__rank_group_1__change,

    results__count__rank_group_2 AS results__count__rank_group_2__value,
    results__count__rank_group_2 - FIRST_VALUE(results__count__rank_group_2) OVER (change) AS results__count__rank_group_2__change,

    results__count__rank_group_3 AS results__count__rank_group_3__value,
    results__count__rank_group_3 - FIRST_VALUE(results__count__rank_group_3) OVER (change) AS results__count__rank_group_3__change,

    results__count__rank_group_4_6 AS results__count__rank_group_4_6__value,
    results__count__rank_group_4_6 - FIRST_VALUE(results__count__rank_group_4_6) OVER (change) AS results__count__rank_group_4_6__change,

    results__count__rank_group_7_10 AS results__count__rank_group_7_10__value,
    results__count__rank_group_7_10 - FIRST_VALUE(results__count__rank_group_7_10) OVER (change) AS results__count__rank_group_7_10__change,

    results__count__rank_group_11_15 AS results__count__rank_group_11_15__value,
    results__count__rank_group_11_15 - FIRST_VALUE(results__count__rank_group_11_15) OVER (change) AS results__count__rank_group_11_15__change,

    results__count__rank_group_16_20 AS results__count__rank_group_16_20__value,
    results__count__rank_group_16_20 - FIRST_VALUE(results__count__rank_group_16_20) OVER (change) AS results__count__rank_group_16_20__change,

    results__count__rank_group_21_50 AS results__count__rank_group_21_50__value,
    results__count__rank_group_21_50 - FIRST_VALUE(results__count__rank_group_21_50) OVER (change) AS results__count__rank_group_21_50__change,

    results__count__rank_group_51 AS results__count__rank_group_51__value,
    results__count__rank_group_51 - FIRST_VALUE(results__count__rank_group_51) OVER (change) AS results__count__rank_group_51__change,

  FROM per_serp_metrics_aggregation
  WINDOW change AS (PARTITION BY %s ORDER BY requested)
)

SELECT * FROM group_by_totals
""", 
  dataset_id, group_by, rollup_by,
  dataset_id, cache_table,
  segment_sql,
  brand_sql,
  keyword_group_sql,
  group_by,
  group_by
);

SET stmt_hash = FARM_FINGERPRINT(stmt);



EXECUTE IMMEDIATE FORMAT("""CREATE TABLE IF NOT EXISTS `nozzledata.%s.rollup_by_brand_%d`
CLUSTER BY requested, %s
OPTIONS(
  expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC"
)
AS
%s
""",
  dataset_id, stmt_hash,
  group_by,
  stmt
) USING 
  requested_start AS requested_start,
  requested_end AS requested_end,
  brand_names AS brand_names,
  segment_names AS segment_names,
  keyword_groups AS keyword_groups;

EXECUTE IMMEDIATE FORMAT("""SELECT
  requested,
  
  brand_id,
  b.name AS brand_name,
  b.type AS brand_type,
  b.description AS brand_description,
  
  segment_id,
  s.name AS segment_name,
  s.description AS segment_description,
  
  keyword_group,
  
  r.* EXCEPT(requested, %s),
  
FROM `nozzledata.%s.rollup_by_brand_%d` r
JOIN nozzledata.%s.latest_brands b USING (brand_id)
JOIN nozzledata.%s.latest_segments s USING (segment_id)
JOIN nozzledata.nozzle_customer.latest_keyword_groups k USING (keyword_group_hash)
""",
  group_by,
  dataset_id, stmt_hash,
  dataset_id,
  dataset_id
);

END

Comments

de...@derekperkins.com <de...@derekperkins.com> #2Jun 13, 2021 09:38PM

bw...@google.com <bw...@google.com> #3Jun 29, 2021 02:12AM

de...@derekperkins.com <de...@derekperkins.com> #4Jun 29, 2021 05:35AM

I'm very excited for Table Functions, that enables the parameterized view feature mentioned in my second comment, and we will definitely use it right now. It still doesn't provide all that I am looking for, since it doesn't allow for scripting or side-effects, which would really unleash the power of BigQuery as a data platform api.

bw...@google.com <bw...@google.com> #5Aug 10, 2021 03:21PM

Assigned to ja...@google.com.

Understood. We will consider this for a future roadmap item.

bw...@google.com <bw...@google.com> #6Oct 6, 2021 06:01AM

Table Functions are now Generally Available:

https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions.

de...@derekperkins.com <de...@derekperkins.com> #7May 3, 2022 03:48AM

Following up to see if there has been any movement

de...@derekperkins.com <de...@derekperkins.com> #8Mar 18, 2024 05:55AM

I've attempted to make this work with Table Functions, but they're pretty much unusable without having optional parameters

https://issuetracker.google.com/issues/330055203