LoginSignup
0
0

More than 1 year has passed since last update.

@turupon(Issei Tsuruzono)in株式会社ネットワールド

SnowflakeでAzureの請求データを分析してみました - ワークシート編

Posted at

概要

このプログラムで取得した csv のAzureの請求データを この記事 で Snowflake に取り込みました。今回は Snowflake に取り込んだそのデータをワークシート上でSQL文を定義することにより該当データを抽出できることを確認しました。

本作業はすべて、Snowflakeウェブインターフェイス を利用して実施しています。ワークシートとフィルタ管理を利用することにより、動的にデータ抽出できることを確認しました。

ほぼ、備忘録となっています、、、

実行環境

macOS Ventura 13.0
python 3.8.12
snowsql 1.2.24

参考ドキュメント

ワークシートを使用したデータのクエリ

ワークシート

２つのフォルダと 10個のワークシートを作成しました
image.png

フォルダ : TYPE-Usage

Azure Plan でデータを対象としたワークシート

User_Customer_TOP50

-- customername top50 の請求金額
select top 50
    cast(sum(pretaxcost) as int) as costsummary,
    customername
from
    tbl_usage
where
    billingmonth = :billingbucket
group by
    customername
order by
    costsummary desc
;

ServiceFamily_ServiceName

-- ServiceFamily毎の請求金額
select
    cast(sum(pretaxcost) as int) as costsummary,
    servicename
from
    tbl_usage
where
    billingmonth = :billingbucket
group by
    servicename
order by
    costsummary desc
;

-- ServiceName毎の請求金額
select
    cast(sum(pretaxcost) as int) as costsummary,
    servicefamily,
    servicename
from
    tbl_usage 
where
    billingmonth = :billingbucket
group by
    servicefamily,
    servicename 
order by
    servicefamily
;

Usage_BillingMonth

-- 月毎の請求金額
select
    cast(sum(pretaxcost) as int),
    billingmonth
from
    tbl_usage
group by
    billingmonth
order by
    billingmonth
;

Create_TBL

-- providor = Azure データでテーブル作成
drop table tbl_usage;
create or replace table tbl_usage as select * from tbl_billing where provider='Azure';
select count(servicename) from tbl_usage;


-- Usage上位顧客のテーブル作成
create or replace table select_usage_customer as
select top 50
    cast(sum(pretaxcost) as int) as costsummary,
    customername
from
    tbl_usage
where
    billingmonth = :billingbucket
group by
    customername
order by
    costsummary desc
;
select * from select_usage_customer;


-- Actual上位顧客のテーブル作成
create or replace table select_actual_customer as
select top 50
    cast(sum(pretaxcost) as int) as costsummary,
    customername
from
    tbl_billing
where
    billingmonth = :billingbucket
group by
    customername
order by
    costsummary desc
;
select * from select_actual_customer;

User_Customer_Detail

-- とある顧客でテーブル作成
create or replace table tbl_usage_customer as
select * from tbl_billing
where
    customername = :usage_customer
;
select count(servicename) from tbl_usage_customer;


-- 月毎の請求金額
select
    cast(sum(pretaxcost) as int) as costsummary,
    billingmonth
from
    tbl_usage_customer
group by
    billingmonth 
order by
    billingmonth
;


-- ServiceFamily毎の請求金額
select
    servicefamily,
    cast(sum(pretaxcost) as int) as costsummary 
from
    tbl_usage_customer 
where
    billingmonth = :billingbucket
group by
    servicefamily 
order by
    costsummary desc
;


-- ServiceName毎の請求金額
select
    servicename, 
    billingmonth, 
    cast(sum(pretaxcost) as int) as costsummary 
from
    tbl_usage_customer
where
    billingmonth = :billingbucket
group by 
    servicename, 
    billingmonth
order by 
    costsummary desc
;


-- servicefamily - ServiceName - product毎の請求金額
select
    servicefamily,
    servicename, 
    product, 
    cast(sum(pretaxcost) as int) as costsummary,
    cast(sum(usagequantity) as int) as quantitysummary 
from
    tbl_usage_customer
where
    billingmonth = :billingbucket
group by 
    servicefamily,
    servicename, 
    product
order by 
    costsummary desc
;

フォルダ : TYPE-ActualCost

全データ（Azure Plan + M365系）を対象としたワークシート

M365_Customer_detal

-- とある顧客でテーブル作成
create or replace table tbl_m365_customer as
select * from tbl_billing
where
    customername = :m365_customer
;
select count(servicename) from tbl_m365_customer;


-- 月毎の請求金額
select
    cast(sum(pretaxcost) as int) as costsummary,
    billingmonth
from
    tbl_m365_customer
group by
    billingmonth 
order by
    billingmonth
;


-- ServiceFamily毎の請求金額
select
    servicefamily,
    cast(sum(pretaxcost) as int) as costsummary 
from
    tbl_m365_customer 
where
    billingmonth = :billingbucket
group by
    servicefamily 
order by
    costsummary desc
