Drop a YCQL Keyspace Which is Not Empty

In YCQL the DROP KEYSPACE statement is used remove a keyspace from the system.

An error is raised if the specified keyspace is non-empty (contains tables or types).

This bash script can be deployed to help make dropping a keyspace which is non-empty a little easier. It accepts the keyspace name as a parameter.

				
					#!/bin/bash
shopt -s expand_aliases

# Script:      drop_keyspace.sh
# Description: This script will drop a given YCQL keyspace, first dropping all tables and types in that keyspace
# Created:     10/12/2022
# Last update: 10/12/2022

# Notes:       When issuing a DROP KEYSPACE command in YCQL, an error is raised if the specified keyspace
#                is non-empty (contains tables or types).
# Reference:   https://docs.yugabyte.com/latest/api/ycql/ddl_drop_keyspace/#semantics

# Set up the YCQLSH alias below per your environment
yb_path='/root/yugabyte-2.15.2.1/bin/ycqlsh'
yb_ip='xxx.xx.xx.xxx' # This can be any node, preferrably the Master Leader node
yb_user='cassandra'
yb_password='password'

alias c="$yb_path $yb_ip -u $yb_user -p $yb_password"

# Check if keyspace exists
ks=`c -e "SELECT COUNT(*) FROM system_schema.keyspaces WHERE keyspace_name = '$1';" |  head -n -2 | tail -n +4 | awk '{ print $1'}`

if [ $ks -gt 0 ]
then
  # Count the number of tables in keyspace prior to dropping the keyspace
  tbc=`c -e "SELECT COUNT(*) FROM system_schema.tables WHERE keyspace_name = '$1';" |  head -n -2 | tail -n +4 | awk '{ print $1'}`

  # Count the number of types in keyspace prior to dropping the keyspace
  tpc=`c -e "SELECT COUNT(*) FROM system_schema.types WHERE keyspace_name = '$1';" |  head -n -2 | tail -n +4 | awk '{ print $1'}`

  if [ $tbc -gt 0 ] || [$tpc -gt 0 ]
  then
    echo "INFO: Keyspace \"$1\" contains $tbc table(s) and $tpc type(s)."
    c -e "SELECT table_name FROM system_schema.tables WHERE keyspace_name = '$1';" | head -n -2 | tail -n +4 | awk -v var=$1 '{ print "DROP TABLE "var"."$1";"}' | c
    c -e "SELECT type_name FROM system_schema.types WHERE keyspace_name = '$1';" | head -n -2 | tail -n +4 | awk -v var=$1 '{ print "DROP TYPE "var"."$1";"}' | c
    c -e "DROP KEYSPACE $1;"

    # Check if keyspace still exists, if it does, show failure message, otherwise, show success message
    ks=`c -e "SELECT COUNT(*) FROM system_schema.keyspaces WHERE keyspace_name = '$1';" |  head -n -2 | tail -n +4 | awk '{ print $1'}`

    if [ $ks -gt 0 ]
    then
      tbc=`c -e "SELECT COUNT(*) FROM system_schema.tables WHERE keyspace_name = '$1';" |  head -n -2 | tail -n +4 | awk '{ print $1'}`
      tpc=`c -e "SELECT COUNT(*) FROM system_schema.types WHERE keyspace_name = '$1';" |  head -n -2 | tail -n +4 | awk '{ print $1'}`
      echo "Keyspace \"$1\" was unsuccesfully dropped. There are $tbc table(s) and $tpc type(s) remaining in the keyspace."

    else
      echo "Keyspace \"$1\" was succesfully dropped."

    fi

  fi

else
  echo "Keyspace \"$1\" does not exist."

fi

exit 0

Example:

I want to drop the keyspace test, which contains the following objects:

				
					[root@localhost ~]# alias c
alias c='ycqlsh xxx.xx.xx.xxx -u cassandra -p password'

[root@localhost ~]# c -e "DESC KEYSPACE test;"

CREATE KEYSPACE test WITH replication = {'class': 'SimpleStrategy', 'replication_factor': '1'}  AND durable_writes = true;

CREATE TYPE test.person (
    first_name text,
    last_name text,
    emails frozen<list<text>>
);

CREATE TABLE test.t2 (
    c1 int PRIMARY KEY
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};

CREATE TABLE test.t1 (
    c1 int PRIMARY KEY
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};

CREATE TABLE test.t3 (
    c1 int PRIMARY KEY
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};

If I try to drop the keyspace, I get an error message.

				
					[root@localhost ~]# c -e "DROP KEYSPACE test;"
<stdin>:1:ServerError: Server Error. Cannot delete keyspace which has table: t2 [id=3bd112a615874422bc4822b12ee6e9df], request: namespace { name: "test" }: NAMESPACE_IS_NOT_EMPTY (master error 14)
DROP KEYSPACE test;
              ^^^^
 (ql error -2)

I could manually drop all of the objects myself, but I’ll let the drop_keyspace.sh script do all that work for me!

				
					[root@localhost ~]# ./drop_keyspace.sh test
INFO: Keyspace "test" contains 3 table(s) and 1 type(s).
Keyspace "test" was succesfully dropped.

Have Fun!