`{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
BQ query = f""" SELECT COUNT(*) AS row_count FROM `xxxxx.xxxxx.xxxxx` WHERE CAST(created AS DATE) = DATE(‘2019-01-01') """ result_bq = pd.read_gbq(query, project_id=‘xxxxx', dialect='standard') # MySQL con = mysql.connector.connect(host = ‘x.x.x.x’, port = xxxxx, user = ‘xxxxx’, password = 'xxxxx', database = 'xxxxx') read_sql = f""" SELECT COUNT(*) AS row_count FROM `xxxxx` WHERE created BETWEEN '2019-01-01 00:00:00' AND '2019-01-01 23:59:59' """ result_mysql = psql.read_sql(read_sql, con) result_bq.equals(result_mysql)