知人の店舗のレジデータを可視化した話②

まえがき

以前のお話はこちら

　以前はデータを抽出して、カラム定義とか一切無い状態から解析した話だったと思います。
　今回は店舗のPC、タブレットから常に見られるように環境を整えてきました。
　SQLServerの環境は以前お話しした通りのものですが、今回データ可視化に使用するのは
　　・　raspberrypi　2GB
になります。
　なぜこれを使用するか、と言うと
　　①　小型
　　②　省電力
　　③　低価格
です。

　①　小型
　　言わずもがな、ラズパイは小さなPCで場所を取りません。
　　そしてレジの付近に置くためには場所を取らないことが必須。
　　実際にミニPCを置けるか試したのですが、これまた微妙なスペース
　　なのでラズパイを選択しました。
　（省スペースだから排熱もうまくいっています）

　②　省電力
　　通常のPCで24時間処理をさせると電力がそこそこかかります。
　　さらに、ノートPCなら火災の危険性も出てきます。
　　もちろんラズパイも電力を使うし、火災の危険性もあります。
　　ですが、通常PCよりも低性能なのでそのリスクが少ない、と言えます。

　③　低価格
　　なるべく導入のコストを抑えるため低価格である点も魅力でした。
　　最近は円安ですしそこそこ高いと言わざるを得ませんが・・・

ラズパイの導入＆可視化だが・・・

　店舗のルーターに有線でラズパイを接続して、同一ネットワークのPCからVNCを使用してラズパイにリモート接続、設定していきました。
　その店舗ではNASも欲しいということだったのでラズパイにSambaを入れて共有サーバー化して先に運用、データ解析後にStreamlitで可視化しよう、という流れになりました・・・がここでも何個か問題が発生しました。

①　ラズパイからSQLServerに接続できない

　通常のPCのCPUと比較して性能が低いCPUであるラズパイでは、通常のPCでできることとできないことがあるのは把握済みで、性能が低いからだ、Debian系列なのだから性能が低いだけで出来ないものはないだろう、と高を括っておりました。
　ですが、ラズパイさん、pyodbcが使えない、のです。
　いくつかFreeTDSを使用してSQLServerに接続できた、という記事に従って設定を試してみたのですが、どれもうまくいかない。
　その記事のアーキテクチャはARMとなっていますが、私のはAARCHという奴で全く違うものだったので、ラズパイからSQLServer直接参照は諦めざるを得ませんでした。

②　DBから抽出したデータが大きすぎる

　DBに格納されているデータは2015年からで、サイズは360MB（列を減らしても160MB）、行にして52万行、程度です。
　例えばintel、corei5くらいの性能があるCPUでSELECT文を実行したら処理はすぐに終わりますし、実際10秒もかからずに実行できますが、実行するのはラズパイ、どれくらいの時間がかかるか、また処理が正常に終わるかは未知数（と言ってもほぼ確実にメモリエラーで強制終了の未来が待っています）です。
　さらに前処理がたとえうまくいったとしても、そのあとのstreamlitで可視化するとなると、まともに動かなくなるのは目に見えていました。

解決策

　そもそものやりたいこととしては、レジデータを可視化したい、であるので、上の①の制約は、SQLServerからデータを抽出することができない、ではなく、ラズパイからSQLServerへ接続ができないだけで、レジが稼働している間はレジ用のPC（SQLServer）は動いているのだし、②の制約は細切れにすれば良いのです、が、案外、②が難しかったりしました。
　以下で使用する番号は上記の制約の解決策の番号と対応しています。

①　ラズパイをネットワークドライブとしてファイル送信

　先ほど、ラズパイにSambaを導入してNAS化した、とお話ししました。
　このNASはレジ用PCからでも例外なく閲覧、編集できます。
　つまり、レジ用PCでデータを抽出、加工し、net useコマンドを使用してファイルを送信すればいいじゃない、となりました。
　そのまま送ると制約②に波及するので、SELECT文でカラムを削除したり、と色々加工しましたが①の制約に関してはこの方法で乗り切りました。

②　ファイルサーバーに送信するファイルサイズを小さくする

　表題の通りではあるのですが、年別にすると、あら不思議、20MB以下になりました。
　このほかにも、レジデータの会員の郵便番号と、日本郵政で公開されている、郵便番号に対する住所のデータを紐づけてどの地方に住んでいる会員なのか、気象庁の気温データを使用して、どれくらいの気温のときに何を買う傾向にあるか、のデータを結合したりもしましたが、概ね20MB以下に収まりました。
　あとはファイルサーバーにファイルを送るだけ、なのですがCMDでfor文を使用してファイルを送ろうとすると一つしか送れない・・・ということで直近1年分のデータのみ、年が明ける前の12月は大みそかにSQLServerを立ち上げたりすることが無い可能性を加味して、１月中は直近2年分のデータを抽出して送信、ラズパイ側で加工するようにしました。

Streamlitで可視化する

　この点は大した話は無いです。
　データは年月日時のデータなので、年間での月別売り上げ推移、年月を指定したうえでの売り上げ、年月日を指定したうえでの時刻単位での明細を可視化し、さらに予約データを可視化しました（データは個人情報も含まれるためどんなグラフを作成したかは詳細控えます）。
　具体的に想像いただくなら、
　　Ⅰ：何月が売上があるか
　　Ⅱ：どの年代がより多く購入しているか
　　Ⅲ：どのカテゴリが買われているか
といったありきたりなものです。
　予約データは、行に予約者名、列に日付、リファレンスラインで今日の日付、として、日数が経過しているものは警戒色を付けておきました。
　当初の予定と変更したのは、読み込む売上明細のファイルを一つでなく年単位で分けることにしたので、読み込むファイルパスをフィルタに従って変えた、くらいです。
　おかげで読み込み時間が5秒くらいになりました。

おわりに

　足掛け半年かかった今回の「お仕事」ですが、普段使用しているTableau、リモート接続できるSQLServer、VPC、という便利な環境を離れ、一から自分で考えて、次にどうするか？、ラズパイの計算量は？、失敗した後のバックアップは？、ほかの方法は？、といったように考えることが多く、大変勉強になりました。
　最後の方は、管理者権限でしか開けないネットワークドライブを指定してスクリプトを上書きしてもらう、という店舗の行事、繁忙期にも重なって自分が店舗に出向けない苦肉の策を取っていましたが、皆さんはやらないでくださいね。
　あと、サーバーは何でもいいからとりあえずceleron以上にしておくことをお勧めします。ラズパイは小型な反面、貴方のやりたいことが制限されるかもです。