CSCI3287 Homework # 6 - Cassandra Lab Solution

Overview


This assignment will give you hands-on practice in working with the Apache Cassandra “NoSQL” database software. Objectives

• Students will deploy and run the Apache Cassandra NoSQL database engine
• Students will gain exposure to CQL – Cassandra Query Language
• Students will experience deployment of Apache Cassandra in a Docker container
• Students will launch and use the Cassandra CQL Shell
• Students will run CQL queries
Submission

Create a document and save it as a PDF file. Submit your document via the link in Canvas in the Week 15 materials for Homework # 6.

Your submission should contain SCREEN SHOTS from your console to verify that you have completed each step. Please number your screen shots to match each step and problem within each step if applicable.

Instructions to Get Started:





We are now connected to the Cassandra image inside docker.



Using Real-world dataset to get started with the assignment:
1. Download the cubnb-dataset.txt from Canvas.

2. Once we have the cassandra image running and are connected to the sh terminal inside the image, open the cubnb-dataset.txt in a notepad/textpad and copy all the contents (Ctrl + C) and paste the contents in the sh terminal (Ctrl + V)
3. Ensure after the completion of all the commands, you get the following output.


Assignment Submission Instructions:
Go through below basic operations of Cassandra before starting with Sections 1 and 2.

Create operation commands and queries for Section 1 & 2. Please attach the screenshots (of query and output) of both the sections.
Submit the document as PDF file on Canvas.

Basic Operations of Cassandra:
1. Creating a KeySpace: A keyspace in Cassandra is a namespace that defines data replication on nodes.



2. Using a Keyspace : You can use a created KeySpace using the keyword USE



3. Creating tables in keyspace :



4. Describe a KeySpace: Verify the keyspaces and its tables.



5. Drop a KeySpace: Delete the keyspaces and its tables.



Section 1 :

Create commands for below operations as mentioned above and attach screenshots in the assignment.
1. Create a keyspace cubnb for the application having class as SimpleStrategy and replication_factor as 1.
Keyspace name: cubnb.

2. Use the keyspace created above.
3. Create a table for tenant if not exists. Table name: tenant .
Columns: tenant_id (int, primary key), mobile (bigint), password (string), tenant_name (string).

4. Create a table for property if not exists. Table name: property.
Columns: property_id (int, primary key), property_name (string), host_id (int, primary key),
location (string), type (string), price (int), number_of_rooms (int), max_occupancy (int), number_of_reviews (int), (set<string> type), status (string).
amenities
5. Create a table for booking if not exists. Table name: booking .
Columns: booking_id (int, primary key), property_id (int, primary key), total_occupancy (int),
no_of_days start_date end_date




Section 2 :

Create queries for below questions and attach screenshots of the query and output in the assignment.
Note: WE HAVE ALREADY IMPORTED DATASET IN THE ABOVE INSTRUCTIONS SECTION
2. Get the count of properties whose reviews are more than 200.
3. List all properties from “Brooklyn” location with type as apartment and have status as available.
4. a) Get all the properties which are handled by host_id 1029.
b) Add new amenity ‘Heater’ to the above results (in a single query).
c) Show the updated results from part b query.
5. a) Add your details in tenant table with tenant_id as 3287
b) Change the password of the tenant_id 3287 from the existing to ‘password@123’ and display the change
6. Remove an amenity ‘GardenView’ for host_id 1012 and property_id 110 and display the results.
7. List the properties which have a ‘BeachView’ in its amenities and whose rent price is more than 1000. 8. Get the number of highest occupancy of properties for 8 rooms.