How to write NaN to biqquery in Java?

In biqquery table, we can use isNaN() to check NaN value.

But how to write NaN to bigquery table in java (JVM languages)?

Write string "NaN"?

Any comments are welcome. Thanks

UPDATE:

we were trying to send Double.NaN, but the big query does not accept it and throwing exceptions. No idea why.

Error sending insert request, table: xxxx
com.google.cloud.bigquery.BigQueryException: java.lang.IllegalArgumentException
at com.google.cloud.bigquery.BigQueryException.translateAndThrow(BigQueryException.java:100)
at com.google.cloud.bigquery.BigQueryImpl.insertAll(BigQueryImpl.java:979)
...
Caused by: java.lang.IllegalArgumentException
 	at com.google.common.base.Preconditions.checkArgument(Preconditions.java:128)
 	at com.google.api.client.util.Preconditions.checkArgument(Preconditions.java:35) 	
    at com.google.api.client.json.JsonGenerator.serialize(JsonGenerator.java:134)
	at com.google.api.client.json.JsonGenerator.serialize(JsonGenerator.java:173)
 	at com.google.api.client.json.JsonGenerator.serialize(JsonGenerator.java:173)
 	at com.google.api.client.json.JsonGenerator.serialize(JsonGenerator.java:173)
 	at com.google.api.client.json.JsonGenerator.serialize(JsonGenerator.java:146)
 	at com.google.api.client.json.JsonGenerator.serialize(JsonGenerator.java:173)
 	at com.google.api.client.json.JsonGenerator.serialize(JsonGenerator.java:105)

Float.NaN or Double.NaN will not work.

Under the hood the Java BigQuery API uses the Google HTTP Client Library for Java.

Neither NaN nor infinity are supported JSON symbols. Probably for that reason, when serializing the provided data, JsonGenerator explicitly forbids it:

double doubleValue = ((Number) value).doubleValue();
Preconditions.checkArgument(!Double.isInfinite(doubleValue) && !Double.isNaN(doubleValue));
writeNumber(doubleValue);

As a side note, in contrast, the library allows parsing this values. Please, see the relevant commit.

In addition two the aforementioned cases, in Java you cannot provide the NaN value by other mean.

In my opinion, NaN is not conceived to be passed as a function argument, rather they should be considered a computational error or the result of some kind of overflow like the one that could be caused in IEEE_DIVIDE, and that is the reason for the existence of the data type in BigQuery:

> Function calls and operators return an overflow error if the input is
> finite but the output would be non-finite. If the input contains non-finite
> values, the output can be non-finite. In general functions do not introduce
> NaNs or +/-inf. However, specific functions like IEEE_DIVIDE can
> return non-finite values on finite input. All such cases are noted
> explicitly in Mathematical functions.

Instead of using NaN, please treat then as null values: that is the approach followed in other languages.

Below is for BigQuery Standard SQL and gives you few ways of generating Nan, I just wrapped it with IS_NaN to check that result is actually NaN and not just string 'NaN'

SELECT
  test1,
  IS_NAN(test1),
  test2,
  IS_NAN(test2)
FROM (  
SELECT 
  IEEE_DIVIDE(0, 0) test1, 
  CAST('NaN' AS FLOAT64) test2
)  

with result

Row	test1	f0_	    test2	f1_	 
1	NaN	    true	NaN	    true