BigQuery Client - Class QueryJobConfiguration (1.31.1)

Reference documentation and code samples for the BigQuery Client class QueryJobConfiguration.

Represents a configuration for a query job. For more information on the available settings please see the Jobs configuration API documentation.

Example:

use Google\Cloud\BigQuery\BigQueryClient;

$bigQuery = new BigQueryClient();
$query = $bigQuery->query('SELECT commit FROM `bigquery-public-data.github_repos.commits` LIMIT 100');

Namespace

Google \ Cloud \ BigQuery

Methods

__construct

Parameters
Name Description
mapper ValueMapper

Maps values between PHP and BigQuery.
projectId string

The project's ID.
config array

A set of configuration options for a job.
location string|null

The geographic location in which the job is executed.

allowLargeResults

Sets whether or not the query can produce arbitrarily large result tables at a slight cost in performance.

Only applies to queries performed with legacy SQL dialect and requires a QueryJobConfiguration::destinationTable() to be set.

Example:

$query->allowLargeResults(true);
Parameter
Name Description
allowLargeResults bool

Whether or not to allow large result sets.
Returns
Type Description
QueryJobConfiguration

clustering

See also:

Parameter
Name Description
clustering array

Clustering specification for the table.
Returns
Type Description
QueryJobConfiguration

createDisposition

Sets whether the job is allowed to create new tables.

Example:

$query->createDisposition('CREATE_NEVER');
Parameter
Name Description
createDisposition string

The create disposition. Acceptable values include "CREATE_IF_NEEDED", "CREATE_NEVER".
Returns
Type Description
QueryJobConfiguration

defaultDataset

Sets the default dataset to use for unqualified table names in the query.

Example:

$dataset = $bigQuery->dataset('my_dataset');
$query->defaultDataset($dataset);
Parameter
Name Description
defaultDataset Dataset

The default dataset.
Returns
Type Description
QueryJobConfiguration

destinationEncryptionConfiguration

Sets the custom encryption configuration (e.g., Cloud KMS keys).

Example:

$query->destinationEncryptionConfiguration([
    'kmsKeyName' => 'my_key'
]);
Parameter
Name Description
configuration array

Custom encryption configuration.
Returns
Type Description
QueryJobConfiguration

destinationTable

Sets the table where the query results should be stored. If not set, a new table will be created to store the results. This property must be set for large results that exceed the maximum response size.

Example:

$table = $bigQuery->dataset('my_dataset')
    ->table('my_table');
$query->destinationTable($table);
Parameter
Name Description
destinationTable Table

The destination table.
Returns
Type Description
QueryJobConfiguration

flattenResults

Sets whether or not to flatten all nested and repeated fields in the query results.

Only applies to queries performed with legacy SQL dialect. QueryJobConfiguration::allowLargeResults() must be true if this is set to false.

Example:

$query->useLegacySql(true)
    ->flattenResults(true);
Parameter
Name Description
flattenResults bool

Whether or not to flatten results.
Returns
Type Description
QueryJobConfiguration

maximumBillingTier

Sets the billing tier limit for this job. Queries that have resource usage beyond this tier will fail (without incurring a charge). If unspecified, this will be set to your project default.

Example:

$query->maximumBillingTier(1);
Parameter
Name Description
maximumBillingTier int

The maximum billing tier.
Returns
Type Description
QueryJobConfiguration

maximumBytesBilled

Sets a bytes billed limit for this job. Queries that will have bytes billed beyond this limit will fail (without incurring a charge). If unspecified, this will be set to your project default.

Example:

$query->maximumBytesBilled(3000);
Parameter
Name Description
maximumBytesBilled int

The maximum allowable bytes to bill.
Returns
Type Description
QueryJobConfiguration

parameters

Sets parameters to be used on the query. Only available for standard SQL queries.

For examples of usage please see BigQueryClient::runQuery().

Parameter
Name Description
parameters array

Parameters to use on the query. When providing a non-associative array positional parameters (?) will be used. When providing an associative array named parameters will be used (@name).
Returns
Type Description
QueryJobConfiguration

setParamTypes

Sets the parameter types for positional parameters.

Note, that this is of high importance when an empty array can be passed as a positional parameter, as we have no way of guessing the data type of the array contents.

$queryStr = 'SELECT * FROM `bigquery-public-data.github_repos.commits` ' .
    'WHERE author.time_sec IN UNNEST (?) AND message IN UNNEST (?) AND committer.name = ? LIMIT 10';

