Hi again folks! Related to my previous question, b...
# troubleshooting
d
Hi again folks! Related to my previous question, but not the same: what's the best partitioning strategy for a STRING column: Murmur, HashCode or ByteArray? What are the criteria I should use to choose what's the best for my case?
m
Always use murmur (going by the deployments I have seen).
d
Cool! And would it be a problem to set it for 1000 partitions? Here's my concrete case: we have data organized by regions and sectors, so I'd like to use them as information to determine the partitions I want the data searches to be done at, but I'd also like to add the year of the data; So I'm thinking of using a column where I'd store
region|sector|year
and then use this column as the partition reference, and then create 1000 partitions beforehand, to significantly reduce the amount of places the data has to be searched from. Does this strategy sound any silly?
m
Doesn't sound silly at all. Should be well partitioned(read: evenly) data. Depending on the type of data you may end up with some lumpyness, but with that wide of distribution it should come out in the wash
d
Nice, good to know! 🙂
k
One cautionary note - we did something similar (where region == country), and the US data dominated. So if we created “reasonable” segment sizes for the general case, we wound up with huge segments for the US. We solved that by sub-partitioning the US data by a hash of yet another field.
2
m
Actually, creating 1000 partitions means Pinot will have to consume from 1000 partitions, and you will end up creating too many small segments (which will have to be merged and rolled up).
What’s your event rate? We have seen 32-64 partitions hold really well for 100k events / sec. so I’d avoid creating 1000 segments upfront.
d
Thanks Ken! We're actually going to want to partition by sector too, which is an organization structure that resides in a region for us. We also have the issue with much data for US than other regions, but breaking by sector helps us distributing the data a better. Mayank, we're actually going to use batch ingestion, for this case, not realtime. Each batch will be a daily run for each region. In this fashion, we can still have many segments marked to one partition, right?
My idea would be that, for each new year that comes, we would have a new partition identifier for each region, and then keep creating segments for these partitions. Then, when the year turns, new partitions would be used. Does this approach make sense?
m
Ken's comment has me thinking....is the string your partitioning by the concatation if the 3 values into a single string?
m
What’s the total data size Diogo (ballpark)?
Even in offline if you have 1000 partitions, are you going to create 1000 segments per day (period)? If not, then you will have multiple partitions in a single Pinot segment anyways.
d
Mitchell: yep, that's the idea. Mayank: I'll have to check how much we have, but a very rough and possibly around 1 billion rows. What we'll need to do is to run one batch per day for each region, in which case I expect the segment for a day/region to contain one partition for each sector (since the partitioning will be on the string that concatenates region|sector|year. Is my thinking correct?