The schema of a table consists of the names, and types of the columns. We may want to quickly export the schema information when integrating with our application, migrating the data, or changing the schema.
We will look at exporting this data in multiple ways, so you can choose the most convenient for you – or the one that is possible, depending on your access rights.
In this tutorial, we will use the google_analytics_sample table from BigQuery. This sample dataset is very useful for practicing working with user session data of a website.
We can find the ga_sessions table by searching for “analytics sample” in the explorer in BigQuery:
Since 2020, Google has added introspection functionality to BigQuery SQL queries. The data about the columns in a schema is available querying the following table:
In our case: bigquery-public-data.google_analytics_sample.INFORMATION_SCHEMA.COLUMNS
The full query, only showing the columns in our table:
The benefit is that we can now save in many different formats, and quickly manipulate or filter the data using SQL to get it out exactly the way that we want to:
This is the quickest, easiest, and an entirely non-technical solution. At the same time, it’s not as flexible as the other methods mentioned here.
To copy the schema to the keyboard, navigate to the Schema tab of the table information in the GUI. Select all fields, click on the “Copy” button:
Here, we can copy as “Table” (tab-separated lines) or JSON.
We can also do the export with BigQuery’s official CLI tool.
To get general information about the table, we can use the following command:
To get the same in JSON format:
BigQuery is an extremely powerful tool where even something as simple as exporting a table schema can be done in several different ways, depending on the output format desired and other factors.
Please let us know in the comment section below if any of the solutions worked for you, or if you encounter any issues!