CODEWITHSUNDEEP
google-bigquery
J. G.
J. G.

Reputation: 1832

Bigquery API fails to recognize wildcard tables

SELECT
  timestamp,
  user,
  object_id
FROM
  `x.com:projectx.audit_log.audit_log*`
WHERE
    _TABLE_SUFFIX >= "20190401"
  and event = "EDITED"

But when I drag the query into my google apps script project i have problems, it won't recognize the table at all

JsonResponseException: API call to bigquery.jobs.query failed with error: Invalid table name x.com:projectx.audit_log.audit_log*

Is there another way to call wildcard tables?

Edit: the part that seems to be causing problems is enclosing the table in

`

This works: (for a single table) 

[x.com:x.audit_log.audit_log_20190801]

but this does not

`x.com:x.audit_log.audit_log_20190801`

Edit2:

This is the error I get for using

[x.com:x.audit_log_.audit_log_*]

(table) is not queryable with legacy SQL.

Upvotes: 2

Views: 335

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Try to add #standardSQL as in below example 

#standardSQL   
SELECT
  timestamp,
  user,
  object_id
FROM
  `x.com:projectx.audit_log.audit_log*`
WHERE
    _TABLE_SUFFIX >= "20190401"
  and event = "EDITED"

on a side note: try also just `projectx.audit_log.audit_log*` - w/o x.com: part. Domain part in fully qualified path (domain:project.dataset.table) might be optimal in some cases

Upvotes: 2

Related Questions