Funciones de tabla

Una función de tabla, también llamada función con valores de tabla (TVF), es una función definida por el usuario que devuelve 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 forma similar a las vistas, pero una función de tabla puede aceptar parámetros.

Crear funciones de tabla

Para crear una función de tabla, usa la instrucción CREATE TABLE FUNCTION. Una función de tabla contiene una consulta que genera una tabla. La función devuelve el resultado de la consulta. La siguiente función de tabla toma un parámetro INT64 y usa este valor en una cláusula WHERE en una consulta sobre un conjunto de datos público 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 formas, 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 tabla

Puede definir parámetros de TVF como tablas. Después del parámetro table_name, debe especificar explícitamente el esquema de tabla necesario, de la misma forma que especifica los campos de una estructura. El argumento de tabla que se pasa a la función de tabla con valores 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 la tabla, no al parámetro. Lo recomendable es usar nombres de parámetros que sean distintos de los nombres de las columnas de las tablas a las que se haga referencia.

Usar funciones de tabla

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

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

Los resultados tienen este aspecto:

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

Puedes combinar 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 incluyan en el esquema del parámetro de 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 |
 +-------------+-------*/

Mostrar funciones de tabla

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

Eliminar funciones de tabla

Para eliminar una función de tabla, usa la instrucción DROP TABLE FUNCTION:

DROP TABLE FUNCTION mydataset.names_by_year

Autorizar rutinas

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

Limitaciones

• El cuerpo de la consulta debe ser una instrucción SELECT y no puede modificar nada. Por ejemplo, no se permiten instrucciones de lenguaje de definición de datos (DDL) ni de lenguaje de manipulación de datos (DML) en las funciones de tabla. Si necesitas efectos secundarios, te recomendamos que escribas un procedimiento.

• 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.