Create a table in your keyspace

Create tables in your database using the DataStax Astra DB REST API. Use the application token you generated to create a table in your keyspace. A keyspace is like a bucket that holds your tables. You can create different keyspaces for groups of tables.

Prerequisites

  1. Manage application token.

  2. After generating an authorization token, run the following request to get all available keyspaces and verify that the keyspace you wish to use is listed in the results:

  • cURL command (/v2)

  • Result

curl -s -L -X GET http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"
{
  "data":[
    {
      "name":"system_schema"
    },
    {
      "name":"system"
    },
    {
      "name":"system_distributed"
    },
    {
      "name":"system_auth"
    },
    {
      "name":"system_traces"
    }
    {
      "name":"data_endpoint_auth"
    },
    {
      "name":"stargate_system"
    },
    {
      "name":"users_keyspace"
    },
    {
      "name":"myworld"
    },
    {
      "name":"library"
    },
  ]
}

For example, we’ll use users_keyspace as the keyspace. If the keyspace is not listed, go to your Astra DB dashboard and add the keyspace.

  • Optional: A header line using a {unique-UUID}, a randomly-generated UUID that is unique for the authorization request, can be included in the previous command by adding the following line:

 --header 'x-cassandra-request-id: {unique-UUID}'

Creating a table

Send a POST request to /api/rest/v2/schemas/keyspaces/{keyspace_name}/tables to create a table. Set the table name and column definitions in the JSON body in the --data.

  • cURL command (/v2)

  • Result

curl -s --location \
--request POST http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header "Content-Type: application/json" \
--header "Accept: application/json" \
--data '{
	"name": "users",
	"columnDefinitions":
	  [
        {
	      "name": "firstname",
	      "typeDefinition": "text"
	    },
        {
	      "name": "lastname",
	      "typeDefinition": "text"
	    },
        {
	      "name": "favorite_color",
	      "typeDefinition": "text"
	    }
	  ],
	"primaryKey":
	  {
	    "partitionKey": ["firstname"],
	    "clusteringKey": ["lastname"]
	  }
}'
{"name":"users"}

The name of our example table is users. This table column definition includes:

Partition key

One or more columns required. firstname is the partition key.

Clustering key

Optional, but zero, one or more columns can be defined. lastname is the clustering key.

Non-primary key

Optional, but zero, one or more columns that are not a partition key or clustering key can be defined. favorite_color is the non-primary key column.

Notice that each column must have a data type specified.

Information about primary keys (partition keys and clustering keys) are found in the Cassandra Query Language (CQL) reference.

  • Optional: Table options can be set. The two options are:

    defaultTimeToLive

    Sets the default Time-To-Live (TTL).

    clusteringExpression

    Defines the order, either ascending (ASC) or descending (DESC) for columns that are clustering keys. ASC is the default.

  "tableOptions":
    {
      "defaultTimeToLive": 0,
      "clusteringExpression":
        [{ "column": "lastname", "order": "ASC" }]
    }

Data types

Many common data types are supported by the REST API, such as text, integer, decimal, date and time. In addition, collections (set, list, map) and tuples are supported. Collections can be specified as frozen collections, meaning that the entire collection must be matched in the query to retrieve the collection data. User-defined types (UDTs) can be defined using CQL or GraphQL and then used when a table is created. Make UDTs before defining a table that uses one.

You can also nest any of the data types available. For example, a LIST of TUPLES, or a UDT that uses a MAP is possible.

All columns must be set with a data type in the original table creation. If columns are added after table creation, a data type is also required. For more examples of specifying columns of various data types, see Add columns to your table.

Checking table existence

To check if a table exists, execute a REST API query with cURL to find all the tables:

  • cURL command (/v2)

  • Result

curl -s -L -X GET http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"
{"data":{"name":"users_keyspace"}}

In this case, we only have one table in the keyspace.

To get a particular table, specify the table in the URL:

  • cURL command (/v2)

  • Result

curl -s -L -X GET http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json"
{
  "data": {
    "name": "users",
    "keyspace": "testks",
    "columnDefinitions": [
      {
        "name": "firstname",
        "typeDefinition": "varchar",
        "static": false
      },
      {
        "name": "lastname",
        "typeDefinition": "varchar",
        "static": false
      },
      {
        "name": "address",
        "typeDefinition": "address_type",
        "static": false
      },
      {
        "name": "current_country",
        "typeDefinition": "frozen<tuple<varchar, date, date>>",
        "static": false
      },
      {
        "name": "email",
        "typeDefinition": "varchar",
        "static": false
      },
      {
        "name": "evaluations",
        "typeDefinition": "map<int, varchar>",
        "static": false
      },
      {
        "name": "favorite_books",
        "typeDefinition": "set<varchar>",
        "static": false
      },
      {
        "name": "favorite_color",
        "typeDefinition": "varchar",
        "static": false
      },
      {
        "name": "top_three_tv_shows",
        "typeDefinition": "list<varchar>",
        "static": false
      }
    ],
    "primaryKey": {
      "partitionKey": [
        "firstname"
      ],
      "clusteringKey": [
        "lastname"
      ]
    },
    "tableOptions": {
      "defaultTimeToLive": 0,
      "clusteringExpression": [
        {
          "order": "ASC",
          "column": "lastname"
        }
      ]
    }
  }
}

Although this command is slightly different, because we have only one table, the command to get all tables and this command to just get the table users return the same information.