notebook.bin 0100644 0000000 0000000 00000026002 13561151073 012121 0 ustar 00 0000000 0000000 json_notebook_v1 {"1":"00000000-0000-0000-0000-000000000007","10":"eb42732f-78c2-45be-b549-d95d0832761e","11":"Notebook 7 - Completing KillrVideo Solutions","12":{"1":1572973454,"2":668000000},"13":{"1":1573179931,"2":402000000},"14":false,"15":[{"1":"e3344804-5259-44f6-b7cd-b2782fae0a90","10":4,"11":"
![CompletingKillrVideo](https://s3.amazonaws.com/datastaxtraining/CaaS/CompletingKillrVideo.png \"CodingSplash\" )\n\n# **Completing KillrVideo Solutions Notebook**","12":"markdown","13":{"1":"9cb937a6-40d9-4330-90b3-e085f81dabbb","10":{"9":"\nCompleting KillrVideo Solutions Notebook
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"6fee27ad-0ab2-474c-b6f4-f73b8f99ff8e","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Set Up the Notebook\n","12":"markdown","13":{"1":"1cf0ac55-83f8-4972-8dcb-c6635b415655","10":{"9":"\nSet Up the Notebook
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"1286c137-6893-473d-8dd4-220b27a0f384","11":"//CREATE KEYSPACE IF NOT EXISTS killrvideo WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };\n\nuse killrvideo;\n\n// Remove this section after DROP bug is fixed (https://datastax.jira.com/browse/CP-3499)\n// Note we create the tables and then drop them so we can recreate them.\n// This assures us the tables have the correct configuration.\n// If we only did the CREATE TABLE IF NOT EXISTS, we might end up with a table with the wrong columns or something...\nCREATE TABLE IF NOT EXISTS user_credentials (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS users (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS videos (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS user_videos (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS latest_videos (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS video_ratings (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS video_ratings_by_user (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS video_playback_stats (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS video_recommendations (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS video_recommendations_by_video (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS videos_by_tag (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS tags_by_letter (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS comments_by_video (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS comments_by_user (key text, PRIMARY KEY(key));\nCREATE TABLE IF NOT EXISTS kv_init_done (key text, PRIMARY KEY(key));\n// END removable section\n\nDROP TABLE IF EXISTS user_credentials;\nDROP TABLE IF EXISTS users;\nDROP TABLE IF EXISTS videos;\nDROP TABLE IF EXISTS user_videos;\nDROP TABLE IF EXISTS latest_videos;\nDROP TABLE IF EXISTS video_ratings;\nDROP TABLE IF EXISTS video_ratings_by_user;\nDROP TABLE IF EXISTS video_playback_stats;\nDROP TABLE IF EXISTS video_recommendations;\nDROP TABLE IF EXISTS video_recommendations_by_video;\nDROP TABLE IF EXISTS videos_by_tag;\nDROP TABLE IF EXISTS tags_by_letter;\nDROP TABLE IF EXISTS comments_by_video;\nDROP TABLE IF EXISTS comments_by_user;\nDROP TABLE IF EXISTS kv_init_done;\n\n\n// User credentials, keyed by email address so we can authenticate\nCREATE TABLE IF NOT EXISTS user_credentials (\n email text,\n password text,\n userid uuid,\n PRIMARY KEY (email)\n);\n\n// Users keyed by id\nCREATE TABLE IF NOT EXISTS users (\n userid uuid,\n firstname text,\n lastname text,\n email text,\n created_date timestamp,\n PRIMARY KEY (userid)\n);\n\n// Videos by id\nCREATE TABLE IF NOT EXISTS videos (\n videoid uuid,\n userid uuid,\n name text,\n description text,\n location text,\n location_type int,\n preview_image_location text,\n tags set,\n added_date timestamp,\n PRIMARY KEY (videoid)\n);\n\n// One-to-many from user point of view (lookup table)\nCREATE TABLE IF NOT EXISTS user_videos (\n userid uuid,\n added_date timestamp,\n videoid uuid,\n name text,\n preview_image_location text,\n PRIMARY KEY (userid, added_date, videoid)\n) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);\n\n// Track latest videos, grouped by day (if we ever develop a bad hotspot from the daily grouping here, we could mitigate by\n// splitting the row using an arbitrary group number, making the partition key (yyyymmdd, group_number))\nCREATE TABLE IF NOT EXISTS latest_videos (\n yyyymmdd text,\n added_date timestamp,\n videoid uuid,\n userid uuid,\n name text,\n preview_image_location text,\n PRIMARY KEY (yyyymmdd, added_date, videoid)\n) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);\n\n// Video ratings (counter table)\nCREATE TABLE IF NOT EXISTS video_ratings (\n videoid uuid,\n rating_counter counter,\n rating_total counter,\n PRIMARY KEY (videoid)\n);\n\n// Video ratings by user (to try and mitigate voting multiple times)\nCREATE TABLE IF NOT EXISTS video_ratings_by_user (\n videoid uuid,\n userid uuid,\n rating int,\n PRIMARY KEY (videoid, userid)\n);\n\n// Records the number of views/playbacks of a video\nCREATE TABLE IF NOT EXISTS video_playback_stats (\n videoid uuid,\n views counter,\n PRIMARY KEY (videoid)\n);\n\n// Recommendations by user (powered by Spark), with the newest videos added to the site always first\nCREATE TABLE IF NOT EXISTS video_recommendations ( \n userid uuid,\n added_date timestamp,\n videoid uuid,\n rating float,\n authorid uuid,\n name text,\n preview_image_location text,\n PRIMARY KEY(userid, added_date, videoid)\n) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);\n\n// Recommendations by video (powered by Spark)\nCREATE TABLE IF NOT EXISTS video_recommendations_by_video (\n videoid uuid,\n userid uuid,\n rating float,\n added_date timestamp STATIC,\n authorid uuid STATIC,\n name text STATIC,\n preview_image_location text STATIC,\n PRIMARY KEY(videoid, userid)\n);\n\n// Index for tag keywords\nCREATE TABLE IF NOT EXISTS videos_by_tag (\n tag text,\n videoid uuid,\n added_date timestamp,\n userid uuid,\n name text,\n preview_image_location text,\n tagged_date timestamp,\n PRIMARY KEY (tag, videoid)\n);\n\n// Index for tags by first letter in the tag\nCREATE TABLE IF NOT EXISTS tags_by_letter (\n first_letter text,\n tag text,\n PRIMARY KEY (first_letter, tag)\n);\n\n// Comments for a given video\nCREATE TABLE IF NOT EXISTS comments_by_video (\n videoid uuid,\n commentid timeuuid,\n userid uuid,\n comment text,\n PRIMARY KEY (videoid, commentid)\n) WITH CLUSTERING ORDER BY (commentid DESC);\n\n// Comments for a given user\nCREATE TABLE IF NOT EXISTS comments_by_user (\n userid uuid,\n commentid timeuuid,\n videoid uuid,\n comment text,\n PRIMARY KEY (userid, commentid)\n) WITH CLUSTERING ORDER BY (commentid DESC);\n\nCREATE TABLE killrvideo.kv_init_done (is_true boolean, Primary Key(is_true));\nINSERT INTO killrvideo.kv_init_done (is_true) VALUES(true);","12":"cql","16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"c086ebf7-f511-4f87-b014-1e4b930ac029","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# KillrVideo Registration and Login\n\n\n#### Execute the following two cells to inspect the contents of the tables - verify your inserts worked!","12":"markdown","13":{"1":"8afcd721-4739-410f-9a38-14eb99db842a","10":{"9":"\nKillrVideo Registration and Login
\nExecute the following two cells to inspect the contents of the tables - verify your inserts worked!
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"a6cc83d4-37f0-401a-b528-3b439ac0f171","11":"// Execute this cell (click on Run in the top-right corner) to see the contents of the user_credentials table\nSELECT * FROM killrvideo.user_credentials;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"c273e9d2-5aa5-4d7d-aefd-8ae98755036f","11":"// Execute this cell (click on Run in the top-right corner) to see the contents of the users table\nSELECT * FROM killrvideo.users;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"fdd3b88c-1c41-47d7-88ae-e2ca434dd0e7","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Congratulations, You Did It!!!!\n\n#### You completed KillrVideo and finished the course!\n\n
You may want to learn more at DataStax Academy.\n\n
\n\nLet's celebrate! Click here.
\n\n \n","12":"markdown","13":{"1":"32950725-631b-416b-b7b3-d31f036e1f03","10":{"9":"\nCongratulations, You Did It!!!!
\nYou completed KillrVideo and finished the course!
\n\n
You may want to learn more at DataStax Academy.\n\n
\n
\n
Let's celebrate! Click here.
\n\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"}],"16":{"1":{}},"17":"","19":false} code.txt 0100644 0000000 0000000 00000020031 13561151073 011256 0 ustar 00 0000000 0000000 --------------------NOTEBOOK_Notebook 7 - Completing KillrVideo Solutions--------------------
--------------------CELL_MARKDOWN_1--------------------
![CompletingKillrVideo](https://s3.amazonaws.com/datastaxtraining/CaaS/CompletingKillrVideo.png "CodingSplash" )
# **Completing KillrVideo Solutions Notebook**
--------------------CELL_MARKDOWN_2--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Set Up the Notebook
--------------------CELL_CQL_3--------------------
//CREATE KEYSPACE IF NOT EXISTS killrvideo WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 };
use killrvideo;
// Remove this section after DROP bug is fixed (https://datastax.jira.com/browse/CP-3499)
// Note we create the tables and then drop them so we can recreate them.
// This assures us the tables have the correct configuration.
// If we only did the CREATE TABLE IF NOT EXISTS, we might end up with a table with the wrong columns or something...
CREATE TABLE IF NOT EXISTS user_credentials (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS users (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS videos (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS user_videos (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS latest_videos (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_ratings (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_ratings_by_user (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_playback_stats (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_recommendations (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS video_recommendations_by_video (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS videos_by_tag (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS tags_by_letter (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS comments_by_video (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS comments_by_user (key text, PRIMARY KEY(key));
CREATE TABLE IF NOT EXISTS kv_init_done (key text, PRIMARY KEY(key));
// END removable section
DROP TABLE IF EXISTS user_credentials;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS videos;
DROP TABLE IF EXISTS user_videos;
DROP TABLE IF EXISTS latest_videos;
DROP TABLE IF EXISTS video_ratings;
DROP TABLE IF EXISTS video_ratings_by_user;
DROP TABLE IF EXISTS video_playback_stats;
DROP TABLE IF EXISTS video_recommendations;
DROP TABLE IF EXISTS video_recommendations_by_video;
DROP TABLE IF EXISTS videos_by_tag;
DROP TABLE IF EXISTS tags_by_letter;
DROP TABLE IF EXISTS comments_by_video;
DROP TABLE IF EXISTS comments_by_user;
DROP TABLE IF EXISTS kv_init_done;
// User credentials, keyed by email address so we can authenticate
CREATE TABLE IF NOT EXISTS user_credentials (
email text,
password text,
userid uuid,
PRIMARY KEY (email)
);
// Users keyed by id
CREATE TABLE IF NOT EXISTS users (
userid uuid,
firstname text,
lastname text,
email text,
created_date timestamp,
PRIMARY KEY (userid)
);
// Videos by id
CREATE TABLE IF NOT EXISTS videos (
videoid uuid,
userid uuid,
name text,
description text,
location text,
location_type int,
preview_image_location text,
tags set,
added_date timestamp,
PRIMARY KEY (videoid)
);
// One-to-many from user point of view (lookup table)
CREATE TABLE IF NOT EXISTS user_videos (
userid uuid,
added_date timestamp,
videoid uuid,
name text,
preview_image_location text,
PRIMARY KEY (userid, added_date, videoid)
) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);
// Track latest videos, grouped by day (if we ever develop a bad hotspot from the daily grouping here, we could mitigate by
// splitting the row using an arbitrary group number, making the partition key (yyyymmdd, group_number))
CREATE TABLE IF NOT EXISTS latest_videos (
yyyymmdd text,
added_date timestamp,
videoid uuid,
userid uuid,
name text,
preview_image_location text,
PRIMARY KEY (yyyymmdd, added_date, videoid)
) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);
// Video ratings (counter table)
CREATE TABLE IF NOT EXISTS video_ratings (
videoid uuid,
rating_counter counter,
rating_total counter,
PRIMARY KEY (videoid)
);
// Video ratings by user (to try and mitigate voting multiple times)
CREATE TABLE IF NOT EXISTS video_ratings_by_user (
videoid uuid,
userid uuid,
rating int,
PRIMARY KEY (videoid, userid)
);
// Records the number of views/playbacks of a video
CREATE TABLE IF NOT EXISTS video_playback_stats (
videoid uuid,
views counter,
PRIMARY KEY (videoid)
);
// Recommendations by user (powered by Spark), with the newest videos added to the site always first
CREATE TABLE IF NOT EXISTS video_recommendations (
userid uuid,
added_date timestamp,
videoid uuid,
rating float,
authorid uuid,
name text,
preview_image_location text,
PRIMARY KEY(userid, added_date, videoid)
) WITH CLUSTERING ORDER BY (added_date DESC, videoid ASC);
// Recommendations by video (powered by Spark)
CREATE TABLE IF NOT EXISTS video_recommendations_by_video (
videoid uuid,
userid uuid,
rating float,
added_date timestamp STATIC,
authorid uuid STATIC,
name text STATIC,
preview_image_location text STATIC,
PRIMARY KEY(videoid, userid)
);
// Index for tag keywords
CREATE TABLE IF NOT EXISTS videos_by_tag (
tag text,
videoid uuid,
added_date timestamp,
userid uuid,
name text,
preview_image_location text,
tagged_date timestamp,
PRIMARY KEY (tag, videoid)
);
// Index for tags by first letter in the tag
CREATE TABLE IF NOT EXISTS tags_by_letter (
first_letter text,
tag text,
PRIMARY KEY (first_letter, tag)
);
// Comments for a given video
CREATE TABLE IF NOT EXISTS comments_by_video (
videoid uuid,
commentid timeuuid,
userid uuid,
comment text,
PRIMARY KEY (videoid, commentid)
) WITH CLUSTERING ORDER BY (commentid DESC);
// Comments for a given user
CREATE TABLE IF NOT EXISTS comments_by_user (
userid uuid,
commentid timeuuid,
videoid uuid,
comment text,
PRIMARY KEY (userid, commentid)
) WITH CLUSTERING ORDER BY (commentid DESC);
CREATE TABLE killrvideo.kv_init_done (is_true boolean, Primary Key(is_true));
INSERT INTO killrvideo.kv_init_done (is_true) VALUES(true);
--------------------CELL_MARKDOWN_4--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# KillrVideo Registration and Login
#### Execute the following two cells to inspect the contents of the tables - verify your inserts worked!
--------------------CELL_CQL_5--------------------
// Execute this cell (click on Run in the top-right corner) to see the contents of the user_credentials table
SELECT * FROM killrvideo.user_credentials;
--------------------CELL_CQL_6--------------------
// Execute this cell (click on Run in the top-right corner) to see the contents of the users table
SELECT * FROM killrvideo.users;
--------------------CELL_MARKDOWN_7--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Congratulations, You Did It!!!!
#### You completed KillrVideo and finished the course!
You may want to learn more at DataStax Academy.
Let's celebrate! Click here.
versions-info.txt 0100644 0000000 0000000 00000000051 13561151073 013145 0 ustar 00 0000000 0000000 Studio Version: 6.8.0-201909270000-CLOUD