Secondary indexing in Azure Cosmos DB for Apache Cassandra

  • Applies to: ✅ Apache Cassandra

APPLIES TO: Cassandra

Important

Are you looking for a database solution for high-scale scenarios with a 99.999% availability service level agreement (SLA), instant autoscale, and automatic failover across multiple regions? Consider Azure Cosmos DB for NoSQL.

Are you looking to migrate an existing Apache Cassandra application? Consider Azure Managed Instance for Apache Cassandra.

The API for Cassandra in Azure Cosmos DB uses the underlying indexing infrastructure to expose the indexing capabilities that are inherent in the platform. However, unlike the core API for NoSQL, API for Cassandra in Azure Cosmos DB doesn't index all attributes by default. Instead, it supports secondary indexing to create an index on certain attributes, which works the same way as Apache Cassandra.

In general, avoid running filter queries on columns that aren't partitioned. You need to use the ALLOW FILTERING syntax explicitly, which results in an operation that might not perform well. In Azure Cosmos DB, you can run such queries on low cardinality attributes because they fan out across partitions to retrieve the results.

We don't recommend creating an index on a frequently updated column. Instead, create an index when you define the table. This approach ensures that data and indexes are in a consistent state. If you create a new index on existing data, you currently can't track the index progress change for the table. If you need to track the progress for this operation, request the progress change via a support ticket.

Note

You can create secondary indexes only by using the CQL commands described in this article. You can't create secondary indexes by using the Resource Provider utilities (ARM templates, Azure CLI, PowerShell, or Terraform). Secondary indexes aren't supported on the following objects:

  • Data types, such as frozen collection types, decimal, and variant types.
  • Static columns
  • Clustering keys

Warning

API for Cassandra doesn't index partition keys by default. If your table has a compound primary key, and you filter either on partition key and clustering key, or just partition key, you get the desired behavior. However, if you filter on partition key and any other non-indexed fields aside from the clustering key, this filter causes a partition key fan-out - even if the other non-indexed fields have a secondary index. If your table has a compound primary key, and you want to filter on both the partition key value element of the compound primary key, plus another field that isn't the partition key or clustering key, explicitly add a secondary index on the partition key. The index in this scenario should significantly improve query performance, even if the other non-partition key and non-clustering key fields have no index. For more information, see partitioning.

Indexing example

First, create a sample keyspace and table by running the following commands on the CQL shell prompt:

CREATE KEYSPACE sampleks WITH REPLICATION = {'class' : 'SimpleStrategy'};
CREATE TABLE sampleks.t1(user_id int PRIMARY KEY, lastname text) WITH cosmosdb_provisioned_throughput=400;

Then, insert sample user data by using the following commands:

insert into sampleks.t1(user_id,lastname) values (1, 'nishu');
insert into sampleks.t1(user_id,lastname) values (2, 'vinod');
insert into sampleks.t1(user_id,lastname) values (3, 'bat');
insert into sampleks.t1(user_id,lastname) values (5, 'vivek');
insert into sampleks.t1(user_id,lastname) values (6, 'siddhesh');
insert into sampleks.t1(user_id,lastname) values (7, 'akash');
insert into sampleks.t1(user_id,lastname) values (8, 'Theo');
insert into sampleks.t1(user_id,lastname) values (9, 'jagan');

If you try running the following statement, you get an error that asks you to use ALLOW FILTERING:

select user_id, lastname from sampleks.t1 where lastname='nishu';

Although the API for Cassandra supports ALLOW FILTERING, as mentioned in the previous section, we don't recommend that you use it. Instead, create an index as shown in the following example:

CREATE INDEX ON sampleks.t1 (lastname);

After creating an index on the lastname field, you can run the previous query successfully. When you use API for Cassandra in Azure Cosmos DB, you don't have to provide an index name. A default index with the format tablename_columnname_idx is used. For example, t1_lastname_idx is the index name for the previous table.

Dropping the index

You need to know the index name to drop the index. Run the desc schema command to get the description of your table. The output of this command includes the index name in the format CREATE INDEX tablename_columnname_idx ON keyspacename.tablename(columnname). You can then use the index name to drop the index, as shown in the following example:

drop index sampleks.t1_lastname_idx;

Next steps

  • Last updated on