You can start the Hive shell, which uses Beeline in the background, to enter statements 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 SQL statements. 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
.
-
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 configure
the database user name and password. Property names in hive-site.xml for these settings
are javax.jdo.option.ConnectionPassword
and
javax.jdo.option.ConnectionUserName
.
-
Enter a password at the prompt.
-
Enter a query.
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 |
+---------------------+
-
Create a table in the default database.
CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3,2));
-
Insert data into the table.
INSERT INTO TABLE students VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32);
-
Exit the Beeline and Hive shells.