E-MapReduce:Hive connection methods

Use the Hive client

Run the following command:

hive

To exit, run quit; or exit;.

Use Beeline

Run the following command:

beeline -u jdbc:hive2://master-1-1:10000

To exit, run !quit or !exit.

Use JDBC

1. Add hadoop-common and hive-jdbc as dependencies in your pom.xml file:

   <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>

   Make sure the versions match the Hadoop-Common and Hive versions in your EMR cluster. Check these in the Software Information section of the Basic Information tab in the EMR console.

2. Write code to connect to HiveServer2 and query data:

   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();
           }
   
           // Replace <Public IP address of the master node> with the actual public IP address
           Connection con = DriverManager.getConnection(
               "jdbc:hive2://<Public IP address of the master node>: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.

3. Package the project as a JAR file and upload it to the host where you want to run it.

   Important

   The JAR file requires hadoop-common and hive-jdbc to run. If your runtime environment does not include these dependency packages in its environment variables, you must download and configure them or package them together in the JAR file. If these dependencies are missing when you run the JAR file, the following errors are reported:

   • 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 emr-hiveserver2-1.0.jar. Upload this JAR file to the master node of the E-MapReduce cluster.

4. Run the JAR file:

   Important

   The server where you run the JAR file and the E-MapReduce cluster must be in the same VPC and security group and have network connectivity. If they are in different VPCs or network environments, you must use a public IP address for access. Alternatively, you can use a network product to establish a connection between them and then use the internal network for access. To test network connectivity:

   • Public network: telnet <Public IP address of master-1-1> 10000

   • Internal network: telnet <Internal IP address of master-1-1> 10000

   java -jar emr-hiveserver2-1.0.jar

Set up Kerberos authentication

1. Open the Kerberos admin CLI.

   • On master-1-1 (the Key Distribution Center (KDC) node), run as root:

     kadmin.local

     The CLI is ready when the output includes:

     Authenticating as principal hadoop/admin@EMR.C-85D4B8D74296****.COM with password.
     kadmin.local:

   • On any other node or gateway, run:

     kadmin -p <admin-user> -w <admin-password>

     Replace the placeholders:

     Placeholder	Value
     <admin-user>	root/admin (for a self-managed KDC in EMR)
     <admin-password>	The value of the admin_pwd parameter, found on the Configure tab of the Kerberos service in the EMR console

     The CLI is ready when the output includes:

     Authenticating as principal root/admin with password.
     kadmin:

2. Create a Kerberos principal named test:

   Note

   Save the username and password — you need them to create a TGT.

   addprinc -pw 123456 test

   The principal is created when the output includes:

   Principal "test@EMR.C-85D4B8D74296****.COM" created.

   To exit the admin CLI, run quit.

3. Create a system user and switch to it:

   useradd test
   su test

4. Create a TGT:

   kinit

   When prompted, enter the password for the test user (in this example, 123456).

Use the Hive client

After completing the Kerberos setup above, run:

hive

Use Beeline

After completing the Kerberos setup above, run:

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 values based on your cluster:

Use Beeline with ZooKeeper service discovery

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

Use Beeline with multi-server service discovery

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