Share via

how to use count query using CosmosTemplate from springboot

Gondi 0 Reputation points
2023-04-20T09:26:24.72+00:00 
String sql = "SELECT value COUNT(rowCount) FROM ( SELECT DISTINCT c.opportunityId FROM c GROUP BY c.opportunityId ) AS rowCount";
SqlQuerySpec sqlQuery = new SqlQuerySpec(sql);

cosmosTemplate.count(sqlQuery,"xxx.class") or cosmosTemplate.count(sqlQuery,"c")

Resolved[
  com.azure.spring.data.cosmos.exception.CosmosAccessException: Failedtogetcountvalue;nestedexceptionis{
    "ClassName": "CosmosException",
    "userAgent": "azsdk-java-cosmos/4.16.0 Windows10/10.0 JRE/11.0.17",
    "statusCode": 404,
    "resourceAddress": "https://127.0.0.1:8081/dbs/POC/colls/OfferCase.class",
    "error": "{\"code\":\"NotFound\",\"message\":\"Message: {\\\"Errors\\\":[\\\"Resource Not Found. Learn more: https:\\\\/\\\\/aka.ms\\\\/cosmosdb-tsg-not-found\\\"]}\\r\\nActivityId: 05a51d5b-4542-4fcc-a7c2-eb47c5e99998, Request URI: /apps/DocDbApp/services/DocDbMaster0/partitions/780e44f4-38c8-11e6-8106-8cdcd42c33be/replicas/1p/, RequestStats: \\r\\nRequestStartTime: 2023-04-20T09:20:51.3718690Z, RequestEndTime: 2023-04-20T09:20:51.3758565Z,  Number of regions attempted:1\\r\\n{\\\"systemHistory\\\":[{\\\"dateUtc\\\":\\\"2023-04-20T09:19:58.2129285Z\\\",\\\"cpu\\\":8.873,\\\"memory\\\":2245156.000,\\\"threadInfo\\\":{\\\"isThreadStarving\\\":\\\"False\\\",\\\"threadWaitIntervalInMs\\\":0.0255,\\\"availableThreads\\\":32766,\\\"minThreads\\\":8,\\\"maxThreads\\\":32767},\\\"numberOfOpenTcpConnection\\\":2},{\\\"dateUtc\\\":\\\"2023-04-20T09:20:08.2279298Z\\\",\\\"cpu\\\":11.973,\\\"memory\\\":2270928.000,\\\"threadInfo\\\":{\\\"isThreadStarving\\\":\\\"False\\\",\\\"threadWaitIntervalInMs\\\":0.049,\\\"availableThreads\\\":32766,\\\"minThreads\\\":8,\\\"maxThreads\\\":32767},\\\"numberOfOpenTcpConnection\\\":2},{\\\"dateUtc\\\":\\\"2023-04-20T09:20:18.2335840Z\\\",\\\"cpu\\\":7.266,\\\"memory\\\":2300040.000,\\\"threadInfo\\\":{\\\"isThreadStarving\\\":\\\"False\\\",\\\"threadWaitIntervalInMs\\\":0.0346,\\\"availableThreads\\\":32766,\\\"minThreads\\\":8,\\\"maxThreads\\\":32767},\\\"numberOfOpenTcpConnection\\\":2},{\\\"dateUtc\\\":\\\"2023-04-20T09:20:28.2395269Z\\\",\\\"cpu\\\":15.562,\\\"memory\\\":2238532.000,\\\"threadInfo\\\":{\\\"isThreadStarving\\\":\\\"False\\\",\\\"threadWaitIntervalInMs\\\":0.048,\\\"availableThreads\\\":32766,\\\"minThreads\\\":8,\\\"maxThreads\\\":32767},\\\"numberOfOpenTcpConnection\\\":2},{\\\"dateUtc\\\":\\\"2023-04-20T09:20:38.2417243Z\\\",\\\"cpu\\\":29.688,\\\"memory\\\":2820096.000,\\\"threadInfo\\\":{\\\"isThreadStarving\\\":\\\"False\\\",\\\"threadWaitIntervalInMs\\\":0.03,\\\"availableThreads\\\":32766,\\\"minThreads\\\":8,\\\"maxThreads\\\":32767},\\\"numberOfOpenTcpConnection\\\":2},{\\\"dateUtc\\\":\\\"2023-04-20T09:20:48.2486265Z\\\",\\\"cpu\\\":20.293,\\\"memory\\\":2419848.000,\\\"threadInfo\\\":{\\\"isThreadStarving\\\":\\\"False\\\",\\\"threadWaitIntervalInMs\\\":0.1937,\\\"availableThreads\\\":32766,\\\"minThreads\\\":8,\\\"maxThreads\\\":32767},\\\"numberOfOpenTcpConnection\\\":2}]}\\r\\nRequestStart: 2023-04-20T09:20:51.3718690Z; ResponseTime: 2023-04-20T09:20:51.3758565Z; StoreResult: StorePhysicalAddress: rntbd://127.0.0.1:10251/apps/DocDbApp/services/DocDbMaster0/partitions/780e44f4-38c8-11e6-8106-8cdcd42c33be/replicas/1p/, LSN: 177, GlobalCommittedLsn: -1, PartitionKeyRangeId: , IsValid: True, StatusCode: 404, SubStatusCode: 0, RequestCharge: 1, ItemLSN: -1, SessionToken: -1#177, UsingLocalLSN: False, TransportException: null, BELatencyMs: 0.325, ActivityId: 05a51d5b-4542-4fcc-a7c2-eb47c5e99998, RetryAfterInMs: , TransportRequestTimeline: {\\\"requestTimeline\\\":[{\\\"event\\\": \\\"Created\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3718690Z\\\", \\\"durationInMs\\\": 0.0158},{\\\"event\\\": \\\"ChannelAcquisitionStarted\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3718848Z\\\", \\\"durationInMs\\\": 0.0026},{\\\"event\\\": \\\"Pipelined\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3718874Z\\\", \\\"durationInMs\\\": 0.3541},{\\\"event\\\": \\\"Transit Time\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3722415Z\\\", \\\"durationInMs\\\": 0.7048},{\\\"event\\\": \\\"Received\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3729463Z\\\", \\\"durationInMs\\\": 0.1667},{\\\"event\\\": \\\"Completed\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3731130Z\\\", \\\"durationInMs\\\": 0}],\\\"serviceEndpointStats\\\":{\\\"inflightRequests\\\":1,\\\"openConnections\\\":1},\\\"connectionStats\\\":{\\\"waitforConnectionInit\\\":\\\"False\\\",\\\"callsPendingReceive\\\":0,\\\"lastSendAttempt\\\":\\\"2023-04-20T09:20:50.9284346Z\\\",\\\"lastSend\\\":\\\"2023-04-20T09:20:50.9284346Z\\\",\\\"lastReceive\\\":\\\"2023-04-20T09:20:50.9294350Z\\\"},\\\"requestSizeInBytes\\\":417,\\\"responseMetadataSizeInBytes\\\":172,\\\"responseBodySizeInBytes\\\":87};\\r\\n ResourceType: Collection, OperationType: Read\\r\\nRequestStart: 2023-04-20T09:20:51.3728576Z; ResponseTime: 2023-04-20T09:20:51.3758565Z; StoreResult: StorePhysicalAddress: rntbd://127.0.0.1:10251/apps/DocDbApp/services/DocDbMaster0/partitions/780e44f4-38c8-11e6-8106-8cdcd42c33be/replicas/1p/, LSN: 177, GlobalCommittedLsn: -1, PartitionKeyRangeId: , IsValid: True, StatusCode: 404, SubStatusCode: 0, RequestCharge: 1, ItemLSN: -1, SessionToken: -1#177, UsingLocalLSN: False, TransportException: null, BELatencyMs: 0.87, ActivityId: 05a51d5b-4542-4fcc-a7c2-eb47c5e99998, RetryAfterInMs: , TransportRequestTimeline: {\\\"requestTimeline\\\":[{\\\"event\\\": \\\"Created\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3718690Z\\\", \\\"durationInMs\\\": 0.0028},{\\\"event\\\": \\\"ChannelAcquisitionStarted\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3718718Z\\\", \\\"durationInMs\\\": 0.0009},{\\\"event\\\": \\\"Pipelined\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3718727Z\\\", \\\"durationInMs\\\": 0.2293},{\\\"event\\\": \\\"Transit Time\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3721020Z\\\", \\\"durationInMs\\\": 2.2962},{\\\"event\\\": \\\"Received\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3743982Z\\\", \\\"durationInMs\\\": 0.1878},{\\\"event\\\": \\\"Completed\\\", \\\"startTimeUtc\\\": \\\"2023-04-20T09:20:51.3745860Z\\\", \\\"durationInMs\\\": 0}],\\\"serviceEndpointStats\\\":{\\\"inflightRequests\\\":2,\\\"openConnections\\\":1},\\\"connectionStats\\\":{\\\"waitforConnectionInit\\\":\\\"False\\\",\\\"callsPendingReceive\\\":1,\\\"lastSendAttempt\\\":\\\"2023-04-20T09:20:51.3718690Z\\\",\\\"lastSend\\\":\\\"2023-04-20T09:20:51.3728576Z\\\",\\\"lastReceive\\\":\\\"2023-04-20T09:20:50.9294350Z\\\"},\\\"requestSizeInBytes\\\":417,\\\"responseMetadataSizeInBytes\\\":172,\\\"responseBodySizeInBytes\\\":87};\\r\\n ResourceType: Collection, OperationType: Read\\r\\n, SDK: Microsoft.Azure.Documents.Common/2.14.0, StatusCode: NotFound\",\"additionalErrorInfo\":null}",
    "innerErrorMessage": "Message: {\"Errors\":[\"Resource Not Found. Learn more: https:\\/\\/aka.ms\\/cosmosdb-tsg-not-found\"]}\r\nActivityId: 05a51d5b-4542-4fcc-a7c2-eb47c5e99998, Request URI: /apps/DocDbApp/services/DocDbMaster0/partitions/780e44f4-38c8-11e6-8106-8cdcd42c33be/replicas/1p/, RequestStats: \r\nRequestStartTime: 2023-04-20T09:20:51.3718690Z, RequestEndTime: 2023-04-20T09:20:51.3758565Z,  Number of regions attempted:1\r\n{\"systemHistory\":[{\"dateUtc\":\"2023-04-20T09:19:58.2129285Z\",\"cpu\":8.873,\"memory\":2245156.000,\"threadInfo\":{\"isThreadStarving\":\"False\",\"threadWaitIntervalInMs\":0.0255,\"availableThreads\":32766,\"minThreads\":8,\"maxThreads\":32767},\"numberOfOpenTcpConnection\":2},{\"dateUtc\":\"2023-04-20T09:20:08.2279298Z\",\"cpu\":11.973,\"memory\":2270928.000,\"threadInfo\":{\"isThreadStarving\":\"False\",\"threadWaitIntervalInMs\":0.049,\"availableThreads\":32766,\"minThreads\":8,\"maxThreads\":32767},\"numberOfOpenTcpConnection\":2},{\"dateUtc\":\"2023-04-20T09:20:18.2335840Z\",\"cpu\":7.266,\"memory\":2300040.000,\"threadInfo\":{\"isThreadStarving\":\"False\",\"threadWaitIntervalInMs\":0.0346,\"availableThreads\":32766,\"minThreads\":8,\"maxThreads\":32767},\"numberOfOpenTcpConnection\":2},{\"dateUtc\":\"2023-04-20T09:20:28.2395269Z\",\"cpu\":15.562,\"memory\":2238532.000,\"threadInfo\":{\"isThreadStarving\":\"False\",\"threadWaitIntervalInMs\":0.048,\"availableThreads\":32766,\"minThreads\":8,\"maxThreads\":32767},\"numberOfOpenTcpConnection\":2},{\"dateUtc\":\"2023-04-20T09:20:38.2417243Z\",\"cpu\":29.688,\"memory\":2820096.000,\"threadInfo\":{\"isThreadStarving\":\"False\",\"threadWaitIntervalInMs\":0.03,\"availableThreads\":32766,\"minThreads\":8,\"maxThreads\":32767},\"numberOfOpenTcpConnection\":2},{\"dateUtc\":\"2023-04-20T09:20:48.2486265Z\",\"cpu\":20.293,\"memory\":2419848.000,\"threadInfo\":{\"isThreadStarving\":\"False\",\"threadWaitIntervalInMs\":0.1937,\"availableThreads\":32766,\"minThreads\":8,\"maxThreads\":32767},\"numberOfOpenTcpConnection\":2}]}\r\nRequestStart: 2023-04-20T09:20:51.3718690Z; ResponseTime: 2023-04-20T09:20:51.3758565Z; StoreResult: StorePhysicalAddress: rntbd://127.0.0.1:10251/apps/DocDbApp/services/DocDbMaster0/partitions/780e44f4-38c8-11e6-8106-8cdcd42c33be/replicas/1p/, LSN: 177, GlobalCommittedLsn: -1, PartitionKeyRangeId: , IsValid: True, StatusCode: 404, SubStatusCode: 0, RequestCharge: 1, ItemLSN: -1, SessionToken: -1#177, UsingLocalLSN: False, TransportException: null, BELatencyMs: 0.325, ActivityId: 05a51d5b-4542-4fcc-a7c2-eb47c5e99998, RetryAfterInMs: , TransportRequestTimeline: {\"requestTimeline\":[{\"event\": \"Created\", \"startTimeUtc\": \"2023-04-20T09:20:51.3718690Z\", \"durationInMs\": 0.0158},{\"event\": \"ChannelAcquisitionStarted\", \"startTimeUtc\": \"2023-04-20T09:20:51.3718848Z\", \"durationInMs\": 0.0026},{\"event\": \"Pipelined\", \"startTimeUtc\": \"2023-04-20T09:20:51.3718874Z\", \"durationInMs\": 0.3541},{\"event\": \"Transit Time\", \"startTimeUtc\": \"2023-04-20T09:20:51.3722415Z\", \"durationInMs\": 0.7048},{\"event\": \"Received\", \"startTimeUtc\": \"2023-04-20T09:20:51.3729463Z\", \"durationInMs\": 0.1667},{\"event\": \"Completed\", \"startTimeUtc\": \"2023-04-20T09:20:51.3731130Z\", \"durationInMs\": 0}],\"serviceEndpointStats\":{\"inflightRequests\":1,\"openConnections\":1},\"connectionStats\":{\"waitforConnectionInit\":\"False\",\"callsPendingReceive\":0,\"lastSendAttempt\":\"2023-04-20T09:20:50.9284346Z\",\"lastSend\":\"2023-04-20T09:20:50.9284346Z\",\"lastReceive\":\"2023-04-20T09:20:50.9294350Z\"},\"requestSizeInBytes\":417,\"responseMetadataSizeInBytes\":172,\"responseBodySizeInBytes\":87};\r\n ResourceType: Collection, OperationType: Read\r\nRequestStart: 2023-04-20T09:20:51.3728576Z; ResponseTime: 2023-04-20T09:20:51.3758565Z; StoreResult: StorePhysicalAddress: rntbd://127.0.0.1:10251/apps/DocDbApp/services/DocDbMaster0/partitions/780e44f4-38c8-11e6-8106-8cdcd42c33be/replicas/1p/, LSN: 177, GlobalCommittedLsn: -1, PartitionKeyRangeId: , IsValid: True, StatusCode: 404, SubStatusCode: 0, RequestCharge: 1, ItemLSN: -1, SessionToken: -1#177, UsingLocalLSN: False, TransportException: null, BELatencyMs: 0.87, ActivityId: 05a51d5b-4542-4fcc-a7c2-eb47c5e99998, RetryAfterInMs: , TransportRequestTimeline: {\"requestTimeline\":[{\"event\": \"Created\", \"startTimeUtc\": \"2023-04-20T09:20:51.3718690Z\", \"durationInMs\": 0.0028},{\"event\": \"ChannelAcquisitionStarted\", \"startTimeUtc\": \"2023-04-20T09:20:51.3718718Z\", \"durationInMs\": 0.0009},{\"event\": \"Pipelined\", \"startTimeUtc\": \"2023-04-20T09:20:51.3718727Z\", \"durationInMs\": 0.2293},{\"event\": \"Transit Time\", \"startTimeUtc\": \"2023-04-20T09:20:51.3721020Z\", \"durationInMs\": 2.2962},{\"event\": \"Received\", \"startTimeUtc\": \"2023-04-20T09:20:51.3743982Z\", \"durationInMs\": 0.1878},{\"event\": \"Completed\", \"startTimeUtc\": \"2023-04-20T09:20:51.3745860Z\", \"durationInMs\": 0}],\"serviceEndpointStats\":{\"inflightRequests\":2,\"openConnections\":1},\"connectionStats\":{\"waitforConnectionInit\":\"False\",\"callsPendingReceive\":1,\"lastSendAttempt\":\"2023-04-20T09:20:51.3718690Z\",\"lastSend\":\"2023-04-20T09:20:51.3728576Z\",\"lastReceive\":\"2023-04-20T09:20:50.9294350Z\"},\"requestSizeInBytes\":417,\"responseMetadataSizeInBytes\":172,\"responseBodySizeInBytes\":87};\r\n ResourceType: Collection, OperationType: Read\r\n, SDK: Microsoft.Azure.Documents.Common/2.14.0, StatusCode: NotFound",
    "causeInfo": null,
    "responseHeaders": "{x-ms-last-state-change-utc=Thu, 20 Apr 2023 04:20:40.621 GMT, Server=Microsoft-HTTPAPI/2.0, x-ms-session-token=0:-1#177, Access-Control-Allow-Origin=, lsn=177, x-ms-request-charge=2, Content-Location=https://127.0.0.1:8081/dbs/POC/colls/OfferCase.class, x-ms-transport-request-id=1585, x-ms-cosmos-quorum-acked-llsn=177, x-ms-quorum-acked-lsn=177, Access-Control-Allow-Credentials=true, x-ms-serviceversion=version=2.14.0.0, Content-Type=application/json, Transfer-Encoding=chunked, x-ms-current-replica-set-size=1, x-ms-request-duration-ms=0.325, x-ms-schemaversion=1.14, x-ms-number-of-read-regions=0, x-ms-current-write-quorum=1, Date=Thu, 20 Apr 2023 09:20:50 GMT, x-ms-activity-id=05a51d5b-4542-4fcc-a7c2-eb47c5e99998, x-ms-xp-role=0, x-ms-global-Committed-lsn=-1, x-ms-cosmos-llsn=177, x-ms-gatewayversion=version=2.14.0}",
    "requestHeaders": "[x-ms-date=Thu, 20 Apr 2023 09:20:51 GMT]",
    "cosmosDiagnostics": {
      "userAgent": "azsdk-java-cosmos/4.16.0 Windows10/10.0 JRE/11.0.17",
      "requestLatencyInMs": 25,
      "requestStartTimeUTC": "2023-04-20T09:20:51.360673900Z",
      "requestEndTimeUTC": "2023-04-20T09:20:51.386374400Z",
      "responseStatisticsList": [
        
      ],
      "supplementalResponseStatisticsList": [
        
      ],
      "addressResolutionStatistics": {
        
      },
      "regionsContacted": [
        "https://127.0.0.1:8081/"
      ],
      "retryContext": {
        "statusAndSubStatusCodes": null,
        "retryCount": 0,
        "retryLatency": 0
      },
      "metadataDiagnosticsContext": {
        "metadataDiagnosticList": null
      },
      "serializationDiagnosticsContext": {
        "serializationDiagnosticsList": null
      },
      "gatewayStatistics": {
        "sessionToken": null,
        "operationType": "Read",
        "resourceType": "DocumentCollection",
        "statusCode": 404,
        "subStatusCode": 0,
        "requestCharge": null,
        "requestTimeline": [
          {
            "eventName": "connectionCreated",
            "startTimeUTC": "2023-04-20T09:20:51.361682200Z",
            "durationInMicroSec": 0
          },
          {
            "eventName": "connectionConfigured",
            "startTimeUTC": "2023-04-20T09:20:51.361682200Z",
            "durationInMicroSec": 0
          },
          {
            "eventName": "requestSent",
            "startTimeUTC": "2023-04-20T09:20:51.361682200Z",
            "durationInMicroSec": 1005
          },
          {
            "eventName": "transitTime",
            "startTimeUTC": "2023-04-20T09:20:51.362687400Z",
            "durationInMicroSec": 15134
          },
          {
            "eventName": "received",
            "startTimeUTC": "2023-04-20T09:20:51.377822Z",
            "durationInMicroSec": 0
          }
        ]
      },
      "systemInformation": {
        "usedMemory": "242604 KB",
        "availableMemory": "3875924 KB",
        "systemCpuLoad": "(2023-04-20T09:20:43.591647100Z 21.4%), (2023-04-20T09:20:48.587968500Z 15.4%)",
        "availableProcessors": 8
      },
      "clientCfgs": {
        "id": 0,
        "connectionMode": "DIRECT",
        "numberOfClients": 1,
        "connCfg": {
          "rntbd": "(cto:PT5S, rto:PT5S, icto:PT0S, ieto:PT1H, mcpe:130, mrpc:30, cer:false)",
          "gw": "(cps:1000, rto:PT5S, icto:null, p:false)",
          "other": "(ed: true, cs: false)"
        },
        "consistencyCfg": "(consistency: null, mm: true, prgns: [])"
      }
    }
  }
]
Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,906 questions

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.