CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
lf215
lf215

Reputation: 1009

How to include table name in result for wildcard query?

I'm querying from a bunch of tables in bigquery using a wildcard query. I'd like each result row to show which table it's from.

I've tried to include _TABLE_SUFFIX in the select, but it won't compile:

SELECT _TABLE_SUFFIX, *
FROM `foo.bar_*`
WHERE x = ...

Invalid field name "_TABLE_SUFFIX"

Upvotes: 1

Views: 1401

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

use alias as in below example

SELECT _TABLE_SUFFIX as table_name, *    
FROM `foo.bar_*`
WHERE x = ...

You can also use below to preserve full table name

SELECT CONCAT('bar_', _TABLE_SUFFIX) as table_name, *    
FROM `foo.bar_*`
WHERE x = ...

Note also: Field names are not allowed to start with the (case-insensitive) prefixes _PARTITION, _TABLE_, _FILE_ and _ROW_TIMESTAMP

Upvotes: 2

Related Questions