Work with table indexes

This Astra DB Serverless feature is currently in public preview. Development is ongoing, and the features and functionality are subject to change. Astra DB Serverless, and the use of such, is subject to the DataStax Preview Terms.

Table indexes allow you search on columns outside the table’s primary key.

Creating a vector index on a vector column allows you to perform vector similarity searches on the table. Creating regular indexes on non-vector columns allows you to run normal queries against those columns.

Querying a non-indexed column either fails or triggers allow filtering, which is the least efficient way to query a table. Allow filtering can be slow and expensive, especially on large tables.

In contrast, querying on the primary key is the most efficient way to search. Indexes are not as performant as primary key queries, but they are much more performant than allow filtering.

You can use the Data API to manage indexes for fixed schema tables. For more information about creating and managing tables with the Data API, see Work with tables. For information about indexing in dynamic schema collections, see The indexing option.

Prerequisites

Required client version

The Data API tables commands are available through HTTP and the clients. If you use a client, tables commands are available only in client versions 2.0-preview or later. For more information, see Data API client upgrade guide.

Create an index

Create a new index for a table in a Serverless (Vector) database.

Index names must be unique within a keyspace.

When you create an index, you specify the table and column to index so that the correct data is indexed. However, when you drop an index, you only need to specify the index name.

With the clients, you use Table methods to create and list indexes, but you use a Database method to drop indexes.

To manage indexes, your application token must have the same level of permissions that you need to manage tables.

  • Python

  • TypeScript

  • Java

  • curl

For more information, see the Client reference.

Create an index on a table column:

my_table.create_index("score_index", column="score")

Create an index on a text or ascii column with options:

my_table.create_index(
    "winner_index", column="winner",
    options=TableIndexOptions(
        ascii=False, normalize=True, case_sensitive=False,
    ),
)

Parameters:

Name Type Summary

name

str

The name of the index.

Index names must be unique within a keyspace.

column

str

The name of the table column on which to create the index.

You cannot create indexes based on map, list, or set columns. To create vector indexes based on vector columns, see Create a vector index.

options

TableIndexOptions | dict | None

Specifies Index options for text and ascii types. If passed, it must be an instance of TableIndexOptions or an equivalent dictionary.

if_not_exists

bool | None

If True, and an index with the given name already exists in the keyspace, then the command succeeds and silently does nothing. In this case, no actual index creation takes place on the database.

If False (default), an error occurs if an index with the specified name already exists.

if_not_exists: True, does not check the type or content of any existing indexes. This parameter checks index names only.

This means that the command succeeds if the given index name is already in use, even if the type or indexed column is different.

table_admin_timeout_ms

int | None

A timeout, in milliseconds, to impose on the underlying API request. If not provided, the Table defaults apply. This parameter is aliased as request_timeout_ms and timeout_ms for convenience.

Returns:

None

Example:

Full script 
from astrapy import DataAPIClient
client = DataAPIClient("TOKEN")
database = client.get_database("API_ENDPOINT")

from astrapy.constants import SortMode
from astrapy.info import (
    CreateTableDefinition,
    ColumnType,
)

my_table = database.create_table(
    "games",
    definition=(
        CreateTableDefinition.builder()
        .add_column("match_id", ColumnType.TEXT)
        .add_column("round", ColumnType.TINYINT)
        .add_vector_column("m_vector", dimension=3)
        .add_column("score", ColumnType.INT)
        .add_column("when", ColumnType.TIMESTAMP)
        .add_column("winner", ColumnType.TEXT)
        .add_set_column("fighters", ColumnType.UUID)
        .add_partition_by(["match_id"])
        .add_partition_sort({"round": SortMode.ASCENDING})
        .build()
    ),
)

from astrapy.info import TableIndexOptions

# create an index on a column
my_table.create_index(
    "score_index",
    column="score",
)

# create an index on a textual column, specifying indexing options
my_table.create_index(
    "winner_index",
    column="winner",
    options=TableIndexOptions(
        ascii=False,
        normalize=True,
        case_sensitive=False,
    ),
)
from astrapy.info import TableIndexOptions

# create an index on a column
my_table.create_index(
    "score_index",
    column="score",
)

# create an index on a textual column, specifying indexing options
my_table.create_index(
    "winner_index",
    column="winner",
    options=TableIndexOptions(
        ascii=False,
        normalize=True,
        case_sensitive=False,
    ),
)

For more information, see the Client reference.

Create an index on a table column:

await table.createIndex('score_idx', 'score');

By default, an error occurs if an index with the given name already exists in the keyspace.

To silently ignore existing indexes, use ifNotExists: true. If true, an index is created if there is no name collision. If an index with the given name already exists, the command silently does nothing (neither creates an index nor throws an error).

await table.createIndex('winner_idx', 'winner', {
  ifNotExists: true,
});

Create an index on a text or ascii column with options:

await table.createIndex('winner_idx', 'winner', {
  options: {
    ascii: true,
    normalize: true,
    caseSensitive: false,
  },
});

Parameters:

Name Type Summary

name

string

The name of the index.

Index names must be unique within a keyspace.

column

string

The name of the table column on which to create the index.

You cannot create indexes based on map, list, or set columns. To create vector indexes based on vector columns, see Create a vector index.

An error occurs if the given column is already indexed or isn’t an indexable type.

options?

TableCreateIndexOptions

The options for this operation.

Options (TableCreateIndexOptions):

Name Type Summary

ifNotExists?

boolean

If true, and an index with the given name already exists in the keyspace, then the command succeeds and silently does nothing. In this case, no actual index creation takes place on the database.

If false (default), an error occurs if an index with the specified name already exists.

ifNotExists: true, does not check the type or content of any existing indexes. This parameter checks index names only.

This means that the command succeeds if the given index name is already in use, even if the type or indexed column is different.

options.ascii?

boolean

Whether to convert non-ASCII characters to their US-ASCII equivalent before indexing. The default is false. See Index options for text and ascii types.

options.normalize?

boolean

Whether to normalize Unicode characters and diacritics before indexing. The default is false. See Index options for text and ascii types.

options.caseSensitive?

boolean

Whether the index is case sensitive. The default is true. See Index options for text and ascii types.

timeout?

number | TimeoutDescriptor

The client-side timeout for this operation.

Returns:

void - A promise which resolves when the operation is complete.

Example:

Full script 
import { CreateTableDefinition, DataAPIClient, SomeRow } from '@datastax/astra-db-ts';

// Instantiate the client and connect to the database
const client = new DataAPIClient();
const db = client.db(process.env.CLIENT_DB_URL!, { token: process.env.CLIENT_DB_TOKEN! });

// Create table schema using bespoke Data API table definition syntax.
// For information about table definition and data types, see the documentation for createTable.
const TableDefinition = <const>{
  columns: {
    matchId: 'text'
    round: 'tinyint',
    mVector: { type: 'vector', dimension: 3 },
    score: 'int',
    when: 'timestamp',
    winner: 'text',
    fighters: { type: 'set', valueType: 'uuid' },
  },
  primaryKey: {
    partitionBy: ['matchId'],
    partitionSort: { round: 1 },
  },
} satisfies CreateTableDefinition;

