CODEWITHSUNDEEP
pythongoogle-bigquery
Oksana Ok
Oksana Ok

Reputation: 555

Append Big query table to existing one, by ignoring SCHEMA

I have a straight forward question, does Google have a way to append one table to another one, by ignoring SCHEMA as I have the error:

Invalid schema update. Field XXXhas changed type from NUMERIC to FLOAT

Python code:

def COPY():
    client = bigquery.Client()

    job_config = bigquery.QueryJobConfig()
    job_config.destination = client.dataset("DATASET").table("TABLE")
    #job_config.create_disposition = 'CREATE_IF_NEEDED'
    job_config.write_disposition = 'WRITE_APPEND'
    #job_config._properties['schemaUpdateOptions'] = ['ALLOW_FIELD_ADDITION']

    wave_sql = 'SELECT * FROM `PROJECT.DATASET.TABLE`'
    wave = client.query(wave_sql,location='EU',job_config=job_config)  
    wave.result()

Upvotes: 0

Views: 604

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

wave_sql = 'SELECT * REPLACE(CAST(XXXhas AS NUMERIC) AS XXXhas) FROM `PROJECT.DATASET.TABLE`'

note: you will pay for whole table scan, if schema were same you would rather use COPY (instead of SELECT *); also you cannot use SELECT * EXCEPT() ... pattern as this will change schema's layout - so you rather should use SELECT * REPLACE() ...

Upvotes: 2

Yun Zhang
Yun Zhang

Reputation: 5503

If you don't care about losing precision, you may change one line of your code to make it work:

    wave_sql = 'SELECT * EXCEPT(XXXhas), CAST(XXXhas AS FLOAT64) XXXhas FROM `PROJECT.DATASET.TABLE`'

Upvotes: 1

manesioz
manesioz

Reputation: 837

You could always try resolving this error by changing the data type of the relevant columns.

For example:

SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable

and then choose the appropriate table and select "Overwrite"

Upvotes: 0

Related Questions