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 SQL estándar. 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”, que contiene el valor coincidente con el carácter comodín.

Para obtener información sobre la sintaxis de la tabla comodín, consulta Tablas comodín en la referencia de SQL estándar.

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. Esto es cierto sin importar si tu consulta contiene una cláusula WHERE en la seudocolumna _TABLE_SUFFIX como filtro para evitar que se incluya la vista.
• En este momento, los resultados almacenados en caché no son compatibles con las consultas de varias tablas 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 nativo en BigQuery. No puedes usar comodines cuando consultas una tabla externa o una vista.
• 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.
• Las consultas comodín no son compatibles con las tablas protegidas con claves de encriptación administradas por el cliente (CMEK).

Antes de comenzar

• Asegúrate de utilizar el SQL estándar de BigQuery. Para obtener más información, consulta cómo cambiar 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 nativo de BigQuery. No puedes usar comodines cuando consultas una tabla externa o una vista.

Consulta conjuntos de tablas con 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í sucesivamente.

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 en gran medida la cantidad de bytes analizados, lo que ayuda a reducir el costo de la ejecución de tus consultas.

Sin embargo, los filtros en _TABLE_SUFFIX que incluyen condiciones que no son 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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id LIKE 'gsod194%')

Otro ejemplo: la siguiente consulta limita el análisis según la primera condición de filtro, _TABLE_SUFFIX BETWEEN '40' and '60', porque es una expresión constante. Sin embargo, la consulta siguiente no limita el análisis según la segunda condición de filtro, _TABLE_SUFFIX = (SELECT SUBSTR(MAX(table_id), LENGTH('gsod19') + 1) FROM bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__ WHERE table_id 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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id 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_id), LENGTH('gsod19') + 1)
      FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`
      WHERE table_id 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'

Nota: En las consultas de ejemplo se usa la metatabla __TABLES__SUMMARY.

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 SQL estándar 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. Si el esquema es incoherente en las tablas que coinciden con la tabla comodín, BigQuery muestra un error.

Recomendaciones

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

Próximos pasos

• Para obtener más información acerca de SQL estándar, revisa la Referencia de consulta de SQL estándar.