Consulta varias tablas mediante una tabla comodín

Las tablas comodín te permiten consultar varias tablas con instrucciones de SQL concisas. Las tablas comodín solo están disponibles en GoogleSQL. Para obtener una funcionalidad equivalente en SQL heredado, consulta Funciones de comodín de tablas.

Una tabla comodín representa una unión de todas las tablas que coinciden con la expresión de comodín. Por ejemplo, en la siguiente cláusula FROM se usa la expresión comodín gsod* para hacer coincidir todas las tablas del conjunto de datos noaa_gsod que comienzan con la string gsod.

FROM
  `bigquery-public-data.noaa_gsod.gsod*`

Cada fila de la tabla comodín contiene una columna especial, _TABLE_SUFFIX, con el valor que coincide con el carácter comodín.

Limitaciones

Las consultas de tablas comodín están sujetas a las siguientes limitaciones.

• La funcionalidad de la tabla comodín no admite vistas. Si la tabla comodín coincide con alguna vista del conjunto de datos, la consulta muestra un error incluso si tu consulta contiene una cláusula WHERE en la seudocolumna _TABLE_SUFFIX para filtrar la vista.
• Los resultados almacenados en caché no son compatibles con las consultas de varias tablas con un comodín, incluso si la opción Usar resultados en caché está marcada. Si ejecutas la misma consulta comodín varias veces, se te facturará por cada consulta.
• Las tablas comodín solo admiten el almacenamiento integrado en BigQuery. No puedes usar comodines para consultar una tabla externa o una vista.
• No puedes usar consultas comodín en tablas con particiones incompatibles o en una combinación de tablas con y sin particiones. Las tablas consultadas también deben tener especificaciones idénticas de agrupamiento en clústeres.
• Puedes usar tablas comodín con tablas particionadas y estas se admiten para la reducción de particiones y clústeres. Sin embargo, las tablas agrupadas en clústeres que no tienen particiones, no obtienen ninguna reducción de clúster como beneficio del uso del comodín.
• Las consultas que contienen declaraciones de lenguaje de manipulación de datos (DML) no pueden usar una tabla comodín como destino de la consulta. Por ejemplo, una tabla comodín se puede usar en la cláusula FROM de una consulta UPDATE, pero no se puede usar como el destino de la operación UPDATE.
• Los filtros de las seudocolumnas _TABLE_SUFFIX o _PARTITIONTIME que incluyen funciones definidas por el usuario de JavaScript no limitan el número de tablas analizadas en una tabla comodín.
• Las consultas comodín no son compatibles con las tablas protegidas con claves de encriptación administradas por el cliente (CMEK).
• Todas las tablas a las que se hace referencia en una consulta comodín deben tener exactamente el mismo conjunto de claves y valores de etiqueta.

• Cuando se usan tablas comodín, todas las tablas del conjunto de datos que comienzan con el nombre de la tabla antes de * se analizan, incluso si _TABLE_SUFFIX se usa en combinación con REGEXP_CONTAINS y se le proporciona una expresión regular, como ^[0-9]{2}$. Por ejemplo:

  SELECT *
  FROM `my_project.my_dataset.my_table_*`
  WHERE REGEXP_CONTAINS(_TABLE_SUFFIX, '^[0-9]{2}$');
  

• Si una sola tabla analizada tiene una discrepancia de esquemas (es decir, una columna con el mismo nombre es de un tipo diferente), la consulta falla y muestra el error Cannot read field of type X as Y Field: column_name (No se puede leer el campo de tipo X como un campo Y: column_name). Todas las tablas coinciden, incluso si usas el operador de igualdad =. Por ejemplo, en la siguiente consulta, la tabla my_dataset.my_table_03_backup también se analiza. Por lo tanto, la consulta puede fallar debido a una falta de coincidencia del esquema. Sin embargo, si no hay ninguna coincidencia del esquema, los resultados provienen solo de la tabla my_dataset.my_table_03, como se esperaba.

  SELECT *
  FROM my_project.my_dataset.my_table_*
  WHERE _TABLE_SUFFIX = '03'
  

