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 con el valor que coincide 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 consultaUPDATE
, pero no se puede usar como el destino de la operaciónUPDATE
. - 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
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 la consulta de forma que analice un conjunto arbitrario de tablas, usa la seudocolumna _TABLE_SUFFIX
en la cláusula WHERE
. La seudocolumna _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 reduce el costo de ejecutar tus consultas.
Los filtros en _TABLE_SUFFIX
que incluyen subconsultas no pueden usarse para limitar el número de tablas analizadas en busca de una tabla comodín. Por ejemplo, en la siguiente consulta, no se limitan las tablas exploradas para la tabla comodín bigquery-public-data.noaa_gsod.gsod19*
.
#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%')
En la siguiente consulta, se limita el análisis según la condición del filtro, _TABLE_SUFFIX BETWEEN '40' and '60'
, pero no se limita según la condición que involucra la subconsulta.
#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%')
Para limitarlo en función de la condición relacionada con la subconsulta, puedes realizar dos consultas separadas.
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'
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.