big query csv load issue

Question

I recently started working on gcp and bigquery in general I am getting the below error while loading csv file from google cloud storage to BQ table using Google cloud composer

Below is the code I am using.

t1 = GoogleCloudStorageToBigQueryOperator(
      task_id='gcs_to_bq_mak',
      bucket='bucketname',
      source_objects=['FULL/mak.csv'],
      field_delimiter='|',
      destination_project_dataset_table='{0}.mak_initial_f'
      .format(BQ_DATASET_NAME),
      schema_fields= mak_schemas.mak_schema(),
      #create_disposition='CREATE_IF_NEEDED',
      skip_leading_rows=1,
      #quote = '""',
      #quote = ''
      #quote = '"'
      #allowQuotedNewlines =  True,
      write_disposition='WRITE_APPEND',
      time_partitioning={'Date':'timestamp'}
                 )

My file is delimited by pipe.

No matter what value I set my quote operator to all i get is the below error

Error: Data between close double quote (") and field separator.'}], 'state': 'DONE'}}

I am not sure if I am using the quote option as intended.

Below is a sample row for why my job is failing

100|I|50|100010012|F|1" PAE|1-8" PAE|20190|C|1

as you see the " has been closed after the delimiter which is pipe so my job fails.

Is there any workaround for this I referenced many posts all mentioned to use the quote operator but that's not working for me or I am not using it properly.

Now If it is not possible to escape the quotes in a field then I am planning to load whole row as a text into an int table

but what should my next step be how to load that single row again back to the original table.

example code I am planning to implement.

t3 = GoogleCloudStorageToBigQueryOperator(
        task_id='mak_load_one_column',
        bucket='bucketname',
        source_objects=['mak.csv'],
        field_delimiter='\t',
        allow_jagged_rows=True,
        destination_project_dataset_table='{0}.mak_init_singlecolumn'
        .format(BQ_DATASET_NAME),
        schema_fields=[{"name": "singlecolumn","type": "TEXT","description": 
                      "load all the rows into one column"}],
        skip_leading_rows=1,
        write_disposition='WRITE_APPEND',
        #time_partitioning={'Date':'timestamp'},
        #provide_context=True,
        #trigger_rule=TriggerRule.ALL_FAILED)

but how do i reload the data into a table with proper schema.

Any advise appreciated.

Regards.

Kenny Ostrom Kenny Ostrom · Accepted Answer · 2019-08-27T04:20:38

RFC 4180

2.5 Each field may or may not be enclosed in double quotes ... If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.

2.6 Fields containing line breaks (CRLF), double quotes, and commas should be enclosed in double-quotes.

2.7 If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote. For example:

"aaa","b""bb","ccc"

Not everything is fully csv compliant. Other delimiters are okay, and quoting rules might vary, so there might be a library specific issue, between the two libraries that are trying to talk to each other here. But since you mentioned trying to quote it, I would suggest trying this format (although I would just put every field in double quotes)

100|I|50|100010012|F|"1"" PAE"|"1-8"" PAE"|20190|C|1

or if that pipe is supposed to be in the field, too, maybe you meant this?

100|I|50|100010012|F|"1"" PAE|1-8"" PAE"|20190|C|1

big query csv load issue

3 Answers