BigQuery Client - Class BigQueryClient (1.23.10)

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

Google Cloud BigQuery allows you to create, manage, share and query data.

Find more information at the Google Cloud BigQuery Docs.

Example:

use Google\Cloud\BigQuery\BigQueryClient;

$bigQuery = new BigQueryClient();

Methods

__construct

Create a BigQuery client.

Parameters
Name	Description
`config`	`array` Configuration options.
`↳ apiEndpoint`	`string` The hostname with optional port to use in place of the default service endpoint. Example: `foobar.com` or `foobar.com:1234`.
`↳ projectId`	`string` The project ID from the Google Developer's Console.
`↳ authCache`	`CacheItemPoolInterface` A cache for storing access tokens. Defaults to a simple in memory implementation.
`↳ authCacheOptions`	`array` Cache configuration options.
`↳ authHttpHandler`	`callable` A handler used to deliver Psr7 requests specifically for authentication.
`↳ credentialsFetcher`	`FetchAuthTokenInterface` A credentials fetcher instance.
`↳ httpHandler`	`callable` A handler used to deliver Psr7 requests. Only valid for requests sent over REST.
`↳ keyFile`	`array` The contents of the service account credentials .json file retrieved from the Google Developer's Console. Ex: `json_decode(file_get_contents($path), true)`.
`↳ keyFilePath`	`string` The full path to your service account credentials .json file retrieved from the Google Developers Console.
`↳ requestTimeout`	`float` Seconds to wait before timing out the request. Defaults to `0` with REST and `60` with gRPC.
`↳ retries`	`int` Number of retries for a failed request. Defaults to `3`.
`↳ scopes`	`array` Scopes to be used for the request.
`↳ quotaProject`	`string` Specifies a user project to bill for access charges associated with the request.
`↳ returnInt64AsObject`	`bool` If true, 64 bit integers will be returned as a {@see} object for 32 bit platform compatibility. Defaults to false.
`↳ location`	`string` If provided, determines the default geographic location used when creating datasets and managing jobs. Please note: This is only required for jobs started outside of the US and EU regions. Also, if location metadata has already been fetched over the network it will take precedent over this setting (by calling {@see}, for example).

query

Returns a job configuration to be passed to either BigQueryClient::runQuery() or BigQueryClient::startQuery(). A configuration can be built using fluent setters or by providing a full set of options at once.

Unless otherwise specified, all configuration options will default based on the query job configuration except for configuration.query.useLegacySql, which defaults to false in this client.

Example:

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

// Set create disposition using fluent setters.
$queryJobConfig = $bigQuery->query(
    'SELECT commit FROM `bigquery-public-data.github_repos.commits` LIMIT 100'
)->createDisposition('CREATE_NEVER');

// This is equivalent to the above example, using array configuration
// instead of fluent setters.
$queryJobConfig = $bigQuery->query(
    'SELECT commit FROM `bigquery-public-data.github_repos.commits` LIMIT 100',
    [
        'configuration' => [
            'query' => [
                'createDisposition' => 'CREATE_NEVER'
            ]
        ]
    ]
);

// Set a region to run the job in.
$queryJobConfig = $bigQuery->query(
    'SELECT name FROM `my_project.users_dataset.users` LIMIT 100'
)->location('asia-northeast1');

Parameters

Name Description

query

string

A BigQuery SQL query.

options

array

Configuration options.

↳ configuration

array

Job configuration. Please see the API documentation for the available options.

Properties
Name	Description
`query`	Query job configuration. Please see the documentation for the available options.

Returns
Type	Description
`Google\Cloud\BigQuery\QueryJobConfiguration`

queryConfig

Unless otherwise specified, all configuration options will default based on the query job configuration except for configuration.query.useLegacySql, which defaults to false in this client.

As this method is an alias, please see {@see} for usage examples.

Parameters

Name Description

query

string

A BigQuery SQL query.

options

array

Configuration options.

↳ configuration

array

Job configuration. Please see the API documentation for the available options.

Properties
Name	Description
`query`	Query job configuration. Please see the documentation for the available options.

Returns
Type	Description
`Google\Cloud\BigQuery\QueryJobConfiguration`

runQuery

Runs a BigQuery SQL query in a synchronous fashion.

This method is ideal for queries which return results quickly - otherwise we highly recommend utilizing {@see} as it provides better mechanisms for fine grained control over result polling.

Unless $options.maxRetries is specified, this method will block until the query completes, at which time the result set will be returned.

