CODEWITHSUNDEEP
google-bigquery
Phuc Trinh
Phuc Trinh

Reputation: 455

BIG QUERY Error: Partitioning by expressions of type FLOAT64 is not allowed at [5:70]

I am facing a issue. Basically, i want to delete all duplicate rows, i did it successfully in a few tables. But there is a table that contains FLOAT column which is REVENUE. It show this error:

Error: Partitioning by expressions of type FLOAT64 is not allowed at [5:70] Here are my codes:

CREATE OR REPLACE TABLE `pops-204909.monthly_reports.top_20_countries_revenue` AS
SELECT * EXCEPT(rn)
FROM 
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY CMS_ID, DATE,COUNTRY_NAME,REVENUE ORDER BY DATE  ) rn
  FROM `pops-204909.monthly_reports.top_20_countries_revenue`
)
WHERE rn = 1

Upvotes: 7

Views: 7754

Answers (1)

Pentium10
Pentium10

Reputation: 207828

If you are 100% sure that you actually need REVENUE in the columns for partition, you need to convert into a string.

Usually this is a mistake, and partitioning only by CMS_ID should be fine.

CAST(REVENUE as STRING)

Upvotes: 5

Related Questions