CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
wubr2000
wubr2000

Reputation: 965

Select All Columns Except Some in Google BigQuery?

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.

Upvotes: 77

Views: 166594

Answers (5)

MMM
MMM

Reputation: 11

Yes, For example, Let's say you don't want a column from the table in CTE, then you can use a similar command.

with cte_table as ( SELECT * FROM 
        table1)
select  b.*, a.* EXCEPT (column_name(s))  from cte_table as a
        left join
        table2 as b
        on a.col1 = b.col1

Upvotes: 0

Tunneller
Tunneller

Reputation: 589

With regard @Markus' comment, not sure if this is exactly his request, but the functionality to drop duplicated column names from both tables does in fact seem to be working, e.g.

WITH
  CTE1 AS (
   SELECT TIME, "StringA" As Table_ID, Timestamp_Local , temperature_flow 
   FROM `yourtable1`
  ) ,
  CTE2 AS (
    SELECT  TIME, "StringB" As Table_ID, Timestamp_Local, vpp_average 
    FROM `yourtable2`
  ) 
SELECT * EXCEPT (Table_ID, Timestamp_Local ), 
   COALESCE(CTE1.Table_ID, CTE2.Table_ID) As M_Table_ID,
   COALESCE(CTE1.Timestamp_Local, CTE2.Timestamp_Local) As M_Timestamp  FROM
CTE1 FULL OUTER JOIN CTE2
USING(TIME) ORDER BY TIME DESC

Upvotes: 0

Markus
Markus

Reputation: 2455

Use case: Joining

As pointed out by the previous posts it is now possible to exclude columns from queries using the SELECT * EXCEPT()-syntax.
Anyhow, the feature seems not entirely thought through as one of the crucial use cases to require such functionality is to get rid of duplicate key-columns in joining while keeping one instance of the key-column.

In other words: When joining table_2 on table_1 I want to keep the column used for joining only from table_1.

Especially for large tables this can be tedious if all columns except the desired are needed.
E.g., what currently fails and would be desired:

SELECT *
    EXCEPT(table_2.primary_key_column) -- -> Only exclude key-column from joined table
FROM table_1 AS table_1
LEFT JOIN table_2 AS table_2 USING(primary_key_column)

This fails with a syntax error as the table_2. is not allowed in the EXCEPT()-clause...

Workaround

While EXCEPT() excludes columns with the specified names from the "input"-tables it does not affect expressions within the select statement. In other words: If you specify the required column specifically from the respective table it will not be affected by EXCEPT().

Thus, this allows us to work around our issue like in the example below.

SELECT
    table_1.primary_key_column AS primary_key_column -- -> Not affected
    , * 
    EXCEPT(primary_key_column)
FROM table_1 AS table_1
LEFT JOIN table_2 AS table_2 USING(primary_key_column)

What is still not nice about this solution: Your column order is affected if your key-column is not in first place.

I hope it will get "fixed" in the future as it is not really user friendly and also does not seem very stringent.

Note: If you have more than one join you have to adjust the logic (as EXCEPT() only refers to the * in front of it), which also shows a solution closer to the shown use case issue:

SELECT
    table_1.* --> Includes primary_key_column
    , table_2.* EXCEPT(primary_key_column) --> Specifically excludes primary key from table_2
    , table_3.* EXCEPT(primary_key_column) --> Specifically excludes primary key from table_3
FROM table_1 AS table_1
LEFT JOIN table_2 AS table_2 USING(primary_key_column)
LEFT JOIN table_3 AS table_3 USING(primary_key_column)

Upvotes: -1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

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;

Upvotes: 14

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

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

Upvotes: 161

Related Questions