saylidalvi13_184114 avatar image
saylidalvi13_184114 asked Erick Ramirez edited

Indexing Column in Cassandra Table

Hi Team, I have a usecase where i want to retrieve details using 3 different columns just having different hierarchy storing similar data. Date is my partition key and daily volume of data is almost 30 million records. Just to retrieve the data using 3 different query parameters I am not in favour of duplicating data with 3 times for 3 queries as the volume would be 120 million records daily. Can I use secondary indexing here and what would be the performance impact

Kindly consider following sample structure :

CREATE TABLE amount_details (
    file_date date,
    shop_unit_id long,
    line_id UUId,
    region_unit_id long,
    company_unit_id long,
    total_sale_amount decimal,
    shop_name text,
    region_name text,
    company_name text,
    PRIMARY KEY ((file_date,hash_id) shop_unit_id, line_id)

Here, total record volume for daily would be around 30 million

and mapping is like , 1 company_unit can have multiple region_units and 1 region_unit can belong to multiple shop_units

My use case needs 4 queries

  • retrieve total amount details for given date range on file_date
  • retrieve total amount for given date range for shop_unit_id
  • retrieve total amount for given date range for region_unit_id
  • retrieve total amount for given date range for company_id

Kindly help me with some solution :)

data modeling
10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

Erick Ramirez avatar image Erick Ramirez ♦♦ commented ·

There isn't enough info in your post to make an informed recommendation. Please post the following:

  • table schema
  • queries you want to run against the table


0 Likes 0 ·
saylidalvi13_184114 avatar image saylidalvi13_184114 Erick Ramirez ♦♦ commented ·

[Posted in original question]

0 Likes 0 ·

0 Answers