(async function () {
    // Create an untyped table if a 'games' table doesn't already exist
  const table = await db.createTable<SomeRow>('games', { definition: TableDefinition, ifNotExists: true });

  // Create a secondary index on the 'score' column with default options.
  // Errors if a 'score_idx' index already exists in the working keyspace.
  await table.createIndex('score_idx', 'score');

  // Create a secondary index on the 'winner' column with case-insensitivity
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'winner_idx'.
  await table.createIndex('winner_idx', 'winner', {
    options: {
      caseSensitive: false,
    },
    ifNotExists: true,
  });

  // Case insensitive indexes ignore case when querying.
  // Insert a row with upper case and lower case characters,
  // and then query the row.
  // findOne returns a match because 'winner_idx' is case-insensitive.
  await table.insertOne({ matchId: '01', round: 0, winner: 'Gray Tist' });
  await table.findOne({ winner: 'gray tist' }).then(console.log);

  // Create a vector index on the 'mVector' column with cosine similarity (default).
  // Errors if an 'm_vector_idx' index already exists in the working keyspace.
  await table.createVectorIndex('m_vector_idx', 'mVector');

  // Create a vector index on the 'mVector' column with dot-product similarity.
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'm_vector_idx'.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
    },
    ifNotExists: true,
  });

  // Drop the index so you can recreate it with different options.
  await db.dropTableIndex('m_vector_idx');

  // Create the vector index with dot-product similarity and a source model.
  // For accurate searches, use a source model and metric that are compatible with your vectors.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
      sourceModel: 'ada002',
    },
  });

  // Vector indexes allow you to perform vector ANN searches.
  // Insert a row with a vector, and then run a vector search on the table.
  await table.insertOne({ matchId: '01', round: 0, mVector: [0.2, -0.3, -0.5] });
  await table.findOne({}, { sort: { mVector: [0.2, -0.3, -0.5] } }).then(console.log);

  // Get detailed information about the indexes
  // Returns information like [{ name: 'score_idx', definition: { column: 'score', options: {} } }, ...]
  await table.listIndexes().then(console.log);

  // Get index names only.
  // Returns information like ['score_idx', 'winner_idx', 'm_vector_idx', ...]
  await table.listIndexes({ nameOnly: true }).then(console.log);

  // Drop an index from a database's working keyspace without checking if the index exists.
  // If there is no match, the command succeeds but does nothing.
  // If there is a match, the named index is deleted.
  await db.dropTableIndex('score_idx');

  // Drop an index from a database's working keyspace if the index exists.
  // Errors if there is no match.
  await db.dropTableIndex('score_idx', { ifExists: true });

  // Uncomment the following line to drop the table and any related indexes.
  // await table.drop();
})();
// Create a secondary index on the 'score' column with default options.
// Errors if a 'score_idx' index already exists in the working keyspace.
await table.createIndex('score_idx', 'score');

// Create a secondary index on the 'winner' column with case-insensitivity
// Because 'ifNotExists: true', the command does not throw an error
// if the working keyspace already has an index named 'winner_idx'.
await table.createIndex('winner_idx', 'winner', {
  options: {
    caseSensitive: false,
  },
  ifNotExists: true,
});

// Case insensitive indexes ignore case when querying.
// Insert a row with upper case and lower case characters,
// and then query the row.
// findOne returns a match because 'winner_idx' is case-insensitive.
await table.insertOne({ matchId: '01', round: 0, winner: 'Gray Tist' });
await table.findOne({ winner: 'gray tist' }).then(console.log);

For more information, see the Client reference.

Create an index on a table column:

// Expects index name and the column to index
tableGames.createIndex("score_index","score");

Create an index on a text or ascii column with options:

TableIndexDefinition definition = new TableIndexDefinition()
 .column("winner")
 .ascii(false)
 .caseSensitive(true)
 .normalize(false);

CreateIndexOptions options = new CreateIndexOptions()
  .ifNotExists(true)
  .timeout(Duration.ofSeconds(2));

tableGames
  .createIndex("winner_index", definition, options);

Parameters:

Name Type Summary

name

str

The name of the index.

Index names must be unique within a keyspace.

definition

TableIndexDefinition

Definition of the index to create. Requires the name of the column to index. For text and ascii columns, you can specify index options.

You cannot create indexes based on map, list, or set columns. To create vector indexes based on vector columns, see Create a vector index.

options

CreateIndexOptions

A specialization of index creation options, including ifNotExists and timeout.

If ifNotExists(true), and an index with the given name already exists in the keyspace, then the command succeeds and silently does nothing. In this case, no actual index creation takes place on the database.

If ifNotExists(false) (default), an error occurs if an index with the specified name already exists.

ifNotExists(true), does not check the type or content of any existing indexes. This parameter checks index names only.

This means that the command succeeds if the given index name is already in use, even if the type or indexed column is different.

Example:

package com.datastax.astra.client.tables;

import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.DataAPIClients;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.commands.options.CreateIndexOptions;
import com.datastax.astra.client.tables.definition.indexes.TableIndexDefinition;
import com.datastax.astra.client.tables.definition.rows.Row;

import java.time.Duration;

public class CreateIndex {
 public static void main(String[] args) {
   Database db = new DataAPIClient("token").getDatabase("endpoint");

   Table<Row> tableGames = db.getTable("games");

   tableGames.createIndex("score_index","score");

   TableIndexDefinition definition = new TableIndexDefinition()
     .column("winner")
     .ascii(true)  // only text or ascii
     .caseSensitive(true)
     .normalize(true);

   CreateIndexOptions options = new CreateIndexOptions()
     .ifNotExists(true)
     .timeout(Duration.ofSeconds(2));
   tableGames.createIndex("winner_index", definition, options);
 }
}

Create an index:

curl -sS --location -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/ASTRA_DB_KEYSPACE/ASTRA_DB_TABLE" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "createIndex": {
    "name": "INDEX_NAME",
    "definition": {
      "column": "COLUMN_NAME",
      "options": {
        "normalize": true,
        "caseSensitive": false
      }
    }
  }
}'

Parameters:

Name Type Summary

createIndex

command

The Data API command to create an index for a table in a Serverless (Vector) database. It acts as a container for all the attributes and settings required to create the index.

name

string

The name of the index.

Index names must be unique within a keyspace.

definition

object

Contains the column and options for the index.

definition.column

string

The name of the table column on which to create the index.

You cannot create indexes based on map, list, or set columns. To create vector indexes based on vector columns, see Create a vector index.

definition.options.ascii

boolean

Whether to convert non-ASCII characters to their US-ASCII equivalent before indexing. The default is false. See Index options for text and ascii types.

definition.options.normalize

boolean

Whether to normalize Unicode characters and diacritics before indexing. The default is false. See Index options for text and ascii types.

definition.options.caseSensitive

boolean

Whether the index is case sensitive. The default is true. See Index options for text and ascii types.

Returns:

A successful request returns 200 OK.

Example response 
{
  "status": {
    "ok": 1
  }
}

Examples:

curl -sS --location -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/default_keyspace/students" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "createIndex": {
    "name": "index_metadata_students",
    "definition": {
      "column": "student_id",
      "options": {
        "ascii": true,
        "caseSensitive": false
      }
    }
  }
}'

Index options for text and ascii types