Antes de comenzar

• Asegúrate de usar GoogleSQL. Para obtener más información, consulta Cambia los dialectos de SQL.
• Si usas SQL heredado, consulta Funciones comodín de tablas.
• Muchos de los ejemplos de esta página usan un conjunto de datos públicos de la Administración Nacional Oceánica y Atmosférica (NOAA). Para obtener más información sobre los datos, consulta el Resumen diario de datos climáticos de la superficie global de la NOAA.

Cuándo usar tablas comodín

Las tablas comodín son útiles cuando un conjunto de datos contiene varias tablas con nombres similares que tienen esquemas compatibles. Por lo general, estos conjuntos de datos contienen tablas que representan datos de un solo día, mes o año. Por ejemplo, un conjunto de datos públicos alojado en BigQuery, el Resumen diario de datos climáticos de la superficie global de la NOAA, contiene una tabla por cada año desde 1929 hasta el presente.

Una consulta que analiza todos los ID de tablas desde 1929 hasta 1940 sería muy larga si tuvieras que nombrar las 12 tablas en la cláusula FROM (la mayoría de las tablas se omiten en este ejemplo):

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM (
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1929` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1930` UNION ALL
  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1931` UNION ALL

  # ... Tables omitted for brevity

  SELECT
    *
  FROM
    `bigquery-public-data.noaa_gsod.gsod1940` )
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

La misma consulta con una tabla comodín es mucho más concisa:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29'
  AND '40'
ORDER BY
  max DESC

Las tablas comodín solo admiten el almacenamiento integrado en BigQuery. No puedes usar comodines cuando consultas una tabla externa o una vista.

Sintaxis de la tabla comodín

Sintaxis de la tabla comodín:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression

<project-id>

ID del proyecto de Cloud Platform. Es opcional si usas tu ID del proyecto predeterminado.

<dataset-id>

ID del conjunto de datos de BigQuery.

<table-prefix>

Una string que se usa para todas las tablas que coinciden con el carácter comodín. El prefijo de tabla es opcional. Si se omite el prefijo de tabla, coincide con todas las tablas del conjunto de datos.

* (carácter comodín)

El carácter comodín, “*”, representa uno o más caracteres del nombre de una tabla. El carácter comodín solo puede aparecer al final del nombre de una tabla comodín.

Las consultas con tablas comodín admiten la seudocolumna _TABLE_SUFFIX en la cláusula WHERE. Esta columna contiene los valores que coinciden con el carácter comodín, de modo que, en las consultas, se puede filtrar a qué tablas se accede. Por ejemplo, las siguientes cláusulas WHERE usan operadores de comparación para filtrar las tablas coincidentes:

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

Para obtener más información sobre la seudocolumna _TABLE_SUFFIX, consulta Filtra las tablas seleccionadas con _TABLE_SUFFIX.

Escribe nombres de tablas con comodines entre acentos graves

El nombre de la tabla comodín contiene el carácter especial (*), lo que significa que debes escribir el nombre de la tabla comodín entre caracteres de acento grave (`). Por ejemplo, la siguiente consulta es válida porque usa acentos graves:

