Hidden Gems of BigQuery — March News

In recent months I was not particularly active with writing — I’m working on a new Gemini project AND I had a newborn in my family :) With such news there is not much time for writing.

So, while I’ve accumulated a lot of material for future parts of “Hidden Gems of BigQuery,” — they still require time to be analyzed, processed, and formatted.

However, meanwhile, I wanted to share a quick update considering User Defined Functions in BigQuery. Since it is pretty small (text wise), it does not deserve to be a new “Part” of the series, so I will try new format — quick news post.

User-defined aggregate functions

In Part 3, we talked about the different forms of routines in BQ. A couple of days ago, a new member was added to this family — User-defined aggregate functions (in Preview now).

Syntaxes are pretty straightforward (especially if you already worked with regular UDFs in BigQuery), with only a couple of differences.

1. You need to add AGGREGATE to the CREATE … FUNCTION statement to show that this is an aggregate function.

CREATE AGGREGATE FUNCTION myproject.mydataset.ScaledAverage(
  dividend FLOAT64,
  divisor FLOAT64)
RETURNS FLOAT64
AS (
  AVG(dividend / divisor)
);

2. Of course, you should expect that your parameters by default are not scalar values but rather “columns.” As a result, to return a scalar value — you need to aggregate the data.

3. But you can also have scalar parameters (by specifying NOT AGGREGATE keyword. Only literals can be passed as non-aggregate arguments for an SQL UDAF.

CREATE AGGREGATE FUNCTION myproject.mydataset.ScaledSum(
  dividend FLOAT64,
  divisor FLOAT64 NOT AGGREGATE)
RETURNS FLOAT64
AS (
  SUM(dividend) / divisor
);

Other than that, you have to follow the same rules as for regular UDFs:

• You can create both permanent and temporary UDFs
• You can DROP aggregate UDF (if you don’t need it anymore)
• Aggregate UDFs would be listed in the Routines list
• UDAFs can’t mutate data, talk to external systems, or send logs to Google Cloud Observability or similar applications
• And other limitations

Now, you can go and give it a try!

Please let me know if you find this format useful also (or if you don’t need another channel that repeats the news…).

If you want to read more Hidden Gems of BigQuery:

• Auto, Transactions and Indexes — Hidden gems of BigQuery
• Change History and Defaults — Hidden gems of BigQuery — Part 2
• Data Lineage and Routines — Hidden gems of BigQuery — Part 3
• Views, Caching, Scheduled Queries, Materialized Views — Hidden Gems of BigQuery — Part 4– Five types of “Views”
• ML in BigQuery, including new Generative AI — BigQuery Breaking News — Generative AI
• Time-travel window, fail-safe, snapshots and clones — Hidden Gems of BigQuery — P6 — Time-traveling and clones
• TRY…CATCH, RAISE, ERROR, SAFE_ and SAFE. — Hidden Gems of BigQuery — P7 — Safety first