Motivation
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.
Open a dataset
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:
Method 1: Using an SQL query with introspection
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:
DATASET_ID.INFORMATION_SCHEMA.COLUMNS
In our case: bigquery-public-data.google_analytics_sample.INFORMATION_SCHEMA.COLUMNS
The full query, only showing the columns in our table:
SELECT * FROM `bigquery-public-data.google_analytics_sample.INFORMATION_SCHEMA.COLUMNS` WHERE table_name = ‘ga_sessions_20170127’
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:
Method 2: Using the GUI to copy to the clipboard
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:
Image:
Here, we can copy as “Table” (tab-separated lines) or JSON.
Method 3: Using the bq command-line tool
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:
bq show bigquery-public-data:google_analytics_sample.ga_sessions_20170127
To get the same in JSON format:
bq show –format=prettyjson bigquery-public-data:google_analytics_sample.ga_sessions_20170127
Wrap up
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!
