Hyper-Converged Database (HCD) quickstart for tables

import os
from astrapy import DataAPIClient, Database
from astrapy.authentication import UsernamePasswordTokenProvider
from astrapy.constants import Environment


def connect_to_database() -> Database:
    """
    Connects to your database.
    This function retrieves the database endpoint, username, and password from the
    environment variables `API_ENDPOINT`, `USERNAME`, and `PASSWORD`.

    Returns:
        Database: An instance of the connected database.

    Raises:
        RuntimeError: If the environment variables `API_ENDPOINT`,
        `USERNAME`, or `PASSWORD` are not defined.
    """
    endpoint = os.environ.get("API_ENDPOINT")  (1)
    username = os.environ.get("USERNAME")
    password = os.environ.get("PASSWORD")

    if not endpoint or not username or not password:
        raise RuntimeError(
            "Environment variables API_ENDPOINT, USERNAME, and PASSWORD must be defined"
        )

    # Create an instance of the `DataAPIClient` class
    client = DataAPIClient(environment=Environment.HCD)

    # Get the database specified by your endpoint and provide the token
    database = client.get_database(
        endpoint,
        token=UsernamePasswordTokenProvider(username, password),
    )

    print(f"Connected to database {database.info().name}")

    return database

import {
  DataAPIClient,
  Db,
  UsernamePasswordTokenProvider,
} from "@datastax/astra-db-ts";

/**
 * Connects to your database.
 * This function retrieves the database endpoint, username, and password from the
 * environment variables `API_ENDPOINT`, `USERNAME`, and `PASSWORD`.
 *
 * @returns An instance of the connected database.
 * @throws Will throw an error if the environment variables
 * `API_ENDPOINT`, `USERNAME`, or `PASSWORD` are not defined.
 */
export function connectToDatabase(): Db {
  const {
    API_ENDPOINT: endpoint,
    USERNAME: username,
    PASSWORD: password,
  } = process.env; (1)

  if (!endpoint || !username || !password) {
    throw new Error(
      "Environment variables API_ENDPOINT, USERNAME, and PASSWORD must be defined.",
    );
  }

  // Create an instance of the `DataAPIClient` class
  const client = new DataAPIClient({ environment: "hcd" });

  // Get the database specified by your endpoint and provide the token
  const database = client.db(endpoint, {
    token: new UsernamePasswordTokenProvider(username, password),
  });

  console.log("Connected to database");

  return database;
}

package com.quickstart;

import com.datastax.astra.client.DataAPIClient;
import com.datastax.astra.client.DataAPIClients;
import com.datastax.astra.client.databases.Database;

public class QuickstartConnect {

  /**
   * Connects to your database. This function retrieves the database endpoint and
   * application token from the environment variables
   * `API_ENDPOINT`, `USERNAME`, and `PASSWORD`.
   *
   * @return an instance of the connected database
   * @throws IllegalStateException if the environment variables
   * `API_ENDPOINT`, `USERNAME`, or `PASSWORD` are not defined
   */
  public static Database connectToDatabase(String keyspace) {
    String endpoint = System.getenv("API_ENDPOINT"); (1)
    String username = System.getenv("USERNAME");
    String password = System.getenv("PASSWORD");

    if (endpoint == null || username == null || password == null) {
      throw new IllegalStateException(
          "Environment variables API_ENDPOINT, USERNAME, and PASSWORD must be defined");
    }

    // Create an instance of `DataAPIClient`
    DataAPIClient client = DataAPIClients.clientHCD(username, password);

    // Get the database specified by your endpoint
    Database database;
    if (keyspace != null && !keyspace.trim().isEmpty()) {
      // Connect with the provided keyspace
      database = client.getDatabase(endpoint, keyspace);
      System.out.println("Connected to database with keyspace: " + keyspace);
    } else {
      // Default connection without a specific keyspace
      database = client.getDatabase(endpoint);
      System.out.println("Connected to database (no keyspace specified).");
    }

    return database;
  }

  public static Database connectToDatabase() {
    return connectToDatabase(null);
  }
}

from quickstart_connect import connect_to_database  (1)


def main() -> None:
    database = connect_to_database()

    # Get an admin object
    admin = database.get_database_admin()

    # Create a keyspace
    admin.create_keyspace("quickstart_keyspace")  (2)

    print(f"Created keyspace")


