2

I have an existing query which uses TABLE_QUERY(), and filters the results based on creation_time:

SELECT 
     *
FROM (TABLE_QUERY( project_114151_dataset , "MSEC_TO_TIMESTAMP(creation_time) > DATE_ADD(CURRENT_TIMESTAMP(), -45, 'DAY') AND REGEXP_MATCH(table_id, r'^fact_[0-9]{8}$') "))

I want to change the query to run based on last_modified_time; since it is also a timestamp in msec, I changed the query to be as follows

SELECT 
     *
FROM (TABLE_QUERY( project_114151_dataset , "MSEC_TO_TIMESTAMP(last_modified_time) > DATE_ADD(CURRENT_TIMESTAMP(), -45, 'DAY') AND REGEXP_MATCH(table_id, r'^fact_[0-9]{8}$') "))

However, when running I am getting the following error:

Error: Error evaluating subsidiary query

Not sure why I'm getting this error; I've verified that this field exists by running the following query, and it does indeed return in the results:

SELECT * FROM project_114151_dataset.__TABLES__ 
WHERE MSEC_TO_TIMESTAMP(creation_time)  < 
DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

Any help would be appreciated!

Gil Adirim
  • 1,834
  • 2
  • 21
  • 33

2 Answers2

1

The expression inside TABLE_QUERY cannot use data form the table, it has to use constants and free functions (such as CURRENT_TIMESTAMP) only.

Mosha Pasumansky
  • 13,206
  • 5
  • 32
  • 55
1

The only fields available in the TABLE_QUERY() function are: project_id, dataset_id, table_id, creation_time, and type. They are the feilds returned by the __TABLES_SUMMARY__ pseudo-table. (try SELECT * FROM project_114151_dataset.__TABLES_SUMMARY__).

Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63
  • I understand that this has changed? I was basing my syntax on your post found here: http://stackoverflow.com/questions/22734777/how-do-i-use-the-table-query-function-in-bigquery Would it be possible to update the documentation as well? – Gil Adirim May 04 '15 at 04:33
  • @GilAdirim what has changed on that post? – Pentium10 May 04 '15 at 06:21
  • @Pentium10 on your answer I've linked to, you explain that table_query() uses the same logic as behind __TABLES__, which includes the last_modifed_time field; whereas Jordan's answer implies that the logic works by returning data from __TABLES_SUMMARY__ which does not. So which is the correct? – Gil Adirim May 04 '15 at 07:00
  • 1
    We switched to `__TABLES_SUMMARY__` for performance reasons... it is much faster than `__TABLES__` – Jordan Tigani May 04 '15 at 17:17