Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

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

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

Posted at

概要

このプログラムで取得した 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 に取り込むことができました。後は分析のためのワークシートの作成ですね、、、、、

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

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?