Funciones de tabla

Una función de tabla, también llamada función con valor de tabla (TVF), es una función definida por el usuario que muestra una tabla. Puedes usar una función de tabla en cualquier lugar en el que puedas usar una tabla. Las funciones de tabla se comportan de manera similar a las vistas, pero una función de tabla puede tomar parámetros.

Crea funciones de tabla

Para crear una función de tabla, usa la sentencia CREATE TABLE FUNCTION. Una función de tabla contiene una consulta que produce una tabla. La función muestra el resultado de la consulta. La siguiente función de tabla toma un parámetro INT64 y usa este valor dentro de una cláusula WHERE en una consulta en un conjunto de datos públicos llamado 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
);

Para filtrar de otras maneras, puedes pasar varios parámetros a una función de tabla. La siguiente función de tabla filtra los datos por año y prefijo de nombre:

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

Parámetros de la tabla

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Nota: Para solicitar asistencia o proporcionar comentarios sobre esta función, envía un correo electrónico a [e-mail].

Puedes configurar los parámetros de la TVF para que sean tablas. Después del nombre del parámetro de la tabla, debes especificar el esquema de tabla requerido de forma explícita, de la misma manera en que especificas los campos de una struct. El argumento de tabla que pasas a la TVF puede contener columnas adicionales además de las especificadas en el esquema de parámetros, y las columnas pueden aparecer en cualquier orden.

La siguiente función de tabla devuelve una tabla que contiene las ventas totales de item_name de la tabla orders:

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

Nombres de parámetros

Si un parámetro de función de tabla coincide con el nombre de una columna de tabla, puede crear una referencia ambigua. En ese caso, BigQuery interpreta el nombre como una referencia a la columna de tabla, no al parámetro. Se recomienda usar nombres de parámetros que sean distintos de los nombres de cualquier columna de tabla a la que se haga referencia.

Usa funciones de tabla

Puedes llamar a una función de tabla en cualquier contexto en el que una tabla sea válida. En el siguiente ejemplo, se llama a la función mydataset.names_by_year en la cláusula FROM de una sentencia SELECT:

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

Los resultados se ven de la siguiente manera:

+------+--------+-------+
| year |  name  | total |
+------+--------+-------+
| 1950 | James  | 86447 |
| 1950 | Robert | 83717 |
| 1950 | Linda  | 80498 |
| 1950 | John   | 79561 |
| 1950 | Mary   | 65546 |
+------+--------+-------+

Puedes unir el resultado de una función de tabla con otra tabla:

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

También puedes usar una función de tabla en una subconsulta:

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

Cuando llamas a una función de tabla que tiene un parámetro de tabla, debes usar la palabra clave TABLE antes del nombre del argumento de tabla. El argumento de tabla puede tener columnas que no se enumeran en el esquema de parámetros de la tabla:

CREATE TABLE FUNCTION mydataset.compute_sales (
  orders TABLE<sales INT64, item STRING>, item_name STRING)
AS (
  SELECT SUM(sales) AS total_sales, item
  FROM orders
  WHERE item = item_name
  GROUP BY item
);

WITH my_orders AS (
    SELECT 1 AS sales, "apple" AS item, 0.99 AS price
    UNION ALL
    SELECT 2, "banana", 0.49
    UNION ALL
    SELECT 5, "apple", 0.99)
SELECT *
FROM mydataset.compute_sales(TABLE my_orders, "apple");

/*-------------+-------+
 | total_sales | item  |
 +-------------+-------+
 | 6           | apple |
 +-------------+-------*/

Enumera funciones de tabla

Las funciones de tabla son un tipo de rutina. Para enumerar todas las rutinas en un conjunto de datos, consulta Enumera rutinas.

Borra funciones de la tabla

Para borrar una función de tabla, usa la sentencia DROP TABLE FUNCTION:

DROP TABLE FUNCTION mydataset.names_by_year

Autoriza rutinas

Puedes autorizar funciones de tabla como rutinas. Las rutinas autorizadas te permiten compartir resultados de consultas con usuarios o grupos específicos sin darles acceso a las tablas subyacentes que generaron los resultados. Por ejemplo, una rutina autorizada puede calcular una agregación en los datos o buscar un valor de tabla y usar ese valor en un cálculo. Para obtener más información, consulta Rutinas autorizadas.

Limitaciones

• El cuerpo de la consulta debe ser una sentencia SELECT y no puede modificar nada. Por ejemplo, las declaraciones de lenguaje de definición de datos (DDL) y de lenguaje de manipulación de datos (DML) no están permitidas en las funciones de tabla. Si necesitas efectos secundarios, considera escribir un procedimiento en su lugar.

• Las funciones de tabla deben almacenarse en la misma ubicación que las tablas a las que hacen referencia.

Cuotas

Para obtener más información sobre las cuotas y los límites de las funciones de tabla, consulta Cuotas y límites.