Default values not initialized when job loading data with the Python API

[blieusong]

When issuing a job with load_table_from_json, fields in the Schema with a default values are not populated.

I use the following table for testing purposes:

schema = [
    bigquery.SchemaField("client_id", "INTEGER"),
    bigquery.SchemaField(
        "create_date", "DATETIME", default_value_expression="CURRENT_DATETIME()"
    ),
    bigquery.SchemaField("number", "INTEGER", default_value_expression=2, mode="NULLABLE"),
]

Note that I have tried with mode="REQUIRED" but that the loading only fails in such case.

I have also tried creating the table before launching the job and to let the Python load job handle it: none of that worked.

I don't have this issue when streaming instead of using jobs.

Environment details

• macOS 10.15.7
• Python version: 3.11.1
• pip version: 23.0
• google-cloud-bigquery version: 3.5.0

Steps to reproduce

1. Run code below
2. Query the table. Columns create_date and number are empty

Code example

from google.cloud import bigquery

client = bigquery.Client()

schema = [
    bigquery.SchemaField("client_id", "INTEGER"),
    bigquery.SchemaField(
        "create_date", "DATETIME", default_value_expression="CURRENT_DATETIME()"
    ),
    bigquery.SchemaField("number", "INTEGER", default_value_expression=2, mode="NULLABLE"),
]

lines = [
    {"client_id": 1},
    {"client_id": 2},
    {"client_id": 3},
    {"client_id": 4},
]

job_config = bigquery.LoadJobConfig()
job_config.schema = schema
job_config.create_disposition = "CREATE_IF_NEEDED"
job_config.write_disposition = "WRITE_TRUNCATE"

table = "whatever_table"

try:
    load_job = client.load_table_from_json(
        lines,
        destination=table,
        location="europe-west2",
        job_config=job_config,
    )
    print("Load job: %s [%s]" % (load_job.job_id, table))
except Exception as e:
    print("Oops: %s" % (e))

load_job.result()

Stack trace

None. Insertion happens but without the default columns populated.

[chalmerlowe]

I attempted to run this with google-cloud-bigquery=3.4.0 and 3.10.0.
In both cases, I was able to replicate the error on my system.
Thanks for a detailed code example to work from.

[H-Max]

Encountered the issue too, and this goes against what the documentation says here.

Tried to unsert a few lines with a INSERT INTO statement and the default value was correctly used.

On the other hand, trying the same thing with a LOAD DATA sql statement does not work either (it leaves the missing field from the JSON file to null and not set to the default value)

[leonardomira]

Facing the same issue using parquet files from cloud storage and load_table_from_uri function.

Can provide a code sample, if needed

---

EDIT:
In our case, to make this scenario work, we created a table schema and explicitly called for table creation.
After it, we created a new schema WITHOUT columns with default value, and passed that schema for load job.

Code snippet

        table_schema = [
                bigquery.SchemaField("store_id", "INTEGER"),
                bigquery.SchemaField("seller_id", "INTEGER"),
                bigquery.SchemaField("rec_cre_tms", "TIMESTAMP", default_value_expression="CURRENT_TIMESTAMP()"),
            ]
        table_id = f'{project_id}.{dataset}.{table_name}'
        table = bigquery.Table(table_id, schema=table_schema)
        bigquery_client.create_table(table)
...
        job_schema = [
                bigquery.SchemaField("store_id", "INTEGER"),
                bigquery.SchemaField("seller_id", "INTEGER"),
            ]

        job_config = bigquery.LoadJobConfig(
            source_format=bigquery.SourceFormat.PARQUET,
            write_disposition=bigquery.WriteDisposition.WRITE_APPEND,
            schema=job_schema
        )

[BenK-RF]

same issue here!
any updates?

[themoonrider]

same issue here, default values only got inserted correctly with INSERT sql statement.

[nanachong2727]

same issue here