Hive Application Development Rules

Load the Hive JDBC driver

The client software connects to HiveServer using Java database connectivity (JDBC). Therefore, you must load the JDBC driver class org.apache.hive.jdbc.HiveDriver for Hive.

Use the current class loader to load the driver class.

If there is no jar package in classpath, the client software throws "Class Not Found" and exits.

Example:

Class.forName("org.apache.hive.jdbc.HiveDriver").newInstance();

Set up a database connection

The driver management class java.sql.DriverManager of JDK is used to obtain a connection to the Hive database.

The Hive database URL is url="jdbc:hive2://xxx10.64xxx.22xxx.231xxx:2181,10xxx.64xxx.22xxx.232xxx:2181,10xxx.64xxx.22xxx.233xxx:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2;sasl.qop=auth-conf;auth=KERBEROS;principal=hive/hadoop.hadoop.com@HADOOP.COM;user.principal=hive/hadoop.hadoop.com;user.keytab=conf/hive.keytab";

In this example, ZooKeeper is deployed on three nodes and the default port is 2181. xxx.xxx.xxx.xxx indicates each of the IP addresses of the three nodes.The user name and password are null or empty because authentication has been performed successfully.

Example:

// Set up a connection. 
 connection = DriverManager.getConnection(url, "", "");

Execute HQL

Note that the HQL statement cannot end with a semicolon (;).

Correct:

String sql = "SELECT COUNT(*) FROM employees_info"; 
 Connection connection = DriverManager.getConnection(url, "", ""); 
 PreparedStatement statement = connection.prepareStatement(sql); 
 resultSet = statement.executeQuery();

Incorrect:

String sql = "SELECT COUNT(*) FROM employees_info;"; 
 Connection connection = DriverManager.getConnection(url, "", ""); 
 PreparedStatement statement = connection.prepareStatement(sql); 
 resultSet = statement.executeQuery();

Close a database connection

After the client executes the HQL, close the database connection to prevent memory leakage.

Close the statement and connection objects of the JDK.

Example:

finally { 
             if (null != statement) { 
                 statement.close(); 
             } 

             // Close the JDBC connection. 
             if (null != connection) { 
                 connection.close(); 
             } 
         }

HQL syntax used to check for null values

Use is null to check whether a field is empty, that is, the field has no value. Use is not null to check whether a field is not mull, that is, the field has a value.

If you use is null for a character whose type is String and length is 0, False is returned. Use col = '' to check for null values, and use col != '' to check for non-null values.

Correct:

select * from default.tbl_src where id is null; 
 select * from default.tbl_src where id is not null; 
 select * from default.tbl_src where name = ''; 
 select * from default.tbl_src where name != '';

Incorrect:

select * from default.tbl_src where id = null; 
 select * from default.tbl_src where id != null; 
 select * from default.tbl_src where name is null; 
 select * from default.tbl_src where name is not null;

Note that the type of the id field in the tbl_src table is Int, and the type of the name field is String.

The client configuration parameters must be consistent with the server configuration parameters

If the configuration parameters of the Hive, YARN, and HDFS servers of the cluster are modified, the related parameter in a client program will be modified. You need to check whether the configuration parameters submitted to the HiveServer before the configuration parameters are modified are consistent with those on the servers. If the configuration parameters are inconsistent, modify them on the client and submit them to the HiverServer. In the following example, if the parameter of YARN in the cluster is modified, the parameter submitted to the HiverServer from the Hive client and sample program before the modification must be reviewed and modified.

Initial state:

The parameter configuration of YARN in the cluster is as follows:

mapreduce.reduce.java.opts=-Xmx2048M

The parameter configuration on the client is as follows:

mapreduce.reduce.java.opts=-Xmx2048M

The parameter configuration of YARN in the cluster after the modification is as follows:

mapreduce.reduce.java.opts=-Xmx1024M

If the parameter in the client program is not changed, the parameter is still valid. This will result in insufficient memory for reducer and lead to MR running failure.

Multithread security login mode

If multiple threads are performing login operations, the relogin mode must be used for the subsequent logins of all threads after the first successful login of an application.

login example code:

   private Boolean login(Configuration conf){ 
     boolean flag = false; 
     UserGroupInformation.setConfiguration(conf); 

     try { 
       UserGroupInformation.loginUserFromKeytab(conf.get(PRINCIPAL), conf.get(KEYTAB)); 
       System.out.println("UserGroupInformation.isLoginKeytabBased(): " +UserGroupInformation.isLoginKeytabBased()); 
       flag = true; 
     } catch (IOException e) { 
       e.printStackTrace(); 
     } 
     return flag; 

   }

relogin example code:

public Boolean relogin(){ 
         boolean flag = false; 
         try { 

           UserGroupInformation.getLoginUser().reloginFromKeytab(); 
           System.out.println("UserGroupInformation.isLoginKeytabBased(): " +UserGroupInformation.isLoginKeytabBased()); 
           flag = true; 
         } catch (IOException e) { 
             e.printStackTrace(); 
         } 
         return flag; 
     }

Prerequisites for using the REST interface of WebHCat to submit an MR task in Streaming mode

The REST interface depends on the streaming packages of Hadoop. Before submitting an MR task to WebHCat in Streaming mode, upload hadoop-streaming-2.7.0.jar to the specified path of the HDFS: hdfs:///apps/templeton/hadoop-streaming-2.7.0.jar. Log in to the node where the client and Hive service are installed. Assume that the client installation path is /opt/client.

source /opt/client/bigdata_env

Run the kinit command to log in to the node as the human-machine or machine-machine user.

hdfs dfs -put ${BIGDATA_HOME}/FusionInsight_HD_8.1.0.1/FusionInsight-Hadoop-*/hadoop/share/hadoop/tools/lib/hadoop-streaming-*.jar /apps/templeton/

/apps/templeton/ need to be modified based on different instances. The default instance uses /apps/templeton/ and the Hive1 instance uses /apps1/templeton/. The others follow the same rule

Read and write operations cannot be performed on the same table at the same time

Currently, Hive does not support concurrent operations. Read and write operations cannot be performed on the same table at the same time. Otherwise, query results may be inaccurate and tasks may fail.

A bucket table does not support insert into

A bucket table does not support insert into, and only supports insert overwrite; otherwise, the number of files and the number of buckets will be inconsistent.

Prerequisites for using some REST interfaces of WebHCat

Some REST interfaces of WebHCat depend on the JobHistoryServer instance of MapReduce. The interfaces are as follows:

• mapreduce/jar(POST)
• mapreduce/streaming(POST)
• hive(POST)
• jobs(GET)
• jobs/:jobid(GET)
• jobs/:jobid(DELETE)

Hive Authorization Description

It is recommended that Hive authorization (databases, tables, or views) be performed on the Manager authorization page. Authorization in command-line interface is not recommended except in the alter databases databases_name set owner='user_name' scenario.

Hive on HBase partition tables cannot be created

Data of Hive on HBase tables is stored on HBase. Because HBase tables are divided into multiple partitions that are scattered on RegionServer, Hive on HBase partition tables cannot be created on Hive.

A Hive on HBase table does not support insert overwrite

HBase uses a RowKey to uniquely identify a record. If data to be inserted has the same RowKey as the existing data, HBase will use the new data to overwrite the existing data. If insert overwrite is performed for a Hive on HBase table on Hive, only data with the same RowKey will be overwritten.