SnowflakeでAzureの請求データを分析してみました - 取込編

## 概要

[このプログラム](https://qiita.com/turupon/items/73d2ca55f0fd2347c702)で取得した csv のAzureの請求データを Snowflake に取り込んで分析を実施します。今回は取り込みまでを記載しています。 ローカルにある 2021年3月〜2022年10月までの20ヶ月分（20個のCSVファイル）の請求データを取り込みます。

- 取込ステップ
  - STEP-1 : SnowSQL で Snowflake にログインする
  - STEP-2 : Snowflake オブジェクトを作成する
  - STEP-3 : データファイルをステージングする
  - STEP-4 : データをターゲットテーブルにコピーする
  - 
- 操作確認
  - ロードされたデータのクエリ
  - 接続の終了
  - 再接続後のアタッチ
  - 追加でローカルのCSVファイルをアップロード
  - 追加でデータコピー
  - クリーンアップ

### 実行環境

macOS Ventura 13.0
python 3.8.12
snowsql 1.2.24

### 事前準備

[このドキュメント](https://docs.snowflake.com/ja/user-guide/snowsql-install-config.html) を参考に　SnowSQL のインストールを完了していること


### 参考ドキュメント

[Snowflakeを20分で紹介](https://docs.snowflake.com/ja/user-guide/getting-started-tutorial.html)
[Snowsightをはじめるにあたり](https://docs.snowflake.com/ja/user-guide/ui-snowsight-gs.html#getting-started-with-snowsight)
[Snowsight: Snowflakeウェブインターフェイス](https://docs.snowflake.com/ja/user-guide/ui-snowsight.html)

## 取込ステップ

### STEP-1 :  SnowSQL で Snowflake にログインする

```
$ snowsql -a <account_identifier> -u <user_name>

## ログイン（user_name : turupon）
$ snowsql -a <account_identifier> -u turupon
Password: 
* SnowSQL * v1.2.24
Type SQL statements or !help
turupon#(no warehouse)@(no database).(no schema)>
```

### STEP-2 :  Snowflake オブジェクトを作成する

データベースの作成

```
## CREATE DATABASE コマンドを使用して db_costmanage データベースを作成
create or replace database db_costmanage;

turupon#(no warehouse)@(no database).(no schema)>create or replace database db_costmanage;
+----------------------------------------------+                                
| status                                       |
|----------------------------------------------|
| Database DB_COSTMANAGE successfully created. |
+----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.266s


## 作成したデータベースとスキーマの確認
select current_database(), current_schema();

turupon#(no warehouse)@DB_COSTMANAGE.PUBLIC>select current_database(), current_schema();
+--------------------+------------------+                                       
| CURRENT_DATABASE() | CURRENT_SCHEMA() |
|--------------------+------------------|
| DB_COSTMANAGE      | PUBLIC           |
+--------------------+------------------+
1 Row(s) produced. Time Elapsed: 0.188s
```

テーブルの作成

```
## CREATE TABLE コマンドを使用して db_costmanage.public に tbl_billing という名前のテーブルを作成
create or replace table tbl_billing (
  PreTaxCost float ,
  UsageQuantity float ,
  ResellerMPNId string ,
  CustomerName string ,
  CustomerTenantDomainName string ,
  SubscriptionName string ,
  ResourceGroup string ,
  ResourceType string ,
  ServiceFamily string ,
  ServiceName string ,
  Product string ,
  UnitOfMeasure string ,
  PricingModel string ,
  Provider string ,
  ProductOrderName string ,
  BillingMonth string
  );

turupon#(no warehouse)@DB_COSTMANAGE.PUBLIC>create or replace table tbl_billing (
                                              PreTaxCost float ,
                                              UsageQuantity float ,
                                              ResellerMPNId string ,
                                              CustomerName string ,
                                              CustomerTenantDomainName string ,
                                              SubscriptionName string ,
                                              ResourceGroup string ,
                                              ResourceType string ,
                                              ServiceFamily string ,
                                              ServiceName string ,
                                              Product string ,
                                              UnitOfMeasure string ,
                                              PricingModel string ,
                                              Provider string ,
                                              ProductOrderName string ,
                                              BillingMonth string
                                              );
+-----------------------------------------+                                     
| status                                  |
|-----------------------------------------|
| Table TBL_BILLING successfully created. |
+-----------------------------------------+
1 Row(s) produced. Time Elapsed: 0.587s
```

[仮想ウェアハウスの作成](https://docs.snowflake.com/ja/sql-reference/sql/create-warehouse.html)

```
## CREATE WAREHOUSE コマンドを使用して、 wh_costmanage という名前のX-Smallウェアハウスを作成
create or replace warehouse wh_costmanage with
  warehouse_size='X-SMALL'
  auto_suspend = 60
  auto_resume = true
  initially_suspended=true;

turupon#(no warehouse)@DB_COSTMANAGE.PUBLIC>create or replace warehouse wh_costmanage with
                                              warehouse_size='X-SMALL'
                                              auto_suspend = 60
                                              auto_resume = true
                                              initially_suspended=true;
+-----------------------------------------------+                               
| status                                        |
|-----------------------------------------------|
| Warehouse WH_COSTMANAGE successfully created. |
+-----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.683s


## 現在のセッションで使用されていウェアハウスの確認
select current_warehouse();

turupon#WH_COSTMANAGE@DB_COSTMANAGE.PUBLIC>select current_warehouse();
+---------------------+                                                         
| CURRENT_WAREHOUSE() |
|---------------------|
| WH_COSTMANAGE       |
+---------------------+
1 Row(s) produced. Time Elapsed: 0.172s
```

### STEP-3 : データファイルをステージングする

PUT を実行して、作成した tbl_billing テーブルに提供されたテーブルステージにローカルデータファイルをアップロードします。

```
put file://./billing_data/cm2sf_2021-0*.csv @db_costmanage.public.%tbl_billing;
put file://./billing_data/cm2sf_*.csv @db_costmanage.public.%tbl_billing;

## 2021-03 〜 2021-09 の7ヶ月分のCSVファイルをアップロード
turupon#WH_COSTMANAGE@DB_COSTMANAGE.PUBLIC>put file://./billing_data/cm2sf_2021-0*.csv @db_costmanage.public.%tbl_billing;
+-------------------+----------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source            | target               | source_size | target_size | source_compression | target_compression | status   | message |
|-------------------+----------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| cm2sf_2021-03.csv | cm2sf_2021-03.csv.gz |       10792 |        1728 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2021-04.csv | cm2sf_2021-04.csv.gz |       15587 |        2576 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2021-05.csv | cm2sf_2021-05.csv.gz |      167130 |       14272 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2021-06.csv | cm2sf_2021-06.csv.gz |      246392 |       21168 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2021-07.csv | cm2sf_2021-07.csv.gz |      356728 |       29680 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2021-08.csv | cm2sf_2021-08.csv.gz |      317797 |       27568 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2021-09.csv | cm2sf_2021-09.csv.gz |      396888 |       32320 | NONE               | GZIP               | UPLOADED |         |
+-------------------+----------------------+-------------+-------------+--------------------+--------------------+----------+---------+
7 Row(s) produced. Time Elapsed: 2.000s


## 正常にステージングされたファイルのリストの表示
list @db_costmanage.public.%tbl_billing;

turupon#WH_COSTMANAGE@DB_COSTMANAGE.PUBLIC>list @db_costmanage.public.%tbl_billing;
                                                                                
+----------------------+-------+----------------------------------+------------------------------+
| name                 |  size | md5                              | last_modified                |
|----------------------+-------+----------------------------------+------------------------------|
| cm2sf_2021-03.csv.gz |  1728 | 3199dd775c50318d794da503729f9f2a | Wed, 29 Nov 2022 05:12:26 GMT |
| cm2sf_2021-04.csv.gz |  2576 | 6940036a2627a6760394df497b86656a | Wed, 29 Nov 2022 05:12:26 GMT |
| cm2sf_2021-05.csv.gz | 14272 | 0d6ecdfd10e6096c7ce60d04da73d579 | Wed, 29 Nov 2022 05:12:26 GMT |
| cm2sf_2021-06.csv.gz | 21168 | cdf8e4dca39edd9167e1984942b68c3b | Wed, 29 Nov 2022 05:12:26 GMT |
| cm2sf_2021-07.csv.gz | 29680 | 7f0fef92c5fb2397d6e46c0b5def9636 | Wed, 29 Nov 2022 05:12:26 GMT |
| cm2sf_2021-08.csv.gz | 27568 | 63aa906a0300ae582031a3b6eae9e23d | Wed, 29 Nov 2022 05:12:26 GMT |
| cm2sf_2021-09.csv.gz | 32320 | 053b114df32cadc7a05326087f2d92e1 | Wed, 29 Nov 2022 05:12:26 GMT |
+----------------------+-------+----------------------------------+------------------------------+
7 Row(s) produced. Time Elapsed: 0.214s
```

### STEP-4 : データをターゲットテーブルにコピーする

COPY INTO <テーブル> を実行して、ステージングされたデータをターゲットテーブルにロードします。このコマンドには、 稼働中の仮想ウェハウスが必要です（STEP-2で作成済み）

```
## データ取込中にエラーが発生時、そのファイルのデータを取込まないようにする定義
copy into tbl_billing
  from @%tbl_billing
  file_format = (type = csv field_optionally_enclosed_by='"')
  pattern = '.*cm2sf_2021-0[1-9].csv.gz'
  on_error = 'skip_file';

## データ取込中にエラーが発生時、そのレコードのデータのみ取込まないようにする定義
copy into tbl_billing
  from @%tbl_billing
  file_format = (type=csv SKIP_HEADER=1)
  pattern = '.*cm2sf_2021-0[1-9].csv.gz'
  on_error = 'continue';


## エラーが発生時、そのレコードのデータのみスキップする方法で取込を実行
turupon#WH_COSTMANAGE@DB_COSTMANAGE.PUBLIC>copy into tbl_billing
                                             from @%tbl_billing
                                             file_format = (type=csv SKIP_HEADER=1)
                                             pattern = '.*cm2sf_2021-0[1-9].csv.gz'
                                             on_error = 'continue';
+----------------------+------------------+-------------+-------------+-------------+-------------+-----------------------------------------------------------------+------------------+-----------------------+----------------------------------+
| file                 | status           | rows_parsed | rows_loaded | error_limit | errors_seen | first_error                                                     | first_error_line | first_error_character | first_error_column_name          |
|----------------------+------------------+-------------+-------------+-------------+-------------+-----------------------------------------------------------------+------------------+-----------------------+----------------------------------|
| cm2sf_2021-06.csv.gz | LOADED           |         911 |         911 |         911 |           0 | NULL                                                            |             NULL |                  NULL | NULL                             |
| cm2sf_2021-04.csv.gz | LOADED           |          65 |          65 |          65 |           0 | NULL                                                            |             NULL |                  NULL | NULL                             |
| cm2sf_2021-03.csv.gz | LOADED           |          45 |          45 |          45 |           0 | NULL                                                            |             NULL |                  NULL | NULL                             |
| cm2sf_2021-05.csv.gz | LOADED           |         616 |         616 |         616 |           0 | NULL                                                            |             NULL |                  NULL | NULL                             |
| cm2sf_2021-08.csv.gz | PARTIALLY_LOADED |        1213 |        1212 |        1213 |           1 | Field delimiter ',' found while expecting record delimiter '\n' |              996 |                    90 | "TBL_BILLING"["BILLINGMONTH":16] |
| cm2sf_2021-07.csv.gz | LOADED           |        1347 |        1347 |        1347 |           0 | NULL                                                            |             NULL |                  NULL | NULL                             |
| cm2sf_2021-09.csv.gz | LOADED           |        1513 |        1513 |        1513 |           0 | NULL                                                            |             NULL |                  NULL | NULL                             |
+----------------------+------------------+-------------+-------------+-------------+-------------+-----------------------------------------------------------------+------------------+-----------------------+----------------------------------+
7 Row(s) produced. Time Elapsed: 1.503s

取込を実行した結果、2021-08ファイルから1件のデータのみ取り込めませんでした
```

## 操作確認

### ロードされたデータのクエリ

標準の SQL およびサポートされている 関数 および 演算子 を使用して、テーブルにロードされたデータをクエリできます。
<!-- 標準の DML コマンド を使用して、ロードされたデータの更新や追加データの挿入など、データを操作することもできます。 -->

```
## 請求月：「2021-08」のデータを取得するクエリ
select * from tbl_billing where billingmonth='2021-08';

## 請求月毎に請求金額の合計を取得するクエリ
select billingmonth, sum(PreTaxCost), sum(UsageQuantity) from tbl_billing group by BillingMonth;
select billingmonth, sum(PreTaxCost), sum(UsageQuantity) from tbl_billing group by BillingMonth order by BillingMonth;
```

### 接続の終了

```
!exit
```

### 再接続後のアタッチ

```
## ログイン
$ snowsql -a <account_identifier> -u turupon
Password: 
* SnowSQL * v1.2.24
Type SQL statements or !help
turupon#(no warehouse)@(no database).(no schema)>


## データベースへのアタッチ
turupon#(no warehouse)@(no database).(no schema)>use database DB_COSTMANAGE;
+----------------------------------+                                            
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.521s


## 仮想ウェハウスへのアタッチ
turupon#(no warehouse)@DB_COSTMANAGE.PUBLIC>use WAREHOUSE WH_COSTMANAGE;
+----------------------------------+                                            
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.406s


## アタッチ確認
turupon#WH_COSTMANAGE@DB_COSTMANAGE.PUBLIC>select current_database(), current_schema(), current_warehouse();
+--------------------+------------------+---------------------+                 
| CURRENT_DATABASE() | CURRENT_SCHEMA() | CURRENT_WAREHOUSE() |
|--------------------+------------------+---------------------|
| DB_COSTMANAGE      | PUBLIC           | WH_COSTMANAGE       |
+--------------------+------------------+---------------------+
1 Row(s) produced. Time Elapsed: 0.079s
```

### 追加でローカルの CSVファイル をアップロード

重複しているものはスキップされるみたい、、、、

```
## 全ての月の請求データを取り込んでみます
turupon#WH_COSTMANAGE@DB_COSTMANAGE.PUBLIC>put file://./billing_data/cm2sf_*.csv @db_costmanage.public.%tbl_billing;
+-------------------+----------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source            | target               | source_size | target_size | source_compression | target_compression | status   | message |
|-------------------+----------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| cm2sf_2021-03.csv | cm2sf_2021-03.csv.gz |       10792 |           0 | NONE               | GZIP               | SKIPPED  |         |
| cm2sf_2021-04.csv | cm2sf_2021-04.csv.gz |       15587 |           0 | NONE               | GZIP               | SKIPPED  |         |
| cm2sf_2021-05.csv | cm2sf_2021-05.csv.gz |      167130 |           0 | NONE               | GZIP               | SKIPPED  |         |
| cm2sf_2021-06.csv | cm2sf_2021-06.csv.gz |      246392 |           0 | NONE               | GZIP               | SKIPPED  |         |
| cm2sf_2021-07.csv | cm2sf_2021-07.csv.gz |      356728 |           0 | NONE               | GZIP               | SKIPPED  |         |
| cm2sf_2021-08.csv | cm2sf_2021-08.csv.gz |      317797 |           0 | NONE               | GZIP               | SKIPPED  |         |
| cm2sf_2021-09.csv | cm2sf_2021-09.csv.gz |      396888 |           0 | NONE               | GZIP               | SKIPPED  |         |
| cm2sf_2021-10.csv | cm2sf_2021-10.csv.gz |      668534 |       55984 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2021-11.csv | cm2sf_2021-11.csv.gz |      705343 |       61184 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2021-12.csv | cm2sf_2021-12.csv.gz |      929770 |       81728 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-01.csv | cm2sf_2022-01.csv.gz |     1430556 |      118416 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-02.csv | cm2sf_2022-02.csv.gz |     1651889 |      138496 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-03.csv | cm2sf_2022-03.csv.gz |     1872143 |      161040 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-04.csv | cm2sf_2022-04.csv.gz |     1821375 |      153984 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-05.csv | cm2sf_2022-05.csv.gz |     1827973 |      153744 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-06.csv | cm2sf_2022-06.csv.gz |     1948771 |      164448 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-07.csv | cm2sf_2022-07.csv.gz |     1972527 |      168080 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-08.csv | cm2sf_2022-08.csv.gz |     2085193 |      176768 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-09.csv | cm2sf_2022-09.csv.gz |     2143407 |      180368 | NONE               | GZIP               | UPLOADED |         |
| cm2sf_2022-10.csv | cm2sf_2022-10.csv.gz |     2215355 |      186688 | NONE               | GZIP               | UPLOADED |         |
+-------------------+----------------------+-------------+-------------+--------------------+--------------------+----------+---------+
20 Row(s) produced. Time Elapsed: 3.119s
```

### 追加でデータコピー

```
## 2021-10〜12 のデータ
copy into tbl_billing
  from @%tbl_billing
  file_format = (type=csv SKIP_HEADER=1)
  pattern = '.*cm2sf_2021-1[0-2].csv.gz'
  on_error = 'continue';

## 2022-01〜09 のデータ
copy into tbl_billing
  from @%tbl_billing
  file_format = (type=csv SKIP_HEADER=1)
  pattern = '.*cm2sf_2022-0[1-9].csv.gz'
  on_error = 'continue';

## 2022-10〜12 のデータ
copy into tbl_billing
  from @%tbl_billing
  file_format = (type=csv SKIP_HEADER=1)
  pattern = '.*cm2sf_2022-1[0-2].csv.gz'
  on_error = 'continue';


## とある項目のレコード数
turupon#WH_COSTMANAGE@DB_COSTMANAGE.PUBLIC>select count(servicename) from TBL_BILLING;
+--------------------+                                                          
| COUNT(SERVICENAME) |
|--------------------|
|              90530 |
+--------------------+
1 Row(s) produced. Time Elapsed: 0.299s
```

### クリーンアップ

 DROP <オブジェクト> ステートメントを実行して、初期の状態に戻します。

 ```
drop table if exists tbl_billing;
drop database if exists db_costmanage;
drop warehouse if exists wh_costmanage;
```

## まとめ

これで SnowSQL を利用して Azureの請求データをSnowflake に取り込むことができました。後は分析のためのワークシートの作成ですね、、、、、