if __name__ == "__main__":
    main()

import { connectToDatabase } from "./quickstart-connect"; (1)

(async function () {
  const database = connectToDatabase();

  // Get an admin object
  const admin = database.admin({ environment: "hcd" });

  // Create a keyspace
  await admin.createKeyspace("quickstart_keyspace"); (2)

  console.log("Created keyspace");
})();

package com.quickstart;

import com.datastax.astra.client.admin.DatabaseAdmin;
import com.datastax.astra.client.databases.Database;

public class QuickstartCreateKeyspaceDemo {

  public static void main(String[] args) {
    Database database = QuickstartConnect.connectToDatabase(); (1)

    // Get an admin object
    DatabaseAdmin admin = database.getDatabaseAdmin();

    // Create a keyspace
    admin.createKeyspace("quickstart_keyspace"); (2)

    System.out.println("Created keyspace");
  }
}

from quickstart_connect import connect_to_database  (1)
from astrapy.info import (
    CreateTableDefinition,
    ColumnType,
    TableVectorIndexOptions,
)
from astrapy.constants import VectorMetric


def main() -> None:
    database = connect_to_database()

    table_definition = (
        CreateTableDefinition.builder()
        # Define all of the columns in the table
        .add_column("title", ColumnType.TEXT)
        .add_column("author", ColumnType.TEXT)
        .add_column("number_of_pages", ColumnType.INT)
        .add_column("rating", ColumnType.FLOAT)
        .add_column("publication_year", ColumnType.INT)
        .add_column("summary", ColumnType.TEXT)
        .add_set_column(
            "genres",
            ColumnType.TEXT,
        )
        .add_map_column(
            "metadata",
            # This is the key type for the map column
            ColumnType.TEXT,
            # This is the value type for the map column
            ColumnType.TEXT,
        )
        .add_column("is_checked_out", ColumnType.BOOLEAN)
        .add_column("borrower", ColumnType.TEXT)
        .add_column("due_date", ColumnType.DATE)
        # This column will store vector embeddings. (2)
        .add_vector_column("summary_genres_vector", dimension=5)
        # Define the primary key for the table.
        # In this case, the table uses a composite primary key.
        .add_partition_by(["title", "author"])
        # Finally, build the table definition.
        .build()
    )

    table = database.create_table(
        "quickstart_table",  (3)
        keyspace="quickstart_keyspace",  (4)
        definition=table_definition,
    )

    print("Created table")

    # Index any columns that you want to sort and filter on.
    table.create_index(
        "rating_index",
        column="rating",
    )

    table.create_index(
        "number_of_pages_index",
        column="number_of_pages",
    )

    table.create_vector_index(
        "summary_genres_vector_index",
        column="summary_genres_vector",
        options=TableVectorIndexOptions(
            metric=VectorMetric.COSINE,  (5)
        ),
    )

    print("Indexed columns")


if __name__ == "__main__":
    main()

import { connectToDatabase } from "./quickstart-connect"; (1)
import {
  Table,
  InferTablePrimaryKey,
  InferTableSchema,
} from "@datastax/astra-db-ts";

const database = connectToDatabase();

const tableDefinition = Table.schema({
  // Define all of the columns in the table
  columns: {
    title: "text",
    author: "text",
    number_of_pages: "int",
    rating: "float",
    publication_year: "int",
    summary: "text",
    genres: { type: "set", valueType: "text" },
    metadata: {
      type: "map",
      keyType: "text",
      valueType: "text",
    },
    is_checked_out: "boolean",
    borrower: "text",
    due_date: "date",
    // This column will store vector embeddings. (2)
    summary_genres_vector: { type: "vector", dimension: 5 },
  },
  // Define the primary key for the table.
  // In this case, the table uses a composite primary key.
  primaryKey: {
    partitionBy: ["title", "author"],
  },
});

// Infer the TypeScript-equivalent type of the table's schema and primary key.
// Export the types for later use.
export type TableSchema = InferTableSchema<typeof tableDefinition>;
export type TablePrimaryKey = InferTablePrimaryKey<typeof tableDefinition>;

