5

I have a situation where I want to track page views, click events, etc. in a time series database but am having scalability issues in the case where I want to retrieve aggregated groups of data that has very high cardinality.

The problems I'm trying to solve are:

What are the top N referrers within a given time range?

How many views does a URL have for each URL within a given time range?

How many views do specific URLs have throughout all time?

The schema I have so far is:

timestamp - Time of the event

domain - The Base URL for the record

uri - The unique resource. Would like grouped counts of these (Millions of possible values)

referrer - the HTTP referrer. Grouped counts of these as well (Millions of possible values)

event - The type of event

So far I've tried using InfluxDB, but discovered issues due to the sheer amount of possible values for the uri and referrer. Although I would only be scanning for record within a small time range, grouping by millions of unique possible values makes this a lot harder. What other options do I have to store data that support both the write/query requirements?

Improve this question
2
  • 2
    This is probably more a data modeling issue than a database selection issue.
    – Karl Bielefeldt
    Oct 25, 2017 at 21:56
  • This is usually solved by copying whole database into data warehouse, that is specially made for this kind of aggregation.
    – Euphoric
    May 16, 2018 at 7:06

3 Answers 3

Reset to default
6

Performing complex queries in real time on large time series data is not scalable. It puts an unreasonable load on your store, and will not perform well regardless of the database used. The only time you should be querying your time series database directly is when you want to look at individual events (e.g. a table of events or look at a single log entry).

You should index your data in a way that is scalable to query. For example, you can aggregate views over a period of time (minutes, hours, days, weeks), or grouped by some other metric (referrer, event type, or domain), so when you need to query for the views of a given entity for a month, you're querying hundreds of rows, not millions.

The index can be stored wherever you want (e.g. we stored ours in a relational database) because the load and data size should be much smaller. You can create the index by either a stream analytics pipeline, or periodically by a batch process. Using streaming analytics means your data will be (nearly) immediately available for querying, but it will likely be more complex to do right. Batch processing will have some latency before data is queryable, but is easier to implement (usually just a cron job that runs periodically and indexes the latest data). For a stream analytics framework, check out Apache Spark Streaming. For batch processing, Apache Spark is a popular choice.

Improve this answer
3
  • Good answer. In some cases, a custom "database" is the best solution, for time-series data especially.
    – Frank Hileman
    Oct 25, 2017 at 22:27
  • Where should I start if I wanted to look at stream analytics as a solution?
    – AnonymousCoward
    Oct 25, 2017 at 22:44
  • @AnonymousCoward I added to my answer. Streaming analytics is not the only option, you could also do batch analytics. Building the index is the important part. Apache Spark and Apache Spark Streaming are tools that can do either.
    – Samuel
    Oct 25, 2017 at 22:53
-1

You could try Druid, It’s quite fast for ad hoc queries http://druid.io/docs/0.12.0/querying/topnquery.html

Improve this answer
-1

It looks like you need fast analytical database, which can process raw events (rows) at high speed. Take a look at ClickHouse. This analytical database can scan billions of rows per second per CPU core and its performance scales with the number of available CPU cores. It also compresses the data before storing it on disk. This may significantly reduce disk space usage while improving query speed when the scanned data is missing in the operating system page cache.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.