Cassandra is a powerful NoSQL database system that's well-suited for handling large volumes of data. In this blog, we'll go through a series of common tasks related to managing data in Cassandra. We'll address each query and provide a detailed solution with explanations to help you get started.
To create a keyspace named 'electric' with class 'SimpleStrategy' and replication factor 1, you can use the following command in the Cassandra Query Language Shell (cqlsh):
CREATE KEYSPACE IF NOT EXISTS electric
WITH REPLICATION = {'class': 'SimpleStrategy', 'replication_factor': 1};
This command ensures that the keyspace is created only if it doesn't already exist.
Now, let's create a table named 'products' with the specified columns and data types:
- id (int, primary key)
- category (text)
- name (text)
- price (int)
- discount (text)
- companyname (text)
To create this table, use the following command:
CREATE TABLE IF NOT EXISTS electric.products (
id int PRIMARY KEY,
category text,
name text,
price int,
discount text,
companyname text
);
If you have a JSON file named 'products.json' with data to insert into the 'products' table, you can use the following CQL command:
COPY electric.products (id, category, name, price, discount, companyname)
FROM 'products.json' WITH HEADER = true;
Make sure that the 'products.json' file is in the same directory as cqlsh or provide the full path to the file. The `WITH HEADER = true` option assumes that the first row of the JSON file contains column headers.
To check if the data is loaded successfully, you can query the 'products' table.
To retrieve the details of products available for discounts (where `discount` is true) and save the results in 'discount_products.txt', use the following command:
cqlsh -e "SELECT * FROM electric.products WHERE discount = 'true'" > discount_products.txt
This command executes a SELECT query and redirects the output to 'discount_products.txt'.
To get the maximum price among the specified products and store the result in 'max_price.txt', use the following command:
cqlsh -e "SELECT MAX(price) FROM electric.products" > max_price.txt
This command calculates the maximum price and saves it to 'max_price.txt'.
If you want to retrieve the details of products belonging to the company 'Haier' and store the results in 'haier_products.txt', use the following command:
cqlsh -e "SELECT * FROM electric.products WHERE companyname = 'Haier'" > haier_products.txt
This command filters the products based on the company name and saves the results in 'haier_products.txt'.
To get the details of products categorized as 'Washing machine' and store the results in 'washingmachine_products.txt', use the following command:
cqlsh -e "SELECT * FROM electric.products WHERE category = 'Washing machine'" > washingmachine_products.txt
This command filters the products based on the category and saves the results in 'washingmachine_products.txt'.
By following these steps, you can efficiently manage your data in Cassandra, from creating keyspaces and tables to querying and exporting specific data based on your needs.