DBI::dbWriteTable error when writing to BigQuery -- trying to coerce string to int

Question

In an AI notebook, I have the following:

%%R
tempdf %>% summary() %>% print()

DBI::dbWriteTable(
    conn=clinvar_conn, 
    name=table_name, 
    value=tempdf, 
    overwrite=T
)

Giving

    CHROM               POS                 ID                REF           
 Length:775501      Length:775501      Length:775501      Length:775501     
 Class :character   Class :character   Class :character   Class :character  
 Mode  :character   Mode  :character   Mode  :character   Mode  :character  
     ALT                QUAL              FILTER              INFO          
 Length:775501      Length:775501      Length:775501      Length:775501     
 Class :character   Class :character   Class :character   Class :character  
 Mode  :character   Mode  :character   Mode  :character   Mode  :character  


Error: Job 'eacri-genomics.job_om9Un5yZVSHjLpt0d4ZzyF743zjy.US' failed
✖ Error while reading data, error message: JSON processing encountered too many errors, giving up. Rows: 740479; errors: 1; max bad: 0; error percent: 0 [invalid]
✖ Error while reading data, error message: JSON parsing error in row starting at position 392688729: Could not convert value 'string_value: "X"' to integer. Field: CHROM; Value: X [invalid]
Run `rlang::last_error()` to see where the error occurred.

I made sure to cast all the data as character. Why is it trying to convert "X" to an integer?

The error message talks about JSON processing, but there are no details about your data format in the question. Please would you provide more details? In particular, where does the text `string_value: "X"` appear in your input data? and what is at position 392688729? — Simon.S.A., Oct 01 '20 at 19:48
One column has chromosomes which are mostly numbered 1-34, but there is also X and Y. — abalter, Oct 01 '20 at 19:52
Position 392688729 seems very high if you only have 775501 rows. Two possibilities to investigate (1) some of your text fields are longer than the maximum number of characters, (2) there is an unescaped quote in your input that means multiple values are being misread as a single value. — Simon.S.A., Oct 01 '20 at 19:53

score 0 · Answer 1 · answered Oct 10 '20 at 04:48

Re-posting comment as an answer for completeness.

Position 392688729 seems very high if you only have 775501 rows. This makes me suspect that some of your data-types are not what they are assumed to be. Given that the error message mentions JSON, perhaps some of your fields are JSON objects and casting them to string does not work how you might assume.

Two possibilities to investigate:

some of your text fields are longer than the maximum number of characters
there is an un-escaped quote in your input that means multiple values are being misread as a single value

DBI::dbWriteTable error when writing to BigQuery -- trying to coerce string to int

1 Answers1