Select All Columns Except Some in Google BigQuery?

Solution 1:

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

Solution 2:

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;