Code Zen
Published in

Code Zen

Image from https://www.jisc.ac.uk/rd/projects/digging-into-data-challenge

Cassandra Schemas for Beginners (like me)

I was awe-struck when I first learned that Cassandra had tables. Then followed was another question along the line of how was it even a NoSQL database.

NoSQL != Schemaless

The Only Difference

Cassandra as a Key-value Database

“user_tweets” table
key-value relationship of the “user_tweet” table
var tweets = JSON.parse(tweet_data)
var my_email = tweets[0]['jochasinga']['email'];
SELECT "email" FROM "user_tweets" WHERE "username" = 'jochasinga';
SELECT * FROM "user_tweets" WHERE "email" = 'jo.chasinga@gmail.com';
InvalidRequest: code=2200 [Invalid query] message=”Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING
tweets.forEach(function(elm, i, arr) {    // Retrieve an array of all the keys
var keys = Object.keys[elm]
if elm[keys[0]]['email'] == "jo.chasinga@gmail.com" {
return elm
}
}
“user_tweets” table after I have retweeted to @banksy
Key-value relationship of the “user_tweets” table
SELECT * FROM "user_tweets" WHERE "username" = 'jochasinga' AND "id" = bd48ac00-8310-11e5-985d-dd516b67e698;
SELECT * FROM "user_tweets" WHERE "username" = 'jochasinga' AND "tweet" = '@banksy thanks I'll try to check it out!'

Secondary Indexes

SELECT "email" FROM "user_tweets" WHERE "username" = 'jochasinga';
SELECT * FROM "user_tweets" WHERE "hashtags" CONTAINS "funnycats";
ALTER TABLE "user_tweets" ADD "hashtags" list<text>;
UPDATE "user_tweets" SET "hashtags" = ['art', 'graffiti', 'nyc'] WHERE "username" = 'jochasinga' AND "id" = bd48ac00-8310-11e5-985d-dd516b67e698;UPDATE "user_tweets" SET "hashtags" = ['rad'] WHERE "username" = 'banksy' AND "id" = 76e7a4d0-e796-11e3-90ce-5f98e903bf02;UPDATE "user_tweets" SET "hashtags" = ['sad', 'HopeForUkraine'] WHERE "username" = 'random_may' AND "id" = fa507380-8310-11e5-985d-dd516b67e698;
CREATE INDEX ON "user_tweets" ("hashtags")
SELECT * FROM "user_tweets" WHERE "hashtags" CONTAINS 'art';

Conclusion

CREATE TABLE "my_users" (
id uuid,
email text,
password blob,
country text,
PRIMARY KEY (country, id)
);
CREATE TABLE "my_users" (
id uuid,
email text,
password blob,
city text,
country text,
PRIMARY KEY ((country, city), id)
);

--

--

Thoughts, notes, and everything in between for developers’ enlightenment.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
pancy

I’m interested in Web3 and machine learning, and helping ambitious people. I like programming in Ocaml and Rust. I angel invest sometimes.