nifi-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Burgess <mattyb...@apache.org>
Subject Re: ConvertJSONToSQL empty VALUES fields
Date Sat, 09 Dec 2017 01:56:58 GMT
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 <alberto@propus.com.br> 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

Mime
View raw message