------------------------------
Title: Use Log Analytics for BigQuery Usage Analysis on Google Cloud
[content]
On Google Cloud you can use Log Analytics to query and analyze your log data, and then you can view or chart the query results.BigQuery 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 INFORMATION_SCHEMA 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.Upgrade Log bucketFirst, 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:In the navigation panel of the Google Cloud console, select Logging, and then select Logs Storage.Locate the bucket that you want to upgrade.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.Perform BigQuery ActivitiesComplete the following tasks to generate some BigQuery logs. In the tasks, the BigQuery command line tool bq is used.Task 1. Create datasetsUse the bq mk command to create new datasets named bq_logs and bq_logs_test in your project:bq mk bq_logsbq mk bq_logs_testbq mk bq_logs_testTask 2. List the datasetsUse the bq ls command to list the datasets:bq lsTask 3. Delete a datasetUse the bq rm command to delete the a dataset (select y when prompted):bq rm bq_logs_testTask 4. Create a new tablebq mk \ --table \ --expiration 3600 \ --description "This is a test table" \ bq_logs.test_table \ id:STRING,name:STRING,address:STRINGYou 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:bq query — use_legacy_sql=false ‘SELECT current_date’The following query will leverage weather data from the National Oceanic and Atmospheric Administration (NOAA). Copy the query into the BigQuery editor and click RUN.bq query --use_legacy_sql=false \'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.hailFROM `bigquery-public-data.noaa_gsod.gsod2021` gsod2021INNER JOIN `bigquery-public-data.noaa_gsod.stations` stationsON gsod2021.stn = stations.usaf AND gsod2021.wban = stations.wbanWHERE stations.country = "US" AND gsod2021.date = "2021-12-15" AND stations.state IS NOT NULL AND gsod2021.max != 9999.9ORDER BY gsod2021.min;'Perform log analysisNow 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: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: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 = 'cloudaudit.googleapis.com/activity' AND proto_payload.audit_log.method_name LIKE 'datasetservice%'LIMIT 100After run the query, you should see the output like the following:Task 3. To find the activities for BigQuery tablesYou can query the activities that a dataset is created or deleted: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 = 'cloudaudit.googleapis.com/activity' AND proto_payload.audit_log.method_name LIKE '%TableService%'LIMIT 100After run the query, you should see the output like the following:Task 4. To view the queries completed in BigQueryRun the following query: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, severityFROM `[Your Project Id].global._Default._Default`WHERE log_id = "cloudaudit.googleapis.com/data_access" AND proto_payload.audit_log.service_data.jobCompletedEvent IS NOT NULLORDER BY startTimeAfter the query completes, you should see the output like the following: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: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 GCP’s sample GitHub repository.Use Log Analytics for BigQuery Usage Analysis on Google Cloud was originally published in Google Cloud - Community on Medium, where people are continuing the conversation by highlighting and responding to this story.
[/content]

Author: Xiang Shen
PublishedDate: 2024-04-16
Category: Blogs
NewsPaper: Google Cloud - Medium
Tags: log-analytics, google-cloud-platform, observability, data, logs