Select All Columns Except Some in Google BigQuery?

52
votes

Is there a way to Select * except [x,y,z column names] in BigQuery? I see some solutions for MySQL but not sure if it applies to BQ.

Thank you.

2
google-bigquery
the only answer I have is kind of extension of approach in stackoverflow.com/questions/34010002/… which looks like not what works for you - Mikhail Berlyant

2 Answers

106
votes

There is nothing in current BigQuery SQL dialect that will allow it. But since this is recurring request, we have added work item to support

SELECT * EXCEPT (a, b, c) FROM ...

Update: This functionality is now available in BigQuery standard SQL. Details at https://cloud.google.com/bigquery/sql-reference/enabling-standard-sql Example using public wikipedia table - select all columns except title and comment:

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

In addition to SELECT * EXCEPT() syntax there is a SELECT * REPLACE() syntax - both supported with Standard SQL introduced
Usage is simple and obvious as per documentation

What is less obvious is that you can use both together in the same SELECT, like in example below 

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * EXCEPT (order_id) REPLACE ("widget" AS item_name), "more" as more_fields
FROM orders;