Hadoop Hive Data Connection

1.1 Application scenarios

Hadoop is a very popular distributed computing solution, and Hive is a data warehouse infrastructure built on Hadoop.

Hive defines a simple SQL-like query language called HQL, which allows users familiar with SQL to query data. In this chapter, we will introduce how to connect to the Hadoop Hive database in FineBI.

1.2 Matters needing attention

The connected Hive database needs to be in the mapreduce operating mode. Other modes such as tez mode will cause an error when using it.

2.1 Configuration information

There are two ways to connect to Hive:

• Method 1: After installing the driver isolation plug-in, connect to the database.

  For details on downloading and installing the driver isolation plug-in, please refer to: driver isolation plug-in

• Method 2: Do not use driver isolation plug-in, directly connect to the database.

  Refer to section 2.2 of this article for connection method. But in this way, there may be conflicts between JAR packages and cause connection failure. It is recommended that users use the first method.

2.2 Connection step

1) Download the corresponding Hadoop Hive driver compression package according to the database version, and unzip it to the FineBI folder %FineBI%\webapps\webroot\WEB-INF\lib, and restart FineBI.

2) Log in to the data decision system, select "Management System> Data Connection> Data Connection Management", click "New Data Connection> All" and select "Hadoop Hive", as shown in the following figure:

3) Click OK to enter the data connection configuration page, and enter the corresponding information of your own database according to the configuration information provided above.

Kerberos authentication can be selected. If you choose kerberos authentication, you need to fill in the name of the client registered with kdc, the keytab key path, and the corresponding URL for kerberos authentication. Refer to Data Connection Kerberos Authentication, as shown in the following figure:

• Before connecting, please check whether the corresponding IP of the machine name in /etc/hosts is a LAN IP;

• Check whether the machine name setting in /etc/hostname is consistent with that in /etc/hosts;

• Check whether the IP + machine name of the hosts configuration of the machine where FineBI is located is correct;

• When connecting locally, you need to configure the /etc/hosts file and add the remote mapping: IP+machine name, for example: 192.168.5.206 centos-phoenix.

If you do not choose kerberos authentication, it will be consistent with other database settings, just choose to fill in the relevant information (for related introduction, please refer to the configuration data connection), as shown in the following figure:

2.3 Test connection

Click Test Connection, if a pop-up box of test connection success as shown in the figure below appears, it means that the database is successfully connected. As shown below:

After the test connection is successful, click the "Save" button in the upper right corner, and the data connection is added successfully. As shown below:

3.1 Remove the table name from the field name in the query

When connecting to the Hadoop Hive database, FineBI will add the table name before the field name that is queried by default. If you do not want to add the table name, you need to configure it in the database file hive-site.xml. As shown in the figure below, change the value of value from the default true to false.

3.2 The database address cannot be pinged

When the database address cannot be pinged, an error will be reported: java:sql.SQLException: Could not open client transport with JDBC, it may be that the database and the BI server have a network disconnection, as shown in the following figure:

3.3 Hive connection error 08s01

Problem Description:

When hive is connected, error 08s01 is reported, and the error log is as follows:

Error: Could not open client transport with JDBC Uri: jdbc:hive2://192.168.100.132:10000: java.net.ConnectException: Connection refused (Connection refused) (state=08S01,code=0)

solution:

The service of Hive Server2 is not turned on, the command to turn on is: hive --service hiveserver2 &