Hello Matt, Hmmm. I was expecting to have the entire SQL statement on the SQL relationship. Actually, I was planning to keep this flow "flowing" and use ReplaceText to adjust the SQL statement to persist data on Phoenix/HBase too. Bad news to me. :-(
Regarding NIFI-4071, I had seen this issue before. It looks to not affect me. I'm not sure if it's allowed to attach files here. If you don't mind I could send you screenshots of my configurations to your email. I have around 100 columns on each table. NIFI-4684 will come in handy. I would test your patch. Can you point me how could I build/compile a new version of this processor? Thanks! Alberto On Fri, Dec 8, 2017 at 11:56 PM, Matt Burgess <[email protected]> wrote: > 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 >
