Support optional parameters in UDFs and Table Functions [330055203]

Assigned

Bug

Status Update

No update yet.

Description

de...@nozzle.io

created issue #1

Mar 18, 2024 05:06AM

I'm trying to simplify BigQuery integration for our customers by hiding complex query logic behind a table function. The problem is that without optional parameters, I can't make any updates without breaking every customer using the function, and they can't use the function without passing in every parameter, even if they're all null.

Here's an example function, where I typically expect 0-3 of the parameters sent in at one time

CREATE OR REPLACE TABLE FUNCTION dataset.table.get_keywords (
  -- search
  search STRING,
  
  -- keywords
  include_keyword_ids ARRAY<INT64>,
  exclude_keyword_ids ARRAY<INT64>,
  
  -- phrases
  include_phrases ARRAY<STRING>,
  exclude_phrases ARRAY<STRING>,
  
  phrase_contains ARRAY<STRING>,
  phrase_does_not_contain ARRAY<STRING>,
  
  phrase_regex_contains STRING,
  phrase_regex_does_not_contain STRING,
  
  -- locales
  include_locale_ids ARRAY<INT64>,
  exclude_locale_ids ARRAY<INT64>,
  
  -- languages
  include_language_codes ARRAY<STRING>,
  exclude_language_codes ARRAY<STRING>,
  
  -- devices
  include_devices ARRAY<STRING>,
  exclude_devices ARRAY<STRING>,
  
  -- locations
  include_location_ids ARRAY<INT64>,
  exclude_location_ids ARRAY<INT64>,
  
  -- countries
  include_country_codes ARRAY<STRING>,
  exclude_country_codes ARRAY<STRING>,
  
  -- location_types
  include_location_types ARRAY<STRING>,
  exclude_location_types ARRAY<STRING>,
  
  -- keyword_groups
  include_keyword_groups ARRAY<STRING>,
  exclude_keyword_groups ARRAY<STRING>,
  include_built_in_keyword_groups BOOL

) AS (
WITH

filtered_keyword_ids AS (
  SELECT keyword_id
  FROM dataset.table.latest_keywords_by_keyword_id

  -- keywords
  WHERE (include_keyword_ids IS NULL OR keyword_id IN UNNEST(include_keyword_ids))
  AND (exclude_keyword_ids IS NULL OR keyword_id NOT IN UNNEST(exclude_keyword_ids))

  -- phrases
  AND (include_phrases IS NULL OR phrase IN UNNEST(include_phrases))
  AND (exclude_phrases IS NULL OR phrase NOT IN UNNEST(exclude_phrases))

  -- locales
  AND (include_locale_ids IS NULL OR locale_id IN UNNEST(include_locale_ids))
  AND (exclude_locale_ids IS NULL OR locale_id NOT IN UNNEST(exclude_locale_ids))

  -- languages
  AND (include_language_codes IS NULL OR language_code IN UNNEST(include_language_codes))
  AND (exclude_language_codes IS NULL OR language_code NOT IN UNNEST(exclude_language_codes))

  -- devices
  AND (include_devices IS NULL OR device IN UNNEST(include_devices))
  AND (exclude_devices IS NULL OR device NOT IN UNNEST(exclude_devices))

  -- locations
  AND (include_location_ids IS NULL OR location_id IN UNNEST(include_location_ids))
  AND (exclude_location_ids IS NULL OR location_id NOT IN UNNEST(exclude_location_ids))

  -- countries
  AND (include_country_codes IS NULL OR country_code IN UNNEST(include_country_codes))
  AND (exclude_country_codes IS NULL OR country_code NOT IN UNNEST(exclude_country_codes))

  -- location_types
  AND (include_location_types IS NULL OR location_type IN UNNEST(include_location_types))
  AND (exclude_location_types IS NULL OR location_type NOT IN UNNEST(exclude_location_types))

  -- keyword_groups
  AND (include_keyword_groups IS NULL OR EXISTS(SELECT 1 FROM UNNEST(keyword_groups) kg WHERE kg IN UNNEST(include_keyword_groups)))
  AND (exclude_keyword_groups IS NULL OR NOT EXISTS(SELECT 1 FROM UNNEST(keyword_groups) kg WHERE kg IN UNNEST(exclude_keyword_groups)))
)

SELECT
  keyword_id,
  phrase_id,
  phrase,
  locale_id,
  device_code,
  device,
  engine_code,
  engine,
  language_code,
  language,
  location_id,
  location_type,
  location,
  country_code,
  country,
  ad_words_criteria_id,

  IF(include_built_in_keyword_groups,
    keyword_groups,

    -- this filters out built-in keyword groups like "- All Keywords -"
    (SELECT ARRAY_AGG(kg) FROM UNNEST(keyword_groups) kg WHERE NOT STARTS_WITH(kg, '- '))
  ) AS keyword_groups,

FROM dataset.table.latest_keywords_by_keyword_id
JOIN filtered_keyword_ids USING (keyword_id)
)

