01 Aug 20 · npack · #bigquery ·   Bookmark   ×

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

npack

posted on 01 Aug 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