UDF that returns a table

user_133932 0
Viewed 2145

In BigQuery, how does one write a UDF that returns a table? What I would like is a CTE that is able to accept parameters. As far as I can tell, UDFs only returns scalars, am I correct?

google-bigquery user-defined-functions
2 Answers

UDF can return ARRAYS of different types including ARRAY of STRUCTs

But obviously it is not the same as returning table, which is not really supported by BigQuery UDF [yet]

P.S. If you have specific issue that you want to resolve - ask specific question and someone will help

Below are two relatively naive and useless (from practical standpoint) examples - but I hope they show concept of using ARRAYS to mimic at some extend CTE

Example #1 

#standardSQL
CREATE TEMPORARY FUNCTION pseudoCTE(x INT64, y INT64) AS (
  GENERATE_ARRAY(x, y)
);
SELECT * FROM UNNEST(pseudoCTE(1,5)) z

with result 

Row z    
1   1    
2   2    
3   3    
4   4    
5   5

Example #2 

#standardSQL
CREATE TEMPORARY FUNCTION pseudoCTE(x INT64, y INT64) AS (
  ARRAY(SELECT AS STRUCT z AS id, RAND() AS value
  FROM UNNEST(GENERATE_ARRAY(x, y)) z)
);
SELECT * FROM UNNEST(pseudoCTE(1,5))   

Row id  value    
1   1   0.9319445195173228   
2   2   0.36404932965409453  
3   3   0.4615807541752828   
4   4   0.5504890432993448   
5   5   0.29635275888268836
user_52219440

In BigQuery, you now have table functions:

A table function, also called a table-valued function (TVF), is a user-defined function that returns a table.

An example (found in the Google documentation):

CREATE OR REPLACE TABLE FUNCTION mydataset.names_by_year(y INT64)
AS
  SELECT year, name, SUM(number) AS total
  FROM `bigquery-public-data.usa_names.usa_1910_current`
  WHERE year = y
  GROUP BY year, name

To execute it:

SELECT * FROM mydataset.names_by_year(1950)
ORDER BY total DESC
LIMIT 5
user_39330520
Related