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

Question

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.

Answer 1

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

Answer 2

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.