Reputation: 31
I connect to a remote Cassandra DB by:
from cassandra.cluster import Cluster
from cassandra.auth import PlainTextAuthProvider
from cassandra.query import dict_factory
cluster = Cluster(contact_points=[CASSANDRA_HOST], username='1',password='123')
session=cluster.connect()
Then, I would like to get the list of keyspace, table, columns which already in Cassandra. In cqlsh, it works with describe keyspace
. How can I do this in Python?
Upvotes: 3
Views: 2913
Reputation: 57843
In addition to the cluster metadata, the system_schema
keyspace is your friend! You can SELECT
this data like any other from the following tables:
system_schema.keyspaces
system_schema.tables
system_schema.columns
Pulling column metadata could look something like this:
strCCQL = "SELECT column_name,type FROM system_schema.columns WHERE keyspace_name=? AND table_name=?"
pcStatement = session.prepare(strCCQL)
pcStatement.consistency_level=ConsistencyLevel.LOCAL_ONE
crows = session.execute(pcStatement,[keyspace,table])
for crow in crows:
column = crow.column_name
type = crow.type
print (keyspace,table,column,type)
This example puts it all together. But you could seed it with keyspace and table name data by querying the keyspaces
and tables
tables in a similar manner.
Upvotes: 2
Reputation: 309
I have checked your requirement and say once you get the keyspace names you can separate and use the below code accordingly;
Note: To fetch, list of tables and views from Cassandra DB we have to pass the appropriate Keyspace Name aswell.
# Note: pip install cassandra-driver
import pandas as pd
from cassandra.cluster import Cluster
from cassandra.query import dict_factory
from cassandra.auth import PlainTextAuthProvider
def get_keyspace_list_and_view_names(HOST, PORT, USERNAME, PASSWORD, KEYSPACE_NAME):
"""
This method fetches the keyspace, list and view names from a given cassandra cluster
"""
try:
t_list, v_list, keyspace_list = [], [], []
cluster = Cluster(contact_points=[HOST], port=int(PORT), load_balancing_policy=None,
auth_provider=PlainTextAuthProvider(username=USERNAME, password=PASSWORD))
session = cluster.connect(KEYSPACE_NAME)
for i in session.execute("SELECT * FROM system_schema.keyspaces;"): keyspace_list.append(i.keyspace_name)
for i in session.execute("SELECT * FROM system_schema.tables WHERE keyspace_name = cassandra_test_keyspace;"): t_list.append(i.table_name)
for i in session.execute("SELECT * FROM system_schema.views WHERE keyspace_name = cassandra_test_keyspace;"): v_list.append(i.view_name)
except Exception as error_msg:
return error_msg
finally:
session.shutdown()
return keyspace_list, t_list, v_list
Thanks
Upvotes: 0
Reputation: 87369
The instance of Cluster
has a metadata
field that is an instance of the Metadata class - it has the keyspaces
member that is the list of existing keyspaces, and iterating over it you can get all tables, then per table - columns, etc.
DESCRIBE KEYSPACE
is the cqlsh
command, although maybe it will be supported as normal CQL command (don't remember, maybe it will be a part of 4.0 release)
Upvotes: 2