プログラミング」カテゴリーアーカイブ

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限定)。

Dapperのマルチマッピング機能で3層ネストされたリストを取得する(C#)

お久しぶりです。

今年はWindows Embedded Compact 7によるハンディターミナルの開発ばかりやってたのですが、今月からうってかわってAzure Functions V2によるWebアプリ開発になりました。

振れ幅の大きさに自分でも笑ってしまいます。

それはさておきDBへのクエリ結果から配列がネストされた構造のJSONに変換するのに簡単な方法はないものか色々と悩みましたが、あまり簡単な方法はなかったようでエクセレントな解法ではないものの、まあ許容できる範囲かと思ったので検証結果を公開します。

検証には次のような条件を前提としています。

  • JOINするのは3テーブル
  • それぞれのテーブルには単一カラムの主キーがある
  • クエリ解析にはDapperのマルチマッピング機能を利用する

そして以上の条件を踏まえ、Dapper公式サイトのマルチマッピング機能のソースを改変した検証コードが次です。

上記のコードのポイントは次の通りです。

  • レコードの読込判定にディクショナリを用いる
  • ディクショナリに格納するのは最下層の配列
  • 最終的に出力するリストにひとまず追加し、追加済みであれば上書する

じゃあ3階層を超えたネストはどうすればいいんだ、という疑問については

「3階層目からメソッド分割して別途Query呼び出せばいいんじゃね?」

と思ったりしますが、DapperのQueryがネストして呼び出せるかどうか試していないので断言は出来ません。

それにしてもWordpressを更新したら、前のような記述が出来なくなって、やたら平坦て簡素な書き方を強いられてしまいます。

しかもシンタックスハイライターが動かんのでソースコード見づらいし・・・。

勝手に変えるなよ・・・と思っても、何も考えずよく知らないWordpressを選んだ私の自業自得なのでしょう。

汎用CSV読込フォーム

ブログでは初めてのプログラミング関係の記事です。
CsvReader動作イメージ

下記に汎用CSV読込フォームに関する記事を公開しました。
汎用CSV読込フォーム CsvReaderについて

使い方とかソースコードの説明とか省略しまくったので、もし質問があればコメントをください。
もちろん指摘やバグ報告も歓迎します。

まあ、すぐに対応できるとも対応するとも限りませんがね:-p)