Hive views and indexing

Bitmap Indexing ExplainedKartikeya Sharma · Bitmap indexingBitmap Indexes Explained: Efficient Database Filtering for BeginnersCodeLucky · Bitmap indexes for beginnersIndexing Techniques in Data Warehousing: B-Tree, BitmapCOURSE PLEASE · B-tree vs bitmap indexes

Views in HiveSearch on YouTubeBookmarkSaved

A Hive view is a searchable object defined by a query. It behaves like a Hive table. It stores no data of its own. For this reason it is also called a virtual table.

A view stores its defining query, not data. Query it like a table. Combine data from more than one table with joins inside the view. Keep only a subset of rows or columns to hide the rest from some users.

The slides list the same uses as SQL views. They are: save any result set, run operations through the view, and expose a subset of the data.

Creating a Hive viewSearch on YouTubeBookmarkSaved

CREATE VIEW [IF NOT EXISTS] view_name
  [(column_name [COMMENT column_comment], ...)]
  [COMMENT table_comment]
AS SELECT ...;

Name the view. List output columns if the SELECT names need overrides. End with the SELECT that defines the rows.

Base table emp_tabSearch on YouTubeBookmarkSaved

The slides build every view demo on one table, emp_tab, with 7 columns and 6 rows:

SELECT * FROM emp_tab;

emp_tab.col1  emp_tab.col2  emp_tab.col3  emp_tab.col4  emp_tab.col5  emp_tab.col6  emp_tab.col7
1281  Shawn    Architect  7890  1481  10  IXZ
1381  Jacob    Admin      4560  1481  20  POI
1481  flink    Mgr        9580  1681  10  IXZ
1581  Richard  Developer  1000  1681  40  LKJ
1681  Mira     Mgr        5098  1481  10  IKZ
1781  John     Developer  6500  1681  10  IXZ
Time taken: 2.176 seconds, Fetched: 6 row(s)

Narrow view emp_view2Search on YouTubeBookmarkSaved

The first view keeps only 3 of the 7 columns:

CREATE VIEW emp_view2 AS SELECT col1, col2, col3 FROM emp_tab;

SELECT * FROM emp_view2;

emp_view2.col1  emp_view2.col2  emp_view2.col3
1281  Shawn    Architect
1381  Jacob    Admin
1481  flink    Mgr
1581  Richard  Developer
1681  Mira     Mgr
1781  John     Developer
Time taken: 0.773 seconds, Fetched: 6 row(s)

Full view emp_view1Search on YouTubeBookmarkSaved

The second view keeps the whole table. The slide hides its query text. The output matches a full-table select:

CREATE VIEW emp_view1 AS SELECT * FROM emp_tab;

SELECT * FROM emp_view1;

emp_view1.col1  emp_view1.col2  emp_view1.col3  emp_view1.col4  emp_view1.col5  emp_view1.col6  emp_view1.col7
1281  Shawn    Architect  7890  1481  10  IXZ
1381  Jacob    Admin      4560  1481  20  POI
1481  flink    Mgr        9580  1681  10  IXZ
1581  Richard  Developer  1000  1681  40  LKJ
1681  Mira     Mgr        5098  1481  10  IKZ
1781  John     Developer  6500  1681  10  IXZ
Time taken: 0.424 seconds, Fetched: 6 row(s)

(The reconstructing query above is marked (extra): the slide shows the output, not the query.)

Salary-filter view emp_35000Search on YouTubeBookmarkSaved

The last view demo filters rows. The base table is employee:

CREATE VIEW emp_35000 AS
SELECT * FROM employee
WHERE salary > 35000;

Altering a viewSearch on YouTubeBookmarkSaved

Two ALTER forms appear on the slides. One replaces the defining query. The other renames the view.

ALTER VIEW emp_view1 AS SELECT col1 FROM emp_tab;

SELECT * FROM emp_view1;