The docs say "A table function can have up to 256 arguments." Are people really using 256 functions and forcing every single parameter to be sent in every time?

None of these are required, so I would expect NULL values where nothing is explicitly specified.

SELECT * FROM nozzledata.nozzle_actionlocksmith.get_keywords();

SELECT * FROM nozzledata.nozzle_actionlocksmith.get_keywords(include_languages => ['en']);
''')

JSON options workaround

I attempted to support optional parameters using JSON, which technically worked, but caused intense query plans that quickly triggered query planner errors about too many resources.

CREATE OR REPLACE TABLE FUNCTION nozzledata.nozzle_actionlocksmith.get_keywords (opts JSON)
OPTIONS (description = 'This takes JSON options instead of SQL parameters, so that we can introduce new parameters and filter options without breaking the function signature. While many of the filter options could be done with the output of the function, the value of using the JSON option is that filters can be applied earlier in the execution plan, making queries faster and cheaper, especially when it comes to pruning partitions on the requested column, and taking advantage of clustered columns. See comment at the top of the query to see all available options')
AS (
WITH
/*
Here is an example JSON file showing the available options:

{
  "include_keyword_ids": [],
  "exclude_keyword_ids": [],

  "include_keyword_groups": [],
  "exclude_keyword_groups": [],
  "include_built_in_keyword_groups": false,
}
*/
o AS (
  SELECT
    -- search
    IF(TRIM(JSON_VALUE(opts, '$.search')) = '', NULL, JSON_VALUE(opts, '$.search')) AS search,

    -- keywords
    (SELECT ARRAY(SELECT INT64(v) FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.include_keyword_ids')) AS v WHERE v IS NOT NULL)) AS include_keyword_ids,
    (SELECT ARRAY(SELECT INT64(v) FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.exclude_keyword_ids')) AS v WHERE v IS NOT NULL)) AS exclude_keyword_ids,

    -- phrases
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.include_phrases')) AS v WHERE v IS NOT NULL)) AS include_phrases,
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.exclude_phrases')) AS v WHERE v IS NOT NULL)) AS exclude_phrases,

    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.phrase_contains')) AS v WHERE v IS NOT NULL)) AS phrase_contains,
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.phrase_does_not_contain')) AS v WHERE v IS NOT NULL)) AS phrase_does_not_contain,

    IF(TRIM(JSON_VALUE(opts, '$.phrase_regex_contains')) = '', NULL, JSON_VALUE(opts, '$.phrase_regex_contains')) AS phrase_regex_contains,
    IF(TRIM(JSON_VALUE(opts, '$.phrase_regex_does_not_contain')) = '', NULL, JSON_VALUE(opts, '$.phrase_regex_does_not_contain')) AS phrase_regex_does_not_contain,

    -- locales
    (SELECT ARRAY(SELECT INT64(v) FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.include_locale_ids')) AS v WHERE v IS NOT NULL)) AS include_locale_ids,
    (SELECT ARRAY(SELECT INT64(v) FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.exclude_locale_ids')) AS v WHERE v IS NOT NULL)) AS exclude_locale_ids,

    -- languages
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.include_language_codes')) AS v WHERE v IS NOT NULL)) AS include_language_codes,
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.exclude_language_codes')) AS v WHERE v IS NOT NULL)) AS exclude_language_codes,

    -- devices
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.include_devices')) AS v WHERE v IS NOT NULL)) AS include_devices,
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.exclude_devices')) AS v WHERE v IS NOT NULL)) AS exclude_devices,

    -- locations
    (SELECT ARRAY(SELECT INT64(v) FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.include_location_ids')) AS v WHERE v IS NOT NULL)) AS include_location_ids,
    (SELECT ARRAY(SELECT INT64(v) FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.exclude_location_ids')) AS v WHERE v IS NOT NULL)) AS exclude_location_ids,

    -- countries
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.include_country_codes')) AS v WHERE v IS NOT NULL)) AS include_country_codes,
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.exclude_country_codes')) AS v WHERE v IS NOT NULL)) AS exclude_country_codes,

    -- location_types
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.include_location_types')) AS v WHERE v IS NOT NULL)) AS include_location_types,
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.exclude_location_types')) AS v WHERE v IS NOT NULL)) AS exclude_location_types,

    -- keyword_groups
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.include_keyword_groups')) AS v WHERE v IS NOT NULL)) AS include_keyword_groups,
    (SELECT ARRAY(SELECT JSON_VALUE(v, '$') FROM UNNEST(JSON_QUERY_ARRAY(opts, '$.exclude_keyword_groups')) AS v WHERE v IS NOT NULL)) AS exclude_keyword_groups,
    IFNULL(BOOL(JSON_QUERY(opts, '$.include_built_in_keyword_groups')), FALSE) AS include_built_in_keyword_groups,
),

-- filter keywords early to reduce query execution time
filtered_keyword_ids AS (
  SELECT keyword_id
  FROM dataset.table.latest_keywords_by_keyword_id

  -- keywords
  WHERE (ARRAY_LENGTH((SELECT include_keyword_ids FROM o)) = 0 OR keyword_id IN UNNEST((SELECT include_keyword_ids FROM o)))
  AND (ARRAY_LENGTH((SELECT exclude_keyword_ids FROM o)) = 0 OR keyword_id NOT IN UNNEST((SELECT exclude_keyword_ids FROM o)))

  -- phrases
  AND (ARRAY_LENGTH((SELECT include_phrases FROM o)) = 0 OR phrase IN UNNEST((SELECT include_phrases FROM o)))
  AND (ARRAY_LENGTH((SELECT exclude_phrases FROM o)) = 0 OR phrase NOT IN UNNEST((SELECT exclude_phrases FROM o)))

  -- locales
  AND (ARRAY_LENGTH((SELECT include_locale_ids FROM o)) = 0 OR locale_id IN UNNEST((SELECT include_locale_ids FROM o)))
  AND (ARRAY_LENGTH((SELECT exclude_locale_ids FROM o)) = 0 OR locale_id NOT IN UNNEST((SELECT exclude_locale_ids FROM o)))

  -- languages
  AND (ARRAY_LENGTH((SELECT include_language_codes FROM o)) = 0 OR language_code IN UNNEST((SELECT include_language_codes FROM o)))
  AND (ARRAY_LENGTH((SELECT exclude_language_codes FROM o)) = 0 OR language_code NOT IN UNNEST((SELECT exclude_language_codes FROM o)))

  -- devices
  AND (ARRAY_LENGTH((SELECT include_devices FROM o)) = 0 OR device IN UNNEST((SELECT include_devices FROM o)))
  AND (ARRAY_LENGTH((SELECT exclude_devices FROM o)) = 0 OR device NOT IN UNNEST((SELECT exclude_devices FROM o)))

  -- locations
  AND (ARRAY_LENGTH((SELECT include_location_ids FROM o)) = 0 OR location_id IN UNNEST((SELECT include_location_ids FROM o)))
  AND (ARRAY_LENGTH((SELECT exclude_location_ids FROM o)) = 0 OR location_id NOT IN UNNEST((SELECT exclude_location_ids FROM o)))

  -- countries
  AND (ARRAY_LENGTH((SELECT include_country_codes FROM o)) = 0 OR country_code IN UNNEST((SELECT include_country_codes FROM o)))
  AND (ARRAY_LENGTH((SELECT exclude_country_codes FROM o)) = 0 OR country_code NOT IN UNNEST((SELECT exclude_country_codes FROM o)))

  -- location_types
  AND (ARRAY_LENGTH((SELECT include_location_types FROM o)) = 0 OR location_type IN UNNEST((SELECT include_location_types FROM o)))
  AND (ARRAY_LENGTH((SELECT exclude_location_types FROM o)) = 0 OR location_type NOT IN UNNEST((SELECT exclude_location_types FROM o)))

  -- keyword_groups
  AND (ARRAY_LENGTH((SELECT include_keyword_groups FROM o)) = 0 OR EXISTS(SELECT 1 FROM UNNEST(keyword_groups) kg WHERE kg IN UNNEST((SELECT include_keyword_groups FROM o))))
  AND (ARRAY_LENGTH((SELECT exclude_keyword_groups FROM o)) = 0 OR NOT EXISTS(SELECT 1 FROM UNNEST(keyword_groups) kg WHERE kg IN UNNEST((SELECT exclude_keyword_groups FROM o))))
)

SELECT
  keyword_id,
  phrase_id,
  phrase,
  locale_id,
  device_code,
  device,
  engine_code,
  engine,
  language_code,
  language,
  location_id,
  location_type,
  location,
  country_code,
  country,
  ad_words_criteria_id,

  IF((SELECT include_built_in_keyword_groups FROM o),
    keyword_groups,

    -- this filters out built-in keyword groups like "- All Keywords -"
    (SELECT ARRAY_AGG(kg) FROM UNNEST(keyword_groups) kg WHERE NOT STARTS_WITH(kg, '- '))
  ) AS keyword_groups,

FROM dataset.table.latest_keywords_by_keyword_id
JOIN filtered_keyword_ids USING (keyword_id)
)

Comments

de...@nozzle.io <de...@nozzle.io> #2Mar 18, 2024 05:54AM

I've tried getting around this in the past with stored procedures, but that doesn't return a table, which makes it unusable for this.

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

de...@nozzle.io <de...@nozzle.io> #3Mar 18, 2024 06:33PM

The docs show that GAP_FILL is a built in table function, and it has optional parameters, so the functionality is there, we just need to be able to annotate our functions correctly.

https://cloud.google.com/bigquery/docs/reference/standard-sql/table-functions-built-in#gap_fill_table_functions

https://cloud.google.com/bigquery/docs/reference/standard-sql/time-series-functions#gap_fill

de...@nozzle.io <de...@nozzle.io> #4Mar 18, 2024 07:41PM

I found some links in ZetaSQL referring to TVFs and optional params. It sort of looks like REQUIRED is hardcoded?

Here's a related issue about optional parameters for stored procedures. https://issuetracker.google.com/issues/189354200

va...@google.com <va...@google.com> Mar 19, 2024 06:14AM

Assigned to ds...@google.com.

ds...@google.com <ds...@google.com> #5Mar 20, 2024 09:05AM

So , It's not a bug , you want a feature to Support optional parameters in UDFs and Table Functions in Bigquery ?

de...@derekperkins.com <de...@derekperkins.com> #6Mar 20, 2024 12:24PM

Correct, this is a feature request, sorry if I mislabeled it, I didn't notice where to set that when creating the issue.

ds...@google.com <ds...@google.com> #7Mar 26, 2024 07:03AM

Reassigned to gc...@google.com.

Hello,

Thank you for reaching out to us with your request.

We have duly noted your feedback and will thoroughly validate it. While we cannot provide an estimated time of implementation or guarantee the fulfillment of the Feature request

Message last modified on Mar 26, 2024 07:03AM