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'); 

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); 

See also:

• Introduction to Clustered Tables

Sets whether the job is allowed to create new tables.

Example:

 $query->createDisposition('CREATE_NEVER'); 

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

Example:

 $dataset = $bigQuery->dataset('my_dataset');
$query->defaultDataset($dataset); 

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

Example:

 $query->destinationEncryptionConfiguration([
    'kmsKeyName' => 'my_key'
]); 

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); 

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); 

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); 

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); 

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

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

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']); 

Sets a priority for the query.

Example:

 $query->priority('BATCH'); 

Sets the SQL query.

Example:

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

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'
]); 

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'
    ]
]); 

Sets time-based partitioning for the destination table.

Only one of timePartitioning and rangePartitioning should be specified.

Example:

 $query->timePartitioning([
    'type' => 'DAY'
]); 

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'
    ]
]); 

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

Example:

 $query->useLegacySql(true); 

See also:

• Using cached results

Sets user-defined function resources used in the query.

Example:

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

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');