CODEWITHSUNDEEP
pythongoogle-bigquerydatetime-format
balkon16
balkon16

Reputation: 1438

BigQuery field has changed type from STRING to TIMESTAMP

I'm importing data into BigQuery using Python API:

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

from schema import schema


target_project_name = 'project_name'
target_dataset_name = 'dataset_name'
target_table_name = 'table_name'

os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = './credentials.json'

dataset_ref = bigquery.dataset.DatasetReference(target_project_name, target_dataset_name)
table_ref = bigquery.table.TableReference(dataset_ref, target_table_name)

job_config = bigquery.LoadJobConfig()
job_config.schema = schema

client = bigquery.Client()

cnt = 0

with open('./data/transformed_v2.json', 'r') as f:
    json_list = []
    for line in f.readlines():
        cnt += 1
        if cnt >= 10:
            break
        json_contents = json.loads(line)
        json_list.append(json_contents)


    job = client.load_table_from_json(json_list, table_ref)

try:
    result = job.result()
except BadRequest as ex:
    for err in ex.errors:
        print(err)
    print(job.errors)

The schema mentioned in the code above contains a problematic field migration_datetime that is defined as bigquery.SchemaField("migration_datetime", "STRING", mode="NULLABLE"). The field contains values that are stored in the '%Y-%m-%dT%H:%M:%S' format. The schema in the target table has the migration_datetimefield specified asSTRING`.

The job.result() raises an error saying that Field migration_datetime has changed type from STRING to TIMESTAMP. To mitigate this, I added the _string suffix to values in the migration_datetime column but the error still occurs. Do you what may be the reason?

Upvotes: 2

Views: 4944

Answers (1)

Sergey Geron
Sergey Geron

Reputation: 10152

Consider passing job_config into the load_table_from_json like here or using client.schema_from_json

Upvotes: 3

Related Questions