Schedule a bq command line in GCP with Cloud Pub/Sub topic

Question

I have a command line that I execute to select data from view and exporting to a staging table in BigQuery:

bq mk --transfer_config --headless=true --project_id="XXXXXXX" --target_dataset=XXXXXX --display_name=XXXXXX --schedule='XXXXXX'  --use_legacy_sql=false --params='{"query":"SELECT * FROM xxxxxxxx","destination_table_name_template":"xxxxxx","write_disposition":"WRITE_TRUNCATE"}' --data_source=scheduled_query --service_account_name="[email protected]"

Is there any way to set the Cloud Pub/Sub topic using the command line?

Thank you in advance for the help!

Answer 1

Obviously, by command line, you can't. It's possible by API call with this kind of payload

{
  "notificationPubsubTopic":"projects/PROJECT/topics/TOPIC",
  "scheduleOptions": {
    "disableAutoScheduling":false,
    "startTime":"2020-01-24T10:06:12.344Z"
  }, 
  "displayName":"test",
  "dataSourceId":"scheduled_query",
  "destinationDatasetId":"DATASET",
  "emailPreferences":{"enableFailureEmail":false},
  "params": {
    "query":"SELECT 1",
    "write_disposition":"WRITE_APPEND",
    "destination_table_name_template":"test_schedule"
  },
  "schedule":"every day 10:06"
}

Answer 2

This issue is a little tricky and not so obvious. I've found this discussion about it.

It seems that BigQuery's CLI or API doesn't support this kind of notifications when creating the transfer configuration.

As it was said in the discussion:

This means we can't programmatically create transfers with notifications enabled :(

You an find here the API's and CLI documentations regarding transfer service.

I hope it helps you.
If you need any further information or support, don't hesitate about asking me.

Answer 3

It seems that the Pub/Sub notification parameter has been added to the API and to the CLI (my CloudSDK version is 382.0). The parameter is notification_pubsub_topic. In my case I need the email notification which is not added yet to the CLI (but to the API). So I added it manually in bigquery_client.py in function CreateTransferConfig. For sure not the best way but it works for me.

emailOptions = dict()
emailOptions['enable_failure_email'] = True
create_items['email_preferences'] = emailOptions