CODEWITHSUNDEEP
primary-keycql3cassandra-2.0
Mr. MonoChrome
Mr. MonoChrome

Reputation: 1383

Cassandra select on primary key without primary keys name

Using CQL 3 I essentially want to make a statement like so....

SELECT * FROM columnFamilyName WHERE <PK>=1;

Where <PK> is a generic term for primary key.

If i have 2 tables one where the primary key name is ID, and one where it is Index, the above statement should work for both.

In a sense aerospike supports this(the primary key in aerospike simply doesnt have a name), but im not sure how to get the same generic primary key name in cassandra.

Upvotes: 1

Views: 966

Answers (2)

BrianC
BrianC

Reputation: 10721

CQL does not have a generic term for the primary key; instead you would need to provide it explicitly. Presumably an application or script which is reading from Cassandra would already have knowledge of the schema and would therefore know the key names.

However if you wanted to get the primary key column name(s) programmatically, here's one approach: use the Cassandra system tables to query the table definitions, and parse through the results to pick out the key name(s).

For example here's a simple table with a simple primary key (partition key):

cqlsh:key1> create table table1 (a int, b int, c int, primary key (a));
cqlsh:key1> select column_name,component_index,type from system.schema_columns 
        ... where keyspace_name='key1' and columnfamily_name='table1';

 column_name | component_index | type
-------------+-----------------+---------------
           a |            null | partition_key
           b |               0 |       regular
           c |               0 |       regular

And here's a slightly more complicated primary key like @reggoodwin suggested:

cqlsh:key1> create table table2 (a int, b int, c int, d int, e int, 
        ... primary key ((a, b), c, d));
cqlsh:key1> select column_name,component_index,type from system.schema_columns
        ... where keyspace_name='key1' and columnfamily_name='table1';

 column_name | component_index | type
-------------+-----------------+----------------
           a |               0 |  partition_key
           b |               1 |  partition_key
           c |               0 | clustering_key
           d |               1 | clustering_key
           e |               2 |        regular

For more information, check out the docs page on Querying a system table.

Upvotes: 1

reggoodwin
reggoodwin

Reputation: 1544

Ukemi, I'm not aware of this being possible because a Cassandra table can have a compound primary key and the first part of the primary key can also itself be a compound partition key.

E.g. in this situation:

CREATE TABLE .... PRIMARY KEY ((a, b), c, d) ..

Does <PK> refer to a, b, c or d?

Of course, if it IS possible, I'd like to know too!

Cheers,

Upvotes: 1

Related Questions