(async function () {
  const table = await database.createTable<TableSchema, TablePrimaryKey>(
    "quickstart_table", (3)
    {
      definition: tableDefinition,
      keyspace: "quickstart_keyspace", (4)
    },
  );

  console.log("Created table");

  // Index any columns that you want to sort and filter on.
  await table.createIndex("rating_index", "rating");

  await table.createIndex("number_of_pages_index", "number_of_pages");

  await table.createVectorIndex(
    "summary_genres_vector_index",
    "summary_genres_vector",
    {
      options: {
        metric: "cosine", (5)
      },
    },
  );

  console.log("Indexed columns");
})();

package com.quickstart;

import com.datastax.astra.client.core.vector.SimilarityMetric;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.Table;
import com.datastax.astra.client.tables.definition.TableDefinition;
import com.datastax.astra.client.tables.definition.columns.ColumnDefinitionVector;
import com.datastax.astra.client.tables.definition.columns.ColumnTypes;
import com.datastax.astra.client.tables.definition.indexes.TableVectorIndexDefinition;
import com.datastax.astra.client.tables.definition.rows.Row;

public class QuickstartTableCreateDemo {
  public static void main(String[] args) {
    Database database = QuickstartConnect.connectToDatabase("quickstart_keyspace"); (1)

    TableDefinition tableDefinition =
        new TableDefinition()
            // Define all of the columns in the table
            .addColumnText("title")
            .addColumnText("author")
            .addColumnInt("number_of_pages")
            .addColumn("rating", ColumnTypes.FLOAT)
            .addColumnInt("publication_year")
            .addColumnText("summary")
            .addColumnSet("genres", ColumnTypes.TEXT)
            .addColumnMap("metadata", ColumnTypes.TEXT, ColumnTypes.TEXT)
            .addColumnBoolean("is_checked_out")
            .addColumnText("borrower")
            .addColumn("due_date", ColumnTypes.DATE)
            // This column will store vector embeddings. (2)
            .addColumnVector(
                "summary_genres_vector",
                new ColumnDefinitionVector().dimension(5).metric(SimilarityMetric.COSINE))
            // Define the primary key for the table.
            // In this case, the table uses a composite primary key.
            .addPartitionBy("title")
            .addPartitionBy("author");

    // Default Table Creation
    Table<Row> table = database.createTable(
      "quickstart_table", (3)
      tableDefinition);

    System.out.println("Created table.");

    // Index any columns that you want to sort and filter on.
    table.createIndex("rating_index", "rating");

    table.createIndex("number_of_pages_index", "number_of_pages");

    TableVectorIndexDefinition definition =
        new TableVectorIndexDefinition()
            .column("summary_genres_vector")
            .metric(SimilarityMetric.COSINE); (4)

    table.createVectorIndex("summary_genres_vector_index", definition);

    System.out.println("Indexed columns.");
  }
}

from quickstart_connect import connect_to_database  (1)
from astrapy.data_types import DataAPIDate, DataAPIVector
import json


def main() -> None:
    database = connect_to_database()

    table = database.get_table(
        "quickstart_table", keyspace="quickstart_keyspace"
    )  (2)

    data_file_path = "PATH_TO_DATA_FILE"  (3)

    with open(data_file_path, "r", encoding="utf8") as file:
        json_data = json.load(file)

    rows = [
        {
            **data,
            "due_date": (
                DataAPIDate.from_string(data["due_date"])
                if data.get("due_date")
                else None
            ),
            "summary_genres_vector": DataAPIVector(data["summary_genres_vector"]),
        }
        for data in json_data
    ]

    insert_result = table.insert_many(rows)

    print(f"Inserted {len(insert_result.inserted_ids)} rows")


if __name__ == "__main__":
    main()

import { connectToDatabase } from "./quickstart-connect"; (1)
import { TableSchema, TablePrimaryKey } from "./quickstart-create-table"; (2)
import { DataAPIDate, DataAPIVector } from "@datastax/astra-db-ts";
import fs from "fs";

(async function () {
  const database = connectToDatabase();

  const table = database.table<TableSchema, TablePrimaryKey>(
    "quickstart_table",
    { keyspace: "quickstart_keyspace" },
  ); (3)

  const dataFilePath = "PATH_TO_DATA_FILE"; (4)

  // Read the JSON file and parse it into a JSON array.
  const rawData = fs.readFileSync(dataFilePath, "utf8");
  const jsonData = JSON.parse(rawData);

  const rows = jsonData.map((data: any) => ({
    ...data,
    genres: new Set(data.genres),
    metadata: new Map(Object.entries(data.metadata)),
    due_date: data.due_date ? new DataAPIDate(data.due_date) : null,
    summary_genres_vector: new DataAPIVector(data["summary_genres_vector"]),
  }));

  const insertedResult = await table.insertMany(rows);

  console.log(`Inserted ${insertedResult.insertedCount} rows.`);
})();

