Esta página se ha traducido con Cloud Translation API.

Consultar varias tablas con una tabla comodín

Las tablas comodín te permiten consultar varias tablas mediante instrucciones SQL concisas. Las tablas comodín solo están disponibles en GoogleSQL. Para obtener una función equivalente en SQL antiguo, consulta Funciones comodín de tabla.

Una tabla comodín representa la unión de todas las tablas que coinciden con la expresión comodín. Por ejemplo, la siguiente cláusula FROM usa la expresión comodín gsod* para que coincida con todas las tablas del conjunto de datos noaa_gsod que empiecen por la cadena gsod.

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

Cada fila de la tabla de comodines contiene una columna especial, _TABLE_SUFFIX, que incluye 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 función de tabla comodín no admite vistas. Si la tabla comodín coincide con alguna vista del conjunto de datos, la consulta devuelve un error aunque contenga una cláusula WHERE en la pseudocolumna _TABLE_SUFFIX para filtrar la vista.
Los resultados almacenados en caché no pueden utilizarse en las consultas de varias tablas con comodín aunque esté marcada la opción Utilizar los resultados almacenados en caché. Si realiza varias veces la misma consulta con comodín, se le facturará cada una de ellas.
Las tablas comodín solo admiten el almacenamiento integrado de BigQuery. No puedes usar caracteres comodín para consultar una tabla externa ni una vista.
No puedes usar consultas con comodines en tablas con particiones incompatibles o en una combinación de tablas con y sin particiones. Las tablas consultadas también deben tener las mismas especificaciones de agrupamiento en clústeres.
Puedes usar tablas comodín con tablas con particiones. Se admiten tanto la eliminación de particiones como la de clústeres. Sin embargo, las tablas agrupadas, pero no particionadas, no se benefician por completo de la poda de clústeres al usar comodines.
Las consultas que contienen instrucciones del lenguaje de manipulación de datos (DML) no pueden usar una tabla comodín como destino de la consulta. Por ejemplo, se puede usar una tabla comodín en la cláusula FROM de una consulta UPDATE, pero no se puede usar como destino de la operación UPDATE.
Los filtros de las pseudocolumnas _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 con comodines no se admiten en las tablas protegidas con claves de cifrado gestionadas por el cliente (CMEK).
Todas las tablas a las que se hace referencia en una consulta con comodín deben tener exactamente el mismo conjunto de claves y valores de etiqueta.
Cuando se usan tablas comodín, se analizan todas las tablas del conjunto de datos que empiezan por el nombre de la tabla antes de *, aunque se use _TABLE_SUFFIX en combinación con REGEXP_CONTAINS y se proporcione 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 tabla escaneada tiene un error de coincidencia de esquema (es decir, una columna con el mismo nombre es de un tipo diferente), la consulta fallará y se mostrará el error No se puede leer el campo de tipo X como Y Campo: column_name. Todas las tablas se emparejan aunque uses el operador de igualdad =. Por ejemplo, en la siguiente consulta también se analiza la tabla my_dataset.my_table_03_backup. Por lo tanto, es posible que la consulta falle debido a una discrepancia en el esquema. Sin embargo, si no hay ninguna discrepancia en el esquema, los resultados proceden únicamente de la tabla my_dataset.my_table_03, como es de esperar.
```
SELECT *
FROM my_project.my_dataset.my_table_*
WHERE _TABLE_SUFFIX = '03'
```

Antes de empezar

