An Overview of DDL Commands in Apache Hive

Apache Hive DDL (Data Definition Language) consists of HiveQL statements that create, inspect, alter, and remove databases, tables, partitions, views, and other metadata objects. Hive DDL operates through the Hive Metastore, while the actual files remain in HDFS or another configured storage system. This guide uses Hive 3.x/4.x-compatible syntax where possible and marks features that require Hive 4.0.0 or another specific release. Syntax and data-lifecycle behavior can differ between Apache Hive and vendor distributions; verify destructive commands and version-specific features in your deployment.

Official reference: Apache Hive Language Manual: DDL.

#	Preview	Product	Price
1		Apache Hive Handbook: Query, Analyze, and Optimize Big Data	$39.99	Buy on Amazon
2		Waterproof Beekeeping Log Book, 3 Pack Beehive Inspection Logbook, A5	$17.99	Buy on Amazon
3		Apache Hive Cookbook	$50.99	Buy on Amazon
4		Apache Hive: Memo sur son utilisation (French Edition)	$47.00	Buy on Amazon
5		Apache Hive Essentials	$16.54	Buy on Amazon

What counts as Hive DDL?

DDL defines or changes the structure and metadata of Hive objects rather than processing individual rows.

SHOW, DESCRIBE, and USE are often taught with DDL because they manage or inspect metadata, although USE is primarily session management. HiveQL is SQL-like, not fully standard SQL: partitions, SerDes, storage clauses, and metastore behavior are Hive-specific. See the Hive DML manual for the contrasting data-manipulation statements.

#1 Best Overall

Apache Hive Handbook: Query, Analyze, and Optimize Big Data

$39.99

Buy on Amazon

Metastore, storage, and query engine

The Hive Metastore records databases, tables, columns, partition values, locations, file formats, SerDes, properties, and (where available) statistics and transactional flags. The underlying storage—usually HDFS, but potentially another filesystem or connector-backed source—contains the files. A query engine reads the metastore definition and interprets those files. Consequently, adding a directory directly to storage does not necessarily register a partition in Hive, and changing metadata does not automatically rewrite existing files.

Keep this distinction in mind: a metadata operation is not the same as a physical data conversion or move. Commands such as SET LOCATION, column changes, bucket declarations, and SerDe-property changes can be accepted without touching existing data.

Databases and schemas

Hive treats DATABASE and SCHEMA as interchangeable terms (some SCHEMA forms arrived later in Hive’s history).

Create and select a database

CREATE DATABASE IF NOT EXISTS analytics
COMMENT 'Analytics database'
LOCATION 'hdfs:///warehouse/analytics.db'
WITH DBPROPERTIES ('owner' = 'data-team');

USE analytics;
USE DEFAULT;

Current Hive documentation also supports MANAGEDLOCATION; it was added in Hive 4.0.0. Hive 4.0.0 additionally documents REMOTE databases for data connectors. Do not use either feature without checking the target release and distribution.

Inspect and alter a database

SHOW DATABASES;
DESCRIBE DATABASE analytics;
DESCRIBE DATABASE EXTENDED analytics;

ALTER DATABASE analytics
SET DBPROPERTIES ('department' = 'finance');

ALTER DATABASE analytics SET OWNER ROLE analytics_admin;
ALTER DATABASE analytics
SET LOCATION 'hdfs:///new/default/location';

Changing a database location changes the default location for newly created tables. It does not move existing table or partition data.

Drop a database safely

DROP DATABASE IF EXISTS analytics RESTRICT;
DROP DATABASE IF EXISTS analytics CASCADE;

RESTRICT is the default and fails when the database contains objects. CASCADE removes objects in the database and can remove their data, so treat it as an explicitly approved data-loss operation.

Rank #2

Waterproof Beekeeping Log Book, 3 Pack Beehive Inspection Logbook, A5

