google bigquery - safe_cast - ¿Cómo uso la función TABLE_QUERY() en BigQuery?

if big query

Question

Un par de preguntas sobre la función TABLE_QUERY:

• Los ejemplos muestran el uso de table_id en la cadena de consulta, ¿hay otros campos disponibles?
• Parece difícil de depurar. Obtengo un "error al evaluar la consulta secundaria" cuando trato de usarlo.
• ¿Cómo funciona TABLE_QUERY() ?

Answer 1

La función TABLE_QUERY() permite escribir una cláusula SQL WHERE que se evalúa para encontrar qué tablas ejecutar la consulta. Por ejemplo, puede ejecutar la siguiente consulta para contar las filas en todas las tablas en publicdata:samples conjunto de datos de publicdata:samples que son anteriores a 7 días:

SELECT count(*)
FROM TABLE_QUERY(publicdata:samples,
    "MSEC_TO_TIMESTAMP(creation_time) < "
    + "DATE_ADD(CURRENT_TIMESTAMP(), -7, ''DAY'')")

O puede ejecutar esto para consultar sobre todas las tablas que tienen ''git'' en el nombre (que son las tablas de muestra github_timeline y github_nested ) y encontrar las urls más comunes:

SELECT url, COUNT(*)
FROM TABLE_QUERY(publicdata:samples, "table_id CONTAINS ''git''")
GROUP EACH BY url
ORDER BY url DESC
LIMIT 100

A pesar de ser muy poderoso, TABLE_QUERY() puede ser difícil de usar. La cláusula WHERE debe especificarse como una cadena, lo que puede ser un poco incómodo. Además, puede ser difícil depurar, ya que cuando hay un problema, solo aparece el error "Error al evaluar la consulta secundaria", que no siempre es útil.

Cómo funciona:

TABLE_QUERY() básicamente ejecuta dos consultas. Cuando ejecuta TABLE_QUERY(<dataset>, <table_query>) , BigQuery ejecuta SELECT table_id FROM <dataset>.__TABLES_SUMMARY__ WHERE <table_query> para obtener la lista de ID de tabla para ejecutar la consulta, luego ejecuta su consulta real sobre esas tablas .

La parte __TABLES__ de esa consulta puede parecer desconocida. __TABLES_SUMMARY__ es una __TABLES_SUMMARY__ contiene información sobre tablas en un conjunto de datos. Puede usar esta meta-tabla usted mismo. Por ejemplo, la consulta SELECT * FROM publicdata:samples.__TABLES_SUMMARY__ devolverá metadatos sobre las tablas en publicdata:samples dataset.

Campos disponibles:

Los campos de la __TABLES_SUMMARY__ (que están todos disponibles en la consulta TABLE_QUERY ) incluyen:

• table_id : nombre de la tabla.
• creation_time : tiempo, en milisegundos desde 1/1/1970 UTC, en que se creó la tabla. Esto es lo mismo que el campo creation_time en la tabla.
• type : si es una vista (2) o una tabla normal (1).

Los siguientes campos no están disponibles en TABLE_QUERY() ya que son miembros de __TABLES__ pero no __TABLES_SUMMARY__ . Se guardan aquí para el interés histórico y para documentar parcialmente el __TABLES__ __TABLES__:

• last_modified_time : time, en milisegundos desde 1/1/1970 UTC, que la tabla se actualizó (ya sea metadatos o contenido de la tabla). Tenga en cuenta que si usa tabledata.insertAll() para transmitir registros a su tabla, esto puede tabledata.insertAll() algunos minutos.
• row_count : número de filas en la tabla.
• size_bytes : tamaño total en bytes de la tabla.

Cómo depurar

Para depurar sus consultas TABLE_QUERY() , puede hacer lo mismo que BigQuery; es decir, puede ejecutar la consulta metatabla usted mismo. Por ejemplo:

SELECT * FROM publicdata:samples.__TABLES_SUMMARY__ 
WHERE MSEC_TO_TIMESTAMP(creation_time)  < 
   DATE_ADD(CURRENT_TIMESTAMP(), -7, ''DAY'')

no solo le permite depurar su consulta, sino también ver qué tablas se devolverán cuando ejecute la función TABLE_QUERY . Una vez que haya depurado la consulta interna, puede juntarla con su consulta completa sobre esas tablas.

Answer 2

Respuesta alternativa, para aquellos que avancen a SQL estándar:

• BigQuery Standard SQL no es compatible con TABLE_QUERY, pero admite * expansión para nombres de tablas.
• Al expandir los nombres de tablas *, puede usar la metacolumna _TABLE_SUFFIX para restringir la selección.
• La expansión de tabla con * solo funciona cuando todas las tablas tienen esquemas compatibles.

Por ejemplo, para obtener la temperatura media mundial de NOAA GSOD entre 2010 y 2014:

#standardSQL
SELECT AVG(temp) avg_temp, _TABLE_SUFFIX y
FROM `bigquery-public-data.noaa.gsod_20*` #every year that starts with "20"
WHERE _TABLE_SUFFIX BETWEEN "10" AND "14" #only years between 2010 and 2014
GROUP BY y
ORDER BY y