Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://cloud.google.com/bigquery/docs/table-functions below:

Table functions | BigQuery | Google Cloud

Stay organized with collections Save and categorize content based on your preferences.

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.

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
);

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.

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)

Table functions are a type of routine. To list all of the routines in a dataset, see List routines.

To delete a table function, use the DROP TABLE FUNCTION statement:

DROP TABLE FUNCTION mydataset.names_by_year

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.

• Parameters must be scalar values. Table functions in BigQuery cannot take tables as input parameters.

• The query body must be a SELECT statement 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.

For more information about table function quotas and limits, see Quotas and limits.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-07 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-07 UTC."],[[["Table functions, or table-valued functions (TVFs), are user-defined functions that return a table, usable anywhere a table is valid, and can take parameters, unlike views."],["Table functions are created using the `CREATE TABLE FUNCTION` statement, which contains a query that produces a table, and the function returns the query's result."],["You can call table functions within `SELECT` statements, join their output with other tables, and use them in subqueries."],["Table functions are subject to certain limitations, such as parameters having to be scalar values, and the query body must be a `SELECT` statement without any modifications."],["Table functions are classified as a type of routine in big query, which are authorized to share query results with users without giving direct access to the underlying data."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4