新機能 AWSCLIから Amazon Athena のクエリを実行する

石川覚

2017.05.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

昨日、以前から要望の多かった AWSCLI から Amazon Athena のクエリ実行する機能がリリースされましたので、早速試してみました。

Amazon Athena adds API/CLI, AWS SDK support, and audit logging with AWS CloudTrail

今回のリリースでは、AWSCLIの他に Java, .NET, Node.js, PHP, Python, Ruby, Go, 及び C++ で利用可能な AWS SDK を使用して、REST API 経由で Amazon Athena からもクエリを実行できるようになりました。詳細については、APIリファレンスおよびCLIガイドを参照してください。

サポートされた機能

ペタバイトクラスのクエリ実行も想定した Amazon Athena なので、クエリ実行は、「クエリの非同期実行〜クエリ実行状態の確認〜クエリ実行結果の取得」という流れになります。

AWS CLI Command Reference

AWSCLIの実行例などありませんでしたが、AWS SDK for Java の Examples and Code Samples を例に読み解き、サブコマンドを分類したり、ユースケースを私なりに解説したいと思います。

サブコマンド

準備〜 AWSCLIを最新に更新

最新のASCLIに更新します。検証はMacBook、AWSCLIのバージョンは1.11.89で確認しました。

$ aws --version
aws-cli/1.11.73 Python/2.7.10 Darwin/16.6.0 botocore/1.5.36

$ sudo pip install --upgrade awscli --ignore-installed six
Password:
：
（中略）
：
Successfully installed PyYAML-3.12 awscli-1.11.89 botocore-1.5.52 colorama-0.3.7 docutils-0.13.1 futures-3.1.1 jmespath-0.9.2 pyasn1-0.2.3 python-dateutil-2.6.0 rsa-3.4.2 s3transfer-0.1.10 six-1.10.0

$ aws --version
aws-cli/1.11.89 Python/2.7.10 Darwin/16.6.0 botocore/1.5.52

検証用テーブルの作成

検証データは、AWSが提供しているテストデータのlineorderテーブル(75004738レコード)を利用します。

s3://awssampledb/ssbgz/lineorder0000_part_00.gz

-- DROP TABLE cmathenadb.lineorder;
CREATE EXTERNAL TABLE IF NOT EXISTS cmathenadb.lineorder (
lo_orderkey int,
lo_linenumber int,
lo_custkey int,
lo_partkey int,
lo_suppkey int,
lo_orderdate int,
lo_orderpriority string,
lo_shippriority string,
lo_quantity int,
lo_extendedprice int,
lo_ordertotalprice int,
lo_discount int,
lo_revenue int,
lo_supplycost int,
lo_tax int,
lo_commitdate int,
lo_shipmode string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '|',
'field.delim' = '|'
) LOCATION 's3://cm-datalake/lineorder/';

「単一のクエリー」を実行する

「単一のクエリー」を実行は、「クエリ実行の開始〜クエリ実行状況の確認〜クエリ実行結果の取得」という流れになります。実行したクエリを中断することも可能です。

クエリ実行の開始：start-query-execution

クエリの実行は、start-query-executionサブコマンドを使います。 --query-stringパラメータに実行したいSQL、--result-configurationパラメータに実行結果を保存するS3のパスを指定します。（JDBCドライバで指定するS3_Staging_Dirに相当します）

実行するとクエリの実行を待たずに終了し、代わりに QueryExecutionId が直ちに返ってきます。以降はこのQueryExecutionIdをキーに操作します。

$ aws athena start-query-execution \
> --query-string "SELECT * FROM cmathenadb.lineorder limit 10;" \
> --result-configuration OutputLocation=s3://cm-datalake/awscli_staging_dir
{
"QueryExecutionId": "e397cfa4-aa12-41c9-aebf-aa629bba7453"
}

引数に渡したクエリーに誤りがあるなどの場合は非同期ではなく、直ちにエラーメッセージが返ってきます。クエリーにエラーがあった場合、エラーメッセージが標準出力に出力されます。

$ aws athena start-query-execution \
> --query-string "SELECT ** FROM cmathenadb.lineorder limit 10;" \
> --result-configuration OutputLocation=s3://cm-datalake/awscli_staging_dir

An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: line 1:9: extraneous input '*' expecting {, ',', 'FROM', 'WHERE', 'GROUP', 'ORDER', 'HAVING', 'LIMIT', 'APPROXIMATE', 'UNION', 'EXCEPT', 'INTERSECT'}

