How to run a big query schedule query as soon as another big query schedule query has run

Question

I am having issues triggering a schedule big query job through another schedule big query job. I have 2 big query tables which gets loaded once file is placed in google cloud bucket. I have 2 data flow job which triggers the process and load big query tables. Using cloud function and pub sub topic , I trigger schedule queries for these 2 tables. It was very easy for me to create a filter for a data flow job which will trigger my schedule query. for example as below

protoPayload.methodName="jobservice.jobcompleted"
protoPayload.serviceData.jobCompletedEvent.job.jobStatus.state="DONE"
protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.load.destinationTable.tableId="table_name"
protoPayload.serviceData.jobCompletedEvent.job.referencedTables.datasetId="big query dataset name"
protoPayload.serviceData.jobCompletedEvent.job.referencedTables.projectId="my project id"

This works fine and schedule query is triggered. I have similar check for other table.

Now, the problem that I am facing is, I need to trigger another scheudle query once my previous schedule query has run fine, Here is the challenge. The only thing I can think of and can filter logs based on below

    protoPayload.methodName="jobservice.jobcompleted"
    protoPayload.serviceData.jobCompletedEvent.job.jobStatus.state="DONE"
  protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.labels.data_source_id="scheduled_query"

However, the above gives me logs for all schedule queries running in my project where in I am looking for a specific one. My schedule query that has run earlier has many insert and few update statement and hence table id is zero there.The only unique thing is query that is present in each schedule query logs, Do you think I need to add the last query out of the multiple queries running as part of my schedule query to filter the logs and which says schedule query is completed? for example below

protoPayload.methodName="jobservice.jobcompleted"
protoPayload.serviceData.jobCompletedEvent.job.jobStatus.state="DONE"
protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.labels.data_source_id="scheduled_query"
protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query="UPDATE `whr-asia-datalake-nonprod.WHR_DATALAKE.CONSUMER_EXTRACT` CEXT SET CEXT.CUST_MOBILE=If ( length(SAFE_CAST(CEXT.CUST_MOBILE AS STRING))=10,SAFE_CAST(CONCAT('91',CEXT.CUST_MOBILE) AS Numeric),CEXT.CUST_MOBILE) WHERE TRUE"

However, the above doesn't give any logs. Please advise as I am stuck and need to deploy this in prod.

Answer 1

You can use the Schedule query pubsub notification feature to get the updates on the execution process. In a Cloud Functions, receive and process the PubSub messages and check the value of the state, and when it's done, trigger your schedule query as you did the first time.