Queries constructed using standard SQL can take advantage of parameterization.

Refer to the table below for a guide on how parameter types are mapped to their BigQuery equivalents.

PHP Type	BigQuery Data Type
`\DateTimeInterface`	`DATETIME`
{@see}	`BYTES`
{@see}	`DATE`
{@see}	`INT64`
{@see}	`TIME`
{@see}	`TIMESTAMP`
Associative Array	`STRUCT`
Non-Associative Array	`ARRAY`
`float`	`FLOAT64`
`int`	`INT64`
`string`	`STRING`
`resource`	`BYTES`
`bool`	`BOOL`
`object` (Outside types specified above)	ERROR `InvalidArgumentException`

Example:

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

foreach ($queryResults as $row) {
    echo $row['commit'];
}

// Construct a query utilizing named parameters.
$query = 'SELECT commit FROM `bigquery-public-data.github_repos.commits`' .
         'WHERE author.date < @date AND message = @message LIMIT 100';
$queryJobConfig = $bigQuery->query($query)
    ->parameters([
        'date' => $bigQuery->timestamp(new \DateTime('1980-01-01 12:15:00Z')),
        'message' => 'A commit message.'
    ]);
$queryResults = $bigQuery->runQuery($queryJobConfig);

foreach ($queryResults as $row) {
    echo $row['commit'];
}

// Construct a query utilizing positional parameters.
$query = 'SELECT commit FROM `bigquery-public-data.github_repos.commits` WHERE message = ? LIMIT 100';
$queryJobConfig = $bigQuery->query($query)
    ->parameters(['A commit message.']);
$queryResults = $bigQuery->runQuery($queryJobConfig);

foreach ($queryResults as $row) {
    echo $row['commit'];
}

Parameters
Name	Description
`query`	`Google\Cloud\BigQuery\QueryJobConfiguration` A BigQuery SQL query configuration.
`options`	`array` Configuration options.
`↳ maxResults`	`int` The maximum number of rows to return per page of results. Setting this flag to a small value such as 1000 and then paging through results might improve reliability when the query result set is large.
`↳ startIndex`	`int` Zero-based index of the starting row.
`↳ timeoutMs`	`int` How long, in milliseconds, each API call will wait for query results to become available before timing out. Depending on whether the $maxRetries has been exceeded, the results will be polled again after the timeout has been reached. Defaults to `10000` milliseconds (10 seconds).
`↳ maxRetries`	`int` The number of times to poll the Job status, until the job is complete. By default, will poll indefinitely.
`↳ returnRawResults`	`bool` Returns the raw data types returned from BigQuery without converting their values into native PHP types or the custom type classes supported by this library.

Returns
Type	Description
`Google\Cloud\BigQuery\QueryResults`

startQuery

Runs a BigQuery SQL query in an asynchronous fashion.

Queries constructed using standard SQL can take advantage of parameterization. For more details and examples please see {@see}.

Example:

$queryJobConfig = $bigQuery->query(
    'SELECT commit FROM `bigquery-public-data.github_repos.commits` LIMIT 100'
);
$job = $bigQuery->startQuery($queryJobConfig);
$queryResults = $job->queryResults();

foreach ($queryResults as $row) {
    echo $row['commit'];
}

Parameters
Name	Description
`query`	`Google\Cloud\BigQuery\QueryJobConfiguration` A BigQuery SQL query configuration.
`options`	`array` [optional] Configuration options.

Returns
Type	Description
`Google\Cloud\BigQuery\Job`

job

Lazily instantiates a job. There are no network requests made at this point. To see the operations that can be performed on a job please see Job.

Example:

$job = $bigQuery->job('myJobId');

Parameters
Name	Description
`id`	`string` The id of the already run or running job to request.
`options`	`array` Configuration options.
`↳ location`	`string` The geographic location of the job. Required for jobs started outside of the US and EU regions. Defaults to a location specified in the client configuration.

Returns
Type	Description
`Google\Cloud\BigQuery\Job`

jobs

Fetches jobs in the project.

Example:

// Get all jobs with the state of 'done'
$jobs = $bigQuery->jobs([
    'stateFilter' => 'done'
]);

foreach ($jobs as $job) {
    echo $job->id() . PHP_EOL;
}

