Create an index
Tables can contain partition keys and clustering keys, both of which define the primary key. A table can also include non-primary keys.
If you wish to create a table query that uses anything other than the partition key to define which row or rows are to be retrieved, a column index must be created on each column to read the data.
Send a POST
request to
/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes
to create an index.
Set the index definitions in the JSON body in the --data
. In this example,
an index name is defined as fav_books_idx
for the column favorite_books
in
the keyspace users_keyspace
and table users
.
An option to only create the index if it already exists is set with ifNotExists: true
;
the default is false
.
Two additional options are available:
-
type
can be defined to use SAI indexes if desired; the default is secondary indexes. -
kind
defines the kind of index for map collections; the choices available areVALUES/KEYS/ENTRIES/FULL
.
curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
"column": "favorite_books",
"name": "fav_books_idx",
"ifNotExists": true
}'
{
"success": true
}
Here is an additional example, which creates indexes that are used in the REST API examples:
curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
"column": "favorite_books",
"name": "fav_books_idex",
"ifNotExists": true
}'
curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
"column": "top_three_tv_shows",
"name": "tv_idx",
"ifNotExists": true
}'
curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
"column": "evaluations",
"name": "evalv_idx",
"ifNotExists": true
}'
curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
"column": "evaluations",
"name": "evalk_idx",
"kind": "KEYS",
"ifNotExists": true
}'
curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
"column": "evaluations",
"name": "evale_idx",
"kind": "ENTRIES",
"ifNotExists": true
}'
curl -s --location \
--request POST https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/indexes \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
"column": "current_country",
"name": "country_idx",
"ifNotExists": true
}'
{
"success": true
}
{
"success": true
}
{
"success": true
}
{
"success": true
}
{
"success": true
}
{
"success": true
}
Note the use of the kind
in the definitions used for the map collection evaluations
.