Hive Interface Introduction (Web UI/JDBC)

Hive Interface Introduction (Web UI/JDBC)

Experiment Introduction

This experiment learns the two interfaces of Hive: Web UI and JDBC.

First, the experimental environment explained

1. Environment Login

No password automatic login, system user name Shiyanlou, password Shiyanlou

2. Introduction to the Environment

This experiment environment uses the Ubuntu Linux environment with the desktop, the experiment will use the program on the desktop:

• Xfceterminal:linux command line terminal, Open will enter the bash environment, you can use the Linux command;
• Firefox: Browser, can be used in the need for the front-end interface of the course, only need to open the environment to write the HTML/JS page;
• GVim: Very useful editor, the simplest usage can refer to the course Vim editor.
• Eclipse:eclipse is a well-known cross-platform, free integrated development environment (IDE). It is used primarily for Java language development, but it is also being developed as a development tool for languages such as C + + and Python through plugins.

3. Use of the environment

Use the Gvim editor to enter the code required for the experiment, and then use the xfceterminal command-line environment to compile and run, view the running results, run and share your experiment results, the lab building provides the backstage, cannot cheat, can prove that you have completed the experiment effectively.

The Experiment records page can be viewed in the "My Course", which contains each experiment and notes, as well as the effective learning time of each experiment (refers to the time of the experiment desktop operation, if there is no action, the system will be recorded as Daze time). These are the proof of authenticity of your studies.

4. Other

In the previous experiment, we were using the Hadoop V2.4.1 version. When we were testing, we found that Hive V1.1.0 needed Hadoop V2.6 above for the Session query, so the Hadoop version of this experiment was based on the V2.6.0 version. Since the Hadoop directory has been modified, the hive directory is normal, and if Hadoop or hive is prompted as an invalid command, make thesource /etc/profileconfiguration file effective.

The Hadoop directory is: The/usr/local/hadoop-2.6.0Hive directory is:/usr/local/hive-1.1.0

Second, Hive network interface (Web UI)

(1) Introduction to Web UI

The Hive Web UI provides an image-based interface for easier and more intuitive operation through the Hive Web UI interface, especially for users who have just touched Hive. The Hive Web UI has the following features:

Execution of detached queries

Under the command line (CLI), multiple queries have to be opened to execute more than one, and through the Web UI, multiple queries can be executed simultaneously, and session sessions can be managed on a network server.

Do not rely on local Hive

Users need to install local hive to access hive through a Web browser and to perform related operations. If you want to interact with Hadoop and Hive through the WEB, you need to access multiple ports.

(2) Configuration Hive-site.xml

Modify$HIVE_HOME/confThe Hive-site.xml file in the directory.

We can look at what the default configuration is:

We only need to modifyhive.hwi.war.file, you should first$HIVE_HOME/libcheck the HWI version in the directory:

The result is surprisingly, there is no suchwardocument! There is only one jar package with the same name and no war file. The solution is to download the corresponding Hive source package and then package it into a war file.

Download Hive source code (note here is the SRC package, not the bin package.) Last time we used the 1.1.0 version)

$ wget http://labfile.oss.aliyuncs.com/apache-hive-1.1.0-src.tar.gz

Extract:

$ tar zxvf apache-hive-1.1.0.src.tar.gz

Then go to the HWI directory and package the war file (note that there is a point at the end of the command.):

$ cd apache-hive-1.1.0-src/hwi
$ jar cvfM0 hive-hwi-1.1.0.war -C web/ .

After the package is complete, we have the war file we need and then copy it to the $HIVE _home/lib directory:

$ cp hive-hwi-1.1.0.war /usr/local/hive-1.1.0/lib

In addition we need to copy a Java Tools.jar to the $HIVE _home/lib directory:

$ cp /usr/local/jdk1.7.0_67/lib/tools.jar /usr/local/hive-1.1.0/lib

Otherwise, an error  will appear (because Java_home refers to the$JAVA_HOME/jrenext, and the Tools.jar under the Lib is$JAVA_HOME/lib/tools.jarnot the same, it needs to be compiled with the latter):

Finally, we change the Hive-site.xml file to:

 
 
