17.7. External Tables in BigQuery

In this lesson, we'll explore how to create an external table in BigQuery that reads data directly from a Google Sheet.

Connecting Google Sheets with BigQuery

In the previous lesson, we learned how to birng a Google Sheet into BigQuery but BigQuery can also read data directly from a Google Sheet without having to download it. This is because both products are part of the Google ecosystem, making it easy to integrate them. By connecting the sheet as an external table, we can ensure that any changes made in the sheet will be reflected in our queries.

First, copy the URL of your Google Sheet as you'll need it later. Then, follow these steps:

1. Click on the ellipses (three dots) next to your dataset.
2. Select "Create table".
3. Choose "Google Drive" as the source of your data.
4. Paste the URL of your Google Sheet.
5. Select "Google Sheets" as the file format.
6. Define your sheet's range (e.g., GSC_data!A1:D).

Defining Destination and Schema

After defining your source, you'll need to specify your destination and schema:

1. Choose the same project and dataset as before.
2. Name your table (e.g., "Google_Search_Console").
3. Set the table type to "External Table".
4. Allow BigQuery to auto-detect the schema by selecting "Auto detect".

Before creating the table, ensure that you've set up header skipping correctly by specifying how many rows should be skipped (usually one row for headers). If not done correctly, field names may not be properly detected.

Deleting and Recreating Tables

If you've made a mistake while creating your external table, you can delete it and create another one:

1. Click on the ellipses next to your table.
2. Select "Delete".
3. Type "DELETE" when prompted.

Remember that deleting a table is a permanent action and cannot be undone.

Working with External Tables

Once your external table is created, you'll notice some differences compared to native tables:

• The "Details" tab will show zero bytes, as the data is still stored in the Google Sheet, not in BigQuery.
• There's no "Preview" tab available for external tables.

To access the data within an external table, you need to run a query. Keep in mind that every time you want to view the data, you'll have to query it since BigQuery doesn't store the actual data from Google Sheets.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn