DuckDB④ CLIコマンドの結果を分析する

この記事でやること

ここではAWS Trusted Advisorのレコメンデーション情報をAWS CLIで取得してDuckDBに取り込みます。また、取り込んだ情報に対してSteampipeで取得できるEBSボリューム、EC2インスタンス情報を付与してみます。

参考にしたページ

AWS Trusted Advisor レコメンデーション情報を取得するには？

ここではTrusted Advisorのレコメンデーション情報をDuckDBに取り込みます。
具体的には「コスト最適化-利用頻度の低いAmazon EBSボリューム」の推奨項目に該当したEBSボリュームの一覧を対象とします。（AWSマネジメントコンソールでは図の赤枠部分に対応）

先に取り上げたSteampipeはTrusted Advisorのレコメンデーション情報には対応せず、③の様にSteampipe(PostgreSQL)のテーブルとしては取り込めないので別の方法を考えます。

AWS CLIコマンドaws trustedadvisor list-recommendation-resourcesを使えば、該当のレコメンデーション情報を取得できるので、このコマンドの実行結果をDuckDBに取り込めればよさそうです。

DuckDBの拡張機能

DuckDBでは様々なソースからデータを取り込む処理が拡張機能(Extension)として実装されており、以下のExtensionが標準で組み込まれています。

また、コミュニティが提供するExtensionも公開されており、以下のようなINSTALL/LOADステートメントで必要なものを自由に追加できます。

INSTALL quack FROM community;
LOAD quack;

この中のshellfsというExtensionが「シェルコマンドの出力結果をDuckDBに取り込む」機能を提供するので、今回はこのExtensionを使ってみます。

環境準備

③で利用した環境をベースにセットアップしていきます。

AWS CLIの導入

WSL2のUbuntuにAWS CLIコマンドを導入しておきます。

古いバージョンだとレコメンデーション情報が取得できないので最新バージョンにアップデートすることをおすすめします。今回の環境では2.22.31を導入しました。

$ aws --version
aws-cli/2.22.31 Python/3.12.6 Linux/5.15.133.1-microsoft-standard-WSL2 exe/x86_64.ubuntu.22

shellfsの導入

こちらの「Installing and Loading」に沿ってDuckDBのCLIからインストールします。
インストールしたExtensionは~/.duckdb/extensions配下に格納されるようです。

$ duckdb test.duckdb
v1.1.3 19864453f7
Enter ".help" for usage hints.
D INSTALL shellfs FROM community;
D LOAD shellfs;

試しにseqコマンドを実行して動作を確認します。

D SELECT * FROM read_csv('seq 1 100 | grep 2 |');
┌─────────┐
│ column0 │
│  int64  │
├─────────┤
│       2 │
│      12 │
│      20 │
│      21 │
│      22 │
│      23 │
│      24 │
│      25 │
│      26 │
│      27 │
│      28 │
│      29 │
│      32 │
│      42 │
│      52 │
│      62 │
│      72 │
│      82 │
│      92 │
├─────────┤
│ 19 rows │
└─────────┘

dbtにshellfsを設定

先ほどはDuckDBのCLIから手動でshellfsをLOADしましたが、dbtでは~/.dbt/profiles.ymlのextensionsブロックに定義することでLOADされます。

~/.dbt/profiles.yml

duckdb_test:
  outputs:
    dev:
      type: duckdb
      path: test.duckdb
      threads: 1
      extensions:
        - shellfs
      attach:
        - path: postgres://steampipe@127.0.0.1:9193/steampipe
          type: postgres
  target: dev

dbtのモデルを作成

③で作成したdbtプロジェクトのmodels/配下に新たなモデルファイルを作成します。

1. Trusted Advisorのレコメンデーション情報

aws trustedadvisor list-recommendation-resourcesコマンドの実行結果をJSON形式で標準出力し、read_json関数でテーブルとして扱います。

aws_cli_trustedadvisor_recommendations_ebs_raw.sql

{{ config(materialized='table') }}

WITH raw_data AS (
    SELECT unnest(recommendationResourceSummaries) AS Records
    FROM read_json('aws trustedadvisor list-recommendation-resources --recommendation-identifier arn:aws:trustedadvisor:::recommendation/722e700e-6f53-422a-a72d-53a3635cefb8 --region us-east-1 |')
)
SELECT unnest(Records)
FROM raw_data

なお、--recommendation-identifierにはあらかじめaws trustedadvisor list-recommendationsコマンドで調べた推奨項目「コスト最適化-利用頻度の低いAmazon EBSボリューム」のARNを指定しています。

2. EBSボリュームの情報

後でレコメンデーション情報と紐づけたいので、③のaws_ec2_instanceと同様にSteampipeのテーブルを取り込んでおきます。

aws_ebs_volume.sql

{{ config(materialized='table') }}

SELECT *
FROM steampipe.aws.aws_ebs_volume

3. EBSボリュームがアタッチされているEC2インスタンスのIDを特定

aws_ebs_volumeテーブルのattachmentsを展開して、EBSボリュームのIDとアタッチされているEC2インスタンスのIDとの関連を保有するビューを構成します。

ebs_volume_with_instance.sql

SELECT 
	volume_id,
	UNNEST(json_extract_string(attachments, '$[*].InstanceId')) AS attached_instance_id,
FROM {{ ref('aws_ebs_volume') }}

4. レコメンデーション情報のメタ情報を展開

「推奨項目に該当したEBSボリュームの一覧情報」は取り込んだレコメンデーション情報(aws_cli_trustedadvisor_recommendations_ebs_raw)の中のmetadataに入っているので、この情報を展開するビューを構成します。