When creating an index on a text or ascii column, you can specify the following options:

  • ASCII: Whether to convert non-ASCII characters to their US-ASCII equivalent before indexing. The default is false.

    This option can improve search performance by limiting the range of allowed ASCII characters. This option is best for ASCII-only data or ASCII-only applications where you can safely ignore non-ASCII characters. It is not recommended for multilingual data or applications that must support non-ASCII characters.

    If true, then alphabetic, numeric, and symbolic characters that are not in the Basic Latin Unicode block (the first 127 ASCII characters) are converted to the ASCII equivalent, if one exists. For example, à is indexed as a, and searches using a can match à. Characters with no equivalent are ignored.

  • Normalize: Whether to normalize Unicode characters and diacritics before indexing. The default is false.

    This option can improve search consistency and inclusivity by normalizing Unicode characters, such as accented characters and diacritical marks that have multiple Unicode representations. This option is best for data sets that can contain multiple Unicode versions of the same character, such as multilingual data or non-standardized user input.

    If true, multiple versions of a Unicode character are normalized to a single version while retaining all marks and symbols in the index. For example, Å (U+212B) normalizes to Å (U+00C5).

  • Case sensitive: Whether the index is case sensitive. The default is true.

    This option enforces case sensitivity in searches. It is best for data sets where you want to enforce exact matches based on capitalization.

    If true, indexing and searches are case sensitive and honor capitalization. If false, indexing and searches are case insensitive and ignore capitalization.

Create a vector index

Create a new vector index for a table in a Serverless (Vector) database.

Vector indexes are required to perform similarity searches on vector data in tables. For more information about working with vector data in tables, see Vector type and Find rows reference.

  • Python

  • TypeScript

  • Java

  • curl

For more information, see the Client reference.

Create a vector index with default settings:

my_table.create_vector_index("m_vector_index", column="m_vector")

Create a vector index and specify the similarity metric for vector searches on this index:

my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    options=TableVectorIndexOptions(
        metric=VectorMetric.DOT_PRODUCT,
    ),
)

Parameters:

Name Type Summary

name

str

The name of the index.

Index names must be unique within a keyspace.

column

str

The name of the table column on which to create the index. The column must be of type vector.

To create indexes on non-vector columns, see Create an index.

options

TableVectorIndexOptions | dict | None

Specifies index options:

  • metric: One of the values in astrapy.constants.VectorMetric selects a similarity metric, such as COSINE (default), DOT_PRODUCT, or EUCLIDEAN.

  • source_model: Enable certain vector optimizations on the index by specifying the source model for your vectors, such as 'openai_v3_large', 'openai_v3_small', 'ada002', 'gecko', 'bert', or 'other' (default). If a model is specified, the index automatically uses the parameters best suited to that embedding model.

If passed, it must be an instance of TableVectorIndexOptions or an equivalent dictionary.

if_not_exists

bool | None

If True, and an index with the given name already exists in the keyspace, then the command succeeds and silently does nothing. In this case, no actual index creation takes place on the database.

If False (default), an error occurs if an index with the specified name already exists.

ifNotExists: True, does not check the type or content of any existing indexes. This parameter checks index names only.

This means that the command succeeds if the given index name is already in use, even if the type or indexed column is different.

table_admin_timeout_ms

int | None

A timeout, in milliseconds, to impose on the underlying API request. If not provided, the Table defaults apply. This parameter is aliased as request_timeout_ms and timeout_ms for convenience.

Returns:

None

Example:

Full script 
from astrapy import DataAPIClient
client = DataAPIClient("TOKEN")
database = client.get_database("API_ENDPOINT")

from astrapy.constants import SortMode
from astrapy.info import (
    CreateTableDefinition,
    ColumnType,
)

my_table = database.create_table(
    "games",
    definition=(
        CreateTableDefinition.builder()
        .add_column("match_id", ColumnType.TEXT)
        .add_column("round", ColumnType.TINYINT)
        .add_vector_column("m_vector", dimension=3)
        .add_column("score", ColumnType.INT)
        .add_column("when", ColumnType.TIMESTAMP)
        .add_column("winner", ColumnType.TEXT)
        .add_set_column("fighters", ColumnType.UUID)
        .add_partition_by(["match_id"])
        .add_partition_sort({"round": SortMode.ASCENDING})
        .build()
    ),
)

from astrapy.constants import VectorMetric
from astrapy.info import TableVectorIndexOptions

# create a vector index with dot-product similarity
my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    options=TableVectorIndexOptions(
        metric=VectorMetric.DOT_PRODUCT,
    ),
)

# specify a source_model (since the previous statement
# succeeded, this will do nothing because of if_not_exists):
my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    options=TableVectorIndexOptions(
        metric=VectorMetric.DOT_PRODUCT,
        source_model="nv-qa-4",
    ),
    if_not_exists=True,
)

# leave the settings to the Data API defaults of cosine
# similarity metric (since the previous statement
# succeeded, this will do nothing because of if_not_exists):
my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    if_not_exists=True,
)
from astrapy.constants import VectorMetric
from astrapy.info import TableVectorIndexOptions

# create a vector index with dot-product similarity
my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    options=TableVectorIndexOptions(
        metric=VectorMetric.DOT_PRODUCT,
    ),
)

# specify a source_model (since the previous statement
# succeeded, this will do nothing because of if_not_exists):
my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    options=TableVectorIndexOptions(
        metric=VectorMetric.DOT_PRODUCT,
        source_model="nv-qa-4",
    ),
    if_not_exists=True,
)

# leave the settings to the Data API defaults of cosine
# similarity metric (since the previous statement
# succeeded, this will do nothing because of if_not_exists):
my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    if_not_exists=True,
)

For more information, see the Client reference.

Create a vector index with default settings:

await table.createVectorIndex('m_vector_idx', 'm_vector');

By default, an error occurs if an index with the given name already exists in the keyspace.

To silently ignore existing indexes, use ifNotExists: true. If true, an index is created if there is no name collision. If an index with the given name already exists, the command silently does nothing (neither creates an index nor throws an error).

await table.createVectorIndex('m_vector_idx', 'm_vector', {
  ifNotExists: true,
});

Create a vector index and specify the similarity metric for vector searches on this index:

await table.createVectorIndex('m_vector_idx', 'm_vector', {
  options: {
    metric: 'dot_product',
  },
});

Parameters:

Name Type Summary

name

string

The name of the index.

Index names must be unique within a keyspace.

column

string

The name of the table column on which to create the index. The column must be of type vector.

To create indexes on non-vector columns, see Create an index.

options?

TableCreateVectorIndexOptions

The options for this operation.

Options (TableCreateVectorIndexOptions):

Name Type Summary

ifNotExists?

boolean

If true, and an index with the given name already exists in the keyspace, then the command succeeds and silently does nothing. In this case, no actual index creation takes place on the database.

If false (default), an error occurs if an index with the specified name already exists.

ifNotExists: true, does not check the type or content of any existing indexes. This parameter checks index names only.

This means that the command succeeds if the given index name is already in use, even if the type or indexed column is different.

options.metric?

string

The similarity metric to use for vector search, one of 'cosine' (default), 'dot_product', or 'euclidean'.

options.sourceModel?

string

Enable certain vector optimizations on the index by specifying the source model for your vectors, such as 'openai_v3_large', 'openai_v3_small', 'ada002', 'gecko', 'bert', or 'other' (default).

timeout?

number | TimeoutDescriptor

The client-side timeout for this operation.

Returns:

void - A promise which resolves when the operation is complete.

Example:

Full script 
import { CreateTableDefinition, DataAPIClient, SomeRow } from '@datastax/astra-db-ts';

// Instantiate the client and connect to the database
const client = new DataAPIClient();
const db = client.db(process.env.CLIENT_DB_URL!, { token: process.env.CLIENT_DB_TOKEN! });

