R to BigQuery Data Upload Error

2
votes

I'm using the R package "bigrquery" to upload data from an R data frame into an existing BigQuery table as follows:

mybq = bq_table(project='...', dataset='...', table=...)
bq_table_upload(x=mybq, values=..., create_disposition='CREATE_NEVER', 
                write_disposition='WRITE_APPEND')

But I'm the following error message:

Error: Invalid schema update. Field NewID has changed type from STRING to INTEGER

BigQuery appears to be auto-detecting the data format and mistakenly thinks the NewID column, which has values like "00487", is numeric when it's actually a string. When I append an "x" character to the NewID values, then the error goes away and the upload functions perfectly. Is there any way to disable the auto-detection when uploading data using the "bigrquery" package?

1
rgoogle-bigquery
maybe convert NewID column into character class using as.character before uploading? - chinsoon12
It's already a character in R. Otherwise it can't have leading zeros. The corresponding column in the BigQuery table is a STRING data type. The problem is the auto-detection that ignores the explicit data type; I can't figure out how to turn that feature off. - user1769120

1 Answers

5
votes

The bq_perform_load function in the same library should be a solution for this. In this function you can specify the schema with parameter fields so Bigquery will not auto-detect schema as explained here.

I tested and it worked on my end. I created a table with two columns (STRING, STRING) and this is my source data:

0017    0015
0123    1234
1022    1202

I run the following command in R to make it work:

bq_perform_load('MY_PROJECT.MYDATASET.MYTABLE', "MY_GCS_OBJECT_LINK", nskip = 0, fields = list(bq_field("test1", "string"),bq_field("test2", "string")) , source_format = "CSV",create_disposition = "CREATE_NEVER", write_disposition = "WRITE_APPEND")

Note: I tried it at first time to run the same command with fields = NULL and it failed.