4 ms·

Great article! The bit about not being able to use columns for each metric because there were too many .... the classic solution is to have a column called “m

Great article! The bit about not being able to use columns for each metric because there were too many .... the classic solution is to have a column called “metric name” and another for “metric value”. Can’t spot why they didn’t just do that.

jsnell 6y ago
Then you lose the benefits that columnar databases have for time series data.
- willvarfar 6y ago
  Yes and no. We already know there were too many named metrics to give each its own column on the system they were using (paraquet on a data lake), so what are they left with? Does a column store like paraquet make a good time series DB? Trendy named time series databases I’ve had the displeasure of using would all fail miserably by high cardinality series too, so I’m not convinced there is actually a better thing than files on a lake for this stuff. So, use some format to name the metric in each row. If paraquet, use dictionary encoding on that column and sort or cluster the rows ... will give go min/max pruning etc. But presto is currently 5x slower to chew through paraquet vs orc so perhaps simply use orc. Or, for this data, Avro or json lines. And then when you’ve used presto to discover interesting metrics you can always use presto (or scalding or whatever your poison is) to extract the metrics you have identified you want to examine more closely and to put them into separate datasets etc. I’m just outlining standard approach’s to these kinds of problems.
- kyllo 6y ago
  You lose a lot of the benefits, but you can still take advantage of time range partition elimination just as long as your data is still physically partitioned by the timestamp column. That's the most important thing when processing time series data--never read from disk any of the data that's outside the time range you actually need for your query.

jsnell 6y ago

Then you lose the benefits that columnar databases have for time series data.

willvarfar 6y ago
Yes and no. We already know there were too many named metrics to give each its own column on the system they were using (paraquet on a data lake), so what are they left with? Does a column store like paraquet make a good time series DB? Trendy named time series databases I’ve had the displeasure of using would all fail miserably by high cardinality series too, so I’m not convinced there is actually a better thing than files on a lake for this stuff. So, use some format to name the metric in each row. If paraquet, use dictionary encoding on that column and sort or cluster the rows ... will give go min/max pruning etc. But presto is currently 5x slower to chew through paraquet vs orc so perhaps simply use orc. Or, for this data, Avro or json lines. And then when you’ve used presto to discover interesting metrics you can always use presto (or scalding or whatever your poison is) to extract the metrics you have identified you want to examine more closely and to put them into separate datasets etc. I’m just outlining standard approach’s to these kinds of problems.
kyllo 6y ago
You lose a lot of the benefits, but you can still take advantage of time range partition elimination just as long as your data is still physically partitioned by the timestamp column. That's the most important thing when processing time series data--never read from disk any of the data that's outside the time range you actually need for your query.