// Create table schema using bespoke Data API table definition syntax.
// For information about table definition and data types, see the documentation for createTable.
const TableDefinition = <const>{
  columns: {
    matchId: 'text'
    round: 'tinyint',
    mVector: { type: 'vector', dimension: 3 },
    score: 'int',
    when: 'timestamp',
    winner: 'text',
    fighters: { type: 'set', valueType: 'uuid' },
  },
  primaryKey: {
    partitionBy: ['matchId'],
    partitionSort: { round: 1 },
  },
} satisfies CreateTableDefinition;

(async function () {
    // Create an untyped table if a 'games' table doesn't already exist
  const table = await db.createTable<SomeRow>('games', { definition: TableDefinition, ifNotExists: true });

  // Create a secondary index on the 'score' column with default options.
  // Errors if a 'score_idx' index already exists in the working keyspace.
  await table.createIndex('score_idx', 'score');

  // Create a secondary index on the 'winner' column with case-insensitivity
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'winner_idx'.
  await table.createIndex('winner_idx', 'winner', {
    options: {
      caseSensitive: false,
    },
    ifNotExists: true,
  });

  // Case insensitive indexes ignore case when querying.
  // Insert a row with upper case and lower case characters,
  // and then query the row.
  // findOne returns a match because 'winner_idx' is case-insensitive.
  await table.insertOne({ matchId: '01', round: 0, winner: 'Gray Tist' });
  await table.findOne({ winner: 'gray tist' }).then(console.log);

  // Create a vector index on the 'mVector' column with cosine similarity (default).
  // Errors if an 'm_vector_idx' index already exists in the working keyspace.
  await table.createVectorIndex('m_vector_idx', 'mVector');

  // Create a vector index on the 'mVector' column with dot-product similarity.
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'm_vector_idx'.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
    },
    ifNotExists: true,
  });

  // Drop the index so you can recreate it with different options.
  await db.dropTableIndex('m_vector_idx');

  // Create the vector index with dot-product similarity and a source model.
  // For accurate searches, use a source model and metric that are compatible with your vectors.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
      sourceModel: 'ada002',
    },
  });

  // Vector indexes allow you to perform vector ANN searches.
  // Insert a row with a vector, and then run a vector search on the table.
  await table.insertOne({ matchId: '01', round: 0, mVector: [0.2, -0.3, -0.5] });
  await table.findOne({}, { sort: { mVector: [0.2, -0.3, -0.5] } }).then(console.log);

  // Get detailed information about the indexes
  // Returns information like [{ name: 'score_idx', definition: { column: 'score', options: {} } }, ...]
  await table.listIndexes().then(console.log);

  // Get index names only.
  // Returns information like ['score_idx', 'winner_idx', 'm_vector_idx', ...]
  await table.listIndexes({ nameOnly: true }).then(console.log);

  // Drop an index from a database's working keyspace without checking if the index exists.
  // If there is no match, the command succeeds but does nothing.
  // If there is a match, the named index is deleted.
  await db.dropTableIndex('score_idx');

  // Drop an index from a database's working keyspace if the index exists.
  // Errors if there is no match.
  await db.dropTableIndex('score_idx', { ifExists: true });

  // Uncomment the following line to drop the table and any related indexes.
  // await table.drop();
})();
// Create a vector index on the 'mVector' column with cosine similarity (default).
// Errors if an 'm_vector_idx' index already exists in the working keyspace.
await table.createVectorIndex('m_vector_idx', 'mVector');

// Create a vector index on the 'mVector' column with dot-product similarity.
// Because 'ifNotExists: true', the command does not throw an error
// if the working keyspace already has an index named 'm_vector_idx'.
await table.createVectorIndex('m_vector_idx', 'mVector', {
  options: {
    metric: 'dot_product',
  },
  ifNotExists: true,
});

// Drop the index so you can recreate it with different options.
await db.dropTableIndex('m_vector_idx');

// Create the vector index with dot-product similarity and a source model.
// For accurate searches, use a source model and metric that are compatible with your vectors.
await table.createVectorIndex('m_vector_idx', 'mVector', {
  options: {
    metric: 'dot_product',
    sourceModel: 'ada002',
  },
});

// Vector indexes allow you to perform vector ANN searches.
// Insert a row with a vector, and then run a vector search on the table.
await table.insertOne({ matchId: '01', round: 0, mVector: [0.2, -0.3, -0.5] });
await table.findOne({}, { sort: { mVector: [0.2, -0.3, -0.5] } }).then(console.log);

For more information, see the Client reference.

Create a vector index with default settings:

// Expects index name and the column to index
tableGames
  .createVectorIndex("m_vector_index", "m_vector");

Create a vector index and specify the embedding source model and the similarity metric for vector searches on this index:

TableVectorIndexDefinition definition = new TableVectorIndexDefinition()
  .column("m_vector")
  .metric(SimilarityMetric.COSINE)
  .sourceModel("openai-v3-large");

CreateVectorIndexOptions options = new CreateVectorIndexOptions()
  .ifNotExists(true)
  .timeout(Duration.ofSeconds(2));

tableGames
  .createVectorIndex("m_vector_index", definition, options);

Parameters:

Name Type Summary

name

str

The name of the index.

Index names must be unique within a keyspace.

definition

TableVectorIndexDefinition

Definition of the index to create.

Requires the name of the column to index. The column must be of type vector. To create indexes on non-vector columns, see Create an index.

Optionally, you can specify the similarity metric and source model:

  • metric(SimilarityMetric.METRIC): The similarity metric to use for vector search, one of COSINE (default), DOT_PRODUCT, or EUCLIDEAN.

  • sourceModel(): Enable certain vector optimizations on the index by specifying the source model for your vectors, such as "openai_v3_large", "openai_v3_small", "ada002", "gecko", "bert", or "other" (default).

options

CreateVectorIndexOptions

A specialization of index creation options, including ifNotExists and timeout.

If ifNotExists(true), and an index with the given name already exists in the keyspace, then the command succeeds and silently does nothing. In this case, no actual index creation takes place on the database.

If ifNotExists(false) (default), an error occurs if an index with the specified name already exists.

ifNotExists(true), does not check the type or content of any existing indexes. This parameter checks index names only.

This means that the command succeeds if the given index name is already in use, even if the type or indexed column is different.

Example:

package com.datastax.astra.client.tables;

import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.DataAPIClients;
import com.datastax.astra.client.core.vector.SimilarityMetric;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.commands.options.CreateIndexOptions;
import com.datastax.astra.client.tables.commands.options.CreateVectorIndexOptions;
import com.datastax.astra.client.tables.definition.indexes.TableIndexDefinition;
import com.datastax.astra.client.tables.definition.indexes.TableVectorIndexDefinition;
import com.datastax.astra.client.tables.definition.rows.Row;

import java.time.Duration;

public class CreateVectorIndex {
 public static void main(String[] args) {
   Database db = new DataAPIClient("token").getDatabase("endpoint");
   Table<Row> tableGames = db.getTable("games");

   //tableGames.createVectorIndex("m_vector_index", "m_vector");

   TableVectorIndexDefinition definition = new TableVectorIndexDefinition()
     .column("m_vector")
     .metric(SimilarityMetric.COSINE)
     .sourceModel("openai-v3-large");

   CreateVectorIndexOptions options = new CreateVectorIndexOptions()
     .ifNotExists(true)
     .timeout(Duration.ofSeconds(2));

   tableGames.createVectorIndex("m_vector_index", definition, options);
 }
}

Create a vector index with default options:

curl -sS --location -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/ASTRA_DB_KEYSPACE/ASTRA_DB_TABLE" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "createVectorIndex": {
    "name": "INDEX_NAME",
    "definition": {
      "column": "VECTOR_COLUMN_NAME"
    }
  }
}'

