CODEWITHSUNDEEP
databasegocassandranosqldata-modeling
jpiechowka
jpiechowka

Reputation: 75

How to properly model data in Apache Cassandra to allow querying by two different fields that are not unique

I am developing a simple API in Go using Apache Cassandra and I am wondering what is the best way to represent data that I have.

I have the following struct in Go.

type Message struct {
    Id          gocql.UUID `json:"id"`
    Email       string     `json:"email"`
    Title       string     `json:"title"`
    Content     string     `json:"content"`
    Number      int64      `json:"number"`
    DateCreated time.Time  `json:"dateCreated"`
}

What is the best way to create a data structure in Cassandra to allow querying by Email and Number which are both not unique (Only Id is unique. There can be multiple messages that have the same email and/or number)? Should I create two separate tables to allow querying by those two fields?

For querying by Id I would create table like this:

USE some_keyspace;

CREATE TABLE IF NOT EXISTS messages
(
    id           UUID,
    email        TEXT,
    title        TEXT,
    content      TEXT,
    number.      BIGINT,
    date_created TIMESTAMP,
    PRIMARY KEY (id)
);

Upvotes: 1

Views: 70

Answers (1)

Aaron
Aaron

Reputation: 57748

The best way? Create a table for each query you want to serve. Build the PRIMARY KEY definitions with both the column you want to query by and id (to ensure uniqueness):

CREATE TABLE IF NOT EXISTS messages_by_email (
    id           UUID,
    email        TEXT,
    title        TEXT,
    content      TEXT,
    number      BIGINT,
    date_created TIMESTAMP,
    PRIMARY KEY (email,id));

CREATE TABLE IF NOT EXISTS messages_by_number (
    ....
    PRIMARY KEY (number,id));

When you write a row to these tables, use BATCH to ensure atomicity.

BEGIN BATCH
    INSERT INTO messages (id,email,number,title,content,date_created)
        VALUES (uuid(),'[email protected]',1,'Hi','Cassandra rocks!',toTimestamp(now()));
    INSERT INTO messages_by_email (id,email,number,title,content,date_created)
        VALUES (uuid(),'[email protected]',1,'Hi','Cassandra rocks!',toTimestamp(now()));
    INSERT INTO messages_by_number (id,email,number,title,content,date_created)
        VALUES (uuid(),'[email protected]',1,'Hi','Cassandra rocks!',toTimestamp(now()));
APPLY BATCH;

Upvotes: 1

Related Questions