steampipe plugin install azure

Table: azure_cosmosdb_mongo_collection - Query Azure Cosmos DB Mongo Collections using SQL

Azure Cosmos DB is a globally distributed, multi-model database service for any scale. It offers multiple well-defined consistency models, guaranteed single-digit-millisecond read and write latencies at the 99th percentile, and guaranteed 99.999% high availability with multi-homing. In particular, the Mongo Collection is a resource within the Azure Cosmos DB that represents a group of MongoDB documents.

Table Usage Guide

The azure_cosmosdb_mongo_collection table provides insights into Mongo Collections within Azure Cosmos DB. As a database administrator, explore collection-specific details through this table, including the collection's name, resource group, account name, and more. Utilize it to uncover information about collections, such as their properties, the associated database, and the verification of their configurations.

Important notes:

  • You must specify the database_name = '<cosmosdb_mongo_database_name>' in the where clause to query this table.

Examples

Basic info

This query is used to gain insights into the relationship between Azure CosmosDB Mongo collections and databases. It can be used to manage and analyze the distribution of resources across different databases and regions, which is crucial for optimizing resource usage and performance.

select
c.name,
c.database_name,
c.account_name,
c.region,
c.resource_group,
c.shard_key,
c.id
from
azure_cosmosdb_mongo_collection c,
azure_cosmosdb_mongo_database d
where
c.database_name = d.name;
select
c.name,
c.database_name,
c.account_name,
c.region,
c.resource_group,
c.shard_key,
c.id
from
azure_cosmosdb_mongo_collection c,
azure_cosmosdb_mongo_database d
where
c.database_name = d.name;

Collection count by cosmos DB database name

Discover the segments that have a significant number of collections in your Azure Cosmos DB. This is beneficial for understanding database usage and managing resource allocation effectively.

select
c.database_name,
count(c.name) as collection_count
from
azure_cosmosdb_mongo_collection c,
azure_cosmosdb_mongo_database d
where
c.database_name = d.name
group by
database_name;
select
c.database_name,
count(c.name) as collection_count
from
azure_cosmosdb_mongo_collection c
join azure_cosmosdb_mongo_database d on c.database_name = d.name
group by
c.database_name;

Get throughput settings for each collection

Analyze the settings to understand the throughput configurations for each collection in your Azure Cosmos DB. This helps in optimizing resource utilization and managing the performance of your database.

select
c.name as collection_name,
c.database_name,
c.account_name,
c.throughput_settings ->> 'Name' as name,
c.throughput_settings ->> 'ResourceThroughput' as throughput,
c.throughput_settings ->> 'AutoscaleSettingsMaxThroughput' as maximum_throughput,
c.throughput_settings ->> 'ResourceMinimumThroughput' as minimum_throughput,
c.throughput_settings ->> 'ID' as id
from
azure_cosmosdb_mongo_collection c,
azure_cosmosdb_mongo_database d
where
c.database_name = d.name;
select
c.name as collection_name,
c.database_name,
c.account_name,
json_extract(c.throughput_settings, '$.Name') as name,
json_extract(c.throughput_settings, '$.ResourceThroughput') as throughput,
json_extract(
c.throughput_settings,
'$.AutoscaleSettingsMaxThroughput'
) as maximum_throughput,
json_extract(
c.throughput_settings,
'$.ResourceMinimumThroughput'
) as minimum_throughput,
json_extract(c.throughput_settings, '$.ID') as id
from
azure_cosmosdb_mongo_collection c,
azure_cosmosdb_mongo_database d
where
c.database_name = d.name;

Get index keys in each collection

Determine the areas in which specific index keys are used across different collections in Azure Cosmos DB. This is beneficial for optimizing database performance and understanding data distribution across your collections.

select
c.name as collection_name,
c.database_name,
c.account_name,
i -> 'key' -> 'keys' as index_keys
from
azure_cosmosdb_mongo_collection c,
azure_cosmosdb_mongo_database d,
jsonb_array_elements(indexes) i
where
c.database_name = d.name;
select
c.name as collection_name,
c.database_name,
c.account_name,
json_extract(i.value, '$.key.keys') as index_keys
from
azure_cosmosdb_mongo_collection c,
azure_cosmosdb_mongo_database d,
json_each(indexes) as i
where
c.database_name = d.name;

Schema for azure_cosmosdb_mongo_collection

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_nametext=The friendly name that identifies the cosmosdb account in which the collection is created.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
analytical_storage_ttlbigintAnalytical TTL.
autoscale_settings_max_throughputbigintContains maximum throughput, the resource can scale up to.
cloud_environmenttextThe Azure Cloud Environment.
collection_etagtextA system generated property representing the resource etag required for optimistic concurrency control.
collection_idtextName of the Cosmos DB MongoDB collection.
collection_ridtextA system generated unique identifier for collection.
collection_tsbigintA system generated property that denotes the last updated timestamp of the resource.
database_nametext=The friendly name that identifies the database in which the collection is created.
idtextContains ID to identify a Mongo DB collection uniquely.
indexesjsonbList of index keys.
nametext=The friendly name that identifies the Mongo DB collection.
regiontextThe Azure region/location in which the resource is located.
resource_grouptext=The resource group which holds this resource.
shard_keyjsonbA key-value pair of shard keys to be applied for the request.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
subscription_idtext=, !=, ~~, ~~*, !~~, !~~*The Azure Subscription ID in which the resource is located.
tagsjsonbA map of tags for the resource.
throughputbigintContains the value of the Cosmos DB resource throughput.
throughput_settingsjsonbContains the Cosmos DB resource throughput or autoscaleSettings.
titletextTitle of the resource.
typetextType of the resource.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azure

You can pass the configuration to the command with the --config argument:

steampipe_export_azure --config '<your_config>' azure_cosmosdb_mongo_collection