fetch count of records from azure cosmos collection using python script or az cli

Jignesh Vala 61 Reputation points
2022-08-18T14:34:25.293+00:00

Hi Team,

Currently, I am using python library to fetch records from Azure cosmos db: https://pypi.org/project/azure-cosmos/

client = CosmosClient(url, credential=key)
database_name = 'cosmosdb'
container_name = 'cosmosCollection'
database = client.get_database_client(database_name)
container = database.get_container_client(container_name)
for item in container.query_items(query='SELECT * FROM cosmosCollection r WHERE r.id="0932804-890-890-098-03984923"',enable_cross_partition_query=True):

Above code working fine and I am able to fetch record based on id.

In my table I have more then 10k records.

I need to fetch only count of table's records.

for item in container.query_items(query='SELECT count(1) FROM cosmosCollection'):
I tried above code but it's not working and gives below error:

(BadRequest) Message: {"Errors":["Cross partition query only supports 'VALUE <AggregateFunc>' for aggregates."]}
ActivityId: 7f353ddf-b55c-4a10-b573-5510d0f73a0b, Microsoft.Azure.Documents.Common/2.14.0
Code: BadRequest

Please suggest me how to fetch count of record using python library. If we can fetch using az cli or python rest apis that will also work for me.

Thanks in advance.

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,439 questions
.NET CLI
.NET CLI
A cross-platform toolchain for developing, building, running, and publishing .NET applications.
322 questions
Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,359 questions
Accepted answer
  1. GeethaThatipatri-MSFT 27,017 Reputation points Microsoft Employee
    2022-08-22T15:47:10.16+00:00

    Hi, @Jignesh Vala Looks like your count query is incorrect.
    it should be select value count(1) from cosmosCollection
    Can you please give it a try?

    Regards
    Geetha

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Limitless Technology 43,926 Reputation points
    2022-08-19T14:20:39.507+00:00

    Hi Jignesh,

    Getting started
    Important update on Python 2.x Support
    New releases of this SDK won't support Python 2.x starting January 1st, 2022. Please check the CHANGELOG for more information.

    Prerequisites
    Azure subscription - Create a free account
    Azure Cosmos DB account - SQL API
    Python 3.6+
    If you need a Cosmos DB SQL API account, you can create one with this Azure CLI command:

    Bash

    Copy
    az cosmosdb create --resource-group <resource-group-name> --name <cosmos-account-name>
    Install the package
    Bash

    Copy
    pip install azure-cosmos
    Configure a virtual environment (optional)
    Although not required, you can keep your base system and Azure SDK environments isolated from one another if you use a virtual environment. Execute the following commands to configure and then enter a virtual environment with venv:

    Bash

    Copy
    python3 -m venv azure-cosmosdb-sdk-environment
    source azure-cosmosdb-sdk-environment/bin/activate
    Authenticate the client
    Interaction with Cosmos DB starts with an instance of the CosmosClient class. You need an account, its URI, and one of its account keys to instantiate the client object.

    Use the Azure CLI snippet below to populate two environment variables with the database account URI and its primary master key (you can also find these values in the Azure portal). The snippet is formatted for the Bash shell.

    Bash

    Copy
    RES_GROUP=<resource-group-name>
    ACCT_NAME=<cosmos-db-account-name>

    export ACCOUNT_URI=$(az cosmosdb show --resource-group $RES_GROUP --name $ACCT_NAME --query documentEndpoint --output tsv)
    export ACCOUNT_KEY=$(az cosmosdb list-keys --resource-group $RES_GROUP --name $ACCT_NAME --query primaryMasterKey --output tsv)
    Create the client
    Once you've populated the ACCOUNT_URI and ACCOUNT_KEY environment variables, you can create the CosmosClient.

    Python

    Copy
    from azure.cosmos import CosmosClient

    import os
    URL = os.environ['ACCOUNT_URI']
    KEY = os.environ['ACCOUNT_KEY']
    client = CosmosClient(URL, credential=KEY)
    AAD Authentication
    You can also authenticate a client utilizing your service principal's AAD credentials and the azure identity package. You can directly pass in the credentials information to ClientSecretCrednetial, or use the DefaultAzureCredential:

    Python

    Copy
    from azure.cosmos import CosmosClient
    from azure.identity import ClientSecretCredential, DefaultAzureCredential

    import os
    url = os.environ['ACCOUNT_URI']
    tenant_id = os.environ['TENANT_ID']
    client_id = os.environ['CLIENT_ID']
    client_secret = os.environ['CLIENT_SECRET']

    Using ClientSecretCredential

    aad_credentials = ClientSecretCredential(
    tenant_id=tenant_id,
    client_id=client_id,
    client_secret=client_secret)

    Using DefaultAzureCredential (recommended)

    aad_credentials = DefaultAzureCredential()

    client = CosmosClient(url, aad_credentials)
    Always ensure that the managed identity you use for AAD authentication has readMetadata permissions.