Query a public dataset with the BigQuery PHP client library

Open the Cloud Shell Editor

1. In Cloud Shell, create a new PHP project and file:

   mkdir bigquery-php-quickstart \
       && touch \
       bigquery-php-quickstart/app.php
   

   This command creates a PHP project that's named bigquery-php-quickstart and a file that's named app.php.

2. Open the Cloud Shell Editor:

   cloudshell workspace bigquery-php-quickstart
   

To learn how to prepare files for queries, click Next.

Prepare files for queries

1. To open a terminal in the Cloud Shell Editor, click Open Terminal.

2. Open your project directory:

   cd bigquery-php-quickstart
   

3. Install the BigQuery client library for PHP:

   composer require google/cloud-bigquery
   

   The output is similar to the following. Several lines are omitted to simplify the output.

   Running composer update google/cloud-bigquery
   Loading composer repositories with package information
   Updating dependencies
   ...
   No security vulnerability advisories found
   Using version ^1.24 for google/cloud-bigquery
   

To learn how to query a public dataset in BigQuery, click Next.

Query a public dataset in BigQuery

1. Click Open Editor.

2. In the Explorer pane, locate your BIGQUERY-PHP-QUICKSTART project.

3. Click the app.php file to open it.

4. To create a query against the bigquery-public-data.stackoverflow dataset that returns the top 10 most viewed Stack Overflow pages and their view counts, copy the following code into the app.php file:

   <?php
   # ...
   
   require __DIR__ . '/vendor/autoload.php';
   
   use Google\Cloud\BigQuery\BigQueryClient;
   
   
   $bigQuery = new BigQueryClient();
   $query = <<<ENDSQL
   SELECT
     CONCAT(
       'https://stackoverflow.com/questions/',
       CAST(id as STRING)) as url,
     view_count
   FROM `bigquery-public-data.stackoverflow.posts_questions`
   WHERE tags like '%google-bigquery%'
   ORDER BY view_count DESC
   LIMIT 10;
   ENDSQL;
   $queryJobConfig = $bigQuery->query($query);
   $queryResults = $bigQuery->runQuery($queryJobConfig);
   
   if ($queryResults->isComplete()) {
       $i = 0;
       $rows = $queryResults->rows();
       foreach ($rows as $row) {
           printf('--- Row %s ---' . PHP_EOL, ++$i);
           printf('url: %s, %s views' . PHP_EOL, $row['url'], $row['view_count']);
       }
       printf('Found %s row(s)' . PHP_EOL, $i);
   } else {
       throw new Exception('The query failed to complete');
   }

5. Click Open Terminal.

6. In the terminal, run the app.php script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.

   php app.php
   

   The result is similar to the following:

   --- Row 1 ---
   url: https://stackoverflow.com/questions/35159967, 170023 views
   --- Row 2 ---
   url: https://stackoverflow.com/questions/22879669, 142581 views
   --- Row 3 ---
   url: https://stackoverflow.com/questions/10604135, 132406 views
   --- Row 4 ---
   url: https://stackoverflow.com/questions/44564887, 128781 views
   --- Row 5 ---
   url: https://stackoverflow.com/questions/27060396, 127008 views
   --- Row 6 ---
   url: https://stackoverflow.com/questions/12482637, 120766 views
   --- Row 7 ---
   url: https://stackoverflow.com/questions/20673986, 115720 views
   --- Row 8 ---
   url: https://stackoverflow.com/questions/39109817, 108368 views
   --- Row 9 ---
   url: https://stackoverflow.com/questions/11057219, 105175 views
   --- Row 10 ---
   url: https://stackoverflow.com/questions/43195143, 101878 views
   Found 10 row(s)
   

You have successfully queried a public dataset with the BigQuery PHP client library.

To avoid incurring charges to your account and learn about next steps, click Next.