Hive JDBC Application Development

Updated on 2024-08-10 GMT+08:00

View PDF

Hive is an open-source data warehouse framework built on Hadoop. You can use it to store structured data and analyze data with the Hive query language (HiveQL) statements. Hive converts HiveQL statements to MapReduce or Spark jobs to query and analyze massive amounts of data stored in Hadoop clusters.

You can use Hive to:

Extract, transform, and load (ETL) data with HiveQL.
Analyze massive amounts of structured data with HiveQL.
Process data in a wide range of formats, such as JSON, CSV, TEXTFILE, RCFILE, ORCFILE, and SEQUENCEFILE, and customize extensions.
Connect the client flexibly and call JDBC APIs.

Hive is good for offline massive data analysis (such as log and cluster status analysis), large-scale data mining (such as user behavior analysis, interest region analysis, and region display), and other scenarios.

MRS provides sample application development projects based on Hive. This practice provides guidance for you to obtain and import a sample project after creating an MRS cluster and then compile and debug the code locally. In this sample project, you can create Hive tables, insert data, and read data.

Creating an MRS Hive Cluster

Create and purchase an MRS cluster that contains Hive. For details, see Buying a Custom Cluster.
NOTE:

In this practice, an MRS 3.1.5 cluster, with Hadoop and Hive installed and with Kerberos authentication enabled, is used as an example.
Click Buy Now and wait until the MRS cluster is created.

Preparing the Application Development Configuration File

Log in to FusionInsight Manager and create a cluster user for security authentication of the sample project.

Choose System > Permission > User. On the displayed page, click Create. On the displayed page, create a machine-machine user, for example, developuser.

Add hive and supergroup to User Group.
Choose System > Permission > User. In the Operation column of developuser, choose More > Download Authentication Credential. Save the file and decompress it to obtain the user.keytab and krb5.conf files of the user.
Choose Cluster. On the Dashboard tab, click More and select Download Client. In the dialog box that is displayed, set Select Client Type to Configuration Files Only and click OK. After the client package is generated, download the package as prompted and decompress it.

For example, if the client configuration file package is FusionInsight_Cluster_1_Services_Client.tar, decompress it to obtain FusionInsight_Cluster_1_Services_ClientConfig_ConfigFiles.tar. Then, continue to decompress this file.
1. Go to the FusionInsight_Cluster_1_Services_ClientConfig_ConfigFiles\Hive\config directory and obtain the configuration files.
2. Copy all content from the hosts file in the decompression directory to your local hosts file. Ensure that the local PC can communicate with the hosts listed in the hosts file in the decompression directory.
  NOTE:
  - In this practice, ensure that the local environment can communicate with the network plane where the MRS cluster is deployed. Generally, you can access the MRS cluster via an EIP.
  - C:\WINDOWS\system32\drivers\etc\hosts is an example directory in a Windows environment for storing the local hosts file.

Obtaining the Sample Project

Obtain the sample project from Huawei Mirrors.

Download the source code and configuration files of the sample project, and configure related development tools on your local PC. For details, see Obtaining Sample Projects from Huawei Mirrors.

Select a branch based on the cluster version and download the required MRS sample project.

For example, the sample project suitable for this practice is hive-jdbc-example, which can be obtained at https://github.com/huaweicloud/huaweicloud-mrs-example/tree/mrs-3.1.5/src/hive-examples/hive-jdbc-example.
Use IDEA to import the sample project and wait for the Maven project to download the dependency packages. For details, see Configuring the JDBC Sample Project.

Figure 1 Hive sample project

After you configure Maven and SDK parameters on the local PC, the sample project automatically loads related dependency packages.
Place the cluster configuration files and user authentication credentials obtained in Preparing the Application Development Configuration File into the resources directory of the sample project.
To connect to an MRS cluster with Kerberos authentication enabled, specify related authentication information in the sample code.
In the JDBCExample class of the com.huawei.bigdata.hive.examples package, change USER_NAME to the username you are using, for example, developuser.
```
KRB5_FILE = userdir + "krb5.conf";
System.setProperty("java.security.krb5.conf", KRB5_FILE);
USER_NAME = "developuser";
if ("KERBEROS".equalsIgnoreCase(auth)) {
    USER_KEYTAB_FILE = "src/main/resources/user.keytab";
    ZOOKEEPER_DEFAULT_SERVER_PRINCIPAL = "zookeeper/" + getUserRealm();
    System.setProperty(ZOOKEEPER_SERVER_PRINCIPAL_KEY, ZOOKEEPER_DEFAULT_SERVER_PRINCIPAL);
}
...
```
In this sample project, the development roadmap based on the service requirement is as follows.
1. Prepare data.
  1. Create an employee information table employees_info.
  2. Load employee information to employees_info.
2. Analyze data.
  Collect the number of records in the employees_info table.
3. Deletes the table.

Building and Running the Application

Compile the JDBC sample program.

Click Terminal in the lower left corner of the IDEA page to access the terminal. Run the mvn clean package command to perform compilation.

If "BUILD SUCCESS" is displayed, the compilation is successful. A JAR file containing the -with-dependencies field is generated in the target directory of the sample project.

[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  03:30 min
[INFO] Finished at: 2023-05-17T20:22:44+08:00
[INFO] ------------------------------------------------------------------------

Create a directory as the runtime directory, for example, D:\jdbc_example in your local environment, save the generated JAR packages whose names contain the -with-dependencies field to the directory, and create the src/main/resources subdirectory in the directory. Copy all files from the resources directory of the sample project to this local subdirectory.
Run the following commands in the Windows CMD environment:

cd /d d:\jdbc_example

java -jar hive-jdbc-example-XXX-with-dependencies.jar

Check the output information after running the sample. The following information indicates that Hive table operations are successfully executed:

...
2023-05-17 20:25:09,421 INFO  HiveConnection - Login timeout is 0
2023-05-17 20:25:09,656 INFO  HiveConnection - user login success.
2023-05-17 20:25:09,685 INFO  HiveConnection - Will try to open client transport with JDBC Uri: jdbc:hive2://192.168.64.216:21066/;principal=hive/hadoop.hadoop.com@HADOOP.COM;sasl.qop=auth-conf;serviceDiscoveryMode=zooKeeper;auth=KERBEROS;zooKeeperNamespace=hiveserver2;user.principal=developuser;user.keytab=src/main/resources/user.keytab
2023-05-17 20:25:30,294 INFO  JDBCExample - Create table success!
2023-05-17 20:26:34,032 INFO  JDBCExample - _c0
2023-05-17 20:26:34,266 INFO  JDBCExample - 0
2023-05-17 20:26:35,199 INFO  JDBCExample - Delete table success!
...

Parent topic: Rapidly Develop MRS Component Applications

Previous topic: HDFS Application Development

Next topic: Hive HCatalog Application Development