Hive connection methods - E-MapReduce - Alibaba Cloud Documentation Center

Prerequisites

You have created a cluster with the Hive service enabled (for example, DataLake or Custom) and turned on the Assign Public Network IP switch for the master node to allow remote logon. For more information about creating a cluster, see Create a cluster.
- Common clusters: Clusters without Kerberos Authentication or High Service Availability enabled.
- High-security clusters: Clusters created with the Kerberos Authentication switch turned on.
- High availability clusters: Clusters created with the High Service Availability switch turned on. Note that when you create a high availability cluster, you must also select the ZooKeeper service.
You have logged on to the master node of the cluster. For more information, see Log on to a cluster.

Notes

master-1-1 and <Master node public IP address>: The hostname and public IP address of the master node. You can obtain this information on the Nodes page of the destination cluster in the EMR console. For more information, see Log on to a cluster.

Typically, the master node hostname is master-1-1. If your cluster is a Hadoop cluster, the hostname is emr-header-1.
HiveServer2 does not validate usernames and passwords by default. To enable username and password authentication, turn on LDAP authentication. For more information, see Use LDAP authentication.

Procedure

Common clusters

Connect using the Hive client

Run the following command to connect to Hive.
```
hive
```
Optional: Run quit; or exit; to exit Hive.

Connect using the Beeline client

Run the following command to connect to Hive.
```
beeline -u jdbc:hive2://master-1-1:10000
```
Optional: Run !quit or !exit to exit Beeline.

Connect using JDBC

Important

Before you begin, ensure that you have installed a Java environment and Java development tools, and configured the relevant environment variables.

Configure project dependencies (hadoop-common and hive-jdbc) in the pom.xml file. The following example shows the added dependencies.
```
<dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>3.1.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>3.2.1</version>
        </dependency>
</dependencies>
```
Ensure that the versions of hadoop-common and hive-jdbc match the versions of Hadoop-Common and Hive in your EMR cluster. You can view this software information in the Basic Information tab under the Software Information section of the destination cluster in the EMR console.

Write code to connect to HiveServer2 and operate Hive table data. The following example shows sample code.

import java.sql.*;
public class App
{
    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();
        }
        Connection con = DriverManager.getConnection(
            "jdbc:hive2://<Master node public IP address>:10000", "root", "");
        Statement stmt = con.createStatement();
        String sql = "select * from sample_tbl limit 10";
        ResultSet res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getString(1) + "\t" + res.getString(2));
        }
    }
}

Note

This method requires port 10000 to be open. For more information, see Manage security groups.

Build the project (to generate a JAR file) and upload the JAR file to the runtime environment.
Important
The JAR file depends on hadoop-common and hive-jdbc. If these dependency packages are not included in the environment variables of the runtime environment, download and configure them, or package them directly into the JAR file. If either dependency is missing when you run the JAR file, you will see one of the following errors:
- Missing hadoop-common: java.lang.NoClassDefFoundError: org/apache/hadoop/conf/Configuration
- Missing hive-jdbc: java.lang.ClassNotFoundException: org.apache.hive.jdbc.HiveDriver
In this example, the generated JAR file is named emr-hiveserver2-1.0.jar. Upload this JAR file to the master node of your E-MapReduce cluster.
Test whether the JAR file runs correctly.
Important
The server running the JAR file and the E-MapReduce cluster must be in the same VPC and security group, and network connectivity must exist between them. If they are in different VPCs or network environments, access the cluster using its public IP address, or use Alibaba Cloud networking products to establish connectivity before accessing it over the internal network. To test network connectivity:
- Public network: telnet public IP address of master-1-1 10000
- Private network: telnet private IP address of master-1-1 10000
```
java -jar emr-hiveserver2-1.0.jar
```

High-security clusters

Connect using the Hive client

Run the following command to start the Kerberos admin.local tool.
- If you log on to the master-1-1 node where the Kerberos Key Distribution Center (KDC) server runs as the root user, you can run the following command to directly access the admin tool.
```
kadmin.local
```
  The following output indicates that you have entered the kadmin.local command-line interface:
```
Authenticating as principal hadoop/admin@EMR.C-85D4B8D74296****.COM with password.
kadmin.local:
```
- If you log on to another node or use a gateway, run the following command to access the admin tool.
```
kadmin -p <admin-user> -w <admin-password>
```
  Note
  If you use the KDC provided by E-MapReduce, note the following parameters:
  
  <admin-user>: The value is fixed to root/admin.
  
  <admin-password>: In the E-MapReduce console, go to the Configure tab of the Kerberos service to get the value of the admin_pwd parameter.
  The following output indicates that you have entered the kadmin command-line interface:
