PHP BigQuery Client长任务401认证错误及令牌刷新求助

问题根源 #

使用服务账号密钥初始化的BigQueryClient，默认获取的OAuth2令牌有效期为1小时。当循环拉取大表数据的时间超过1小时，旧令牌失效，而客户端实例不会自动刷新令牌，导致触发401认证错误。

解决方案1：定期重建BigQueryClient实例 #

在循环过程中，每隔55分钟（提前于1小时令牌有效期）重新初始化BigQueryClient，获取新的有效令牌。

修改后的代码示例：

// 封装客户端创建方法
private function createBigQueryClient(array $keyFile, string $projectId): BigQueryClient
{
    return new BigQueryClient([
        'keyFile' => $keyFile,
        'projectId' => $projectId
    ]);
}

// 初始化初始客户端
$bigQuery = $this->createBigQueryClient($array[$key], $this->projectId);
$info = $bigQuery->dataset($this->datasetId)->table($tableName)->info();

$this->info($this->messagePrepend . "Rows: " . number_format($info['numRows']) . ", Chunk size: " . number_format($this->chunkSize) . " – Processing.." . PHP_EOL);

$startTime = Carbon::now();
$file = fopen(storage_path("tmp/{$tableName}.csv"), 'w');
fputcsv($file, $this->tableHeaders);

$orderBy = match ($keyword) {
    default => "id"
};

// 设置令牌刷新间隔：55分钟（提前5分钟避免过期）
$refreshInterval = 55 * 60;

while ($info['numRows'] > $offset) {
    // 检查是否需要刷新客户端
    if (Carbon::now()->diffInSeconds($startTime) >= $refreshInterval) {
        $bigQuery = $this->createBigQueryClient($array[$key], $this->projectId);
        $startTime = Carbon::now();
    }

    $config = $bigQuery->query("SELECT * FROM {$this->datasetId}.{$tableName} ORDER BY {$orderBy} ASC LIMIT {$this->chunkSize} OFFSET {$offset}");
    $job = $bigQuery->startQuery($config);
    $queryResults = collect($job->queryResults());

    $queryResults->map(function ($row) use ($file) {
        $line = [/* 构造行数据的逻辑 */];
        fputcsv($file, $line);
    });

    $offset += $this->chunkSize;
}

fclose($file);

解决方案2：使用可自动刷新的凭据 #

借助Google Auth库的ServiceAccountCredentials类，手动创建支持自动刷新的凭据注入到BigQueryClient，令牌过期时会自动刷新。

首先确保安装依赖：composer require google/auth，然后修改代码：

use Google\Auth\Credentials\ServiceAccountCredentials;
use Google\Cloud\BigQuery\BigQueryClient;

// 初始化可自动刷新的凭据
$credentials = new ServiceAccountCredentials(
    'https://www.googleapis.com/auth/bigquery',
    $array[$key]
);

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

// 后续循环逻辑保持不变，客户端会自动处理令牌刷新
$info = $bigQuery->dataset($this->datasetId)->table($tableName)->info();

$this->info($this->messagePrepend . "Rows: " . number_format($info['numRows']) . ", Chunk size: " . number_format($this->chunkSize) . " – Processing.." . PHP_EOL);

$startTime = Carbon::now();
$file = fopen(storage_path("tmp/{$tableName}.csv"), 'w');
fputcsv($file, $this->tableHeaders);

$orderBy = match ($keyword) {
    default => "id"
};

while ($info['numRows'] > $offset) {
    $config = $bigQuery->query("SELECT * FROM {$this->datasetId}.{$tableName} ORDER BY {$orderBy} ASC LIMIT {$this->chunkSize} OFFSET {$offset}");
    $job = $bigQuery->startQuery($config);
    $queryResults = collect($job->queryResults());

    $queryResults->map(function ($row) use ($file) {
        $line = [/* 构造行数据的逻辑 */];
        fputcsv($file, $line);
    });

    $offset += $this->chunkSize;
}

fclose($file);

额外优化：替换OFFSET分页提升性能 #

对于超大规模表，OFFSET分页会随着偏移量增大导致查询效率急剧下降，建议改用游标分页（pageToken）配合自动刷新方案：

// 初始查询获取第一页及游标
$config = $bigQuery->query("SELECT * FROM {$this->datasetId}.{$tableName} ORDER BY {$orderBy} ASC LIMIT {$this->chunkSize}");
$job = $bigQuery->startQuery($config);
$queryResults = $job->queryResults();

do {
    // 处理当前页数据
    foreach ($queryResults as $row) {
        $line = [/* 构造行数据的逻辑 */];
        fputcsv($file, $line);
    }
    // 获取下一页游标
    $pageToken = $queryResults->pageToken();
    // 存在游标则继续查询下一页
    if ($pageToken) {
        $queryResults = $job->queryResults(['pageToken' => $pageToken]);
    }
} while ($pageToken);

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