使用AWS PHP SDK执行Athena查询返回空结果，求代码排查

我尝试使用AWS PHP SDK运行Athena查询，但得到的结果里没有实际的查询数据，只有QueryExecutionId。我的代码如下：

require '/usr/bin/vendor/autoload.php';

use Aws\Athena\AthenaClient;


$options = [
    'version' => 'latest',
    'region'  => 'eu-west-1',
    'credentials' => [
       'key'    => 'mykey',
       'secret' => 'mysecret']
 ];
 $athenaClient = new Aws\Athena\AthenaClient($options);

$result = $athenaClient->startQueryExecution([
    'QueryExecutionContext' => [
        'Database' => 'mydbname',
    ],
    'QueryString' => 'select * from mytable limit 3', // REQUIRED
    'ResultConfiguration' => [ // REQUIRED
        'EncryptionConfiguration' => [
            'EncryptionOption' => 'SSE_S3' // REQUIRED
        ],
        'OutputLocation' => 's3://mybucket/', // REQUIRED
    ],
]);

print_r($result);

执行后得到的结果：

Aws\Result Object ( [data:Aws\Result:private] => Array ( [QueryExecutionId] => 21221212121212121 [@metadata] => Array ( [statusCode] => 200 [effectiveUri] => https://athena.eu-west-1.amazonaws.com [headers] => Array ( [date] => Tue, 28 Feb 2023 16:09:57 GMT [content-type] => application/x-amz-json-1.1 [content-length] => 59 [connection] => keep-alive [x-amzn-requestid] => 3232323232323232 ) [transferStats] => Array ( [http] => Array ( [0] => Array ( ) ) ) ) ) [monitoringEvents:Aws\Result:private] => Array ( ) )

你的代码本身没有错误，但startQueryExecution方法仅负责提交Athena查询任务，不会直接返回查询结果，只会返回用于跟踪任务的QueryExecutionId。要获取实际的查询数据，需要完成以下流程：

• 提交查询任务：这是你当前代码完成的步骤，得到QueryExecutionId用于后续跟踪。
• 轮询查询状态：调用getQueryExecution方法，传入QueryExecutionId，直到任务状态变为SUCCEEDED、FAILED或CANCELLED。Athena查询是异步执行的，必须等待任务完成才能获取结果。
• 获取查询结果：当任务状态为SUCCEEDED时，调用getQueryResults方法获取实际的查询数据。

修正后的完整代码示例：

require '/usr/bin/vendor/autoload.php';

use Aws\Athena\AthenaClient;
use Aws\Exception\AwsException;

$options = [
    'version' => 'latest',
    'region'  => 'eu-west-1',
    'credentials' => [
        'key'    => 'mykey',
        'secret' => 'mysecret'
    ]
];
$athenaClient = new AthenaClient($options);

// 提交查询任务
$result = $athenaClient->startQueryExecution([
    'QueryExecutionContext' => [
        'Database' => 'mydbname',
    ],
    'QueryString' => 'select * from mytable limit 3',
    'ResultConfiguration' => [
        'EncryptionConfiguration' => [
            'EncryptionOption' => 'SSE_S3'
        ],
        'OutputLocation' => 's3://mybucket/',
    ],
]);

$executionId = $result['QueryExecutionId'];

// 轮询查询状态
$status = 'RUNNING';
while (in_array($status, ['RUNNING', 'QUEUED'])) {
    $executionInfo = $athenaClient->getQueryExecution([
        'QueryExecutionId' => $executionId
    ]);
    $status = $executionInfo['QueryExecution']['Status']['State'];
    
    if ($status === 'FAILED') {
        $error = $executionInfo['QueryExecution']['Status']['StateChangeReason'];
        die("查询失败：{$error}");
    } elseif ($status === 'CANCELLED') {
        die("查询已取消");
    }
    
    // 间隔1秒后再次查询状态，避免频繁请求
    sleep(1);
}

// 获取并打印查询结果
if ($status === 'SUCCEEDED') {
    $queryResults = $athenaClient->getQueryResults([
        'QueryExecutionId' => $executionId
    ]);
    
    print_r($queryResults['ResultSet']['Rows']);
}

额外说明 #

• 确保AWS账号拥有Athena查询权限，以及访问指定S3输出桶的权限。
• 若查询结果数据量较大，getQueryResults会返回分页数据，此时需要使用NextToken参数循环获取所有结果。
• 可根据查询的复杂度调整轮询间隔，减少不必要的API调用。

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