Bigquery cost execution

[shubham21feb]

How can i get the real time alert if the user executes a SQL in BIG QUERY and that SQL execution is taking a lot of time?

[marckevin]

Hi @shubham21feb,

Welcome to Google Cloud Community!

BigQuery cost refers to the charges incurred when running queries. Usage is usually reflected several hours later to billing, so it is impossible to get a real-time cost alert on your queries. A workaround is to cap your project’s quotas usage to monitor your real-time cost control. You can also set your Budget Alert to Forecasted cost as it notifies users earlier based on cost estimates.

I hope the above information is helpful.

[gojoe]

If you access to Gemini in the BigQuery console, try asking it something like "how do i create an alert if a bigquery query runs longer than 2 minutes". It should give you a good description with sample script.

Else, go to Cloud Monitoring and click on the "Alerting" tab. From there, create a new policy. Default option is to use Builder which is an easy way to get started. Once you are familiar with how it works, you should use scripts especially for production so you can have code reviews, re-use, manage versions, etc... Using Builder, select the metrics Global-->Query-->Query execution times. There are a few steps to complete but they're intuitive and you can always click on "Learn more" for docs. You do need to configure a notification channel; recommend at least 2 and of different types (e.g. 1 email, 1 SMS or some other mix, don't do 2 emails else you fly blind if mail service is asleep). Once done, run some queries that you know will exceed whatever threshold you set in the alert and you will receive the notifications in the channels you configured. 

In case you're tempted to setup functions to automatically terminate these queries, recommend you don't, at least for the first few months. Some queries may legitimately take a longer time and you do want them to complete. Also, users who don't know you're doing this might build "unintelligent" retries that creates other problems.

[shubham21feb]

Thank you @gojoe  for the detailed answer.

I was trying to set up the alert based on the above explanation and trying to select the notification channel as Pub/Sub to get the real time alert and from there user who is execution a query should get an email but it is not working for me when i am executing the query as per the setup threshold but i am not getting any email.

And What is the cost implication of this set up?

[gojoe]

Suggest you start with a very narrow time window to make sure your policy and notification channel are working first then adjust the window to meet your requirements.

If you haven't already seen it, please check out the latency and sample frequency documented here. Note the bottom section with guidance related to alerts.

[shubham21feb]

Hi @gojoe , I tried to implement the above solution but that is not giving the information of user who is executing the query with bytes billed metric. What other metrics or user defined metrics is available that can gives us user email address we can drop an email alert to the user if it crosses the threshold.

[gojoe]

Don't know if/how we can directly notify the user that submitted the query that exceeded an alert threshold from Alerts. A common approach is to configure the alert to take action by notifying a pub/sub channel. You can deploy a Cloud Function that responds to a Pub/Sub trigger. That Cloud Function can do pretty much anything you want. Other channels can take action automatically also (e.g. slack, webhook, email, etc..), each with their own additional setup.

To get the userID, you can write a script as part of the Cloud Function to retrieve data relevant to the notification you want to send to the User. A few options on how to get the user_email, total_bytes_billed, etc... are documented here. INFORMATION_SCHEMAJOBS_by_USER is one option. You can then add more details to help the user take the appropriate action.

One thing to consider is whether the user who submitted the query is the right person to notify and if so, can that person actually do anything meaningful. E.g. if the query came from some BI tool or dashboard, it might be using a proxy user or service account to access BQ so the notification goes nowhere. Even if it gets to the actual user (some tools pass-on the actual user's credentials), if that's an analyst, customer service agent, etc... they might not be able to do anything either. 

I'd start with logging violations and notify users if they do it repeatedly. If the numbers are high, maybe publish them to a dashboard (e.g. using Looker) and ping stakeholders and offenders daily with a link to the dashboard.

[shubham21feb]

Hi @gojoe , I created the eventarc trigger as below and use that trigger in the cloud function to send email to the user that option worked.

gcloud eventarc triggers update bq-audit-trigger-function-5 \

    --location=us-central1 \

    --destination-run-service=function-5 \

--event-filters="type=google.cloud.audit.log.v1.written" \

  --event-filters="serviceName=bigquery.googleapis.com" \

  --event-filters="methodName=jobservice.jobcompleted" \

    --service-account=service_account