Skip to content

Commit ea115d4

Browse files
saranshdhingrabshaffer
saranshdhingra
and
bshaffer
authored
feat(BigQuery): Add ability to specify types of empty arrays (#6275)
Co-authored-by: Brent Shaffer <betterbrent@google.com>
1 parent a0baf3d commit ea115d4

3 files changed

Lines changed: 255 additions & 0 deletions

File tree

‎BigQuery/src/QueryJobConfiguration.php‎

Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,9 @@
1717

1818
namespace Google\Cloud\BigQuery;
1919

20+
use InvalidArgumentException;
21+
use LogicException;
22+
2023
/**
2124
* Represents a configuration for a query job. For more information on the
2225
* available settings please see the
@@ -287,6 +290,82 @@ public function parameters(array $parameters)
287290
return $this;
288291
}
289292

293+
/**
294+
* Sets the parameter types for positional parameters.
295+
* Note, that this is of high importance when an empty array can be passed as
296+
* a positional parameter, as we have no way of guessing the data type of the
297+
* array contents.
298+
*
299+
* ```
300+
* $queryStr = 'SELECT * FROM `bigquery-public-data.github_repos.commits` ' .
301+
* 'WHERE author.time_sec IN UNNEST (?) AND message IN UNNEST (?) AND committer.name = ? LIMIT 10';
302+
*
303+
* $queryJobConfig = $bigQuery->query("")
304+
* ->parameters([[], ["abc", "def"], "John"])
305+
* ->setParamTypes(['INT64']);
306+
* ```
307+
* In the above example, the first array will have a type of INT64
308+
* while the next one will have a type of
309+
* STRING (even though the second array type is not supplied).
310+
*
311+
* For named params, we can simply call:
312+
* ```
313+
* $queryJobConfig = $bigQuery->query("")
314+
* ->parameters(['times' => [], 'messages' => ["abc", "def"]])
315+
* ->setParamTypes(['times' => 'INT64']);
316+
* ```
317+
*
318+
* @param array $userTypes The user supplied types for the positional parameters.
319+
* This overrides the guessed types that the ValueMapper got from the toParameter
320+
* method call.
321+
*
322+
* @return QueryJobConfiguration
323+
*/
324+
public function setParamTypes(array $userTypes)
325+
{
326+
$queryParams = $this->config['configuration']['query']['queryParameters'] ?? null;
327+
$mode = $this->config['configuration']['query']['parameterMode'] ?? null;
328+
329+
if (is_null($queryParams)) {
330+
throw new LogicException('QueryJobConfiguration::parameters must be called before setParamTypes');
331+
}
332+
333+
foreach ($queryParams as $index => &$param) {
334+
// If the user supplied named params, we use the `name` attribute of the parameter
335+
// otherwise we just use the index to map.
336+
$key = $mode === 'named' ? $param['name'] : $index;
337+
$userType = $this->pluck($key, $userTypes, false);
338+
339+
// If the user hasn't supplied this key in the setParamTypes
340+
// call, then we just use the $guessedType(no need to override)
341+
if (is_null($userType)) {
342+
continue;
343+
}
344+
345+
$guessedType = $param['parameterType']['type'];
346+
347+
if ($guessedType === $this->mapper::TYPE_ARRAY) {
348+
$param['parameterType']['arrayType'] = ['type' => $userType];
349+
} else {
350+
$param['parameterType']['type'] = $userType;
351+
}
352+
}
353+
354+
// If the $userTypes still contains a value,
355+
// that means it had an extra key which doesn't exist
356+
// in the parameters() call
357+
if (count($userTypes) > 0) {
358+
throw new InvalidArgumentException(sprintf(
359+
'The key \'%s\' doesn\'t exist in the parameters',
360+
key($userTypes)
361+
));
362+
}
363+
364+
$this->config['configuration']['query']['queryParameters'] = $queryParams;
365+
366+
return $this;
367+
}
368+
290369
/**
291370
* Sets a priority for the query.
292371
*

‎BigQuery/tests/System/LoadDataAndQueryTest.php‎

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,7 @@
2121
use Google\Cloud\BigQuery\Geography;
2222
use Google\Cloud\BigQuery\Numeric;
2323
use Google\Cloud\BigQuery\Timestamp;
24+
use Google\Cloud\Core\Exception\BadRequestException;
2425
use Google\Cloud\Core\ExponentialBackoff;
2526
use GuzzleHttp\Psr7\Utils;
2627

@@ -370,6 +371,58 @@ public function testRunQueryWithPositionalParameters()
370371
$this->assertEquals($expectedRows, $actualRows);
371372
}
372373

374+
public function testRunQueryWithEmptyPositionalArrayParams()
375+
{
376+
$queryStr = sprintf(
377+
'SELECT Name Location FROM `%s.%s` WHERE Age IN UNNEST(?)',
378+
self::$dataset->id(),
379+
self::$table->id()
380+
);
381+
382+
$query = self::$client->query($queryStr)->parameters([
383+
[]
384+
])->setParamTypes(['INT64']);
385+
$results = self::$client->runQuery($query);
386+
$actualRows = iterator_to_array($results->rows());
387+
$this->assertEquals(0, count($actualRows));
388+
389+
390+
// Test the same w/o the call to setParamTypes
391+
$query = self::$client->query($queryStr)->parameters([
392+
[]
393+
]);
394+
395+
// we expect an exception as we didn't use setParamTypes with an empty array
396+
$this->expectException(BadRequestException::class);
397+
$results = self::$client->runQuery($query);
398+
}
399+
400+
public function testRunQueryWithEmptyNamedArrayParams()
401+
{
402+
// we expect an exception as we didn't use setParamTypes with an empty array
403+
$this->expectException(BadRequestException::class);
404+
$queryStr = sprintf(
405+
'SELECT Name Location FROM `%s.%s` WHERE Age IN UNNEST(@ages)',
406+
self::$dataset->id(),
407+
self::$table->id()
408+
);
409+
410+
$query = self::$client->query($queryStr)->parameters([
411+
'ages' => []
412+
])->setParamTypes(['ages' => 'INT64']);
413+
$results = self::$client->runQuery($query);
414+
$actualRows = iterator_to_array($results->rows());
415+
$this->assertEquals(0, count($actualRows));
416+
417+
418+
// Test the same w/o the call to setParamTypes
419+
$query = self::$client->query($queryStr)->parameters([
420+
'ages' => []
421+
]);
422+
423+
self::$client->runQuery($query);
424+
}
425+
373426
public function testStartQueryWithNamedParameters()
374427
{
375428
$query = self::$client->query('SELECT @int as int')

‎BigQuery/tests/Unit/QueryJobConfigurationTest.php‎

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -21,6 +21,8 @@
2121
use Google\Cloud\BigQuery\QueryJobConfiguration;
2222
use Google\Cloud\BigQuery\Table;
2323
use Google\Cloud\BigQuery\ValueMapper;
24+
use InvalidArgumentException;
25+
use LogicException;
2426
use PHPUnit\Framework\TestCase;
2527
use Prophecy\PhpUnit\ProphecyTrait;
2628

@@ -206,4 +208,125 @@ public function parameterDataProvider()
206208
]
207209
];
208210
}
211+
212+
/**
213+
* @dataProvider setParamTypesDataProvider
214+
*/
215+
public function testSetParamTypes(array $values, array $types, array $expectedQuery)
216+
{
217+
$this->expectedConfig['configuration']['query'] = $expectedQuery
218+
+ $this->expectedConfig['configuration']['query'];
219+
220+
$this->config
221+
->parameters($values)
222+
->setParamTypes($types);
223+
224+
$this->assertEquals($this->expectedConfig, $this->config->toArray());
225+
}
226+
227+
public function setParamTypesDataProvider()
228+
{
229+
return [
230+
[
231+
// test for empty array as a named param
232+
['test' => []],
233+
['test' => 'STRING'],
234+
[
235+
'parameterMode' => 'named',
236+
'queryParameters' => [
237+
[
238+
'name' => 'test',
239+
'parameterType' => [
240+
'type' => 'ARRAY',
241+
'arrayType' => [
242+
'type' => 'STRING'
243+
]
244+
],
245+
'parameterValue' => [
246+
'arrayValues' => []
247+
]
248+
]
249+
]
250+
]
251+
],
252+
[
253+
// test for empty positional array
254+
[[]],
255+
['INT64'],
256+
[
257+
'parameterMode' => 'positional',
258+
'queryParameters' => [
259+
[
260+
'parameterType' => [
261+
'type' => 'ARRAY',
262+
'arrayType' => [
263+
'type' => 'INT64'
264+
]
265+
],
266+
'parameterValue' => [
267+
'arrayValues' => []
268+
]
269+
]
270+
]
271+
]
272+
],
273+
[
274+
// test for when the types are explicitly converted
275+
// here we expect the values specified by the user, even though we can guess the type
276+
['param2'],
277+
['INT64'],
278+
[
279+
'parameterMode' => 'positional',
280+
'queryParameters' => [
281+
[
282+
'parameterType' => [
283+
'type' => 'INT64'
284+
],
285+
'parameterValue' => [
286+
'value' => 'param2'
287+
]
288+
]
289+
]
290+
]
291+
]
292+
];
293+
}
294+
295+
/**
296+
* Test for setparamTypes call before parameters call
297+
*/
298+
public function testSetParamTypesThrowsLogicException()
299+
{
300+
$this->expectException(LogicException::class);
301+
$this->config
302+
->setParamTypes(['STRING'])
303+
->parameters(['test']);
304+
}
305+
306+
public function testExtraParamInSetParamTypesThrowsException()
307+
{
308+
$this->expectException(InvalidArgumentException::class);
309+
310+
$this->config
311+
->parameters(['test'])
312+
->setParamTypes(['STRING', 'INT64']);
313+
}
314+
315+
public function testIncorrectNameInSetParamTypesThrowsException()
316+
{
317+
$this->expectException(InvalidArgumentException::class);
318+
319+
$this->config
320+
->parameters(['key1' => 'test'])
321+
->setParamTypes(['key2' => 'INT64']);
322+
}
323+
324+
public function testIncorrectIndexInSetParamTypesThrowsException()
325+
{
326+
$this->expectException(InvalidArgumentException::class);
327+
328+
$this->config
329+
->parameters(['test'])
330+
->setParamTypes([1 => 'INT64']);
331+
}
209332
}

0 commit comments

Comments
 (0)