Getting GCS path metadata for BigQuery external tables

Question

When querying Cloud Storage data using permanent external tables,

CREATE OR REPLACE EXTERNAL TABLE mydataset.sales
OPTIONS (
  format = 'CSV',
  uris = ['gs://mybucket/sales-google.csv', 'gs://mybucket/sales-microsoft.csv']
)

Is there a way to know which file the row was ingested from? Metadata?

SELECT * FROM mydataset.sales

Answer 1

Use _FIE_NAME pseudo column:

SELECT 
  *,
  _FILE_NAME AS file_name
FROM mydataset.sales