Azure CosmosDB 查询资源管理器结果与 Azure Functions 结果之间的差异 [英] Difference among the Azure CosmosDB Query Explorer's results and the Azure Functions results

问题描述

我在 CosmosDB 查询资源管理器上执行了以下查询.

SELECT c.id, c.created_at FROM cWHERE c.created_at_epoch <= 1499871600 - 86400*31和(天花板(1499871600/86400)-天花板(c.created_at_epoch/86400))% 31 = 0

结果如下.

<代码>[{"id": "70251cbf-44b3-4cd9-991f-81127ad78bca",created_at":2017-05-11 18:46:16"},{"id": "0fa31de2-4832-49ea-a0c6-b517d64ede85",created_at":2017-05-11 18:48:22"},{id":b9959d15-92e7-41c3-8eff-718c4ab2be6e"，created_at":2017-05-11 19:01:43"}]

看起来问题不存在.

接下来，我将静态定义的 epoch 值替换为占位符，以将其用作 Azure Functions DocumentDB 输入绑定的 sqlQuery.然后，我将调制符号替换为 %modulationsymbol% 以避免

然后，我指定函数如下.

//index.jsmodule.exports = function (context, myQueueItem) {context.log(context.bindings.members, myQueueItem);context.done();};//函数.json{绑定":[{名称":我的队列项目"，类型":队列触发器"，方向":在"，"queueName": "我的队列",连接":我的存储"},{类型":文档数据库"，名称":成员"，数据库名称":我的项目数据库"，"collectionName": "成员","sqlQuery": "SELECT c.id, c.created_at FROM c WHERE {epoch} - c.created_at_epoch >= 86400*31 AND (CEILING({epoch}/86400) - CEILING(c.created_at_epoch/86400)) %调制符号% 31 = 0",连接":MYCOSMOSDB"，方向":在"}],禁用":真}

之后，我触发了这个函数，结果如下.

2017-07-05T03:57:29.640 功能已启动 (Id=d980521e-d23a-4bda-a730-57a236bcd011)2017-07-05T03:57:30.594 [] { 纪元:1499871600 }2017-07-05T03:57:30.594 功能完成(成功，Id=d980521e-d23a-4bda-a730-57a236bcd011，持续时间=951ms)

看起来 context.bindings.members 是一个空列表.它与 CosmosDB 查询资源管理器的结果不同.

为什么会出现这种差异?

解决方案

打印出{epoch}后，发现它的类型是字符串，预期的类型是数字而不是字符串.这就是使用相同查询时得到空列表的原因.

要解决此问题，您可以将类型转换为数字，然后再使用它来过滤查询结果.以下步骤供您参考.

第一步，创建一个可以将字符串转换为数字的UDF.脚本资源管理器->创建用户定义函数

函数 toNumber(ts) {返回 parseInt(ts);}

第2步，创建ConvertToNumber函数后，您可以使用它将{epoch}的类型转换为数字.

SELECT c.id, c.created_at FROM cWHERE c.created_at_epoch <= udf.ConvertToNumber({epoch}) - 86400*31和(天花板(udf.ConvertToNumber({epoch})/86400)-天花板(c.created_at_epoch/86400))％调制符号％31 = 0

如果您熟悉 C#，则可以使用 C# 创建函数.因为 C# 是一种强类型语言.我们可以定义一个类，用于反序列化队列中的消息.它会在语言层转换类型.

公共类 EpochMessage{公共 int 纪元 { 得到；放;}}

整个函数可能是这样的.

使用系统；公共静态无效运行(EpochMessage myQueueItem，TraceWriter 日志，IEnumerable<dynamic> 成员){log.Info(context.bindings.members, myQueueItem);}公共类 EpochMessage{公共 int 纪元 { 得到；放;}}

I executed a following query on the CosmosDB Query Explorer.

