Azure SQL Database 単一データベースの作成方法
こんにちは、kc-Dreamです。
今回は、SQL Databaseの単一（シングル）データベースをAzure Portalから構築する方法についてご紹介します。
Azure SQL Databaseについて
Azure SQL Databaseは下図の種類があり、今回ご紹介するのはシングルデータベースとなります。
- 単一データベースは、フル マネージドの分離されたデータベースを表します。 このオプションは、信頼性の高い 1 つのデータ ソースを必要とする最新のクラウド アプリケーションとマイクロサービスがある場合に使用できます。 単一データベースは Microsoft SQL Server データベース エンジンの包含データベースに似ています。
- マネージド（管理者常駐型）インスタンスは、Microsoft SQL Server データベース エンジンのフル マネージド インスタンスです。 これには、一緒に使用できる一連のデータベースが含まれています。 このオプションは、オンプレミスの SQL Server データベースを Azure クラウドに簡単に移行するため、および SQL Server データベース エンジンが提供するデータベース機能を使用する必要があるアプリケーションに使用します。
- エラスティックプールは、CPU やメモリなどのリソースの共有セットを含む単一データベースのコレクションです。 単一データベースはエラスティック プールの内外に移動できます。
構築方法
Azure PortalからSQL Databaseを選択し、新規SQL Databaseを作成していきます。
基本
- サブスクリプションの選択
- リソースグループの選択
- 新規リソースグループの作成も可能
- データベース名を入力
- サーバー
- SQLデータベースがのるサーバを新規作成を行います。
- 既存サーバを使用することも可能です。
- 新規作成する場合、サーバ名、ログインユーザ名、パスワード、場所を入力し作成を進めます。※SQL DatabaseはPaaSサービスなのでサーバを直接設定するようなことはできません。
- エラスティックプールの使用有無
- 本記事ではシングルデータベースの構築についてなので無を選択します。
- データベースの設定より使用するSQL データベースのスペック等を選択します。
ネットワーク
- ここではSQL Databaseへの接続方法について選択します。
追加設定
- データソース
- 新規に作成し使用する場合、なしを選択
- バックアップから復元の場合には、バックアップを選択
- データベース照合順序
- 構築時にしか選択できません。変更したい場合、SQL Databaseを再作成する必要があります。
- 照合順序と Unicode のサポート
タグ
- タグ設定を行う場合、入力します。
確認および作成
- 選択及び入力した設定項目を確認できます。また、SQL Databaseが起動できるかどうかの検証が行われます。
- 問題なければ作成を選択
- デプロイ後、バックアップ設定は変更することをオススメします。
おわりに
今回はシングルデータベースの構築についてご紹介しました。
マネージドインスタンス、エラスティックプールについての構築方法についても今後ご紹介したいと思います。
Azureでファイルサーバを検討する(VM for Windows)
こんにちは、kc-dreamです。
今回はAzureでファイルサーバを構築する際に考えられる構成をご紹介していきます。
その中でも、Azure 仮想マシン(Windows)を利用した場合についてまとめます。
Azureでファイルサーバを構築する際の構成
①Azure Filesを利用
②Azure 仮想マシン(Windows)を利用
この記事では、上記2パターンのうちAzure 仮想マシン(Windows)に焦点をあてご紹介します。
Azure Filesを利用した場合
【メリット】
- ファイル共有の最大サイズが 1TiB
- 構築が簡単
【デメリット】
- NTFS による権限設定ができない
- オンプレミス ドメインのリソースが利用できない
- 同時アクセス数が多い場合、パフォーマンス低下が懸念される
- SMB ポート利用が制限されるネットワークでは利用が現実的でない
Azure 仮想マシン(Windows)を利用した場合
【メリット】
- 既存のオンプレミス環境との互換性が高く、ハイブリッドの構成も可能
- オンプレミス ドメインのリソースを利用
- 記憶域プールを構成することで 1 ドライブあたりの容量を最大化できる
- スループットの変化に柔軟に対応できる
- 容量の追加が容易
- Reserved Instance でコストメリットを訴求
【デメリット】
- 冗長構成をとる場合価格が2倍以上になる（単純にVMが2台構成）
- ネットワークの構成やストレージの管理など、構築や管理にそれなりのコストがかかる
Azure 仮想マシンで使用するストレージ
Azure 仮想マシンで使用するストレージは、管理ディスクと非管理ディスクの2通りが利用できます。
管理ディスクと非管理ディスクでは課金の考え方が異なるため、SSD を利用しない場合は非管理ディスクを選択することでコストメリットが出せる場合もあります。
Azure 仮想マシンがサポートするディスクドライブの最大容量は4TBですが、Windows Serverの機能である記憶域プールを利用することで、より大容量のドライブを作成することが可能です。
また、使用するVMによりアタッチできるディスク本数が変動します。
下記記事は参考情報になります。
Azure の Windows 仮想マシンのサイズ
複数のディスクを利用する場合、ディスクを配置するストレージアカウントは別々にすることをお勧めです。
同じストレージ アカウントに複数のディスクを配置しますと、ストレージアカウントのiOPS上限の影響を受けパフォーマンスが低下する場合があります。
※管理ディスクにはストレージアカウントの考え方がないため、管理ディスクを利用する場合は考慮の必要はありません。
Azure 仮想マシンとの接続方法
オンプレミスのドメインリソースを利用する場合、VPNやExpressRouteなどの閉域回線を利用します。
ExpressRoute は価格は高めですが、送信転送量を料金に含めることで通信にかかる料金を固定できるプランが存在します。
一定以上の帯域を確保する必要がある場合、ExpressRoute の利用がお勧めです。
VPNやExpressRouteを利用する場合、VPN Gatewayが必要になります。
VPN Gateway の価格
冗長構成を組む場合
ファイルサーバーを冗長化するソリューションとして記憶域スペースダイレクトありますが、これはAzure 仮想マシンでも有効です。
ゲスト仮想マシン クラスターで記憶域スペース ダイレクトの使用
※この場合仮想マシンは最低2台、ストレージ容量は2倍必要なので、コストも2倍になります。
おわりに
今回ご紹介した以外にも、様々な方法で構築することが可能ですが、Azureでファイルサーバを検討する際の参考になれば幸いです。
Azure Backup 番外編
こんにちは、kc-dreamです。
今回は、Azure Backup 番外編ということで、スナップショット取得時の挙動について簡単ではありますが、まとめたいと思います。
スナップショット/復元ポイントの動作について
Azure Backup のインスタントリストアでは、まずVMディスクのスナップショットを作成し、
ディスクと同じストレージアカウント上に保存します。その後、増分データがRecovery Servicesコンテナーに送られ、保存されます。
このスナップショットは、バックアップポリシーの "インスタント回復スナップショットの保持期間" で決められた日数保持され、その後自動的に削除されます。
スナップショット/復元ポイントの動作については、上記が前提となっております。
- コンテナーへのデータ転送の終了を待たずに、復旧に利用できるバックアップジョブの一環として取得されるスナップショットを使用できます。 これにより、復元をトリガーする前にスナップショットをコンテナーにコピーする待機時間が短縮されます。
- スナップショットを既定で2日間ローカルに保持することで、バックアップと復元の時間が短縮されます。 この既定のスナップショット リテンション期間の値は、1 から5日の間の任意の値に構成できます。
- 最大32TBのディスク サイズがサポートされます。Azure Backupでは、ディスクのサイズ変更は推奨されません。
- Standard HDDディスクおよび Premium SSDディスクと共にStandard SSDディスクがサポートされます。
- 復元時に、(ディスクごとの) アンマネージド VM の元のストレージ アカウントを使用できます。この機能は、ストレージアカウント間に分散しているディスクが VM にある場合でも使用できます。さまざまなVM構成で復元操作が速くなります。
- インスタントリストアでPremium Storageを使用している VM のバックアップについては、割り当てられた合計ストレージ領域の50% の空き領域 (最初のバックアップにのみ必要) を割り当てることをお勧めします。最初のバックアップが完了すると、バックアップに50% の空き領域は不要になります。
復元時間について
スナップショットが削除された復元ポイントからの復元では、Recovery Servicesコンテナーから
バックアップデータの転送に伴い復元に時間がかかります。
※復元対象のデータ容量により変わります。
Azure Backup のインスタント リストア機能を使用してバックアップと復元のパフォーマンスを改善する
復元ポイントについて
復元ポイントは保持期間に従って自動的に管理され、個別に手動で削除することはできません。
復元ポイントおよびバックアップデータを削除するには、VM のバックアップを停止する必要がございます。
VM の保護を停止する - Azure VM のバックアップを管理する
復元可能タイミング
- 上図にあるように①のTake snapshotジョブが完了した時点でBackup自体は完了しているので、VMの復元が可能です。
- ②については、Recovery ServicesコンテナーへBackupデータを転送するため時間（24時間以内完了）がかかります。
おわりに
番外編ということでスナップショット取得時の挙動についてご紹介しました。
Azure Backupの復元方法について
こんにちは、kc-dreamです。
今回はAzure Backupの復元方法についてご紹介したいと思います。
Azure Backupとは
Azure Backup サービスでは、Microsoft Azure クラウドにデータをバックアップします。 オンプレミスのマシンとワークロード、および Azure 仮想マシン(VM) をバックアップできます。
下記から抜粋
Azure Backup とは
Azure Backupを使用した復元方法
Azure Backupには大きく３種類の復元方法が用意されています。
本記事では3種類の復元方法について簡単ではありますが、ご紹介していきたいと思います。
1,VM の新規作成
Azure VM が新規作成され、復元されたディスクがアタッチされます。
このオプションでは、容易にVMを作成いただける反面、細かな構成の変更を自身で実施する必要があります。
- バックアップされた VM が静的 IP アドレスを持っていた場合、復元された VM は競合を回避するために動的 IP アドレスを持つことになる。
- Ubuntu など cloud-init ベースの Linux ディストリビューションを使用している場合、セキュリティ上の理由から、復元後にパスワードがブロックされます。 復元した VM で VMAccess 拡張機能を使用して、パスワードをリセットしてください。
- VM でドメイン コントローラーとの関係が破損しているために復元された VM にアクセスできない場合は、下記リンクの手順に従って VM を起動します。
- 復元された VM には可用性セットがありません。 ディスクの復元オプションを使用する場合、提供されているテンプレートまたは PowerShell を 使用してディスクから VM を作成するときに可用性セットを指定することができます。
2,ディスクの復元
こちらのオプションでは、ディスクのみが復元されます。
復元されたディスクを利用して、自由な構成のVMを後から作成することができます。
例えば、PowerShell を使用して Azure VM を作成する方法として、
以下の公開情報に記載のような手順を実施いただくことができます。
既存の VHD を管理ディスク (Managed Disk) に変換し、VM をデプロイする
※バックアップ対象が管理ディスクの場合にも、手順 2 に記載のサンプルコードをお役立ていただけるかと存じます。
3,ディスクの置き換えについて
ディスクの置き換えオプションでは、既存の VM のディスクのみが付け替えられます。
このため、VM の設定をすべて引き継ぐことができる、最も簡単な復元方法となります。
VMにアタッチされていたディスクはすべてディタッチされた状態で残り、
新たに復元され作成されたディスクがアタッチされ、VM が起動します。
ディスクの置き換えオプションは現在管理ディスクでのみサポートされております。
その他の制限事項については以下のリンク参照願います。
復元オプション - Azure VM の復元
*既存ディスクの置き換えは、暗号化されていないマネージド VM でサポートされています。
非管理ディスク、汎用化された VM、またはカスタム イメージを使用して作成された VM では、サポートされていません。
おわりに
簡単ではありますが、Azure Backupの復元方法についてまとめたので参考にしていただければ幸いです。
Excel / Azure Notebooks / Databricks で同じことをやってみる #4 【Databricks編】
はじめに
Excel / Azure Notebook / Databricks で同じことをやってみる #1 【はじめに】
Excel / Azure Notebook / Databricks で同じことをやってみる #2 【Excel編】
Excel / Azure Notebooks / Databricks で同じことをやってみる #3 【Azure Notebook編】
に続き、Databricks を用いて簡易集計及び可視化を行います。
Databricksとは？
概要
以前の記事でも取り上げましたが、こんな特徴のデータ分析基盤プラットフォームです。
・Apache Spark環境を数分でセットアップ
-分析環境がすぐに作れる
-スケーラブル
-自動でスケーリング可能
-Sparkを作った人達が、Spark用にカリカリにチューニングしたプラットフォーム
・Notebookをバッチスクリプトとして使える
-例えば、データストリームに対して、Aの処理を一週間に一回かけて、Bのテーブルに追記する、
という処理が必要な場合、Aの処理を記載したNotebookを、バッチジョブとして設定できる
・バージョン管理が楽
-いわゆるタイムマシーン機能が使える
-複数人での作業がしやすい
・マジックコマンドが便利
-デフォルト言語としてPythonを設定しても、セルの頭にマジックコマンド(%sql)でクエリが書ける
・簡単に可視化
-発行したクエリをそのままグラフ化
-別途ライブラリのインポート不要
・オートターミネーションで安心
-クラスタをxx分未使用の場合には停止(=課金されない) といった設定ができる
-高額なクラスタを立ち上げっぱなしで、気が付いたらものすごい課金が…みたいなケースを回避できる
-今回発生した費用のまとめは後述します
設定
こちらのドキュメントを参照いただき、
・Azure Portal へのサインイン
・Spark クラスタの作成
・notebookの作成
まで行ったら準備完了です。
操作のきほん
Notebook自体の操作は、Jupyter Notebookとほとんど同じです。結構直感的に操作できますが、迷ったときは公式ドキュメントを参照しましょう。
データの準備
ストリーミングデータや大規模なテーブルを接続するのであれば、データの保存先は、Blob Storage Gen 2 や Delta Lake が候補になります。
今回は、そこまで大きくないCSVデータx3ですので、Databricksにテーブルを作成します。
ホーム画面の下記の場所に、CSVデータをドラッグアンドドロップ
Create Table with UIをクリック
作成したクラスタを選択し、Preview Tableをクリック
デフォルトだと、列名やスキーマはこのようになっています
First Row is Header オプションで、一行目を列名として読みこみ、Infer Schema オプションの指定でスキーマを類推してくれます。
Create Tableをクリックし、しばらくするとテーブルの作成が完了します。CSVファイル3つそれぞれでこれらの作業を行います。
Data タブをクリックすると、テーブルを確認できます。
やってみる
流れ
Azure Notebookの回でやったように、Sparkで中間テーブルを作成します。
そこから先は、SQLオンリーでグラフ化まで行います。
手順
PySparkのライブラリをインポート。今回の用途であればこれだけでOK
[crayon-5e4b339b9b21f690415555/]
先ほど作成したテーブルの名称を変数に格納
[crayon-5e4b339b9b230300466286/]
それぞれのテーブルをSparkで読み込み
[crayon-5e4b339b9b23c837789897/]
テーブル結合のために、必要な列名を確認しておきます (結果は省略)
[crayon-5e4b339b9b249246166735/]
中間テーブル (name: sql_tbl) を作成します。コードは#3 Azure Notebookの回で説明したものと同じです。
[crayon-5e4b339b9b255718901249/]
ここからは全部SQLで記載していきます。%sqlとセルの頭に入れれば、SQL文として処理してくれます。結合処理を再度行うのは面倒なので、このテーブルは永続化しておきましょう。一時テーブルで良いときは、CREATE TABLE
[crayon-5e4b339b9b263285655966/]
以下で中間テーブルのスキーマが確認できます
[crayon-5e4b339b9b26f447274294/]
結果
集計後テーブル (name: sql_tbl2) を作り、永続化させます。
[crayon-5e4b339b9b27b896166830/]
集計後テーブルに対してクエリを発行します。可視化した時に同じくらいのスケールに収まるように、送料の割合を1000倍しておきます。
[crayon-5e4b339b9b288575045106/]
結果はこちら。デフォルトだと表が出力されます。下のボタンでクエリの結果をそのまま可視化可能。
こんな感じです。アドホックな分析にとても便利！
費用
料金体系
クラスターの起動時間に応じて、分単位で、以下のマシンリソースに対して課金されます。 (詳細はこちら)
料金表だけを見ていると意識に上がりずらいのですが、Databricks Unit には Driver とWorkerの2タイプがあり、クラスタを立ち上げると両方が起動され、課金されます。それぞれにインスタンスを割り当てて処理をしてもらうわけですが、当然、性能の高いインスタンス(= 高いDBU値) ほど、高コストになります。
今回使用したのはもっとも安価な 0.5 DBU のインスタンス。
今回の費用概算
今回の簡易分析・可視化で、どのくらいの費用が発生したか概算してみましょう。以下は今回使用したクラスタの設定画面。費用に大きく響いてくるところなので、本格的に分析基盤を立ち上げる際には、分析対象と内容に応じて、必要十分な計算資源を割り当てるように留意する必要があります。
設定は以下の通り。
クラスタのJob履歴を見てみたところ、Workerのスケーリングはしなかったようなので、クラスタの起動中は、0.5 DBU + 0.5 DBU で合計 1 DBU 使ってる計算になります。
・リージョン： 米国西部 2
・ワークロード：データ分析
・レベル：Standard
・Driverタイプ：F4s (0.5 DBU)
・Workerタイプ：F4s (0.5 DBU)
・最小ワーカー数: 1
・最大ワーカー数: 4
・自動停止
・15分操作がなかったらクラスタを停止
分単位の課金なので、Cluster の Event Log から、ここ2日間で何分間クラスタを起動したかざっくり見てみます。3回起動/停止しているようです。合計約70分。
Databricks の料金表を参照し、70分間インスタンスを起動した今回の費用概算と、仮に48時間インスタンスを立てたままにした場合の料金を比較してみました。
当たり前ですが、オートターミネーションした場合と継続利用では、かなりの金額差がありますね。
分析プラットフォームサービスは、どうしてもマシンスペックでの比較がされやすい傾向にあります。
しかし、実運用に則して考えると、Databricks の費用対効果の高さはピカイチじゃないかと思います。
まとめ
さいごにDatabricksの長所をおさらい。
・すぐApache Spark環境を作れる
・Notebookをバッチスクリプトとして使える
・バージョン管理が楽
・マジックコマンドが便利
・簡単に可視化
・オートターミネーション
アドホックな分析をするなら、SQLをそのままかけて、すぐに可視化できて、クラスタのオートターミネーションが利用できる Databricks が最高に便利。
おわりに
Databricks、簡易的な可視化ならできるけど、ちょっと凝ったグラフは？BIとの接続は？
ということで、次回は Databricks を Power BI につなげてみようと思います。お楽しみに！
参考サイト
クイック スタート: Azure portal を使用して Azure Databricks 上で Spark ジョブを実行する
Databricksで分析業務がはかどっている話
平成最後の1月ですし、Databricksでもやってみましょうか
Excel / Azure Notebooks / Databricks で同じことをやってみる #3 【Azure Notebook編】
はじめに
前回行ったExcelでの簡易集計と可視化を、Azure Notebooksでやってみます。
Azure Notebooksとは？
概要
データ分析プラットフォームのデファクトスタンダード、Jupyter Notebookをクラウド環境で起動できるAzureサービスです。他にはGoogle Colaboratoryが有名ですね。
ローカルでJupyter Notebookを使う場合、環境構築でつまづくこともしばしば。クラウドであればそのあたりほんと楽です。
・数ステップでAzure上に環境設定可能
・クラウド上で共同作業可能
・メモリ4GB, ストレージ1GBまで無償
Notebookはデータサイエンティストやアナリスト専用のものではありません。
分析に都合のいいプラットフォームがそろってて、メモ書きが残せて、みんなとシェアできる、いい感じに使いやすい "ノート" だと考えて、身構えずに触ってみましょう。
インストール
まずはAzure Notebookをセットアップします。 手順はこちら。Microsoftアカウントがあればすぐに数ステップでできます。
操作のきほん
初めてNotebookに触れる人のために、基本操作とショートカットをご紹介します。
セルを選択、コード入力後、Shift + Enterでそのセルのコードを実行できます。
セルの中を選択すると、枠が緑色になりEdit Modeに、セルの外を選択すると、枠が水色になりCommand Modeになります。
モード切り替えは、escとenterでも可能。
Command Modeで、mを押すとセルをMarkdownモードに、yを押すとcodeモードにできます。
これでMarkdownのメモが残せます。
Command Modeでaを押せば上に、bを押せば下にセルを追加可能。xで削除。
Command Mode時に、上下カーソルでセルを移動できます。
編集したいセルでenterを押せば、対象セルのEdit Modeに遷移。
作業をチェックポイントとして保存したいときは、ctrl + sを押します。これでいつでもそのポイントに戻れます。
特定のセルだけ実行されないようにしておきたいな、という時はctrl + / でコメントアウトしておきます。
他にもショートカットはたくさんあります。一覧表はHelpを参照しましょう。
あとは触りながら覚えていけばOK。
データの準備
Brazilian E-Commerce Public Dataset by Olistから、データをダウンロード、解凍しておきます。
Azure Notebook を立ち上げ、作成した Notebook と同じディレクトリで、UploadタブのFrom Computerを選択
以下の画面に遷移するので、解凍先のフォルダにあるCSVデータを、ドラッグアンドドロップ。
Uploadを押して、しばらく待ちます。Notebookと同じディレクトリにCSVファイルがアップロードされてれば完了です。
流れ
データの操作の定番といえば Pandas ですが、興味本位で Databricksとの比較してみたいので、並列分散処理フレームワークである PySpark を用いて中間テーブルを作成します。
そこからSQLで必要な集計処理を行い、最後に可視化のためのライブラリを使って、グラフを描画します。
手順
以下のスクリプトをNotebookのセルに入れ、実行していきます。
まずはPySpark関連のライブラリをインポート。
[crayon-5e4b339b9c0f0322166720/]
次に、Sparkのコンストラクタの作成。
正直言ってこのコードが何をしているのか良くわかっていないのですが、このコンストラクタが後述のスクリプトを良しなに処理してくれるようです。
[crayon-5e4b339b9c101123376701/]
アップロードしたCSVファイル名を変数に放り込んでおきます。
[crayon-5e4b339b9c10e646356097/]
CSVファイルをSparkに読み込ませます。上から、売上テーブル、製品テーブル、翻訳テーブル。ちょっと時間がかかります。
[crayon-5e4b339b9c11b413289034/]
テーブル結合のために、製品テーブルの product_id の名称を product_id2 に変更。
[crayon-5e4b339b9c127316341684/]
テーブル結合のために必要な列名を確認しておきます (結果は省略)
[crayon-5e4b339b9c133969234084/]
中間テーブルを作成します。※備考参照のこと
[crayon-5e4b339b9c13f037336216/]
作成した中間テーブルのスキーマを確認します。(これも結果省略)
[crayon-5e4b339b9c14c108074459/]
このテーブルに対してSQLを発行し、売上のTop10カテゴリを抽出、pandas_dfに変換後、平均単価で並べ替えします。こんな形でそのままクエリが書けるのは便利ですね。
[crayon-5e4b339b9c159988435354/]
可視化のためのライブラリをインポート。
[crayon-5e4b339b9c165339617195/]
グラフを描画。
[crayon-5e4b339b9c171249045547/]
出力されるグラフデータはこちら
pros
・ビジネスロジックが埋もれにくい
・どの処理をどんな順番で行うのか明確になる
・Markdownでメモ書きも残せて第三者にもフレンドリー
・意外と習得コストが低い
・今回はPySparkを使ったので参考資料は少ないものの、pandasであれば、たくさんの参考資料あり
・SQLも然り
・環境構築が簡単
・クラウドのNotebookの最大の利点
・違うPCでもアカウントにログインさえできれば同じように動作する
cons
・バージョン管理が大変
・チェックポイントを設けることはできますが、コードを書きたいときにわざわざこれをやるか、というと私はやりません。ちょっと面倒ですがコメントアウトで対応することが多いです。
・可視化に要する工数が大きい
・グラフの成型には結構な手間がかかります。今回はカテゴリの選定なので本来はそこまで凝ったグラフは必要ないのですが、ついいろいろ手を出してしまい、時間を費やしてしまいました(自戒)
まとめ
Azure Notebooksはすごく便利ですが、Jupyter Notebookをそのままクラウドに持ってきたというコンセプトなので、運用という意味では課題がいくつかあるように感じられます。次回はDatabricksでやってみます。お楽しみに！
参考サイト
Kaggle.com
Brazilian E-Commerce Public Dataset by Olist
Class SparkContext
備考: 中間テーブル作成時のPySparkコード
備忘録がてらまとめました
Excel / Azure Notebook / Databricks で同じことをやってみる #2 【Excel編】
はじめに
前回紹介した内容に沿った簡易集計と可視化を、Excelで行います。
流れ
3つのCSVデータを組み合わせて、中間テーブルを作成します。
青色は、 各々のレコードに対して feight / (price + freight)で算出した、単価と送料の合計に占める送料の割合。オレンジは、キーとなる値を紐づけて取得した英語のカテゴリ名。
この中間テーブルを参照元にして、pivotテーブルとpivotグラフを作成します。
手順
テーブルを跨いでしまうと後続の処理が遅くなってしまうので、まずは一つのファイルにCSVデータをまとめます。左から売上テーブル、製品テーブル、翻訳テーブルを配置。
注文テーブルの最右列に、送料の占める割合を計算する数式を入れます。一つのセルに数式を入れ、行末までコピペします。
同じく注文テーブルで、product_idをキーにして、製品カテゴリ(ポルトガル語)データを引っ張ってきます。皆さんおなじみvlookupです。
次は製品カテゴリ(ポルトガル語)をキーにして、製品カテゴリ(英語)を引っ張ってきます。
これでひとまず使おうと思っている情報は一つのテーブルに集約できました。売上ボリュームの大きいカテゴリを出すために、注文テーブルのセル範囲を選択して、pivotグラフ、pivotテーブルを作成します。
グラフの要素を選びます。フィールドリストの値に単価、行にカテゴリを持っていき、テーブルを単価の合計が大きい順にソートします。
この作業で得られた売り上げのトップ10カテゴリがこちら。
次に、トップ10の中から要件に合う3つのカテゴリを選定するために、別のPivotを作成します。Pivotの参照セルは先ほどと同じ。フィールドリストの値に単価と送料に占める割合を入れ、行にカテゴリを入れ、集計データの平均値を算出するように設定を変更します。
先ほど出した売上トップ10のカテゴリが表示されるよう、行ラベルをフィルタリングします。(gifファイルは例として1カテゴリだけフィルタリング)
単価を棒グラフで、送料に占める割合を折れ線グラフに変更し、行ラベルなどのグラフ要素を配置します。
その結果。
このグラフから、
・ある程度売上ボリュームがある (トップ10以内)
・比較的単価が高い
・配送料の割合が低い
の要件を満たすカテゴリを3つ選定するとすれば、
・watched_gifts
・cool_stuff (DVDなどらしいです。なぜにcool_stuffなのか...)
・health_beauty
あたりでしょうか？なんとなくそれっぽい結果が出ました。
それではこの類の簡易分析・可視化をする場合のExcelの長所短所をまとめてみます。
pros
・習得コストが低い
・参照できるドキュメントが多い
・データ量が少ない場合にはさくっと可視化できる
cons
・運用コストが高い
・元データに対する変更 (列の挿入や行の追加) に弱い
・再現性を高めるために必要なコストが高い
・慣れてる人ほどこれに気が付かない
・ビジネスロジックが埋もれやすい
・数式が入れ子になっていて、結局この列で何を計算してるのか、不明瞭になりやすい
・他人が見たら意味不明なシートが出来上がることがある
・not スケーラブル
・処理の可否がローカルマシンのスペックに依存する
・回避する方法はあるものの、それはそれで結構習得コストが高い
・最大で 約100万行 x 1.6万列 をサポートしているが、今回のような用途ではまず使用不可
まとめ
Excelはお手軽で良いのですが、複数テーブルを結合したりといった分析には不向きですね。分析結果を共有したりするのも苦手です。次回はAzure Notebookでやってみます。お楽しみに！
参考サイト
Kaggle.com
Brazilian E-Commerce Public Dataset by Olist
Excel / Azure Notebook / Databricks で同じことをやってみる #1 【はじめに】
はじめに/対象者
Excelのつらみを日々体感している以下のような方に向けて、Azureファミリーの便利なサービスを何記事かに分けてご紹介します。
・非技術者でデータを扱っている
・データの集計や可視化に、Excelを使っている
・とりあえず今やってる作業の効率化から進めたい
・今より少し踏み込んだ分析ができるプラットフォームを探してる
・Excelのつらみ
・vlookup関数やmatch関数を入れ子にしがち → フリーズ
・データ量増大 → フリーズ
・うっかりどこかの列を消去 → N/Aを量産
・バージョン管理(ローカルでやる場合) → ………
流れ
Excelでの作業がどのくらいシンプルになるかを感じてもらうために、簡易的にクロス集計を行って可視化するまでを、下記3つのプラットフォームで同じようにやってみます。
・Excel
・Azure Notebooks (クラウド上のJupyter Notebook)
・Databricks (Sparkベースのデータ分析プラットフォーム)
同じ土俵での比較がそもそもナンセンスですが、ともかく無理やりやってみます。
いわゆるデータ分析前段の前処理を一部かじって、可視化する、までの範囲を行うことになります。
データ分析フローで言う、以下のセグメントを行うイメージ。
それぞれの作業環境は以下の通りです。スペックの単純比較はできませんが、メモリ数見てもらうだけでも、ずば抜けてハイスペックなものを使うわけではないのがわかると思います。
そのあと、ちょっと踏み込んだ内容として、
・DatabricksにPower BI(Microsoft謹製データ可視化ツール)を接続
・タイタニック号の生存者分類 (Kaggleで有名なアレです) をAutoMLにやらせてみる
をやってみます
使用データ
お手元のデータを使ってもらうのが一番いいのですが、初めてPythonやSparkに触れる人にとっては少しハードルが高いはず。そこで、
・テーブルの定義が明確
・売上データがExcelで扱うことができるボリューム
・データ自体のイメージしやすい
の条件を満たすデータとして、Kaggleで公開されている以下のデータセットを採用することにしました。
-------------
Brazilian E-Commerce Public Dataset by Olist
・概要
ブラジル市場最大のデパートのeコマースストアの公開データセット
2016-2018年までの10万件の注文データ
小売店はeコマースストアを通じて製品を販売、提携物流会社を用いて、顧客に直接商品を出荷
製品を受け取る or 配達予定日が来ると、顧客はレビュー調査を電子メールで受け取る
顧客は任意でレビューを書ける
・Kaggleでの課題
自然言語処理 (レビュー内容に対して)
顧客クラスタリング (レビューの記載がない顧客の属性)
製品クラスタリング (不満をもちやすい製品カテゴリ)
売上予測
配送料金最適化
-------------
課題設定
今回はシンプルに以下のような立て付けで課題設定します。
-----------
・あなたは Olist のマーケティング担当者です。
・クリスマス商戦用に、配送料無料キャンペーンを検討中
・単価と配送料の合計に占める配送料の割合が高いカテゴリは避けたい
・クリスマスなので、比較的単価が高いカテゴリを選定したい
・ある程度売上ボリュームのある3つのカテゴリに絞り込みたい
・この課題を解決するために、以下のテーブルを用いて簡易分析し、結果を可視化する
・売上明細データ
・カテゴリデータ
・翻訳データ
・(小売店がOlistに支払う手数料は製品単価に比例しているものとする)
-----------
テーブルスキーマ
まずこのデータセットに含まれる8つのテーブルの関係図を眺めてみましょう。
それぞれのテーブルはCSVデータで提供されます。
黄色が注文の製品データ、オレンジが注文の明細データです。この2テーブルがproduct_idで紐づいています。
今回はこれら2テーブルと翻訳テーブル、併せて3つのテーブルを使用します。
今回使用するテーブルを簡単に説明します。
1．売上明細データテーブル
左から、注文番号、注文番号ごと/製品IDごとに割り当てられる通し番号、製品ID、販売者ID、出荷期日、単価、送料です。今回参照するのは、製品ID、単価、送料の三点です。
2．カテゴリデータテーブル
左から、製品ID、カテゴリ名(ポルトガル語)、製品名の長さ、製品明細の長さ、製品写真枚数、パッケージの重量、パッケージのサイズ(縦/横/深さ)です。今回は参照するのは、製品IDとカテゴリ名(ポルトガル語)の2点です。
3．翻訳データテーブル
カテゴリ名(ポルトガル語)とカテゴリ名(英語)の対応表です
中間テーブル概要
これらの3テーブルをキーとなっている値で紐づけて、中間テーブルを作ります。
単価と配送料の合計に占める配送料の割合を、各々の注文データで算出します。以下のようなイメージです。
まとめ
さて、前置きはここで終わり。次回はこの作業をExcelでやってみたいと思います。お楽しみに！
参考サイト
Kaggle.com
Brazilian E-Commerce Public Dataset by Olist
Azureアーキテクチャガイドまとめ 8 【ビックコンピューティングアーキテクチャ】
はじめに
Azureクラウドアプリケーションアーキテクチャガイドより、7つあるスタイルの最後の1つ、ビッグコンピューティングアーキテクチャに関してまとめます。
概要
作業を個別のタスクに分割し、多数のコアを使って同時に処理するアーキテクチャスタイル。大量のコアを一気にプロビジョニングし、アプリケーションが完了すると、プロビジョニングを解除します。
以下のような特徴を持つアプリケーションに適します。
・計算負荷が高く、複数へのコンピュータのCPUへの分割が必要なシミュレーションや数値演算
・一台のコンピュータでは時間がかかりすぎる処理時間の長い計算
・小さな計算処理を数百万回から数千回実行する必要がある場合
以下のようなケースで活用します
・画像のレンダリング
・流体力学
・金融リスクモデリング
・石油探査
・新薬開発
・応力解析
対応するAzureコンポーネント
大規模なパイパフォーマンスコンピューティングアプリケーションを実行するためのマネージドサービスである、Azure Batchが主要なサービスです。
Azure Batchはワークロードに合わせて、Virtual Machineを自動的にスケールアウトします。
利点
・単純並列処理によるハイパフォーマンス
・大規模な問題を迅速に解決
・専用の高速ネットワークを備えた専用HWにアクセスできる
・作業に必要な数だけVMをプロビジョニング可能
課題
・マシンリソースの管理
・数値演算ボリュームの管理
・数千のコアのタイムリーなプロビジョニング
参考リンク
Azure でのハイ パフォーマンス コンピューティング (HPC)
ビッグ コンピューティング アーキテクチャ スタイル