How do I model a CQL table such that it can be queried by zip_code, or by zip_code and hash?

user_6604408 0
Viewed 49

Hi all I have a cassandra Table containing Hash as Primary key and another column containing List. I want to add another column named Zipcode such that I can query cassandra based on either zipcode or zipcode and hash

Hash | List | zipcode

select * from table where zip_code = '12345';
select * from table where zip_code = '12345' && hash='abcd';

Is there any way that I could do this?

nosql cassandra
2 Answers

Recommendation in Cassandra is that you design your data tables based on your access patterns. For example in your case you would like to get results by zipcode and by zipcode and hash, so ideally you can have two tables like this

CREATE TABLE keyspace.table1 (
zipcode text,
field1  text,
field2 text,
PRIMARY KEY (zipcode));

and

 CREATE TABLE keyspace.table2 (
    hashcode text
    zipcode text,
    field1  text,
    field2 text,
    PRIMARY KEY ((hashcode,zipcode)));

Then you may be required to redesign your tables based on your data. I recommend you understand data model design in cassandra before proceeding further.

ALLOW FILTERING construct can be used but its usage depends on how big/small is your data. If you have a very large data then avoid using this construct as it will require complete scan of the database which is quite expensive in terms of resources and time.

user_29308120

It is possible to design a single table that will satisfy both app queries.

In this example schema, the table is partitioned by zip code with hash as the clustering key:

CREATE TABLE table_by_zipcode (
    zipcode int,
    hash text,
    ...
    PRIMARY KEY(zipcode, hash)
)

With this design, each zip code can have one or more rows of hash. Here's the table with some test data in it:

 zipcode | hash | intcol | textcol
---------+------+--------+---------
     123 |  abc |      1 |   alice
     123 |  def |      2 |     bob
     123 |  ghi |      3 |  charli
     456 |  tuv |      5 |  banana
     456 |  xyz |      4 |   apple

The table contains two partitions zipcode = 123 and zipcode = 456. The first zip code has three rows (abc, def, ghi) and the second has two rows (tuv, xyz).

You can query the table using just the partition key (zipcode), for example:

cqlsh> SELECT * FROM table_by_zipcode WHERE zipcode = 123;

 zipcode | hash | intcol | textcol
---------+------+--------+---------
     123 |  abc |      1 |   alice
     123 |  def |      2 |     bob
     123 |  ghi |      3 |  charli

It is also possible to query the table with the partition key zipcode and clustering key hash, for example:

cqlsh> SELECT * FROM table_by_zipcode WHERE zipcode = 123 AND hash = 'abc';

 zipcode | hash | intcol | textcol
---------+------+--------+---------
     123 |  abc |      1 |   alice

Cheers!

user_42695350
Related