16 Sep 20 · npack · #Bigquery ·   Bookmark   ×

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;

npack

posted on 16 Sep 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds