Using the CQL API

To use Stargate’s Cassandra Query Language (CQL) API you can use the same CQL syntax, tools and native language drivers that you would with a normal Cassandra deployment.

Popular Cassandra drivers are:

Prerequisites

  • cqlsh (CQL shell) (standalone, as part of Astra DB, or as part of a separate Cassandra installation)

If you haven’t already, create a database using Astra DB.

  • Install cURL, a utility for running REST, Document, or GraphQL queries on the command line.

  • [Optional] If you prefer, you can use Postman as a client interface for exploring the APIs

    • You will also find links to downloadable collections and environments in Using Postman

  • [Optional] If you going to use the GraphQL API, you will want to use the GraphQL Playground to deploy schema and execute mutations and queries.

  • [Optional] For the REST and Document APIs, you can use the Swagger UI.

Before you get started, set your environment variables to save time developing on your database. There are four environment variables, three of which you will get from the Astra dashboard (database id, region, and keyspace), and one that you must create (token).

  1. In Astra DB, select the database to which you want to connect.

  2. In your Database Dashboard, select Connect.

  3. Select your API.

    If you have multiple regions, select the region you want to connect to from the dropdown menu for instructions.

  4. Follow the steps to get your application token and set up your environment variables. Or if you have an older Astra Classic database, follow the steps in Authentication for classic databases.

CQL console

The Cassandra Query Language SHell (CQLSH) is a command line shell for interacting with your database through Cassandra Query Language (CQL). This tool provides a useful interface for accessing the database and issuing CQL commands.

Each DataStax Astra DB database includes an embedded CQL shell instance. In the Astra DB console, navigate to your database and click the CQL Console tab to open a CQLSH instance that is connected to your database. Issue CQL commands directly to your Astra DB database without navigating outside of your browser.

To see the CQL console in a full browser window, use this URL format:

https://<database_id>-<region>.apps.astra.datastax.com/cqlsh

Once you open the CQL shell in Astra, you will automatically be signed in and connected to your database. You can now run CQL commands in your embedded CQL shell.

Alternatively, you can connect to your Astra DB database using the standalone version of CQLSH.

Interact with CQL

You can now add and retrieve schema and data using CQL.

Create a table

CREATE TABLE users_keyspace.users (
    firstname text,
    lastname text,
    email text,
    "favorite color" text,
    PRIMARY KEY (firstname, lastname)
) WITH CLUSTERING ORDER BY (lastname ASC);

Insert data

INSERT INTO users_keyspace.users (
  firstname,
  lastname,
  email,
  "favorite color"
) VALUES (
  'Mookie',
  'Betts',
  'mookie.betts@gmail.com',
  'blue'
);

Retrieve data

To select all rows in the table:

SELECT * FROM users_keyspace.users;

To select a row using the primary key:

SELECT * FROM users_keyspace.users WHERE firstname = 'Mookie' AND lastname = 'Betts';

Update data

Update the email address:

UPDATE users_keyspace.users SET email = 'mookie.betts-new-email@gmail.com' WHERE firstname = 'Mookie' AND lastname = 'Betts';

Verify that the row has the updated email address:

SELECT * FROM users_keyspace.users WHERE firstname = 'Mookie';

Delete data

Delete the row using the primary key:

DELETE FROM users_keyspace.users WHERE firstname = 'Mookie' AND lastname = 'Betts';

Using the CQL API with Cassandra drivers

Astra DB for DSE can be used with any of the Cassandra drivers.

For example, to connect to {datastax} running on your local machine using the Python driver, replacing the IP address with an appropriate address:

from cassandra.cluster import Cluster

cluster = Cluster(['127.0.0.1'])

session = cluster.connect()

session.set_keyspace('users_keyspace')

rows = session.execute('SELECT firstname, lastname, email FROM users')
for user_row in rows:
    print(user_row.firstname, user_row.lastname, user_row.email)