WRITE_TRUNCATE appears to be ignored when passed as an option in a load job

[bencromwell]

The documentation suggests to set:

configuration.load.writeDisposition to WRITE_TRUNCATE.

However, when attempting to set the option for this, nothing works. The following array consists of all the options I've tried:

    $options = [
        'configuration.load.writeDisposition' => 'WRITE_TRUNCATE',
        'configuration.writeDisposition' => 'WRITE_TRUNCATE',
        'writeDisposition' => 'WRITE_TRUNCATE',
        'configuration' => [
            'writeDisposition' => 'WRITE_TRUNCATE',
            'load' => [
                'writeDisposition' => 'WRITE_TRUNCATE',
            ],
        ],
    ];

    /** @var $table \Google\Cloud\BigQuery\Table */
    $table->load($data, $options);

I added each option individually, observing each time that the table merely grew in size.

[bencromwell]

Problem appears to be in the JobConfigurationTrait where it constructs the array. The setting is present in $userDefinedOptions at the point of return, but it is being lost and is not present in the code it's returned to.

I've extracted it into a minimal test case:

<?php

$userDefinedOptions = ['configuration' => ['load' => ['writeDisposition' => 'WRITE_TRUNCATE']]];
$projectId = 'my_project';
$name = 'load';
$config = [
    'destinationTable' => [
        'tableId' => 'my_table',
        'datasetId' => 'data',
        'proejctId' => 'gbq_project',
    ]
];

$result = [
    'projectId' => $projectId,
    'configuration' => [
        $name => $config
    ]
] + $userDefinedOptions;

print_r($result);

Output: https://3v4l.org/eBTgg

The behaviour of + with arrays is not to recursively array merge, I think that's what is missing. So it needs to be as follows:

$result = array_merge_recursive([
    'projectId' => $projectId,
    'configuration' => [
        $name => $config
    ]
], $userDefinedOptions);

[bencromwell]

I can confirm having tested the proposed change in the linked pull request and I now observe the table I'm replacing being truncated and having the appropriate number of records. The behaviour before was for it to always append, growing the table every time.

[dwsupplee]

Hey @bencromwell, thanks for the report!

The following is an example of the intended use, where the jobConfig key is to contain the properties found under configuration.load:

$table->load($data, [
    'jobConfig' => [
        'writeDisposition' => 'WRITE_TRUNCATE'
    ]
]);

I can certainly see where your confusion came up, and you aren't alone in having issues with the configuration. We'll see what we can do to help make things more clear. :)

[bencromwell]

Thanks for getting back to me. I have a few follow up queries.

Firstly, why is it intended to be remapped from configuration.load to jobConfig? A good reason here may negate the rest of what I'm going to say (or it may not!).

My view is that the client should, where possible, match the service it's implementing, to reduce friction for implementers. That is one case for the properties being a direct match for those which are defined in the BigQuery documentation.

Additionally, people are most likely to go straight to the BigQuery documentation to find out what is possible and how to do it, not to the client documentation. As a result of that I feel that even if the documentation for this client included this nuance, it would still be missed up front. The chances are people will most likely arrive there having first experienced a failure.

Another question is does everything nest under jobConfig? In the case of writeDisposition, we're stripping the namespacing away. How much of the namespacing is meant to be removed for each of the options you can pass to a job as listed here? Or does only a subset apply to this particular (load) job, making this a moot point? If only a subset does apply at the moment, can you be sure there won't be a collision in the future as a result of a new option that isn't nested having the same name as something that is?

I don't know if I'm off base here, but if I'm not alone in missing this and with the other things I've raised, perhaps it is worth matching the client to the API?

[bencromwell]

Further to the above,

I've just retested with the original library code and with

    $options = [
        'jobConfig' => [
            'writeDisposition' => 'WRITE_TRUNCATE',
        ],
    ];

I am seeing the same bug - the table is not being truncated. (For clarity, at the time of posting the previous comment I expected changing the options to the above to work.)

[dwsupplee]

Firstly, why is it intended to be remapped from configuration.load to jobConfig?

The thought was that when you are calling $table->load($data) you already know it is a load job, so let's save a few characters and already assume what we are providing options for will be a load job.

My view is that the client should, where possible, match the service it's implementing, to reduce friction for implementers. That is one case for the properties being a direct match for those which are defined in the BigQuery documentation.

We agree! We try our best to make sure we expose every feature of the upstream API while helping to save our users as many keystrokes as possible a long the way. In this case, you can still supply all the options one might need to provide to configuration.load - we just don't expose it in the exact same way the REST API does. There are many times this comes up, and often we find it can help to create easier to interact with abstractions.

Additionally, people are most likely to go straight to the BigQuery documentation to find out what is possible and how to do it, not to the client documentation.

Interesting! That goes against my own experience, I have seen most people use a combination of client documentation and upstream documentation. That's a good point to consider, though, and is something we need to help bridge the gap on. Our upstream documentation often points to language specific examples of how to achieve certain tasks, have you found those snippets helpful in the past?

Another question is does everything nest under jobConfig? In the case of writeDisposition

Or does only a subset apply to this particular (load) job, making this a moot point?

As far as I understand it we should not expect configuration options for load to overlap with options from query, for example.

can you be sure there won't be a collision in the future as a result of a new option that isn't nested having the same name as something that is?

Could you provide an example of what you mean here, and how you would see it being problematic? It sounds to me changes like this would come with other considerations that may be breaking in nature - in which case, we would update our options in the library.

As far as how to solve the problem of providing configuration options, WDYT about some of these solutions?

1. Provide a job builder class, this could implement a fluent design to help make it clear which options are available. The downside here is, we need to manually add options to the class whenever the upstream API changes.

$job = new JobBuilder();
$job->writeDisposition($writeDisposition)
    ->anotherExampleOption($value);
$table->load($data, [
    'jobConfig' => $job
]);

2. Validate the request before delivering it, this may prove especially helpful when there are so many options to provide for a job. In this case, if one were to provide options of ['configuration' => ['load' => []]] you would immediately get an error with some direction of how to address it before we even hit the network.

3. If we had an example of how to provide writeDisposition in our source and library documentation, for example, do you think it would be valuable?

RE: The bug you're seeing, the following code sample successfully truncated the target table when I ran it. What does the output of the returned job look like for you after it has completed?

<?php

require 'vendor/autoload.php';

use Google\Cloud\BigQuery\BigQueryClient;

$bq = new BigQueryClient();
$table = $bq->dataset('dataset')
    ->table('table');

$job = $table->load(fopen('data.csv', 'r'), [
    'jobConfig' => [
        'writeDisposition' => 'WRITE_TRUNCATE'
    ]
]);

[dwsupplee]

Hey @bencromwell, just wanted to follow up on this really quick. Are you still having trouble with the snippet provided above?

[bencromwell]

Hi @dwsupplee. Sorry I've not come back to you yet on it - I've not been able to work on it this week. Pretty sure it's still an issue but I'll need to extract the code down to a minimal repeatable example etc.

[jdpedrie]

Hey @bencromwell, wondering if you'd had a chance to confirm whether @dwsupplee's snippet made sense. :)

[jdpedrie]

Closed by #686.