emp_view1.col1
1281
1381
1481
1581
1681
1781
Time taken: 0.175 seconds, Fetched: 6 row(s)

The view now has one column. All 6 ids survive.

ALTER VIEW emp_view1 RENAME TO emp_view_new;

SELECT * FROM emp_view_new;

emp_view_new.col1
1281
1381
1481
1581
1681
1781
Time taken: 0.72 seconds, Fetched: 6 row(s)

Rename changes the name. The rows stay the same.

Indexing in HiveSearch on YouTubeBookmarkSaved

An index speeds up queries that filter on a column. Use one when the dataset is very large, when a query runs longer than expected, when speed matters, and while building a data model.

A Hive index lives in a separate table. It never touches the data inside the base table. Like a table, an index can itself be partitioned when the data is large.

An index is a pointer to a column of a table. The user creates it by hand. The index does not track base-table changes. Run ALTER INDEX ... REBUILD after the data changes.

Compact and bitmap index typesSearch on YouTubeBookmarkSaved

The slides name two index types:

• Compact index. Stores pairs of indexed column value and block id. A query on the value goes straight to the listed blocks.
• Bitmap index. Stores each distinct value together with a bit vector over the rows. Bit $i$ is 1 when row $i$ holds the value, else 0.

Both serve the same purpose. Fetch time differs with the data in the indexed column. A later section times both on the same query.

Creating an indexSearch on YouTubeBookmarkSaved

CREATE INDEX index_name
ON TABLE base_table_name (col_name, ...)
AS index_type
[WITH DEFERRED REBUILD]
[IN TABLE index_table_name]
[COMMENT 'index comment'];

index_type is the full handler class for a compact index, or the word BITMAP for a bitmap index. WITH DEFERRED REBUILD leaves the new index empty until an ALTER rebuilds it.

Example 1: an attendance table with columns roll_id, class, days_attended:

CREATE INDEX index_attendance ON TABLE mark_sheet(days_attended)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD;

Example 2: a compact index on an employee id:

CREATE INDEX idx_EmpID_Emp ON TABLE Emp(EmpID)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD;

The same name with a bitmap index on the department column instead:

CREATE INDEX idx_EmpID_Emp ON TABLE Emp(DeptNo)
AS 'BITMAP'
WITH DEFERRED REBUILD;

The slides use both the full class name and the short words COMPACT and BITMAP as the index type. Both forms appear in the demo commands below.

The EMP_INFO demo tableSearch on YouTubeBookmarkSaved

The timing demo indexes a 20-row table, EMP_INFO (emp_id, emp_name, dept, joining_year):

The demo creates the index with the short type word:

CREATE INDEX ind_dept ON TABLE EMP_INFO(dept)
AS 'COMPACT' WITH DEFERRED REBUILD;

Two more variants change only the storage of the index table:

CREATE INDEX ind_dept2 ON TABLE EMP_INFO(dept)
AS 'COMPACT' WITH DEFERRED REBUILD STORED AS RCFILE;

CREATE INDEX ind_dept3 ON TABLE EMP_INFO(dept)
AS 'COMPACT' WITH DEFERRED REBUILD
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n'
STORED AS TEXTFILE;

Showing an indexSearch on YouTubeBookmarkSaved

SHOW FORMATTED INDEX ON EMP_INFO;

The output lists one row per index on the table: the index name, the base table, the indexed columns, the index table, and the type:

Four indexes sit on the same column here. The slides allow any number of indexes of any type on one table and one column.

Rebuilding an indexSearch on YouTubeBookmarkSaved

WITH DEFERRED REBUILD creates an empty index, even when the table holds data. The index table fills only after an explicit rebuild. A rebuild is needed again whenever rows are overwritten or appended, because the index table does not track the change on its own.

ALTER INDEX ind_dept ON EMP_INFO REBUILD;

If the base table has partitions, the REBUILD needs the partition details. Name the Hive partition in the ALTER INDEX statement.

