[Breaking Change] Don't remove destinationTable attribute in create_job

[dinigo]

What happened:

I run a job with a query and a destination table and the table is not generated from the file.

bq_client.create_job({
    'query': {
        'query': rendered_query,
        'destinationTable': {
            'projectId': GCP_PROJECT,
            'datasetId': DATASET_ID,
            'tableId': TRANSFORMED_TABLE_ID
        },
        'writeDisposition': 'WRITE_TRUNCATE'
})

My code executes successfully and produces the following job in the console

How to fix it

There is a line where the destinationTable is removed from the job config. Removing the line that deletes the property in the job configuration will do.

python-bigquery/google/cloud/bigquery/client.py

Line 1729 in 530e1e8

_del_sub_prop(copy_config, ["query", "destinationTable"])

Can anyone explain why is the destinationTable property removed?

In the meanwhile I'm opening a PR

[plamut]

From the description of the ticket when this was added:

For example, BigQuery automatically populates a destination table if not set. In this case, the destination table should not be part of the retried request. In my opinion, this kind of logic is outside the scope of the client libraries, as it's not clear when destination table needs to be cleared just from the job resource.

While the client library always removes the destination table, that might not be the correct in all cases, but then again, it might be difficult to determine in every case what action to take.

@tswast Do you remember the details and what the backend expects? Is it possible/feasible for the client to be in sync with the backend without duplicating the latter's logic?

[tswast]

@tswast Do you remember the details and what the backend expects? Is it possible/feasible for the client to be in sync with the backend without duplicating the latter's logic?

The main problem is permissions. When the destination table is automatically populated, it creates a table in a special dataset where data automatically expires and the customer is not charged for storage. The customer does not have permission to write directly to such a table.

For example, I just ran a query and it wrote the results to the temporary table swast-scratch:_63cfa399614a54153cc386c27d6c0c6fdb249f9e.anonb14b7884b8147f85ae0731c3a6f8d0026af97ab

In every case I've seen, the temporary table dataset_id begins with _ and the table name begins with anon.

We could possibly only strip the destination table in these cases. There's still a risk of false positives, but it should be much less likely.

[plamut]

OK thanks, this sounds like a better heuristic. If it turns out that it doesn't work well enough in practice, it should be easy to modify/remove, if necessary.

[dinigo]

I might be failing to fully understand why the python API/library works like it does because I lack this context you are talking about.

What I want to achieve is the same functionality as expected and provided by other ways I interact with bigquery:

• REST API and gRPC API
• NodeJs library
• Golang library
• Airflow with the BigQueryInsertJobOperator with the same methodcreateJob

@tswast can you help me understand this use-case with the temp table? It looks to me like a client logic. But again, I think I'm failing to fully grasp the example.

I can seamlessly migrate mi code between several languages simply moving the job configuration (dictionary/json/struct) and I don't understand why the python library is an exception.

I don't want to be rude by any means. And I respect your work, which is fantastic.

[tswast]

@dinigo Full context is here: googleapis/google-cloud-python#5555 and here: #14

You're probably right that the correct choice back then would have been to keep create_job simple and perhaps had a helper to "reset" any server-side job configuration so that it can retried. Unfortunately we did not make that choice, so we need to avoid breaking that "retry this job, please" use-case.

[dinigo]

Thanks @tswast ! I get it now. Also You have to keep the API backwards compatible ;)

No problem. We will use a fork and rebase regularly since we need and expect this funcionality.

I would like though to have this feature integrated at some point in the future. If you could tag it as such. Thanks again

[tswast]

@jimfulton I think if we get retryable queries working in #539, we should be able to remove this odd behavior of create_job.

Also, perhaps if create_job is called with any of our *Job classes, it uses the original request that we cache rather than trying to figure out which properties on the response resource we can send back?

[jimfulton]

Some notes:

• The original use case that drove adding this was retry: BigQuery: no public method to retry/resubmit an existing job google-cloud-python#5555, but feat: retry failed query jobs in result() #837 should remove the need to retry jobs.
• Therefore, after feat: retry failed query jobs in result() #837 lands, we'll stop removing destination from the config used for query jobs.
• This is blocked pending feat: retry failed query jobs in result() #837.

[tswast]

Closed by #891

Will be released in 3.0.0