64-bit integers

[krlmlr]

currently cannot be returned.

> query_exec("SELECT 10000000000", "bigrquery-1168")
  f0_
1  NA
Warning message:
NAs introduced by coercion to integer range 
> query_exec("SELECT 10000000000.1", "bigrquery-1168")
    f0_
1 1e+10

[craigcitro]

yeah, this one is a serious bummer -- @hadley do you have strong opinions on any of the options out there for 64 bit integers in R?

[krlmlr]

In DBI, 64-bit values will be represented as numeric, with an optional attribute that stores the original values as a character vector (see r-dbi/DBI#42 (comment) and discussion above). Perhaps bigrquery could implement such a strategy right away, then there will be less trouble adapting that to DBI.

[hadley]

We're standardising on bit64, but it's a hassle to return all integers as int64 because then most ordinary R code won't work.

[hadley]

Maybe best thing is to have an option to return all integers at bit64s?

[krlmlr]

I'm still not sure we should be returning raw integer64 objects in DBI. I'd rather return something along the lines of

structure(
  as.numeric(x),
  class = "xxx",
  integer64 = as.integer64(x)
)

with suitable as.integer64.xxx(), as.character.xxx(), and perhaps [.xxx() methods. This gives numerics by default but allows lossless retrieval of the original data.

Are you considering an option as an argument to dbConnect()?

[hadley]

@krlmlr where did we end up API-wise on this with other packages? Argument to dbConnect()?

[krlmlr]

Yes: r-dbi/DBItest#133. Default would be integer64, but possible to downgrade to numeric or integer. Planned, not yet implemented.

[randallhelms]

Just chiming in here - the Firebase connection to BigQuery stores event timestamp as a 64-bit integer. It's called event_dim.timestamp_micros, as you can see here:

https://support.google.com/firebase/answer/7029846?hl=en

I guess I could convert it to a timestamp in SQL first (which i am currently trying to figure out), but it would be nice if this could be integrated into BigRQuery so that I could have a choice about making the adjustment in R once it was imported.

[koldLight]

Just seen this issue too, on the Google Adwords to BigQuery Transfer (more info here).

My workaround was to cast as string the integer64 columns, but it will be nice to have it natively supported :)

[MiloParigi]

This problem can be problematic when downloading data from bigrquery, you have no warning that the data is not correctly parsed and the tibble returned just have NA values.

For exemple, this command:
bq_table_download(bq_table("bigquery-public-data", "austin_crime", "crime"), max_results = 10)
Will return:

Downloading 10 rows in 1 pages.
# A tibble: 10 x 18
unique_key address census_tract clearance_date clearance_status council_distric… description district latitude longitude location location_descri… primary_type

1 2016771324 " Aust… NA 2016-04-20 00:00:00.000000 Not cleared NA RAPE UK NA NA "" "" Rape
2 NA " Aust… NA NA "" NA SEXUAL ASS… UK NA NA "" "" Rape
3 NA " Aust… NA 2015-10-27 00:00:00.000000 Not cleared NA RAPE UK NA NA "" "" Rape
4 NA " Aust… NA 2015-12-26 00:00:00.000000 Not cleared NA RAPE UK NA NA "" "" Rape
5 NA " Aust… NA 2016-02-09 00:00:00.000000 Not cleared NA AGG RAPE O… UK NA NA "" "" Rape
6 NA " Aust… NA 2016-07-19 00:00:00.000000 Cleared by Exce… NA RAPE UK NA NA "" "" Rape
7 NA " Aust… NA 2016-11-10 00:00:00.000000 Cleared by Exce… NA SEXUAL ASS… UK NA NA "" "" Rape
8 NA " Aust… NA 2016-03-24 00:00:00.000000 Cleared by Exce… NA RAPE OF A … UK NA NA "" "" Rape
9 NA " Aust… NA 2017-01-19 00:00:00.000000 Not cleared NA RAPE OF A … UK NA NA "" "" Rape
10 NA " Aust… NA 2016-08-12 00:00:00.000000 Cleared by Exce… NA RAPE OF A … A NA NA "" "" Rape
# ... with 5 more variables: timestamp <dttm>, x_coordinate <int>, y_coordinate <int>, year <int>, zipcode <chr>

Despite the unique_key being an integer.

Could we force the data to be downloaded with other types ? The workaround i can see now is saving the data on GCS , then downloading from GCS to your local machine and then reading it directly, but that's a pain we could spare, especially for small tables !

[krlmlr]

The bigint argument to dbConnect() is now part of the API.

[AliceGab]

Hi,
I have timestamps coming from Firebase like @randallhelms .
I don't see how to use bq_table_download with bigint ... Any way to make it work within bigrquery ?
I'll try to cast it within my query otherwise.
Thanks