30 Sep 21 ·  mCollins in Bigquery ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds