GCP Workflows - Set destination table?

Question

I am using Google Cloud Workflows using the googleapis.bigquery.v2. jobs.query to transform data on BigQuery. I am able to run my Workflows correctly and perform queries on a given tables but what I am trying to do now is to store the result of of query on a different dataset and table. There is nothing about destination table on the googleapis.bigquery.v2.jobs.query documentation. Based on this SO question, I found that it is possible to set destination dataset and table but confused if I can do something similar on my .yaml

example I found on SO

"configuration": {
  "query": {
    "query": "select count(*) from foo.bar",
    "destinationTable": {
      "projectId": "my_project",
      "datasetId": "my_dataset",
      "tableId": "my_table"
    },
    "createDisposition": "CREATE_IF_NEEDED",
    "writeDisposition": "WRITE_APPEND",
  }
}

my Workflows: workflows-1.yaml

- getCurrentTime:
    call: googleapis.bigquery.v2.jobs.query
    args:
        projectId: survivor-316012
        body:
            connectionProperties: 
            createSession: false
            defaultDataset:
                datasetId: europe
                projectId: survivor-316012
            dryRun: false
            kind: ...
            location: ...
            maxResults: 32
            preserveNulls: false
            query: "SELECT * FROM `survivor-316012.europe.france`"
            queryParameters: []
            timeoutMs: 10000
            useLegacySql: false
            useQueryCache: false
    result: currentDateTime

Answer 1

1. The googleapis.bigquery.v2.jobs.query endpoint doesn't offer creating a destination table via API/YAML syntax, but you can write your query to create the table

   create table mytable as (select ........)

2. To use the destinationTable API setup you need to use a different endpoint: googleapis.bigquery.v2.jobs.insert which has a lot more to configure, there is a full example on this linked article.

Essentally you need the query section from it.

- insert:
    call: googleapis.bigquery.v2.jobs.insert
    args:
        projectId: ...
        body:
            configuration:
                query:
                    allowLargeResults: ...
                    clustering:
                    connectionProperties: ...
                    createDisposition: ...
                    createSession: ...
                    defaultDataset:
                        datasetId: ...
                        projectId: ...
                    destinationEncryptionConfiguration:
                    destinationTable: <YOUR SECTION> 
                    flattenResults: ...
                    maximumBillingTier: ...
                    maximumBytesBilled: ...
                    parameterMode: ...
                    preserveNulls: ...
                    priority: ...
                    query: ...
                    queryParameters: ...
                    rangePartitioning:
                    schemaUpdateOptions: ...
                    tableDefinitions: ...
                    timePartitioning:
                    useLegacySql: ...
                    useQueryCache: ...
                    userDefinedFunctionResources: ...
                    writeDisposition: ...
    result: insertResult