DBR(Detailed Billing Report with Resources and Tags)のCSVを分析しようとググってみたら以下のリポジトリ見つけました。
中身ざっと読んでみると以下のような感じに見えます。
- ローカルにDBRダウンロード
- DBRのCSVをParquetに変換（Python+Apache Drill)
- Cfnで作成した別S3にアップロード
- AthenaにProxy経由JDBC接続で解析
- CloudWatchにメトリクスとして登録
- CloudWatchダッシュボードとして表示
最近Dockerで使えることを知って、個人的手元ツールの仲間入りをしたApache Drillを使う機会！と思ってDBRのレポートを手元のApache Drillで分析してみました。
DockerでApache Drillをサクッと試してみる（S3上のParquetファイル読んでみた） - YOMON8.NET
S3からDBRレポートをダウンロード・解凍
DBRレポートはZIP形式のCSVなのですが、DrillからZIPを直接扱う方法が見つからなかったので、普通に落としてUnZipします。
$ aws s3 cp s3://<YourBucket>/<YourAccout>-aws-billing-detailed-line-items-with-resources-and-tags-2019-02.csv.zip . $ unzip <YourAccout>-aws-billing-detailed-line-items-with-resources-and-tags-2019-02.csv.zip
DockerでDrillを起動
/c/work/dbr:/dbrwork の部分は環境によって変更してください。
$ docker run --rm -it --name drill -p 8047:8047 -v /c/work/dbr:/dbr -t drill/apache-drill:1.15.0 /bin/bash
DBRをSELECTしてみる
CSVファイルの分析はこの辺りに記載があります。 早速やってみます。
が、実はこのままだとSELECTしてもヘッダーが認識されなかったりします。
0: jdbc:drill:zk=local> SELECT * FROM dfs.root.`/dbr/<YourAccout>-aws-billing-detailed-line-items-with-resources-and-tags-2019-02.csv` LIMIT 1; +----------------------------------------------------------------------------------+ | columns | +----------------------------------------------------------------------------------+ | ["InvoiceID","PayerAccountId","LinkedAccountId","RecordType","RecordId","ProductName","RateId","SubscriptionId","PricingPlanId","UsageType","Operation","AvailabilityZone","ReservedInstance","ItemDescription","UsageStartDate","UsageEndDate","UsageQuantity","BlendedRate","BlendedCost","UnBlendedRate","UnBlendedCost","ResourceId","user:Name"] | +----------------------------------------------------------------------------------+ 1 row selected (0.332 seconds)
以下にアクセスして、１行目をヘッダーとして使うように設定しておくと、ヘッダー認識します。
http://localhost:8047/storage/dfs
"csv": { "type": "text", "extensions": [ "csv" ], "extractHeader": true, "delimiter": "," },
CSVをParquet形式に変換
SQLで分析する前に、CSVはスキーマ全てvarcharで認識されてしまっているので、Parquetに変換しておきます。
変換前のに以下の設定しておきます。
>ALTER SESSION SET `store.format`='parquet'; >ALTER SYSTEM SET `drill.exec.functions.cast_empty_string_to_null` = true;
コンテナ上のTMPディレクトリに dfs.tmp.dbr というスキーマ作る形でParquetファイルに変換します。
>CREATE TABLE dfs.tmp.dbr AS SELECT PayerAccountId, LinkedAccountId, RecordType, RecordId, ProductName, RateId, SubscriptionId, PricingPlanId, UsageType, Operation, AvailabilityZone, ReservedInstance, ItemDescription, CAST(UsageStartDate AS TIMESTAMP) as `UsageStartDate`, CAST(UsageEndDate AS TIMESTAMP) as `UsageEndDate`, CAST(NULLIF(UsageQuantity,'0') AS DOUBLE) as `UsageQuantity`, CAST(NULLIF(BlendedRate,'0') AS DOUBLE) as `BlendedRate`, CAST(NULLIF(BlendedCost,'0') AS DOUBLE) as `BlendedCost `, CAST(NULLIF(UnBlendedRate,'0') AS DOUBLE) as `UnBlendedRate`, CAST(NULLIF(UnBlendedCost,'0') AS DOUBLE) as `UnBlendedCost`, ResourceId, user_Name FROM dfs.root.`/dbr/<YourAccout>-aws-billing-detailed-line-items-with-resources-and-tags-2019-02.csv`; +-----------+----------------------------+ | Fragment | Number of records written | +-----------+----------------------------+ | 0_0 | 216721 | +-----------+----------------------------+ 1 row selected (19.885 seconds)
これで集計かけられるようになりました。
> SELECT ProductName,SUM(UsageQuantity*UnBlendedCost) AS cost FROM dfs.tmp.dbr GROUP BY ProductName; +-------------------------------------+------------------------+ | ProductName | cost | +-------------------------------------+------------------------+ | | null | | AmazonCloudWatch | 0.123456789 | | Amazon Elastic Compute Cloud | 123456.0000000000 | | AWS Lambda | 123456.0 | -- 省略
所感
EXCEPTとかINTERSECT使えないとかところどころDBMSよりは不便な部分もありますが、基本的なSQLは打てるので、一度やり方わかってしまえば、クエリも早いしAthenaより快適です。
