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 :)