Parameters
Name	Description
`options`	`array` Configuration options.
`↳ allUsers`	`bool` Whether to display jobs owned by all users in the project. Defaults to `false`.
`↳ maxResults`	`int` Maximum number of results to return per page.
`↳ resultLimit`	`int` Limit the number of results returned in total. Defaults to `0` (return all results).
`↳ pageToken`	`string` A previously-returned page token used to resume the loading of results from a specific point.
`↳ stateFilter`	`string` Filter for job state. Maybe be either `done`, `pending`, or `running`.
`↳ maxCreationTime`	`int` Milliseconds since the POSIX epoch. If set, only jobs created before or at this timestamp are returned.
`↳ minCreationTime`	`int` Milliseconds since the POSIX epoch. If set, only jobs created after or at this timestamp are returned.
`↳ parentJobId`	`string` If set, show only child jobs of the specified parent. Otherwise, show all top-level jobs.

Returns
Type	Description
`Google\Cloud\Core\Iterator\ItemIterator<\google\cloud\bigquery\job>`

dataset

Lazily instantiates a dataset. There are no network requests made at this point. To see the operations that can be performed on a dataset please see Dataset.

If the dataset is owned by a different project than the project used to authenticate the client, provide the project ID as the second argument.

Example:

$dataset = $bigQuery->dataset('myDatasetId');

// Reference a dataset from other project.
$dataset = $bigQuery->dataset('samples', 'bigquery-public-data');

Parameters
Name	Description
`id`	`string` The id of the dataset to request.
`projectId`	`string\|null` The id of the project. Defaults to current project id.

Returns
Type	Description
`Google\Cloud\BigQuery\Dataset`

datasets

Fetches datasets in the project.

Example:

$datasets = $bigQuery->datasets();

foreach ($datasets as $dataset) {
    echo $dataset->id() . PHP_EOL;
}

Parameters
Name	Description
`options`	`array` Configuration options.
`↳ all`	`bool` Whether to list all datasets, including hidden ones. Defaults to `false`.
`↳ maxResults`	`int` Maximum number of results to return per page.
`↳ resultLimit`	`int` Limit the number of results returned in total. Defaults to `0` (return all results).
`↳ pageToken`	`string` A previously-returned page token used to resume the loading of results from a specific point.
`↳ filter`	`string` An expression for filtering the results of the request by label. The syntax is "labels.

Returns
Type	Description
`Google\Cloud\Core\Iterator\ItemIterator<\google\cloud\bigquery\dataset>`

createDataset

Creates a dataset.

Please note that by default the library will not attempt to retry this call on your behalf. Additionally, if a default location is provided in the client configuration it will be used when creating the dataset.

Example:

$dataset = $bigQuery->createDataset('aDataset');

Parameters
Name	Description
`id`	`string` The id of the dataset to create.
`options`	`array` Configuration options.
`↳ metadata`	`array` The available options for metadata are outlined at the Dataset Resource API docs

Returns
Type	Description
`Google\Cloud\BigQuery\Dataset`

runJob

Starts a job in a synchronous fashion, waiting for the job to complete before returning.

Example:

$job = $bigQuery->runJob($jobConfig);
echo $job->isComplete(); // true

Parameters
Name	Description
`config`	`Google\Cloud\BigQuery\JobConfigurationInterface` The job configuration.
`options`	`array` Configuration options.
`↳ maxRetries`	`int` The number of times to retry, checking if the job has completed. Defaults to `100`.

Returns
Type	Description
`Google\Cloud\BigQuery\Job`

startJob

Starts a job in an asynchronous fashion. In this case, it will be required to manually trigger a call to wait for job completion.

Example:

$job = $bigQuery->startJob($jobConfig);

Parameters
Name	Description
`config`	`Google\Cloud\BigQuery\JobConfigurationInterface` The job configuration.
`options`	`array` [optional] Configuration options.

Returns
Type	Description
`Google\Cloud\BigQuery\Job`

bytes

Create a Bytes object.

Example:

$bytes = $bigQuery->bytes('hello world');

Parameter
Name	Description
`value`	`string\|resource\|Psr\Http\Message\StreamInterface` The bytes value.

Returns
Type	Description
`Google\Cloud\BigQuery\Bytes`

date

Create a Date object.

Example:

$date = $bigQuery->date(new \DateTime('1995-02-04'));

Parameter
Name	Description
`value`	`DateTimeInterface` The date value.

Returns
Type	Description
`Google\Cloud\BigQuery\Date`

int64

Create an Int64 object. This can be used to work with 64 bit integers as a string value while on a 32 bit platform.

Example:

$int64 = $bigQuery->int64('9223372036854775807');

Parameter
Name	Description
`value`	`string` The 64 bit integer value in string format.