The ALTER launches a MapReduce job, because the index is itself a table. The slide job reads the base table and writes the index table:

Table default.default__emp_info_ind_dept__ stats:
  [numFiles=1, numRows=2, totalSize=245, rawDataSize=243]
Time taken: 369.636 seconds

Bitmap indexing in detailSearch on YouTubeBookmarkSaved

A bitmap index suits read-only queries over large datasets. Each distinct value of the indexed column gets one bit vector. The vector has one bit per row of the table. Bit $i$ is 1 when row $i$ holds the value. A 0 means the row lacks it.

The slides demo on a 4-row employee table:

Vectors for New_EmpSearch on YouTubeBookmarkSaved

New_Emp has 2 distinct values, so it gets 2 vectors of 4 bits. Read rows 1 to 4 left to right.

Vectors for JobSearch on YouTubeBookmarkSaved

Job has 4 distinct values, so it gets 4 vectors. Each value occurs once, so each vector has a single 1.

AND query on two bitmapsSearch on YouTubeBookmarkSaved

Find employees who are not new and are salespeople:

SELECT *
FROM Employee
WHERE New_Emp = 'No' AND Job = 'Salesperson';

The engine fetches two vectors and ANDs them bit by bit. No table scan runs.

Features of bitmap indexingSearch on YouTubeBookmarkSaved

Compact versus bitmap storageSearch on YouTubeBookmarkSaved

Both index types answer the same queries. They differ only in how the index rows are stored, and sometimes in fetch time.

HDFS spreads data across nodes in blocks. The query must reach the right block without scanning. Compact indexing names the block. Bitmap indexing names the rows, and the engine maps rows to blocks.

Timing experiment: 276.815 s against 197.424 sSearch on YouTubeBookmarkSaved

The slides run the same query twice on EMP_INFO: once with three compact indexes on dept, once with only a bitmap index. The three compact indexes are dropped first so that only the bitmap index stays.

The bitmap run uses a fourth index on the same column. Create it first with deferred rebuild, then rebuild it:

CREATE INDEX ind_dept4 ON TABLE EMP_INFO(dept)
AS 'BITMAP' WITH DEFERRED REBUILD;

ALTER INDEX ind_dept4 ON EMP_INFO REBUILD;

SELECT MAX(emp_id) AS Latest_Employee
FROM EMP_INFO WHERE dept = 'AISQUAD';

Both runs return the same answer:

249986
Time taken: 276.815 seconds, Fetched: 1 row(s)

249986
Time taken: 197.424 seconds, Fetched: 1 row(s)

When to index and when to skipSearch on YouTubeBookmarkSaved

Use indexing when the dataset is large (gigabytes or more), when speed matters for query execution, and when SELECT statements use WHERE clauses often.

Skip indexing when the dataset is small. Building the index then costs more than it saves. Many indexes also degrade queries. Skip it too when rows are almost all unique, and when WHERE clauses are rare.

Dropping an indexSearch on YouTubeBookmarkSaved

DROP INDEX ind_dept ON EMP_INFO;

The command deletes the index and the table that stores it. Two automatic cases need no command. Dropping the base table deletes its indexes. Dropping a Hive partition deletes that Hive partition's index entries too.

Hive 3.0: indexes are goneSearch on YouTubeBookmarkSaved

Hive supported CREATE INDEX before version 3.0. Since 3.0 the feature is dropped. Two replacements give similar speedups for filtered queries:

• Materialized views with automatic rewriting. Pre-compute an expensive result once; the optimizer answers matching queries from it. (Views and materialized views covers the before-and-after REBUILD behaviour.)
• Columnar file formats (Parquet, ORC). They scan selected columns and skip whole files or blocks that cannot match.

Two design rules from the closing slides still apply. Choose bucket columns with high cardinality: many distinct values spread rows evenly. Choose Hive partition columns with low cardinality: few distinct values keep the directory count small.

Quick self-testSearch on YouTubeBookmarkSaved