I have several tables in BigQuery that are sourced from Google Sheets tables. When the Google Sheets table is updated then automatically the table in BigQuery is also updated. I am trying to understand how the log of this event looks like in the Operations Logging. My end idea is to create a sink of theses logs in order to create a Pub/Sub and run scheduled queries based on these events.

Thank you

CodePudding user response：

When the external data source(Google Sheet or other) is updated and the BigQuery table associated with it is queried, BigQuery initiates an insert job which is visible in Cloud Logging. You can find this log by applying filter resource type as BigQuery Project in Cloud Logging console, ie. you will see protoPayload.methodName set to google.cloud.bigquery.v2.JobService.InsertJob.

For more information on BigQuery Logs you can refer to this documentation.

CodePudding user response：

When you use external Table (Google sheet or other) the data are never stored in BigQuery native storage. It's always external.

Therefore, when you update your Google Sheet, nothing happens in BigQuery. It's only when you query the data, you will read (again) the sheet document and get the latest data.

Therefore, there is no insert log that you can track when you update the data in Google Sheet. The only log that you have is when you perform a request in BigQuery to read the data (external or not), as mentioned by Sakshi.