Querying BigQuery Table Metadata: Made Easy 101

As a Data Engineer working with tables in BigQuery, you often need to have a high-level overview of their structure. This post discusses an easy approach to pull metadata from your BigQuery tables. The BigQuery Table Metadata helps you determine which tables are useful for analysis using the BigQuery INFORMATION_SCHEMA views. So, read along to gain insights into querying BigQuery Table Metadata.

Table of Contents

• Introduction to Google BigQuery
• Understanding Metadata
• Understanding Importance of Querying BigQuery Table Metadata
• Understanding BigQuery INFORMATION_SCHEMA
• Steps to Query BigQuery Table Metadata Using INFORMATION_SCHEMA
• Limitations of Querying BigQuery Table Metadata Using INFORMATION_SCHEMA
• Conclusion

Introduction to Google BigQuery

If you’re a Developer, Data Analyst, or just about anyone else, you’re probably working with data. As the amount of data grows to Terabytes or even Petabytes, you need a more efficient system like a Data Warehouse. That’s because all that data isn’t useful unless you have a way to analyze it.

BigQuery is Google’s enterprise data warehouse designed to help you ingest, store, analyze, and visualize data with ease. BigQuery was designed to make large-scale data analysis accessible to anyone. It can handle massive amounts of data such as log data from thousands of retail systems or IOT data from millions of vehicle sensors across the globe.

BigQuery involves 3 main components – Storage, Ingestion, and Querying. Data is stored in a structured table which means that you can use the same Structured Query Language (SQL) that you may be familiar with if you have worked with ANSI-compliant databases in the past. 

Understanding Metadata

Metadata! I am sure that you have come across this term in the past and may have probably wondered what it means and why it is important.

Well metadata in terms of a Data Warehouse refers to information on the schema itself such as tables, columns, routines, jobs, usage, streaming details, access control, storage details, physical implementation, and more. 

Metadata can be extremely handy and useful in tracking and monitoring your BigQuery tables. In this article, you learn more about how you can query BigQuery Table Metadata.

Understanding Importance of Querying BigQuery Table Metadata

A BigQuery table is a resource that lives inside a dataset. It contains individual records organized into rows with each row composed of columns or fields that have an enforced data type. Aside from simply storing your data, tables can also be used to store metadata such as descriptions and labels.

Suppose that you are provided with a large BigQuery dataset that has many tables, and you are left wondering which table you should query. Well examining metadata can help you identify the information about your tables such as:

• Which is the largest table in terms of GB?
• Which is the largest table in terms of rows?
• Which is the most updated table?
• When was each table created?
• What columns are present in each table?

When it comes to querying table metadata, BigQuery provides a series of read-only metadata tables called INFORMATION_SCHEMA.

Understanding BigQuery INFORMATION_SCHEMA

INFORMATION_SCHEMA is an ANSI-standard set of read-only views that contain information (metadata) about all of the tables that BigQuery maintains. It contains data about datasets, columns, job execution, resource usage, streaming data, and query performance within a BigQuery dataset. 

For example, the INFORMATION_SCHEMA database can provide information about the name of a dataset or table, the data type of a column, etc., in a user-friendly way. The INFORMATION_SCHEMA tables have the same structure and schema across every BigQuery environment. To read more about it, refer to the INFORMATION_SCHEMA article and documentation.

Let’s now look at how you can query BigQuery metadata using INFORMATION_SCHEMA.

Prerequisites:

To follow along, you will need:

• A Google Cloud Platform account and a Google Cloud project
• Familiarity with the Google Cloud console
• If you want to use the command line to run the examples in this post, you should first:
  • Install the gcloud command-line tool’s latest version.
  • Set a default region and zone.
• To retrieve table metadata by using INFORMATION_SCHEMA tables, you will need to have any of the following Identity and Access Management (IAM) roles that give you the necessary permissions:
  • roles/bigquery.admin
  • roles/bigquery.dataEditor
  • roles/bigquery.dataViewer

Steps to Query BigQuery Table Metadata Using INFORMATION_SCHEMA

