月別アーカイブ: 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で表現するとこんな感じです。

SELECT  CUST.CustomerID, 
        CUST.FirstName, 
        CUST.LastName, 
        CUST.Phone,
        SHDR.SalesOrderID, 
        SHDR.OrderDate, 
        SHDR.SubTotal,
        DETL.SalesOrderDetailID,
        DETL.ProductID,
        PRDT.Name,
        DETL.LineTotal
FROM SalesLT.Customer CUST
INNER JOIN SalesLT.SalesOrderHeader SHDR
ON SHDR.CustomerID = CUST.CustomerID
INNER JOIN SalesLT.SalesOrderDetail DETL
ON DETL.SalesOrderID = SHDR.SalesOrderID
INNER JOIN SalesLT.Product PRDT
ON PRDT.ProductID = DETL.ProductID
ORDER BY CUST.CustomerID,SHDR.SalesOrderID,DETL.SalesOrderDetailID

実行するとこんな感じ。

SQL実行結果

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

CREATE OR ALTER FUNCTION GetSalesInfoByValidCustomer() 
RETURNS nvarchar(MAX)
AS
BEGIN
	DECLARE @json nvarchar(MAX)
	SET @json = ( 
		SELECT	CUST.CustomerID, 
				CUST.FirstName, 
				CUST.LastName, 
				CUST.Phone,
				(
					SELECT
					SHDR.SalesOrderID, 
					SHDR.OrderDate, 
					SHDR.SubTotal
				   ,(
						SELECT 
							DETL.SalesOrderDetailID,
							DETL.ProductID,
							PRDT.Name,
							DETL.LineTotal
	   					FROM SalesLT.SalesOrderDetail DETL
						INNER JOIN SalesLT.Product PRDT
						ON PRDT.ProductID = DETL.ProductID
						WHERE DETL.SalesOrderID = SHDR.SalesOrderID
						ORDER BY DETL.SalesOrderDetailID
						FOR JSON PATH
				   ) AS DETAIL
					FROM SalesLT.SalesOrderHeader SHDR
					WHERE SHDR.CustomerID = CUST.CustomerID
					ORDER BY SHDR.SalesOrderID
					FOR JSON PATH
				) AS [ORDERS]
		FROM SalesLT.Customer CUST
		INNER JOIN SalesLT.SalesOrderHeader SHDR
		ON SHDR.CustomerID = CUST.CustomerID
		ORDER BY CUST.CustomerID
		FOR JSON PATH,ROOT('INFO')
	)
	RETURN @json;
END

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

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

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

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

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

--JSON⇒テーブル化用クエリ
DECLARE @json nvarchar(MAX)
SET @json= dbo.GetSalesInfoByValidCustomer();
SELECT 
	Level2.CustomerID,
	Level2.FirstName,
	Level2.LastName,
	Level2.Phone,
	Level2.SalesOrderID,
	Level2.OrderDate,
	Level2.SubTotal,
	SalesOrderDetailID,
	ProductID,
	Name ,
	LineTotal 
FROM
(
	SELECT Level1.CustomerID,
		   Level1.FirstName,
		   Level1.LastName,
		   Level1.Phone,
		   SalesOrderID,
		   OrderDate,
		   SubTotal,
		   DETAIL
	FROM (
		SELECT CustomerID,
			   FirstName,
			   LastName,
			   Phone,
			   ORDERS
		FROM OPENJSON(@json,N'strict $.INFO')
		WITH (
		  CustomerID int,
		  FirstName nvarchar(50),
		  LastName nvarchar(50),
		  Phone nvarchar(25),
		  ORDERS nvarchar(MAX) AS JSON
		)
	) Level1
	CROSS APPLY OPENJSON(Level1.ORDERS)
	WITH(
		SalesOrderID int,
		OrderDate datetime,
		SubTotal money,
		DETAIL nvarchar(MAX) AS JSON
	)
) Level2
CROSS APPLY OPENJSON(Level2.DETAIL)
WITH(
	SalesOrderDetailID int,
	ProductID int,
	Name nvarchar(50),
	LineTotal numeric(38,6)
)
;

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

  • “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”でパーティション化されたコレクションを想定しています。

