create table from query while keep original schema

3
votes

I'm using the following workflow to append data to an existing BigQuery table from an external source:

  1. query the table for the most updated record: (select max(lastModifiedData) from test.table). Save this data as 'lastMigrationTime';
  2. query the external source for ids for records that changed since after 'lastMigrationTime'
  3. query big Query table for all records except the updated ones: save result to test.tempTable.
  4. move tempTable to table (using delete table,copy tempTable to table,delete tempTable).
  5. Query external source for updated records and load them to test.table

The problem I'm facing is that the original schema of the table contains nested elements. Any query I run will flatten the schema, forcing me to flatten the original schema as well. Another side effect I saw is that column names are turned to lower case.

Is there any way to keep the original schema (mainly the nesting, but also maintaining the case would be nice)?

1
google-bigquery

1 Answers

0
votes

The column name casing issue is a known bug and should be fixed in our next release (hopefully in the next few days).

Preserving column nesting is a high-priority feature request. We're very interested in supporting this, but I don't have any time frame for when it will get done, unfortunately.