what is the best practice to design the data "table" in druid

[lulu Lee]

hi, all

as we analyzer a website's visitors, for example, we need show these data in the report:

    pv, sessions, visitors, browser, geo, referer (and more)

if we use MySQL, for best performance, we will split the data into the small dimension table:

CREATE TABLE `trend_browser` (

  `date` int(11) NOT NULL DEFAULT '0',

  `hour` tinyint(4) NOT NULL DEFAULT '0',

  `ips` int(11) unsigned NOT NULL DEFAULT '0',

  `pageviews` int(11) unsigned NOT NULL DEFAULT '0',

  `sessions` int(11) unsigned NOT NULL DEFAULT '0',

  `visitors` int(11) unsigned NOT NULL DEFAULT '0',

  `browser_id` int(11) unsigned NOT NULL DEFAULT '0',

  `browser_version` int(11) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`date`,`browser_id`,`browser_version`,`hour`)

) ENGINE=MyISAM;

CREATE TABLE `trend_geo` (

  `date` int(11) NOT NULL DEFAULT '0',

  `hour` tinyint(4) NOT NULL DEFAULT '0',

  `ips` int(11) unsigned NOT NULL DEFAULT '0',

  `pageviews` int(11) unsigned NOT NULL DEFAULT '0',

  `sessions` int(11) unsigned NOT NULL DEFAULT '0',

  `visitors` int(11) unsigned NOT NULL DEFAULT '0',

`country_id` int(5) unsigned NOT NULL DEFAULT '0',

  `region_id` int(5) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`date`,`country_id`,`region_id`,`hour`)

) ENGINE=MyISAM;

CREATE TABLE `trend_referer` (

  `date` int(11) NOT NULL DEFAULT '0',

  `hour` tinyint(4) NOT NULL DEFAULT '0',

  `ips` int(11) unsigned NOT NULL DEFAULT '0',

  `pageviews` int(11) unsigned NOT NULL DEFAULT '0',

  `sessions` int(11) unsigned NOT NULL DEFAULT '0',

  `visitors` int(11) unsigned NOT NULL DEFAULT '0',

  `referer_host_id` int(11) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`date`,`referer_host_id`,`hour`)

) ENGINE=MyISAM;

these table just for an example, in practice, these data has 50+ Dimension and 20+ Metrics.

so, in druid, may be we can put all the Dimension and Metrics in one segment ?

or just split Dimensions to small segments like what we do in MySQL ?

--
@QLeelulu | FaWave, Net4 Team | qlee...@gmail.com | 学海无涯,回头是岸 

[Gian Merlino]

I'd suggest putting all 50 dimensions and 20 metrics in the same druid datasource (our word for "table").

[zhao weinan]

that's a good question, I'm also wondring that assume we have dimession A with a huge cardinal, then query w/o filter on A will do an on-fly aggregation that go through all value on A, will that be inefficient?

在 2014年10月17日星期五UTC+8上午1时28分57秒，lulu Lee写道：

[Gian Merlino]

I guess you mean groupBys or topNs that involve that high-cardinality dimension A. In that case, yes, those queries will be less efficient than queries involving lower cardinality dimensions.

[zhao weinan]

not only groupBys or topNs, for instance: we have dimension A and B, metrics X and Y, where A's cardinality is 100k, then a "SELECT X,Y FROM source WHERE B=b"  will traverse maximum 100k records to aggregate the metrics, if I'm correct.

In the past we will pre-caculate the aggregation to two table: one for dimension A and B, another for dimension B. So i'm wondering why druid dosent need this...

在 2014年10月17日星期五UTC+8下午9时58分04秒，Gian Merlino写道：

[Fangjin Yang]

Druid is a column store, so only those columns which are required for a query will ever be scanned. If you issue queries that don't involve dimension A in your example, Druid does not need to scan dimension A.

[zhao weinan]

Yes, Druid dose not need to scan dimension A. But giving a schema with dimension A and B, Druid will store a row for each element in Cartesion product:AxB, so query a metric with filter on B=b, will sum all metric value where B=b, in worst case need to sum the number of A's cardinality values?

在 2014年10月17日星期五UTC+8下午11时04分09秒，Fangjin Yang写道：

[Gian Merlino]

Ah, are you talking about the fact that if you include a high-cardinality dimension A, it will lead to the dataset in Druid possibly having more rows than it otherwise would, because of rollup not being as effective? That is true, depending on the other dimensions in your dataset. (Sometimes the other dimensions "partially explain" a high cardinality dimension and so including it doesn't hurt as much as you might think; but this depends a lot on your data.)

For this reason people sometimes choose to index high-cardinality fields using hyperUnique (if all they care about is the number of unique values) rather than as dimensions. hyperUnique is an aggregator and so it won't expand the row count of your dataset-- just expand the size of each row somewhat.

[zhao weinan]

Yes that's what I mean. With more dimensions, more rows in dataset, so query with few dimensions need to do lots works to rollup.

AFAIK metamarkets use Druid to do  ad-tech business analatics, so maybe dimensions will include: time(year,day,hour),advertiser-side(advertiser,ad-group,ad-creative,...),publisher-side(publisher,sub-channel,web-page,...),user-side(geo-location,user-agent,os...)...... maybe dozens of it. Then a query with "SELECT click_num FROM dataset WHERE date=20141017 GROUP BY hour" will do a lot of works on-the-fly, is there some optimation for this case?

在 2014年10月17日星期五UTC+8下午11时37分15秒，Gian Merlino写道：

[Gian Merlino]

There is no explicit optimization for that case, although Druid is designed to do fast column scans. In the case of a query like "SELECT SUM(click_num) FROM dataset WHERE date=20141017 GROUP BY hour" (which in Druid would be a "timeseries" query) the only columns that need to be scanned are timestamp and click_num, and only for that particular day. On a cluster with an appropriate level of parallelism relative to data volume, this can generally be completed in a few hundred milliseconds.

[zhao weinan]

Got it, thanks for the reply!

In my company, this query will be pretty frequent, then my instinct reaction is do pre-aggregation to generate multiple "materialized views". So maybe with immutable data segements and proper cache, the query performance will be good enough.

在 2014年10月19日星期日UTC+8上午5时39分14秒，Gian Merlino写道：

[Fangjin Yang]

Hi Zhao, if it helps, we do not precompute any views and issue ad-hoc queries that aggregate billions of rows. If you are interested in Druid performance you can look at:

http://druid.io/blog/2014/03/17/benchmarking-druid.html

or

http://static.druid.io/docs/druid.pdf for real production numbers.

[zhao weinan]

Hi FJ, I've just read this two papers, and the ingestion throughput as well as the query latency in production and on TPC-H is impressive.

Seeing the production cluster with 10TB RAM, I think the RAM capacity is critical to druid's query performance, from a certain perspective will druid historical nodes look like  in-memory db? And I've noticed the queries per minute in production dataset, is that the limit of the cluster?

在 2014年10月20日星期一UTC+8上午5时05分54秒，Fangjin Yang写道：

[Nishant Bangarwa]

Hi zhao, 

see Inline

On Mon, Oct 20, 2014 at 3:46 PM, zhao weinan <xcvi...@gmail.com> wrote:

Hi FJ, I've just read this two papers, and the ingestion throughput as well as the query latency in production and on TPC-H is impressive.

Seeing the production cluster with 10TB RAM, I think the RAM capacity is critical to druid's query performance, from a certain perspective will druid historical nodes look like  in-memory db?

Druid memory maps the segments and rely on OS to columns in and out of memory, thus the data which is frequently queried is kept in memory and rest is paged out of memory. 

you can configure the max data loaded in the historical to tune the amount of paging in cluster.

also note that paging will slow down query performance depending on the hardware. in general the slowdown in more for normal disks than SSDs.

    

And I've noticed the queries per minute in production dataset, is that the limit of the cluster?

these query per minute are the queries served by our prod cluster and not the result of a limit testing, 

fwiw, the no. of parallel segment scans that can be done at any instant by the cluster is equal to no. of processing threads (default : no. of cores -1) * no. of historical nodes. 

 

--
You received this message because you are subscribed to the Google Groups "Druid Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to druid-developm...@googlegroups.com.
To post to this group, send email to druid-de...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/druid-development/ba1d40ff-be48-4fda-aa36-c305ddbb52a0%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Nishant Software Engineer | METAMARKETS m  +91-9729200044 nishant....@metamarkets.com

[zhao weinan]

Hi Nishant

That's make sense, thanks for replying.

It's there any throuput benchmark result to share?

在 2014年10月20日星期一UTC+8下午9时23分26秒，Nishant Bangarwa写道：

[Fangjin Yang]

Hi Weinan,

Our public benchmarks are mainly located here for queries:

druid.io/blog/2014/03/17/benchmarking-druid.html

http://static.druid.io/docs/druid.pdf

We don't really have benchmarks for paging time with SSDs vs HDDs

[zhao weinan]

Thanks, we will give it a try.


在 2014年10月21日星期二UTC+8上午3时49分15秒，Fangjin Yang写道：