Menu
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.
776
A SELECT * EXCEPT statement specifies the names of one or more columns to exclude from the result. All matching column names are omitted from the output. Note: SELECT * EXCEPT does not exclude columns that do not have names.
To select the data from all the columns, rather than listing all the column names separated by a comma, add a star or asterisk syntax, and it is the same SELECT list of columns and add the table name. In BigQuery, SQL statement will allow us to look at the data in a particular table.
The following limits apply when you load data into BigQuery, using the console, the bq command-line tool, or the load-type jobs. insert API method. Load jobs, including failed load jobs, count toward the limit on the number of table operations per day for the destination table.
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 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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