```
Authenticating as principal root/admin with password.
kadmin:
```
Run the following command to create a principal named test.
In this example, the password is 123456.
```
addprinc -pw 123456 test
```
The following output indicates that the principal was created successfully:
```
Principal "test@EMR.C-85D4B8D74296****.COM" created.
```
Note
Record the username and password. You will need them to create a ticket-granting ticket (TGT).

Run the quit command to exit the Kerberos admin.local tool.
Log on to the machine where you want to run the Hive client, and run the following commands to create and switch to the test user.
```
useradd test
su test
```
Run the following command to create a TGT.
```
kinit
```
Press Enter and enter the password for the test user. In this example, the password is 123456.
Run the hive command to access the Hive client.
```
hive
```

Connect using the Beeline client

Run the following command to start the Kerberos admin.local tool.
- If you log on to the master-1-1 node where the Kerberos Key Distribution Center (KDC) server runs as the root user, you can run the following command to directly access the admin tool.
```
kadmin.local
```
  The following output indicates that you have entered the kadmin.local command-line interface:
```
Authenticating as principal hadoop/admin@EMR.C-85D4B8D74296****.COM with password.
kadmin.local:
```
- If you log on to another node or use a gateway, run the following command to access the admin tool.
```
kadmin -p <admin-user> -w <admin-password>
```
  Note
  If you use the KDC provided by E-MapReduce, note the following parameters:
  
  <admin-user>: The value is fixed to root/admin.
  
  <admin-password>: In the E-MapReduce console, go to the Configure tab of the Kerberos service to get the value of the admin_pwd parameter.
  The following output indicates that you have entered the kadmin command-line interface:
```
Authenticating as principal root/admin with password.
kadmin:
```
Run the following command to create a principal named test.
In this example, the password is 123456.
```
addprinc -pw 123456 test
```
The following output indicates that the principal was created successfully:
```
Principal "test@EMR.C-85D4B8D74296****.COM" created.
```
Note
Record the username and password. You will need them to create a ticket-granting ticket (TGT).

Run the quit command to exit the Kerberos admin.local tool.
Log on to the machine where you want to run the Hive client, and run the following commands to create and switch to the test user.
```
useradd test
su test
```
Run the following command to create a TGT.
```
kinit
```
Press Enter and enter the password for the test user. In this example, the password is 123456.
Run the following command to access the Hive client.
```
beeline -u "jdbc:hive2://master-1-1.c-56187feb57f0****.cn-hangzhou.emr.aliyuncs.com:10000/;principal=hive/_HOST@EMR.c-56187feb57f0****.COM"
```
Replace the following placeholders with your information:
- master-1-1.c-56187feb57f0****.cn-hangzhou.emr.aliyuncs.com: The fully qualified hostname, including the domain name. You can obtain the hostname by running the hostname -f command on the node where the HiveServer2 service runs (typically the master-1-1 node).
- EMR.c-56187feb57f0****.COM: The realm name. In the E-MapReduce console, go to the Configure tab for the Kerberos service to find the value of the realm parameter.

High availability clusters

Connect using the Beeline client

Depending on your selected service discovery mode (zooKeeper or multiServers), use the corresponding command to configure the Beeline connection.

Use zooKeeper mode

beeline -u 'jdbc:hive2://master-1-1:2181,master-1-2:2181,master-1-3:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2'

Use multiServers mode

beeline -u 'jdbc:hive2://master-1-1:10000,master-1-2:10000,master-1-3:10000/default;serviceDiscoveryMode=multiServers'

FAQ

What should I do if I get a "port unreachable" error when connecting to Hive?

If you encounter a port unreachable error when connecting to Hive, troubleshoot from the following two aspects:

Confirm that the HiveServer2 service is running.

On the HiveServer2 node, run the following command to check whether a service process is listening on the specified port (default is 10000).
```
netstat -tulnp | grep 10000
```
Ensure the output includes a HiveServer2-related service process. If no such process appears, check the HiveServer2 startup logs to confirm it started correctly.
Check whether the security group allows traffic on port 10000.

In the EMR console, on the Basic Information page of the destination cluster, click the link next to Cluster Security Group. On the Security Group Details page, verify that port 10000 is open. If it is not open, open port 10000. For more information, see Manage security groups.

How do I connect to Hive if both high availability and high security are enabled?

If both high availability and high security are enabled, first obtain Kerberos credentials (by creating a Kerberos principal and acquiring a TGT), then connect to Hive using the JDBC method. For more information, see HiveServer2 load balancing.