Editing article

Title

Summary

Content

On Google Cloud you can use <a href="https://cloud.google.com/logging/docs/log-analytics#analytics">Log Analytics</a> to query and analyze your log data, and then you can view or <a href="https://cloud.google.com/logging/docs/analyze/charts">chart the query results</a>.<a href="https://cloud.google.com/bigquery">BigQuery</a> is Google Cloud’s fully managed enterprise data warehouse that helps you manage and analyze your data with built-in features like machine learning, geospatial analysis, and business intelligence.While BigQuery offers built-in observability capabilities like the <a href="https://cloud.google.com/bigquery/docs/information-schema-intro">INFORMATION_SCHEMA</a> views, detailed logging remains crucial for in-depth usage analysis, auditing, and troubleshooting potential issues.This article will walk you through how to analyze BigQuery logs using log analytics.<h3>Upgrade Log bucket</h3>First, if you haven’t, you need to configure Cloud Logging to upgrade all the existing log buckets with Log Analytics enabled.To upgrade an existing bucket to use Log Analytics, do the following:<ol><li>In the navigation panel of the Google Cloud console, select Logging, and then select Logs Storage.</li><li>Locate the bucket that you want to upgrade.</li><li>When the Log Analytics available column displays Upgrade, you can upgrade the log bucket to use Log Analytics. Click Upgrade. A dialog opens. Click Confirm.</li></ol><h3>Perform BigQuery Activities</h3>Complete the following tasks to generate some BigQuery logs. In the tasks, the BigQuery command line tool <a href="https://cloud.google.com/bigquery/docs/reference/bq-cli-reference">bq</a> is used.Task 1. Create datasetsUse the bq mk command to create new datasets named bq_logs and bq_logs_test in your project:<pre>bq mk bq_logs bq mk bq_logs_testbq mk bq_logs_test</pre>Task 2. List the datasetsUse the bq ls command to list the datasets:<pre>bq ls</pre>Task 3. Delete a datasetUse the bq rm command to delete the a dataset (select y when prompted):<pre>bq rm bq_logs_test</pre>Task 4. Create a new table<pre>bq mk \ --table \ --expiration 3600 \ --description &quot;This is a test table&quot; \ bq_logs.test_table \ id:STRING,name:STRING,address:STRING</pre>You should have a new empty table named test_table that has been created for your dataset.Task 5. Run some example queriesYou can run a simple query like the following to generates a log entry. Copy and paste the following query into the BigQuery Query editor:<pre>bq query — use_legacy_sql=false ‘SELECT current_date’</pre>The following query will leverage weather data from the <a href="https://cloud.google.com/blog/products/data-analytics/noaa-datasets-on-google-cloud-for-environmental-exploration">National Oceanic and Atmospheric Administration (NOAA)</a>. Copy the query into the BigQuery editor and click RUN.<pre>bq query --use_legacy_sql=false \ &#39;SELECT gsod2021.date, stations.usaf, stations.wban, stations.name, stations.country, stations.state, stations.lat, stations.lon, stations.elev, gsod2021.temp, gsod2021.max, gsod2021.min, gsod2021.mxpsd, gsod2021.gust, gsod2021.fog, gsod2021.hail FROM `bigquery-public-data.noaa_gsod.gsod2021` gsod2021 INNER JOIN `bigquery-public-data.noaa_gsod.stations` stations ON gsod2021.stn = stations.usaf AND gsod2021.wban = stations.wban WHERE stations.country = &quot;US&quot; AND gsod2021.date = &quot;2021-12-15&quot; AND stations.state IS NOT NULL AND gsod2021.max != 9999.9 ORDER BY gsod2021.min;&#39;</pre><h3>Perform log analysis</h3>Now there are some log entries for BigQuery. You can run some queries using Log Analytics.Task 1. Open Log AnalyticsOn the left side, under Logging click Log Analytics to access the feature. You should see something like the following:<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*6PrYdj0eZePE3wF8" /></figure>If your query field is empty or you forget which table you want to use, you can click the Query button to get the sample query back.Now you can run your own queries in the query field. Remember to replace [Your Project Id] with the project id you are using.Task 2. To find the activities for BigQuery datasetsYou can query the activities that a dataset is created or deleted:<pre>SELECT timestamp, severity, resource.type, proto_payload.audit_log.authentication_info.principal_email, proto_payload.audit_log.method_name, proto_payload.audit_log.resource_name, FROM `[Your Project Id].global._Required._AllLogs` WHERE log_id = &#39;cloudaudit.googleapis.com/activity&#39; AND proto_payload.audit_log.method_name LIKE &#39;datasetservice%&#39; LIMIT 100</pre>After run the query, you should see the output like the following:<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*QgplyJgYAw8L93Rn" /></figure>Task 3. To find the activities for BigQuery tablesYou can query the activities that a dataset is created or deleted:<pre>SELECT timestamp, severity, resource.type, proto_payload.audit_log.authentication_info.principal_email, proto_payload.audit_log.method_name, proto_payload.audit_log.resource_name, FROM `[Your Project Id].global._Required._AllLogs` WHERE log_id = &#39;cloudaudit.googleapis.com/activity&#39; AND proto_payload.audit_log.method_name LIKE &#39;%TableService%&#39; LIMIT 100</pre>After run the query, you should see the output like the following:<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*REyZPbwK41Vfotxk" /></figure>Task 4. To view the queries completed in BigQueryRun the following query:<pre>SELECT timestamp, resource.labels.project_id, proto_payload.audit_log.authentication_info.principal_email, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobConfiguration.query.query) AS query, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobConfiguration.query.statementType) AS statementType, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatus.error.message) AS message, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.startTime) AS startTime, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.endTime) AS endTime, CAST(TIMESTAMP_DIFF( CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.endTime) AS TIMESTAMP), CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.startTime) AS TIMESTAMP), MILLISECOND)/1000 AS INT64) AS run_seconds, CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.totalProcessedBytes) AS INT64) AS totalProcessedBytes, CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.totalSlotMs) AS INT64) AS totalSlotMs, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.referencedTables) AS tables_ref, CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.totalTablesProcessed) AS INT64) AS totalTablesProcessed, CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.queryOutputRowCount) AS INT64) AS queryOutputRowCount, severity FROM `[Your Project Id].global._Default._Default` WHERE log_id = &quot;cloudaudit.googleapis.com/data_access&quot; AND proto_payload.audit_log.service_data.jobCompletedEvent IS NOT NULL ORDER BY startTime</pre>After the query completes, you should see the output like the following:<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*yIhioPqh3mZwXguM" /></figure>Scroll through the results of the executed queries.Task 5. To chart the query resultInstead of using a table to see the results, Log Analytics also supports creating charts for visualization. For example, to view a pie chart for the queries that have run, you can click the Chart button in the result view, select Pie chart as the chart type and query as the column. You should see a chart similar to the following:<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*HoiWqDgj6gqLAp58" /></figure>We’ve only scratched the surface of BigQuery log analysis; you can explore many other queries and charts to enhance your understanding of BigQuery. Feel free to contribute and create samples in <a href="https://github.com/GoogleCloudPlatform/observability-analytics-samples/tree/main/samples/logging">GCP’s sample GitHub repository</a>.<img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8f5454626c6c" width="1" height="1" alt=""><hr><a href="https://medium.com/google-cloud/use-log-analytics-for-bigquery-usage-analysis-on-google-cloud-8f5454626c6c">Use Log Analytics for BigQuery Usage Analysis on Google Cloud</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.

