Come faccio a utilizzare la funzione TABLE_QUERY () in BigQuery?

Un paio di domande sulla funzione TABLE_QUERY:

  • Gli esempi mostrano l’uso di table_id nella stringa di query, ci sono altri campi disponibili?
  • Sembra difficile eseguire il debug. Ricevo “errore nella valutazione della query secondaria” quando provo a utilizzarlo.
  • Come funziona TABLE_QUERY() ?

La funzione TABLE_QUERY() consente di scrivere una clausola WHERE SQL valutata per individuare le tabelle su cui eseguire la query. Ad esempio, è ansible eseguire la seguente query per contare le righe in tutte le tabelle nei dati publicdata:samples set di dati dei publicdata:samples età superiore a 7 giorni:

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

Oppure puoi eseguirlo per eseguire una query su tutte le tabelle che hanno 'git' nel nome (che sono le tabelle di esempio github_timeline e github_nested ) e trovare gli URL più comuni:

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

Nonostante sia molto potente, TABLE_QUERY() può essere difficile da usare. La clausola WHERE deve essere specificata come una stringa, che può essere un po 'imbarazzante. Inoltre, può essere difficile eseguire il debug, poiché quando si verifica un problema, viene visualizzato solo l'errore "Errore nella valutazione della query secondaria", che non è sempre utile.

Come funziona:

TABLE_QUERY() esegue essenzialmente due query. Quando esegui TABLE_QUERY(, ) , BigQuery esegue SELECT table_id FROM .__TABLES_SUMMARY__ WHERE per ottenere l'elenco di ID tabella su cui eseguire la query, quindi esegue la query effettiva su tali tabelle .

La parte __TABLES__ di quella query potrebbe sembrare non familiare. __TABLES_SUMMARY__ è una meta-tabella contenente informazioni sulle tabelle in un set di dati. Puoi usare questo meta-tavolo tu stesso. Ad esempio, la query SELECT * FROM publicdata:samples.__TABLES_SUMMARY__ restituirà i metadati relativi alle tabelle nel dataset publicdata:samples .

Campi disponibili:

I campi della meta-tabella __TABLES_SUMMARY__ (che sono tutti disponibili nella query TABLE_QUERY ) includono:

  • table_id : nome della tabella.
  • creation_time : time, in millisecondi dal 1/1/1970 UTC, che la tabella è stata creata. È lo stesso del campo creation_time sul tavolo.
  • type : se è una vista (2) o una tabella normale (1).

I seguenti campi non sono disponibili in TABLE_QUERY() poiché sono membri di __TABLES__ ma non __TABLES_SUMMARY__ . Sono tenuti qui per interesse storico e per documentare parzialmente la __TABLES__ __TABLES__:

  • last_modified_time : time, in millisecondi dal 1/1/1970 UTC, che la tabella è stata aggiornata (metadati o contenuto della tabella). Si noti che se si utilizza tabledata.insertAll() per trasmettere i record alla tabella, è ansible che alcuni minuti non siano aggiornati.
  • row_count : numero di righe nella tabella.
  • size_bytes : dimensione totale in byte della tabella.

Come eseguire il debug

Per eseguire il debug delle tue query TABLE_QUERY() , puoi fare la stessa cosa che fa BigQuery; cioè, puoi eseguire direttamente la query metatable. Per esempio:

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

ti consente non solo di eseguire il debug della query, ma anche di vedere quali tabelle verranno restituite quando esegui la funzione TABLE_QUERY . Dopo aver eseguito il debug della query interna, è ansible inserirla con la query completa su tali tabelle.

Risposta alternativa, per coloro che passano allo standard SQL:

  • SQL standard BigQuery non supporta TABLE_QUERY, ma supporta * l’espansione per i nomi delle tabelle.
  • Quando si espandono i nomi delle tabelle *, è ansible utilizzare la meta colonna _TABLE_SUFFIX per restringere la selezione.
  • L’espansione della tabella con * funziona solo quando tutte le tabelle hanno schemi compatibili.

Ad esempio, per ottenere la temperatura media mondiale NOAA di GSOD tra il 2010 e il 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