INSERT `reby-cloud.bq_logs.bigquery_usage_datepart`
SELECT
timestamp AS Date,
resource.labels.project_id AS ProjectId,
protopayload_auditlog.serviceName AS ServiceName,
protopayload_auditlog.methodName AS MethodName,
protopayload_auditlog.status.code AS ErrorCode,
protopayload_auditlog.status.message AS ErrorMessage,
protopayload_auditlog.authenticationInfo.principalEmail AS UserId,
logName AS JobId,
JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,"$.jobChange.job.jobConfig.queryConfig.query") AS Query,
JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,"$.jobChange.job.jobConfig.queryConfig.destinationTable") AS DestinationTableId,
CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,"$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS INT64) AS BillableBytes,
(CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,"$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS INT64) / 1099511627776) * 6 AS TotalCost,
1 AS QueryCount,
CASE
WHEN JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,"$.jobChange.job.jobConfig.queryConfig.destinationTable") LIKE '%anon%' THEN 'Query'
ELSE 'ETL'
END AS JobType,
ARRAY_TO_STRING(ARRAY(
SELECT
DISTINCT x
FROM
UNNEST(ARRAY_CONCAT(REGEXP_EXTRACT_ALL(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,"$.jobChange.job.jobConfig.queryConfig.query"),r"(?i)\s+(?:FROM|JOIN)\s+([^\s\(]+\.[^\s]+)") ) ) AS x
ORDER BY
x),', ') AS QueryTables,
ARRAY_TO_STRING(ARRAY(
SELECT
DISTINCT x
FROM
UNNEST(ARRAY_CONCAT(REGEXP_EXTRACT_ALL( REGEXP_REPLACE( JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,"$.jobChange.job.jobConfig.queryConfig.query"), r"(?i)\s+(z_+pivot_[a-z0-9_.]+)", ""),r"(?i)\s+(?:WHERE|AND|OR|ON)\s+(?:\s|\(|CAST|`)*([a-z0-9_.]+)(?:AND)?") ) ) AS x
ORDER BY
x),', ') AS QueryWhereColumns
FROM
`reby-cloud.bq_logs.cloudaudit_googleapis_com_data_access`
WHERE
protopayload_auditlog.serviceName = 'bigquery.googleapis.com' and DATE(timestamp) >= '2021-11-28' # Change date to start of gap
and DATE(timestamp) <= DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)
Comments