Add columns to your table
After creating a table in your keyspace, you can add columns to your table definition.
Use the Add a column definition endpoint to replace a column definition in the table you created.
Adding columns to table schema
Add a column with a simple data type
If you need to add an attribute to something you are storing in a table, you
can add a column by sending a POST
request that specifies the column name
and data type in the --data
(shortcut -d
).
curl -s -L -X POST http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
"name": "email",
"typeDefinition": "text"
}'
{ "name": "email" }
Change a column name or data type
To change the name or data type of an existing column, use a similar command, but
send a PUT
request instead:
curl -s -L \
-X PUT http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns/firstname \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
"name": "first",
"typeDefinition": "varchar"
}'
{
"name": "first"
}
Notice that the request changes the column name firstname
to first
by specifying the
new name and that the data type is still required.
Collections (set, list, map)
Collections can be defined when tables are created or added later. Recall that collections can be defined as frozen, meaning that the entire collection must be retrieved, rather than a part of the collection.
See this CQL document to learn more about the keyword frozen and its uses.
To add a set to a table, specify the data type set
along with the data type of the
set contents, such as text
:
curl -s -L -X POST http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
"name": "favorite_books",
"typeDefinition": "set<text>"
}'
{"name":"favorite_books"}
If the set were defined as a frozen set, specify "typeDefinition": "frozen<set<text>>"
.
To add a list to a table, specify the data type list
along with the data type of the
list contents, such as text
.
The difference between a set and a list is that a set is unordered, whereas a list
is ordered:
curl -s -L -X POST http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
"name": "top_three_tv_shows",
"typeDefinition": "list<text>"
}'
{"name":"top_three_tv_shows"}
To add a map to a table, specify the data type map
along with the data type of the
map key and the map value:
curl -s -L -X POST http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
"name": "evaluations",
"typeDefinition": "map<int,text>"
}'
{"name":"evaluations"}
Tuple
To add a tuple to a table, specify the data type tuple
along with the data type of the
each item in the tuple.
A tuple can consist of two or more values that can be retrieved together.
Tuples are frozen by default, and do not need to be specified with the keyword frozen
.
curl -s -L -X POST http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
"name": "current_country",
"typeDefinition": "tuple<text, date, date>"
}'
{"name":"current_country"}
User-defined type (UDT)
User-defined types (UDTs) are a useful feature that allows you to add a custom
data type consisting of two or more associated attributes.
UDTs must currently be created in Cassandra Query Language (CQL)
or GraphQL
before specifying for a column in a table definition with the REST API.
Once the UDT exists in the keyspace as a resource, you can add a column that
specifies the data type as udt
.
First let’s look at how to create a UDT with either CQL or GraphQL.
# create a user-defined type (UDT)
mutation createAddressUDT {
createType(
keyspaceName: "users_keyspace"
typeName: "address_type"
fields: [
{ name: "street", type: { basic: TEXT } }
{ name: "city", type: { basic: TEXT } }
{ name: "state", type: { basic: TEXT } }
{ name: "zip", type: { basic: TEXT } }
]
)
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createAddressUDT {\n createType(\n keyspaceName: \"'$KEYSPACE_NAME'\"\n typeName: \"address_type\"\n fields: [\n { name: \"street\", type: { basic: TEXT } }\n { name: \"city\", type: { basic: TEXT } }\n { name: \"state\", type: { basic: TEXT } }\n { name: \"zip\", type: { basic: TEXT } }\n ]\n )\n}","variables":{}}'
CREATE TYPE IF NOT EXISTS users_keyspace.address_type (
street text,
city text,
state text,
zip text
);
Now we can add a UDT column to our table:
curl -s -L -X POST http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Accept: application/json" \
-H "Content-Type: application/json" \
-d '{
"name": "address",
"typeDefinition": "address_type"
}'
{ "name": "address" }
Checking column existence
To check if a column exists, execute a
REST API query with cURL
to find all the columns:
curl -s -L \
-X GET http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Accept: application/json" \
-H "Content-Type: application/json"
{
"data": [
{
"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
}
]
}
To get a particular column, specify the column in the URL:
curl -s -L \
-X GET http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/rest/v2/schemas/keyspaces/users_keyspace/tables/users/columns/email \
-H "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"
{
"data":{
"name":"email",
"typeDefinition":"varchar",
"static":false
}
}