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)