Googl Cloud Statistic

[BeaWeso]

Hi,

I would like to know number of select queries for all tables or views from the particular project, which was sucessfully run in the last 12 month.

Where I  can I get this information from? In which metadata this infomation is stored?

Can anybody help me?

thank you, 

Bea

[caryna]

Hi @BeaWeso,

Welcome to the Google Cloud Community!

You can do this through looking at INFORMATION_SCHEMA and Cloud Audit Logs. The only challenge is the retention of data.

1. INFORMATION_SCHEMA.JOBS_TIMELINE can provide granular information about query execution. 

Key columns to consider are job_creation_time,job_type,and statement_type. 

There is no specific column indicating whether a job was "successful," you can use the state column to check if the job is PENDING, RUNNING, or DONE. Additionally, you may include other column names based on your needs.

Below is an example SQL query:

SELECT
  COUNT(*) AS totalNumber_select_queries
FROM
  `PROJECT_ID.region-REGION_NAME.INFORMATION_SCHEMA.JOBS_TIMELINE`
WHERE
  job_creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL #set DAY)
  AND state = 'DONE'
  AND job_type = 'QUERY'
  AND statement_type = 'SELECT'

You can check this function under SQL BigQuery for more information.

Important Notes on INFORMATION_SCHEMA: 

• You must have the required permissions to view the timeline.
• Data retention can only last 180 days (6 months).
• Consider also the potential costs that may be incurred.

2. Cloud Audit Logs - Cloud Audit Logs can provide valuable insights into BigQuery queries but do not specify the statement type for SELECT queries.

Important Notes on Cloud Audit Logs: 

• Ensure you have the appropriate permissions to view the logs.
• Be mindful of data retention policies and associated costs.

I hope the above information is helpful. 

[BeaWeso]

Thank you very much Caryna