Create a vector index and specify the similarity metric for vector searches on this index:

curl -sS --location -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/ASTRA_DB_KEYSPACE/ASTRA_DB_TABLE" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "createVectorIndex": {
    "name": "INDEX_NAME",
    "definition": {
      "column": "VECTOR_COLUMN_NAME",
      "options": {
        "metric": "SIMILARITY_METRIC"
      }
    }
  }
}'

Parameters:

Name Type Summary

createVectorIndex

command

The Data API command to create a vector index for a table in a Serverless (Vector) database. It acts as a container for all the attributes and settings required to create the index.

name

string

The name of the index.

Index names must be unique within a keyspace.

definition

object

Contains column and options for the vector index.

definition.column

string

The name of the table column on which to create the index. The column must be of type vector.

To create indexes on non-vector columns, see Create an index.

definition.options

object

Contains either, both, or none of the vector index options:

  • metric: The similarity metric to use for vector searches on this index, one of cosine (default), dot_product, or euclidean.

  • sourceModel: Enable certain vector optimizations on the index by specifying the source model for your vectors, such as openai_v3_large, openai_v3_small, ada002, gecko, bert, or other (default).

Returns:

A successful request returns 200 OK.

Example response 
{
  "status": {
    "ok": 1
  }
}

Examples:

curl -sS --location -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/default_keyspace/students" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "createVectorIndex": {
    "name": "index_vector_students",
    "definition": {
      "column": "vect_emb",
      "options": {
        "sourceModel": "ada002"
      }
    }
  }
}'

List index metadata

Get information about the indexes associated with a specific table.

  • Python

  • TypeScript

  • Java

  • curl

For more information, see the Client reference.

List the details of all indexes on a table:

my_table.list_indexes()

To get index names only, see List index names.

Parameters:

Name Type Summary

table_admin_timeout_ms

int | None

A timeout, in milliseconds, to impose on the underlying API request. If not provided, the Table defaults apply. This parameter is aliased as request_timeout_ms and timeout_ms for convenience.

Returns:

list[TableIndexDescriptor] - A list of TableIndexDescriptor objects, each detailing the properties and settings of an index on the table, in no particular order.

Example response (reformatted for clarity) 
[
  TableIndexDescriptor(
    name='m_vector_index',
    definition=TableVectorIndexDefinition(
      m_vector,
      options=TableVectorIndexOptions(metric=dot_product, source_model=other)
    )
  ),
  TableIndexDescriptor(
    name='winner_index',
    definition=TableIndexDefinition(
      winner,
      options=TableIndexOptions(
        ascii=False, normalize=True, case_sensitive=False
      )
    )
  ),
  TableIndexDescriptor(
    name='score_index',
    definition=TableIndexDefinition(
      score,
      options=TableIndexOptions()
    )
  )
]

Example:

Full script 
from astrapy import DataAPIClient
client = DataAPIClient("TOKEN")
database = client.get_database("API_ENDPOINT")

from astrapy.constants import SortMode
from astrapy.info import (
    CreateTableDefinition,
    ColumnType,
)

my_table = database.create_table(
    "games",
    definition=(
        CreateTableDefinition.builder()
        .add_column("match_id", ColumnType.TEXT)
        .add_column("round", ColumnType.TINYINT)
        .add_vector_column("m_vector", dimension=3)
        .add_column("score", ColumnType.INT)
        .add_column("when", ColumnType.TIMESTAMP)
        .add_column("winner", ColumnType.TEXT)
        .add_set_column("fighters", ColumnType.UUID)
        .add_partition_by(["match_id"])
        .add_partition_sort({"round": SortMode.ASCENDING})
        .build()
    ),
)

from astrapy.info import TableIndexOptions

my_table.create_index(
    "score_index",
    column="score",
)

my_table.create_index(
    "winner_index",
    column="winner",
    options=TableIndexOptions(
        ascii=False,
        normalize=True,
        case_sensitive=False,
    ),
)

from astrapy.constants import VectorMetric
from astrapy.info import TableVectorIndexOptions

my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    options=TableVectorIndexOptions(
        metric=VectorMetric.DOT_PRODUCT,
    ),
)

indexes = my_table.list_indexes()
indexes
# [TableIndexDescriptor(name='m_vector_index', definition=... (shortened)
indexes[1].definition.column
# 'winner'
indexes[1].definition.options.case_sensitive
# False
indexes = my_table.list_indexes()
indexes
# [TableIndexDescriptor(name='m_vector_index', definition=... (shortened)
indexes[1].definition.column
# 'winner'
indexes[1].definition.options.case_sensitive
# False

For more information, see the Client reference.

List the details of all indexes on a table:

await table.listIndexes();

Parameters:

Name Type Summary

option?

ListIndexOptions

The options for this operation

Options (ListIndexOptions):

Name Type Summary

nameOnly?

boolean

If false or undefined, the response includes index names and metadata. If true, the response includes only index names.

timeout?

number | TimeoutDescriptor

The client-side timeout for this operation.

Returns:

Promise<TableIndexDescriptor[]> - A list of TableIndexDescriptor objects, each detailing the properties/settings of an index on the table, in no particular order.

Example response 
[
  {
    name: 'score_idx',
    definition: {
      column: 'score',
      options: {}
    }
  }, {
    name: 'winner_idx',
    definition: {
      column: 'winner',
      options: { ascii: false, caseSensitive: false, normalize: true }
    }
  }, {
    name: 'm_vector_idx',
    definition: {
      column: 'UNKNOWN',
      apiSupport: {
        createIndex: false,
        filter: false,
        cqlDefinition: 'CREATE CUSTOM INDEX m_vector_idx ON default_keyspace.games ("mVector")\n' +
          "USING 'StorageAttachedIndex'\n" +
          'WITH OPTIONS = {\n' +
          "    'similarity_function' : 'DOT_PRODUCT',\n" +
          "    'source_model' : 'OTHER'}"
      }
    }
  }]

Example:

Full script 
import { CreateTableDefinition, DataAPIClient, SomeRow } from '@datastax/astra-db-ts';

// Instantiate the client and connect to the database
const client = new DataAPIClient();
const db = client.db(process.env.CLIENT_DB_URL!, { token: process.env.CLIENT_DB_TOKEN! });

// Create table schema using bespoke Data API table definition syntax.
// For information about table definition and data types, see the documentation for createTable.
const TableDefinition = <const>{
  columns: {
    matchId: 'text'
    round: 'tinyint',
    mVector: { type: 'vector', dimension: 3 },
    score: 'int',
    when: 'timestamp',
    winner: 'text',
    fighters: { type: 'set', valueType: 'uuid' },
  },
  primaryKey: {
    partitionBy: ['matchId'],
    partitionSort: { round: 1 },
  },
} satisfies CreateTableDefinition;

