Implementation:Heibaiying BigData Notes Hive View and Management Operations

Knowledge Sources	BigData-Notes Hive Language Manual
Domains	Data_Warehouse, Big_Data
Last Updated	2026-02-10 10:00 GMT

Overview

Concrete tool for creating views and performing table lifecycle management operations (ALTER, DROP, RENAME) provided by Apache Hive.

Description

This implementation covers two categories of Hive DDL operations:

View operations allow users to create, modify, and drop logical read-only objects defined by SELECT queries. Views store only the query definition in the metastore, not any data. When queried, the view definition is expanded inline and executed as part of the outer query.

Table management operations provide lifecycle control over existing tables, including renaming, adding or modifying columns (schema evolution), changing table properties, and dropping tables. These operations modify the metastore metadata and, in some cases (DROP on managed tables), the underlying HDFS data.

Together, these operations enable maintaining a well-organized, evolvable data warehouse where schemas can adapt to changing requirements and complex query logic can be encapsulated behind simple view interfaces.

Usage

Use view operations to simplify complex queries for end users, implement logical security layers, and create reusable query abstractions. Use table management operations for schema evolution (adding new columns), table lifecycle management (renaming, dropping), and metadata maintenance (updating table properties).

Code Reference

Source Location

• Repository: BigData-Notes
• File: notes/Hive视图和索引.md (Lines 1-239)

Signature

-- Create a view
CREATE VIEW [IF NOT EXISTS] view_name
  [(col_name [COMMENT 'col_comment'], ...)]
  [COMMENT 'view_comment']
  [TBLPROPERTIES (key='value', ...)]
AS SELECT ...;

-- Alter a view definition
ALTER VIEW view_name AS SELECT ...;

-- Drop a view
DROP VIEW [IF EXISTS] view_name;

-- Rename a table
ALTER TABLE table_name RENAME TO new_table_name;

-- Add columns to a table
ALTER TABLE table_name ADD COLUMNS
  (col_name data_type [COMMENT 'col_comment'], ...);

-- Change a column name, type, or position
ALTER TABLE table_name CHANGE [COLUMN]
  old_col_name new_col_name data_type
  [COMMENT 'col_comment']
  [FIRST | AFTER other_col];

-- Replace all columns
ALTER TABLE table_name REPLACE COLUMNS
  (col_name data_type [COMMENT 'col_comment'], ...);

-- Set table properties
ALTER TABLE table_name SET TBLPROPERTIES (key='value', ...);

-- Drop a table
DROP TABLE [IF EXISTS] table_name [PURGE];

Import

N/A (HiveQL statements executed via Hive CLI or Beeline)

I/O Contract

Inputs

Name	Type	Required	Description
view_name	STRING	Yes (for view ops)	Name of the view to create, alter, or drop
SELECT query	HiveQL	Yes (for CREATE/ALTER VIEW)	The query definition that constitutes the view logic
table_name	STRING	Yes (for table ops)	Name of the table to alter or drop
new_table_name	STRING	Yes (for RENAME)	New name for the table
Column definitions	Name + type pairs	Yes (for ADD/CHANGE/REPLACE COLUMNS)	Column specifications to add or modify
TBLPROPERTIES	MAP<STRING, STRING>	No	Key-value metadata properties to set on the table or view
PURGE	Keyword	No	When dropping a managed table, skip the trash and permanently delete data

Outputs

Name	Type	Description
View definition	Metastore entry	The view's query definition and metadata stored in the Hive metastore; no HDFS data is created
Updated schema	Metastore entry	Modified table schema reflecting added, changed, or replaced columns
Renamed table	Metastore entry	Updated table name in the metastore; HDFS directory may be renamed for managed tables
Dropped table/view	Void	Metastore entry removed; for managed tables, HDFS data is also deleted (moved to trash unless PURGE is specified)

Usage Examples

-- Create a view for monthly revenue reporting
CREATE VIEW IF NOT EXISTS monthly_revenue AS
SELECT
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    region,
    COUNT(DISTINCT order_id) AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date), region;

-- Query the view
SELECT region, total_revenue
FROM monthly_revenue
WHERE order_year = 2025 AND order_month = 1
ORDER BY total_revenue DESC;

-- Update the view to include a new metric
ALTER VIEW monthly_revenue AS
SELECT
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    region,
    COUNT(DISTINCT order_id) AS order_count,
    COUNT(DISTINCT customer_id) AS unique_customers,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY YEAR(order_date), MONTH(order_date), region;

-- Create a view with column aliases
CREATE VIEW customer_overview (cust_id, cust_name, total_orders, lifetime_value) AS
SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id),
    SUM(o.amount)
FROM customers c
LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- Drop a view
DROP VIEW IF EXISTS monthly_revenue;

-- Rename a table
ALTER TABLE staging_orders RENAME TO orders_archive;

-- Add new columns to an existing table
ALTER TABLE employees ADD COLUMNS (
    phone_number STRING COMMENT 'Employee phone number',
    emergency_contact STRING COMMENT 'Emergency contact name',
    hire_type STRING COMMENT 'Full-time, part-time, or contractor'
);

-- Change a column name and type
ALTER TABLE employees CHANGE COLUMN phone_number contact_phone VARCHAR(20)
  COMMENT 'Primary contact phone' AFTER name;

-- Replace all columns (destructive schema change)
ALTER TABLE temp_staging REPLACE COLUMNS (
    id BIGINT COMMENT 'Record identifier',
    payload STRING COMMENT 'JSON payload',
    received_at TIMESTAMP COMMENT 'Ingestion timestamp'
);

-- Update table properties
ALTER TABLE orders SET TBLPROPERTIES (
    'orc.compress'='ZLIB',
    'orc.stripe.size'='67108864'
);

-- Drop a managed table permanently (skip trash)
DROP TABLE IF EXISTS temporary_results PURGE;

-- Drop a table (moves data to trash for managed tables)
DROP TABLE IF EXISTS old_archive;

Related Pages

Implements Principle

• Principle:Heibaiying_BigData_Notes_Hive_View_and_Table_Management