#standardSQL
/* Valid SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

La siguiente consulta NO es válida porque no se encuentra entre acentos graves:

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Las comillas no funcionan:

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Consulta tablas con las tablas comodín

Las tablas comodín te permiten consultar varias tablas de forma concisa. Por ejemplo, un conjunto de datos públicos alojado en BigQuery, el Resumen diario de datos climáticos de la superficie global de la NOAA, contiene una tabla por cada año desde 1929 hasta el presente, y todas estas tablas comparten el prefijo común gsod seguido de los cuatro dígitos del año. Las tablas se denominan gsod1929, gsod1930, gsod1931 y así.

Para consultar un grupo de tablas que comparten un prefijo común, usa el símbolo de comodín de tabla (*) después del prefijo de tabla en tu instrucción FROM. Por ejemplo, la siguiente consulta encuentra la temperatura máxima informada durante la década de 1940:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
ORDER BY
  max DESC

Filtra las tablas seleccionadas con _TABLE_SUFFIX

Para restringir una consulta de forma que solo analice un conjunto específico de tablas, usa la seudocolumna _TABLE_SUFFIX en una cláusula WHERE con una condición que sea una expresión constante.

La pseudocolumna _TABLE_SUFFIX contiene los valores que coinciden con el comodín de la tabla. Por ejemplo, la consulta de muestra anterior que analiza todas las tablas de la década de 1940 usa un comodín de tabla para representar el último dígito del año:

FROM
  `bigquery-public-data.noaa_gsod.gsod194*`

La seudocolumna _TABLE_SUFFIX correspondiente contiene valores en el rango de 0 a 9, que representan las tablas de gsod1940 a gsod1949. Estos valores de _TABLE_SUFFIX se pueden usar en la cláusula WHERE para filtrar tablas específicas.

Por ejemplo, a fin de filtrar la temperatura máxima en los años 1940 y 1944, usa los valores 0 y 4 para _TABLE_SUFFIX.

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod194*`
WHERE
  max != 9999.9 # code for missing data
  AND ( _TABLE_SUFFIX = '0'
    OR _TABLE_SUFFIX = '4' )
ORDER BY
  max DESC

Usar _TABLE_SUFFIX puede reducir notablemente la cantidad de bytes analizados, lo que ayuda a reducir el costo de ejecutar tus consultas.

Sin embargo, los filtros en _TABLE_SUFFIX que incluyen condiciones sin expresiones constantes no limitan el número de tablas analizadas en una tabla comodín. Por ejemplo, en la siguiente consulta no se limitan las tablas analizadas para la tabla comodín bigquery-public-data.noaa_gsod.gsod19* porque el filtro usa el valor dinámico de la columna table_id:

#standardSQL
# Scans all tables that match the prefix `gsod19`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

Como otro ejemplo, en la siguiente consulta se limita el análisis según la primera condición del filtro, _TABLE_SUFFIX BETWEEN '40' and '60', porque es una expresión constante. Sin embargo, en la siguiente consulta no se limita el análisis según la segunda condición del filtro, _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES WHERE table_name LIKE 'gsod194%'), porque es una expresión dinámica:

#standardSQL
# Scans all tables with names that fall between `gsod1940` and `gsod1960`
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  _TABLE_SUFFIX BETWEEN '40' AND '60'
  AND _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%')

Como solución alternativa, puedes realizar dos consultas separadas, por ejemplo:

Primera consulta:

#standardSQL
# Get the list of tables that match the required table name prefixes
SELECT SUBSTR(MAX(table_name), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES`
      WHERE table_name LIKE 'gsod194%'

Segunda consulta:

#standardSQL
# Construct the second query based on the values from the first query
SELECT
  ROUND((max-32)*5/9,1) celsius
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '49'

En estas consultas de ejemplo, se usa la vista INFORMATION_SCHEMA.TABLES. Para obtener más información sobre la tabla INFORMATION_SCHEMA, consulta Obtén metadatos de tabla con INFORMATION_SCHEMA.

Analiza un rango de tablas con _TABLE_SUFFIX

Para analizar un rango de tablas, usa la pseudocolumna _TABLE_SUFFIX junto con la cláusula BETWEEN. Por ejemplo, para encontrar la temperatura máxima informada entre los años 1929 y 1935, usa el comodín de tabla a fin de representar los dos últimos dígitos del año:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod19*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '29' and '35'
ORDER BY
  max DESC

Analiza un rango de tablas particionadas por tiempo de transferencia con _PARTITIONTIME

Para analizar un rango de tablas particionadas por tiempo de transferencia, usa la seudocolumna _PARTITIONTIME con la pseudocolumna _TABLE_SUFFIX. Por ejemplo, en la siguiente consulta se analiza la partición del 1 de enero de 2017 en la tabla my_dataset.mytable_id1.

#standardSQL
SELECT
  field1,
  field2,
  field3
FROM
  `my_dataset.mytable_*`
WHERE
  _TABLE_SUFFIX = 'id1'
  AND _PARTITIONTIME = TIMESTAMP('2017-01-01')

Consulta todas las tablas en un conjunto de datos

Para analizar todas las tablas en un conjunto de datos, puedes usar un prefijo vacío y el comodín de tabla, lo que significa que la seudocolumna _TABLE_SUFFIX contiene nombres de tabla completos. Por ejemplo, la siguiente cláusula FROM analiza todas las tablas en el conjunto de datos GSOD:

FROM
  `bigquery-public-data.noaa_gsod.*`

Con un prefijo vacío, la seudocolumna _TABLE_SUFFIX contiene nombres de tabla completos. Por ejemplo, la siguiente consulta es equivalente al ejemplo anterior que encuentra la temperatura máxima entre los años 1929 y 1935, pero usa nombres de tabla completos en la cláusula WHERE:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  mo,
  da,
  year
FROM
  `bigquery-public-data.noaa_gsod.*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod1929' and 'gsod1935'
ORDER BY
  max DESC

Sin embargo, ten en cuenta que los prefijos más largos suelen funcionar mejor. Para obtener más información, consulta las Recomendaciones.

Detalles de ejecución de consulta

Esquema usado para la evaluación de consultas

Para ejecutar una consulta de GoogleSQL que usa una tabla comodín, BigQuery infiere de forma automática el esquema para esa tabla. BigQuery usa el esquema de la tabla más reciente que coincide con el carácter comodín como el esquema de la tabla comodín. Incluso si restringes la cantidad de tablas que deseas usar de la tabla comodín mediante la seudocolumna _TABLE_SUFFIX en una cláusula WHERE, BigQuery usa el esquema para la tabla más recientemente creada que coincida con el comodín.

Si una columna del esquema inferido no existe en una tabla coincidente, BigQuery muestra valores NULL para esa columna en las filas de la tabla en la que falta la columna.

Si el esquema es incoherente en las tablas que coinciden con la consulta comodín, BigQuery muestra un error. Este es el caso cuando las columnas de las tablas coincidentes tienen tipos de datos diferentes, o cuando no se puede suponer que las columnas que no están presentes en todas las tablas coincidentes tienen un valor nulo.

Prácticas recomendadas

• Los prefijos más largos suelen funcionar mejor que los cortos. Por ejemplo, en la siguiente consulta se usa un prefijo largo (gsod200):

  #standardSQL
  SELECT
  max
  FROM
  `bigquery-public-data.noaa_gsod.gsod200*`
  WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN '0' AND '1'
  ORDER BY
  max DESC
  

  La siguiente consulta suele funcionar peor porque usa un prefijo vacío:

  #standardSQL
  SELECT
  max
  FROM
  `bigquery-public-data.noaa_gsod.*`
  WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX BETWEEN 'gsod2000' AND 'gsod2001'
  ORDER BY
  max DESC
  

• Se recomienda realizar particiones en lugar de fragmentación, puesto que las tablas particionadas tienen un mejor rendimiento. La fragmentación reduce el rendimiento, a la vez que crea más tablas para administrar. Si deseas obtener más información, consulta Partición en comparación con fragmentación.

Si deseas conocer las prácticas recomendadas para controlar los costos en BigQuery, consulta Controla costos en BigQuery.

¿Qué sigue?

• Para obtener más información sobre GoogleSQL, consulta la Referencia sobre consultas de GoogleSQL.