Bigquery error: CLUSTER BY expression must be groupable, but type is STRUCT at [x:y]

When creating a table in Bigquery you might stumble on this error. Look at the below example query:

#standardSQL
CREATE TABLE mydataset.newtable 
(
transaction_date DATE,
City string,
Branch Int64,
Customer_Name string,
)
PARTITION BY transaction_date
CLUSTER BY (City, Branch);

Solution:

Simply remove the Parenthesis in the cluster by clause. Bigquery mistakenly thinks of it as a STRUCT. The new DDL would be 

#standardSQL
CREATE TABLE mydataset.newtable 
(
transaction_date DATE,
City string,
Branch Int64,
Customer_Name string,
)
PARTITION BY transaction_date
CLUSTER BY City, Branch;

nVector

posted on 16 Sep 20