Da Data c cubes i in A n Apache he H Hive Amareshwari - - PowerPoint PPT Presentation

▶
da data c cubes i in a n apache he h hive
SMART_READER_LITE
LIVE PREVIEW

Da Data c cubes i in A n Apache he H Hive Amareshwari - - PowerPoint PPT Presentation

Sep 16, 2023 340 likes •679 views

Da Data c cubes i in A n Apache he H Hive Amareshwari Sriramadasu Jaideep Dhok Engineer at Inmobi Amareshwari Apache Hive Committer Apache Hadoop PMC Sriramadasu Working in Hadoop and eco systems since 2007

slide-1
SLIDE 1

Da Data c cubes i in A n Apache he H Hive

Amareshwari Sriramadasu Jaideep Dhok

slide-2
SLIDE 2

Amareshwari Sriramadasu

  • Engineer at Inmobi
  • Apache Hive– Committer
  • Apache Hadoop – PMC
  • Working in Hadoop and eco

systems since 2007

slide-3
SLIDE 3

Jaideep Dhok

  • Engineer at Inmobi
  • Working in distributed

systems and Hadoop eco system since 2007

  • Apache Hive and Hadoop

Contributor

slide-4
SLIDE 4

Background Why Apache Hive Data cubes in Hive Queries on data cubes with examples Road map

Agenda

slide-5
SLIDE 5

Background Why Apache Hive Data cubes in Hive Queries on data cubes with examples Road map

Agenda

slide-6
SLIDE 6

Global Mobile technology company enabling

  • Developers & Publishers to monetize
  • Advertisers to engage and acquire users

@ Scale

About InMobi

slide-7
SLIDE 7

Digital advertising – Intro

Courtesy: http://www.liesdamnedlies.com/

Owns & Sells Real estate on digital inventory Has reach to users Wants to target Users Brings money Market place Consumer

slide-8
SLIDE 8

Hadoop @ InMobi – Factual Reporting & Analytics

Ø 130 TB Hadoop warehouse Ø 5 TB SQL warehouse Ø Pipelines

slide-9
SLIDE 9

Use cases

  • Sharing reports/data with customer (account

level)

  • Understand trends through data &

exploration (analysis)

  • Debugging / Postmortem of issues

(troubleshooting)

  • Sizing & Estimation (Ex: inventory, reach)
  • Summary of Product lines, Geographies,

Network (Ex: Rev by Geo)

  • Sales/Revenue Targets vs Actuals
  • Tracking campaign performance
  • Tracking any metrics on REAL- TIME basis

Categorize use cases

  • Batch queries
  • Adhoc queries
  • Interactive queries
  • Canned reports
  • Scheduled reports
  • Infer insights through ML algorithms

Use cases

slide-10
SLIDE 10

Adhoc Querying system

  • Adhoc and Batch queries
  • Scheduled queries
  • Based on Hadoop Mapreduce
  • Provides UI and custom api
  • Data is stored in HDFS

Dashboard system

  • Canned reports
  • Scheduled reports
  • Interactive and adhoc queries
  • Provides UI and Custom api
  • Data is stored in columnar DWH, InfoBright

Customer facing system

  • Face to the outside world (Advertisers and

publishers)

  • Interactive and adhoc queries
  • Provides UI and custom api
  • Data is stored in relational DB, Postgres

Current state of analytics - Reporting

slide-11
SLIDE 11
  • Disparate user experience
  • Disparate data storage systems causing inability to scale
  • Not leveraging community around

Current State - Problems

slide-12
SLIDE 12

Background Why Apache Hive Data cubes in Hive Queries on data cubes with examples Road map

Agenda

slide-13
SLIDE 13

Associates structure to data Provides Metastore and catalog service – Hcatalog Provides pluggable storage Accepts SQL like queries HQL is widely adopted language by systems like Shark, Impala Provides pluggable interface for adding new storage Has strong apache community Data warehouse features like facts, dimensions Logical table associated with multiple physical storages Pluggable execution engine for HQL Query history, caching Scheduling queries

What does Hive provide What is missing in Hive

Apache Hive to the rescue

slide-14
SLIDE 14

Background Why Apache Hive Data cubes in Hive Queries on data cubes with examples Road map

Agenda

slide-15
SLIDE 15

Data Layout

Aggrk : measures (mak <= ma(k-1)), dimensions (dak < da(k-1)) ….. Aggr2 : measures (ma2 <= ma1), dimensions (da2 < da1) Aggr1 : measures (ma1 <= mr), dimensions (da1 < dr) Raw data : measures (mr), dimensions(dr)

Other side of pyramid is aggregated at timed dimension

slide-16
SLIDE 16

Data Layout

Aggrk : measures (mak <= ma(k-1)), dimensions (dak < da(k-1)) ….. Aggr2 : measures (ma2 <= ma1), dimensions (da2 < da1) Aggr1 : measures (ma1 <= mr), dimensions (da1 < dr) Raw data : measures (mr), dimensions(dr)

Dim2-1 Dim3 Dim2 Dim4-1 Dim4 Dim1

slide-17
SLIDE 17

Data Model

Cube Storage Fact Table Physical Fact tables Dimension Table Physical Dimension tables

