Google Cloud

Accelerate BigQuery solution development with intelligent log analysis

Many people use BigQuery because its scalable, serverless cloud data warehouse simplifies SQL data analysis. Because BigQuery’s origins are in Google’s Dremel system, log ingestion and analysis is second-nature. In addition to writing application logs directly to BigQuery, you can stream GCP billing data and logs from Stackdriver directly into BigQuery for analysis. In fact, you can even stream your BigQuery logs into a BigQuery dataset so you can analyze your usage.

Recently, our team did an analysis of easily-fixed errors that BigQuery users commonly encounter. We discovered that many users received rateLimitExceeded and quotaExceeded were common, and could be largely avoided with the addition of a bit of monitoring and alerting.

Stackdriver provides monitoring, logging and diagnostics across your hybrid cloud environment.  BigQuery natively integrates with Stackdriver through both logs and time series metrics. You can use Stackdriver to see what BigQuery errors your team is experiencing by creating a logs based metric for the error you want to detect, then set up alerts.  For example, to detect quota exceeded errors, create a logs based metric for BigQuery errors matching “quota exceeded”:

  resource.type="bigquery_resource"
severity>=ERROR
"quota exceeded"
logName="projects/bigquerytestdefault/logs/cloudaudit.googleapis.com%2Fdata_access"

Note that since the logs are in the BigQuery data access logs, you’ll need Private Logs Viewer access to see the matching log entries.

image116h1.PNG

You can optionally create an alert from this metric (Stackdriver supports notifications via email, mobile app, Slack, SMS, PagerDuty, and more) and even include the instructions on how to fix the issue in the documentation field. Since logs based metrics will only count log entries after the metric is created, you may not see any data when you create the alerting policy.

image2bxvt.PNG

You can also create dashboards from these metrics to augment the metrics provided by BigQuery.

image3wz4x.PNG

Next Steps

We hope this blog post gave you a good grasp on how to use Stackdriver to reduce errors using BigQuery. Interested in learning more? Sign up for a free trial and test out Stackdriver and BigQuery yourself.