Insert JSON Data into BigQuery ERROR: Error while reading data, error message: JSON parsing error in row starting at position 0: Expected key

Question

I'm trying to insert dummy data into BigQuery using a Python Script seen below.

I'm getting this error and not sure why/how to fix it:

ERROR: Error while reading data, error message: JSON parsing error in row starting at position 0: Expected key

def insert_into_bq(cloud_event):
        values = {
        'person_id': raw_data_to_insert['PersonID'],
        'first_name': raw_data_to_insert['FirstName']
    }


table_schema = {
    'name': 'PersonID',
    'type': 'INTEGER',
    'mode': 'REQUIRED'
    },{
        'name': 'FirstName',
        'type': 'STRING',
        'mode': 'NULLABLE'
    }
        

project_id = 'PROJECT'
dataset_id = 'tests'
table_id = 'persons'

client  = bigquery.Client(project = project_id)
dataset  = client.dataset(dataset_id)
table = dataset.table(table_id)

job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
job_config.schema = format_schema(table_schema)

with open('data_to_insert.json', 'w') as fp:
    json.dump(values, fp, ensure_ascii=False, indent=4)



with open('data_to_insert.json', 'rb') as source_file:
    job = client.load_table_from_file(
        source_file,
        table,
        job_config=job_config
    )
    try:
        print(job.result())
    except BadRequest as e:
        for e in job.errors:
            print('ERROR: {}'.format(e['message']))

@UlrichEckhardt I've seen those posts but they don't mention "expected key". Not sure what that's referring to myself so would appreciate any help with that. — mj8701
– mj8701, Commented Aug 22, 2022 at 18:27
It will help to share your dummy data as well so we can reproduce your scenario — Anjela B
– Anjela B, Commented Aug 22, 2022 at 22:55
@anjelab I managed to get it to work. the dummy data was in the dictionary called values at the top. — mj8701
– mj8701, Commented Aug 23, 2022 at 15:01
I was able to replicate and it also worked on my end, you can also try the approach that I did on the answer section. You may also refer to this SO post: What should I do when someone answers my question? — Anjela B
– Anjela B, Commented Aug 24, 2022 at 5:33

Anjela B · Accepted Answer · 2022-08-24 05:28:36Z

For the benefit of the community, this approach works as well:

from google.cloud import bigquery
from google.api_core.exceptions import BadRequest
import json


def format_schema(schema):
    formatted_schema = []
    for row in schema:
        formatted_schema.append(bigquery.SchemaField(row['name'], row['type'], row['mode']))
    return formatted_schema

raw_data_to_insert = {'idno': 1, 'name': 'Joe'}
raw_data_to_insert_1 = {'idno': 2, 'name': 'Jane'}



values = [{'person_id': raw_data_to_insert['idno'], 'first_name': raw_data_to_insert['name']}, {'person_id': raw_data_to_insert_1['idno'], 'first_name': raw_data_to_insert_1['name']}]
result = [json.dumps(record) for record in values]
#print('\n'.join(result))
result_2 = ('\n'.join(result))


table_schema = {
    'name': 'person_id',
    'type': 'INTEGER',
    'mode': 'REQUIRED'
    },{
        'name': 'first_name',
        'type': 'STRING',
        'mode': 'NULLABLE'
    }


project_id = '<your-project>'
dataset_id = '<your-dataset>'
table_id = 'persons'

client  = bigquery.Client(project = project_id)
dataset  = client.dataset(dataset_id)
table = dataset.table(table_id)

job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
job_config.schema = format_schema(table_schema)

with open('data_to_insert.json', 'w') as fp:
    fp.write(result_2)


with open('data_to_insert.json', 'rb') as source_file:
    job = client.load_table_from_file(
        source_file,
        table,
        job_config=job_config
    )
    try:
        print(job.result())
    except BadRequest as e:
        for e in job.errors:
            print('ERROR: {}'.format(e['message']))

Output:

Collectives™ on Stack Overflow

Insert JSON Data into BigQuery ERROR: Error while reading data, error message: JSON parsing error in row starting at position 0: Expected key

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related