(async function () {
    // Create an untyped table if a 'games' table doesn't already exist
  const table = await db.createTable<SomeRow>('games', { definition: TableDefinition, ifNotExists: true });

  // Create a secondary index on the 'score' column with default options.
  // Errors if a 'score_idx' index already exists in the working keyspace.
  await table.createIndex('score_idx', 'score');

  // Create a secondary index on the 'winner' column with case-insensitivity
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'winner_idx'.
  await table.createIndex('winner_idx', 'winner', {
    options: {
      caseSensitive: false,
    },
    ifNotExists: true,
  });

  // Case insensitive indexes ignore case when querying.
  // Insert a row with upper case and lower case characters,
  // and then query the row.
  // findOne returns a match because 'winner_idx' is case-insensitive.
  await table.insertOne({ matchId: '01', round: 0, winner: 'Gray Tist' });
  await table.findOne({ winner: 'gray tist' }).then(console.log);

  // Create a vector index on the 'mVector' column with cosine similarity (default).
  // Errors if an 'm_vector_idx' index already exists in the working keyspace.
  await table.createVectorIndex('m_vector_idx', 'mVector');

  // Create a vector index on the 'mVector' column with dot-product similarity.
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'm_vector_idx'.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
    },
    ifNotExists: true,
  });

  // Drop the index so you can recreate it with different options.
  await db.dropTableIndex('m_vector_idx');

  // Create the vector index with dot-product similarity and a source model.
  // For accurate searches, use a source model and metric that are compatible with your vectors.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
      sourceModel: 'ada002',
    },
  });

  // Vector indexes allow you to perform vector ANN searches.
  // Insert a row with a vector, and then run a vector search on the table.
  await table.insertOne({ matchId: '01', round: 0, mVector: [0.2, -0.3, -0.5] });
  await table.findOne({}, { sort: { mVector: [0.2, -0.3, -0.5] } }).then(console.log);

  // Get detailed information about the indexes
  // Returns information like [{ name: 'score_idx', definition: { column: 'score', options: {} } }, ...]
  await table.listIndexes().then(console.log);

  // Get index names only.
  // Returns information like ['score_idx', 'winner_idx', 'm_vector_idx', ...]
  await table.listIndexes({ nameOnly: true }).then(console.log);

  // Drop an index from a database's working keyspace without checking if the index exists.
  // If there is no match, the command succeeds but does nothing.
  // If there is a match, the named index is deleted.
  await db.dropTableIndex('score_idx');

  // Drop an index from a database's working keyspace if the index exists.
  // Errors if there is no match.
  await db.dropTableIndex('score_idx', { ifExists: true });

  // Uncomment the following line to drop the table and any related indexes.
  // await table.drop();
})();
// Get detailed information about the indexes
// Returns information like [{ name: 'score_idx', definition: { column: 'score', options: {} } }, ...]
await table.listIndexes().then(console.log);

// Get index names only.
// Returns information like ['score_idx', 'winner_idx', 'm_vector_idx', ...]
await table.listIndexes({ nameOnly: true }).then(console.log);

For more information, see the Client reference.

List the details of all indexes on a table:

List<TableIndexDescriptor> indexes = tableGames.listIndexes();

List indexes with command options:

ListIndexesOptions options = new ListIndexesOptions()
  .timeout(Duration.ofSeconds(5));

tableGames
  .listIndexes(options)
  .forEach(idx -> {
      System.out.println(
        "Index: " + idx.getName() +
        " on column: " + idx.getDefinition().getColumn()
      );
   });

To get index names only, see List index names.

Parameters:

Name Type Summary

options

ListIndexesOptions

Specialization of index creation options.

Example:

package com.datastax.astra.client.tables;

import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.DataAPIClients;
import com.datastax.astra.client.core.vector.SimilarityMetric;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.commands.options.CreateVectorIndexOptions;
import com.datastax.astra.client.tables.commands.options.ListIndexesOptions;
import com.datastax.astra.client.tables.definition.indexes.TableIndexDefinition;
import com.datastax.astra.client.tables.definition.indexes.TableIndexDefinitionOptions;
import com.datastax.astra.client.tables.definition.indexes.TableIndexDescriptor;
import com.datastax.astra.client.tables.definition.indexes.TableVectorIndexDefinition;
import com.datastax.astra.client.tables.definition.rows.Row;

import java.time.Duration;
import java.util.List;

public class ListIndexes {
 public static void main(String[] args) {
   Database db = new DataAPIClient("token").getDatabase("endpoint");
   Table<Row> tableGames = db.getTable("games");

   //List<TableIndexDescriptor> indexes = tableGames.listIndexes();

   ListIndexesOptions options = new ListIndexesOptions()
     .timeout(Duration.ofSeconds(5));

   tableGames.listIndexes(options).forEach(idx -> {
        System.out.println("Index: " + idx.getName() + " on column: " + idx.getDefinition().getColumn());
   });
 }
}

Get details about indexes associated with a specific table:

curl -sS --location -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/ASTRA_DB_KEYSPACE/ASTRA_DB_TABLE" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "listIndexes": {
    "options": {
      "explain": true
    }
  }
}' | jq

Parameters:

Name Type Summary

listIndexes

command

The Data API command to get a list of indexes associated with a table in a Serverless (Vector) database.

options.explain

boolean

If true, the response includes index names and metadata. If false or unset, the response includes only index names.

Returns:

A successful response returns an indexes array that contains an object for each index. Each index object contains the index schema as defined when you created the index.

List index names

Get the names of the indexes associated with a specific table as a list of strings.

  • Python

  • TypeScript

  • Java

  • curl

For more information, see the Client reference.

List the names of all indexes on a table:

my_table.list_index_names()

To get details about the indexes, see List index metadata.

Parameters:

Name Type Summary

table_admin_timeout_ms

int | None

A timeout, in milliseconds, to impose on the underlying API request. If not provided, the Table defaults apply. This parameter is aliased as request_timeout_ms and timeout_ms for convenience.

Returns:

list[str] - A list of the names of all indexes on the table, in no particular order.

Example response 
['m_vector_index', 'winner_index', 'score_index']

Example:

Full script 
from astrapy import DataAPIClient
client = DataAPIClient("TOKEN")
database = client.get_database("API_ENDPOINT")

from astrapy.constants import SortMode
from astrapy.info import (
    CreateTableDefinition,
    ColumnType,
)

my_table = database.create_table(
    "games",
    definition=(
        CreateTableDefinition.builder()
        .add_column("match_id", ColumnType.TEXT)
        .add_column("round", ColumnType.TINYINT)
        .add_vector_column("m_vector", dimension=3)
        .add_column("score", ColumnType.INT)
        .add_column("when", ColumnType.TIMESTAMP)
        .add_column("winner", ColumnType.TEXT)
        .add_set_column("fighters", ColumnType.UUID)
        .add_partition_by(["match_id"])
        .add_partition_sort({"round": SortMode.ASCENDING})
        .build()
    ),
)

from astrapy.info import TableIndexOptions

my_table.create_index(
    "score_index",
    column="score",
)

my_table.create_index(
    "winner_index",
    column="winner",
    options=TableIndexOptions(
        ascii=False,
        normalize=True,
        case_sensitive=False,
    ),
)

from astrapy.constants import VectorMetric
from astrapy.info import TableVectorIndexOptions

my_table.create_vector_index(
    "m_vector_index",
    column="m_vector",
    options=TableVectorIndexOptions(
        metric=VectorMetric.DOT_PRODUCT,
    ),
)

my_table.list_index_names()
# ['m_vector_index', 'winner_index', 'score_index']
my_table.list_index_names()
# ['m_vector_index', 'winner_index', 'score_index']

For more information, see the Client reference.

List the names of all indexes on a table:

await table.listIndexes({ nameOnly: true });

Parameters:

Name Type Summary

option

ListIndexOptions

The options for this operation

Options (ListIndexOptions):

Name Type Summary

nameOnly

boolean

If true, the response includes only index names. If false or undefined, the response includes index names and metadata.

timeout?

number | TimeoutDescriptor

The client-side timeout for this operation.

