Slide 30
Slide 30 text
ɹαϯϓϧSQL
WITH tables AS (
SELECT table_id
FROM `{project_id}.{dataset_name}`.__TABLES__
WHERE table_id NOT LIKE ‘LOAD_TEMP_%' AND table_id NOT LIKE ‘TMP_%'
),
log AS (
SELECT
REGEXP_REPLACE(data.resource, ‘projects/{project_id}/datasets/{dataset_name}/tables/‘, '') AS table,
protopayload_auditlog.authenticationInfo.principalEmail AS user,
DATE(timestamp) AS day
FROM
`{project_id}.{source__cloudaudit__bigquery}.cloudaudit_googleapis_com_data_access_*`,
UNNEST(protopayload_auditlog.authorizationInfo) AS data
WHERE data.permission = 'bigquery.tables.getData'
),
calc AS (
SELECT table, day, COUNT(*) AS PV, COUNT(DISTINCT user) AS UU
FROM log
WHERE table NOT LIKE ‘LOAD_TEMP_%' AND table != ‘__TABLES__' AND table NOT LIKE 'TMP_%'
GROUP BY 1, 2
)
SELECT tables.table_id, calc.PV, calc.UU, REGEXP_REPLACE(CAST(calc.day AS STRING), '-', '') AS day
FROM tables
LEFT JOIN calc
ON tables.table_id = calc.table