Google Cloud: GIG

GCL-jobCompleted-sink.sql

CREATE OR REPLACE VIEW bq_logs.v_querylogs AS
WITH job_completed_event AS (
  SELECT
    resource.labels.project_id,
    protopayload_auditlog.authenticationInfo.principalEmail,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job AS job,
    severity
  FROM `bq_logs.cloudaudit_googleapis_com_data_access_*`
)
SELECT
  project_id,
  principalEmail,
  job.jobConfiguration.query.query,
  job.jobConfiguration.query.statementType,
  job.jobStatus.error.message,
  job.jobStatistics.startTime,
  job.jobStatistics.endTime,
  TIMESTAMP_DIFF(job.jobStatistics.endTime, job.jobStatistics.startTime, MILLISECOND)/1000 AS run_seconds,
  job.jobStatistics.totalProcessedBytes,
  job.jobStatistics.totalSlotMs,
  ARRAY_TO_STRING(ARRAY(
    SELECT CONCAT(datasetid, '.', tableId)
    FROM UNNEST(job.jobStatistics.referencedTables)
  ), ',') AS tables_ref,
  job.jobStatistics.totalTablesProcessed,
  job.jobStatistics.queryOutputRowCount,
  severity
FROM job_completed_event
ORDER BY job.jobStatistics.startTime;

ref.

• Using BigQuery and Cloud Logging to Analyze BigQuery Usage

  • https://www.cloudskillsboost.google/paths/430/course_templates/641/labs/464870
  • https://www.cloudskillsboost.google/focuses/6100?parent=catalog
  • the query is altered from the above.