CODEWITHSUNDEEP
google-bigquery
thomaspark
thomaspark

Reputation: 488

How do I create a BigQuery view that uses a user-defined function?

I'd like to create a BigQuery view that uses a query which invokes a user-defined function. How do I tell BigQuery where to find the code files for the UDF?

Upvotes: 4

Views: 4291

Answers (1)

thomaspark
thomaspark

Reputation: 488

Views can reference UDF resources stored in Google Cloud Storage, inline code blobs, or local files (contents will be loaded into inline code blobs).

To create a view with a UDF using the BigQuery UI, just fill out the UDF resources as you would when running the query normally, and save as a view. (In other words, no special actions are required).

To specify these during view creation from the command-line client, use the --view_udf_resource flag:

bq mk --view="SELECT foo FROM myUdf(table)" \
  --view_udf_resource="gs://my-bucket/my-code.js"

In the above example, gs://my-bucket/my-code.js would contain the definition for myUdf(). You can provide multiple --view_udf_resources flags if you need to reference multiple code files in your view query.

You may specify gs:// URIs or local files. If you specify a local file, then the code will be read once and packed into an inline code resource.

Via the API, this is a repeated field named userDefinedFunctionResources. It is a sibling of the query field that contains the view SQL.

Upvotes: 9

Related Questions