概要

このプログラムで取得した 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

事前準備

このドキュメントを参考に　SnowSQL のインストールを完了していること

参考ドキュメント

Snowflakeを20分で紹介
 Snowsightをはじめるにあたり
 Snowsight: Snowflakeウェブインターフェイス

取込ステップ

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

仮想ウェアハウスの作成

## 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 およびサポートされている関数および演算子を使用して、テーブルにロードされたデータをクエリできます。

## 請求月：「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 に取り込むことができました。後は分析のためのワークシートの作成ですね、、、、、