4

This extends Jordan's post here: How do I use the TABLE_QUERY() function in BigQuery?

Here is an example of working TABLE_QUERY SQL.

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

However, TABLE_QUERY fails if the project_id contains a "-" hyphen. For example:

SELECT whatever
FROM TABLE_QUERY(other-public-data:samples,
    "MSEC_TO_TIMESTAMP(creation_time) < DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')")

Likely because the interpreter sees a subtraction operation.

Without an "eval" (JavaScript) or "exec" (Python) operation to convert strings to variable names, are there any suggestions to get this working in BigQuery?

Community
  • 1
  • 1
cgnorthcutt
  • 3,890
  • 34
  • 41

1 Answers1

4

"Escape" with []:

SELECT whatever
FROM TABLE_QUERY([other-public-data:samples],
  "MSEC_TO_TIMESTAMP(creation_time) < DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')")
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • 1
    Beautiful, thank you Felipe. This works! The one time I tested [], I had an additional error that obfuscated this as the correct syntax. Thanks! – cgnorthcutt Mar 04 '16 at 03:50