この記事について
①設計編では、パイプラインの構成とDB設計、Function Appの実装判断について書きました。この②では、実際に動かす過程で遭遇したトラブルと、その原因の特定・対処までを扱います。
先に結論を書いておくと、今回ハマったポイントの多くは、根っこをたどると同じ一つの感覚の欠如に行き着きました。「今、誰がどこから接続しようとしているか」という、実行主体の所在です。似た症状が3回、違う顔をして出てきました。
接続トラブル三部作
①Query Editorからのアクセスが拒否される
作業の初期、Azure Portal上のQuery Editorから作成したてのSQL Databaseに接続しようとして、最初につまずきました。原因は単純で、SQL Database側のファイアウォールが、自分のPCのIPアドレスからのアクセスをまだ許可していなかったためです。SQLサーバー側の「ファイアウォール規則」で、自分のクライアントIPを許可することで解決しました。
これは分かりやすい話で、この時点では「ああ、ファイアウォールね」くらいの認識でした。ところがこの後、同じ根っこの問題が、もっと分かりにくい形で2回出てきます。
②ローカルからの接続が5分以上ハングする
ローカル環境(func start)からSQL Databaseへの書き込み処理を実行したところ、応答が返ってこず、5分以上ハングする状態になりました。エラーメッセージも出ないまま止まり続けるので、原因の切り分けにかなり時間を使いました。
最初に立てた仮説は「ポート1433がISPかルーターにブロックされているのでは」というものでした。Test-NetConnection -Port 1433で疎通を確認したところ、結果は接続成功。この仮説は外れました。
ここで分かったのが、Azure SQL Databaseの接続方式です。クライアントは最初にゲートウェイ(1433番ポート)に接続しますが、実際にデータがあるバックエンドノードへは、11000〜11999番のどこかにリダイレクトされる、という2段階の仕組みになっていました。Test-NetConnectionで見えていたのは最初の1段階目だけで、2段階目のリダイレクト先が、自宅のネットワーク環境でブロックされていたために、ゲートウェイへの接続自体は成功するのに、その先のクエリだけが永遠にハングする、という今回の症状と一致していました。
対処は、SQLサーバー側(データベース単体ではなく論理サーバー側)の「セキュリティ→ネットワーク→接続性」タブで、接続ポリシーを既定のRedirectからProxyに変更することでした。Proxy方式では、全通信がゲートウェイ経由で完結するため、1433番ポートさえ開いていれば接続が成立します。トレードオフとして、全通信がゲートウェイを経由する分、Redirectよりレイテンシがわずかに増えるとされていますが、開発・検証用途では気にならないレベルでした。
③本番デプロイ後、Function Appからの接続だけエラーになる
ローカルでの動作確認が通ったので、いよいよAzureへデプロイしました。ところが、デプロイ後に本番の関数URLへアクセスすると、今度は別のエラーが返ってきました。
内容を見ると、SQL Database側のファイアウォールが、アクセスしてきたIPアドレスを許可していない、という趣旨のエラーでした。最初は「さっきファイアウォールは直したはずなのに」と面食らったのですが、よく考えると当然の話でした。以前許可したのは自分のPCのIPアドレスで、今回接続しにきているのはAzureのデータセンター内にあるFunction Appです。発信元のIPアドレスが、そもそも別物だったのです。
ローカルでのテストが問題なく通っていたのは、単に「PCのIPは既に許可されていたから」に過ぎず、Function App側からのアクセスは、また別に許可する必要がありました。個別のIPを都度登録していくのは非効率なので、SQLサーバー側の「セキュリティ→ネットワーク→パブリックアクセス」タブにある、「Azureサービスとリソースにこのサーバーへのアクセスを許可する」をオンにする設定で解決しました。
三部作を振り返って
3つのエラーは、発生した場所も症状も別々でした。しかし振り返ってみると、根っこは共通していました。「今、何がどこからアクセスしようとしているか」を、都度きちんと意識できていなかったことです。オンプレミス感覚だと「1つのサーバーが1つの場所にある」という前提で考えてしまいますが、クラウドでは「自分のPC」「Azure上で動くコード」「ブラウザ上のツール」が、それぞれ別々の実行主体として扱われます。この前提を体で理解していなかったからこそ、毎回違う角度からドアを閉められていた、というのが実態だったのだと思います。
データ品質・トランザクション設計
pandas 3.0の破壊的変更
書き込みロジックの中で、空文字列をSQL用のNULLに変換する処理を入れていました。
df = df.applymap(lambda v: None if v == "" else v)このコードを実行すると、AttributeError: 'DataFrame' object has no attribute 'applymap'というエラーで落ちました。調べると、applymapはpandas 2.1で非推奨化され、pandas 3.0で完全に削除されたメソッドでした。手元の環境にインストールされていたのがpandas 3.0系だったため、そのまま引っかかった形です。
修正は単純で、.map()に置き換えるだけです。
df = df.map(lambda v: None if v == "" else v)機能・挙動は変わらず、pandas側がメソッド名を変更しただけでした。ローカルとAzure(Linux環境)でライブラリのメジャーバージョンが違うと、こうした廃止済みメソッドに当たることがある、というのは一つの教訓です。
NaN/NULL変換の判定方法
空文字列だけでなく、pandasが読み込み時に生成するNaNも、SQL側ではNULLとして扱う必要があります。ここは.where()などとの組み合わせでは取りこぼしが出たため、pd.isna()による判定に寄せることで、確実にNULL変換できるようにしました。
トランザクション設計が効いた場面
書き込み処理の途中、実際に例外が発生してテーブルへの書き込みが失敗したことがありました。焦りましたが、結果としてデータが中途半端な状態で残ることはありませんでした。
理由は、複数テーブルへのDELETE・INSERTを1つのトランザクションとしてまとめ、最後にconn.commit()で確定する設計にしていたためです。途中で例外が起きた場合、コミットされていない変更は自動的にロールバックされます。「使う側」としてSQLを書いていたときは、こうした設計判断はそもそも意識に上らない類のものでした。今回、「作る側」に回ったことで初めて、トランザクションが単なる作法ではなく、実際に事故を防ぐ仕組みとして機能する場面を体験できました。
Power BI接続
リレーションには向きがある
Power BI Desktopから、Import modeでSQL Databaseに接続しました。FK制約を貼っていたおかげで、5テーブル間のリレーションは自動的に検出されました。
ここで一度つまずいたのが、あるグラフにフィルターをかけても、集計結果がまったく変わらないという現象でした。原因は操作ミスではなく、リレーションには向きがあり、片方向にしかフィルターが自動で伝わらないという、Power BIのデータモデルの基本的な仕組みを理解していなかったことでした。「フィールドをドラッグしてグラフを作れる」ことと、「データモデルとしてリレーションの向きを設計できる」ことの間には、見た目以上の段差があります。
DAX measure
最終的に7つのDAX measureを作成しました。代表的なものとして、SWITCH文を使って評価グレード(S/A/B/C/Dなど)に独自の並び順を与える測定値と、VAR/RETURNパターンを使って「現在のフィルター文脈における最新年度」を動的に判定する測定値があります。後者は、単純に「最大の年度」を取るだけでは、フィルターがかかった状態で意図しない年度が拾われてしまうケースがあったため、文脈を考慮した判定が必要になった箇所です。
ダッシュボード構成
最終的に3ページ構成のダッシュボードにまとめました。全社サマリ、離職分析、評価×勤怠分析の3ページです。配色はティール×コーラルのカスタムテーマを使っています。当初はネイビー×アンバーの組み合わせも検討していましたが、最終的にこちらに落ち着きました。
AIとどう進めたか
このパイプライン全体は、実況形式でAIと相談しながら進めました。スクリーンショットを共有し、出てきたエラーをその場で読み解き、仮説を立てては棄却し、次の仮説に移る、というやり取りの繰り返しです。
振り返って感じるのは、今回のプロセスで一貫していたのは「知っていたこと」と「実際に要求されたこと」のギャップを、その都度埋めていく作業だったということです。Azureの実行主体の所在、Power BIの意味モデル、DB設計における「作る側」の視点。どれも、事前に知識として知っていたわけではなく、エラーに突き当たるたびに、AIとの対話を通じて輪郭がはっきりしていった感覚があります。AIは答えを一方的に渡してくれる存在というより、こちらが立てた仮説を一緒に検証し、外れたときには次の切り分け方を一緒に考える、伴走者に近い役割だったと思います。
おわりに
①設計編、②トラブルシューティング編と通して、疑似データとはいえ一通り動くパイプラインを作りきることができました。データ更新の自動化や、もう少し踏み込んだPower BIの分析軸など、改善の余地はまだ残っています。それでも、まず一通り「動くもの」を作りきれたことが、今回一番の収穫でした。
コードの全体、DB定義、README(設計判断の理由込み)は、GitHubリポジトリにまとめて公開しています。
kambara-lab/hr-synthetic-pipeline
この記事で紹介しきれなかった実装の細部を見てみたい方は、ぜひ覗いてみてください。
コメント