Alberto, This came up the other day as well, the generated SQL is a Prepared Statement, which allows the code to use the same statement but then just set different values based on "parameters". In this case the values for the parameters are stored in "positional" flow file attributes for the statement, so for INSERT something like "sql.args.1.value = 7501". If you use PutSQL, it will be looking for these attributes and things should work fine. However PutSQL doesn't support Hive AFAIK, which is why there's a PutHiveQL. Unfortunately, PutHiveQL is expecting those attributes in the form "hiveql.args.1.value", with a "hiveql" prefix instead of "sql".
I'm curious as to what DBCPConnectionPool you are using to configure your ConvertJSONToSQL processor, given that your target database is Hive. It used to be that using Hive as the target would give an error (NIFI-4071 [1]). If this is no longer the case somehow, we should update that Jira. One option (if there are a small number of known parameters) is to use UpdateAttribute to store the sql.args.*.* attributes into hiveql.args.*.* attributes. You can also use ExecuteScript to accomplish this for arbitrary numbers of attributes/parameters. I have just written NIFI-4684 [2] to cover the addition of a property to ConvertJSONToSQL that will let you specify the attribute prefix. It would presumably default to "sql" to maintain current behavior but could be changed by the user to "hiveql" if desired. Regards, Matt [1] https://issues.apache.org/jira/browse/NIFI-4071 [2] https://issues.apache.org/jira/browse/NIFI-4684 On Fri, Dec 8, 2017 at 4:45 PM, Alberto Bengoa <[email protected]> wrote: > Hey Folks, > > I'm having some problems with ConvertJSONToSQL processor. > > I'm ingesting a JSON like this: > > { > "_Time_Stamp" : 1512146156211, > "_Operation" : 4, > "cdn_fabrica" : 7501, > "char_1" : "Value 1", > "char_2" : null > } > > On the SQL relationship I got a query like this: > > UPDATE progress_cad2esp.man_fabrica SET char_1 = ?, char_2 = ? WHERE > cdn_fabrica = ? > > Even trying an INSERT query I got something like this: > > INSERT INTO progress_cad2esp.man_fabrica (cdn_fabrica, char_1, char_2) > VALUES (?, ?, ?) > > My current flow is: QueryDatabaseTable -> ConvertAvroToJson -> SplitJson -> > ExtractText -> RouteOnAttribute -> ConvertJSONToSQL > > My target database is on Hive. > > I read a lot on Google about problems with this processor and Hive, but I'm > not sure if it is not solved on Hive 1.2.0. > > Any idea? > > Tks, > Alberto
