Apache Phoenix data connection

Apache Phoenix targets low-latency applications through the perfect combination of online transaction processing and Hadoop runtime analysis platform. It supports standard SQL and the ability to include transaction atomicity, isolation, consistency, and durability of the JDBC API, and bind it flexibly later. It is based on the read time mode of the NoSQL world, and uses HBase as the backend storage. This article describes how to connect to the Apache Phoenix database in FineBI.

2.1 Configuration information

Note: The database JAR package easily conflicts with other Hadoop series JAR packages (Hadoop, Hive, Impala, TRANSWARP INCEPTOR, etc.). If you cannot connect, you need to delete the JAR of the conflicting database. For details, see 3.1.

2.2 Steps

2.2.1 Copy the JAR to FineBI

Download the Apache Phoenix driver from the configuration information above, and put the driver package under %FineBI%\webapps\webroot\WEB-INF\lib, and restart FineBI.

2.2.2 Administrator login data decision system

Select "Manage>Data Connection>Data Connection Management", click "New Data Connection", select "APACHE Phoenix" under "All", as shown in the figure below:

2.2.3 Enter configuration info and test connection

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

• If you choose "Kerberos" authentication, you need to fill in "Client Principal" and "Keytab Key Path", and the URL corresponding to Kerberos authentication. For details, please refer to: Data Connection Kerberos Authentication, as shown in the following figure:

• If Kerberos authentication is not selected, it is consistent with other database settings. According to the configuration information provided in section 2.1 of this article, enter the corresponding information of the database, and you can choose to fill in the advanced setting information. For details, see Configure Data Connection), as shown in the following figure:

Note: According to the provided configuration information, after entering the corresponding information of the database for data configuration, the data connection URL will be automatically parsed, but for some special scenarios, the parsed results may be different, which may cause fail to connect. It is recommended to copy the link directly to the data connection URL to prevent automatic parsing errors.

2) Click "Test Connection", or click "Click to Connect Database" in the "Pattern", if the "Connection Successful" pops up, it means that the database is successfully connected, as shown in the following figure:

2.2.4 Select the pattern

1) After the test connection is successful, you can select the pattern, and select the corresponding pattern according to your needs. If you do not select, the first pattern will be the default. As shown below:

2) After the pattern is selected, click "Save" in the upper right corner, and the data connection is added successfully. As shown below:

3.1 FineBI is always on the startup page

Problem phenomenon:

After putting phoenix-4.12.0-HBase-1.2-client.jar in the directory %FineBI%\webapps\webroot\WEB-INF\lib, restart FineBI, FineBI is always on the startup page. As shown below:

Solution:

1) Open the phoenix-4.12.0-HBase-1.2-client.jar, enter the javax file, delete the "el" and "servlet" folders, as shown in the following figure:

2) Restart FineBI.

3.2 Query statement

1) A join B on A.a=B.b, A B does not support subqueries.

2) Try to avoid using multiple levels of nested SQL statements. Multiple levels of nesting may cause SQL errors. If the user's original SQL dataset already has SQL nesting, there may be problems with the query when the engine opens real-time data.

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.

3.3 Problems and solutions

1) Description: JDBC access to the database cannot be connected, prompting get locations error or zookeeper can not find the corresponding address.

Solution:

• 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 the BI is located is correct.

2) Description: BI test connection failed. Error:

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:

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 you use the custom namespace in Hbase, not just default, then in Phoenix, it corresponds to "schema". But it is not enabled by default, 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>