ta_recommend_ebs_volume.sql

SELECT
  json_extract_string(metadata, '$.0') AS region,
  json_extract_string(metadata, '$.1') AS volume_id,
  json_extract_string(metadata, '$.2') AS volume_name,
  json_extract_string(metadata, '$.3') AS volume_type,
  json_extract_string(metadata, '$.4') AS volume_size,
  json_extract_string(metadata, '$.5') AS monthly_cost,
  json_extract_string(metadata, '$.6') AS snapshot_id,
  json_extract_string(metadata, '$.7') AS snapshot_name,
  json_extract_string(metadata, '$.8') AS snapshot_elapsed_days,

FROM {{ ref('aws_cli_trustedadvisor_recommendations_ebs_raw') }}

5. 最終的な分析結果を生成

最後に1.～4.をJOINして最終的な分析結果を生成するビューを構成します。

ta_recommend_ebs_volume_detail.sql

SELECT 
	trev.*,
	aev.create_time AS volume_create_time,
	aev.iops AS volume_iops,
	json_extract_string(aev.tags, '$.Owner') AS volume_owner,
	aev.tags AS volume_tags,
	aei.instance_id AS attached_instance_id,
	aei.instance_type ,
	aei.instance_state ,
	json_extract_string(aei.tags, '$.Name') AS instance_name,
	json_extract_string(aei.tags, '$.Owner') AS instance_owner,
	aei.tags AS instance_tags
FROM {{ ref('ta_recommend_ebs_volume') }} AS trev 
LEFT OUTER JOIN {{ ref('aws_ebs_volume') }} AS aev 
ON trev.volume_id = aev.volume_id 
LEFT OUTER JOIN {{ ref('ebs_volume_with_instance') }} AS evwi 
ON trev.volume_id = evwi.volume_id 
LEFT OUTER JOIN {{ ref('aws_ec2_instance') }} AS aei 
ON evwi.attached_instance_id = aei.instance_id 
ORDER BY trev.monthly_cost DESC

定義したモデルのテーブル、ビューをDuckDBへ作成

(dbt-py3.13) $ dbt run
08:06:15  Running with dbt=1.9.1
08:06:15  Registered adapter: duckdb=1.9.1
08:06:15  Found 8 models, 424 macros
08:06:15
08:06:15  Concurrency: 1 threads (target='dev')
08:06:15
08:06:16  1 of 8 START sql table model main.aws_cli_trustedadvisor_recommendations_ebs_raw  [RUN]
08:06:19  1 of 8 OK created sql table model main.aws_cli_trustedadvisor_recommendations_ebs_raw  [OK in 3.40s]
08:06:19  2 of 8 START sql table model main.aws_ebs_volume ............................... [RUN]
08:06:22  2 of 8 OK created sql table model main.aws_ebs_volume .......................... [OK in 2.83s]
08:06:22  3 of 8 START sql table model main.aws_ec2_instance ............................. [RUN]
08:06:24  3 of 8 OK created sql table model main.aws_ec2_instance ........................ [OK in 2.10s]
08:06:24  4 of 8 START sql table model main.ct_raw ....................................... [RUN]
08:06:30  4 of 8 OK created sql table model main.ct_raw .................................. [OK in 5.64s]
08:06:30  5 of 8 START sql view model main.ta_recommend_ebs_volume ....................... [RUN]
08:06:30  5 of 8 OK created sql view model main.ta_recommend_ebs_volume .................. [OK in 0.15s]
08:06:30  6 of 8 START sql view model main.ebs_volume_with_instance ...................... [RUN]
08:06:30  6 of 8 OK created sql view model main.ebs_volume_with_instance ................. [OK in 0.14s]
08:06:30  7 of 8 START sql view model main.ct_detail ..................................... [RUN]
08:06:30  7 of 8 OK created sql view model main.ct_detail ................................ [OK in 0.24s]
08:06:30  8 of 8 START sql view model main.ta_recommend_ebs_volume_detail ................ [RUN]
08:06:30  8 of 8 OK created sql view model main.ta_recommend_ebs_volume_detail ........... [OK in 0.12s]
08:06:30
08:06:30  Finished running 4 table models, 4 view models in 0 hours 0 minutes and 15.33 seconds (15.33s).
08:06:31
08:06:31  Completed successfully
08:06:31
08:06:31  Done. PASS=8 WARN=0 ERROR=0 SKIP=0 TOTAL=8

DBeaverでDuckDBに接続するとdbtのモデルで定義したテーブル、ビューが作成されています。

「5. 最終的な分析結果を生成」で作成したta_recommend_ebs_volume_detailビューでは、レコメンデーション情報にリストアップされたEBSボリュームの詳細情報に加え、アタッチされているEC2インスタンスがあればそのインスタンスの情報も紐づけられているので、リストアップされたEBSボリュームへの対処を検討する際の参考となるでしょう。

まとめ

ここではAWS CLIで取得したAWS Trusted Advisorのレコメンデーション情報をshellfsというExtensionでDuckDBに取り込みました。また、Steampipeから取り込んだEBSボリューム、EC2インスタンスの情報と紐づけてよりわかりやすい分析結果を生成しました。

DuckDBは様々なソースからデータを取り込める機能を持ちますが、シェルコマンドの結果も取り込めることでより応用範囲が広がるように思います。

ちなみにWeb APIの結果を直接取り込むスクレイピングの様なニーズにも応えられるようですね。

次はDuckDBに取り込んだデータの可視化を試してみようと思います。