Search code examples
google-bigquerycommand-line-interfaceschemajobs

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

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.

  1. I created manually a dataset and table name in Bigquery.
  2. Execute my first bq load command (Works perfectly fine):
bq --location=${LOCATION} load --autodetect --source_format=${FORMAT} ${DATASET}.${TABLE} ${PATH_TO_SOURCE}.
  1. I try to append a new JSON object introduced with new field to update the current schema.
  2. Execute 2nd bq load command:
bq --location=${LOCATION} load --autodetect --schema_update_option=ALLOW_FIELD_ADDITION --source_format=${FORMAT} ${DATASET}.${TABLE} ${PATH_TO_SOURCE}
  1. 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"
  }
Solution

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


    Loading first JSON Loading first JSON



    First table's data First table loaded



    Loading second JSON Loading second JSON



    Second's table data Second table loaded

    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.