There are in total 4 views in INFORMATION_SCHEMA that provide table metadata in BigQuery:

• INFORMATION_SCHEMA.TABLE*
• INFORMATION_SCHEMA.COLUMN*
• INFORMATION_SCHEMA.PARTITIONS
• INFORMATION_SCHEMA.TABLE_SNAPSHOTS

An INFORMATION_SCHEMA view might need to be qualified with a dataset or region.

• Project Qualifier: It restricts results to the specified project.
• Dataset Qualifier: It restricts results to the specified dataset.
• Region Qualifier: It restricts results to the specified region.

To run the queries to extract BigQuery Table Metadata follow the steps below: 

• Go to the GCP Console and open the BigQuery Web User Interface.
• Run the following standard SQL queries in the Query editor box. 

Query 1: To return table metadata using a project and dataset qualifier, you can use the following standard SQL query format.

SELECT * FROM `project`.dataset.INFORMATION_SCHEMA.TABLES;

For example, suppose that you have a project named myProject and a dataset named myDataset, then you can run the following query:

SELECT * FROM `myProject`.myDataset.INFORMATION_SCHEMA.TABLES;

This query returns all of the columns from the specified project and dataset from the INFORMATION_SCHEMA.TABLES view

Query 2: To return metadata for tables in a specific region, you can use a region qualifier represented using a region-REGION syntax. For example: to return metadata for tables in the US region in your default project, run the following standard SQL query:

SELECT * FROM region-us.INFORMATION_SCHEMA.TABLES;

To run the query against a project other than your default project, add the project ID to the dataset as follows:

SELECT * FROM `myProject`.region-eu.INFORMATION_SCHEMA.TABLES;

Query 3: The following query retrieves metadata about the number of columns in a table.

SELECT * FROM `myProject`.myDataset.INFORMATION_SCHEMA.COLUMNS;

Query 4: To retrieve information about the column names and data type of a specific table within a specified dataset, run the following query:

SELECT column_name, data_type
FROM `myProject`.myDataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'myTable'

Query 5: To identify whether there are any partitioned or clustered columns within a table, run the following query in your console:

SELECT * FROM 
`myProject`.myDataset.INFORMATION_SCHEMA.COLUMNS
WHERE 
  is_partitioning_column = 'YES' OR clustering_ordinal_position IS NOT NULL;

Query 6: To get the partition metadata for all the tables in a dataset, run the following query:

SELECT * FROM `myProject`.myDataset.INFORMATION_SCHEMA.PARTITIONS

Query 7: BigQuery table snapshots persist data about the contents of a table (called the base table) at a particular time. To retrieve information (metadata) for the table snapshots in a specified dataset or region, you should query the INFORMATION_SCHEMA.TABLE_SNAPSHOTS table as follows:

SELECT * FROM `myProject`.myDataset.INFORMATION_SCHEMA.TABLE_SNAPSHOTS

The result displays the snapshot table which was taken from the base table myProject.myDataset.myTable.

Limitations of Querying BigQuery Table Metadata Using INFORMATION_SCHEMA

The BigQuery INFORMATION_SCHEMA views have the following limitations:

• INFORMATION_SCHEMA does not support legacy SQL syntax.
• INFORMATION_SCHEMA query results cannot be cached.
• As of the time of writing this guide, INFORMATION_SCHEMA views don’t support DDL statements.

Conclusion

There are many different ways of formulating your BigQuery Table Metadata SQL queries. This article tried to cover all of the most important and most-used BigQuery Table Metadata queries. With the examples that you just covered above, you are sure to have a solid understanding of how to query table metadata with ease in your BigQuery projects.

However, streaming data from various sources to Google BigQuery can be quite challenging and cumbersome. If you are facing these challenges and are looking for some solutions, then check out a simpler alternative like Hevo.

Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 100+ Data Sources including 40+ Free Sources, into your Data Warehouse to be visualized in a BI tool. You can use Hevo Pipelines without writing any code to replicate the data from your desired data sources to your Google BigQuery.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience with BigQuery Table Metadata with us in the comments section below!