How to list all cassandra tables
ScalaApache SparkCassandraSpark Cassandra-ConnectorScala Problem Overview
There are many tables in cassandra database, which contain column titled user_id. The values user_id are referred to user stored in table users. As some users are deleted, I would like to delete orphan records in all tables that contain column titled user_id.
Is there a way to list all tables using CassandraSQLContext or any other built-in method or custom procedure in order to avoid explicitly defining the list of tables?
Scala Solutions
Solution 1 - Scala
From cqlsh
execute describe tables;
Solution 2 - Scala
There are system tables which can provide information about stored keyspaces, tables, columns.
Try run follows commands in cqlsh console:
-
Get keyspaces info
SELECT * FROM system.schema_keyspaces ;
-
Get tables info
SELECT columnfamily_name FROM system.schema_columnfamilies WHERE keyspace_name = 'keyspace name';
-
Get table info
SELECT column_name, type, validator FROM system.schema_columns WHERE keyspace_name = 'keyspace name' AND columnfamily_name = 'table name';
Since v 5.0.x Docs
-
Get keyspaces info
SELECT * FROM system_schema.keyspaces;
-
Get tables info
SELECT * FROM system_schema.tables WHERE keyspace_name = 'keyspace name';
-
Get table info
SELECT * FROM system_schema.columns WHERE keyspace_name = 'keyspace_name' AND table_name = 'table_name';
Since v 6.0 Docs
-
Get keyspaces info
SELECT * FROM system_schema.keyspaces
-
Get tables info
SELECT * FROM system_schema.tables WHERE keyspace_name = 'keyspace name';
-
Get table info
SELECT * FROM system_schema.columns WHERE keyspace_name = 'keyspace_name' AND table_name = 'table_name';
Solution 3 - Scala
desc keyspaces; // list all databases/collections names
use anyKeyspace; // select any database
desc tables; // list all tables in collection/ database
Solution 4 - Scala
You can achieve what you want using datastax core driver and cluster metadata. Here is an example which will list all the tables in your keyspace and columns in each table:
Cluster cluster= Cluster.builder().addContactPoint(clusterIp).build();
Metadata metadata =cluster.getMetadata();
Collection<TableMetadata> tablesMetadata= metadata.getKeyspace("mykeyspacename").getTables();
for(TableMetadata tm:tablesMetadata){
System.out.println("Table name:"+tm.getName());
Collection<ColumnMetadata> columnsMetadata=tm.getColumns();
for(ColumnMetadata cm:columnsMetadata){
String columnName=cm.getName();
System.out.println("Column name:"+columnName);
}
}
Solution 5 - Scala
For DSE. If later release check for system_schema keyspace. from
cqlsh > desc keyspaces;
spark_system system_schema "OpsCenter" cfs_archive "HiveMetaStore"
system_auth cfs demobeta dsefs
dse_security hypermedia dse_leases system_traces dse_perf
solr_admin system system_distributed dse_system
if you see 'system_schema' then the metadata for tables is in this keyspace.
cqlsh>use system_schema;
cqlsh>select keyspace_name,table_name from tables where keyspace_name = 'system';
Solution 6 - Scala
To list all the tables (only the table names),
DESC tables;
To list all the tables and the schemas (CQL creation statements),
DESC {$KEYSPACE_NAME}
whereas {$KEYSPACE_NAME}
is the name of the keyspace.
I am using Apache Cassandra 3.11.4 binary package downloaded from the official site:
cql> show version;
[cqlsh 5.0.1 | Cassandra 3.11.4 | CQL spec 3.4.4 | Native protocol v4]
The Apache Cassandra's CQL reference is here: https://cassandra.apache.org/doc/cql3/CQL-3.0.html
Solution 7 - Scala
Just connect CQLSH and try below commands
describe tables; or
DESC SCHEMA ;
Also you can 'desc keyspaces;' and 'use keyspace;' where you want to see the tables with 'describe tables;'
Solution 8 - Scala
try running the following query:
cqlsh> describe keyspace <keyspace_name>;