Unable to create another table based on query in BQ using python

Question

I'm trying to create a table that is a product of a query from another table. Basically I want to produce another table called main_table FROM stg_table.

script.sql:

SELECT * FROM `project_id.dataset.stg_table`

my_python.py:

def get_field_schema(field):

    name = field['name']
    field_type = field['type']
    mode = field['mode']
    fields = field.get('fields', [])

    if fields:
        subschema = []
        for val in fields:
            fields_res = get_field_schema(val)
            subschema.append(fields_res)
    else:
        subschema = []

    field_schema = bigquery.SchemaField(name=name, field_type=field_type,
                                        mode=mode, fields=subschema)

    return field_schema


def parse_bq_json_schema(schema_filename):

    schema = []
    with open(schema_filename, 'r') as infile:
        jsonschema = json.load(infile)

    for field in jsonschema:
        schema.append(get_field_schema(field))

    return schema

def push_to_main_bq(final_table, tbl_schema_json, sql_file):

    bq_client = bigquery.Client.from_service_account_json(key_path)
    
    with open(sql_file, 'r') as file:
        query = file.read()
    query = query.format(PROJECT_ID=PROJECT_ID)

    job_config = bigquery.QueryJobConfig(
        allow_large_results=True, use_legacy_sql=False
    )

    job_config.schema = parse_bq_json_schema(tbl_schema_json) #ERROR HERE
    # Start the query, passing in the extra configuration.
    query_job = bq_client.query(query, job_config=job_config)  # Make an API request.
    print("Starting BQ load job {} for {}.{}.{}".format(query_job.job_id, PROJECT_ID,
        DATASET_NAME, final_table))
    query_job.result()  # Wait for the job to complete.
    print("BQ load job finished.")

push_to_main_bq("main_table","table_schema.json", "script.sql")

get_field_schema() and parse_bq_json_schema() will produce the following result :

[SchemaField('column_1', 'INTEGER', 'NULLABLE', None, (), None),......]

I don't think this causing any issue. As I have used it somewhere else and they worked fine. However, I'm getting error here in push_to_main_bq() on line job_config.schema = parse_bq_json_schema(tbl_schema_json) saying :

File "/usr/local/lib/python3.7/site-packages/google/cloud/bigquery/job/base.py", line 697, in setattr "Property {} is unknown for {}.".format(name, type(self)) AttributeError: Property schema is unknown for <class 'google.cloud.bigquery.job.query.QueryJobConfig'>.

Sergey Geron Sergey Geron · Accepted Answer · 2021-04-01T10:24:41

QueryJobConfig does not have a schema attribute. Use destination instead. Look for examples here:

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

sql = "SELECT * FROM `bigquery-public-data.samples.shakespeare`"
cluster_fields = ["corpus"]

job_config = bigquery.QueryJobConfig(
    clustering_fields=cluster_fields, destination=table_id
)

# Start the query, passing in the extra configuration.
query_job = client.query(sql, job_config=job_config)  # Make an API request.
query_job.result()  # Wait for the job to complete.

table = client.get_table(table_id)  # Make an API request.
if table.clustering_fields == cluster_fields:
    print(
        "The destination table is written using the cluster_fields configuration."
    )

Unable to create another table based on query in BQ using python

1 Answers