Table functions
A table function, also called a table-valued function (TVF), is a user-defined function that returns a table. You can use a table function anywhere that you can use a table. Table functions behave similarly to views, but a table function can take parameters.
Create table functions
To create a table function, use the CREATE TABLE FUNCTION
statement. A table function contains a query that produces a table. The function
returns the query result. The following table function takes an INT64
parameter and uses this value inside a WHERE
clause in a query over a public dataset
called bigquery-public-data.usa_names.usa_1910_current
:
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 filter in other ways, you can pass multiple parameters to a table function. The following table function filters the data by year and name prefix:
CREATE OR REPLACE TABLE FUNCTION mydataset . names_by_year_and_prefix ( y INT64 , z STRING ) AS ( SELECT year , name , SUM ( number ) AS total FROM `bigquery-public-data.usa_names.usa_1910_current` WHERE year = y AND STARTS_WITH ( name , z ) GROUP BY year , name );
Parameter names
If a table function parameter matches the name of a table column, it can create an ambiguous reference. In that case, BigQuery interprets the name as a reference to the table column, not the parameter. The recommended practice is to use parameter names that are distinct from the names of any referenced table columns.
Use table functions
You can call a table function in any context where a table is valid. The following
example calls the mydataset.names_by_year
function in the FROM
clause of
a SELECT
statement:
SELECT
*
FROM
mydataset
.
names_by_year
(
1950
)
ORDER
BY
total
DESC
LIMIT
5
The results look like the following:
+------+--------+-------+
| year | name | total |
+------+--------+-------+
| 1950 | James | 86447 |
| 1950 | Robert | 83717 |
| 1950 | Linda | 80498 |
| 1950 | John | 79561 |
| 1950 | Mary | 65546 |
+------+--------+-------+
You can join the output from a table function with another table:
SELECT
*
FROM
`bigquery-public-data.samples.shakespeare`
AS
s
JOIN
mydataset
.
names_by_year
(
1950
)
AS
n
ON
n
.
name
=
s
.
word
You can also use a table function in a subquery :
SELECT
ARRAY
(
SELECT
name
FROM
mydataset
.
names_by_year
(
1950
)
ORDER
BY
total
DESC
LIMIT
5
)
List table functions
Table functions are a type of routine. To list all of the routines in a dataset, see List routines .
Delete table functions
To delete a table function, use the DROP TABLE FUNCTION
statement:
DROP
TABLE
FUNCTION
mydataset
.
names_by_year
Authorize routines
You can authorize table functions as routines . Authorized routines let you share query results with specific users or groups without giving them access to the underlying tables that generated the results. For example, an authorized routine can compute an aggregation over data or look up a table value and use that value in a computation. For more information, see Authorized routines .
Limitations
-
Parameters must be scalar values. Table functions in BigQuery cannot take tables as input parameters.
-
The query body must be a
SELECTstatement and cannot modify anything. For example, data definition language (DDL) and data manipulation language (DML) statements are not allowed in table functions. If you need side-effects, consider writing a procedure instead. -
Table functions must be stored in the same location as the tables they reference.
Quotas
For more information about table function quotas and limits, see Quotas and limits .
