/** * Runs a BigQuery SQL query in a synchronous fashion. Rows are returned * immediately as long as the query completes within a specified timeout. In * the case that the query does not complete in the specified timeout, you * are able to poll the query's status until it is complete. * * Queries constructed using * [standard SQL](https://cloud.google.com/bigquery/docs/reference/standard-sql/) * can take advantage of parametriziation. * * 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 Google\Cloud\BigQuery\Bytes} | `BYTES` | * | {@see Google\Cloud\BigQuery\Date} | `DATE` | * | {@see Google\Cloud\Int64} | `INT64` | * | {@see Google\Cloud\BigQuery\Time} | `TIME` | * | {@see Google\Cloud\BigQuery\Timestamp} | `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: * ``` * $queryResults = $bigQuery->runQuery('SELECT commit FROM [bigquery-public-data:github_repos.commits] LIMIT 100'); * * $isComplete = $queryResults->isComplete(); * * while (!$isComplete) { * sleep(1); // let's wait for a moment... * $queryResults->reload(); // trigger a network request * $isComplete = $queryResults->isComplete(); // check the query's status * } * * foreach ($queryResults->rows() 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'; * $queryResults = $bigQuery->runQuery($query, [ * 'parameters' => [ * 'date' => $bigQuery->timestamp(new \DateTime('1980-01-01 12:15:00Z')), * 'message' => 'A commit message.' * ] * ]); * * $isComplete = $queryResults->isComplete(); * * while (!$isComplete) { * sleep(1); // let's wait for a moment... * $queryResults->reload(); // trigger a network request * $isComplete = $queryResults->isComplete(); // check the query's status * } * * foreach ($queryResults->rows() 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'; * $queryResults = $bigQuery->runQuery($query, [ * 'parameters' => ['A commit message.'] * ]); * * $isComplete = $queryResults->isComplete(); * * while (!$isComplete) { * sleep(1); // let's wait for a moment... * $queryResults->reload(); // trigger a network request * $isComplete = $queryResults->isComplete(); // check the query's status * } * * foreach ($queryResults->rows() as $row) { * echo $row['commit']; * } * ``` * * @see https://cloud.google.com/bigquery/docs/reference/v2/jobs/query Query API documentation. * * @param string $query A BigQuery SQL query. * @param array $options [optional] { * Configuration options. * * @type int $maxResults 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. * @type array $defaultDataset Specifies the default datasetId and * projectId to assume for any unqualified table names in the * query. If not set, all table names in the query string must be * qualified in the format 'datasetId.tableId'. * @type int $timeoutMs How long to wait for the query to complete, in * milliseconds. **Defaults to** `10000` milliseconds (10 seconds). * @type bool $useQueryCache Whether to look for the result in the query * cache. * @type bool $useLegacySql Specifies whether to use BigQuery's legacy * SQL dialect for this query. * @type array $parameters Only available for standard SQL queries. * When providing a non-associative array positional parameters * (`?`) will be used. When providing an associative array * named parameters will be used (`@name`). * } * @return QueryResults */ public function runQuery($query, array $options = []) { if (isset($options['parameters'])) { $options += $this->formatQueryParameters($options['parameters']); unset($options['parameters']); } $response = $this->connection->query(['projectId' => $this->projectId, 'query' => $query] + $options); return new QueryResults($this->connection, $response['jobReference']['jobId'], $this->projectId, $response, $options, $this->mapper); }
/** * Runs a BigQuery SQL query in a synchronous fashion. Rows are returned * immediately as long as the query completes within a specified timeout. In * the case that the query does not complete in the specified timeout, you * are able to poll the query's status until it is complete. * * Example: * ``` * $queryResults = $bigQuery->runQuery('SELECT * FROM [bigquery-public-data:usa_names.usa_1910_2013]'); * * $isComplete = $queryResults->isComplete(); * * while (!$isComplete) { * sleep(1); // let's wait for a moment... * $queryResults->reload(); // trigger a network request * $isComplete = $queryResults->isComplete(); // check the query's status * } * * foreach ($queryResults->rows() as $row) { * echo $row['name']; * } * ``` * * @see https://cloud.google.com/bigquery/docs/reference/v2/jobs/query Query API documentation. * * @param string $query A BigQuery SQL query. * @param array $options [optional] { * Configuration options. * * @type int $maxResults 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. * @type array $defaultDataset Specifies the default datasetId and * projectId to assume for any unqualified table names in the * query. If not set, all table names in the query string must be * qualified in the format 'datasetId.tableId'. * @type int $timeoutMs How long to wait for the query to complete, in * milliseconds. **Defaults to** `10000` milliseconds (10 seconds). * @type bool $useQueryCache Whether to look for the result in the query * cache. * @type bool $useLegacySql Specifies whether to use BigQuery's legacy * SQL dialect for this query. * } * @return QueryResults */ public function runQuery($query, array $options = []) { $response = $this->connection->query(['projectId' => $this->projectId, 'query' => $query] + $options); return new QueryResults($this->connection, $response['jobReference']['jobId'], $this->projectId, $response, $options); }