0

I am losing decimals when I write data from R into Google BigQuery using the bigrquery package. I have tried to mess with both the scipen and digits option but nothing is working. Below is an example.

options(scipen = 20)
options(digits=20)
library(bigrquery)

# authenticate in
bq_auth(path = "path_to_credials.json")

# create data
df <- data.frame(x = c(0.123456789, 1.1, .049384203480324, 2), 
                 y = c("dog", "cat", "apple", "pizza"))

# write to BQ
# make table
test_table_name <- "project.ds.table"

# define fields
the_fields <- as_bq_fields(list(
  bq_field("x", "numeric"),
  bq_field("y", "string")
))

# push to BQ
bq_table_upload(x = test_table_name, 
                values = df, 
                fields = the_fields)

The table is created with the right types. But the decimals drop.

BQ result:

enter image description here

enter image description here

If I make the table in BQ with the following code, I do get more decimals.

CREATE TABLE `project.ds.table` (
  `x` NUMERIC,
  `y` STRING,    
);

INSERT `project.ds.table` (x, y)
VALUES(0.123456789, 'dog'),
(1.1, 'cat'),
(0.049384203480324, 'apple'),
(2, 'pizza')

BQ result:

enter image description here

Ricco D
  • 6,873
  • 1
  • 8
  • 18
Nick Criswell
  • 1,733
  • 2
  • 16
  • 32
  • I tried your code and decimals sure drop when using `bq_table_upload`. You can open an issue in https://github.com/r-dbi/bigrquery/issues to report this behavior. – Ricco D Nov 16 '21 at 05:28
  • 1
    @RiccoD, this actually appears to be [a known issue](https://github.com/r-dbi/bigrquery/issues/320) with `bq_table_upload`. It uses a function or few from the `jsonlite` package but doesn't expose the `digits` argument in one or more of them. So it uses the default `digits = 4` or `digits = 5` depending on which function is used. I have [another question](https://stackoverflow.com/questions/69967934/changing-default-dots-argument-in-r?noredirect=1#comment123701472_69967934) out about how to hack those functions to change defaults but am not having any luck changing the ones I need to change. – Nick Criswell Nov 16 '21 at 13:34

0 Answers0