Here’s a wacky one. I have a table, output_df, that I send via bigrquery’s bq_table_upload. What happens is that the operation OVERWRITES the BigQuery table instead of appending. Here’s my code:

          # Show the output of the dataframe
          print(output_df)
          
          # Proceed with further processing
          print("Writing to DB")
          
          tryCatch({
            # Validate arguments
            if (nrow(output_df) == 0) {
              stop("output_df is empty. No data to upload.")
            }
            
            if (is.null(bqproject) || nzchar(bqproject) == 0) {
              stop("bqproject is NULL or empty.")
            }
            
            # Construct table reference
            table_ref <-
              paste0(bqproject, ".", bqdataset, ".", bqtable)
            if (nzchar(table_ref) == 0) {
              stop("table_ref is empty.")
            }
            
            # Proceed with BigQuery upload
            bq_table_upload(
              x = table_ref,
              values = output_df,
              write_disposition = "WRITE_APPEND"
            )

            print("BigQuery job completed successfully")

          }, error = function(e) {
            # Error handling code
            print(paste("Error in BigQuery upload:", e$message))
            # Optionally, add code here to take additional action on error
          })

Now, what’s wild is that a static test works fine – I am able to push a new row into the BigQuery table statically with no issues.

# Create a static data frame
static_df <- data.frame(
  user_id = "test_user",
  bl_id = "test_bl",
  clean_url = "https://example.com",
  clean_source = "test_source",
  clean_medium = "test_medium",
  clean_campaign = "test_campaign",
  clean_keyword = NA,
  clean_content = NA,
  clean_promo = NA,
  final_url = "https://example.com?utm_source=test_source&utm_medium=test_medium&utm_campaign=test_campaign",
  timestamp = Sys.time(),
  stringsAsFactors = FALSE
)

# Print the static data frame to console
print(static_df)

# Construct table reference
table_ref <- bq_table(bqproject, bqdataset, bqtable)

# Upload the static data frame to BigQuery
bq_table_upload(
  x = table_ref,
  values = static_df,
  write_disposition = "WRITE_APPEND"
)

# Print a message after successful upload
print("Static data frame uploaded successfully")

And here’s the crazy. When I go back to my Shiny app and re-run it, it not only overwrites the previous entry, it blows away the results of the static test too, leaving me with only one row in the table.

Has anyone else encountered this? How did you solve it?