Error Exporting from BigQuery to GCS: 'Operation cannot be performed on a nested schema

Question

I encountered an error while trying to execute an ETL task to export data from a BigQuery table to Google Cloud Storage (GCS).

Here is the exact error message:

raise self._exception google.api_core.exceptions.BadRequest: 400 Operation cannot be performed on a nested schema.
Field: _headers; reason: invalid, message: Operation cannot be performed on a nested schema.
Field: _headers

There is task:

{
  "tasks": [
    {
      "name": "my_export_test_task",
      "type": "bigquery_to_gcs",
      "config": {
        "table": "cmc_vthree_events",
        "query": "SELECT _headers.sourceId AS sourceId, _headers.uid AS uid, _headers.messageId AS messageId, _headers.createdAt AS createdAt, data.campaignDataUrl AS campaignDataUrl, data.expirationDate AS expirationDate, data.vehicleInventoryEventsDataUrl AS vehicleInventoryEventsDataUrl, data.publicationDate AS publicationDate, data.vehicleDataUrl AS vehicleDataUrl FROM {{ CURRENT_DATA_PROJECT }}.cmc_vthree_events",
        "use_query": true,
        "destination": ["gs://transit-ope/{{ ds }}/export2.csv"],
        "format": "csv",
        "delimiter": ",",
        "header": true,
        "compression": true
      }
    }
  ]
}

Thank you for your insights and assistance!

Answer 1

unnest the field before you do the export so that is resembles a flat table (csv, etc). Looks like you have an array_agg(struct(.., ..)) fields (called _headers and data) in your source table, see error, e.g.:
_headers.sourceId AS sourceId, _headers.uid AS uid, _headers.messageId AS messageId

unnest these columns and you should be good to go.