BigQuery Table functions

BigQuery table valued functions behave similarly to views, but a table function can take input parameters

Create table function

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

Table function usage

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

You can also use the table functions like a regular table in the joins

SELECT *
  FROM `bigquery-project.samples.shakespeare` AS s
  JOIN mydataset.names_by_year(1950) AS n
  ON n.name = s.word

Deleting a table function

DROP TABLE FUNCTION mydataset.names_by_year

mCollins

posted on 30 Sep 21