BigQuery SELECT * EXCEPT and REPLACE: Modify Columns Without Listing All of Them

SELECT * is not a good practice in production, but I still use it for spot checks, when debugging, analyzing or validating data - especially when working with wide tables.

Here are two useful clauses to keep in mind when working with SELECT * in BigQuery: EXCEPT and REPLACE.

➡ EXCEPT will exclude one or more columns from the output.
➡ REPLACE will swap the enumerated columns with the new definitions you provide.

Also, since SELECT DISTINCT * won't work when you have a STRUCT column, you can use EXCEPT to exclude struct columns.

Let's look at an example. Say we'd like to select all the columns but exclude the Salary and modify the CustomerId.

Here's how the code would look:

SELECT * 

EXCEPT(Salary) 

REPLACE(CONCAT('SystemA','-',CustomerId) AS CustomerId) 

FROM `learning.Customers`

This would produce the following output!

Thanks for reading!

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

Using SELECT * with EXCEPT and REPLACE

Comments

Practical SQL

More from this blog

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

Command Palette

Comments

Practical SQL

More from this blog