Append / Insert Data Into BigQuery Table Using R's bigrquery Library

Question

Question is straightforward. I have the following code:

# authenticate
bigrquery::bq_auth(path = '/Users/me/restofthepath/bigquery-credentials.json')

# set my project ID and dataset name
project_id <- 'mygreatprojectid'
dataset_name <- 'static'

# how i normally create a new table
players_table = bq_table(project = project_id, dataset = dataset_name, table = 'players')
bq_table_create(x = players_table, fields = as_bq_fields(players_df))
bq_table_upload(x = players_table, values = players_df)

Here, players_df is a dataframe of player statistics already computed in R. The following code works successfully, creating a new table. However, if I have more players that I'd like to append to the table, I am struggling. I have tried the following:

bq_table_upload(x = players_table, values = players_df_2)

...where players_df_2 is another dataframe with more player statistics... However, this returns the error Error: Already Exists: Table mygreatprojectid:static.players [duplicate]

Any thoughts on how to do this, preferably without having to delete + recreate the table? Thanks!!

EDIT: It looks like bq_table_patch exists, however this appears to be for adding new fields/columns, not for appending new rows...

Answer 1

You need to set the WRITE_DISPOSITION and CREATE_DISPOSITION, for example:

bq_table_upload(x=players_table, values= players_df_2, create_disposition='CREATE_IF_NEEDED', write_disposition='WRITE_APPEND')