• 【5-Minute Rapid Logging! Checkbox-Style Hive Inspection Sheet Doubles Management Efficiency】- The beekeeping logbook features a checkbox + short fill-in design, allowing you to complete colony status records in just 5 minutes. The structured form accurately covers key inspection items, say goodbye to scattered notes and memory lapses for efficient multi-hive management!
• 【Stormproof Waterproof! All-Weather Hive Logbook, Fearless in Humid Conditions】- With dual protection from a PVC cover and waterproof inner pages, the entire book remains usable after immersion—just wipe it dry, with no smudging or blurred text. During rainy-season inspections or sudden downpours at the apiary, your records stay clear and intact, ensuring beekeeping data security.
• 【One-Handed Page Turning! Spiral-Bound Portable Design for Smooth Apiary Operations】- The A5 hive inspection notebook features durable spiral binding, lying flat at 180° for effortless writing and smooth one-handed page-turning! Compact size (5.8x8.3 inches) fits easily into protective suit pockets, enabling instant historical record lookup and clear colony trend comparisons—doubling inspection efficiency!
• 【Beginner Friendly! 6-Section Guidance Simplifies Beekeeping Inspections】- Designed for new beekeepers with a logical framework (queen & brood, hive condition, frames & comb, hive health, feeding, honey harvest), it avoids complex jargon and transforms observations into actionable checklists + fill-ins. Go from chaotic checks to systematic management—advance to pro beekeeping with ease!
• 【Beekeeper’s Annual Essential! 3-Pack Supports 300 inspection records, a Must for Scientific Beekeeping】- Each 100-page beekeeping log book meets a full year’s inspection needs (100 inspection records), while the 3-pack allows multi-hive numbering for long-term tracking of seasonal colony strength and honey yield fluctuations. Data analysis aids swarm planning—the perfect practical gift for beekeepers!

$17.99

Buy on Amazon

Creating Hive tables

Managed table

CREATE TABLE IF NOT EXISTS employees (
    employee_id BIGINT,
    name        STRING,
    department  STRING,
    salary      DECIMAL(12,2)
);

A managed table is generally owned by Hive for lifecycle and storage-location purposes. Exact deletion behavior depends on Hive version, table properties, storage handler, permissions, and filesystem configuration.

External table

CREATE EXTERNAL TABLE IF NOT EXISTS raw_events (
    event_id   STRING,
    event_time TIMESTAMP,
    payload    STRING
)
STORED AS TEXTFILE
LOCATION 'hdfs:///data/raw/events';

An external table points metastore metadata at data whose lifecycle is normally managed outside Hive, such as a shared landing area. Do not assume that dropping an external table always preserves files or always deletes them; confirm the deployed semantics, properties, handler, and permissions. Treat the location as production data.

Comments and properties

CREATE TABLE sales (
    order_id BIGINT,
    amount   DECIMAL(12,2)
)
COMMENT 'Order-level sales'
TBLPROPERTIES (
    'source' = 'erp',
    'quality' = 'validated'
);

Partitioned tables

CREATE TABLE page_views (
    user_id   BIGINT,
    page_url  STRING,
    view_time TIMESTAMP
)
PARTITIONED BY (
    event_date DATE,
    country    STRING
)
STORED AS ORC;

Partition columns are metadata columns commonly represented by directories such as event_date=2026-08-18/country=US/. Partitioning is a layout and metastore mechanism—not an index. Queries that filter on partition columns can benefit from partition pruning.

Create-table-as-select (CTAS)

CREATE TABLE daily_sales
STORED AS ORC
AS
SELECT order_date, SUM(amount) AS total_amount
FROM sales
GROUP BY order_date;

The standard documented CTAS form creates a managed table; it is not the standard syntax for creating an external table.

Copy a definition with LIKE

CREATE TABLE sales_copy LIKE sales;

LIKE copies a table definition without copying rows. CTAS instead derives a schema from query output and may transform or aggregate data.

Temporary tables

CREATE TEMPORARY TABLE session_events (
    event_id STRING,
    event_time TIMESTAMP
);

A temporary table is visible only in the current session, uses the user’s scratch area, and is deleted when that session ends. Hive documents limitations including no partition columns and no index support.

Rank #3

Apache Hive Cookbook

$50.99

Buy on Amazon

Types and table-definition clauses

Common primitive types include:

• TINYINT, SMALLINT, INT, BIGINT
• FLOAT, DOUBLE, DECIMAL
• BOOLEAN
• STRING, VARCHAR, CHAR, BINARY
• DATE, TIMESTAMP

Complex types use Hive’s angle-bracket syntax:

ARRAY<STRING>
MAP<STRING, INT>
STRUCT<street:STRING, city:STRING>
UNIONTYPE<INT, STRING>

The Hive 4.0.0 documentation also lists JSONFILE as a file format; support must be checked in your distribution.

• ROW FORMAT DELIMITED describes how fields and rows are serialized.
• STORED AS ORC, PARQUET, or TEXTFILE selects a file format.
• LOCATION associates the object with a storage path.
• TBLPROPERTIES stores table-level metadata and configuration.
• SERDE and SERDEPROPERTIES control serialization and deserialization.

Changing these clauses changes how Hive interprets files; it does not by itself convert existing files to a new format.

Inspecting metadata

SHOW commands

SHOW DATABASES;
SHOW TABLES;
SHOW TABLES IN analytics;
SHOW TABLES LIKE 'sales_*';
SHOW VIEWS;
SHOW PARTITIONS page_views;
SHOW COLUMNS IN employees;
SHOW CREATE TABLE employees;
SHOW TBLPROPERTIES employees;
SHOW FUNCTIONS;
SHOW FUNCTIONS LIKE 'date*';
SHOW LOCKS employees;

