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-5f6bdd016aa22490561415/]
先ほど作成したテーブルの名称を変数に格納
[crayon-5f6bdd016aa30285487921/]
それぞれのテーブルをSparkで読み込み
[crayon-5f6bdd016aa39618411838/]
テーブル結合のために、必要な列名を確認しておきます (結果は省略)
[crayon-5f6bdd016aa42266080244/]
中間テーブル (name: sql_tbl) を作成します。コードは#3 Azure Notebookの回で説明したものと同じです。
[crayon-5f6bdd016aa4b993892589/]
ここからは全部SQLで記載していきます。%sqlとセルの頭に入れれば、SQL文として処理してくれます。結合処理を再度行うのは面倒なので、このテーブルは永続化しておきましょう。一時テーブルで良いときは、CREATE TABLE を CREATE OR REPLACE TEMPORARY VIEW AS にすればOK
[crayon-5f6bdd016aa54525706043/]
以下で中間テーブルのスキーマが確認できます
[crayon-5f6bdd016aa5d941104550/]
結果

集計後テーブル (name: sql_tbl2) を作り、永続化させます。
[crayon-5f6bdd016aa66178899070/]
集計後テーブルに対してクエリを発行します。可視化した時に同じくらいのスケールに収まるように、送料の割合を1000倍しておきます。
[crayon-5f6bdd016aa6f922981294/]
結果はこちら。デフォルトだと表が出力されます。下のボタンでクエリの結果をそのまま可視化可能。

こんな感じです。アドホックな分析にとても便利！

費用

料金体系

クラスターの起動時間に応じて、分単位で、以下のマシンリソースに対して課金されます。 (詳細はこちら)

料金表だけを見ていると意識に上がりずらいのですが、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でもやってみましょうか

Azure Databricksの導入ならナレコムにおまかせください。

導入から活用方法までサポートします。お気軽にご相談ください。

Azure Databricksソリューションページはこちら

by azure-recipe-user

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-5f6bdd016af38894336939/]
次に、Sparkのコンストラクタの作成。
正直言ってこのコードが何をしているのか良くわかっていないのですが、このコンストラクタが後述のスクリプトを良しなに処理してくれるようです。
[crayon-5f6bdd016af45685551474/]
アップロードしたCSVファイル名を変数に放り込んでおきます。
[crayon-5f6bdd016af4f971524246/]
CSVファイルをSparkに読み込ませます。上から、売上テーブル、製品テーブル、翻訳テーブル。ちょっと時間がかかります。
[crayon-5f6bdd016af58641410261/]
テーブル結合のために、製品テーブルの product_id の名称を product_id2 に変更。
[crayon-5f6bdd016af61462608720/]
テーブル結合のために必要な列名を確認しておきます (結果は省略)
[crayon-5f6bdd016af6a997750319/]
中間テーブルを作成します。※備考参照のこと
[crayon-5f6bdd016af72755338163/]
作成した中間テーブルのスキーマを確認します。(これも結果省略)
[crayon-5f6bdd016af7c739735006/]
このテーブルに対してSQLを発行し、売上のTop10カテゴリを抽出、pandas_dfに変換後、平均単価で並べ替えします。こんな形でそのままクエリが書けるのは便利ですね。
[crayon-5f6bdd016af85898442831/]
可視化のためのライブラリをインポート。
[crayon-5f6bdd016af8e816113323/]
グラフを描画。
[crayon-5f6bdd016af96927910437/]
出力されるグラフデータはこちら

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コード

備忘録がてらまとめました

Azure Databricksの導入ならナレコムにおまかせください。

導入から活用方法までサポートします。お気軽にご相談ください。

Azure Databricksソリューションページはこちら

by azure-recipe-user

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

Azure Databricksの導入ならナレコムにおまかせください。

導入から活用方法までサポートします。お気軽にご相談ください。

Azure Databricksソリューションページはこちら

by azure-recipe-user

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 Databricksの導入ならナレコムにおまかせください。

導入から活用方法までサポートします。お気軽にご相談ください。

Azure Databricksソリューションページはこちら

by azure-recipe-user