クエリ実行状況の確認：get-query-execution

バックグラウンドでクエリが実行していますので、クエリが実行状況や結果の取得が可能であるか等、クエリの実行状況を確認します。

$ aws athena get-query-execution --query-execution-id e397cfa4-aa12-41c9-aebf-aa629bba7453
{
"QueryExecution": {
"Status": {
"SubmissionDateTime": 1495305498.251,
"State": "SUCCEEDED",
"CompletionDateTime": 1495305499.987
},
"Query": "SELECT * FROM cmathenadb.lineorder limit 10",
"Statistics": {
"DataScannedInBytes": 220743,
"EngineExecutionTimeInMillis": 1473
},
"ResultConfiguration": {
"OutputLocation": "s3://cm-datalake/awscli_staging_dir/e397cfa4-aa12-41c9-aebf-aa629bba7453.csv"
},
"QueryExecutionId": "e397cfa4-aa12-41c9-aebf-aa629bba7453"
}
}

クエリ実行結果の取得：get-query-results

上記の結果、QueryExecution.Status.Stateが "SUCCEEDED"（クエリは実行済）なので、結果を取得します。 ResultSet.Rows.Dataはレコードが繰り返し出力します。ResultSetMetadata.ColumnInfoにはカラム情報の詳細が含まれています。

$ aws athena get-query-results --query-execution-id e397cfa4-aa12-41c9-aebf-aa629bba7453
{
"ResultSet": {
"Rows": [
{
"Data": [
{
"VarCharValue": "lo_orderkey"
},
{
"VarCharValue": "lo_linenumber"
},
{
"VarCharValue": "lo_custkey"
},
{
"VarCharValue": "lo_partkey"
},
{
"VarCharValue": "lo_suppkey"
},
{
"VarCharValue": "lo_orderdate"
},
{
"VarCharValue": "lo_orderpriority"
},
{
"VarCharValue": "lo_shippriority"
},
{
"VarCharValue": "lo_quantity"
},
{
"VarCharValue": "lo_extendedprice"
},
{
"VarCharValue": "lo_ordertotalprice"
},
{
"VarCharValue": "lo_discount"
},
{
"VarCharValue": "lo_revenue"
},
{
"VarCharValue": "lo_supplycost"
},
{
"VarCharValue": "lo_tax"
},
{
"VarCharValue": "lo_commitdate"
},
{
"VarCharValue": "lo_shipmode"
}
]
},
{
"Data": [
{
"VarCharValue": "526035463"
},
{
"VarCharValue": "1"
},
{
"VarCharValue": "1327621"
},
{
"VarCharValue": "833550"
},
{
"VarCharValue": "41158"
},
{
"VarCharValue": "19971130"
},
{
"VarCharValue": "5-LOW"
},
{
"VarCharValue": "0"
},
{
"VarCharValue": "9"
},
{
"VarCharValue": "1335159"
},
{
"VarCharValue": "21320828"
},
{
"VarCharValue": "10"
},
{
"VarCharValue": "1201643"
},
{
"VarCharValue": "89010"
},
{
"VarCharValue": "0"
},
{
"VarCharValue": "19980111"
},
{
"VarCharValue": "MAIL"
}
]
},
：
（中略）
：
],
"ResultSetMetadata": {
"ColumnInfo": [
{
"Scale": 0,
"Name": "lo_orderkey",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_orderkey",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_linenumber",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_linenumber",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_custkey",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_custkey",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_partkey",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_partkey",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_suppkey",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_suppkey",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_orderdate",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_orderdate",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_orderpriority",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 1073741824,
"Label": "lo_orderpriority",
"CaseSensitive": true,
"SchemaName": "",
"Type": "varchar",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_shippriority",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 1073741824,
"Label": "lo_shippriority",
"CaseSensitive": true,
"SchemaName": "",
"Type": "varchar",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_quantity",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_quantity",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_extendedprice",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_extendedprice",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_ordertotalprice",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_ordertotalprice",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_discount",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_discount",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_revenue",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_revenue",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_supplycost",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_supplycost",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_tax",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_tax",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_commitdate",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 10,
"Label": "lo_commitdate",
"CaseSensitive": false,
"SchemaName": "",
"Type": "integer",
"CatalogName": "hive"
},
{
"Scale": 0,
"Name": "lo_shipmode",
"Nullable": "UNKNOWN",
"TableName": "",
"Precision": 1073741824,
"Label": "lo_shipmode",
"CaseSensitive": true,
"SchemaName": "",
"Type": "varchar",
"CatalogName": "hive"
}
]
}
}
}

