Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 36 ยท HiveQL Basics

Databases and DDL in Hive

Learn Hive data definition language: create, alter, describe and drop databases and tables, set locations and properties, copy structures with LIKE and CTAS, and add columns safely.

DDL (data definition language) is the part of HiveQL that creates and changes the structure of your warehouse: databases, tables, views and their properties. In Hive, DDL has a special meaning: most of it changes only the metastore and folder layout, not the data inside the files. Understanding that makes many surprises disappear.

Hive DDL is shown read-only because no Hive server runs in the page. The runnable editors use SQLite stand-ins for the portable parts (CREATE TABLE, ALTER TABLE ... ADD COLUMN, DROP TABLE, CTAS); where SQLite differs, we say so.

Databases

SQL
CREATE DATABASE IF NOT EXISTS retail
COMMENT 'Retail analytics'
LOCATION '/data/retail.db'
WITH DBPROPERTIES ('owner' = 'analytics', 'created' = '2025-01-15');

SHOW DATABASES;
SHOW DATABASES LIKE 'ret*';
DESCRIBE DATABASE EXTENDED retail;
USE retail;

ALTER DATABASE retail SET DBPROPERTIES ('team' = 'bi');
DROP DATABASE IF EXISTS retail CASCADE;
  • SCHEMA is a synonym for DATABASE.
  • LOCATION overrides the folder; without it Hive uses <warehouse>/retail.db.
  • DROP DATABASE refuses to run if tables exist; CASCADE drops them first (and the data of managed tables!). RESTRICT is the default.
  • Refer to a table in another database as retail.orders without a USE.

CREATE TABLE

A full table definition shows the Hive-specific clauses:

SQL
CREATE TABLE IF NOT EXISTS retail.orders (
  order_id    BIGINT        COMMENT 'Primary identifier',
  customer_id BIGINT,
  status      STRING,
  total       DECIMAL(10,2),
  created_at  TIMESTAMP
)
COMMENT 'One row per order'
PARTITIONED BY (order_date DATE)
STORED AS ORC
TBLPROPERTIES ('transactional' = 'false', 'owner' = 'bi');

The clauses must appear in this order: columns, COMMENT, PARTITIONED BY, CLUSTERED BY, ROW FORMAT, STORED AS, LOCATION, TBLPROPERTIES. You will study partitions and buckets in Chapter 4; STORED AS in the file formats chapter.

The portable part is the column list. Run a SQLite stand-in that creates and fills a small table:

SQL Stand-in: create and fill a table (SQLite, not Hive)

Press Run (or Ctrl + Enter) to see the result here.

Note that SQLite has fewer, looser types. Hive has strict ones (BIGINT, DECIMAL(10,2), STRING); see Data Types.

Copying a table: LIKE and CTAS

Two shortcuts create a table from another:

SQL
-- structure only, no data (and the same format and properties)
CREATE TABLE orders_backup LIKE orders;

-- structure AND data from a query ("CTAS")
CREATE TABLE big_orders
STORED AS ORC
AS
SELECT * FROM orders WHERE total > 1000;

CTAS is the everyday ETL tool: transform and store in one statement, and choose a new format on the way. CTAS in Hive cannot create an external or partitioned table directly (partitioned CTAS is not allowed); create the table first, then use INSERT ... SELECT.

SQLite supports CTAS too, so you can practise it:

SQL Stand-in: CREATE TABLE AS SELECT (SQLite, not Hive) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Inspecting tables

SQL
SHOW TABLES;
SHOW TABLES IN retail LIKE 'ord*';
DESCRIBE orders;                 -- columns
DESCRIBE FORMATTED orders;       -- details: location, format, owner, stats
SHOW CREATE TABLE orders;        -- the DDL, ready to copy
SHOW TBLPROPERTIES orders;

SHOW CREATE TABLE is the quickest way to learn what exact format and properties an existing table has.

ALTER TABLE

SQL
ALTER TABLE orders RENAME TO sales_orders;

ALTER TABLE orders ADD COLUMNS (channel STRING COMMENT 'web or store');
ALTER TABLE orders REPLACE COLUMNS (order_id BIGINT, total DECIMAL(10,2));
ALTER TABLE orders CHANGE COLUMN status order_status STRING;

ALTER TABLE orders SET TBLPROPERTIES ('comment' = 'Cleaned orders');
ALTER TABLE orders SET LOCATION '/data/new/orders';

Remember: these change metadata only. ADD COLUMNS does not rewrite files; old files simply have no value for the new column, so it reads as NULL. REPLACE COLUMNS redefines the whole column list, which can silently hide data if you make a mistake; CHANGE COLUMN renames or retypes a column, and with text files retyping only changes how bytes are read. For columnar formats such as Parquet, Hive matches columns by name or position depending on configuration, so test after any schema change.

SQLite supports adding a column in a similar way:

SQL Stand-in: adding a column leaves old rows NULL (SQLite, not Hive)

Press Run (or Ctrl + Enter) to see the result here.

Dropping and truncating

SQL
DROP TABLE IF EXISTS orders;
TRUNCATE TABLE orders;           -- removes all data, keeps the definition (managed tables only)

DROP TABLE on a managed table deletes its data folder. On an external table it deletes only the definition; see Managed and External Tables. TRUNCATE is refused for external tables.

Good habits

  • Always write IF NOT EXISTS and IF EXISTS in scripts so they can be re-run.
  • Add COMMENT to tables and columns; many catalog tools show them.
  • Put a database per subject area or pipeline layer (raw, clean, marts).
  • Use lowercase names; Hive stores identifiers in lowercase anyway.
  • Keep DDL in version-controlled script files, not only in someone's terminal history.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does ALTER TABLE ... ADD COLUMNS do to existing files?

    Hive DDL normally changes only the metastore, not the data files.
  2. 2. What does DROP DATABASE retail CASCADE do?

    CASCADE removes contained tables (and data of managed ones) before dropping the database.
  3. 3. Which statement creates an empty table with the same structure as another?

    LIKE copies the definition only; CTAS copies data too.
  4. 4. Which command shows the exact DDL of an existing table?

    SHOW CREATE TABLE prints the CREATE statement including format and properties.

Summary

  • DDL creates, alters and drops databases and tables; most changes touch only the metastore.
  • Clause order in CREATE TABLE: columns, comment, partitioned by, clustered by, row format, stored as, location, properties.
  • LIKE copies a structure; CTAS copies structure and data and can change the format.
  • DESCRIBE FORMATTED and SHOW CREATE TABLE reveal a table's real definition.

Next: Hive Data Types