Returns
Type	Description
`Google\Cloud\Core\Int64`

time

Create a Time object.

Example:

$time = $bigQuery->time(new \DateTime('12:15:00.482172'));

Parameter
Name	Description
`value`	`DateTimeInterface` The time value.

Returns
Type	Description
`Google\Cloud\BigQuery\Time`

timestamp

Create a Timestamp object.

Example:

$timestamp = $bigQuery->timestamp(new \DateTime('2003-02-05 11:15:02.421827Z'));

Parameter
Name	Description
`value`	`DateTimeInterface` The timestamp value.

Returns
Type	Description
`Google\Cloud\BigQuery\Timestamp`

numeric

Create a Numeric object.

Numeric represents a value with a data type of Numeric.

It supports a fixed 38 decimal digits of precision and 9 decimal digits of scale, and values are in the range of -99999999999999999999999999999.999999999 to 99999999999999999999999999999.999999999.

Example:

$numeric = $bigQuery->numeric('99999999999999999999999999999999999999.999999999');

Parameter
Name	Description
`value`	`string\|int\|float` The Numeric value.

Returns
Type	Description
`numeric`

bigNumeric

Create a BigNumeric object.

Numeric represents a value with a data type of BIGNUMERIC.

It supports 76.76 (the 77th digit is partial) decimal digits of precision and 38 decimal digits of scale. Values are in the range of -5.7896044618658097711785492504343953926634992332820282019728792003956564819968E+38 to 5.7896044618658097711785492504343953926634992332820282019728792003956564819967E+38.

Example:

$bigNumeric = $bigQuery->bigNumeric('999999999999999999999999999999999999999999999.99999999999999');

Parameter
Name	Description
`value`	`string\|int\|float` The Numeric value.

Returns
Type	Description
`Google\Cloud\BigQuery\BigNumeric`

geography

Create a Geography object.

Example:

$geography = $bigQuery->geography('POINT(10 20)');

Parameter
Name	Description
`value`	`string` The geography data in WKT format.

Returns
Type	Description
`Google\Cloud\BigQuery\Geography`

getServiceAccount

Get a service account for the KMS integration.

Example:

$serviceAccount = $bigQuery->getServiceAccount();

Parameter
Name	Description
`options`	`array` [optional] Configuration options.

Returns
Type	Description
`string`

copy

Returns a copy job configuration to be passed to either BigQueryClient::runJob() or BigQueryClient::startJob(). A configuration can be built using fluent setters or by providing a full set of options at once.

Example:

$copyJobConfig = $bigQuery->copy()
    ->sourceTable($otherTable)
    ->destinationTable($myTable);

Parameters

Name Description

options

array

Configuration options.

↳ configuration

array

Job configuration. Please see the API documentation for the available options.

Properties
Name	Description
`copy`	Copy job configuration. Please see the documentation for the available options.

Returns
Type	Description
`Google\Cloud\BigQuery\CopyJobConfiguration`

extract

Returns an extract job configuration to be passed to either BigQueryClient::runJob() or BigQueryClient::startJob(). A configuration can be built using fluent setters or by providing a full set of options at once.

Example:

$extractJobConfig = $bigQuery->extract()
    ->sourceTable($table)
    ->destinationUris(['gs://my-bucket/table.csv']);

Parameters

Name Description

options

array

Configuration options.

↳ configuration

array

Job configuration. Please see the API documentation for the available options.

Properties
Name	Description
`extract`	Extract job configuration. Please see the documentation for the available options.

Returns
Type	Description
`Google\Cloud\BigQuery\ExtractJobConfiguration`

load

Returns a load job configuration to be passed to either BigQueryClient::runJob() or BigQueryClient::startJob(). A configuration can be built using fluent setters or by providing a full set of options at once.

Example:

$loadJobConfig = $bigQuery->load()
    ->destinationTable($table)
    ->sourceUris(['gs://my-bucket/table.csv']);

Parameters

Name Description

options

array

Configuration options.

↳ configuration

array

Job configuration. Please see the API documentation for the available options.

Properties
Name	Description
`load`	Load job configuration. Please see the documentation for the available options.

Returns
Type	Description
`Google\Cloud\BigQuery\LoadJobConfiguration`

Constants

VERSION

Value: '1.23.10'

MAX_DELAY_MICROSECONDS

Value: 32000000

SCOPE

Value: 'https://www.googleapis.com/auth/bigquery'

INSERT_SCOPE

Value: 'https://www.googleapis.com/auth/bigquery.insertdata'