Suggest appropriate Hbase schema for integration with Hive
===

I am evaluating Hbase + Hive integration as a base for BI solution for site events. I'm sure that such case is rather standard but I am a complete new-bye in the subject and will be glad to hear any suggestion about possible model.

Here is possible (a bit simplified) solution description. Raw events are characterized by several parameters like client_id, product, window period start/end, event type and number of so-typed events during window period. In addition to raw events, there are two types of information to be stored:

- aggregation of raw events for predefined periods: daily, weekly, monthly
- ad-hoc queries results (for example, top ten products for certain event type)

My question is about possibilities to store this information in form optimized for Hbase+Hive integration. I expect to store raw data in HBase table, calculate aggregations  and ad-hoc query results with Hive map-reduce reading data from this table and storing results to other Hbase tables. As I understand for predefined period aggregations, Hbase own means (like coprocessors) can be used.

So here are possible tables organizations, I am thinking about.

Variant 1.
Table site_events. Row key - composite key client + event type. Column families: info(client, event_type, product), raw_data(start, end, number) with TTL 1 day, daily(start, end, number) with TTL 1 week, weekly(start, end, number).
Pros: it is easy to take query data for given client (and event type), built-in TTL functionality handles data expiration.
Cons: As I understand, for Hive all column family columns will be treated as having one type. Hive with Hbase external table doesn't work with composite keys.

Variant 2.
Table site_events. Row key - client + timestamp. Columns: client, event_type, product, start, end, number. Aggregations like ad-hoc queries are stored in separate tables filled in in Hbase by Hive.
Pros: Plain structure should be easier to read from Hive.
Cons:  As I understand, no TTL support can be used. Querying of data for given client/event type is trickier (secondary indexes?)

I would be glad to hear any thoughts about this use case.
