【R】bigrqueryパッケージでBigQueryにデータをインサートするとき、integer型の最大値で困った

RからGoogle BigQueryを操作できるbigrqueryが便利です。クエリを投げてローカルにデータを取得する他、データソース名やテーブル名を取得したり、テーブルを削除したりもできます。

また、次のようにしてinsert_upload_jobを使って、データフレームをテーブルとしてインサートすることもできます。

job <- bigrquery::insert_upload_job("example-dev-111", "datasource", "mtcars", mtcars)
bigrquery::wait_for(job)

https://cran.r-project.org/web/packages/bigrquery/bigrquery.pdf

ところで、BigQueryではデータ構造をスキーマとして定義する必要がある（通常ではjsonなどで定義する）のですが、bigrqueryでは、カラムの変数の型を判別し、自動でスキーマを定義してくれます。また、NA値も自動でnull値として判別します。

> test_data <- data.frame(num=c(2,3,NA), date=as.Date("2016-01-01"))
> test_data
  num         date
1   2   2016-01-01
2   3   2016-01-01
3  NA   2016-01-01
> class(test_data$num)
[1] "numeric"
> class(test_data$date)
[1] "Date"

これをBigQueryにインサートすると、numeric型はFLOATとして、Date型はTIMESTAMPとして認識されます。

	num	date
1	2.0	2016-01-01 00:00:00 UTC
2	3.0	2016-01-01 00:00:00 UTC
3	null	2016-01-01 00:00:00 UTC

ちょっとしたデータなら、Rで読み込んでBigQueryにインサートすれば良さそうです。また、元になるRのデータ型とスキーマの対応関係は以下のコードの部分で見ることができますね。

https://github.com/rstats-db/bigrquery/blob/cdb968d1b314999354bd37fc2224f54d791feec9/R/upload.r#L58

data_type <- function(x) {
  switch(class(x)[1],
    character = "STRING",
    logical = "BOOLEAN",
    numeric = "FLOAT",
    integer = "INTEGER",
    factor = "STRING",
    Date = "TIMESTAMP",
    POSIXct = "TIMESTAMP",
    stop("Unknown class ", paste0(class(x), collapse = "/"))
  )
}

そのため、まずインサートしたいデータフレームの各カラムを、あらかじめas.integerとかas.Dateとかで型を変換してあげる必要があります。

ところがここで落とし穴があって、Rではinteger型の最大値があり、それ以上の数値(numeric)にas.integer関数を適用するとNA値が返ってきてしまうという問題があります。

> .Machine$integer.max
[1] 2147483647
> as.integer(.Machine$integer.max)
[1] 2147483647
> as.integer(.Machine$integer.max + 1)
[1] NA
 警告メッセージ: 
NAs introduced by coercion to integer range

Rではこれ以上の整数値では、bit64ライブラリでinteger64型を使うことになります。この場合、bit64::as.integer64関数を使って変換することになります。

CRAN - Package bit64

> library(bit64)
> bit64::as.integer64(.Machine$integer.max + 1)
integer64
[1] 2147483648
> num <- bit64::as.integer64(.Machine$integer.max + 1)
> class(num)
[1] "integer64"

ところが、型の名前が異なるため、先ほどのswitch文のところで例外が発生し、そのままではinsert_upload_jobを使うことができません。

そこで、bigrqueryの先ほどのswitch文の部分を以下のように書き換えて読み込ませた（というかgithubの公開アカウントでbigrqueryをフォークして、devtools::install_github('takeshi0406/bigrquery')でインストールした）ところ、Rのinteger型で扱えない大きさの値を、integer64型として扱い、BigQueryにINTEGER型として入力することに成功しました。

data_type <- function(x) {
  switch(class(x)[1],
    character = "STRING",
    logical = "BOOLEAN",
    numeric = "FLOAT",
    integer = "INTEGER",
    integer64 = 'INTEGER',
    factor = "STRING",
    Date = "TIMESTAMP",
    POSIXct = "TIMESTAMP",
    stop("Unknown class ", paste0(class(x), collapse = "/"))
  )
}