0

I'm trying to load the example JSON file from the BQ site (https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json#example) but getting an error running this from the console

I'm running the command:

bq load --source_format NEWLINE_DELIMITED_JSON --debug testDataSet.tableData ./SeedFile.json ./schema_test.json

but getting an error

Upload complete.
Waiting on bqjob_rxxxxxx_1 ... (0s) Current status: DONE   
BigQuery error in load operation: Error processing job 'test-project-bqjob_rxxxxxx_1': Error while reading data, error message: JSON table
encountered too many errors, giving up. Rows: 1; errors: 1. Please look into the error stream for more details.
Failure details:
- Error while reading data, error message: JSON parsing error in row

Any suggestions or help gratefully received.

Thanks

The example is taken from the Google site. I have pre-created the table with that schema 

Field name  Type    Mode    Description
id  STRING  NULLABLE    
first_name  STRING  NULLABLE    
last_name   STRING  NULLABLE    
dob DATE    NULLABLE    
addresses   RECORD  REPEATED    
addresses.status    STRING  NULLABLE    
addresses.address   STRING  NULLABLE    
addresses.city  STRING  NULLABLE    
addresses.state STRING  NULLABLE    
addresses.zip   STRING  NULLABLE    
addresses.numberOfYears STRING  NULLABLE

data file is 

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}
{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}

Schema file is

[{
        "name": "id",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "first_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "last_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "dob",
        "type": "DATE",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [{
                "name": "status",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "city",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "state",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "zip",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "numberOfYears",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]
paulcull
  • 13
  • 5
  • @[paulcull](https://stackoverflow.com/users/4721586/paulcull), I've just literally copied and paste from your question and it worked fine. Perhaps you could look into your data file, see if there aren't strange characters, that were introduced while copying from the example. Sometimes double quotes are replaced by another character that looks alike, but it is not quite the same. – ch_mike Oct 16 '18 at 21:07
  • You can also try running this from your [Cloud Shell](https://cloud.google.com/shell/docs/quickstart) (if you haven't already), which is a relatively clean environment – ch_mike Oct 16 '18 at 21:09
  • @ch_mike - I was running in cloud shell, but after your first suggestion, I looked at the file character set. According to VSCode, it was 'utf-8 with BOM'. I saved the file as 'utf-8' and it worked the first time. Thanks for the hint. - https://stackoverflow.com/questions/2223882/whats-different-between-utf-8-and-utf-8-without-bom – paulcull Oct 17 '18 at 07:52

0 Answers0