How to check if a Cassandra table exists

Question

Is there an easy way to check if table (column family) is defined in Cassandra using CQL (or API perhaps, using com.datastax.driver)?

Right now I am leaning towards executing SELECT 1 FROM table and checking for exception but maybe there is a better way?

Aaron · Accepted Answer · 2016-05-23T12:16:54.423

26

As of 1.1 you should be able to query the system keyspace, schema_columnfamilies column family. If you know which keyspace you want to check, this CQL should list all column families in a keyspace:

SELECT columnfamily_name
FROM schema_columnfamilies WHERE keyspace_name='myKeyspaceName';

The report describing this functionality is here: https://issues.apache.org/jira/browse/CASSANDRA-2477

Although, they do note that some of the system column names have changed between 1.1 and 1.2. So you might have to mess around with it a little to get your desired results.

Edit 20160523 - Cassandra 3.x Update:

Note that for Cassandra 3.0 and up, you'll need to make a few adjustments to the above query:

SELECT table_name 
FROM system_schema.tables WHERE keyspace_name='myKeyspaceName';

edited May 23 '16 at 12:16

answered Apr 15 '13 at 14:52

Aaron

55,518
11
116
132

1

It works with CQL 3 but not with CQL 1 or 2: SELECT columnfamily_name FROM System.schema_columnfamilies WHERE keyspace_name='myKeyspaceName'; – Datageek Apr 15 '13 at 16:21
If someone checks if a table exists, and if not then create it, then this method is not thread safe. If multiple threads/client perform this kind of action concurrently, select may return no result, but CREATE statement may already fail. In such case it's better to go with exception based solution. – Wacław Borowiec Sep 25 '15 at 08:54
it will not work with Datastax v3.0 driver. Use KeyspaceMetadata/TableMetadata for that – Neil Stockton May 23 '16 at 07:26
@Aaron is there any way (by quering system tables) to know the existence of a table BY NOT SPECIFYING KEYSPACE NAME ? – Laxmikant Mar 29 '18 at 03:29
@Laxmikant Yes, if you know the table name, you can try: `SELECT * FROM system_schema.tables WHERE table_name='testmap' ALLOW FILTERING;` As the `system_schema` keyspace uses `LocalStrategy`, it will not be nearly as inefficient as using `ALLOW FILTERING` on a regular table. – Aaron Mar 29 '18 at 12:14

Olivier Michallat · Answer 2 · 2020-05-18T16:10:06.523

23

The Java driver (since you mentioned it in your question) also maintains a local representation of the schema.

Driver 3.x and below:

KeyspaceMetadata ks = cluster.getMetadata().getKeyspace("myKeyspace");
TableMetadata table = ks.getTable("myTable");
boolean tableExists = (table != null);

Driver 4.x and above:

Metadata metadata = session.getMetadata();
boolean tableExists =
  metadata.getKeyspace("myKeyspace")
    .flatMap(ks -> ks.getTable("myTable"))
    .isPresent();

edited May 18 '20 at 16:10

answered Jul 16 '15 at 12:27

Olivier Michallat

2,302
11
13

Good answer! I didn't even *think* about using `TableMetadata` for that. – Aaron Jul 16 '15 at 13:03
Perfect! That way you can even get easy access to the column definitions as well. – luk2302 Jul 06 '16 at 12:26
Works great with the DataStax driver v3, thanks Olivier! – albogdano Aug 31 '16 at 10:23
1

My problem with this and the C++ driver is the fact that in my case it transfers 250Kb of data each time. My current implementation gets that meta data once on startup, which improves the speed much, but the data is in a separate service (I have a form of proxy to access Cassandra.) So I think this is the best way (as in cleanest), it's just that it's really slow. – Alexis Wilke Mar 08 '19 at 20:00
With driver v4 an extra `.get()` is needed to unwrap the `Optional`, but other than that it still works. – lapo Apr 08 '20 at 16:08

score 6 · Answer 3 · answered Oct 02 '15 at 00:35

I just needed to manually check for the existence of a table using cqlsh. Possibly useful general info.

describe keyspace_name.table_name

If it doesn't exist you'll get 'table_name' not found in keyspace 'keyspace' If it does exist you'll get a description of the table.

score 0 · Answer 4 · answered Dec 28 '21 at 00:53

For the .NET driver CassandraCSharpDriver version 3.17.1 the following code creates a table if it doesn't exist yet:

var ks = _cassandraSession.Cluster.Metadata.GetKeyspace(keyspaceName);
var tableNames = ks.GetTablesNames();
if(!tableNames.Contains(tableName.ToLowerInvariant()))
{
   var stmt = new SimpleStatement($"CREATE TABLE {tableName} (id text PRIMARY KEY, name text, price decimal, volume int, time timestamp)");
   _cassandraSession.Execute(stmt);
}

You will need to adapt the list of table columns to your needs. This can also be awaited by using await _cassandraSession.ExecuteAsync(stmt).ConfigureAwait(false) in an async method.

Also, I want to mention that I'm using Cassandra version 4.0.1.

How to check if a Cassandra table exists

4 Answers4

Linked