Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 36 ยท Getting Started

Hive CLI and Beeline

Learn how to connect to Hive with Beeline and HiveServer2, run queries and script files, set session variables, and why the old Hive CLI was replaced.

You talk to Hive through a client. For many years that was the Hive CLI, a command-line shell that ran queries directly. Modern installations use Beeline, a JDBC client that connects to HiveServer2. This lesson shows how to connect, run statements and script files, and set options for a session.

Beeline and HiveServer2 are not available in your browser, so the sessions below are read-only. The runnable editor is a stand-in that practises the SQL part with SQLite.

Why Beeline replaced the CLI

The old CLI loaded the Hive libraries and talked to the metastore and HDFS directly. That meant every user needed direct access to the storage and the metastore, and security could not be enforced centrally. HiveServer2 puts a single service in the middle: clients authenticate to it, and it applies authorization (for example with Ranger or SQL standard grants). Beeline is simply a client for that service. In recent releases the legacy hive command is only a thin wrapper that starts Beeline.

flowchart LR
    B["Beeline"] -->|JDBC| H["HiveServer2<br/>port 10000"]
    J["JDBC / ODBC app"] -->|JDBC| H
    P["Python client"] -->|Thrift| H
    H --> M["Metastore"]
    H --> Y["Execution engine<br/>on the cluster"]

Every client goes through HiveServer2, where authentication and authorization happen.

Connecting

Start Beeline and connect with a JDBC URL:

Terminal
beeline -u jdbc:hive2://localhost:10000 -n alice

The -u flag is the URL, -n the user name and -p an optional password. You can also start Beeline first and connect from its prompt:

TEXT
beeline> !connect jdbc:hive2://localhost:10000
Enter username for jdbc:hive2://localhost:10000: alice
Enter password for jdbc:hive2://localhost:10000:
0: jdbc:hive2://localhost:10000>

A URL can include a default database and settings: jdbc:hive2://host:10000/sales;ssl=true. With ZooKeeper-based service discovery the URL names the ZooKeeper ensemble instead of a single host.

Running statements

At the prompt you type HiveQL ending with a semicolon. Lines starting with ! are Beeline's own commands, not HiveQL:

TEXT
0: jdbc:hive2://localhost:10000> SHOW DATABASES;
0: jdbc:hive2://localhost:10000> USE sales;
0: jdbc:hive2://localhost:10000> SHOW TABLES;
0: jdbc:hive2://localhost:10000> !quit

Useful ! commands:

Command Purpose
!help List all Beeline commands
!tables List tables via JDBC metadata
!outputformat csv2 Change the result format (table, csv2, tsv2, json, vertical)
!run script.sql Run a script file
!history Show earlier commands
!quit Leave Beeline

Non-interactive use

For automation you rarely open a prompt. Two flags run work and exit:

Terminal
# one statement
beeline -u jdbc:hive2://localhost:10000 -e "SELECT COUNT(*) FROM sales.orders"

# a script file
beeline -u jdbc:hive2://localhost:10000 -f nightly_report.sql

# pass a variable into the script
beeline -u jdbc:hive2://localhost:10000 -f report.sql --hivevar run_date=2025-01-31

Inside report.sql the variable is used with ${hivevar:run_date}:

SQL
SELECT *
FROM sales.orders
WHERE order_date = '${hivevar:run_date}';

--silent=true hides progress messages, and --outputformat=csv2 makes the output easy to redirect into a file.

Session settings with SET

Hive's behaviour is controlled by properties that you can change per session with SET:

SQL
SET hive.execution.engine=tez;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.cli.print.header=true;
SET hive.execution.engine;        -- print the current value
SET;                              -- print everything

A property set this way lasts until you disconnect. To make it permanent for all users, put it in hive-site.xml. Later chapters, on partitions and tuning, rely heavily on SET.

A script is just SQL

A Hive script file is a list of statements separated by semicolons, with -- comments. You can practise the shape of one with the stand-in below: a header comment, a couple of statements, and a summary query.

SQL Stand-in: a small script of statements (SQLite, not Hive) ยท database: bookstore

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

Where SQLite stops and Hive begins: a real Hive script usually starts with SET lines and a USE database;, which SQLite does not have.

Other ways to connect

  • JDBC and ODBC drivers let tools such as Tableau, DBeaver and Superset run HiveQL.
  • Python: libraries such as PyHive or impyla connect to HiveServer2 over Thrift.
  • Web UI: HiveServer2 serves a status page, by default on port 10002, with running queries and configuration.
  • Hue and similar notebooks give a browser editor on top of HiveServer2.

Common problems

  • Connection refused: HiveServer2 is not running, or it is still starting (it can take a minute).
  • User not allowed to impersonate: the Hadoop proxy-user settings (hadoop.proxyuser.<user>.hosts) do not allow HiveServer2 to act for the user.
  • Statements ignored: you forgot the closing semicolon, so Beeline waits for more input.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why did Beeline replace the original Hive CLI as the standard client?

    HiveServer2 lets administrators control access in one place instead of giving every user direct metastore and storage access.
  2. 2. Which flag runs a script file and then exits?

    -f reads statements from a file; -e takes a single statement string.
  3. 3. How is a variable from --hivevar run_date=2025-01-31 referenced inside the script?

    Hive substitutes ${hivevar:name} before running the statement.
  4. 4. What is the effect of SET hive.execution.engine=tez; typed in a session?

    Session-level SET lasts until you disconnect; permanent changes go in hive-site.xml.

Summary

  • Beeline is a JDBC client for HiveServer2 (port 10000); the legacy CLI is deprecated.
  • Connect with beeline -u jdbc:hive2://host:10000 -n user; use -e for one statement and -f for a file.
  • ! commands belong to Beeline; everything else is HiveQL ending in a semicolon.
  • SET changes properties per session; pass script parameters with --hivevar.

Next: Your First Hive Session