get the information of cassandra by python

user_16163994 0
Viewed 855

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?

cassandra python
3 Answers

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)

Alex_Ott_186270

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.

user_10545580

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

user_125537300
Related