Last active
November 19, 2020 09:15
-
-
Save wankormaru/d031824a2eb2c0232316b4cb5b8cb1b8 to your computer and use it in GitHub Desktop.
Athena SELECT Query Sample Program
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| /*----------------------------- | |
| * Athena SELECT Query Sample Program | |
| * | |
| * - IAM でユーザ作成必要 | |
| * 必要な管理ポリシー : AmazonAthenaFullAccess | |
| * - 作ったIAMユーザのクレデンシャル情報を aws configure で設定必要 | |
| * - AWS SDK for PHP version 3 のインストールが必要 | |
| * | |
| * @date 2017/08/06 | |
| ------------------------------*/ | |
| /*-------------------------------------------------------------- | |
| * AWS SDK for PHP のロード | |
| --------------------------------------------------------------*/ | |
| require './vendor/autoload.php'; | |
| // 引数が無い場合はエラー | |
| if (!$argv[1]){ | |
| echo "ERROR!!\n"; | |
| echo "ex.) $ php ~/athena_test/sample_athena.php 2015-06-22 \n"; | |
| exit; | |
| } | |
| $target_partition = substr($argv[1], 0, 7); | |
| /*-------------------------------------------------------------- | |
| * Athena クライアント・インスタンスの作成 | |
| --------------------------------------------------------------*/ | |
| $options = [ | |
| 'region' => 'us-east-1', | |
| 'version' => 'latest' | |
| ]; | |
| $athenaClient = new Aws\Athena\AthenaClient($options); | |
| /*-------------------------------------------------------------- | |
| * SQLの作成 | |
| --------------------------------------------------------------*/ | |
| echo "スタート\n"; | |
| echo "--------\n"; | |
| $sql = <<<EOT | |
| select * | |
| from sabawa_no_wa_2_db.on_shitudo_tbl | |
| where ym = '$target_partition' | |
| and hiduke = '$argv[1]' | |
| limit 1; | |
| EOT; | |
| $param_Query = [ | |
| 'QueryString' => $sql, | |
| 'ResultConfiguration' => [ | |
| 'OutputLocation' => 's3://athena-iot-lt-query-result/' | |
| ] | |
| ]; | |
| /*-------------------------------------------------------------- | |
| * SQLの実行 | |
| --------------------------------------------------------------*/ | |
| $result = $athenaClient->startQueryExecution($param_Query); | |
| $QueryExecutionId = $result['QueryExecutionId']; | |
| /*-------------------------------------------------------------- | |
| * SQL実行中のステータスをwhileループしながら確認 | |
| --------------------------------------------------------------*/ | |
| $stat_flg = true; | |
| while ($stat_flg){ | |
| $result = $athenaClient->getQueryExecution([ | |
| 'QueryExecutionId' => $QueryExecutionId | |
| ]); | |
| // Debug | |
| echo "Query status = ".$result['QueryExecution']['Status']['State'] ."\n"; | |
| if ($result['QueryExecution']['Status']['State'] == 'SUCCEEDED'){ | |
| $stat_flg = false; | |
| } | |
| } | |
| /*-------------------------------------------------------------- | |
| * SQLが完了するステータスが取得できたら、SQL結果の取得 | |
| --------------------------------------------------------------*/ | |
| $result = $athenaClient->getQueryResults([ | |
| 'QueryExecutionId' => $QueryExecutionId | |
| ]); | |
| /*-------------------------------------------------------------- | |
| * 結果表示 | |
| --------------------------------------------------------------*/ | |
| // 温度データの取得 | |
| $tmp = $result['ResultSet']['Rows'][1]['Data'][0]['VarCharValue']; | |
| // 湿度データの取得 | |
| $hum = $result['ResultSet']['Rows'][1]['Data'][1]['VarCharValue']; | |
| // 日時データの取得 | |
| $date_time = $result['ResultSet']['Rows'][1]['Data'][2]['VarCharValue']; | |
| // 表示 | |
| echo "\n"; | |
| echo "\n"; | |
| echo "日時=".$tmp." 温度=".$hum." 湿度=".$date_time."\n"; | |
| echo "\n"; | |
| echo "\n"; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment