2023年如何用PHP访问Google BigQuery？连接报错求助

我尝试用PHP访问Google BigQuery数据库，编写了以下代码：

<?php

echo "hhhh0";

// Check if the autoload file exists
if (!file_exists('vendor/autoload.php')) {
    die('Please run "composer install" to set up the required dependencies.');
}

require 'vendor/autoload.php';

use Google\Cloud\BigQuery\BigQueryClient;

// Replace 'path/to/your/keyfile.json' and 'your-project-id' with actual values
putenv('GOOGLE_APPLICATION_CREDENTIALS=weather_839.json');

// Function to run a BigQuery query and return data
function runBigQueryQuery($query)
{
    $projectId = 'weather-839';

    echo "hhhh4";


    $bigQuery = new BigQueryClient([
        'projectId' => $projectId,
    ]);

    echo "hhhh5";


    $queryJobConfig = $bigQuery->query($query);

    echo "hhhh6";

    $queryJob = $bigQuery->startQuery($queryJobConfig);

    echo "hhhh7";

    $queryJob->waitUntilComplete();

    if ($queryJob->isComplete()) {
        $queryResults = $queryJob->queryResults();
        $data = [];
        foreach ($queryResults as $row) {
            $data[] = $row;
        }
        return $data;
    } else {
        throw new Exception('Query failed to complete.');
    }
}

echo "hhhh1";

// Example: Run a query and fetch data
$query = 'SELECT * FROM weather.CW_5123 LIMIT 1000';

echo "hhhh2";

$result = runBigQueryQuery($query);


echo "hhhh3";


// Output the result as JSON (or process it as needed)
header('Content-Type: application/json');

echo "hhhh4";

echo json_encode($result);

?>

执行代码后出现以下错误：

hhhh0hhhh1hhhh2hhhh4hhhh5hhhh6
Fatal error: Uncaught Google\Cloud\Core\Exception\ServiceException: cURL error 7: Failed to connect to bigquery.googleapis.com port 443: Connection refused in D:\xampp\htdocs\weather839\vendor\google\cloud\Core\src\RequestWrapper.php:397 Stack trace:
#0 D:\xampp\htdocs\weather839\vendor\google\cloud\Core\src\RequestWrapper.php(214): Google\Cloud\Core\RequestWrapper->convertToGoogleException(Object(GuzzleHttp\Exception\ConnectException))
#1 D:\xampp\htdocs\weather839\vendor\google\cloud\Core\src\RestTrait.php(102): Google\Cloud\Core\RequestWrapper->send(Object(GuzzleHttp\Psr7\Request), Array)
#2 D:\xampp\htdocs\weather839\vendor\google\cloud\BigQuery\src\Connection\Rest.php(241): Google\Cloud\BigQuery\Connection\Rest->send('jobs', 'insert', Array)
#3 D:\xampp\htdocs\weather839\vendor\google\cloud\BigQuery\src\BigQueryClient.php(701): Google\Cloud\BigQuery\Connection\Rest->insertJob(Array)
#4 D:\xampp\htdocs\weather839\vendor\google\cloud\BigQuery\src\BigQueryClient.php(399): Google\Cloud\BigQuery\BigQueryClient->startJob(Array, Array)
#5 D:\xampp\htdocs\weather839\test.php(36): Google\Cloud\BigQuery\BigQueryClient->startQuery(Object(Google\Cloud\BigQuery\QueryJobConfiguration))
#6 D:\xampp\htdocs\weather839\test.php(61): runBigQueryQuery('SELECT * FROM w...')
#7 {main} thrown in D:\xampp\htdocs\weather839\vendor\google\cloud\Core\src\RequestWrapper.php on line 397

解决方法 #

• 验证网络连通性：在运行代码的机器上执行curl https://bigquery.googleapis.com，确认能正常建立连接。如果无法访问，说明网络存在限制。
• 配置代理（若需）：如果机器处于需要代理的网络环境，初始化BigQueryClient时添加代理配置：

  $bigQuery = new BigQueryClient([
      'projectId' => $projectId,
      'requestOptions' => [
          'proxy' => 'http://your-proxy-address:port',
          // 若代理需要认证，添加以下内容
          'proxy_auth' => 'username:password'
      ]
  ]);
  

• 检查防火墙/安全组规则：确认本地防火墙或服务器安全组没有阻止443端口的出站请求，确保允许访问bigquery.googleapis.com。
• 确认依赖版本兼容性：执行composer show google/cloud-bigquery查看依赖版本，建议使用官方推荐的稳定版本，避免版本不兼容导致的问题。
• 校验密钥文件路径：确保weather_839.json文件路径正确，可尝试使用绝对路径设置环境变量：

  putenv('GOOGLE_APPLICATION_CREDENTIALS=' . realpath('weather_839.json'));
  

内容的提问来源于stack exchange，提问作者JK2046