bigQueryRでINSERT

bq-example.R

library(tidyverse)
library(lubridate)
library(googleAuthR)
library(bigQueryR)

options(
  googleAuthR.client_id = Sys.getenv("GOOGLE_CLIENT_ID"),
  googleAuthR.webapp.client_id = Sys.getenv("GOOGLE_CLIENT_ID"),
  googleAuthR.client_secret = Sys.getenv("GOOGLE_CLIENT_SECRET"),
  googleAuthR.webapp.client_secret = Sys.getenv("GOOGLE_CLIENT_SECRET"),
  googleAuthR.scopes.selected = "https://www.googleapis.com/auth/cloud-platform",
  googleAuthR.httr_oauth_cache = ".httr-oauth-bq"
)
token <- gar_auth(new_user = TRUE)
bqr_auth(token = token)

# プロジェクト名: terashim-experiment
# データセット名: my_dataset
# テーブル名: my_table

project_id <- "terashim-experiment"
dataset_id <- "my_dataset"
table_id <- "my_table"

# 存在を確認する

available_project_ids <- bqr_list_projects()$id
project_id %in% available_project_ids

available_datasets <- bqr_list_datasets(projectId = project_id)
dataset_id %in% available_datasets$datasetId

available_tables <- bqr_list_tables(projectId = project_id, datasetId = dataset_id)
table_id %in% available_tables$tableId

# テーブルのスキーマを調べる
table_meta <- bqr_table_meta(project_id, dataset_id, table_id)
table_meta$schema

data <- data.frame(
  date = as_date('2019-03-05'), # shoud be Date
  impressions = 2000L, # should be integer
  clicks = 150L, # should be integer
  cost = 300.0 # should be numeric
)

# INSERT文
result <- bqr_upload_data(
  projectId = project_id,
  datasetId = dataset_id,
  tableId = table_id,
  upload_data = data,
  create = "CREATE_NEVER", # existing table
  overwrite = FALSE # append mode
)