Dynamic BigQuery Schema using Auto Detection:Error Schema has no fields

Question

I am trying to mess with the auto detection feature in Bigquery and currently I am encountering issues on updating the schema on my table.

What currently I have done.

I created manually a dataset and table name in Bigquery.
Execute my first bq load command (Works perfectly fine):

bq --location=${LOCATION} load --autodetect --source_format=${FORMAT} ${DATASET}.${TABLE} ${PATH_TO_SOURCE}.

I try to append a new JSON object introduced with new field to update the current schema.
Execute 2nd bq load command:

bq --location=${LOCATION} load --autodetect --schema_update_option=ALLOW_FIELD_ADDITION --source_format=${FORMAT} ${DATASET}.${TABLE} ${PATH_TO_SOURCE}

Throws an error:

Error in query string. Error processing job. Schema has no fields.

I thought when --autodetect flag is enabled bq load command will not request for schema on your load job. Has anyone already encountered this issue?

First object:

  {
    "chatSessionId": "123",
    "chatRequestId": "1234",
    "senderType": "CUSTOMER",
    "senderFriendlyName": "Player"
  }

Second Object:

{
    "chatSessionId": "456",
    "chatRequestId": "5678",
    "senderType": "CUSTOMER",
    "senderFriendlyName": "Player",
    "languageCode": "EN"
  }

Can you provide a piece of the the first data you loaded and the data you're using to update? The error that you posted is the complete error? — rmesteves

rmesteves rmesteves · Accepted Answer · 2020-02-19T10:23:00

I reproduced your steps but I couldn't reproduce the same error as you can see in the images below:

Loading first JSON

First table's data

Loading second JSON

Second's table data

The only thing I changed in your data was the format: you provided a JSON and I turned it into a NEWLINE DELIMITED JSON (type of JSON that BigQuery expects). You can find more information about it here. Please let me know if it clarifies something for you.

I hope it helps.

Dynamic BigQuery Schema using Auto Detection:Error Schema has no fields

1 Answers