;


-- ServiceName毎の請求金額
select
    servicename, 
    billingmonth, 
    cast(sum(pretaxcost) as int) as costsummary 
from
    tbl_m365_customer
where
    billingmonth = :billingbucket
group by 
    servicename, 
    billingmonth
order by 
    costsummary desc
;


-- servicefamily - ServiceName - product毎の請求金額
select
    servicefamily,
    servicename, 
    product, 
    cast(sum(pretaxcost) as int) as costsummary,
    cast(sum(usagequantity) as int) as quantitysummary 
from
    tbl_m365_customer
where
    billingmonth = :billingbucket
group by 
    servicefamily,
    servicename, 
    product
order by 
    costsummary desc
;

Customer_TOP70

-- customername top70 の請求金額
select top 70
    cast(sum(pretaxcost) as int) as costsummary,
    customername
from
    tbl_billing
where
    billingmonth = :billingbucket
group by
    customername
order by
    costsummary desc
;

BillingMonth

-- 月毎の請求金額
select
    cast(sum(pretaxcost) as int),
    billingmonth
from
    tbl_billing
group by
    billingmonth
order by
    billingmonth
;

Reseller_CustomerName

-- RESELLERMPNID毎の請求金額
select
    cast(sum(pretaxcost) as int) as costsummary,
    RESELLERMPNID
from
    tbl_billing
where
    billingmonth = :billingbucket
group by
    RESELLERMPNID
order by
    costsummary desc
;

-- customername毎の請求金額
select
    cast(sum(pretaxcost) as int) as costsummary,
    customername
from
    tbl_billing
where
    billingmonth = :billingbucket
group by
    customername 
order by
    costsummary des
;

-- RESELLERMPNID-customername毎の請求金額
select
    cast(sum(pretaxcost) as int) as costsummary,
    RESELLERMPNID,
    customername
from
    tbl_billing
where
    billingmonth = :billingbucket
group by
    RESELLERMPNID,
    customername 
order by
    RESELLERMPNID
;

Providor_ProductName

-- ServiceFamily毎の請求金額
select 
    servicefamily, 
    cast(sum(pretaxcost) as int) as costsummary 
from 
    tbl_billing 
group by 
    servicefamily 
order by c
    ostsummary desc
;

-- ServiceName毎の請求金額
select 
    servicename, cast(sum(pretaxcost) as int) as costsummary 
from 
    tbl_billing 
group by 
    servicename 
order by 
    costsummary desc
;

-- productordername毎の請求金額
select 
    productordername, cast(sum(pretaxcost) as int) as costsummary 
from 
    tbl_billing 
group by 
    productordername 
order by 
    costsummary desc
;


-- provider/ProductOrderName毎の請求金額
select
    cast(sum(pretaxcost) as int) as costsummary,
    provider,
    productordername
from
    tbl_billing
where
    billingmonth = :billingbucket
group by
    provider,
    productordername
order by
    provider
;

フィルターの管理

3つのフィルタを新規に作成し、上記SQL文の where 箇所で利用しています。
image.png

## 例
where
    billingmonth = :billingbucket

下記フィルタ表示名「請求月」を利用しています。

利用イメージは下絵のようになります。
image.png

請求月 フィルタの定義

表示名 : 請求月
SQLキーワード : :billingbucket
説明 : BillingMonthでフィルタリング
ロール : ACCOUNTADMIN
ウェハウス : WH_COSTMANAGE
クエリ : 
    select distinct billingmonth from tbl_billing order by billingmonth desc;
    決して更新しない
値のタイプ : テキスト
複数の値の選択可能 : ON
「すべて」オプションを含める : OFF
「その他」オプションを含める : OFF

M365_Customer フィルタの定義

表示名 : M365_Customer
SQLキーワード : :m365_customer
説明 : M365の上位顧客
ロール : ACCOUNTADMIN
ウェハウス : WH_COSTMANAGE
クエリ : 
    SELECT customername FROM select_actual_customer EXCEPT SELECT customername FROM select_usage_customer;
    毎時更新
値のタイプ : テキスト
複数の値の選択可能 : OFF
「すべて」オプションを含める : OFF
「その他」オプションを含める : OFF

Usage-顧客 フィルタの定義

表示名 : Usage-顧客
SQLキーワード : :usage_customer
説明 : Usage-上位顧客
ロール : ACCOUNTADMIN
ウェハウス : WH_COSTMANAGE
クエリ : 
    select customername from select_usage_customer;
    決して更新しない
値のタイプ : テキスト
複数の値の選択可能 : OFF
「すべて」オプションを含める : OFF
「その他」オプションを含める : OFF

まとめ

Snowflake に取り込んだ Azure請求データ をワークシート内のSQL文で抽出できることを確認しました。また、フィルタ機能を利用し、動的にデータ抽出できることも確認できました。

参考記事

以下の記事を参考にさせていただきました。感謝申し上げます。
SQL 複数の行をまとめる(集約関数/group by/having)

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0