// SAMPLE STORED PROCEDURE
// ID「InsertDocuments」としてポータル上から登録します
//
// 引数 documents : JSON配列形式の文字列
// 戻値 作成したドキュメントの配列
function InsertDocuments(documents) {
    const collection = getContext().getCollection();
    const collLink = collection.getSelfLink();
    console.log("documents=" + documents +"\n");
    let jsonList;
    try {
        jsonList = JSON.parse(documents);
    } catch(e) {
        console.log(e);
        return null;
    }

    console.log("length=" + jsonList.length +"\n");
    let createdList = [];
    for(let i=0 ; i<jsonList.length ; i++){
        let doc = jsonList[i];

        // Query documents and tolist processed documents.
        let isAccepted = 
            collection.createDocument(
                    collLink, 
                    doc, 
                    function (err, document) {
                if (err) {
                    console.log(err);

                } else {
                    console.log("created " + document.id + "\n");
                    createdList.push(document);
                    if(i==jsonList.length-1){
                        getContext().getResponse().setBody(JSON.stringify(createdList));
                    }
                }
            });
        if (!isAccepted) throw new Error('The query was not accepted by the server.');
    }

    return;
}

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

using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using Microsoft.Azure.Documents;
using Microsoft.Azure.Documents.Client;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;

namespace FunctionsTest
{
    public static class FuncInsertDocument
    {
        private static readonly string EndpointUrl = "<your endpoint URL>";
        private static readonly string PrimaryKey = "<your primary key>";  //[Read-only Keys]で問題ない
        private static readonly string DataBaseId = "<your DataBase ID>";
        private static readonly string CollectionId = "<your Collection ID>";

        [FunctionName("FuncInsertDocument")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");
            DocumentClient client = new DocumentClient(new Uri(EndpointUrl), PrimaryKey);

            // 書込データ作成
            var partitionKeyValue = "U001";
            var dataList = new List<TestData>();
            dataList.Add(new TestData{
                UniqId = partitionKeyValue,
                SessionId = "SES001",
                Time = new DateTimeOffset(new DateTime(2018,1,15,0,0,0,DateTimeKind.Local))
            });

            // ストアドプロシージャ"InsertDocuments"を実行する
            Uri spUri = UriFactory.CreateStoredProcedureUri(
                DataBaseId,
                CollectionId,
                "InsertDocuments");
            string spParam = JsonConvert.SerializeObject(dataList);
            RequestOptions options = new RequestOptions() {
                EnableScriptLogging = true,
                PartitionKey = new PartitionKey(partitionKeyValue)
            };
            var result = await client.ExecuteStoredProcedureAsync<string>(
              spUri,
              options,
              spParam);

            // クエリ成功時にはスクリプトログを出力
            ActionResult ret = null;
            if (result.Response != null) {
                log.LogInformation(result.ScriptLog);
                ret = new OkObjectResult($"ret=" + result.ScriptLog);
            } else {
                ret = new BadRequestObjectResult("Please pass a name on the query string or in the request body");
            }
            return ret;
        }
    }

    public class TestData {
        [JsonProperty("uniqid")]
        public string UniqId {get;set;}

        [JsonProperty("sessionid")]
        public string SessionId { get; set; }

        [JsonProperty("time")]
        [JsonConverter(typeof(MyCustomDateTimeConverter))]
        public DateTimeOffset Time { get; set; }
    }

    public class MyCustomDateTimeConverter : IsoDateTimeConverter {
        public MyCustomDateTimeConverter() {
            base.DateTimeFormat = "yyyy-MM-dd'Z'HH:mm:ss'Z'";
        }
    }
}

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

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