[SOLVED] BigRQuery How to update a column in a table on google big query?

BigRQuery How to update a column in a table on google big query?

I am struggling to update a table in Google BigQuery using the BigRQuery package in R. I have only managed to patch the new column names but not to update the values.

Here is my code so far:

library(bigrquery)
library(tidyverse)
projectId = "ABCD"
datasetId = "test"
tableId = 'table1'

mydf = mpg #just loading an example dataset

x <- list(projectId = projectId,
          datasetId = datasetId,
          tableId   = tableId)

upload a table for the first time

bq_table_upload(x, values = mydf, fields = as_bq_fields(mydf))

now add a column to mydf

mydf$new_column = 0

and try to upload it

bq_table_upload(x, values = mydf, fields = as_bq_fields(mydf))

this gives an error that the table already exists.

I can add a new column using

bq_table_patch(x, fields = as_bq_fields(mydf))

But it is created with "Null" values everywhere, I do not understand how to upload the values (in this case they should be 0) in "new_column"

Any help would be greatly appreciated!

Solution

I found out that one can do it with DBI.

library(DBI)
DBI: dbWRiteTable

First we set up the connection:

bq_conn <- dbConnect(bigquery(),
project = projectId,
dataset = datasetId)

and then

DBI::dbWriteTable(conn = bq_conn,
                  name = tableId,
                  value = df, 
                  as_bq_fields(df),
                  overwrite = TRUE, 
                  append = TRUE)

where df is a new dataset that contains new rows with at least some values for the existing columns. Append and Overwrite can be chosen TRUE/FALSE as needed, of course. Hope it helps someone (I welcome other solutions/suggestions to make it better - I am a newbie in GCP and R). My googling efforts resulted only in finding one unanswered question: https://community.rstudio.com/t/bigrquery-how-to-update-tables/31533