The official manual documents additional SHOW variants for connectors, materialized views, roles, privileges, configuration, transactions, compactions, and more. SHOW CREATE TABLE is useful for executable DDL; DESCRIBE FORMATTED exposes a broader metadata record.

DESCRIBE commands

DESCRIBE employees;
DESCRIBE FORMATTED employees;
DESCRIBE EXTENDED employees;
DESCRIBE DATABASE analytics;
DESCRIBE DATABASE EXTENDED analytics;
DESCRIBE FORMATTED page_views
PARTITION (event_date='2026-08-18', country='US');

Check column names and types, partition columns, table type, location, input and output formats, SerDe, properties, statistics, transactional flags, and storage-descriptor details.

Altering tables

Rename, add, change, or replace columns

ALTER TABLE old_name RENAME TO new_name;

ALTER TABLE employees ADD COLUMNS (
    hire_date DATE,
    manager_id BIGINT
);

ALTER TABLE employees
CHANGE COLUMN name full_name STRING COMMENT 'Employee full name';

ALTER TABLE employees REPLACE COLUMNS (
    employee_id BIGINT,
    full_name STRING,
    department STRING
);

CHANGE COLUMN and especially REPLACE COLUMNS are metadata-level schema changes, not guaranteed file rewrites. Compatibility depends on file format, SerDe, column order, and the specific change. Verify the resulting schema and test reads before production use. REPLACE COLUMNS can discard columns from the table definition.

Properties, location, and SerDe

ALTER TABLE sales SET TBLPROPERTIES (
    'comment' = 'Validated sales data'
);

ALTER TABLE sales UNSET TBLPROPERTIES ('temporary_flag');

ALTER TABLE sales SET LOCATION 'hdfs:///warehouse/sales';

ALTER TABLE raw_events SET SERDEPROPERTIES (
    'field.delim' = ','
);

The location command updates metadata; it does not move files. SerDe property values must be quoted and are passed to the SerDe when Hive initializes it. A mismatch between new metadata and old files can produce missing or misparsed data.

Rank #4

Apache Hive: Memo sur son utilisation (French Edition)

$47.00

Buy on Amazon

Bucketing and skew metadata

ALTER TABLE sales
CLUSTERED BY (customer_id) INTO 32 BUCKETS;

Bucket and skew declarations modify metadata. Hive does not reorganize existing files to satisfy the declaration; data layout must be produced separately and conform to it.

Partition DDL

Add, rename, move, and drop partitions

ALTER TABLE page_views
ADD PARTITION (
    event_date = '2026-08-18', country = 'US'
)
LOCATION 'hdfs:///data/page_views/event_date=2026-08-18/country=US';

ALTER TABLE page_views
ADD
  PARTITION (event_date='2026-08-18', country='US')
  PARTITION (event_date='2026-08-18', country='CA');

ALTER TABLE page_views
PARTITION (event_date='2026-08-18', country='US')
RENAME TO PARTITION (event_date='2026-08-18', country='USA');

ALTER TABLE page_views
PARTITION (event_date='2026-08-18', country='US')
SET LOCATION 'hdfs:///new/page_views/us';

ALTER TABLE page_views
DROP IF EXISTS PARTITION (
    event_date='2026-08-18', country='US'
);

Dropping a partition removes its metastore entry and may remove its data. Where supported, PURGE bypasses the filesystem trash mechanism and should be considered irreversible:

ALTER TABLE page_views
DROP PARTITION (event_date='2026-08-18', country='US') PURGE;

Use explicit partition DDL when a pipeline knows exactly what it produced. It is more controlled than scanning an entire tree.

Repairing partition metadata with MSCK

MSCK REPAIR TABLE page_views;
MSCK REPAIR TABLE page_views ADD PARTITIONS;
MSCK REPAIR TABLE page_views DROP PARTITIONS;
MSCK REPAIR TABLE page_views SYNC PARTITIONS;

Hive can discover partition directories in storage only when their names and hierarchy match the table’s partition convention. MSCK REPAIR TABLE reconciles recognizable storage paths with metastore metadata; it does not transform badly structured files, fix an incorrect root location, or repair inaccessible storage. Scanning a table with many partitions can be expensive. Use DROP or SYNC modes only after confirming that storage and metastore are intended to match.

Dropping and truncating objects

DROP TABLE IF EXISTS staging_events;
DROP TABLE IF EXISTS staging_events PURGE;
TRUNCATE TABLE staging_events;

TRUNCATE TABLE page_views
PARTITION (event_date='2026-08-18', country='US');

