Enough CQL To Get You Started

DSE Version: 6.0

Video

Exercises

In this unit, you will learn just enough Cassandra Query Language, or CQL, to be dangerous. You will need this to create physical data models in Apache Cassandra.

NO TRANSCRIPT AVAILABLE.

No write up.

Exercise 1.2: Basic CQL Fundamentals

  • Create a Keyspace for KillrVideo
  • Create a table to store video metadata
  • Load the data for the video table from a CSV file

Background

Welcome to the KillrVideo company! KillrVideo hired you to build the latest and greatest video sharing application on the Internet. Your task is to ramp upon the domain and become acquainted with Cassandra. To start, you decided to look into creating a table schema and to load some video data.

The video metadata is made up of:

Column NameData Type
video_idtimeuuid
added_datetimestamp
descriptiontext
titletext
user_iduuid

Steps

1. If necessary, SSH to cloud environment provided by your instructor.

2. Open '/home/ubuntu/labwork/cql/videos.csv' with a text editor and review the contents of the file.

IMPORTANT: Notice the order of the columns matches theorder shown above.

3. Navigate to the '/home/ubuntu/labwork/cql' directory.

cd /home/ubuntu/labwork/cql

4. At the prompt, start 'cqlsh'.

5. In 'cqlsh', create a keyspace called 'killrvideo'and switch to that keyspace. Use 'SimpleStrategy' for the replication class with a replication factor of one. Remember the 'use' command switches keyspaces.

NOTE: You can press the tab key within the CREATE KEYSPACE command to have 'cqlsh' autocomplete the replication parameters.

6. Create a single table called 'videos' with the same structure as shown in table above. 'video_id' is the primary key.

7. Load the newly created table with the 'videos.csv' file using the 'COPY' command.

COPY videos (video_id,added_date,description,title,user_id) FROM 'videos.csv' WITH HEADER=true;

8. Use SELECT to verify the data loaded correctly. Include LIMIT to retrieve only the first 10 rows.

9. Use SELECT to COUNT(*) the number of imported rows. It should match the number of rows COPY reported as imported.

10. Use SELECT to find a row where the video_id = 6c4cffb9-0dc4-1d59-af24-c960b5fc3652.

Next we will explore some other CQL commands that will come in handy, like TRUNCATE in a later exercise, we will show you how to add/remove (non-primary key) columns.

11. Let's remove the data from our table using TRUNCATE.

truncate videos;

12. Exit 'cqlsh'.

No FAQs.
No resources.
Comments are closed.