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

2017年05月21日
石川覚（26）
4

昨日、以前から要望の多かった 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のパスを指定します。（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 {<EOF>, ',', '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"
                    },
       ：
    （中略）
       ：
        ],
        "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"
                },
       ：
    （中略）
       ：
            ]
        }
    }
}

実行クエリの中断：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"