notebook.bin 0100644 0000000 0000000 00000173734 13576510506 012146 0 ustar 00 0000000 0000000 json_notebook_v1 {"1":"f39ff822-874f-4da3-b2ca-d52ca172e615","10":"c9e3d8be-a789-4e25-88dc-c2748c4aa73e","11":"Notebook 5 - Advanced Data Types","12":{"1":1576627850,"2":59000000},"13":{"1":1576702249,"2":967000000},"14":false,"15":[{"1":"51a7de07-b511-4a31-b42d-1f0f6e4c23b0","10":4,"11":"
,\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\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(11111111-1111-1111-1111-111111111111, toTimestamp(now()), 'Jeff', 'Carpenter', 'jc@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(11111111-1111-1111-1111-111111111111, 'jc@datastax.com', 'J3ffL0v3$C@ss@ndr@');\n \nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(22222222-2222-2222-2222-222222222222, toTimestamp(now()), 'Eric', 'Zietlow', 'ez@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(22222222-2222-2222-2222-222222222222, 'ez@datastax.com', 'C@ss@ndr@R0ck$');\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(33333333-3333-3333-3333-333333333333, toTimestamp(now()), 'Cedrick', 'Lunven', 'cl@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(33333333-3333-3333-3333-333333333333, 'cl@datastax.com', 'Fr@nc3L0v3$C@ss@ndr@');\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(44444444-4444-4444-4444-444444444444, toTimestamp(now()), 'David', 'Gilardi', 'dg@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(44444444-4444-4444-4444-444444444444, 'dg@datastax.com', 'H@t$0ff2C@ss@ndr@');\n\n//INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n// VALUES(55555555-5555-5555-5555-555555555555, toTimestamp(now()), 'Cristina', 'Veale', 'cv@datastax.com');\n//INSERT INTO killrvideo.user_credentials (userid, email, password)\n// VALUES(55555555-5555-5555-5555-555555555555, 'cv@datastax.com', '3@$tC0@$tC@ss@ndr@');\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(66666666-6666-6666-6666-666666666666, toTimestamp(now()), 'Adron', 'Hall', 'ah@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(66666666-6666-6666-6666-666666666666, 'ah@datastax.com', 'C@ss@ndr@43v3r');\n\nINSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)\n VALUES(77777777-7777-7777-7777-777777777777, toTimestamp(now()), 'Aleks', 'volochnev', 'av@datastax.com');\nINSERT INTO killrvideo.user_credentials (userid, email, password)\n VALUES(77777777-7777-7777-7777-777777777777, 'av@datastax.com', 'C@ss@ndr@3v3rywh3r3');\n","12":"cql","16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"41c90c9e-00c4-48f6-bd94-9bfcd878e574","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Collection Types\n\n### In this section, you will do the following things:\n- #### Investigate `SET`, which is one of the collection types\n- #### Insert and retrieve rows in the `videos` table that use `SET`\n\n
\n\n#### The `videos` table uses a `SET` collection to keep track of tags associated with each video. A `SET` is a great collection to use because sets do not maintain an order - we are not concerned with any tag order, only if a tag is or is not associated with the video.\n#### Let's start by reviewing the definition of the `videos` table:\n\n
\n#### Step 1: Execute the following cell to describe the `videos` table. \n
","12":"markdown","13":{"1":"5332fc85-5b32-444f-ae9e-dc7b74282a7b","10":{"9":"\nCollection Types
\nIn this section, you will do the following things:
\n\nInvestigate SET
, which is one of the collection types
\n \nInsert and retrieve rows in the videos
table that use SET
\n \n
\n
\nThe videos
table uses a SET
collection to keep track of tags associated with each video. A SET
is a great collection to use because sets do not maintain an order - we are not concerned with any tag order, only if a tag is or is not associated with the video.
\nLet's start by reviewing the definition of the videos
table:
\n
\nStep 1: Execute the following cell to describe the videos
table.
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"b3be83e8-e24c-4e92-98f2-573b272dc5ee","11":"// Execute this cell (click the Run button in the top-right corner)\nDESCRIBE TABLE killrvideo.videos;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"5e653036-fff1-440f-9332-1c5662aa8bf3","10":4,"11":"#### Note two things about the `videos` table. First, the primary key is just `videoid`. Second, the `tags` column is a set of text. Tags are words or phrases we want to associate with a video.\n#### To allow us to keep our focus on `SET`, in this example we will only specify the `videoid` and the `tags`. Once again, let's use our contrived `uuid` of `12121212-1212-1212-1212-121212121212`.\n
\n#### Step 2: In the following cell, insert a sparse row into the videos table with a `videoid` of `12121212-1212-1212-1212-121212121212` and a set of tags that contain the words: `Favorite`, `Fast-paced`, `Funny`. \n\n
\n\n\nNeed a hint? Click here.
\n> You want to `INSERT` into the `killrvideo.videos` table with a `videoid` of `12121212-1212-1212-1212-121212121212` and a set of tags such as `{ 'Favorite', 'Fast-paced', 'Funny' }`.\n \n\n\nWant the command? Click here.
\n> ```\nINSERT INTO killrvideo.videos (videoid, tags)\n VALUES(12121212-1212-1212-1212-121212121212, { 'Favorite', 'Fast-paced', 'Funny' });\n```\n \n","12":"markdown","13":{"1":"3f4411e2-5697-4d5f-9ff7-c5c687eb3b21","10":{"9":"Note two things about the videos
table. First, the primary key is just videoid
. Second, the tags
column is a set of text. Tags are words or phrases we want to associate with a video.
\nTo allow us to keep our focus on SET
, in this example we will only specify the videoid
and the tags
. Once again, let's use our contrived uuid
of 12121212-1212-1212-1212-121212121212
.
\n
\nStep 2: In the following cell, insert a sparse row into the videos table with a videoid
of 12121212-1212-1212-1212-121212121212
and a set of tags that contain the words: Favorite
, Fast-paced
, Funny
.
\n
\n\n
Need a hint? Click here.
\nYou want to INSERT
into the killrvideo.videos
table with a videoid
of 12121212-1212-1212-1212-121212121212
and a set of tags such as { 'Favorite', 'Fast-paced', 'Funny' }
.\n
\n
\n\n
Want the command? Click here.
\nINSERT INTO killrvideo.videos (videoid, tags)\n VALUES(12121212-1212-1212-1212-121212121212, { 'Favorite', 'Fast-paced', 'Funny' });\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"105d405e-0f9e-4ed1-8702-4ce645f2c2c5","11":"// Write a command to insert a row into the videos table\n// Execute this cell (click the Run button in the top-right corner)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"ca6a0da8-2432-44e4-8761-c3b2f06aa22e","10":4,"11":"#### Now, let's check to see if our insert worked as expected.\n
\n#### Step 3: Execute the following cell to query for the row with the `videoid` of `12121212-1212-1212-1212-121212121212`. \n\n
\n","12":"markdown","13":{"1":"aa1aa599-3b34-4e03-9123-58f3d9d5309c","10":{"9":"Now, let's check to see if our insert worked as expected.
\n
\nStep 3: Execute the following cell to query for the row with the videoid
of 12121212-1212-1212-1212-121212121212
.
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"e9eae355-4dde-402d-bcc9-b32d84e11eb4","11":"// Execute this cell (click the Run button in the top-right corner)\nSELECT * FROM killrvideo.videos WHERE videoid = 12121212-1212-1212-1212-121212121212;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"3e29c634-b687-41f4-bed8-f5e33fb29107","10":4,"11":"#### Inspect the `tags` values and see that the `INSERT` worked as expected.\n
\n#### There are two kinds of `SET` updates we could perform. We can completely replace a set, or we can modify the contents of an existing set. First, we'll replace the entire `tags` set with the values `High-brow`, `Intellectual` and `Refined`.\n\n
\n#### Step 4: In the following cell, write a comand to replace the `tags` set for the `videoid` of `12121212-1212-1212-1212-121212121212`. \n\n
\n\n\nNeed a hint? Click here.
\n> You want to `UPDATE` the `killrvideo.videos` table with a `videoid` of `12121212-1212-1212-1212-121212121212`. `SET` the `tags` value to the new set `{ 'High-brow', 'Intellectual', 'Refined' }`.\n \n\n\nWant the command? Click here.
\n> ```\nUPDATE killrvideo.videos SET tags = { 'High-brow', 'Intellectual', 'Refined' } WHERE videoid = 12121212-1212-1212-1212-121212121212;\n```\n ","12":"markdown","13":{"1":"053c6163-1d13-4ed6-8bc1-26b456225f16","10":{"9":"Inspect the tags
values and see that the INSERT
worked as expected.
\n
\nThere are two kinds of SET
updates we could perform. We can completely replace a set, or we can modify the contents of an existing set. First, we'll replace the entire tags
set with the values High-brow
, Intellectual
and Refined
.
\n
\nStep 4: In the following cell, write a comand to replace the tags
set for the videoid
of 12121212-1212-1212-1212-121212121212
.
\n
\n\n
Need a hint? Click here.
\nYou want to UPDATE
the killrvideo.videos
table with a videoid
of 12121212-1212-1212-1212-121212121212
. SET
the tags
value to the new set { 'High-brow', 'Intellectual', 'Refined' }
.\n
\n
\n\n
Want the command? Click here.
\nUPDATE killrvideo.videos SET tags = { 'High-brow', 'Intellectual', 'Refined' } WHERE videoid = 12121212-1212-1212-1212-121212121212;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"7675ab56-186d-4692-8b18-7a9698ed36f3","11":"// Write a command to update the row from the videos table\n// Execute this cell (click the Run button in the top-right corner)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"13e60e0a-dfbf-405b-9b5e-282a29a4e58c","10":4,"11":"#### Once again, let's inspect the effect of the `UPDATE`.\n
\n#### Step 5: Execute the following cell - a query to retrieve the row for the `videoid` of `12121212-1212-1212-1212-121212121212`.\n
\n","12":"markdown","13":{"1":"52d71ad6-1922-4a62-8726-96a6ec134e56","10":{"9":"Once again, let's inspect the effect of the UPDATE
.
\n
\nStep 5: Execute the following cell - a query to retrieve the row for the videoid
of 12121212-1212-1212-1212-121212121212
.
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"7608803e-414c-41f8-ac87-61eff5bc6d41","11":"// Execute this cell (click the Run button in the top-right corner)\nSELECT * FROM killrvideo.videos WHERE videoid = 12121212-1212-1212-1212-121212121212;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"06845b3a-df8a-46af-8c66-e5236607c6a6","10":4,"11":"#### We see the values we updated in Step 3. The values may not be in the same order as in your `UPDATE` command, but that's OK.\n
\n\n#### Thought question: If you _were_ concerned about the order of the tags, what data type would you use instead of a `SET`?\n
\n#### Let's modify the set again. This time we will remove the `Refined` tag. Then in later steps we will replace it with `Low-rent`.\n
\n#### Step 6: In the following cell, write a command to update, by removing the `Refined` tag, for the `videoid` of `12121212-1212-1212-1212-121212121212`.\n
\n\n\nNeed a hint? Click here.
\n> Here, you will use an `UPDATE` command. Again, we are updating the row in the `killrvideo.videos` table with the `videoid` of `12121212-1212-1212-1212-121212121212`. The clause you use to remove the tag looks like `tags = tags - { 'Refined' }`.\n \n\n\nWant the command? Click here.
\n> ```\nUPDATE killrvideo.videos SET tags = tags - { 'Refined' } WHERE videoid = 12121212-1212-1212-1212-121212121212;\n```\n ","12":"markdown","13":{"1":"52fbf2b7-ac01-4a3b-b053-2a77fe5577f5","10":{"9":"We see the values we updated in Step 3. The values may not be in the same order as in your UPDATE
command, but that's OK.
\n
\nThought question: If you were concerned about the order of the tags, what data type would you use instead of a SET
?
\n
\nLet's modify the set again. This time we will remove the Refined
tag. Then in later steps we will replace it with Low-rent
.
\n
\nStep 6: In the following cell, write a command to update, by removing the Refined
tag, for the videoid
of 12121212-1212-1212-1212-121212121212
.
\n
\n\n
Need a hint? Click here.
\nHere, you will use an UPDATE
command. Again, we are updating the row in the killrvideo.videos
table with the videoid
of 12121212-1212-1212-1212-121212121212
. The clause you use to remove the tag looks like tags = tags - { 'Refined' }
.\n
\n
\n\n
Want the command? Click here.
\nUPDATE killrvideo.videos SET tags = tags - { 'Refined' } WHERE videoid = 12121212-1212-1212-1212-121212121212;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"f69de72c-9447-45b1-bba5-405d63fdd0a7","11":"// Write a command to update the row from the videos table\n// Execute this cell (click the Run button in the top-right corner)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"c2a17761-b4d7-4899-b5f7-8662a877a758","10":4,"11":"#### Again, let's check the contents of the row to see the effects of our command.\n
\n#### Step 7: Execute the following cell - a query to retrieve the row for the `videoid` of `12121212-1212-1212-1212-121212121212`.\n
","12":"markdown","13":{"1":"61271a63-9fa8-412d-aad9-7b1dd6b30698","10":{"9":"Again, let's check the contents of the row to see the effects of our command.
\n
\nStep 7: Execute the following cell - a query to retrieve the row for the videoid
of 12121212-1212-1212-1212-121212121212
.
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"9616eb41-f6e5-440c-b94b-0a092eda23a7","11":"// Execute this cell (click the Run button in the top-right corner)\nSELECT * FROM killrvideo.videos WHERE videoid = 12121212-1212-1212-1212-121212121212;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"b342496c-24cb-4ed7-bbe8-84ab068e41a2","10":4,"11":"#### Inspecting the previous results, we see the row now only has two tags - that's what we wanted!\n
\n#### Let's add a third tag `Low-rent`.\n
\n#### Step 8: In the following cell, write a command to update, by adding the `Low-rent` tag, for the `videoid` of `12121212-1212-1212-1212-121212121212`.\n
\n\n\nNeed a hint? Click here.
\n> Here, you will use an `UPDATE` command. Again, we are updating the row in the `killrvideo.videos` table with the `videoid` of `12121212-1212-1212-1212-121212121212`. The clause you use to remove the tag looks like `tags = tags + { 'Low-rent' }`.\n \n\n\nWant the command? Click here.
\n> ```\nUPDATE killrvideo.videos SET tags = tags + { 'Low-rent' } WHERE videoid = 12121212-1212-1212-1212-121212121212;\n```\n ","12":"markdown","13":{"1":"264f3938-29c7-4114-b333-312af48334c3","10":{"9":"Inspecting the previous results, we see the row now only has two tags - that's what we wanted!
\n
\nLet's add a third tag Low-rent
.
\n
\nStep 8: In the following cell, write a command to update, by adding the Low-rent
tag, for the videoid
of 12121212-1212-1212-1212-121212121212
.
\n
\n\n
Need a hint? Click here.
\nHere, you will use an UPDATE
command. Again, we are updating the row in the killrvideo.videos
table with the videoid
of 12121212-1212-1212-1212-121212121212
. The clause you use to remove the tag looks like tags = tags + { 'Low-rent' }
.\n
\n
\n\n
Want the command? Click here.
\nUPDATE killrvideo.videos SET tags = tags + { 'Low-rent' } WHERE videoid = 12121212-1212-1212-1212-121212121212;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"68bdf7b7-8d60-4e27-96f1-4eea39806f46","11":"// Write a command to update the row from the videos table\n// Execute this cell (click the Run button in the top-right corner)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"b60f202b-226d-4482-98e4-9f400ce6518f","10":4,"11":"#### One last time, let's check the contents of the row to see the effects of our command.\n
\n#### Step 9: Execute the following cell.\n
","12":"markdown","13":{"1":"c73de78f-28a3-42e1-a895-e6ce68ad2424","10":{"9":"One last time, let's check the contents of the row to see the effects of our command.
\n
\nStep 9: Execute the following cell.
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"160d3ca5-a626-475c-b7dd-b0004df3a3bd","11":"// Execute this cell (click the Run button in the top-right corner)\nSELECT * FROM killrvideo.videos WHERE videoid = 12121212-1212-1212-1212-121212121212;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"3c3e9080-cf3b-4c55-9aab-43e22dfc89b0","10":4,"11":"#### Review the results in the previous cell to see that the update worked as expected.\n\n---\n\n Note (some things to keep in mind about Collections):
\n\nTo avoid performance problems, only use collections for small-ish numbers of elements\nSets and maps do not incur the read-before-write penalty, but some list operations do. Therefore, when possible, prefer sets to lists\nList prepend and append operations are not idempotent, so retrying after a timeout may result in duplicate elements\nCollections may only be used in primary keys if they are frozen\n
\n\n---\n","12":"markdown","13":{"1":"b7c9b7f0-c678-410a-ae1c-14c814ece215","10":{"9":"Review the results in the previous cell to see that the update worked as expected.
\n
\n Note (some things to keep in mind about Collections):
\n\nTo avoid performance problems, only use collections for small-ish numbers of elements\nSets and maps do not incur the read-before-write penalty, but some list operations do. Therefore, when possible, prefer sets to lists\nList prepend and append operations are not idempotent, so retrying after a timeout may result in duplicate elements\nCollections may only be used in primary keys if they are frozen\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"53280e03-cfbb-41fa-b728-ade21651b7de","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Counters\n\n### In this section, you will do the following things:\n- #### Investigate the `video_playback_stats` table in `killrvideo`\n- #### Add a row to the `video_playback_stats` table\n- #### Increment the counter of the table to simulate videoing a video\n\n
\n#### KillrVideo uses the `video_playback_stats` table to keep track of the number of times a video has been viewed. A counter is a great data type for this use-case because counters perform well in Cassandra, and in the rare event where the counter might drop an update, it is not a serious problem for the app or its users.\n
\n#### Let's start by investigating this table.\n\n
\n#### Step 1: In the following cell, describe the `video_playback_stats` table.\n\n\nNeed a hint? Click here.
\n> You want to use the `DESCRIBE` command to describe only the table.\n \n\n\nWant the command? Click here.
\n> ```\nDESCRIBE TABLE killrvideo.video_playback_stats;\n```\n \n","12":"markdown","13":{"1":"ad689520-c4bc-4841-8d10-17f849ecdc9b","10":{"9":"\nCounters
\nIn this section, you will do the following things:
\n\nInvestigate the video_playback_stats
table in killrvideo
\n \nAdd a row to the video_playback_stats
table
\n \nIncrement the counter of the table to simulate videoing a video
\n \n
\n
\nKillrVideo uses the video_playback_stats
table to keep track of the number of times a video has been viewed. A counter is a great data type for this use-case because counters perform well in Cassandra, and in the rare event where the counter might drop an update, it is not a serious problem for the app or its users.
\n
\nLet's start by investigating this table.
\n
\nStep 1: In the following cell, describe the video_playback_stats
table.
\n\n
Need a hint? Click here.
\nYou want to use the DESCRIBE
command to describe only the table.\n
\n
\n\n
Want the command? Click here.
\nDESCRIBE TABLE killrvideo.video_playback_stats;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"6266c17b-7f29-4cb8-a29c-1ff8720b81f8","11":"// Write a command to allow you to review the table definition for video_playback_stats\n// Execute this cell (click the Run button in the top-right corner)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"ec3ee055-8090-4d83-9e5c-b2cef6ac6dd0","10":4,"11":"#### Notice that this table has two columns: `videoid` and `views`. Also, notice that `views` is a counter that keeps track of how many times the video has been, uh, viewed.\n#### In this section, we want to update a counter. Just to keep things simple, let's use a contrived `uuid` for the `videoid`. We'll use the value `12121212-1212-1212-1212-121212121212`.\n
\n#### We'll start by verifying that a row for this `videoid` does not yet exist in the table.\n
\n#### Step 2: In the following cell, try to retrieve the row with the `videoid` of `12121212-1212-1212-1212-121212121212`.\n\n\nNeed a hint? Click here.
\n> For this command, you will use the `SELECT` statement on the `video_playback_stats` table in the `killrvideo` keyspace. You only want the row where the `videoid` is `12121212-1212-1212-1212-121212121212`, but it will be easiest just to grab all the columns.\n \n\n\nWant the command? Click here.
\n> ```\nSELECT * from killrvideo.video_playback_stats WHERE videoid = 12121212-1212-1212-1212-121212121212;\n```\n ","12":"markdown","13":{"1":"3a335e90-567d-4a8f-9c06-a5dab2b3bbd3","10":{"9":"Notice that this table has two columns: videoid
and views
. Also, notice that views
is a counter that keeps track of how many times the video has been, uh, viewed.
\nIn this section, we want to update a counter. Just to keep things simple, let's use a contrived uuid
for the videoid
. We'll use the value 12121212-1212-1212-1212-121212121212
.
\n
\nWe'll start by verifying that a row for this videoid
does not yet exist in the table.
\n
\nStep 2: In the following cell, try to retrieve the row with the videoid
of 12121212-1212-1212-1212-121212121212
.
\n\n
Need a hint? Click here.
\nFor this command, you will use the SELECT
statement on the video_playback_stats
table in the killrvideo
keyspace. You only want the row where the videoid
is 12121212-1212-1212-1212-121212121212
, but it will be easiest just to grab all the columns.\n
\n
\n\n
Want the command? Click here.
\nSELECT * from killrvideo.video_playback_stats WHERE videoid = 12121212-1212-1212-1212-121212121212;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"74ba81cb-ed57-42bc-8f06-548f9aa6918f","11":"// Write a command to try to retrieve the row with a videoid of 12121212-1212-1212-1212-121212121212\n// Execute this cell (click the Run button in the top-right corner)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"fb1abdec-ce6b-4dd3-9e93-d835e7c06d38","10":4,"11":"#### We see \"No Data Returned\" which allows us to verify that the row with that key is not in the table yet.\n#### Let's try creating the row with that `videoid`. \n
\n#### Step 3: Create the row by incrementing the `views` counter for the `videoid` `12121212-1212-1212-1212-121212121212`.\n\n\nNeed a hint? Click here.
\n> You will be `UPDATE`ing the row in the `killrvideo.video_playback_stats` with the `videoid` of `12121212-1212-1212-1212-121212121212`. You will increment the counter with code like this: `views = views + 1`.\n \n\n\nWant the command? Click here.
\n> ```\nUPDATE killrvideo.video_playback_stats SET views = views + 1 WHERE videoid = 12121212-1212-1212-1212-121212121212;\n```\n \n\n\n#### **Thought question:** Since we know we cannot `INSERT` a row with a counter, we can only `UPDATE` the row, what will the value of the counter be after we increment it?\n
","12":"markdown","13":{"1":"30f7a950-fb6c-4e72-bf78-dbda97d3a3bc","10":{"9":"We see “No Data Returned” which allows us to verify that the row with that key is not in the table yet.
\nLet's try creating the row with that videoid
.
\n
\nStep 3: Create the row by incrementing the views
counter for the videoid
12121212-1212-1212-1212-121212121212
.
\n\n
Need a hint? Click here.
\nYou will be UPDATE
ing the row in the killrvideo.video_playback_stats
with the videoid
of 12121212-1212-1212-1212-121212121212
. You will increment the counter with code like this: views = views + 1
.\n
\n
\n\n
Want the command? Click here.
\nUPDATE killrvideo.video_playback_stats SET views = views + 1 WHERE videoid = 12121212-1212-1212-1212-121212121212;\n
\n\n
\nThought question: Since we know we cannot INSERT
a row with a counter, we can only UPDATE
the row, what will the value of the counter be after we increment it?
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"408cf785-603a-4170-ad7e-9a0bb1379496","11":"// Write a command to update the views counter of the row with a videoid of 12121212-1212-1212-1212-121212121212\n// Execute this cell (click the Run button in the top-right corner)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"07229843-1ba4-491e-b6c1-2f46bd7a7bf4","10":4,"11":"#### Let's see the results of the update!\n
\n#### Step 4: Execute the following cell and inspect the results.\n
","12":"markdown","13":{"1":"040510a6-5042-4f2d-8c4b-61753052273e","10":{"9":"Let's see the results of the update!
\n
\nStep 4: Execute the following cell and inspect the results.
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"54f4f8dc-3cc8-4b52-819c-780a094b906e","11":"// Execute this cell (click the Run button in the top-right corner)\nSELECT * FROM killrvideo.video_playback_stats WHERE videoid = 12121212-1212-1212-1212-121212121212;","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"87d710de-76b0-4d6c-84c4-cdba90521a7b","10":4,"11":"#### This time we see the row. We created the row with the `UPDATE` command - an upsert! Notice that the value of the `views` counter is one. That's because, when you create a row by incrementing the counter, it's as if the counter started at zero.\n\n---\n\n Note (some things to keep in mind about counters):
\n\nCounters cannot be part of a primary key\nIncrementing or decrementing counters is not idempotent\nIncrementing or decrementing a counter is not always guaranteed to work - under high traffic situations, it is possible for one of these operations to get dropped\n
\n\n---\n","12":"markdown","13":{"1":"3e2590ca-890e-41b7-b238-35a653230b86","10":{"9":"This time we see the row. We created the row with the UPDATE
command - an upsert! Notice that the value of the views
counter is one. That's because, when you create a row by incrementing the counter, it's as if the counter started at zero.
\n
\n Note (some things to keep in mind about counters):
\n\nCounters cannot be part of a primary key\nIncrementing or decrementing counters is not idempotent\nIncrementing or decrementing a counter is not always guaranteed to work - under high traffic situations, it is possible for one of these operations to get dropped\n
\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"9483b8c2-1eaa-4fbc-93f3-410e4e479e75","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Congratulations!!!!\n\n#### If you have made it to the end of this notebook successfully, you have additional data types under your belt that you can use in your data type virtuoso!\n\n\n
You can learn about even more data types at DataStax Academy. The price? NADA!\n\n\n####\n\nWant to see another virtuoso? Click here.
\n\n ","12":"markdown","13":{"1":"4f864ac9-205a-4218-b907-6227e0102eea","10":{"9":"\nCongratulations!!!!
\nIf you have made it to the end of this notebook successfully, you have additional data types under your belt that you can use in your data type virtuoso!
\n\n
You can learn about even more data types at DataStax Academy. The price? NADA!\n
\n\n\n
Want to see another virtuoso? Click here.
\n\n\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.ONE"},{"1":"ea0cd084-7bed-4c6d-b8e1-b15a9aefe2a7","10":4,"11":"![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png \"line\" )\n# Bonus Challenge: Create a UDT\n### If you got done early and want something to do while you wait for others, here's a bonus challenge\n\n### In this section, you will do the following things:\n- #### Create a UDT to represent video encoding\n- #### Alter the KillrVideo `videos` table to use this UDT\n- #### Load some data into the altered `videos` table\n- #### Run a query on the loaded data with the UDT\n- #### Update the value of a row containing a UDT\n\n\n#### **Here's the pitch:**\n#### As KillrVideo grows in popularity, it becomes necessary to support various video formats with different bit rates, encodings and frame sizes. This seems like an ideal use for a UDT. Let's build the UDT and then add it to the `videos` table.\n\n\n#### Step 1: In the next cell, write and execute the CQL to create a UDT, named `video_encoding` that contains the fields as described in the following table.\n\n\n \n \n Field Name | \n Data Type | \n
\n \n bit_rates | \n SET<TEXT> | \n
\n \n encoding | \n TEXT | \n
\n \n height | \n INT | \n
\n \n width | \n INT | \n
\n
\n
\n\n\nWant the solution? Click here.
\n> ```\nCREATE TYPE IF NOT EXISTS killrvideo.video_encoding (\n bit_rates SET,\n encoding TEXT,\n height INT,\n width INT\n);\n```\n \n","12":"markdown","13":{"1":"6b83e129-6c89-466f-8f09-f8b5a591052c","10":{"9":"\nBonus Challenge: Create a UDT
\nIf you got done early and want something to do while you wait for others, here's a bonus challenge
\nIn this section, you will do the following things:
\n\nCreate a UDT to represent video encoding
\n \nAlter the KillrVideo videos
table to use this UDT
\n \nLoad some data into the altered videos
table
\n \nRun a query on the loaded data with the UDT
\n \nUpdate the value of a row containing a UDT
\n \n
\nHere's the pitch:
\nAs KillrVideo grows in popularity, it becomes necessary to support various video formats with different bit rates, encodings and frame sizes. This seems like an ideal use for a UDT. Let's build the UDT and then add it to the videos
table.
\nStep 1: In the next cell, write and execute the CQL to create a UDT, named video_encoding
that contains the fields as described in the following table.
\n\n \n \n Field Name | \n Data Type | \n
\n \n bit_rates | \n SET<TEXT> | \n
\n \n encoding | \n TEXT | \n
\n \n height | \n INT | \n
\n \n width | \n INT | \n
\n
\n
\n\n
Want the solution? Click here.
\nCREATE TYPE IF NOT EXISTS killrvideo.video_encoding (\n bit_rates SET<TEXT>,\n encoding TEXT,\n height INT,\n width INT\n);\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"21d2fb06-4b2c-468b-b436-3f911ff14798","11":"// Create the video_encoding UDT in this cell as described.\n// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"3b06ff27-33b0-4bdf-bd75-5c9613262107","10":4,"11":"#### Step 2: In the next cell, truncate the contents of the `videos` table to prepare it to receive the data with the encoding.\n\n\nWant the solution? Click here.
\n> ```\nTRUNCATE TABLE killrvideo.videos;\n```\n \n","12":"markdown","13":{"1":"a80670ee-ebd9-4d28-9764-adb8131c2dfd","10":{"9":"Step 2: In the next cell, truncate the contents of the videos
table to prepare it to receive the data with the encoding.
\n\n
Want the solution? Click here.
\nTRUNCATE TABLE killrvideo.videos;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"369b41e5-fa05-4638-abc1-fb9cee779200","11":"// Write the CQL to truncate the videos table\n// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"0962b7d2-03c1-4549-87b9-ab517f9729b6","10":4,"11":"#### Step 3: In the next cell, alter the `videos` table by adding an `encodings` column of type `FROZEN`.\n\n---\n> #### **NOTE:** UDTs that contain containers (e.g., sets, lists, etc.) must be declared `FROZEN` to make it explicit that Cassandra serializes the contents of the UDT into a single value for storage.\n\n---\n\n\nWant the solution? Click here.
\n> ```\nALTER TABLE killrvideo.videos ADD (encoding FROZEN);\n```\n \n","12":"markdown","13":{"1":"23bdca24-86a8-4484-93b7-67bbbe3ca643","10":{"9":"Step 3: In the next cell, alter the videos
table by adding an encodings
column of type FROZEN<video_encoding>
.
\n
\nNOTE: UDTs that contain containers (e.g., sets, lists, etc.) must be declared FROZEN
to make it explicit that Cassandra serializes the contents of the UDT into a single value for storage.
\n
\n
\n\n
Want the solution? Click here.
\nALTER TABLE killrvideo.videos ADD (encoding FROZEN<video_encoding>);\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"e419df0c-35e3-48f6-9070-f69e35b3df9c","11":"// Write the CQL to add the encoding column to the videos table\n// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"5b721c72-a668-4a19-98d5-959c015e0b01","10":4,"11":"#### Step 4: Use the `COPY` command in _cqlsh_ to populate the `videos` table from the contents of the `/home/ubuntu/data/videos.csv` file.\n- #### To complete this step, you must have already completed the bonus challenge in _Notebook 2: Using CQL_, which setups up the _cqlsh_ connection.\n- #### In Theia, open `/home/ubuntu/data/videos.csv` to make note of the columns in the file. Since the file does not contain all the columns in the table, you will need to list these in the `COPY` command. The form of the command looks like `COPY keyspacename.tablename (col1,col2,col3) FROM filename.csv WITH HEADER=TRUE;`\n- #### While still in the `videos.csv` file, you may find it helpful to also note the format of the values for the `video_encoding` UDT.\n- #### Open a terminal window in Theia\n- #### Be sure to `cd ~/.cassandra`\n- #### Launch _cqlsh_ by `cqlsh -u KVUser -p KVPassword`\n- #### Remember to set the consistency level by `CONSISTENCY LOCAL_QUORUM;`\n- #### `COPY` the `/home/ubuntu/data/videos.csv` file into the `videos` table.\n\n\nWant the solution? Click here.
\n> ```\nCOPY killrvideo.videos (videoid,added_date,description,tags,name,userid) FROM '/home/ubuntu/data/videos.csv' WITH HEADER=TRUE;\n```\n \n","12":"markdown","13":{"1":"4fe51897-f3da-4de8-a157-33030d0a2ed3","10":{"9":"Step 4: Use the COPY
command in cqlsh to populate the videos
table from the contents of the /home/ubuntu/data/videos.csv
file.
\n\nTo complete this step, you must have already completed the bonus challenge in Notebook 2: Using CQL, which setups up the cqlsh connection.
\n \nIn Theia, open /home/ubuntu/data/videos.csv
to make note of the columns in the file. Since the file does not contain all the columns in the table, you will need to list these in the COPY
command. The form of the command looks like COPY keyspacename.tablename (col1,col2,col3) FROM filename.csv WITH HEADER=TRUE;
\n \nWhile still in the videos.csv
file, you may find it helpful to also note the format of the values for the video_encoding
UDT.
\n \nOpen a terminal window in Theia
\n \nBe sure to cd ~/.cassandra
\n \nLaunch cqlsh by cqlsh -u KVUser -p KVPassword
\n \nRemember to set the consistency level by CONSISTENCY LOCAL_QUORUM;
\n \nCOPY
the /home/ubuntu/data/videos.csv
file into the videos
table.
\n \n
\n\n
Want the solution? Click here.
\nCOPY killrvideo.videos (videoid,added_date,description,tags,name,userid) FROM '/home/ubuntu/data/videos.csv' WITH HEADER=TRUE;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"ef49ff38-991b-408a-b908-58c0bcf320eb","10":4,"11":"#### Step 5: Use the `COPY` command in _cqlsh_ also to encodings to the `videos` table from the contents of the `/home/ubuntu/data/videos_encoding.csv` file.\n- #### Again, in Theia open the file (`/home/ubuntu/data/videos_encoding`) to make note of the columns the file contains.\n- #### Back in _cqlsh_ `COPY` the `/home/ubuntu/data/videos_encodings.csv` file into the `videos` table.\n\n\n\nWant the solution? Click here.
\n> ```\nCOPY killrvideo.videos (videoid,encoding) FROM '/home/ubuntu/data/videos_encoding.csv' WITH HEADER=TRUE;\n```\n \n","12":"markdown","13":{"1":"e3c2917a-8279-4f30-84ee-5bc29a75eec2","10":{"9":"Step 5: Use the COPY
command in cqlsh also to encodings to the videos
table from the contents of the /home/ubuntu/data/videos_encoding.csv
file.
\n\nAgain, in Theia open the file (/home/ubuntu/data/videos_encoding
) to make note of the columns the file contains.
\n \nBack in cqlsh COPY
the /home/ubuntu/data/videos_encodings.csv
file into the videos
table.
\n \n
\n\n
Want the solution? Click here.
\nCOPY killrvideo.videos (videoid,encoding) FROM '/home/ubuntu/data/videos_encoding.csv' WITH HEADER=TRUE;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"de5bb2c7-5f62-433b-a883-e62c256fdb94","10":4,"11":"#### Step 6: In the following cell, query the `videos` table for the `encoding` column value of the row with the `videoid` of `2644c36e-14bd-11e5-839e-8438355b7e3a`.\n- #### Inspect the output to see what the UDT looks like.\n\n\nWant the solution? Click here.
\n> ```\nSELECT encoding FROM killrvideo.videos WHERE videoid=2644c36e-14bd-11e5-839e-8438355b7e3a;\n```\n \n","12":"markdown","13":{"1":"bd0d2c17-b655-4e35-bed8-33b60e83c346","10":{"9":"Step 6: In the following cell, query the videos
table for the encoding
column value of the row with the videoid
of 2644c36e-14bd-11e5-839e-8438355b7e3a
.
\n\nInspect the output to see what the UDT looks like.
\n \n
\n\n
Want the solution? Click here.
\nSELECT encoding FROM killrvideo.videos WHERE videoid=2644c36e-14bd-11e5-839e-8438355b7e3a;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"f8faae76-c483-4f65-80c4-8a28f69b73dc","11":"// Write a CQL query to find the row in the videos table with a videoid of 2644c36e-14bd-11e5-839e-8438355b7e3a\n// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"6e615635-d4c2-4a30-b095-534311aa18d0","10":4,"11":"#### The UDT is `FROZEN` which means if you want to update it, you must replace the entire contents of the UDT. Remember, Cassandra encodes the UDT into something like a single blob.\n\n\n#### Step 7: In the following cell, write a CQL UPDATE command to add a bit rate of \"1500 Kbps\" to the `video_encoding` for the row with the `videoid` of `2644c36e-14bd-11e5-839e-8438355b7e3a`.\n- #### Here is an example of the correct format of the UDT _before_ the update of the additional bit rate.\n\n```\n{\n encoding: '1080p',\n height: 1080,\n width: 1920,\n bit_rates: {\n '3000 Kbps', \n '4500 Kbps', \n '6000 Kbps'\n }\n}\n```\n\n\n\nWant the solution? Click here.
\n> ```\nUPDATE killrvideo.videos\n SET encoding={encoding: '1080p', height: 1080, width: 1920, bit_rates: {'1500 Kbps', '3000 Kbps', '4500 Kbps', '6000 Kbps'}}\n WHERE videoid=2644c36e-14bd-11e5-839e-8438355b7e3a;\n```\n \n","12":"markdown","13":{"1":"c2e20148-4a37-46a4-8929-7317a79e59f3","10":{"9":"The UDT is FROZEN
which means if you want to update it, you must replace the entire contents of the UDT. Remember, Cassandra encodes the UDT into something like a single blob.
\nStep 7: In the following cell, write a CQL UPDATE command to add a bit rate of “1500 Kbps” to the video_encoding
for the row with the videoid
of 2644c36e-14bd-11e5-839e-8438355b7e3a
.
\n\nHere is an example of the correct format of the UDT before the update of the additional bit rate.
\n \n
\n{\n encoding: '1080p',\n height: 1080,\n width: 1920,\n bit_rates: {\n '3000 Kbps', \n '4500 Kbps', \n '6000 Kbps'\n }\n}\n
\n\n
Want the solution? Click here.
\nUPDATE killrvideo.videos\n SET encoding={encoding: '1080p', height: 1080, width: 1920, bit_rates: {'1500 Kbps', '3000 Kbps', '4500 Kbps', '6000 Kbps'}}\n WHERE videoid=2644c36e-14bd-11e5-839e-8438355b7e3a;\n
\n\n
\n"},"11":4,"12":false},"16":true,"17":true,"18":{},"24":"killrvideo","25":"LOCAL.QUORUM"},{"1":"90371e33-c522-41ad-bd1b-2c732c623e71","11":"// Write a CQL update command to add a bit_rate of 1500 Kbps to the row in the videos table with a videoid of 2644c36e-14bd-11e5-839e-8438355b7e3a\n// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)\n","12":"cql","16":true,"17":false,"24":"killrvideo","25":"LOCAL.QUORUM"}],"16":{"1":{}},"17":"","19":false} code.txt 0100644 0000000 0000000 00000100253 13576510506 011271 0 ustar 00 0000000 0000000 --------------------NOTEBOOK_Notebook 5 - Advanced Data Types--------------------
--------------------CELL_MARKDOWN_1--------------------
![DataTypesSplash](https://s3.amazonaws.com/datastaxtraining/CaaS/DataTypesSplash.png "DataTypesSplash" )
### **Welcome to the Launchpad Advanced Data Types Notebook!**
#### In this notebook, we will learn about collection types such as set and counters. Let's get started!
--------------------CELL_MARKDOWN_2--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Set Up the Notebook
### In this section, you will do the following things:
- #### Execute a CQL script to initialize the KillrVideo database for this notebook
#### Step 1: Execute the following cell to initialize this notebook. Hover over the right-hand corner of the cell and click the _Run_ button.
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/ExecuteCell.png "line" )
#### **Note:** You don't see the CQL script because the code editor is hidden, but you can still run the cell.
--------------------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));
// 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;
// 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);
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(11111111-1111-1111-1111-111111111111, toTimestamp(now()), 'Jeff', 'Carpenter', 'jc@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(11111111-1111-1111-1111-111111111111, 'jc@datastax.com', 'J3ffL0v3$C@ss@ndr@');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(22222222-2222-2222-2222-222222222222, toTimestamp(now()), 'Eric', 'Zietlow', 'ez@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(22222222-2222-2222-2222-222222222222, 'ez@datastax.com', 'C@ss@ndr@R0ck$');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(33333333-3333-3333-3333-333333333333, toTimestamp(now()), 'Cedrick', 'Lunven', 'cl@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(33333333-3333-3333-3333-333333333333, 'cl@datastax.com', 'Fr@nc3L0v3$C@ss@ndr@');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(44444444-4444-4444-4444-444444444444, toTimestamp(now()), 'David', 'Gilardi', 'dg@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(44444444-4444-4444-4444-444444444444, 'dg@datastax.com', 'H@t$0ff2C@ss@ndr@');
//INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
// VALUES(55555555-5555-5555-5555-555555555555, toTimestamp(now()), 'Cristina', 'Veale', 'cv@datastax.com');
//INSERT INTO killrvideo.user_credentials (userid, email, password)
// VALUES(55555555-5555-5555-5555-555555555555, 'cv@datastax.com', '3@$tC0@$tC@ss@ndr@');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(66666666-6666-6666-6666-666666666666, toTimestamp(now()), 'Adron', 'Hall', 'ah@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(66666666-6666-6666-6666-666666666666, 'ah@datastax.com', 'C@ss@ndr@43v3r');
INSERT INTO killrvideo.users (userid, created_date, firstname, lastname, email)
VALUES(77777777-7777-7777-7777-777777777777, toTimestamp(now()), 'Aleks', 'volochnev', 'av@datastax.com');
INSERT INTO killrvideo.user_credentials (userid, email, password)
VALUES(77777777-7777-7777-7777-777777777777, 'av@datastax.com', 'C@ss@ndr@3v3rywh3r3');
--------------------CELL_MARKDOWN_4--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Collection Types
### In this section, you will do the following things:
- #### Investigate `SET`, which is one of the collection types
- #### Insert and retrieve rows in the `videos` table that use `SET`
#### The `videos` table uses a `SET` collection to keep track of tags associated with each video. A `SET` is a great collection to use because sets do not maintain an order - we are not concerned with any tag order, only if a tag is or is not associated with the video.
#### Let's start by reviewing the definition of the `videos` table:
#### Step 1: Execute the following cell to describe the `videos` table.
--------------------CELL_CQL_5--------------------
// Execute this cell (click the Run button in the top-right corner)
DESCRIBE TABLE killrvideo.videos;
--------------------CELL_MARKDOWN_6--------------------
#### Note two things about the `videos` table. First, the primary key is just `videoid`. Second, the `tags` column is a set of text. Tags are words or phrases we want to associate with a video.
#### To allow us to keep our focus on `SET`, in this example we will only specify the `videoid` and the `tags`. Once again, let's use our contrived `uuid` of `12121212-1212-1212-1212-121212121212`.
#### Step 2: In the following cell, insert a sparse row into the videos table with a `videoid` of `12121212-1212-1212-1212-121212121212` and a set of tags that contain the words: `Favorite`, `Fast-paced`, `Funny`.
Need a hint? Click here.
> You want to `INSERT` into the `killrvideo.videos` table with a `videoid` of `12121212-1212-1212-1212-121212121212` and a set of tags such as `{ 'Favorite', 'Fast-paced', 'Funny' }`.
Want the command? Click here.
> ```
INSERT INTO killrvideo.videos (videoid, tags)
VALUES(12121212-1212-1212-1212-121212121212, { 'Favorite', 'Fast-paced', 'Funny' });
```
--------------------CELL_CQL_7--------------------
// Write a command to insert a row into the videos table
// Execute this cell (click the Run button in the top-right corner)
--------------------CELL_MARKDOWN_8--------------------
#### Now, let's check to see if our insert worked as expected.
#### Step 3: Execute the following cell to query for the row with the `videoid` of `12121212-1212-1212-1212-121212121212`.
--------------------CELL_CQL_9--------------------
// Execute this cell (click the Run button in the top-right corner)
SELECT * FROM killrvideo.videos WHERE videoid = 12121212-1212-1212-1212-121212121212;
--------------------CELL_MARKDOWN_10--------------------
#### Inspect the `tags` values and see that the `INSERT` worked as expected.
#### There are two kinds of `SET` updates we could perform. We can completely replace a set, or we can modify the contents of an existing set. First, we'll replace the entire `tags` set with the values `High-brow`, `Intellectual` and `Refined`.
#### Step 4: In the following cell, write a comand to replace the `tags` set for the `videoid` of `12121212-1212-1212-1212-121212121212`.
Need a hint? Click here.
> You want to `UPDATE` the `killrvideo.videos` table with a `videoid` of `12121212-1212-1212-1212-121212121212`. `SET` the `tags` value to the new set `{ 'High-brow', 'Intellectual', 'Refined' }`.
Want the command? Click here.
> ```
UPDATE killrvideo.videos SET tags = { 'High-brow', 'Intellectual', 'Refined' } WHERE videoid = 12121212-1212-1212-1212-121212121212;
```
--------------------CELL_CQL_11--------------------
// Write a command to update the row from the videos table
// Execute this cell (click the Run button in the top-right corner)
--------------------CELL_MARKDOWN_12--------------------
#### Once again, let's inspect the effect of the `UPDATE`.
#### Step 5: Execute the following cell - a query to retrieve the row for the `videoid` of `12121212-1212-1212-1212-121212121212`.
--------------------CELL_CQL_13--------------------
// Execute this cell (click the Run button in the top-right corner)
SELECT * FROM killrvideo.videos WHERE videoid = 12121212-1212-1212-1212-121212121212;
--------------------CELL_MARKDOWN_14--------------------
#### We see the values we updated in Step 3. The values may not be in the same order as in your `UPDATE` command, but that's OK.
#### Thought question: If you _were_ concerned about the order of the tags, what data type would you use instead of a `SET`?
#### Let's modify the set again. This time we will remove the `Refined` tag. Then in later steps we will replace it with `Low-rent`.
#### Step 6: In the following cell, write a command to update, by removing the `Refined` tag, for the `videoid` of `12121212-1212-1212-1212-121212121212`.
Need a hint? Click here.
> Here, you will use an `UPDATE` command. Again, we are updating the row in the `killrvideo.videos` table with the `videoid` of `12121212-1212-1212-1212-121212121212`. The clause you use to remove the tag looks like `tags = tags - { 'Refined' }`.
Want the command? Click here.
> ```
UPDATE killrvideo.videos SET tags = tags - { 'Refined' } WHERE videoid = 12121212-1212-1212-1212-121212121212;
```
--------------------CELL_CQL_15--------------------
// Write a command to update the row from the videos table
// Execute this cell (click the Run button in the top-right corner)
--------------------CELL_MARKDOWN_16--------------------
#### Again, let's check the contents of the row to see the effects of our command.
#### Step 7: Execute the following cell - a query to retrieve the row for the `videoid` of `12121212-1212-1212-1212-121212121212`.
--------------------CELL_CQL_17--------------------
// Execute this cell (click the Run button in the top-right corner)
SELECT * FROM killrvideo.videos WHERE videoid = 12121212-1212-1212-1212-121212121212;
--------------------CELL_MARKDOWN_18--------------------
#### Inspecting the previous results, we see the row now only has two tags - that's what we wanted!
#### Let's add a third tag `Low-rent`.
#### Step 8: In the following cell, write a command to update, by adding the `Low-rent` tag, for the `videoid` of `12121212-1212-1212-1212-121212121212`.
Need a hint? Click here.
> Here, you will use an `UPDATE` command. Again, we are updating the row in the `killrvideo.videos` table with the `videoid` of `12121212-1212-1212-1212-121212121212`. The clause you use to remove the tag looks like `tags = tags + { 'Low-rent' }`.
Want the command? Click here.
> ```
UPDATE killrvideo.videos SET tags = tags + { 'Low-rent' } WHERE videoid = 12121212-1212-1212-1212-121212121212;
```
--------------------CELL_CQL_19--------------------
// Write a command to update the row from the videos table
// Execute this cell (click the Run button in the top-right corner)
--------------------CELL_MARKDOWN_20--------------------
#### One last time, let's check the contents of the row to see the effects of our command.
#### Step 9: Execute the following cell.
--------------------CELL_CQL_21--------------------
// Execute this cell (click the Run button in the top-right corner)
SELECT * FROM killrvideo.videos WHERE videoid = 12121212-1212-1212-1212-121212121212;
--------------------CELL_MARKDOWN_22--------------------
#### Review the results in the previous cell to see that the update worked as expected.
---
Note (some things to keep in mind about Collections):
To avoid performance problems, only use collections for small-ish numbers of elements
Sets and maps do not incur the read-before-write penalty, but some list operations do. Therefore, when possible, prefer sets to lists
List prepend and append operations are not idempotent, so retrying after a timeout may result in duplicate elements
Collections may only be used in primary keys if they are frozen
---
--------------------CELL_MARKDOWN_23--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Counters
### In this section, you will do the following things:
- #### Investigate the `video_playback_stats` table in `killrvideo`
- #### Add a row to the `video_playback_stats` table
- #### Increment the counter of the table to simulate videoing a video
#### KillrVideo uses the `video_playback_stats` table to keep track of the number of times a video has been viewed. A counter is a great data type for this use-case because counters perform well in Cassandra, and in the rare event where the counter might drop an update, it is not a serious problem for the app or its users.
#### Let's start by investigating this table.
#### Step 1: In the following cell, describe the `video_playback_stats` table.
Need a hint? Click here.
> You want to use the `DESCRIBE` command to describe only the table.
Want the command? Click here.
> ```
DESCRIBE TABLE killrvideo.video_playback_stats;
```
--------------------CELL_CQL_24--------------------
// Write a command to allow you to review the table definition for video_playback_stats
// Execute this cell (click the Run button in the top-right corner)
--------------------CELL_MARKDOWN_25--------------------
#### Notice that this table has two columns: `videoid` and `views`. Also, notice that `views` is a counter that keeps track of how many times the video has been, uh, viewed.
#### In this section, we want to update a counter. Just to keep things simple, let's use a contrived `uuid` for the `videoid`. We'll use the value `12121212-1212-1212-1212-121212121212`.
#### We'll start by verifying that a row for this `videoid` does not yet exist in the table.
#### Step 2: In the following cell, try to retrieve the row with the `videoid` of `12121212-1212-1212-1212-121212121212`.
Need a hint? Click here.
> For this command, you will use the `SELECT` statement on the `video_playback_stats` table in the `killrvideo` keyspace. You only want the row where the `videoid` is `12121212-1212-1212-1212-121212121212`, but it will be easiest just to grab all the columns.
Want the command? Click here.
> ```
SELECT * from killrvideo.video_playback_stats WHERE videoid = 12121212-1212-1212-1212-121212121212;
```
--------------------CELL_CQL_26--------------------
// Write a command to try to retrieve the row with a videoid of 12121212-1212-1212-1212-121212121212
// Execute this cell (click the Run button in the top-right corner)
--------------------CELL_MARKDOWN_27--------------------
#### We see "No Data Returned" which allows us to verify that the row with that key is not in the table yet.
#### Let's try creating the row with that `videoid`.
#### Step 3: Create the row by incrementing the `views` counter for the `videoid` `12121212-1212-1212-1212-121212121212`.
Need a hint? Click here.
> You will be `UPDATE`ing the row in the `killrvideo.video_playback_stats` with the `videoid` of `12121212-1212-1212-1212-121212121212`. You will increment the counter with code like this: `views = views + 1`.
Want the command? Click here.
> ```
UPDATE killrvideo.video_playback_stats SET views = views + 1 WHERE videoid = 12121212-1212-1212-1212-121212121212;
```
#### **Thought question:** Since we know we cannot `INSERT` a row with a counter, we can only `UPDATE` the row, what will the value of the counter be after we increment it?
--------------------CELL_CQL_28--------------------
// Write a command to update the views counter of the row with a videoid of 12121212-1212-1212-1212-121212121212
// Execute this cell (click the Run button in the top-right corner)
--------------------CELL_MARKDOWN_29--------------------
#### Let's see the results of the update!
#### Step 4: Execute the following cell and inspect the results.
--------------------CELL_CQL_30--------------------
// Execute this cell (click the Run button in the top-right corner)
SELECT * FROM killrvideo.video_playback_stats WHERE videoid = 12121212-1212-1212-1212-121212121212;
--------------------CELL_MARKDOWN_31--------------------
#### This time we see the row. We created the row with the `UPDATE` command - an upsert! Notice that the value of the `views` counter is one. That's because, when you create a row by incrementing the counter, it's as if the counter started at zero.
---
Note (some things to keep in mind about counters):
Counters cannot be part of a primary key
Incrementing or decrementing counters is not idempotent
Incrementing or decrementing a counter is not always guaranteed to work - under high traffic situations, it is possible for one of these operations to get dropped
---
--------------------CELL_MARKDOWN_32--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Congratulations!!!!
#### If you have made it to the end of this notebook successfully, you have additional data types under your belt that you can use in your data type virtuoso!
You can learn about even more data types at DataStax Academy. The price? NADA!
####
Want to see another virtuoso? Click here.
--------------------CELL_MARKDOWN_33--------------------
![line](https://s3.amazonaws.com/datastaxtraining/CaaS/line.png "line" )
# Bonus Challenge: Create a UDT
### If you got done early and want something to do while you wait for others, here's a bonus challenge
### In this section, you will do the following things:
- #### Create a UDT to represent video encoding
- #### Alter the KillrVideo `videos` table to use this UDT
- #### Load some data into the altered `videos` table
- #### Run a query on the loaded data with the UDT
- #### Update the value of a row containing a UDT
#### **Here's the pitch:**
#### As KillrVideo grows in popularity, it becomes necessary to support various video formats with different bit rates, encodings and frame sizes. This seems like an ideal use for a UDT. Let's build the UDT and then add it to the `videos` table.
#### Step 1: In the next cell, write and execute the CQL to create a UDT, named `video_encoding` that contains the fields as described in the following table.
Field Name |
Data Type |
bit_rates |
SET<TEXT> |
encoding |
TEXT |
height |
INT |
width |
INT |
Want the solution? Click here.
> ```
CREATE TYPE IF NOT EXISTS killrvideo.video_encoding (
bit_rates SET,
encoding TEXT,
height INT,
width INT
);
```
--------------------CELL_CQL_34--------------------
// Create the video_encoding UDT in this cell as described.
// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)
--------------------CELL_MARKDOWN_35--------------------
#### Step 2: In the next cell, truncate the contents of the `videos` table to prepare it to receive the data with the encoding.
Want the solution? Click here.
> ```
TRUNCATE TABLE killrvideo.videos;
```
--------------------CELL_CQL_36--------------------
// Write the CQL to truncate the videos table
// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)
--------------------CELL_MARKDOWN_37--------------------
#### Step 3: In the next cell, alter the `videos` table by adding an `encodings` column of type `FROZEN`.
---
> #### **NOTE:** UDTs that contain containers (e.g., sets, lists, etc.) must be declared `FROZEN` to make it explicit that Cassandra serializes the contents of the UDT into a single value for storage.
---
Want the solution? Click here.
> ```
ALTER TABLE killrvideo.videos ADD (encoding FROZEN);
```
--------------------CELL_CQL_38--------------------
// Write the CQL to add the encoding column to the videos table
// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)
--------------------CELL_MARKDOWN_39--------------------
#### Step 4: Use the `COPY` command in _cqlsh_ to populate the `videos` table from the contents of the `/home/ubuntu/data/videos.csv` file.
- #### To complete this step, you must have already completed the bonus challenge in _Notebook 2: Using CQL_, which setups up the _cqlsh_ connection.
- #### In Theia, open `/home/ubuntu/data/videos.csv` to make note of the columns in the file. Since the file does not contain all the columns in the table, you will need to list these in the `COPY` command. The form of the command looks like `COPY keyspacename.tablename (col1,col2,col3) FROM filename.csv WITH HEADER=TRUE;`
- #### While still in the `videos.csv` file, you may find it helpful to also note the format of the values for the `video_encoding` UDT.
- #### Open a terminal window in Theia
- #### Be sure to `cd ~/.cassandra`
- #### Launch _cqlsh_ by `cqlsh -u KVUser -p KVPassword`
- #### Remember to set the consistency level by `CONSISTENCY LOCAL_QUORUM;`
- #### `COPY` the `/home/ubuntu/data/videos.csv` file into the `videos` table.
Want the solution? Click here.
> ```
COPY killrvideo.videos (videoid,added_date,description,tags,name,userid) FROM '/home/ubuntu/data/videos.csv' WITH HEADER=TRUE;
```
--------------------CELL_MARKDOWN_40--------------------
#### Step 5: Use the `COPY` command in _cqlsh_ also to encodings to the `videos` table from the contents of the `/home/ubuntu/data/videos_encoding.csv` file.
- #### Again, in Theia open the file (`/home/ubuntu/data/videos_encoding`) to make note of the columns the file contains.
- #### Back in _cqlsh_ `COPY` the `/home/ubuntu/data/videos_encodings.csv` file into the `videos` table.
Want the solution? Click here.
> ```
COPY killrvideo.videos (videoid,encoding) FROM '/home/ubuntu/data/videos_encoding.csv' WITH HEADER=TRUE;
```
--------------------CELL_MARKDOWN_41--------------------
#### Step 6: In the following cell, query the `videos` table for the `encoding` column value of the row with the `videoid` of `2644c36e-14bd-11e5-839e-8438355b7e3a`.
- #### Inspect the output to see what the UDT looks like.
Want the solution? Click here.
> ```
SELECT encoding FROM killrvideo.videos WHERE videoid=2644c36e-14bd-11e5-839e-8438355b7e3a;
```
--------------------CELL_CQL_42--------------------
// Write a CQL query to find the row in the videos table with a videoid of 2644c36e-14bd-11e5-839e-8438355b7e3a
// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)
--------------------CELL_MARKDOWN_43--------------------
#### The UDT is `FROZEN` which means if you want to update it, you must replace the entire contents of the UDT. Remember, Cassandra encodes the UDT into something like a single blob.
#### Step 7: In the following cell, write a CQL UPDATE command to add a bit rate of "1500 Kbps" to the `video_encoding` for the row with the `videoid` of `2644c36e-14bd-11e5-839e-8438355b7e3a`.
- #### Here is an example of the correct format of the UDT _before_ the update of the additional bit rate.
```
{
encoding: '1080p',
height: 1080,
width: 1920,
bit_rates: {
'3000 Kbps',
'4500 Kbps',
'6000 Kbps'
}
}
```
Want the solution? Click here.
> ```
UPDATE killrvideo.videos
SET encoding={encoding: '1080p', height: 1080, width: 1920, bit_rates: {'1500 Kbps', '3000 Kbps', '4500 Kbps', '6000 Kbps'}}
WHERE videoid=2644c36e-14bd-11e5-839e-8438355b7e3a;
```
--------------------CELL_CQL_44--------------------
// Write a CQL update command to add a bit_rate of 1500 Kbps to the row in the videos table with a videoid of 2644c36e-14bd-11e5-839e-8438355b7e3a
// Then, to execute this cell, click the Run button in the top-right corner (or SHIFT-ENTER)
versions-info.txt 0100644 0000000 0000000 00000000055 13576510506 013157 0 ustar 00 0000000 0000000 Studio Version: 6.8.0-20191105-CLOUD-3368ca6