AWS Cost and Usage ReportsをSnowflakeからクエリする

著者：@ohsawa0515
目次

はじめまして、クリエイティブサーベイ株式会社の大澤（@ohsawa0515）と申します。

Sansan株式会社でITインフラエンジニアとデータエンジニアをした後、2024年1月からグループ会社のクリエイティブサーベイに出向して、SREチームのかたわら、データエンジニアチームにEmbedded SREとしても活動しています。

AWSのコストを分析・可視化する場合に、AWS Cost Explorerを使うことが一般的ですが、より詳細な分析を行う場合にはAWS Cost and Usage Reports（AWSのコストと使用状況レポート、以下CUR）を利用することがあります。CURはS3バケットにCSVもしくはParquet形式の請求データを定期的に出力する機能で、Amazon AthenaやAmazon Redshift、Amazon QuickSightといったAWSサービスによってクエリしたり可視化できます。

弊社はデータウェアハウスとしてSnowflakeを採用しており、CURのデータをSnowflakeからクエリできるようにしたので、その方法をご紹介します。

CURの設定

デフォルトではCURは有効になっていないため、AWS公式ドキュメントを参考に設定します。また、TerraformでCURを定義したい場合は、以下のブログをご参考ください。

AWS Cost and Usage Reports(CUR)をTerraformで作成する

S3にアクセスするためのSnowflakeの設定

Snowflakeのストレージ統合によって、SnowflakeからS3バケットにアクセスできるようにします。AWS側でIAMポリシーとIAMロールの作成を行い、Snowflake側でCREATE STORAGE INTEGRATIONクエリによってストレージ統合を作成します。詳細な手順はSnowflake公式ドキュメントを参照してください。

参考までに、CURのデータを保存するS3バケット（example-billing-report）へのストレージ統合作成クエリを以下に記載します。

create storage integration cur_integration
  type = EXTERNAL_STAGE
  storage_provider = 'S3'
  enabled = TRUE
  storage_aws_role_arn = 'arn:aws:iam::123456789012:role/snowflake_access_role'
  storage_allowed_locations = ('s3://example-billing-report/hoge/billing/billing/')
;

外部ステージの作成

S3バケットのファイルをSnowflakeにロードするためのステージをCREATE STAGEクエリで作成します。ステージ作成するためにはスキーマ、データベース、ファイルフォーマットが必要なため、事前に作成しています。

-- データベース、スキーマの作成
create database CUR;
use cur;
create schema billing;

-- ファイルフォーマットの作成
create or replace file format my_parquet_format
  type = parquet;

-- S3外部ステージの作成
create stage cur_stage
  storage_integration = cur_integration
  url = 's3://example-billing-report/hoge/billing/billing/'
  file_format = my_parquet_format;

外部テーブルの作成

CURが仕様変更やコスト配分タグなどによってカラムが増減します。AWSではGlueクローラーによって自動的にカラム追加と削除に追従できていますが、Snowflakeではそれがありません。したがって、次のクエリのようにスキーマの自動検出と、スキーマの変更があった場合に外部テーブルのメタデータを自動更新するように設定します。

create or replace external table external_billing
  using template (
    select array_agg(object_construct(
      'COLUMN_NAME', COLUMN_NAME,
      'TYPE', TYPE,
      'NULLABLE', NULLABLE,
      'EXPRESSION', EXPRESSION
    ))
    from table(
      infer_schema(
        LOCATION => '@cur_stage',
        FILE_FORMAT => 'my_parquet_format'
      )
    )
  )
location = @cur_stage
file_format = my_parquet_format
auto_refresh = true
;

ビューの作成

external_billingテーブルでCURデータをクエリできるようになりましたが、より使いやすくするためのビューを作成します。CURデータの保存先S3バケットには、年（year）、月（month）でHiveスタイルのパーティションを設定されています。

例：s3://example-billing-report/hoge/billing/billing/year=2024/month=06/

しかし、Snowflakeではパーティション設定することができません。そのため、次のようなビューを作成してS3パスから年月を取得してカラムとして追加します。

create or replace view external_billing_with_year_month as
  select
    * exclude value, 
    split_part(split_part(METADATA$FILENAME, '/year=', 2), '/', 1) as year,
    split_part(split_part(METADATA$FILENAME, '/month=', 2), '/', 1) as month
  from
    external_billing;

クエリの実行

次のクエリは2024年6月のS3コスト合計を算出する例です。スキーマ自動検出したカラムは "" で囲む必要があります。

select
  sum("line_item_unblended_cost") as cost,
from external_billing_view
where
  "line_item_product_code" = 'AmazonS3'
  and year = '2024' and month = '3'

まとめ

本記事では、CURデータをSnowflakeでクエリするための方法を紹介しました。スキーマの変更とパーティション列の対応が必要ですが、S3上のファイルをSnowflakeからそのままクエリできるため、データレイクとしてもSnowflakeを活用できるメリットがあります。

また、Snowsight内でのダッシュボードやSnowflake上でStreamlitアプリを作成できるため、クエリだけでなくビジュアライズの幅も広がりますね。