package com.quickstart;

import com.datastax.astra.client.core.vector.DataAPIVector;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.Table;
import com.datastax.astra.client.tables.commands.results.TableInsertManyResult;
import com.datastax.astra.client.tables.definition.rows.Row;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.io.FileInputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

public class QuickstartInsertToTableDemo {

  private static Date parseDate(String date) {
    if (date == null) return null;
    try {
      return new SimpleDateFormat("yyyy-MM-dd").parse(date);
    } catch (ParseException e) {
      throw new RuntimeException(e);
    }
  }

  public static void main(String[] args) throws Exception {
    Database database = QuickstartConnect.connectToDatabase("quickstart_keyspace"); (1)

    Table<Row> table = database.getTable("quickstart_table"); (2)

    // Initialize Jackson ObjectMapper
    ObjectMapper objectMapper = new ObjectMapper();

    try (FileInputStream stream = new FileInputStream("PATH_TO_DATA_FILE")) { (3)
      List<Row> rows = objectMapper.readValue(stream, new TypeReference<>() {});
      rows.forEach(
          row -> {
            // Deserialize the "genres" field into a HashSet
            row.add("genres", new HashSet<>(row.getList("genres", String.class)));

            // Deserialize the "metadata" field into a Map
            Map<String, String> metadataMap =
                objectMapper.convertValue(
                    row.get("metadata"), new TypeReference<Map<String, String>>() {});
            row.add("metadata", metadataMap);

            // Deserialize the "due_date" field into a Date or null
            row.add("due_date", parseDate(row.getText("due_date")));

            // Deserialize the "summary_genres_vector" field into DataAPIVector
            List<Double> vectorDoubles = row.getList("summary_genres_vector", Double.class);
            if (vectorDoubles != null) {
              float[] vectorData = new float[vectorDoubles.size()];
              for (int i = 0; i < vectorDoubles.size(); i++) {
                vectorData[i] = vectorDoubles.get(i).floatValue();
              }
              row.addVector("summary_genres_vector", new DataAPIVector(vectorData));
            }
          });

      TableInsertManyResult result = table.insertMany(rows);

      System.out.println("Inserted " + result.getInsertedIds().size() + " items.");
    }
  }
}

from quickstart_connect import connect_to_database  (1)
from astrapy.data_types import DataAPIVector


def main() -> None:
    database = connect_to_database()

    table = database.get_table(
        "quickstart_table", keyspace="quickstart_keyspace"
    )  (2)

    # Find rows that match a filter
    print("\nFinding books with rating greater than 4.7...")

    rating_cursor = table.find(
        {"rating": {"$gt": 4.7}}, projection={"title": True, "rating": True}
    )

    for row in rating_cursor:
        print(f"{row['title']} is rated {row['rating']}")

    # Perform a vector search to find the closest match to a search string
    print("\nUsing vector search to find a book...")

    single_vector_match = table.find_one(
        {},
        sort={
            "summary_genres_vector": DataAPIVector(
                [0.016326904, -0.031677246, 0.04815674, 0.0033435822, 0.01876831]
            )
        },
        projection={"title": True},
    )

    print(f"{single_vector_match['title']} is the best match")

    # Combine a filter and vector search to find the 3 books with
    # more than 400 pages that are the closest matches to a search string
    print(
        "\nUsing filters and vector search to find 3 books with more than 400 pages, returning just the title and author..."
    )

    vector_cursor = table.find(
        {"number_of_pages": {"$gt": 400}},
        sort={
            "summary_genres_vector": DataAPIVector(
                [0.016326904, -0.031677246, 0.04815674, 0.0033435822, 0.01876831]
            )
        },
        limit=3,
        projection={"title": True, "author": True},
    )

    for row in vector_cursor:
        print(row)


if __name__ == "__main__":
    main()

import { connectToDatabase } from "./quickstart-connect"; (1)
import { TableSchema, TablePrimaryKey } from "./quickstart-create-table"; (2)