<property>
    <name>hive.hwi.war.file</name>
    <value>/lib/hive-hwi-1.1.0.war</value>
    <description>This sets the path to the HWI war file, relative to ${HIVE_HOME}. </description>
</property>

(3) Start HWI

Under $HIVE _home/bin directory, start Hwi (since we have previously modified Derby to MySQL database, make sure that MySQL and Hadoop have started successfully before starting HWI):

$ hive --service hwi

Now, we can open the address of the network interface in the browser:localhost:9999/hwistart successfully:

(4) Examples of WEB UI operations

Database and table information query

Before querying, we'll create a new person table to facilitate subsequent query operations. After you start Hive, enter the command (this table is just an example, you can create it yourself if you need to):

Create a new TXT file (path customization, such as I am/home/hadoop/hive/person.txt), write some sample data, the data is divided between, the\texperiment has been written, direct import can.

Then import the data into the person table.

ClickBrowse Schemato view the database in the current Hive, showing the current database information that can be used, including only one default database:

Then click Default to see information about all the tables contained in the default database (here are the Table:person tables we just created):

Click the Person table to see specific information.

Hive Session Query

We need to create a new session before making a query (note that the previous session will be invalidated after each restart of hwi). Click Create Session to create a new:

Fill in the  information.

Where: result file is the last query result document;

query for your inquiry statement;

start Query is set to YES to begin querying;

After clicking Submit, you will see the view File;

Click View File to see the results of the final query;

Through the above study, we can understand that although the Web UI provides a visual query operation, but the biggest disadvantage is that users can not understand the status of the query in real-time, the ability to interact poorly.

Third. JDBC

Create a new Java project in Eclipse, such as HIVEJDBC. Then add the required jar package, right-click on the project, select Properties, Java Build Path, Libraries, add External Jars. The required jar packages are as follows (if you are not sure you can add all the jar packages directly, and if you do not have permission to add them, copy the jar to the other privileged directories and add them to the experiment/home/hadoop/hive):

To create a new package and class, add the following code:

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

public class HiveJdbc {

    private static String driverName = 
                   "org.apache.hive.jdbc.HiveDriver";

    public static void main(String[] args) 
                            throws SQLException {
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }

        Connection con = DriverManager.getConnection(
"jdbc:hive2://localhost:10000/default", "", "");
        Statement stmt = con.createStatement();
        String tableName = "hive_jdbc";
        stmt.execute("drop table if exists " + tableName);
        stmt.execute("create table " + tableName + 
                                     " (key int, value string)");
        System.out.println("Create table success!");
        // show tables
        String sql = "show tables ‘" + tableName + "‘";
        System.out.println("Running: " + sql);
        ResultSet res = stmt.executeQuery(sql);
        if (res.next()) {
            System.out.println(res.getString(1));
        }

        // describe table
        sql = "describe " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1) + "\t" + res.getString(2));
        }


        sql = "select * from " + tableName;
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(String.valueOf(res.getInt(1)) + "\t"
                                               + res.getString(2));
        }

        sql = "select count(1) from " + tableName;
        System.out.println("Running: " + sql);
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1));
        }
    }
}

As you can see from the code above, the work that needs to be done before querying is:

• Through Class.forName ("Org.apache.hive.jdbc.HiveDriver"); To register the Hive driver;

• by Connection con = drivermanager.getconnection ("Jdbc:hive2://localhost:10000/default", "", "" "); To establish a connection to the Hive database;

You need to start hiveserver before running, but now Hive has abandoned hiveserver instead of hiveserver2, which is reflected in two different points in the code. First, the drivername changed from one toorg.apache.hadoop.hive.jdbc.HiveDriverthe otherorg.apache.hive.jdbc.HiveDriver, and the other was changed from Connectionjdbc:hive://localhost:10000/defaultjdbc:hive2://localhost:10000/default.

Therefore, starting Hiveserver before running the program is also changed to start Hiveserver2, which is started in the $HIVE _home/bin directory.

Homework

What are the similarities and differences between Web UI and JDBC two interfaces?

Reference documents

• "Hadoop Combat 2nd Edition" Lu Jiaheng, mechanical industry press;
• The operation and use of Hive Web interface hwi;
• Hive JDBC Call;

Hive Interface Introduction (Web UI/JDBC)