$queryJobConfig = $bigQuery->query("")
  ->parameters([[], ["abc", "def"], "John"])
  ->setParamTypes(['INT64']);

In the above example, the first array will have a type of INT64 while the next one will have a type of STRING (even though the second array type is not supplied).

For named params, we can simply call:

$queryJobConfig = $bigQuery->query("")
  ->parameters(['times' => [], 'messages' => ["abc", "def"]])
  ->setParamTypes(['times' => 'INT64']);
Parameter
Name Description
userTypes array

The user supplied types for the positional parameters. This overrides the guessed types that the ValueMapper got from the toParameter method call.
Returns
Type Description
QueryJobConfiguration

priority

Sets a priority for the query.

Example:

$query->priority('BATCH');
Parameter
Name Description
priority string

The priority. Acceptable values include "INTERACTIVE", "BATCH". Defaults to "INTERACTIVE".
Returns
Type Description
QueryJobConfiguration

query

Sets the SQL query.

Example:

$query->query(
    'SELECT commit FROM `bigquery-public-data.github_repos.commits` LIMIT 100'
);
Parameter
Name Description
query string

SQL query text to execute.
Returns
Type Description
QueryJobConfiguration

schemaUpdateOptions

Sets options to allow the schema of the destination table to be updated as a side effect of the query job. Schema update options are supported in two cases: when writeDisposition is "WRITE_APPEND"; when writeDisposition is "WRITE_TRUNCATE" and the destination table is a partition of a table, specified by partition decorators. For normal tables, "WRITE_TRUNCATE" will always overwrite the schema.

Example:

$query->schemaUpdateOptions([
    'ALLOW_FIELD_ADDITION'
]);
Parameter
Name Description
schemaUpdateOptions array

Schema update options. Acceptable values include "ALLOW_FIELD_ADDITION" (allow adding a nullable field to the schema), "ALLOW_FIELD_RELAXATION" (allow relaxing a required field in the original schema to nullable).
Returns
Type Description
QueryJobConfiguration

tableDefinitions

Sets table definitions for querying an external data source outside of BigQuery. Describes the data format, location and other properties of the data source.

Example:

$query->tableDefinitions([
    'autodetect' => true,
    'sourceUris' => [
        'gs://my_bucket/table.json'
    ]
]);
Parameter
Name Description
tableDefinitions array

The table definitions.
Returns
Type Description
QueryJobConfiguration

timePartitioning

Sets time-based partitioning for the destination table.

Only one of timePartitioning and rangePartitioning should be specified.

Example:

$query->timePartitioning([
    'type' => 'DAY'
]);
Parameter
Name Description
timePartitioning array

Time-based partitioning configuration.
Returns
Type Description
QueryJobConfiguration

rangePartitioning

Sets range partitioning specification for the destination table.

Only one of timePartitioning and rangePartitioning should be specified.

Example:

$query->rangePartitioning([
    'field' => 'myInt',
    'range' => [
        'start' => '0',
        'end' => '1000',
        'interval' => '100'
    ]
]);
Parameter
Name Description
rangePartitioning array
Returns
Type Description
QueryJobConfiguration

useLegacySql

Sets whether or not to use legacy SQL dialect. When not set, defaults to false in this client.

Example:

$query->useLegacySql(true);
Parameter
Name Description
useLegacySql bool

Whether or not to use legacy SQL dialect.
Returns
Type Description
QueryJobConfiguration

useQueryCache

See also:

Parameter
Name Description
useQueryCache bool

Whether or not to use the query cache.
Returns
Type Description
QueryJobConfiguration

userDefinedFunctionResources

Sets user-defined function resources used in the query.

Example:

$query->userDefinedFunctionResources([
    ['resourceUri' => 'gs://my_bucket/code_path']
]);
Parameter
Name Description
userDefinedFunctionResources array

User-defined function resources used in the query. This is to be formatted as a list of sub-arrays containing either a key resourceUri or inlineCode.
Returns
Type Description
QueryJobConfiguration

writeDisposition

Sets the action that occurs if the destination table already exists. Each action is atomic and only occurs if BigQuery is able to complete the job successfully. Creation, truncation and append actions occur as one atomic update upon job completion.

Example:

$query->writeDisposition('WRITE_TRUNCATE');
Parameter
Name Description
writeDisposition string

The write disposition. Acceptable values include "WRITE_TRUNCATE", "WRITE_APPEND", "WRITE_EMPTY". Defaults to* "WRITE_EMPTY".
Returns
Type Description
QueryJobConfiguration