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

## 概要

[このプログラム](https://qiita.com/turupon/items/73d2ca55f0fd2347c702)で取得した csv のAzureの請求データを [この記事](https://qiita.com/turupon/items/0070168be0532c7d65ec) で Snowflake に取り込みました。今回は Snowflake に取り込んだそのデータをワークシート上でSQL文を定義することにより該当データを抽出できることを確認しました。

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

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

### 実行環境

macOS Ventura 13.0
python 3.8.12
snowsql 1.2.24

### 参考ドキュメント

[ワークシートを使用したデータのクエリ](https://docs.snowflake.com/ja/user-guide/ui-snowsight-query.html)

## ワークシート

２つのフォルダと 10個のワークシートを作成しました
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/678e3445-c717-9035-a662-57b0807d5865.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/2e88d6a7-e313-5c91-5ca9-ffd59d3bb5f9.png)


```
## 例
where
    billingmonth = :billingbucket

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

利用イメージは下絵のようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/544022/d49fd611-b062-4224-c209-03e49f850755.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)](https://itsakura.com/sql-groupby-having)