In BigQuery, INFORMATION_SCHEMA views provide metadata about datasets, tables, columns, and other database objects within a project. These views are useful for querying system-related information, such as table sizes, schema details, jobs, and user privileges.
Here are the main types of INFORMATION_SCHEMA in BigQuery:
1. Dataset-Level INFORMATION_SCHEMA Views
These views provide information about all the objects within a specific dataset.
• INFORMATION_SCHEMA.TABLES: Contains metadata about all tables and views in a dataset.
• Example: Get the size and creation time of tables.
• Columns include table_name, table_type, creation_time, size_bytes.
• INFORMATION_SCHEMA.COLUMNS: Contains metadata about columns in the tables and views within a dataset.
• Example: List column names and data types.
• Columns include table_name, column_name, data_type, is_nullable.
• INFORMATION_SCHEMA.ROUTINES: Provides details about user-defined functions (UDFs) and stored procedures.
• Example: Get the definition of stored routines.
• Columns include routine_name, routine_type, routine_body.
• INFORMATION_SCHEMA.PARTITIONS: Shows information about table partitions.
• Example: List the partitions of a partitioned table.
• Columns include table_name, partition_id, partition_creation_time.
• INFORMATION_SCHEMA.JOBS: Provides information about all jobs (queries, loads, etc.) run within a dataset.
• Example: View recent query jobs and their statuses.
• Columns include job_id, user_email, job_type, creation_time, state.
• INFORMATION_SCHEMA.TABLE_OPTIONS: Lists the table options such as expiration times for each table in a dataset.
• Example: Check if tables have expiration times set.
• Columns include table_name, option_name, option_value.
2. Project-Level INFORMATION_SCHEMA Views
These views provide metadata across the entire project.
• INFORMATION_SCHEMA.SCHEMATA: Lists all datasets in a project.
• Example: Get all dataset names in a project.
• Columns include schema_name (dataset name), location.
• INFORMATION_SCHEMA.JOBS_BY_PROJECT: Provides details on all jobs across the entire project.
• Example: Track the status of jobs like queries, loads, and exports.
• Columns include job_id, user_email, job_type, creation_time, state.
• INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT: Offers a more detailed timeline of job execution across the project.
• Example: Analyze when jobs start, run, and complete.
• Columns include job_id, job_duration, bytes_processed.
3. Region-Specific INFORMATION_SCHEMA Views
These views are similar to project-level views but are scoped to a specific region within a project. They are useful for analyzing jobs or datasets based on their geographic location.
• INFORMATION_SCHEMA.JOBS_BY_REGION: Lists all jobs in a project for a specific region.
• Example: Monitor job activity for a specific region.
• Columns include job_id, location, job_type, state.
• INFORMATION_SCHEMA.JOBS_TIMELINE_BY_REGION: Shows detailed job timelines within a specific region.
• Example: Analyze the performance and duration of jobs region-wise.
• Columns include job_id, job_duration, bytes_processed.
4. Reservation INFORMATION_SCHEMA Views
These views provide information about slots and reservations, useful for managing BigQuery slots and resource reservations.
• INFORMATION_SCHEMA.RESERVATIONS: Lists information about all slot reservations in a project.
• Example: List all reservations and their usage.
• Columns include reservation_name, slot_count.
• INFORMATION_SCHEMA.ASSIGNMENTS: Shows assignments of reservations to projects.
• Example: Find out how slot reservations are assigned to different projects.
• Columns include project_id, reservation_name.
5. Other Special Information Schema Views
These views provide metadata about specialized objects like policies or capacity commitments.
• INFORMATION_SCHEMA.POLICY_TAGS: Lists policy tags (for data governance and classification) for tables in a dataset.
• Example: List all policy tags attached to columns for data classification.
• Columns include policy_tag, column_name, table_name.
• INFORMATION_SCHEMA.CAPACITY_COMMITMENTS: Provides details about capacity commitments for slots in a project.
• Example: Check the slot capacity commitments for a project.
• Columns include commitment_id, slot_count, plan.
Usage of INFORMATION_SCHEMA Views
• Query Dataset Metadata: Use INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.COLUMNS to get information about the structure of datasets and tables.
• Track Jobs: Use INFORMATION_SCHEMA.JOBS to