Connecting to Phoenix on kerberized cluster from NiFi's DBCP Connection Pool

[#1]

Hello,

I am having issues creating a DBCP controller service that connects to Phoenix on a kerberized cluster. I have validated the jdbc connection string by connecting via sqlline.

Here is the error message I see when testing the connection by executing an ExecuteSQL processor (select * from $tbl) with the configured Phoenix DBCP controller service:

• org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (org.apache.hadoop.hbase.client.RetriesExhaustedException: Failed after attempts=36, exceptions: java.net.SocketTimeoutException: callTimeout=60000, callDuration=68422: row 'SYSTEM:CATALOG,,' on table 'hbase:meta' at region=hbase:meta

My understanding of the above error is that this often occurs with issues related to authentication and in particular when the hbase-site.xml is not available in the application's classpath.

My questions is, how do I make the configuration resources (hbase-site.xml, core-site.xml, hdfs-site.xml) available in the classpath for the DBCP controller service?

For example, in the HiveConnectionPool controller service, there is a property "Hadoop Configuration Resources" where the hive-site.xml, core-site.xml, and hdfs-site.xml locations can be specified.

I initially tried listing the configuration files locations in the property "Database Driver Location(s)" however that did not work, and after looking at the source code for the DBCPConnectionPool service it appears that the method "getDriverClassLoader" only attempts to load files that end in ".jar". Relevant source code: (line 233: (dir, name) -> name !=null && name.endsWith(".jar") --- DBCPConnectionPool.java)

My next idea is to add these configuration files to either of the 2 following locations:

• NiFi's conf directory (<nifi_root_path>/nifi/conf)
• DBCP's nar work directory (<nifi_root_path>/nifi/work/nar/extensions/nifi-dbcp-service-nar-1.0.0.2.0.1.0-12.nar-unpacked/META-INF/bundled-dependencies).

However, I don't know if either two options make sense or if this would have to repeated every time an upgrade occurs.

Does anyone have any suggestions for adding configuration resources to the DBCP controller service's classpath? Or any general suggestions on how to make jdbc connections to Phoenix on kerberized clusters via NiFi's DBCP controller service?

Any help would be greatly appreciated!

[#2]

@Paras Mehta I've been dealing with the same issue for a couple of weeks. The work around that I found was to use the Phoenix Query Server and JDBC Thin Client instead. It doesn't require any of the hadoop resources. However there does appear to be a performance penalty for large numbers of inserts. I'm still trying to track down if it's possible to add the hbase-site.xml to the NiFi class path as hinted at in the Hive Connection Pool but that wouldn't work if you have multiple Hadoop Clusters you're working with. Based on my research the last couple of weeks the NiFi community seems to be pretty anti Phoenix anyway so expect to have to fight with all of the processors due to the slight changes in syntax.

[#3]

@Paras Mehta, Did you got the solution to connect Kerberos enabled Phoenix with Nifi? I am also facing same issue.

[#4]

The workaround described in this article works:

https://community.hortonworks.com/content/supportkb/158121/errorcannot-create-poolableconnectionfact...

[#5]

The link you provided seems to be broken.

[#6]

I've been managing a similar issue for two or three weeks.The work around that I found was to utilize the Phoenix Question Server and JDBC Thin Customer.Anyway there appears to be an execution punishment for extensive quantities of supplements.Regardless i'm endeavoring to find if it's conceivable to add the hbase-site.xml to the NiFi class way as indicated at in the Hive Association Pool however that wouldn't work on the off chance that you have numerous Hadoop Bunches you're working with. In light of my exploration the most recent few weeks the NiFi people group is by all accounts truly hostile to Phoenix at any rate so hope to needto battle with the majority of the processors because of the slight changes in sentence structure.

[#7]

Could you describe more in detail how to use the thin client ? I'm facing the same issue.

[#8]

@DamD others, please follow this article.
https://my.cloudera.com/knowledge/ERRORCannot-create-PoolableConnectionFactory-row-SYSTEMCATALOG?id=...

In short the article tells you to add your:

hbase-site.xml
core-site.xml
hdfs-site.xml

 to your phoenix-client.jar

jar uf < phoenix client jar > hbase-site.xml core-site.xml

 and point to your  phoenix-client.jar from NIFI's 

DBCPConnectionPool 

[#9]

I couldn't open the link you provided.

by applying the jar uf command on the driver, I still couldn't get it to work. After a closer inspection in the driver, I see two files that sets the hbase.zookeeper.quorum: hbase-site.xml, and hbase-default.xml.

hbase-site sets the correct value, but hbase-default sets it to localhost. How can I be sure that the correct value is propagated ?

[#10]

I do not think that is the issue since on my working phoenix jar I have that too.

Did you restart your nifi?