Solved: Partitioning by expressions of type FLOAT64 is not allowed in BigQuery

FLOAT is an approximate datatype. It doesn't preserve the precision. Big query doesn't let you use columns of FLOAT64 datatype in PARTITION BY Clause as it will lead to uncertain results. It will throw the below error message:

Example: In the below query REVENUE is defined as a FLOAT datatype

  SELECT 
  ROW_NUMBER() OVER(PARTITION BY ID, REVENUE ORDER BY DATE) rownum
  FROM `project.dataset.table`

Error: Partitioning by expressions of type FLOAT64 is not allowed

Solution:

Method #1: You need to convert the FLOAT64 column into a NUMERIC using CAST

  SELECT 
  ROW_NUMBER() OVER(PARTITION BY ID, CAST(REVENUE AS NUMERIC) ORDER BY DATE) rownum
  FROM `project.dataset.table`

Method #2: Convert the datatype in the table

It is recommended to change the column (REVENUE) datatype from FLOAT64 to NUMERIC datatype. NUMERIC datatype preserves the decimal precision. You can also use the NUMERIC column as part of the partition by clause

mCollins

posted on 01 Aug 20