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

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.

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'