Starting Apache Hive
Also available as:
PDF

Start a Hive shell as the hive user

You can start the Hive shell, which uses Beeline in the background, to enter HiveQL commands on the command line of a node in a cluster. After starting the Hive shell as the hive user, you can submit queries to Hive.

The architectural changes in Hive 3 support only Beeline for querying Hive from the command line. On an HDP cluster, you start a Hive shell. From the Hive shell, you can enter HiveQL commands. After connecting to Hive, you see a prompt that resembles the following example:
0: jdbc:hive2://myhiveserver.com>
The prompt consists of the following components:
  • jdbc: The Java Database Connectivity protocol designator
  • hive2: The HiveServer protocol designator in HDP 3 for using Hive 3
  • myhiveserver.com: The fully-qualified domain name (FQDN) of the node that hosts HiveServer
  • You added the Hive service on the cluster, using Ambari for example, and the following components are running:
    • HiveServer
    • Hive Metastore
    • A database for the metastore, such as the default MySQL Server
    • Hive clients
  • In Ambari, Hive > Settings > Run as end user instead of Hive user is set to False.
  1. Start Hive using the FQDN of the HiveServer in your cluster to replace myhiveserver.com and the Database Username and Database Password password for the default hive user.
    beeline -u jdbc:hive2://myhiveserver.com:10000 -n hive -p
    In Ambari > Services > Hive > Configs > Database, you can find the user name and reset the password. Property names in hive-site.xml for these settings are javax.jdo.option.ConnectionPassword and javax.jdo.option.ConnectionUserName.
  2. Enter a password at the prompt.
  3. Enter a HiveQL query.
    SHOW DATABASES;
    Hive creates two databases when you add the Hive service to a cluster: information_schema and sys. All Metastore tables are mapped into your tablespace and are available in sys. The information_schema data reveals the state of the system, similar to sys database data, but in a user-friendly way. You can query information_schema using SQL standard queries, which are portable from one DBMS to another.
    Output is:
    +---------------------+
    |    database_name    |
    +---------------------+
    | default             |
    | information_schema  |
    | sys                 |
    +---------------------+
  4. Create a table in the default database.
    CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3,2));
  5. Insert data into the table.
    INSERT INTO TABLE students VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
  6. Exit the Beeline and Hive shells.
    !quit