Author

Link

Published date

Image url

Feed url

Guid

Hidden blurb

--- !ruby/object:Feedjira::Parser::RSSEntry
title: Use Log Analytics for BigQuery Usage Analysis on Google Cloud
published: 2024-04-16 00:04:36.000000000 Z
categories:
- log-analytics
- google-cloud-platform
- observability
- data
- logs
url: https://medium.com/google-cloud/use-log-analytics-for-bigquery-usage-analysis-on-google-cloud-8f5454626c6c?source=rss----e52cf94d98af---4
entry_id: !ruby/object:Feedjira::Parser::GloballyUniqueIdentifier
 is_perma_link: 'false'
 guid: https://medium.com/p/8f5454626c6c
carlessian_info:
 news_filer_version: 2
 newspaper: Google Cloud - Medium
 macro_region: Blogs
content: On Google Cloud you can use <a href="https://cloud.google.com/logging/docs/log-analytics#analytics">Log
 Analytics</a> to query and analyze your log data, and then you can view or <a href="https://cloud.google.com/logging/docs/analyze/charts">chart
 the query results</a>.<a href="https://cloud.google.com/bigquery">BigQuery</a>
 is Google Cloud’s fully managed enterprise data warehouse that helps you manage
 and analyze your data with built-in features like machine learning, geospatial analysis,
 and business intelligence.While BigQuery offers built-in observability capabilities
 like the <a href="https://cloud.google.com/bigquery/docs/information-schema-intro">INFORMATION_SCHEMA</a>
 views, detailed logging remains crucial for in-depth usage analysis, auditing, and
 troubleshooting potential issues.This article will walk you through how to
 analyze BigQuery logs using log analytics.<h3>Upgrade Log bucket</h3>First,
 if you haven’t, you need to configure Cloud Logging to upgrade all the existing
 log buckets with Log Analytics enabled.To upgrade an existing bucket to use
 Log Analytics, do the following:<ol><li>In the navigation panel of the Google
 Cloud console, select Logging, and then select Logs Storage.</li><li>Locate
 the bucket that you want to upgrade.</li><li>When the Log Analytics available
 column displays Upgrade, you can upgrade the log bucket to use
 Log Analytics. Click Upgrade. A dialog opens. Click Confirm.</li></ol><h3>Perform
 BigQuery Activities</h3>Complete the following tasks to generate some BigQuery
 logs. In the tasks, the BigQuery command line tool <a href="https://cloud.google.com/bigquery/docs/reference/bq-cli-reference">bq</a>
 is used.Task 1. Create datasetsUse the bq
 mk command to create new datasets named bq_logs and bq_logs_test
 in your project:<pre>bq mk bq_logs bq mk bq_logs_testbq mk bq_logs_test</pre>Task
 2. List the datasetsUse the bq ls command to list
 the datasets:<pre>bq ls</pre>Task 3. Delete a datasetUse
 the bq rm command to delete the a dataset (select y
 when prompted):<pre>bq rm bq_logs_test</pre>Task 4. Create a new table<pre>bq
 mk \ --table \ --expiration 3600 \ --description &quot;This is a test
 table&quot; \ bq_logs.test_table \ id:STRING,name:STRING,address:STRING</pre>You
 should have a new empty table named test_table that has been created
 for your dataset.Task 5. Run some example queriesYou
 can run a simple query like the following to generates a log entry. Copy and paste
 the following query into the BigQuery Query editor:<pre>bq query — use_legacy_sql=false
 ‘SELECT current_date’</pre>The following query will leverage weather data from
 the <a href="https://cloud.google.com/blog/products/data-analytics/noaa-datasets-on-google-cloud-for-environmental-exploration">National
 Oceanic and Atmospheric Administration (NOAA)</a>. Copy the query into the BigQuery
 editor and click RUN.<pre>bq query --use_legacy_sql=false \ &#39;SELECT 
 gsod2021.date, stations.usaf, stations.wban, stations.name, stations.country, 
 stations.state, stations.lat, stations.lon, stations.elev, gsod2021.temp, 
 gsod2021.max, gsod2021.min, gsod2021.mxpsd, gsod2021.gust, gsod2021.fog, 
 gsod2021.hail FROM `bigquery-public-data.noaa_gsod.gsod2021` gsod2021 INNER
 JOIN `bigquery-public-data.noaa_gsod.stations` stations ON gsod2021.stn
 = stations.usaf AND gsod2021.wban = stations.wban WHERE stations.country
 = &quot;US&quot; AND gsod2021.date = &quot;2021-12-15&quot; AND stations.state
 IS NOT NULL AND gsod2021.max != 9999.9 ORDER BY gsod2021.min;&#39;</pre><h3>Perform
 log analysis</h3>Now there are some log entries for BigQuery. You can run some
 queries using Log Analytics.Task 1. Open Log AnalyticsOn
 the left side, under Logging click Log Analytics
 to access the feature. You should see something like the following:<figure><img
 alt="" src="https://cdn-images-1.medium.com/max/1024/0*6PrYdj0eZePE3wF8" /></figure>If
 your query field is empty or you forget which table you want to use, you can click
 the Query button to get the sample query back.Now you can
 run your own queries in the query field. Remember to replace [Your Project
 Id] with the project id you are using.Task 2. To find the
 activities for BigQuery datasetsYou can query the activities that
 a dataset is created or deleted:<pre>SELECT timestamp, severity, 
 resource.type, proto_payload.audit_log.authentication_info.principal_email, 
 proto_payload.audit_log.method_name, proto_payload.audit_log.resource_name, FROM 
 `[Your Project Id].global._Required._AllLogs` WHERE log_id = &#39;cloudaudit.googleapis.com/activity&#39; 
 AND proto_payload.audit_log.method_name LIKE &#39;datasetservice%&#39; LIMIT 
 100</pre>After run the query, you should see the output like the following:<figure><img
 alt="" src="https://cdn-images-1.medium.com/max/1024/0*QgplyJgYAw8L93Rn" /></figure>Task
 3. To find the activities for BigQuery tablesYou can query the activities
 that a dataset is created or deleted:<pre>SELECT timestamp, severity, 
 resource.type, proto_payload.audit_log.authentication_info.principal_email, 
 proto_payload.audit_log.method_name, proto_payload.audit_log.resource_name, FROM 
 `[Your Project Id].global._Required._AllLogs` WHERE log_id = &#39;cloudaudit.googleapis.com/activity&#39; 
 AND proto_payload.audit_log.method_name LIKE &#39;%TableService%&#39; LIMIT 
 100</pre>After run the query, you should see the output like the following:<figure><img
 alt="" src="https://cdn-images-1.medium.com/max/1024/0*REyZPbwK41Vfotxk" /></figure>Task
 4. To view the queries completed in BigQueryRun the following query:<pre>SELECT 
 timestamp, resource.labels.project_id, proto_payload.audit_log.authentication_info.principal_email, 
 JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobConfiguration.query.query)
 AS query, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobConfiguration.query.statementType)
 AS statementType, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatus.error.message)
 AS message, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.startTime)
 AS startTime, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.endTime)
 AS endTime, CAST(TIMESTAMP_DIFF( CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.endTime)
 AS TIMESTAMP), CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.startTime)
 AS TIMESTAMP), MILLISECOND)/1000 AS INT64) AS run_seconds, CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.totalProcessedBytes)
 AS INT64) AS totalProcessedBytes, CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.totalSlotMs)
 AS INT64) AS totalSlotMs, JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.referencedTables)
 AS tables_ref, CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.totalTablesProcessed)
 AS INT64) AS totalTablesProcessed, CAST(JSON_VALUE(proto_payload.audit_log.service_data.jobCompletedEvent.job.jobStatistics.queryOutputRowCount)
 AS INT64) AS queryOutputRowCount, severity FROM `[Your Project Id].global._Default._Default` WHERE 
 log_id = &quot;cloudaudit.googleapis.com/data_access&quot; AND proto_payload.audit_log.service_data.jobCompletedEvent
 IS NOT NULL ORDER BY startTime</pre>After the query completes, you should
 see the output like the following:<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*yIhioPqh3mZwXguM"
 /></figure>Scroll through the results of the executed queries.Task
 5. To chart the query resultInstead of using a table to see the
 results, Log Analytics also supports creating charts for visualization. For example,
 to view a pie chart for the queries that have run, you can click the Chart
 button in the result view, select Pie chart as the chart type and
 query as the column. You should see a chart similar to the following:<figure><img
 alt="" src="https://cdn-images-1.medium.com/max/1024/0*HoiWqDgj6gqLAp58" /></figure>We’ve
 only scratched the surface of BigQuery log analysis; you can explore many other
 queries and charts to enhance your understanding of BigQuery. Feel free to contribute
 and create samples in <a href="https://github.com/GoogleCloudPlatform/observability-analytics-samples/tree/main/samples/logging">GCP’s
 sample GitHub repository</a>.<img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8f5454626c6c"
 width="1" height="1" alt=""><hr><a href="https://medium.com/google-cloud/use-log-analytics-for-bigquery-usage-analysis-on-google-cloud-8f5454626c6c">Use
 Log Analytics for BigQuery Usage Analysis on Google Cloud</a> was originally published
 in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium,
 where people are continuing the conversation by highlighting and responding to this
 story.
rss_fields:
- title
- published
- categories
- url
- entry_id
- content
- author
author: Xiang Shen

Language

Active

Ricc internal notes

Ricc source

Show this article Back to articles