RSS

Tag Archives: getQueryExecution

How to fetch data from AWS Athena using PHP

// https://aws.amazon.com/documentation/sdk-for-php/

 

<?php
require_once ‘aws/aws-autoloader.php’;

use Aws\Athena\AthenaClient;

class CLSAthena {
public $Client = ”;

function createAthenaObject($version, $region, $key, $secret){
try {

$this->Client = AthenaClient::factory(array(
‘version’ => $version,
‘region’ => $region,
‘credentials’ => array(
‘key’ => $key,
‘secret’ => $secret,
)
));

}
catch (AthenaException $e) {
// Catch an S3 specific exception.
echo $e->getMessage();
} catch (AwsException $e) {
// This catches the more generic AwsException. You can grab information
// from the exception using methods of the exception object.
echo $e->getAwsRequestId() . “\n”;
echo $e->getAwsErrorType() . “\n”;
echo $e->getAwsErrorCode() . “\n”;
}

}

function getData($db_name, $table_name, $query = ”, $result_logs)
{
try {

$result1 = $this->Client->StartQueryExecution(
array(
“QueryExecutionContext”=> array( “Database”=> $db_name ),
“QueryString”=> $query,
“ResultConfiguration”=> array(
“EncryptionConfiguration”=> array(“EncryptionOption”=> “SSE_S3”),
“OutputLocation”=> “s3://”.$result_logs
)
)
);

$QueryExecutionId = $result1->get(‘QueryExecutionId’);

$this->waitForQueryToComplete($QueryExecutionId);

$result1 = $this->Client->GetQueryResults(array(
‘QueryExecutionId’ => $QueryExecutionId, // REQUIRED
‘MaxResults’ => 500
));

$data = $result1->get(‘ResultSet’);
$res = $data[‘Rows’];

while (true) {

if($result1->get(‘NextToken’) == null)
{
break;
}

$result1 = $this->Client->GetQueryResults(array(
‘QueryExecutionId’ => $QueryExecutionId, // REQUIRED
‘NextToken’ => $result1->get(‘NextToken’),
‘MaxResults’ => 500
));

$data = $result1->get(‘ResultSet’);
$res = array_merge($res, $data[‘Rows’]);;
}

$resData = $this->processResultRows($res);
return $resData;
}
catch (AthenaException $e) {
// Catch an S3 specific exception.
echo $e->getMessage();
} catch (AwsException $e) {
// This catches the more generic AwsException. You can grab information
// from the exception using methods of the exception object.
echo $e->getAwsRequestId() . “\n”;
echo $e->getAwsErrorType() . “\n”;
echo $e->getAwsErrorCode() . “\n”;
}
}

/*
* function to wait for fetch result from query
*/
function waitForQueryToComplete($QueryExecutionId)
{
while(1)
{
$result = $this->Client->getQueryExecution(array(‘QueryExecutionId’ => $QueryExecutionId));
$res = $result->toArray();

//echo $res[‘QueryExecution’][‘Status’][‘State’].'<br/>’;
if($res[‘QueryExecution’][‘Status’][‘State’]==’FAILED’)
{
echo ‘Query Failed’;
die;
}
else if($res[‘QueryExecution’][‘Status’][‘State’]==’CANCELED’)
{
echo ‘Query was cancelled’;
die;
}
else if($res[‘QueryExecution’][‘Status’][‘State’]==’SUCCEEDED’)
{
break; // break while loop
}

}
}

/*
* function to process data
*/
function processResultRows($res)
{
$result = array();
$resul_array = array();

// echo ‘@@@Count: ‘.count($res).'<br/>’;

for($i=0; $i <= count($res); $i++)
{
for($n=0; $n < count($res[$i][‘Data’]); $n++)
{
if($i==0)
$result[] = $res[$i][‘Data’][$n][‘VarCharValue’];
else
{
$resul_array[$i][$result[$n]] = $res[$i][‘Data’][$n][‘VarCharValue’];
}
}
}

// echo ‘resul_array_cnt: ‘.count($resul_array).'<br/>’;
return $resul_array;
}
}

Advertisements
 
Leave a comment

Posted by on July 20, 2017 in Athena, AWS

 

Tags: , , ,