CODEWITHSUNDEEP
cassandra
Christoph Kind
Christoph Kind

Reputation: 21

How to make a lookup-table in cassandra

I want to create a table in cassandra, that is used as a lookup table. I have a lot of urls in my database and want to store ids instead of the urls-strings. So my approach is, to store the urls in a table with two columns: id (int) and url (text).

My problem is, that I need an index for the url field and also for the id field.

The first index is used during progressing new ulrs (so find an id for an url in the database) and the second index is use during displaying data (get the url for an id).

How can I implement that in cassandra?

Upvotes: 1

Views: 1811

Answers (2)

mikea
mikea

Reputation: 6667

I would suggest creating 2 separate tables for this:

CREATE TABLE id_url (id int primary key, url text);

and

CREATE TABLE url_id (url text primary key, id int);

Inserts to these tables should be done with a batch:

BEGIN BATCH
    INSERT INTO id_url (id, url) VALUES (1, '<url1>');
    INSERT INTO url_id (url, id) VALUES ('<url1>', 1);
APPLY BATCH

Upvotes: 2

Horia
Horia

Reputation: 2982

You could create your table like this:

CREATE TABLE urls_table(
   id int PRIMARY KEY,
   url text
   );

and then create an index on the second column:

create index urls_table_url on urls_table (url);

Your first query is satisfied since you're querying over partition key. The second one is satisfied since you created an index on url column.

Upvotes: 0

Related Questions