Create bqutil UDFs in all other non-US datasets

[davidvanrooij]

I ran into some issues when trying to use CTE's in combination with bqutil.

This exectues as expected:

SELECT `bqutil.fn.median`([1,1,1,2,3,4,5,100,1000]) as median

However, after adding a CTE:

WITH covid AS (
  SELECT date, daily_confirmed_cases 
  FROM `bigquery-public-data.covid19_ecdc_eu.covid_19_geographic_distribution_worldwide` 
)
SELECT `bqutil.fn.median`([1,1,1,2,3,4,5,100,1000]) as median

BQ throws the error: "Function not found: bqutil.fn.median".

I there a way to explicitly import the BQ utils or any other suggestions to address this issue?

[danieldeleo]

It's not clear from the error message, but this is caused by the table and the UDF being in different BigQuery dataset locations, and the query must be processed in only one location. By default, BigQuery will attempt to process your query in EU location since bigquery-public-data.covid19_ecdc_eu.covid_19_geographic_distribution_worldwide is stored in EU. The bqutil.fn dataset is only available in US location for now, so the query errors because it cannot find the UDF in the EU location. For now, you can create the UDF as either a persistent UDF in your own dataset, or as a temporary UDF in a script along with your query.

I will assign this issue to myself to have all the bqutil UDFs created in the other non-US locations

[davidvanrooij]

That solves my issue, for the time being. Thanks for the prompt response.

For what it is worth: I originally stumbled on this error on a private dataset that resides in us-west2 and even though it is in the US still had the same issue.

[pokutuna]

all the bqutil UDFs created in the other non-US locations

This is very helpful. I'm a non-US user, so I copy the UDFs to my region and use it. It would be easy to share queries using UDFs if bqutil project provide them.

[Paso]

With the recent change to template the sql-files (into sqlx) this issue has become actualized. Because of the templating the files can no longer easily be deployed to private projects just using bq. It would be very useful with either instructions on how to build the sqlx files so they can be used in private projects, deploying them to other regions, or just keeping them native .sql in git.

[danieldeleo]

Thanks for the feeback! I'm addressing these points in a followup PR

[deplorableword]

@danieldeleo Apologies for commenting on this closed issue, do you anticipate that bqutil be publicly available in non-US locations or if we are outside the US should we always deploy our own? Thanks

[afleisc]

Hello, UDFs are now available in all BQ supported regions! Please see https://github.com/GoogleCloudPlatform/bigquery-utils/pull/405 and docs to use regional UDFs