Can I retrieve filename for external table data in BigQuery?

Question

Looking to implement a simple datastore for a departmental team where they currently manage a load of excel/csv files. We will get them to prepare the files and drop them in CSV format into a GCS bucket and then point an external BQ table at this (that all works great).

However, if they run a query and see some data and then want to find where that data has actually been pulled from, how can we find out (assuming there is no contextual clues in the filename) which file contains the row(s) in question?

Answer 1

Adding a function before, like say lower() gives the output instead of error "Invalid field name "_FILE_NAME"

try this :

SELECT lower(_FILE_NAME) as f FROM externalTable'