Physical Data Modeling

DSE Version: 6.0

Video

Exercises

Now lets get down to creating table in Apache Cassandra! That's what this unit is all about. This is how we create our physical data model.

NO TRANSCRIPT AVAILABLE.

No write up.

Exercise 4.4: Finalizing Physical Data Modeling

In this exercise, you will:

  • Add data types to the physical data model
  • Run the CQL CREATE TABLE statements for each table in physical model
  • Load data and run some queries to test the physical data model

Background

In this exercise, you will build on your logical data model and finalize your physical data model. Not all your tables have data types for columns.

You will also need to build and run the CREATE TABLE statements for each of the tables, and run some queries to verify that everything is working correctly.

Steps

  1. Use a text editor to open and review the killrvideo.cql file in the labwork/final directory.
  2. There are several tables with columns marked with *CQL Type*. Fill in the appropriate data type for the columns in users_by_email, users, videos_by_user, and comments_by_user.
  3. In the labwork/final directory, start up cqlsh.
  4. Run the SOURCE command on the killrvideo.cql file to execute the CREATE TABLE statements.

If the SOURCE command was successful, you should now have a new keyspace called killr_video.

  1. Run the DESCRIBE KEYSPACE command on the killr_video keyspace to review the keyspace and table schema.
  2. Set the default keyspace to killr_video with the USE command.
  3. You can now load video data into your new keyspace. Use the COPY command to load data into the following tables:
COPY videos FROM 'videos.csv' WITH HEADER=true;
COPY latest_videos FROM 'latest_videos.csv' WITH HEADER=true;
COPY trailers_by_video FROM 'trailers_by_video.csv' WITH HEADER=true;
COPY actors_by_video FROM 'actors_by_video.csv' WITH HEADER=true;

If everything is successful, you should now have video data available for reading.

  1. Query the latest_videos table to find the most recent 50 videos that was uploaded.
    • Is there a video uploaded for the movie Gone Girl? What is the video_id for that movie?
  2. Let's find out some more information about this movie. Query the videos table using the previously found video_id.
    • When was this movie released? What are the genres for this movie?
  3. We can also find the actors that were in the movie and the characters they played. Go ahead and query the actors_by_video table using the video_id for Gone Girl.
    • Who was the actor that played the character Desi Collings?
  4. At this point, an interested user might want to watch a trailer for this movie. Query the trailers_by_video table to check if there are any trailers available for this movie.
  5. If there is a trailer available, make note of the trailer_id and then query the videos table again using the trailer_id value as the equality condition for the video_id column.
    • What is the URL for the trailer?

Copy the URL into a web browser and enjoy!

No FAQs.
No resources.
Comments are closed.