Introduction

Typical data layout in a data ware house is to have fact data rolled up with time and reduce dimensions at each level. Fact data will have dimension keys sothat it can be joined with actual dimension tables to get more information on dimension attributes.

The typical data layout is depicted in the following diagram.

Lens provides an abstraction to represent above layout, and allows user to define schema of the data at conceptual level and also query the same, without knowing the physical storages and rollups, which is described in below sections.

Metastore model

Metastore model introduces constructs Storage, Cube, Dimension, Fact table and Dimtable, Partition. Below we'll provide a brief indroduction of the constructs. You're welcome to checkout the javadoc. You'll find corresponding classes for each of the construct. The entities can be defined by either creating objects of these classes, or by writing xmls according to their schema. The schema is also available in javadoc.

We have followed a convention in naming classes for constructs, class for a Storage is called XStorage and the xml root tag is x_storage. If storage is part of a bigger xml where root tag is some other construct, then the tag is storage. So in all xmls for lens, all and only the outer most tag are x_* and other tags are not.

lens-shell>fact timelines --fact_name sales_aggr_fact2
EndsAndHolesPartitionTimeline(super=PartitionTimeline(storageTableName=mydb_sales_aggr_fact2, updatePeriod=DAILY, partCol=dt, all=null), first=2015-04-12, holes=[], latest=2015-04-12)
EndsAndHolesPartitionTimeline(super=PartitionTimeline(storageTableName=mydb_sales_aggr_fact2, updatePeriod=DAILY, partCol=ot, all=null), first=2015-04-12, holes=[], latest=2015-04-12)
EndsAndHolesPartitionTimeline(super=PartitionTimeline(storageTableName=mydb_sales_aggr_fact2, updatePeriod=DAILY, partCol=pt, all=null), first=2015-04-13, holes=[], latest=2015-04-13)
EndsAndHolesPartitionTimeline(super=PartitionTimeline(storageTableName=local_sales_aggr_fact2, updatePeriod=HOURLY, partCol=dt, all=null), first=2015-04-13-04, holes=[], latest=2015-04-13-05)
EndsAndHolesPartitionTimeline(super=PartitionTimeline(storageTableName=local_sales_aggr_fact2, updatePeriod=DAILY, partCol=dt, all=null), first=2015-04-11, holes=[], latest=2015-04-12)

lens-shell>fact timelines --fact_name sales_aggr_fact2 --storage_name local
EndsAndHolesPartitionTimeline(super=PartitionTimeline(storageTableName=local_sales_aggr_fact2, updatePeriod=HOURLY, partCol=dt, all=null), first=2015-04-13-04, holes=[], latest=2015-04-13-05)
EndsAndHolesPartitionTimeline(super=PartitionTimeline(storageTableName=local_sales_aggr_fact2, updatePeriod=DAILY, partCol=dt, all=null), first=2015-04-11, holes=[], latest=2015-04-12)

lens-shell>fact timelines --fact_name sales_aggr_fact2 --storage_name local --update_period HOURLY
EndsAndHolesPartitionTimeline(super=PartitionTimeline(storageTableName=local_sales_aggr_fact2, updatePeriod=HOURLY, partCol=dt, all=null), first=2015-04-13-04, holes=[], latest=2015-04-13-05)

lens-shell>fact timelines --fact_name sales_aggr_fact2 --storage_name local --update_period HOURLY --time_dimension delivery_time
EndsAndHolesPartitionTimeline(super=PartitionTimeline(storageTableName=local_sales_aggr_fact2, updatePeriod=HOURLY, partCol=dt, all=null), first=2015-04-13-04, holes=[], latest=2015-04-13-05)
lens-shell>

Metastore API

LENS provides REST api, Java client api and CLI for doing CRUD on metastore.

Query Language

User can query the lens through OLAP Cube QL, which is subset of Hive QL.

Here is the grammar:

  [CUBE] SELECT [DISTINCT] select_expr, select_expr, ...
  FROM cube_table_reference
  [WHERE [where_condition AND] [TIME_RANGE_IN(colName, from, to)]]
  [GROUP BY col_list]
  [HAVING having_expr]
  [ORDER BY colList]
  [LIMIT number]

  cube_table_reference:
  cube_table_factor
  | join_table
  join_table:
  cube_table_reference JOIN cube_table_factor [join_condition]
  | cube_table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN cube_table_reference [join_condition]
  cube_table_factor:
  cube_name or dimension_name [alias]
  | ( cube_table_reference )
  join_condition:
  ON equality_expression ( AND equality_expression )*
  equality_expression:
  expression = expression
  colOrder: ( ASC | DESC )
  colList : colName colOrder? (',' colName colOrder?)*

  TIME_RANGE_IN(colName, from, to) : The time range inclusive of ‘from’ and exclusive of ‘to’.
  time range clause is applicable only if cube_table_reference has cube_name.
  Format of the time range is <yyyy-MM-dd-HH:mm:ss,SSS>

OLAP Cube QL supports all the functions that hive supports as documented in Hive Functions

Query engine provides following features :

• Figure out which dimension tables contain data needed to satisfy the query.
• Figure out the exact fact tables and their partitions based on the time range in the query. While doing this, it will try to minimize computation needed to complete the queries.
• If No Join condition is passed for Joins,join condition will be inferred from the relationship definition.
• Projected fields can be added to group by col list automatically, if not present and if the group by keys specified are not projected, they can be projected
• Automatically add aggregate functions to measures specified as measure's default aggregate.

  Various configurations available for running an OLAP query are documented at OLAP query configurations

Relative time range

Relative timerange is helpful to the users in scheduling their queries. We'll explain here with example. User can specify the HOURLY granularity with 'now.hour'.

The follwong table tells the available unit granularities and how to specify those granualarities for relative timerange

 query execute cube select col1 from cube where TIME_RANGE_IN(col2, "now.hour-4hours", "now.hour")

 The above queries for the last 4hours data.

Absolute time range

Users can query the data with absolute timerange at different granularities. The following table describes how to specify absoulte timerange at different granularities

 query execute cube select col1 from cube where TIME_RANGE_IN(it, "2014-12-29-07", "2014-12-29-11")

 query execute cube select col1 from cube where TIME_RANGE_IN(it, "2014-12-29", "2014-12-30")

 It queries the data between 29th Dec 2014 and 30th Dec 2014.

Bridge Table

A bridge table sits between a cube and a dimension or between two dimensions and is used to resolve many-to-many relationships. Refer following for more details :

• Kimball group's definition
• Kimball group's design tip
• Pythian blog

  User can specify if any destination link in join-chain maps to many-many relationship during the creation of cube/dimension.

Flattening feature

When we look at the following example :

User :

User interests :

Sports :

User Interests is the bridge table which is capturing the many-to-many relationship between Users and Sports. And if we have a fact as follows :

If analyst is interested in analyzing with respect to user's interested sport, then the report would look the following :

Though the individual rows are correct and the overall revenue is actually 150, looking at above report makes people assume that overall revenue is 350. The flattening feature to optionally flatten the selected fields, if fields involved are coming from bridge tables in join path. If flattening is enabled, the report would be the following :

When there is an expression around the bridge table fields, user might be interested in doing field aggregations on top of the expression defined. Also, simple filters on the fields should be applied to the array generated. The feature provides capability for the same.

For ex: "select user.sport, revenue from sales where user.sport in ('CRICKET')" would convert the filter user.sport in 'CRICKET' to contains check in aggregated user sports.

See configuration params available at OLAP query configurations and look for config related to bridge tables, for turning this on.