Keyspaces and tables

This page explores keyspaces and tables in YCQL using the command line shell ycqlsh.

YCQL shell

Use the ycqlsh shell to interact with a Yugabyte database cluster using the YCQL API. Because ycqlsh is derived from the Apache Cassandra shell cqlsh, most cqlsh commands work as is in ycqlsh. Unsupported commands are listed at the end of this page.

Using ycqlsh, you can:

• interactively enter YCQL DDL and DML and see the query results
• input from a file or the command line
• use tab completion to automatically complete commands
• use a minimal command name as long as it can be distinguished from other commands (for example, desc instead of DESCRIBE)

ycqlsh is installed with YugabyteDB and is located in the bin directory of the YugabyteDB home directory.

Connect to a node

From your YugabyteDB home directory, connect to any node of the database cluster as follows:

$ ./bin/ycqlsh 127.0.0.1

This should bring up the following prompt, which prints the version of ycqlsh being used.

Connected to local cluster at 127.0.0.1:9042.
[ycqlsh 5.0.1 | Cassandra 3.9-SNAPSHOT | CQL spec 3.4.2 | Native protocol v4]
Use HELP for help.
ycqlsh>

Users

By default, YugabyteDB has one admin user already created: cassandra (the recommended user), and you can check the existing user as follows:

ycqlsh> select * from system_auth.roles;

You should see an output similar to the following:

 role      | can_login | is_superuser | member_of | salted_hash
-----------+-----------+--------------+-----------+------------------------------------------------------------------------------
 cassandra |      True |         True |          [] | $2a$12$64A8Vo0R3K9XeUp26CSzpuWtvUBwOiGFjPAbXGt7wsxZIScGrcsDu\x00\x00\x00\x00

Keyspaces

A keyspace is a container for a set of tables that belong together somehow. If multiple applications are using a single database, the tables belonging to each application can be grouped into a separate keyspace. In Apache Cassandra, replication configuration is done at the keyspace level. So, all tables that require the same replication configuration may be grouped into a keyspace. This pattern does not apply to YugabyteDB because in YugabyteDB replication configuration is done at the universe level.

To create a new keyspace testdb, run the following statement:

ycqlsh> CREATE KEYSPACE testdb;

To list all keyspaces, use the following command:

ycqlsh> DESCRIBE KEYSPACES;

system_schema  system_auth  testdb  system

To use a specific keyspace, use the following command:

ycqlsh> USE testdb;

You should see the following output:

ycqlsh:testdb>

To drop the keyspace you just created, use the DROP command as follows:

ycqlsh> DROP KEYSPACE testdb;

Verify the keyspace is no longer present as follows:

ycqlsh> DESCRIBE KEYSPACES;

system_schema  system_auth  system

Tables

Create a table using the CREATE TABLE statement.

CREATE TABLE users (
  id UUID,
  username TEXT,
  enabled boolean,
  PRIMARY KEY (id)
  );

To list all tables, use the following command:

ycqlsh> DESCRIBE TABLES;

ycqlsh:testdb> DESCRIBE TABLES;

users

To describe the table you created, enter the following:

ycqlsh> DESCRIBE TABLE users;

ycqlsh:testdb> describe table users;

CREATE TABLE testdb.users (
    id uuid PRIMARY KEY,
    username text,
    enabled boolean
) WITH default_time_to_live = 0
    AND transactions = {'enabled': 'false'};

Quit ycqlsh

To quit the shell, enter the following command:

ycqlsh> quit;

Unsupported cqlsh commands

Learn more

• CREATE KEYSPACE
• CREATE TABLE