Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 36 ยท Getting Started

Your First Hive Session

Walk through a complete first Hive session: create a database and table, load a small file, run SELECT queries, inspect the table with DESCRIBE, and clean up.

You have a Hive server and a client. Now let's do the full loop that every Hive user repeats: create a database, create a table, put data in it, query it and look at what Hive recorded. This lesson shows the real Hive statements first, then practises the query part with a runnable SQLite stand-in.

The Hive statements are read-only here because there is no Hive server in the page. Where a result is certain from the definition of the statement, we show it; for anything else we do not invent output.

Step 1: a database

A Hive database is a namespace for tables (it is the same thing as a schema). Hive always starts with one called default.

SQL
CREATE DATABASE IF NOT EXISTS shop
COMMENT 'Practice database'
WITH DBPROPERTIES ('owner' = 'training');

SHOW DATABASES;
USE shop;

IF NOT EXISTS makes the statement safe to re-run. Behind the scenes Hive creates a folder shop.db under the warehouse directory.

Step 2: a table

We want to store a small list of products as comma-separated text:

SQL
CREATE TABLE products (
  id    INT,
  name  STRING,
  price DECIMAL(8,2)
)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

This creates a managed table, with a folder /user/hive/warehouse/shop.db/products. The ROW FORMAT line says the fields are separated by commas. The table is empty; no file exists yet.

Step 3: put data in

Suppose products.csv on your machine contains:

TEXT
1,Notebook,3.50
2,Pen,1.20
3,Backpack,29.90

Load it from the client machine:

SQL
LOAD DATA LOCAL INPATH '/home/alice/products.csv'
INTO TABLE products;

LOCAL means the path is on the machine running the server. Without it, the path is in HDFS and the file is moved into the table's folder. Nothing is parsed during the load, so even a wrongly formatted file loads fine. The details are in Loading and Inserting Data.

You can also insert a few rows directly:

SQL
INSERT INTO products VALUES (4, 'Ruler', 0.99), (5, 'Stapler', 7.25);

Each INSERT creates a new small file in the table folder, so avoid inserting rows one at a time on real data.

Step 4: query

SQL
SELECT * FROM products;

SELECT name, price
FROM products
WHERE price > 2
ORDER BY price DESC;

A simple SELECT * with no filter often reads the files directly (a fetch task) without launching a cluster job. The second query uses filters and sorting, so Hive normally compiles it into a job; you would see progress lines in Beeline before the rows appear. That start-up delay is normal.

Here is the same flow with the bookstore database in SQLite, so you can practise the query syntax. It is a stand-in, since the table already exists and there is no LOAD DATA.

SQL Stand-in: filter, sort and limit (SQLite, not Hive) ยท database: bookstore

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

The Hive version would be identical. Try changing the filter to stock = 0 to find sold-out titles.

Step 5: inspect what Hive knows

Hive can show you the definition it stored:

SQL
DESCRIBE products;
DESCRIBE FORMATTED products;
SHOW CREATE TABLE products;
SHOW TABLES;

DESCRIBE products lists each column and its type:

TEXT
id      int
name    string
price   decimal(8,2)

DESCRIBE FORMATTED adds the location, table type (MANAGED_TABLE), owner, input and output format classes and storage details. Whenever something surprising happens, start there.

Step 6: look at the files

Because data lives in HDFS, you can see it with ordinary file commands:

Terminal
hdfs dfs -ls /user/hive/warehouse/shop.db/products
hdfs dfs -cat /user/hive/warehouse/shop.db/products/products.csv

This is a great way to understand Hive: a managed table is a folder, and each file in it is part of the table.

Step 7: clean up

SQL
DROP TABLE products;
DROP DATABASE shop;          -- fails if the database still has tables
-- DROP DATABASE shop CASCADE; -- drops the tables too

For a managed table, DROP TABLE deletes the folder and its files too; it cannot be undone (unless the Hadoop trash is on). For an external table, only the definition would be dropped. You will learn the difference in Managed and External Tables.

A mental model of the whole session

sequenceDiagram
    participant U as You (Beeline)
    participant H as HiveServer2
    participant M as Metastore
    participant S as HDFS
    U->>H: CREATE TABLE products
    H->>M: store definition
    H->>S: create table folder
    U->>H: LOAD DATA ...
    H->>S: move file into folder
    U->>H: SELECT ...
    H->>M: where are the files?
    H->>S: read files, run job
    H-->>U: rows

DDL changes the metastore (and folders); queries read the metastore, then the files.

Common beginner mistakes

  • Forgetting USE shop; and creating tables in default.
  • Choosing the wrong delimiter, then seeing every column except the first as NULL.
  • Loading a header row of a CSV as data. Fix it with TBLPROPERTIES ('skip.header.line.count'='1').
  • Expecting DROP TABLE to be reversible on a managed table.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a Hive database correspond to on disk (for managed tables)?

    Each database gets a .db folder, and each managed table is a subfolder.
  2. 2. What does LOAD DATA INPATH (without LOCAL) do to the source file?

    Loading from an HDFS path moves the file; nothing is parsed at load time.
  3. 3. Which statement shows the table type, location and storage formats?

    DESCRIBE FORMATTED prints the detailed table information from the metastore.
  4. 4. Why should you avoid inserting rows one at a time?

    Many tiny files slow down listing and scanning in HDFS.

Summary

  • A first session: CREATE DATABASE, USE, CREATE TABLE, LOAD DATA or INSERT, SELECT, DESCRIBE FORMATTED, DROP.
  • A managed table is a folder under the warehouse directory; files in it are the data.
  • Loading does not validate data; the schema is applied when you query.
  • Dropping a managed table removes its data; always check DESCRIBE FORMATTED first.

Next: Hive Architecture