月別アーカイブ: 2019年1月

AzureのSQL DATABASEから多層ネストされたJSONを返すREST APIを作る場合、SELECT文だけで間に合ってしまう(Transact-SQL)

たまたまAzureのSQL DATABASE(≒SQL Server)のドキュメントを読んでいたときに気が付いたのですが、最近のSQL Serverってクエリ末尾に”FOR JSON”を付与すればクエリ結果をそのままJSONに出来るんですって!?そんなこと知らずにDapperでガリガリとSQLをパースしてJSONを作っていたとか馬鹿丸出しですねワタシ。パトラッシュ・・次から頑張るよ・・・・。

ということでSQLだけで多層ネストJSONが出来るまでを検証しました。以下にその方法を示します。

準備:Azure上にサンプルDBを作成 (テンプレートの”AdventureWorkLT”を利用)

このサンプルデータを利用して「セールスの存在する顧客別にオーダーとそのオーダー詳細を表示する」という3層ネスト構造を再現します。

SQLで表現するとこんな感じです。

実行するとこんな感じ。

SQL実行結果

いかにもDapperで分割してくださいと言わんばかりの出力結果ですが、今回はDapperは使わずSQL DATABASEの基本機能だけしか使いません。ですのでこのSQLの文末に”FOR JSON AUTO”を付与すればめでたく解決・・・ではなく、ここからネストさせたい階層ごとにサブクエリを定義していく必要があります。それをストアドファンクション化したのが以下となります。

ポイントは次の通りです。

  • JSONの受けは必ずNVARCHAR(MAX)とする
  • “FOR JSON PATH”とする(PATH付けないとコケる)
  • ROOT(‘INFO’)とすることでルートオブジェクト配列にアクセスしやすくする
  • サブクエリでのエイリアスがJSONでの型名となる

こうして作成したJSONをオンラインパーサーで確認するとちゃんと3層ネスト構造となっていました。

オンラインパーサーでのJSON構造確認結果

あとはこのまま普通にHTTP TriggerやPHPファイルからレスポンスを返してあげればREST APIの作成完了なのです。余談ですが逆にSQLだけでJSONを解析することも可能です。

実はこの逆変換の方が大変でした。ポイントは次の通りです。

  • “CROSS APPLY OPENJSON”を用いてJSON型を手動で展開する
  • 展開したJSONの中に再度JSON型があった場合はさらに展開する
  • OPENJSONのWITHにて型指定する必要がある(型推論ないんかい・・・
  • ”CROSS APPLY”で展開した列名は展開対象のエイリアスとは別となる
  • “CROSS APPLY”しても展開元のエイリアスは呼べる(これになかなか気付かなかった・・・)

今どきのSQLServerってこんな感じでSQLを駆使していけばJSON化もパースもできるんですね。でも楽になった気があまりしないのは気のせいではないかもしれません。正直、慣れてなければ面倒くさいからこれならDapper使うわってのもアリではないでしょうか。あとSQLの見通しが悪くなってるのがかなり保守観点でマイナス。

ですので間違ってもこれらの「SQLだけでREST APIは作れる」という知識だけで 判断してはダメです。
「(言語名)経験○○年の要員がホゲホゲ」なんて考えのマトモにスキル判定も出来ない無能揃いのSES業者が「運用でSQLバリバリやってましたHAHAHA」という定型作業しか経験のない人材を押し込んでくることになりかねないので要注意です。

Azure Cosmos DBにストアドプロシージャからパーティションキーを指定してドキュメントを追加する(C#)

Azure Cosmos DBをSQL(document)で作成すれば普通のSQL文でCRUDできるのではないかと思いましたが、どうやらそうではなく使えるのはSELECT、しかもGROUP BYもできない、トランザクションスコープも適用されないという非常に癖のある代物でした。オールドタイプにはなかなか馴染めないですね。

特にトランザクションスコープは面倒で、ストアドプロシージャを経由すればパーティション単位でトランザクションが保証されるというものです。ちなみにストアドプロシージャの実装方法ははAzureポータル上にてJavaScriptにより定義する必要があります。

仕方がないと諦め、ベタ書きでJavaScriptを書いてストアドプロシージャを作成します。テストデータでは”uniqid”でパーティション化されたコレクションを想定しています。

次に利用側のコードを書きます。今回、Functions V2(.NET Core)からC#を利用してCosmos DBにアクセスします。

指定したパーティションキー値と投入データの値が一致しない場合はストアドプロシージャの呼び出しは失敗します。よって実際の利用にあたっては投入前にLINQからパーティションキー単位でグループ化を行い、またコレクションに主キー(Primary Keys)設定を行い、ストアドプロシージャ側に主キー重複時の例外処理などを実装する必要があると思います。

なおストアドプロシージャを経由する追加処理は非常に遅く、あくまでもトランザクションを必要とした少量データの更新などにしか使えません。大量データの取込にはBulkExecuterライブラリを利用して一気に追加/更新をやるのが本来望まれる処理のようです(ただしV1限定)。