CODEWITHSUNDEEP
arraysgoogle-bigquery
NaN
NaN

Reputation: 3591

BigQuery Types: How to define a array of any type?

Bigquery SQL-UDFs are quite convenient, but is there a possibility to define array arguments without specifying the type of its elements? At least sometimes it would be nice to define operations on all arrays irrespective of the specific type. For example, one could create a function to get the most frequent elements of an array like this:

CREATE TEMPORARY FUNCTION anyHEAVY(arr Array<ANY TYPE>) AS ((
  SELECT APPROX_TOP_COUNT(a, 1)[OFFSET(0)].value
  FROM UNNEST(arr) as a
));

However, it seems like BQ expects here a specific type and the generic "ANY TYPE" placeholder does not work anymore. Up to now, I am just using the type "any type" without forcing the argument to be an array. This works, but is IMHO not really clean and would require an additional check. I can imagine, that any type would cause some troubles, especially, in case of nested arrays or structs. However, would be great if one could define functions of arrays containing only "elementary" types (excluding arrays and structs).

Upvotes: 3

Views: 1518

Answers (1)

Tlaquetzal
Tlaquetzal

Reputation: 2850

Currently there's no support for ARRAYS of ANY TYPE in SQL-UDF. The workaround is, as you mention, to declare the function as expecting ANY TYPE:

CREATE TEMPORARY FUNCTION anyHEAVY(arr ANY TYPE)

Upvotes: 4

Related Questions