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:
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:
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!
@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.
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.
@DamD others, please follow this article.
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
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 ?
I did restart nifi , but the problem still persists.
The next release of nifi will add a new DBCP connection Pool: Hadoop DBCP connection pool:
It will, hopefully, solve the issue.
For my part, I implemented a specific connector which modifies the classpath:
public static final PropertyDescriptor DB_DRIVER_LOCATION = new PropertyDescriptor.Builder()
.displayName("Database Driver Location(s)")
.description("Comma-separated list of files/folders and/or URLs containing the driver JAR and its dependencies (if any). For example '/var/tmp/mariadb-java-client-1.1.7.jar'")
.addValidator(StandardValidators.createListValidator(true, true, StandardValidators.createURLorFileValidator()))
dataSource = new BasicDataSource();
But I still got same error message:
PutSQL[id=94d192a9-fd1d-3c59-99be-d848f8902968] Failed to process session due to java.sql.SQLException: Cannot create PoolableConnectionFactory (ERROR 103 (08004): Unable to establish connection.): org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot create PoolableConnectionFactory (ERROR 103 (08004): Unable to establish connection.)
Database Connection URL :
Database Driver Class Name: