Data Connection to Apache Phoenix

Version

Application Scenario

This document introduces how to connect to the Apache Phoenix database in FineBI.

Note:

This database does not support data tables with direct connection attributes.

Version/Driver

Connection Information Collection

Before connecting the database to FineBI, you need to collect the following information:

• IP address and port number of the database server

• Database  name

• Username and password (if username and password authentication is used); client principal and keytab file path (if Kerberos authentication is used)

• Schema of the database to be connected

1. Log in to FineBI as the admin, choose System Management > Data Connection > Data Connection Management, and choose Create > Data Connection, as shown in the following figure.

Note:

If you are not the admin, you can configure data connections only after the admin assigns you the permission on data connection management. For details, see Data Connection Permission Overview.

2. Find the Apache Phoenix icon, as shown in the following figure.

3. Select Custom from the Driver drop-down list, select the driver uploaded in section "Version/Driver", and enter the connection information collected in section "Connection Information Collection."

HBase supports Kerberos authentication. For details about Kerberos authentication methods, see Kerberos Authentication in Data Connection.

Note:

You can modify other advanced settings as needed. For details, see section "Advanced Settings" in Entry-Level Data Connection Configuration.

4. Click Click to Connect Database, and select the database schema after successful connection. (If no database schema is selected, the default schema will be used.) Finally, save the data connection, as shown in the following figure.

Two methods are available to add database tables to FineBI.

• Adding Database Tables

• Adding SQL Datasets

FineBI Always on the Startup Page

Problem:

After you place phoenix-4.12.0-HBase-1.2-client.jar in FineBI installation directory\webapps\webroot\WEB-INF\lib and restart FineBI, FineBI remains on the startup page, as shown in the following figure.

Solution:

1. Open the phoenix-4.12.0-HBase-1.2-client.jar file with a compression tool, enter the javax folder, and delete the el and servlet files, as shown in the following figure.

2. Restart FineBI.

Query Statement

1. In the A join B on A.a = B.b query, A and B do not support subqueries.

2. Try to avoid multi-level nested SQL statements. Multi-level nesting may cause SQL errors. If SQL nesting has been used in your original SQL dataset, queries may be faulty when real-time data is enabled for the engine.

For example, an error will occur if the SQL statement select T_2.a from (select T_1.a from (select * from A) T_1) T_2 is used.

FAQ and Solution

1. Description: The database cannot be connected through JDBC. In this case, an error occurs, indicating that the system failed to get locations or ZooKeeper failed to find the corresponding address.

Solution:

Check if the IP address corresponding to the machine name in /etc/hosts is a local IP address.

Check if the machine name set in /etc/hostname is consistent with the configuration in /etc/hosts.

Check if the IP address and machine name in the hosts file of BI are correct.

2. Description: BI test connection failed. Error message: "Caused by: java.sql.SQLException: org.apache.hadoop.hbase.DoNotRetryIOException: java.lang.IllegalAccessError: tried to access method com.google.common.base.Stopwatch.<init>()V from class org.apache.hadoop.hbase.zookeeper.MetaTableLocator"

Solution:

Replace the guava package with guava-16.0.1.jar.

3. Description: BI test connection failed. Error message (43M10): “Inconsistent namespace mapping properties. Cannot initiate connection as SYSTEM:CATALOG is found but client does not have phoenix.schema.isNamespaceMappingEnabled enabled” ERROR 726 (43M10): “Inconsistent namespace mapping properties. Cannot initiate connection as SYSTEM:CATALOG is found but client does not have phoenix.schema.isNamespaceMappingEnabled enabled” 

Error screenshot

Solution:

If the custom namespace is used in Hbase, the default mapping is used. That is to say, namespace in HBase maps schema in Phoenix. However, the default mapping is not enabled. You need to add the following configuration items in hbase-site.xml.

The code is as follows:

<property>
    <name>phoenix.schema.isNamespaceMappingEnabled</name>
    <value>
true</value></property><property
>
    <name>phoenix.schema.mapSystemTablesToNamespace</name>
    <value>
true</value></property>