Selecting Specific Columns in Google BigQuery | Dataform
Data Warehouse Guide

Selecting Specific Columns in Google BigQuery

Learn how to select all columns, except some, within Bigquery

Data Warehouse

When working with your data, it's sometimes easy to forget that you don't need to work with all _of it, _all of the time.

You've probably encountered situations where you have specific fields in your tables that are common across them, but they're not relevant to your query, so why include them?

For example, you could have a department table with columns deptno, deptname and location, but you could exclude location as it's not relevant to your analysis.

So, how do you do this in BigQuery? (and why should you?)

Your Data & BigQuery

Understanding how your data is represented in BigQuery can help you to improve the quality, performance, and costs associated with your queries.

When you load your data into BigQuery, each column is stored separately. The values within each column are then compressed, and the corresponding data file is replicated, before being stored in an underlying distributed filesystem.

This process and representation of your data is why BigQuery doesn't support indices, and why, crucially, each column you pull in increases the cost. Each additional column requires access to a different file (one of the replications) in the underlying filesystem.

With traditional databases, an index can often be the right solution. When using BigQuery however, as the size of your data increases more servers are utilised to keep performance at a consistent level. Due to this, you should expect your costs to increase linearly, while performance stays the same without the need for indexes.

This makes it quite different from traditional databases but makes sense when you realise that each column's data is essentially stored separately.

Using SELECT * EXCEPT (x,y,z)

Since the introduction of standard SQL support to BigQuery, you can now select specific columns, by excluding specified columns from your SELECT statements. Here’s an example showing how you can exclude a few fields from your query using the EXCEPT function:

select * except(title, comment) from publicdata.samples.wikipedia limit 10

It's as simple as that!

Next Steps

Hungry to learn more about BigQuery?

Check-out our guides on how to export usage logs, build a machine learning pipeline or send data to Intercom.

Or, if you'd like to learn more about us, check out our documentation and sign up for a free account today!

More content from Dataform

Dataform is joining Google Cloud

Announcement

Dataform is joining Google Cloud

Today we're excited to announce that we've joined Google Cloud. Over the course of the past several months, our partnership with Google Cloud has deepened and we believe that our new combined efforts can make our customers and partners even more successful.
Learn more
Using Javascript to Create an Acquisition Funnel Dataset illustration
Tutorial

Using Javascript to Create an Acquisition Funnel Dataset

How to use a combination of SQL and Javascript to create an acquisition funnel dataset.
Learn more
How to add a new warehouse adapter to Dataform illustration
Guide

How to add a new warehouse adapter to Dataform

I recently wrote a Presto adapter for Dataform during a week long quarterly hackathon. Here's a guide on how I'd do it again.
Learn more

Learn more about the Dataform data modeling platform

Dataform brings open source tooling, best practices, and software engineering inspired workflows to advanced data teams that are seeking to scale, enabling you to deliver reliable data to your entire organization.