Returns:

Promise<string[]> - The list of the table’s indexes' names, in no particular order.

Example response 
['score_idx', 'winner_idx', 'm_vector_idx']

Example:

Full script 
import { CreateTableDefinition, DataAPIClient, SomeRow } from '@datastax/astra-db-ts';

// Instantiate the client and connect to the database
const client = new DataAPIClient();
const db = client.db(process.env.CLIENT_DB_URL!, { token: process.env.CLIENT_DB_TOKEN! });

// Create table schema using bespoke Data API table definition syntax.
// For information about table definition and data types, see the documentation for createTable.
const TableDefinition = <const>{
  columns: {
    matchId: 'text'
    round: 'tinyint',
    mVector: { type: 'vector', dimension: 3 },
    score: 'int',
    when: 'timestamp',
    winner: 'text',
    fighters: { type: 'set', valueType: 'uuid' },
  },
  primaryKey: {
    partitionBy: ['matchId'],
    partitionSort: { round: 1 },
  },
} satisfies CreateTableDefinition;

(async function () {
    // Create an untyped table if a 'games' table doesn't already exist
  const table = await db.createTable<SomeRow>('games', { definition: TableDefinition, ifNotExists: true });

  // Create a secondary index on the 'score' column with default options.
  // Errors if a 'score_idx' index already exists in the working keyspace.
  await table.createIndex('score_idx', 'score');

  // Create a secondary index on the 'winner' column with case-insensitivity
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'winner_idx'.
  await table.createIndex('winner_idx', 'winner', {
    options: {
      caseSensitive: false,
    },
    ifNotExists: true,
  });

  // Case insensitive indexes ignore case when querying.
  // Insert a row with upper case and lower case characters,
  // and then query the row.
  // findOne returns a match because 'winner_idx' is case-insensitive.
  await table.insertOne({ matchId: '01', round: 0, winner: 'Gray Tist' });
  await table.findOne({ winner: 'gray tist' }).then(console.log);

  // Create a vector index on the 'mVector' column with cosine similarity (default).
  // Errors if an 'm_vector_idx' index already exists in the working keyspace.
  await table.createVectorIndex('m_vector_idx', 'mVector');

  // Create a vector index on the 'mVector' column with dot-product similarity.
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'm_vector_idx'.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
    },
    ifNotExists: true,
  });

  // Drop the index so you can recreate it with different options.
  await db.dropTableIndex('m_vector_idx');

  // Create the vector index with dot-product similarity and a source model.
  // For accurate searches, use a source model and metric that are compatible with your vectors.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
      sourceModel: 'ada002',
    },
  });

  // Vector indexes allow you to perform vector ANN searches.
  // Insert a row with a vector, and then run a vector search on the table.
  await table.insertOne({ matchId: '01', round: 0, mVector: [0.2, -0.3, -0.5] });
  await table.findOne({}, { sort: { mVector: [0.2, -0.3, -0.5] } }).then(console.log);

  // Get detailed information about the indexes
  // Returns information like [{ name: 'score_idx', definition: { column: 'score', options: {} } }, ...]
  await table.listIndexes().then(console.log);

  // Get index names only.
  // Returns information like ['score_idx', 'winner_idx', 'm_vector_idx', ...]
  await table.listIndexes({ nameOnly: true }).then(console.log);

  // Drop an index from a database's working keyspace without checking if the index exists.
  // If there is no match, the command succeeds but does nothing.
  // If there is a match, the named index is deleted.
  await db.dropTableIndex('score_idx');

  // Drop an index from a database's working keyspace if the index exists.
  // Errors if there is no match.
  await db.dropTableIndex('score_idx', { ifExists: true });

  // Uncomment the following line to drop the table and any related indexes.
  // await table.drop();
})();
// Get detailed information about the indexes
// Returns information like [{ name: 'score_idx', definition: { column: 'score', options: {} } }, ...]
await table.listIndexes().then(console.log);

// Get index names only.
// Returns information like ['score_idx', 'winner_idx', 'm_vector_idx', ...]
await table.listIndexes({ nameOnly: true }).then(console.log);

For more information, see the Client reference.

List the names of all indexes on a table:

List<String> indexesNames = tableGames.listIndexesNames();

List index names with command options:

ListIndexesOptions options = new ListIndexesOptions()
  .timeout(Duration.ofSeconds(5));

tableGames
  .listIndexesNames(options)
  .forEach(System.out::println);

To get details about the indexes, see List index metadata.

Parameters:

Name Type Summary

options

ListIndexesOptions

Specialization of index creation options.

Example:

package com.datastax.astra.client.tables;

import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.DataAPIClients;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.commands.options.ListIndexesOptions;
import com.datastax.astra.client.tables.definition.rows.Row;

import java.time.Duration;

public class ListIndexesNames {
 public static void main(String[] args) {
   //Database db = new DataAPIClient("token").getDatabase("endpoint");
   Database db = new DataAPIClient("token").getDatabase("endpoint");
   Table<Row> tableGames = db.getTable("games");

   //List<String> indexesNames = tableGames.listIndexesNames();

   ListIndexesOptions options = new ListIndexesOptions()
     .timeout(Duration.ofSeconds(5));

   tableGames.listIndexesNames(options)
             .forEach(System.out::println);
 }
}

Get the names of indexes associated with a specific table:

curl -sS --location -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/ASTRA_DB_KEYSPACE/ASTRA_DB_TABLE" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "listIndexes": {
    "options": {
      "explain": false
    }
  }
}' | jq

Parameters:

Name Type Summary

listIndexes

command

The Data API command to get a list of indexes associated with a table in a Serverless (Vector) database.

options.explain

boolean

If true, the response includes index names and metadata. If false or unset, the response includes only index names.

Returns:

A successful response returns a array of index names.

Example response 
{
  "status": {
    "indexes": [
      "index_name_1",
      "index_name_2"
    ]
  }
}

Drop an index

Delete an index from a table in a Serverless (Vector) database.

Dropping an index is a database-level operation because index names are unique across each keyspace.

In contrast, creating an index is table-level operation because you must specify a column within a table to index.

  • Python

  • TypeScript

  • Java

  • curl

For more information, see the Client reference.

Drop an index in a database:

database.drop_table_index("score_index")

Parameters:

Name Type Summary

name

str

The name of the index to delete.

keyspace

str | None

The keyspace where you created the index. If unspecified, the database’s default keyspace setting is used.

if_exists

bool | None

If True, and an index with the given name doesn’t exist in the keyspace, then the command succeeds and silently does nothing. In this case, no actual index deletion occurs.

If False (default), an error occurs if an index with the specified name does not exist in the keyspace.

table_admin_timeout_ms

int | None

A timeout, in milliseconds, to impose on the underlying API request. If not provided, the Database defaults apply. This parameter is aliased as request_timeout_ms and timeout_ms for convenience.

Example:

from astrapy import DataAPIClient
client = DataAPIClient("TOKEN")
database = client.get_database("API_ENDPOINT")

from astrapy.constants import SortMode
from astrapy.info import (
    CreateTableDefinition,
    ColumnType,
)

# Create table
my_table = database.create_table(
    "games",
    definition=(
        CreateTableDefinition.builder()
        .add_column("match_id", ColumnType.TEXT)
        .add_column("round", ColumnType.TINYINT)
        .add_vector_column("m_vector", dimension=3)
        .add_column("score", ColumnType.INT)
        .add_column("when", ColumnType.TIMESTAMP)
        .add_column("winner", ColumnType.TEXT)
        .add_set_column("fighters", ColumnType.UUID)
        .add_partition_by(["match_id"])
        .add_partition_sort({"round": SortMode.ASCENDING})
        .build()
    ),
)

