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, 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 click New 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 Permissions 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 and 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 optionally modify other advanced settings. 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 the directory %FineBI%\webapps\webroot\WEB-INF\lib and restart FineBI, FineBI remains on the startup page. 

Solution:

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 folders, as shown in the following figure.

Restart FineBI.

Query Statement

During the A join B on A.a = B.b query. A and B does not support subqueries.

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, the SQL statement select T_2.a from (select T_1.a from (select * from A) T_1) T_2 will report an error message.

FAQ

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

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 that set in /etc/hosts.

Check if the IP address and machine name in the hosts file of BI is 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: ERROR 726 (43M10): Inconsistent namespace mapping properties. Cannot initiate connection as SYSTEM:CATALOG is found but client does not have phoenix.schema.isNamespaceMappingEnabled enabledERROR 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 specific code is as follows:

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