実行クエリの中断：stop-query-execution

なお、バックグラウンドで実行しているこのクエリの実行を中断したいときは、stop-query-executionを実行します。

$ aws athena stop-query-execution --query-execution-id e397cfa4-aa12-41c9-aebf-aa629bba7453

クエリの結果をjsonではなく、CSV形式で取得したいときは？

get-query-resultsサブコマンドは、プログラムで構造化したjsonファイルから必要な値をピンポイントで取得するのに便利ですが、普通にCSVファイルとして取得したいことがあるでしょう。そんなときは、Athenaは実行結果をS3にCSV出力しているので、そのCSVファイルで取得したり、標準出力に出力するという方法が手っ取り早いのでおすすめです。

$ aws s3 cp s3://cm-datalake/awscli_staging_dir/e397cfa4-aa12-41c9-aebf-aa629bba7453.csv -
"lo_orderkey","lo_linenumber","lo_custkey","lo_partkey","lo_suppkey","lo_orderdate","lo_orderpriority","lo_shippriority","lo_quantity","lo_extendedprice","lo_ordertotalprice","lo_discount","lo_revenue","lo_supplycost","lo_tax","lo_commitdate","lo_shipmode"
"526035463","1","1327621","833550","41158","19971130","5-LOW","0","9","1335159","21320828","10","1201643","89010","0","19980111","MAIL"
"457881700","1","849551","682835","698046","19950803","2-HIGH","0","33","5998740","19697557","7","5578828","109068","0","19950912","AIR"
"457881762","5","975452","341456","874640","19970524","1-URGENT","0","38","5690272","19069420","9","5178147","89846","8","19970815","MAIL"
"457881829","3","2047915","442604","490246","19950424","5-LOW","0","29","4485082","17152942","9","4081424","92794","3","19950524","SHIP"
"457881894","3","2855755","108553","372555","19920816","2-HIGH","0","33","5153115","26092287","10","4637803","93693","0","19921016","RAIL"
"457881956","4","2286178","511197","88971","19930217","5-LOW","0","37","4470229","19623933","4","4291419","72490","6","19930409","TRUCK"
"457882019","3","2473054","958777","403362","19951106","5-LOW","0","10","1835730","19051236","5","1743943","110143","3","19951231","MAIL"
"457882081","1","121738","240519","706437","19920121","4-NOT SPECI","0","0","0","0","8","0","87570","3","19920223","AIR"
"457882117","2","2762990","547842","49430","19930729","3-MEDIUM","0","38","7181316","7208005","6","6750437","113389","5","19930922","REG AIR"
"457882182","1","2670493","755731","11430","19980705","5-LOW","0","9","1608030","29117346","6","1511548","107202","3","19980901","RAIL"

まとめ

シンプルですが必要な機能がよくまとまったアップデートです。これまでバッチ処理はJDBCドライバによるプログラミングに限定されていましたが、AWSCLIのみで簡単に実装できるようになりました。AmazonAthena の東京リージョンのサービス開始が待ち遠しい限りです。

新機能 AWSCLIから Amazon Athena のクエリを実行する

サポートされた機能

サブコマンド

準備〜 AWSCLIを最新に更新

検証用テーブルの作成

「単一のクエリー」を実行する

クエリ実行の開始：start-query-execution

クエリ実行状況の確認：get-query-execution

クエリ実行結果の取得：get-query-results

実行クエリの中断：stop-query-execution

クエリの結果をjsonではなく、CSV形式で取得したいときは？

まとめ

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

新機能 AWSCLIから Amazon Athena のクエリを実行する

サポートされた機能

サブコマンド

準備 〜 AWSCLIを最新に更新

検証用テーブルの作成

「単一のクエリー」を実行する

クエリ実行の開始：start-query-execution

クエリ実行状況の確認：get-query-execution

クエリ実行結果の取得：get-query-results

実行クエリの中断：stop-query-execution

クエリの結果をjsonではなく、CSV形式で取得したいときは？

まとめ

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS / システムアーキテクト案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

関連記事

MFA認証を使ったAssumeRoleでシンプルにTerraformを実行する(aws configure export-credentials)

AWS Config の個別アカウントアグリゲータを AWS CLI で作成する

Changing EC2 instance type

組織のConfigアグリゲータをAWS CLIで作成する

準備〜 AWSCLIを最新に更新

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