SELECT c.id, c.created_at FROM c 
WHERE c.created_at_epoch <= 1499871600 - 86400*31 
AND (CEILING(1499871600/86400) - CEILING(c.created_at_epoch / 86400)) % 31 = 0

That's result is followings.

[
  {
    "id": "70251cbf-44b3-4cd9-991f-81127ad78bca",
    "created_at": "2017-05-11 18:46:16"
  },
  {
    "id": "0fa31de2-4832-49ea-a0c6-b517d64ede85",
    "created_at": "2017-05-11 18:48:22"
  },
  {
    "id": "b9959d15-92e7-41c3-8eff-718c4ab2be6e",
    "created_at": "2017-05-11 19:01:43"
  }
]

It looks that problem does not exist.

Next, I replaced epoch values that defined statically to placeholders for using it as sqlQuery of the Azure Functions DocumentDB input bindings. Then, I replaced a modulations symbol to %modulationsymbol% for avoiding this issue

SELECT c.id, c.created_at FROM c 
WHERE c.created_at_epoch <= {epoch} - 86400*31 
AND (CEILING({epoch}/86400) - CEILING(c.created_at_epoch / 86400))  %modulationsymbol% 31 = 0

And I defined modulationsymbol = % as an application setting.

Then, I specified the function as follows.

// index.js
module.exports = function (context, myQueueItem) {
    context.log(context.bindings.members, myQueueItem);
    context.done();
};

// function.json
{
  "bindings": [
    {
      "name": "myQueueItem",
      "type": "queueTrigger",
      "direction": "in",
      "queueName": "myqueue",
      "connection": "MYSTORAGE"
    },
    {
      "type": "documentDB",
      "name": "members",
      "databaseName": "myproject-db",
      "collectionName": "member",
      "sqlQuery": "SELECT c.id, c.created_at FROM c  WHERE {epoch} - c.created_at_epoch >= 86400*31  AND (CEILING({epoch}/86400) - CEILING(c.created_at_epoch / 86400)) %modulationsymbol% 31 = 0",
      "connection": "MYCOSMOSDB",
      "direction": "in"
    }
  ],
  "disabled": true
}

Afterward, I triggered the function and this result is followings.

2017-07-05T03:57:29.640 Function started (Id=d980521e-d23a-4bda-a730-57a236bcd011)
2017-07-05T03:57:30.594 [] { epoch: 1499871600 }
2017-07-05T03:57:30.594 Function completed (Success, Id=d980521e-d23a-4bda-a730-57a236bcd011, Duration=951ms)

It looks context.bindings.members is a empty list. It differenced from the CosmosDB Query Explorer's result.

Why appeared this differences?

解决方案

After print out the {epoch}, I found its type is string and the expected type is number instead of string. That is the reason of getting empty list when use the same query.

To fix this issue, you could convert the type to number before using it to filter your query result. Steps below for your reference.

Step 1, Create a UDF which could convert string to number. Script Explorer->Create User Defined Function

function toNumber(ts) { 
   return parseInt(ts);
}

Step 2, After created the ConvertToNumber function, you could use it to convert the type of {epoch} to number.

SELECT c.id, c.created_at FROM c 
WHERE c.created_at_epoch <= udf.ConvertToNumber({epoch}) - 86400*31 
AND (CEILING(udf.ConvertToNumber({epoch})/86400) - CEILING(c.created_at_epoch / 86400))  %modulationsymbol% 31 = 0

If you are familiar with C#, you could create the function with C#. Since C# is a strongly typed language. We can define a class which is used to deserialize the message from queue. It will convert the type in language layer.

public class EpochMessage
{
    public int epoch { get; set; }
}

The whole function could be like this.

using System;

public static void Run(EpochMessage myQueueItem, TraceWriter log, IEnumerable<dynamic> members)
{
    log.Info(context.bindings.members, myQueueItem);
}

public class EpochMessage
{
    public int epoch { get; set; }
}

这篇关于Azure CosmosDB 查询资源管理器结果与 Azure Functions 结果之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！