CODEWITHSUNDEEP
google-bigquery
Charles
Charles

Reputation: 590

Append data from SELECT to existing table

I'm trying to append data fetched from a SELECT to another existing table but I keep getting the following error:

Provided Schema does not match Table projectId:datasetId.existingTable

Here is my request body:

{'projectId': projectId,
 'configuration': {
     'query': {
         'query': query,
         'destinationTable': {
             'projectId': projectId,
                 'datasetId': datasetId,
                 'tableId': tableId
         },
         'writeDisposition': "WRITE_APPEND"
     }
  }
}

Seems like the writeDisposition option does not get evaluated.

Upvotes: 1

Views: 5285

Answers (3)

Sathish Senathi
Sathish Senathi

Reputation: 215

I have successfully appended data to existing table from a CSV file using bq command line tool. The only difference i see here is the configuration to have

write_disposition instead of writeDisposition as shown in the original question.

What i did is add the append flag to bq command line utility (python scripts) for load and it worked like charm.

I have to update the bq.py with the following.

  1. Added a new flag called --append for load function
  2. in the _Load class under RunWithArgs checked to see if append was set if so set 'write_disposition' = 'WRITE_APPEND'

The code is changed for bq.py as follows

In the __init__ function for _Load Class add the following

**flags.DEFINE_boolean(
        'append', False,
        'If true then data is appended to the existing table',
        flag_values=fv)**

And in the function RunWithArgs for _Load class after the following statement

if self.replace:
      opts['write_disposition'] = 'WRITE_TRUNCATE'

---> Add the following text 

**if self.append:
      opts['write_disposition'] = 'WRITE_APPEND'**

Now in the command line 

> bq.py --append=true <mydataset>.<existingtable> <filename>.gz

will append the contents of compressed (gzipped) csv file to the existing table.

Upvotes: 1

Charles
Charles

Reputation: 590

Ok think I got something here. That's a weird one... Actually it does not work if you have the same schema exactly (field mode).

Here is the source table schema:

 "schema": {
   "fields": [
   {
     "name": "ID_CLIENT",
     "type": "INTEGER",
     "mode": "NULLABLE"
   },
   {
     "name": "IDENTITE",
     "type": "STRING",
     "mode": "NULLABLE"
   }
  ]
  }

If if I use the copy functionality from the browser interface (bigquery.cloud.google.com), I get the exact same schema which is expected:

"schema": {
  "fields": [
  {
    "name": "ID_CLIENT",
    "type": "INTEGER",
    "mode": "NULLABLE"
  },
  {
    "name": "IDENTITE",
    "type": "STRING",
    "mode": "NULLABLE"
  }
  ]
 }

But then I cannot append from the following fetch to the copied table:

SELECT ID_CLIENT + 1 AS ID_CLIENT, RIGHT(IDENTITE,12) AS IDENTITE FROM datasetid.client

although it returns the same schema, at least from the browser interface view, internally this returns the following schema:

"schema": {
 "fields": [
 {
   "name": "ID_CLIENT",
   "type": "INTEGER",
   "mode": "REQUIRED"
 },
 {
   "name": "IDENTITE",
   "type": "STRING",
   "mode": "NULLABLE"
 }
]

}

Which isn't the same schema exactly (check mode).

And weirder this select:

SELECT ID_CLIENT, IDENTITE FROM datasetid.client

returns this schema:

"schema": {
  "fields": [
  {
    "name": "ID_CLIENT",
    "type": "INTEGER",
    "mode": "REQUIRED"
  },
  {
    "name": "IDENTITE",
    "type": "STRING",
    "mode": "REQUIRED"
  }
 ]
}

Conclusion:

Don't rely on tables schema information from the browser interface, always use Tables.get API. Copy doesn't really work as expected...

Upvotes: 1

Jordan Tigani
Jordan Tigani

Reputation: 26617

In order for the append to work, the schema of the existing table must match exactly the schema of the query results you're appending. Can you verify that this is the case (one way to check this would be to save this query as a table and compare the schema with the table you are appending to).

Upvotes: 1

Related Questions