Script to identify when was the table last accessed based on `INFORMATION_SCHEMA.JOBS`

[pratik-m]

BigQuery Table Access and Usage Analysis Script This script analyzes BigQuery job history to determine when tables were last accessed and provides insights into Data Manipulation Language (DML) and Data Definition Language (DDL) operations performed on those tables. It helps identify:

• The last time a table was referenced in a query (last accessed time).
• The frequency of SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and other DDL/DML operations. This information is valuable for:
• Identifying unused tables for potential deletion to optimize storage costs.
• Understanding table usage patterns for performance tuning and resource allocation.

Please share your feedback and suggestion!

[google-cla[bot]]

Thanks for your pull request! It looks like this may be your first contribution to a Google open source project. Before we can look at your pull request, you'll need to sign a Contributor License Agreement (CLA).

View this failed invocation of the CLA check for more information.

For the most up to date status, view the checks section at the bottom of the pull request.

[afleisc]

Hi @pratik-m thanks for the PR! A couple of things,

• Please sign the google CLA in the above comment
• Looks like there are some syntax errors around the temp table statement, can you please resolve any syntax errors ?
• This script appears to be more of an audit use case, do you mind creating a folder called audit under scripts and placing this sql file there?

[pratik-m]

Hi @afleisc - Thanks for the comments. I'll review and fix the script and move it to the audit folder. thanks!

[pratik-m]

Hi @afleisc - I';ve done the requested changes. Please review and let me know if any suggestions or feedback!

[pratik-m]

Hello @afleisc @danieldeleo - any other feedback or suggestion?