Insert multiple records to BigQuery using POST api

Question

I am trying to use the BigQuery python api to load records from a JSON file. However it fails when there are multiple records in the file.

Here is how my json data file looks like

[{"queryID": "newId", "newCol": "newCol"},
 {"queryID": "newId", "newCol": "newCol"}]

And this is the relavent code

insert_request = bigquery.jobs().insert(
    projectId=project_id,
    body={
        'configuration': {
            'load': {
                'schema': {
                    'fields': simplejson.load(open(schema_path, 'r'))
                    },  
                'destinationTable': {
                    'projectId': project_id,
                    'datasetId': dataset_id,
                    'tableId': table_id
                    },  
                'sourceFormat': 'NEWLINE_DELIMITED_JSON',
                }   
            }   
        },  

    media_body=MediaFileUpload(
        './test_data.json',                                                                                                        
        mimetype='application/octet-stream'))

job = insert_request.execute()

This fails with the error JSON parsing error in row starting at position 0 at file: file-00000000. Start of array encountered without start of object. which I think is because it is not able to recognize it as two rows.

However if I make this only one record in my test_data.json file it loads successfully.

{"queryID": "newId", "newCol": "newCol"}

I have been looking at the insert docs but not able to find an options that lets me set multiple rows for insertion.

Any body know how to load multiple records? Any leads with this is appreciated. I feel I am missing something really silly. Thanks.

Answer 1

Your file with JSON data should look like below

{"queryID": "newId", "newCol": "newCol"}  
{"queryID": "newId", "newCol": "newCol"}

So it should be not just JSON but rather newline delimited JSON
See more about supported JSON format