Inf turns into NULL

[eheinzen]

I'm guessing this is a limitation of BigQuery, but in case it's not: R's Inf doesn't make it into BigQuery as such; it turns into NULL, which then gets read back into R as NA.

> x <- data.frame(hi = c(1, 1.2, Inf))
> DBI::dbCreateTable(conn, "blah", x)
> DBI::dbAppendTable(conn, "blah", x, billing = conn@billing)
> DBI::dbGetQuery(conn, "select * from blah")
# A tibble: 3 × 1
     hi
  <dbl>
1   1  
2   1.2
3  NA  

[r2evans]

This is not a limitation of BQ, it's just the behavior of the hard-coded default value for na="null". This can be remedied if #664 is merged, allowing custom arguments to jsonlite functions:

options(bigrquery.jsonlite.toJSON=list(na='string')
x <- data.frame(hi = c(1, 1.2, Inf))
bq_table_upload(ds, x)
bq_table_download(ds)
# # A tibble: 3 × 1
#   hi   
#   <dbl>
# 1 1    
# 2 1.2  
# 3 Inf  

However, this fails if there are also NA values in x:

x <- data.frame(hi = c(1, 1.2, Inf, NA))
bq_table_upload(ds, x)
bq_table_download(ds)
# Streamed 4 rows in 1 messages.
# # A tibble: 4 × 1
#   hi   
#   <chr>
# 1 1    
# 2 1.2  
# 3 Inf  
# 4 NA   

This is a limitation in how jsonlite is handling NA-like values.

In export_json(), the jsonlite::stream_out by default (na="null") produces:

{"hi":1}
{"hi":1.2}
{}
{}

If we apply #664 and set options to use na="string", then it produces:

{"hi":1}
{"hi":1.2}
{"hi":"Inf"}
{"hi":"NA"}

This shows the problem of "NA". If we interject and replace that with the json-friendly null, we'd like to upload

{"hi":1}
{"hi":1.2}
{"hi":"Inf"}
{"hi":null}

If that stream is sent to BQ then it works as desired:

bq_table_download(ds)
# # A tibble: 4 × 1
#      hi
#   <dbl>
# 1   1  
# 2   1.2
# 3 Inf  
# 4  NA  

So ... if you are sure that you'll never have Inf, then na="null" (default and current) works; if you are sure that you'll never have NA, then na="string" (with PR664) works. If you want both, well, we need to do some magic.

To be honest, I "might" be able to construct a regex mechanism to intercept the json stream to change "NA" to null, but it can certainly go awry with well-crafted counter-examples (as regex can), as well as being potentially inefficient in other ways. For efficiency this needs to be resolved within jsonlite. In the past, @jeroen has been loath to add support for "features" not supported expicitly within the JSON standard, but there might be some room here.

Perhaps we can support toJSON(x, na="null2") (or similar) to map R's NA to the literal null (as in na="null") and other specials mapped as when using na="string". Jeroen, does that notion seem palatable to you?

[r2evans]

For clarity, jeroen has already discussed this request in various (but not identical) forms in jeroen/jsonlite#94, jeroen/jsonlite#178, jeroen/jsonlite#245, jeroen/jsonlite#302, jeroen/jsonlite#445, and jeroen/jsonlite#459. The closest/most-applicable to this issue is 94, where yihui asked to be able to differentiate NA from NaN and Inf. Jeroen suggested his way forward is to force the user to deal with this with as.character.

I think the collective use of JSON has evolved outside of its original/simple roots, and the fact that BQ will support "Inf" is a good example of how we might want to update how we use JSON (even if its definition of permitted literals is not changing). Unfortunately, I don't see an easy path forward right now so long as jsonlite does not adapt to support this, and bigrquery stays with jsonlite. Afaict, no other json packages for R would support this anyway, so it's a "global shift".