aws的athena的使用-php
laravel自带支持aws的s3,故可以直接使用AthenaClient.
2_1先new AthenaClient,初始化参数,如key,secret :
$athena_client = new AthenaClient([
'version' => 'latest',
'region' => env('AWS_DEFAULT_REGION'),
'credentials' => [
'key' => env('AWS_ACCESS_KEY_ID'),
'secret' => env('AWS_SECRET_ACCESS_KEY'),
],
]);
2_2运行SQL,获取到QueryExecutionId:
//运行查询语句.指定database,sql,output
$res = $athena_client->startQueryExecution([
'QueryExecutionContext' => [
'Database' => 'yoywow_dws',//数据库
],
//sql查询语句
'QueryString' => "select * from dws_new_industry_bi_hot_talents where dt='20230715' limit 100",
'ResultConfiguration' => [
'EncryptionConfiguration' => ['EncryptionOption' => 'SSE_S3'],
//运行结果保存的目录示例:s3://testforanalytist/123654/83002323-42323-332323-332d-3232323dafsa.csv
'OutputLocation' => 's3://testforanalytist/123654/',
],
]);
$query_execution_id = $res->toArray()['QueryExecutionId'];
2_3_1:根据QueryExecutionId获取运行状态:
$query_execution = $athena_client->getQueryExecution(['QueryExecutionId' => '840f2c3d-1790-45b7-942e-4e02a5815cc2']);
//根据id获取运行状态,当State=='SUCCESSED'则,表示已经运行成功,并自动保存到了,创建时指定的目录+queryExecutionId.csv中了.
dd($query_execution->get('QueryExecution')['Status']);
运行成功后,自动生成的结果:
2_3_2:还根据QueryExecutionId获取数据集,并自定义下载数据到自定义的csv中,此方法不如直接采用2_3_1的结果来的方便和快捷.
$param = [
'QueryExecutionId' => '2c43e5ce-ac95-4e59-bc72-a922eb203eb2',
//一次最多获取n条
'MaxResults' => 10,
];
//循环获取结果
$datas = [];
do {
$result = $athena_client->getQueryResults($param);
$token = $result->get('NextToken');
if ($token) {
$param['NextToken'] = $token;
}
$data = $result->get('ResultSet');
foreach ($data['Rows'] as $key => $row) {
$datas[] = array_column($row['Data'], 'VarCharValue');
}
} while ($token);
//导入到csv中.
$csv = Writer::createFromString();
$csv->setOutputBOM(Reader::BOM_UTF8);
$csv->insertAll($datas);
$put_res = Storage::disk('local')->put('test.csv', $csv->getContent());
dd($put_res);
3:官网athena支持的所有方法列表:
文档链接:https://docs.aws.amazon.com/aws-sdk-php/v3/api/class-Aws.Athena.AthenaClient.html
4:其余参考链接:
https://stackoverflow.com/questions/75594678/athena-query-using-aws-php-sdk
5:在页面中直接使用athena查询:
