HBase Schema Design for clickstream data

[Mohit Anchlia <mo...@gmail.com>]

I am starting out with a new application where I need to store users
clickstream data. I'll have Visitor Id, session id along with other page
related data. I am wondering if I should just key off randomly generated
session id and store all the page related data as columns inside that row
assuming that this would also give good distribution accross region
servers. In a session user could send 100s of HTML requests and get
responses. If someone is already doing this in HBase I would like to learn
more about it as to how they have designed the schema.

Re: HBase Schema Design for clickstream data

[Dhaval Shah <pr...@yahoo.co.in>]

We, at Bloomberg, currently do it the way you just described where each user is represented by a row and each column is a particular click/page view..

Regards,
Dhaval


________________________________
From: Mohit Anchlia <mo...@gmail.com>
To: user@hbase.apache.org 
Sent: Tuesday, 26 June 2012 1:34 PM
Subject: HBase Schema Design for clickstream data

I am starting out with a new application where I need to store users
clickstream data. I'll have Visitor Id, session id along with other page
related data. I am wondering if I should just key off randomly generated
session id and store all the page related data as columns inside that row
assuming that this would also give good distribution accross region
servers. In a session user could send 100s of HTML requests and get
responses. If someone is already doing this in HBase I would like to learn
more about it as to how they have designed the schema. 

Re: HBase Schema Design for clickstream data

[Amandeep Khurana <am...@gmail.com>]

That's not a whole lot of information to give you recommendations about the schema. However, at a high level, you should think about structuring your row keys such that you minimize the requirement for scans and can get the required data based on the row keys. 

So, putting the user in the row key would be desirable for the visitor level aggregations. Add to it the session ID. That'll give you user+session level aggregates.

Give us more information about the fields you are storing and what all read patterns you need to address and we'll try to give more concrete recommendations on the schema design. 


On Wednesday, June 27, 2012 at 2:13 PM, Mohit Anchlia wrote:

> Analysis include:
> 
> Visitor level
> Session level - visitors could have multiple levels
> Page hits, conversions - popular pages, sequence of pages hit in one session
> Orders purchased - mostly determined by URL and query parameters
> 
> How should I go about designing schema?
> 
> Thanks
> 
> 
> Sent from my iPad
> 
> On Jun 27, 2012, at 2:01 PM, Amandeep Khurana <amansk@gmail.com (mailto:amansk@gmail.com)> wrote:
> 
> > Mohit,
> > 
> > What would be your read patterns later on? Are you going to read per
> > session, or for a time period, or for a set of users, or process through
> > the entire dataset every time? That would play an important role in
> > defining your keys and columns.
> > 
> > -Amandeep
> > 
> > On Tue, Jun 26, 2012 at 1:34 PM, Mohit Anchlia <mohitanchlia@gmail.com (mailto:mohitanchlia@gmail.com)>wrote:
> > 
> > > I am starting out with a new application where I need to store users
> > > clickstream data. I'll have Visitor Id, session id along with other page
> > > related data. I am wondering if I should just key off randomly generated
> > > session id and store all the page related data as columns inside that row
> > > assuming that this would also give good distribution accross region
> > > servers. In a session user could send 100s of HTML requests and get
> > > responses. If someone is already doing this in HBase I would like to learn
> > > more about it as to how they have designed the schema.
> > 
> 

Re: HBase Schema Design for clickstream data

[Mohit Anchlia <mo...@gmail.com>]

Analysis include:

Visitor level
Session level - visitors could have multiple levels
Page hits, conversions - popular pages, sequence of pages hit in one session
Orders purchased - mostly determined by URL and query parameters

How should I go about designing schema?

Thanks


Sent from my iPad

On Jun 27, 2012, at 2:01 PM, Amandeep Khurana <am...@gmail.com> wrote:

> Mohit,
> 
> What would be your read patterns later on? Are you going to read per
> session, or for a time period, or for a set of users, or process through
> the entire dataset every time? That would play an important role in
> defining your keys and columns.
> 
> -Amandeep
> 
> On Tue, Jun 26, 2012 at 1:34 PM, Mohit Anchlia <mo...@gmail.com>wrote:
> 
>> I am starting out with a new application where I need to store users
>> clickstream data. I'll have Visitor Id, session id along with other page
>> related data. I am wondering if I should just key off randomly generated
>> session id and store all the page related data as columns inside that row
>> assuming that this would also give good distribution accross region
>> servers. In a session user could send 100s of HTML requests and get
>> responses. If someone is already doing this in HBase I would like to learn
>> more about it as to how they have designed the schema.
>> 

Re: HBase Schema Design for clickstream data

[Amandeep Khurana <am...@gmail.com>]

Mohit,

What would be your read patterns later on? Are you going to read per
session, or for a time period, or for a set of users, or process through
the entire dataset every time? That would play an important role in
defining your keys and columns.

-Amandeep

On Tue, Jun 26, 2012 at 1:34 PM, Mohit Anchlia <mo...@gmail.com>wrote:

> I am starting out with a new application where I need to store users
> clickstream data. I'll have Visitor Id, session id along with other page
> related data. I am wondering if I should just key off randomly generated
> session id and store all the page related data as columns inside that row
> assuming that this would also give good distribution accross region
> servers. In a session user could send 100s of HTML requests and get
> responses. If someone is already doing this in HBase I would like to learn
> more about it as to how they have designed the schema.
>