CODEWITHSUNDEEP
google-bigquery
DimJim
DimJim

Reputation: 13

"CLUSTER BY expression must be groupable, but type is STRUCT" error

I've created a table using the web UI as:

create table `project.dataset.test3` as (select 123 as id, 456 as offer_id)

I want to create a clustered table from the previously created table so I try:

create table `project.dataset.test4` partition by (fake_date) 
cluster by (id, offer_id) as (
SELECT current_timestamp() fake_date, id, offer_id
FROM `project.dataset.test3`
group by 1,2,3)

But I get the error message:

CLUSTER BY expression must be groupable, but type is STRUCT at [2:12]

I searched the documentation but was unable to get any insight into this error message or formulate any way around it. Any ideas?

Upvotes: 0

Views: 1169

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

Putting parentheses around two or more comma-separated expressions creates a struct, and in the statement you provided, you have:

cluster by (id, offer_id)

If you remove the parentheses, the statement should succeed:

cluster by id, offer_id

Upvotes: 1

Related Questions