Asegúrate de que estás usando GoogleSQL. Para obtener más información, consulta Cambiar de dialecto de SQL.
Si usas Legacy SQL, consulta Funciones comodín de tabla.
Muchos de los ejemplos de esta página usan un conjunto de datos público de la Oficina Nacional de Administración Oceánica y Atmosférica de EE. UU. (NOAA). Para obtener más información sobre los datos, consulta NOAA Global Surface Summary of the Day Weather Data (Datos meteorológicos del resumen global de superficie 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 y esquemas compatibles. Normalmente, 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úblico alojado en BigQuery, los datos meteorológicos globales de la NOAA de resumen diario de la superficie, contiene una tabla para cada año desde 1929 hasta la actualidad.

Una consulta que analice todos los IDs de tabla 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 de BigQuery. No puedes usar comodines al consultar una tabla externa ni una vista.

Sintaxis de tabla comodín

Sintaxis de tabla comodín:

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

<project-id>: ID de proyecto de Cloud Platform. Es opcional si usas el ID de proyecto predeterminado.
<dataset-id>: ID del conjunto de datos de BigQuery.
<table-prefix>: Cadena que es común a todas las tablas que coinciden con el carácter comodín. El prefijo de tabla es opcional. Si se omite el prefijo de la tabla, se buscarán todas las tablas del conjunto de datos.
* (carácter comodín): El carácter comodín "*" representa uno o varios caracteres de un nombre de tabla. El carácter comodín solo puede aparecer como el último carácter de un nombre de tabla comodín.

Las consultas con tablas comodín admiten la pseudocolumna _TABLE_SUFFIX en la cláusula WHERE. Esta columna contiene los valores que coinciden con el carácter comodín, de modo que las consultas pueden 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 pseudocolumna _TABLE_SUFFIX, consulta el artículo sobre cómo filtrar tablas seleccionadas usando _TABLE_SUFFIX.

Incluye los nombres de las tablas con comodines entre comillas inversas

El nombre de la tabla comodín contiene el carácter especial (*), lo que significa que debes incluirlo entre comillas inversas (`). Por ejemplo, la siguiente consulta es válida porque usa comillas inversas:

#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 ha escrito correctamente entre comillas inversas:

#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

Consultar tablas con tablas comodín

Las tablas comodín te permiten consultar varias tablas de forma concisa. Por ejemplo, un conjunto de datos público alojado en BigQuery, NOAA Global Surface Summary of the Day Weather Data, contiene una tabla para cada año desde 1929 hasta la actualidad. Todas las tablas comparten el prefijo común gsod seguido del año de cuatro dígitos. Las tablas se denominan gsod1929, gsod1930, gsod1931, etc.

Para consultar un grupo de tablas que comparten un prefijo común, usa el símbolo comodín de tabla (*) después del prefijo de tabla en tu instrucción FROM. Por ejemplo, la siguiente consulta busca la temperatura máxima registrada 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

Filtrar tablas seleccionadas usando _TABLE_SUFFIX

Para restringir una consulta de forma que solo analice un conjunto de tablas específico, usa la pseudocolumna _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 ejemplo 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 pseudocolumna _TABLE_SUFFIX correspondiente contiene valores del intervalo 0 a 9, que representan las tablas gsod1940 a gsod1949. Estos valores _TABLE_SUFFIX se pueden usar en una cláusula WHERE para filtrar tablas específicas.

Por ejemplo, para filtrar la temperatura máxima de 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 considerablemente el número de bytes analizados, lo que ayuda a reducir el coste de ejecutar las consultas.

Sin embargo, los filtros de _TABLE_SUFFIX que incluyen condiciones sin expresiones constantes no limitan el número de tablas analizadas en una tabla con comodín. Por ejemplo, la siguiente consulta no limita 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%')

Por otro lado, la siguiente consulta limita el análisis en función de la primera condición de filtro, _TABLE_SUFFIX BETWEEN '40' and '60', porque es una expresión constante. Sin embargo, la siguiente consulta no limita el análisis en función de la segunda condición de 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 hacer dos consultas independientes. 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'

Estas consultas de ejemplo usan la vista INFORMATION_SCHEMA.TABLES. Para obtener más información sobre la tabla INFORMATION_SCHEMA, consulta el artículo Obtener metadatos de tablas con INFORMATION_SCHEMA.

Analizar un intervalo de tablas con _TABLE_SUFFIX

Para analizar un intervalo de tablas, usa la pseudocolumna _TABLE_SUFFIX junto con la cláusula BETWEEN. Por ejemplo, para encontrar la temperatura máxima registrada entre 1929 y 1935 (ambos años incluidos), usa el comodín de tabla para 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

Analizar un intervalo de tablas con particiones por hora de ingestión mediante _PARTITIONTIME

Para analizar un intervalo de tablas con particiones por hora de ingestión, usa la pseudocolumna _PARTITIONTIME con la pseudocolumna _TABLE_SUFFIX. Por ejemplo, la siguiente consulta analiza la partición del 1 de enero del 2017 de 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')

Consultar todas las tablas de un conjunto de datos

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

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

Si el prefijo está vacío, la pseudocolumna _TABLE_SUFFIX contiene los nombres completos de las tablas. Por ejemplo, la siguiente consulta es equivalente a la del ejemplo anterior, que busca 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 tener un mejor rendimiento. Para obtener más información, consulta las prácticas recomendadas.

Detalles de ejecución de la consulta

Esquema usado para la evaluación de consultas

Para ejecutar una consulta de GoogleSQL que use una tabla comodín, BigQuery infiere automáticamente el esquema de esa tabla. BigQuery usa el esquema de la tabla creada más recientemente que coincide con el comodín como esquema de la tabla comodín. Aunque restrinjas el número de tablas que quieres usar de la tabla comodín mediante la pseudocolumna _TABLE_SUFFIX en una cláusula WHERE, BigQuery usará el esquema de la tabla creada más recientemente que coincida con el comodín.

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

Si el esquema no es coherente en las tablas que coinciden con la consulta con comodín, BigQuery devuelve un error. Esto ocurre 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 dar mejores resultados que los más cortos. Por ejemplo, la siguiente consulta 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 tener un rendimiento 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 usar particiones en lugar de fragmentación, ya que las tablas con particiones tienen un mejor rendimiento. La fragmentación reduce el rendimiento al crear más tablas que gestionar. Para obtener más información, consulta Partición frente a fragmentación.

Para consultar las prácticas recomendadas para controlar los costes en BigQuery, consulta el artículo Controlar los costes en BigQuery.

Siguientes pasos

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