Hive Views

When you start working with Apache Hive, you’ll often come across situations where you don’t want to create a new table but still want to save a query result for future use. This is where Hive Views come into play.

A view in Hive is like a virtual table. It doesn’t store any actual data but stores a query definition that you can reuse whenever you need it.

A View is:

• Basically a saved SELECT query that behaves like a table.
• It does not store any data itself—every time you query a view, Hive runs the underlying query in real-time.
• Views help you simplify complex queries, hide sensitive columns, or create custom reports without copying data.

CREATE VIEW -

CREATE VIEW lets you define a new view based on a SELECT query. The view does not store the data—it only stores the query structure.

Syntax -

CREATE VIEW [IF NOT EXISTS] view_name [(column_list)]
  AS select_statement

IF NOT EXISTS clause used to create the view if the specified view is not existed.

Example -

Scenario1: Create a Simple View: You have a table called orders:

CREATE TABLE orders (
  order_id INT,
  customer_name STRING,
  order_amount DOUBLE,
  order_date STRING
);

Now you want to create a view that shows only the high-value orders (order amount above 10,000):

CREATE VIEW high_value_orders AS
SELECT order_id, customer_name, order_amount
FROM orders
WHERE order_amount > 10000;

This view is now saved, and you can use it anytime by simply writing:

SELECT * FROM high_value_orders;

ALTER VIEW -

In Hive, the ALTER VIEW command allows you to replace an existing view with a new query definition. It’s used when the business rules change, or you need to modify the view logic without creating a new name.

Syntax -

ALTER VIEW view_name AS
SELECT new_query;

Example -

Scenario1: Alter a View to Change Business Logic:

Let’s say you previously created high_value_orders for orders above 10,000. Now you want to change it to orders above 5,000.

ALTER VIEW high_value_orders AS
SELECT order_id, customer_name, order_amount
FROM orders
WHERE order_amount > 5000;

Now the high_value_orders view will reflect the new rule without needing to drop and recreate it.

DROP VIEW -

The DROP VIEW command is used when you no longer need a view and want to remove it from Hive. This does not affect the original tables or data.

Syntax -

DROP VIEW [IF EXISTS] 
[database_name.]view_name

Example -

Scenario1: Drop an Unwanted View

If you no longer need the high_value_orders view:

DROP VIEW high_value_orders;

The view is deleted, but the original orders table remains untouched.

---