I would like to create an efficient Bigtable row key

Question

I would like to create an optimal row key in Bigtable. I have a table channel_data with 3 columns: channel_id,date,fan_count.

channel_id	date	fan_count
1	2022-03-01	5000
1	2022-03-02	6000
2	2022-03-01	200
2	2022-03-02	300
3	2022-03-03	1000

Users of our application can set up brands/buckets by adding multiple channels. Users can choose any random channel_id.

I want to design an efficient row key to fetch aggregated fan_count in a date range for a brand.

Let's say the user creates a brand with channel_id 1 and 3 and wish to see sum of all fans for the time period 2022-03-01 to 2022-03-03

The result should be 5000+6000+1000=12000

Answer 1

You have a few options here. Because you're looking to do queries based on date, you should probably make that the end part of your rowkey so you can scope down by brand first. You could also use timestamped cells to store multiple values for each channel. Perhaps a week or month of data, so it is grouped together in that way, but this isn't necessary.

Perhaps a rowkey like channel_id/yyyy-mm-dd is what you'd want. You can choose to store the date and channel info in the table, but it isn't necessary since you'd have it in your ids. You can just treat Bigtable like a key/value store in this instance which might be more optimal depending on your scenario.

If you choose to store a month of data per row, you would just make the rowkey something like channel_id/yyyy-mm and just timestamp each value for the day.

Either way for your queries, if you need multiple channels, then you could just do multiple reads or a multi-prefix scan. Let me know if this helps clarify the schema design and if you have more questions.