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]

Hi,

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:

https://issues.apache.org/jira/browse/NIFI-7257

It will, hopefully, solve the issue.

For my part, I implemented a specific connector which modifies the classpath:

https://github.com/dams666/nifi-dbcp-connectionpool

In short:

public static final PropertyDescriptor DB_DRIVER_LOCATION = new PropertyDescriptor.Builder()
.name("database-driver-locations")
.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'")
.defaultValue(null)
.required(false)
.addValidator(StandardValidators.createListValidator(true, true, StandardValidators.createURLorFileValidator()))
.expressionLanguageSupported(ExpressionLanguageScope.VARIABLE_REGISTRY)
.dynamicallyModifiesClasspath(true)
.build();

...

dataSource = new BasicDataSource();
dataSource.setDriverClassName(drv);
dataSource.setDriverClassLoader(this.getClass().getClassLoader());

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.)

My setup:

Database Connection URL :

jdbc:phoenix:zk4-xxx.ax.internal.cloudapp.net,zk5-xxx.ax.internal.cloudapp.net,zk6-xxx.ax.internal.cloudapp.net:2181:/hbase-unsecure

Database Driver Class Name:

org.apache.phoenix.jdbc.PhoenixDriver

Damien