JDBC Connection Pool for ConvertJsonToSQL

[#1]

Hi,

What's the role of the JDBC Connection Pool property of the ConvertJsonToSQL processor ?

How to use this processor with Hive ? I noticed that HiveConnectionPool was possible choice for this property in previous NiFi version but it's no more the case ?

Thanks

[#2]

@Joe Harvy

The Connection Pool is necessary in order to determine the appropriate database column types when building the SQL from the provided JSON.

Thanks,

Matt

[#3]

Hi @Matt Clarke, in this case how we connect a ConvertJsonToSQL processor to Hive DB?

You cannot select a HiveConnectionPool as the value of the JDBC driver property and if i try to setup configure a DBCP connection pool with Hive like this:

I get a driver error: Cannot load JDBC driver class for the class i have configured.

My flow uses an incoming JSON file to create the SQL statement with the ConvertJsonToSQL processor, and then i want to send the flowfile with the SQL statement to PutHiveQL to insert the data into Hive.

Is there something wrong with my approach?

Thanks in advance!

[#4]

ConvertJSONToSQL does not currently support Hive (NIFI-4071). For completeness I should mention as of NiFi 1.2.0 you can replace ConvertJSONToSQL -> PutSQL with PutDatabaseRecord, it will accept JSON (via a JsonTreeReader) and do the conversion and also execute the statement(s), this can be more efficient especially if you are using SplitJson upstream, as you can remove that and handle the whole JSON array as a single flow file. However it too does not support Hive for the reasons mentioned in NIFI-4071.