from astrapy.info import TableIndexOptions

# Create index
my_table.create_index(
    "score_index",
    column="score",
)

# Drop an index from the keyspace:
database.drop_table_index("score_index")

# Drop an index, unless it does not exist already:
database.drop_table_index("score_index", if_exists=True)

For more information, see the Client reference.

Drop an index in a database:

await db.dropTableIndex('score_index');

Parameters:

Name Type Summary

name

string

The name of the index to delete.

option?

TableDropIndexOptions

The options for this operation.

Options (TableDropIndexOptions):

Name Type Summary

ifExists?

boolean

If true, and an index with the given name doesn’t exist in the keyspace, then the command succeeds and silently does nothing. In this case, no actual index deletion occurs.

If false (default), an error occurs if an index with the specified name does not exist in the keyspace.

timeout?

number | TimeoutDescriptor

The client-side timeout for this operation.

Returns:

Promise<void> - A promise which resolves when the operation is complete.

Example:

Full script 
import { CreateTableDefinition, DataAPIClient, SomeRow } from '@datastax/astra-db-ts';

// Instantiate the client and connect to the database
const client = new DataAPIClient();
const db = client.db(process.env.CLIENT_DB_URL!, { token: process.env.CLIENT_DB_TOKEN! });

// Create table schema using bespoke Data API table definition syntax.
// For information about table definition and data types, see the documentation for createTable.
const TableDefinition = <const>{
  columns: {
    matchId: 'text'
    round: 'tinyint',
    mVector: { type: 'vector', dimension: 3 },
    score: 'int',
    when: 'timestamp',
    winner: 'text',
    fighters: { type: 'set', valueType: 'uuid' },
  },
  primaryKey: {
    partitionBy: ['matchId'],
    partitionSort: { round: 1 },
  },
} satisfies CreateTableDefinition;

(async function () {
    // Create an untyped table if a 'games' table doesn't already exist
  const table = await db.createTable<SomeRow>('games', { definition: TableDefinition, ifNotExists: true });

  // Create a secondary index on the 'score' column with default options.
  // Errors if a 'score_idx' index already exists in the working keyspace.
  await table.createIndex('score_idx', 'score');

  // Create a secondary index on the 'winner' column with case-insensitivity
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'winner_idx'.
  await table.createIndex('winner_idx', 'winner', {
    options: {
      caseSensitive: false,
    },
    ifNotExists: true,
  });

  // Case insensitive indexes ignore case when querying.
  // Insert a row with upper case and lower case characters,
  // and then query the row.
  // findOne returns a match because 'winner_idx' is case-insensitive.
  await table.insertOne({ matchId: '01', round: 0, winner: 'Gray Tist' });
  await table.findOne({ winner: 'gray tist' }).then(console.log);

  // Create a vector index on the 'mVector' column with cosine similarity (default).
  // Errors if an 'm_vector_idx' index already exists in the working keyspace.
  await table.createVectorIndex('m_vector_idx', 'mVector');

  // Create a vector index on the 'mVector' column with dot-product similarity.
  // Because 'ifNotExists: true', the command does not throw an error
  // if the working keyspace already has an index named 'm_vector_idx'.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
    },
    ifNotExists: true,
  });

  // Drop the index so you can recreate it with different options.
  await db.dropTableIndex('m_vector_idx');

  // Create the vector index with dot-product similarity and a source model.
  // For accurate searches, use a source model and metric that are compatible with your vectors.
  await table.createVectorIndex('m_vector_idx', 'mVector', {
    options: {
      metric: 'dot_product',
      sourceModel: 'ada002',
    },
  });

  // Vector indexes allow you to perform vector ANN searches.
  // Insert a row with a vector, and then run a vector search on the table.
  await table.insertOne({ matchId: '01', round: 0, mVector: [0.2, -0.3, -0.5] });
  await table.findOne({}, { sort: { mVector: [0.2, -0.3, -0.5] } }).then(console.log);

  // Get detailed information about the indexes
  // Returns information like [{ name: 'score_idx', definition: { column: 'score', options: {} } }, ...]
  await table.listIndexes().then(console.log);

  // Get index names only.
  // Returns information like ['score_idx', 'winner_idx', 'm_vector_idx', ...]
  await table.listIndexes({ nameOnly: true }).then(console.log);

  // Drop an index from a database's working keyspace without checking if the index exists.
  // If there is no match, the command succeeds but does nothing.
  // If there is a match, the named index is deleted.
  await db.dropTableIndex('score_idx');

  // Drop an index from a database's working keyspace if the index exists.
  // Errors if there is no match.
  await db.dropTableIndex('score_idx', { ifExists: true });

  // Uncomment the following line to drop the table and any related indexes.
  // await table.drop();
})();
// Drop an index from a database's working keyspace without checking if the index exists.
// If there is no match, the command succeeds but does nothing.
// If there is a match, the named index is deleted.
await db.dropTableIndex('score_idx');

// Drop an index from a database's working keyspace if the index exists.
// Errors if there is no match.
await db.dropTableIndex('score_idx', { ifExists: true });

For more information, see the Client reference.

Drop an index from a database:

database.dropTableIndex("score_index");

Drop an index with command options:

DropTableIndexOptions options = new DropTableIndexOptions()
  .ifExists(true)
  .keyspace("KEYSPACE_NAME")
  .timeout(Duration.ofSeconds(5));

database
  .dropTableIndex("winner_index", options);

Parameters:

Name Type Summary

name

str

Name of the index to drop

options

DropTableIndexOptions

Specialization of index deletion options, including ifExists, 'keyspace` and timeout.

If ifExists(true), and an index with the given name doesn’t exist in the keyspace, then the command succeeds and silently does nothing. In this case, no actual index deletion occurs.

If ifExists(false) (default), an error occurs if an index with the specified name does not exist in the keyspace.

Example:

package com.datastax.astra.client.database;

import com.datastax.astra.client.DataAPIClients;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.commands.options.DropTableIndexOptions;
import com.datastax.astra.client.tables.commands.options.DropTableOptions;

import static java.time.Duration.ofSeconds;

public class DropTableIndex {

 public static void main(String[] args) {
  // Database astraDb = new DataAPIClient(token).getDatabase(endpoint);
  Database db = DataAPIClients.localDbWithDefaultKeyspace();

  // Drop without options
  db.dropTableIndex("games");

  // Adding a timestamp
  DropTableIndexOptions options = new DropTableIndexOptions()
   .ifExists(false)
   .keyspace("KEYSPACE_NAME")
   .timeout(ofSeconds(5));
  db.dropTableIndex("games", options);
 }
}
curl -sS --location -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/ASTRA_DB_KEYSPACE" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "dropIndex": {
      "name": "INDEX_NAME"
  }
}'

Parameters:

Name Type Summary

dropIndex

command

The Data API command to delete an index from a Serverless (Vector) database. It acts as a container for all the attributes and settings required to delete the index.

name

string

The name of the index to delete.

Returns:

A successful request returns 200 OK.

Example response 
{
  "status": {
    "ok": 1
  }
}

Example:

curl -sS --location -X POST "ASTRA_DB_API_ENDPOINT/api/json/v1/default_keyspace" \
--header "Token: ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--data '{
  "dropIndex": {
    "name": "index_metadata_students"
  }
}'

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com