Without PURGE, a configured trash path may provide recovery through .Trash/Current. PURGE (documented from Hive 0.14.0) removes that recovery path. Availability and behavior of TRUNCATE vary with table type, transactional settings, authorization, and filesystem. Confirm them before running production cleanup.

Views and materialized views

CREATE VIEW us_sales AS
SELECT * FROM sales WHERE country = 'US';

ALTER VIEW us_sales AS
SELECT * FROM sales WHERE country = 'USA';

DROP VIEW IF EXISTS us_sales;

A view stores a query definition rather than a second copy of its source data. Dropping or changing a referenced view can leave dependent views invalid; Hive does not automatically repair those dependencies.

Best Value

Sale

Apache Hive Essentials

$16.54

Buy on Amazon

CREATE MATERIALIZED VIEW sales_summary AS
SELECT order_date, SUM(amount)
FROM sales
GROUP BY order_date;

Materialized-view syntax, refresh behavior, and query-rewrite support are version- and configuration-dependent; verify them against the target Hive release.

Functions, macros, connectors, and indexes

Functions and macros

CREATE TEMPORARY FUNCTION normalize_email
AS 'com.example.hive.NormalizeEmail';
DROP TEMPORARY FUNCTION IF EXISTS normalize_email;

CREATE FUNCTION analytics.normalize_email
AS 'com.example.hive.NormalizeEmail'
USING JAR 'hdfs:///jars/normalize-email.jar';

CREATE TEMPORARY MACRO add_tax(price DOUBLE, rate DOUBLE)
price * (1 + rate);
DROP TEMPORARY MACRO IF EXISTS add_tax;

Permanent functions can be registered in the metastore from Hive 0.13 onward. Loading a class still requires compatible code, a reachable JAR, and appropriate privileges.

Indexes and Hive 4 connectors

Older manuals document index DDL, but indexes were removed in Hive 3.0.0; do not use CREATE INDEX as current general-purpose optimization guidance. Hive 4.0.0 adds connector-related statements such as CREATE CONNECTOR, DROP CONNECTOR, and ALTER CONNECTOR, plus remote-database support. These are advanced, release-specific features.

Permissions and authorization failures

Correct syntax is not sufficient. SQL-standard-based authorization distinguishes privileges for operations such as creating, altering, truncating, and dropping tables and partitions. A command can also fail because the user lacks database or table ownership, URI privileges, filesystem permissions, partition privileges, or administrative rights. Inspect the exact HiveServer2 error and check both Hive authorization and permissions on the underlying location. See the Hive SQL-standard authorization documentation.

SHOW CURRENT ROLES;
SHOW GRANT USER some_user ON TABLE employees;

Common failures and recovery checks

“The table exists but queries return no rows”

• Run DESCRIBE FORMATTED table_name and verify the location.
• Run SHOW PARTITIONS table_name for partitioned tables.
• Check that files exist under the recorded path and that the service account can read them.
• If directories were added outside Hive, use explicit ADD PARTITION or a carefully scoped MSCK REPAIR TABLE.

“A new partition is missing”

• Confirm the directory uses key=value names in the declared partition order.
• Confirm the table root and permissions.
• Prefer explicit partition registration in controlled pipelines.
• Use repair only when scanning the storage tree is acceptable.

“DROP DATABASE fails”

The database is probably nonempty and RESTRICT is in effect. List and remove objects individually, or obtain explicit approval for CASCADE.

“DDL succeeded but data looks corrupted”

Review recent schema, location, SerDe, file-format, bucket, or column-order changes. Metadata can describe files incorrectly without rewriting them. Restore the prior metadata or perform a separately planned data conversion after validating backups and reads.

“SET LOCATION did not move files”

That statement changes the metastore pointer only. Move or copy data with an appropriate storage workflow, then set and verify the location.

Reserved-word and version errors

Reserved keywords change by Hive release. For example, REGEXP and RLIKE changed status beginning with Hive 2.0.0. Prefer names such as orders instead of order. If quoting is necessary, follow the deployment’s hive.support.quoted.identifiers setting and test the statement on that release.

DDL quick reference

For syntax details and release notes, consult the official DDL manual, the Hive commands manual, and the Hive documentation index.

Quick Recap

Bestseller No. 1

Apache Hive Handbook: Query, Analyze, and Optimize Big Data

$39.99

Bestseller No. 2

Waterproof Beekeeping Log Book, 3 Pack Beehive Inspection Logbook, A5

$17.99

Bestseller No. 3

Apache Hive Cookbook

$50.99

Bestseller No. 4

Apache Hive: Memo sur son utilisation (French Edition)

$47.00

SaleBestseller No. 5

Apache Hive Essentials

$16.54

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.