Select all columns, but replace some with expression in Google BigQuery?

5
votes

Similar to Select All Columns Except Some in Google BigQuery? - we want to SELECT * from the table, but instead of excluding some columns, we want to replace them with some expression. For example, given table with columns: name, start_date, amount, end_date, comment, we want to convert start and end from STRING to DATE. It is possible to write 

SELECT 
  * EXCEPT(start_date, end_date),
  CAST(start_date AS DATE) start_date,
  CAST(end_date AS DATE) end_date

But this would change order of columns moving start and end to the end.

2
google-bigquery

2 Answers

6
votes

In addition to SELECT * EXCEPT, Google BigQuery also supports SELECT * REPLACE clause in Standard SQL dialect. Documentation can be found here: https://cloud.google.com/bigquery/sql-reference/query-syntax#select-list Your example will become:

SELECT * REPLACE(
  CAST(start_date AS DATE) AS start_date,
  CAST(end_date AS DATE) AS end_date)
FROM T
4
votes

I would go further and say - you can chain EXCEPT and REPLACE in same SELECT
It is not that obvious from documentation, so I thought it valuable

For example 

SELECT * EXCEPT(end_date) REPLACE(
  CAST(start_date AS DATE) AS start_date
FROM T

This will remove end_date from output end replace original start_date with casted to date start_date