slide-18
SLIDE 18

Data Model - Cube

Dimension

  • Simple Dimension: name,

type, start date, end date

  • Referenced Dimension :

Referencing table and column

  • Hierarchical

Dimension :hierarchy

  • Expression Dimension :

Associated expression Measure

  • Column Measure : name,

type, default aggregate, format string, start date, end date

  • Expression Measure :

Associated Expression

Cube Measures Dimensions

slide-19
SLIDE 19

Data Model – Storage

Storage

Name End point Properties Ex : UA2, UJ1, Mpower-IB

slide-20
SLIDE 20

Data Model – Fact Table

Fact table Cube

Fact table Storage

Fact Table

Columns Cube that it belongs Storages on which it is present and the associated update periods

slide-21
SLIDE 21

Data Model – Dimension table

Dimension Table

Columns Dimension references Storages on which it is present and associated snapshot dump period, if any.

Cube Dimension table Dimension table

Dimension table Storage

slide-22
SLIDE 22

Data Model – Storage tables and partitions

Storage table

Associated storage descriptor Partitioned by columns

  • Fact storage table

Fact table

  • Dimension storage table

Dimension table

slide-23
SLIDE 23

Background Why Apache Hive Data cubes in Hive Queries on data cubes with examples Road map

Agenda

slide-24
SLIDE 24

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 [alias] | ( cube_table_reference ) join_condition: ON equality_expression ( AND equality_expression )* equality_expression: expression = expression colOrder: ( ASC | DESC ) colList : colName colOrder? (',' colName colOrder?)*

Queries on Data cubes

slide-25
SLIDE 25
  • Resolve candidate dimension tables and the

storage tables .

  • Resolve the candidate fact tables and the storage

tables for the queried time range.

  • Automatically resolve joins using the relationships

between cubes and dimension.

  • Automatically add aggregate functions to measures.
  • Add expression to group by clause, if projected; and

project group by clause, if it is not. Querying features

slide-26
SLIDE 26
  • SELECT ( citytable . name ), ( citytable . stateid ) FROM c2_citytable citytable

LIMIT 100

  • SELECT ( citytable . name ), ( citytable . stateid ) FROM c1_citytable citytable

WHERE (citytable.dt = 'latest') LIMIT 100

cube select name, stateid from citytable limit 100

Example query

slide-27
SLIDE 27

Example query

  • SELECT (citytable.name), sum((testcube.msr2)) FROM c2_testfact testcube INNER JOIN

c1_citytable citytable ON ((testcube.cityid)= (citytable.id)) WHERE (( testcube.dt='2014-03-10-03') OR (testcube.dt='2014-03-10-04') OR (testcube.dt='2014-03-10-05') OR (testcube.dt='2014-03-10-06') OR (testcube.dt='2014-03-10-07') OR (testcube.dt='2014-03-10-08') OR (testcube.dt='2014-03-10-09') OR (testcube.dt='2014-03-10-10') OR (testcube.dt='2014-03-10-11') OR (testcube.dt='2014-03-10-12') OR (testcube.dt='2014-03-10-13') OR (testcube.dt='2014-03-10-14') OR (testcube.dt='2014-03-10-15') OR (testcube.dt='2014-03-10-16') OR (testcube.dt='2014-03-10-17') OR (testcube.dt='2014-03-10-18') OR (testcube.dt='2014-03-10-19') OR (testcube.dt='2014-03-10-20') OR (testcube.dt='2014-03-10-21') OR (testcube.dt='2014-03-10-22') OR (testcube.dt='2014-03-10-23') OR (testcube.dt='2014-03-11') OR (testcube.dt='2014-03-12-00') OR (testcube.dt='2014-03-12 -01') OR (testcube.dt='2014-03-12-02') )AND (citytable.dt = 'latest') GROUP BY(citytable.name)

cube select citytable.name, msr2 from testcube where timerange_in(dt, '2014-03-10-03’, '2014-03-12-03’)

slide-28
SLIDE 28

Stats

  • Number of queries - 700 to 900 per day
  • Number of dimension tables - 125
  • Number of fact tables – 24
  • Number cubes – 15
  • Size of the data
  • Total size – 136 TB
  • Dimension data – 400 MB compressed per hour
  • Raw data - 1.2 TB per day
  • Aggregated facts- 53GB per day

Data ware house statistics

slide-29
SLIDE 29

Available in Hive

  • Data warehouse features like

facts, dimensions

  • Logical table associated

with multiple physical storages

Available in github

  • Pluggable execution engine for

HQL

  • Query history, caching
  • Scheduling queries

What is available

slide-30
SLIDE 30

Implements an interface

  • execute
  • explain
  • executeAsynchronously
  • fetchResults
  • Specify all storages it can support

Pluggable execution engine

slide-31
SLIDE 31

Cube QL query Rewrite query for available execution engines Get cost of the rewritten query from each execution engine Pick up execution engine with least cost and fire the query

Cube query with multiple execution engines

slide-32
SLIDE 32

Future roadmap: Unified analytics

slide-33
SLIDE 33

Thank you!

  • amareshwari@apache.org
  • jaideep.dhok@inmobi.com