(async function () {
  const database = connectToDatabase();

  const table = database.table<TableSchema, TablePrimaryKey>(
    "quickstart_table",
    { keyspace: "quickstart_keyspace" },
  ); (3)

  // Find rows that match a filter
  console.log("\nFinding books with rating greater than 4.7...");

  const ratingCursor = table.find(
    { rating: { $gt: 4.7 } },
    {
      limit: 10,
      projection: { title: true, rating: true },
    },
  );

  for await (const row of ratingCursor) {
    console.log(`${row.title} is rated ${row.rating}`);
  }

  // Perform a vector search to find the closest match to a search string
  console.log("\nUsing vector search to find a book...");

  const singleVectorMatch = await table.findOne(
    {},
    {
      sort: {
        summary_genres_vector: [
          0.016326904, -0.031677246, 0.04815674, 0.0033435822, 0.01876831,
        ],
      },
      projection: { title: true },
    },
  );

  console.log(`${singleVectorMatch?.title} is the best match`);

  // Combine a filter, vector search, and projection to find the 3 books with
  // more than 400 pages that are the closest matches to a search string
  console.log(
    "\nUsing filters and vector search to find 3 books with more than 400 pages, returning just the title and author...",
  );

  const vectorCursor = table.find(
    { number_of_pages: { $gt: 400 } },
    {
      sort: {
        summary_genres_vector: [
          0.016326904, -0.031677246, 0.04815674, 0.0033435822, 0.01876831,
        ],
      },
      limit: 3,
      projection: { title: true, author: true },
    },
  );

  for await (const row of vectorCursor) {
    console.log(row);
  }
})();

package com.quickstart;

import static com.datastax.astra.client.core.query.Projection.include;

import com.datastax.astra.client.core.query.Filter;
import com.datastax.astra.client.core.query.Filters;
import com.datastax.astra.client.core.query.Sort;
import com.datastax.astra.client.core.vector.DataAPIVector;
import com.datastax.astra.client.databases.Database;
import com.datastax.astra.client.tables.Table;
import com.datastax.astra.client.tables.commands.options.TableFindOneOptions;
import com.datastax.astra.client.tables.commands.options.TableFindOptions;
import com.datastax.astra.client.tables.definition.rows.Row;

public class QuickstartFindTableRowsDemo {
  public static void main(String[] args) {

    Database database = QuickstartConnect.connectToDatabase("quickstart_keyspace"); (1)

    Table<Row> table = database.getTable("quickstart_table"); (2)

    // Find rows that match a filter
    System.out.println("\nFinding books with rating greater than 4.7...");

    Filter filter = Filters.gt("rating", 4.7);

    TableFindOptions options =
        new TableFindOptions().limit(10).projection(include("title", "rating"));

    table
        .find(filter, options)
        .forEach(
            row -> {
              System.out.println(row.get("title") + " is rated " + row.get("rating"));
            });

    // Perform a vector search to find the closest match to a search string
    System.out.println("\nUsing vector search to find a book...");

    TableFindOneOptions options2 =
        new TableFindOneOptions()
            .sort(
                Sort.vector(
                    "summary_genres_vector",
                    new DataAPIVector(
                        new float[] {
                          0.016326904f, -0.031677246f, 0.04815674f, 0.0033435822f, 0.01876831f
                        })))
            .projection(include("title"));

    table
        .findOne(options2)
        .ifPresent(
            row -> {
              System.out.println(row.get("title") + " is the best match");
            });

    // Combine a filter, vector search, and projection to find the 3 books with
    // more than 400 pages that are the closest matches to a search string
    System.out.println(
        "\nUsing filters and vector search to find 3 books with more than 400 pages, returning just the title and author...");

    Filter filter3 = Filters.gt("number_of_pages", 400);

    TableFindOptions options3 =
        new TableFindOptions()
            .limit(3)
            .sort(
                Sort.vector(
                    "summary_genres_vector",
                    new DataAPIVector(
                        new float[] {
                          0.016326904f, -0.031677246f, 0.04815674f, 0.0033435822f, 0.01876831f
                        })))
            .projection(include("title", "author"));

    table
        .find(filter3, options3)
        .forEach(
            row -> {
              System.out.println(row);
            });
  }
}