BigQuery's INFORMATION_SCHEMA.JOBS*
BigQuery provides a suite of views under the INFORMATION_SCHEMA namespace. These views are specifically designed to give users the ability to peek into the metadata of datasets, tables, and other entities without having to access the data itself.
Among the myriad of views within the INFORMATION_SCHEMA, there lies the INFORMATION_SCHEMA.JOBS* view, a specialized collection tailored for capturing the metadata associated with jobs. "Jobs" in BigQuery parlance refers to the queries, data loads, exports, and copies that are executed on the platform. Each of these actions, or "jobs", comes with a rich set of metadata that includes details like the job's type, start and end time, user who initiated it, destination table, and much more.
Accessing Job Metadata and Data Access History
By querying the INFORMATION_SCHEMA.JOBS* view, users can garner insights into various aspects of job execution. Notably:
- Timestamps
Understanding when a particular job was initiated and its duration. - Users
Identifying which user or service account triggered the job. - Queries
Retrieving the exact SQL query that was executed. - Destination Tables
Knowing where the result of a query was stored, especially useful in tracking data flows and dependencies.
Types of INFORMATION_SCHEMA.JOBS* Views
The INFORMATION_SCHEMA.JOBS* views in BigQuery are designed to offer granular insights into the different aspects of jobs. Each of the specific views comes with its unique attributes, catering to diverse needs and use cases.
JOBS
This view is the general-purpose audit trail of every job executed in the current BigQuery project. It encapsulates all the crucial details such as the job's type, SQL used, runtime, and many other metrics.
JOBS_BY_USER
Focusing on user-centric auditing, this view tracks all jobs that have been executed by the currently authenticated user in the project.
JOBS_BY_ORGANIZATION
This view aggregates jobs sent from all projects associated with a given organization.
JOBS_BY_FOLDER
This view provides a consolidated look at jobs executed from projects within a parent folder, aiding in structured data governance and analysis at the folder level.
Ryusei Kakujo
Focusing on data science for mobility
Bench Press 100kg!