nifi-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Payne <marka...@hotmail.com>
Subject Re: Inserting to Database
Date Wed, 02 Aug 2017 12:39:23 GMT
Noel,

The easiest way is probably to use PutDatabaseRecord instead of PutSQL. This allows you to
put
a document that contains many entries into a database with a single query. Unfortunately,
though,
there is no XML Reader yet, so you'd need to convert the XML to JSON or CSV before using that
processor.

I know some people have been converting XML to JSON using TransformXml. An XSLT template that
may be
useful there is located at [1]. The nice thing about this is that you can have a single FlowFile
with many entries,
so you don't need to use SplitXml, EvaluateXPath, ReplaceText, UpdateAttribute... you can
just use TransformXml
to convert into JSON and then PutDatabaseRecord.

Does this help?

Thanks
-Mark



[1] https://github.com/bramstein/xsltjson


On Aug 2, 2017, at 5:46 AM, Noel Alex Makumuli <alexnoel81@gmail.com<mailto:alexnoel81@gmail.com>>
wrote:

Hello all,
Apologies I forgot to include the images in my previous questions. But I have still failed
to find a solution for my problem..
I tried to create a test using dummy data which looks like follows..

<employees>
<employee>
<name>James</name>
<job>developer</job>
</employee>
<employee>
<name>Tiger</name>
<job>designer</job>
</employee>
<employee>
<name>robert</name>
<job>engineer</job>
</employee>
</employees>

When I run the PutSql..
i get 3 inserts:
Insert into Employees (name,job) values ('james','developer')
Insert into Employees (name,job) values ('second',otherjob)
Insert into Employees (name,job) values ('jame', 'thirdjob')


But i would like to achieve just one query like::

Insert into Employees (name,job) values( ('james','developer'), ('second',otherjob), ('jame',
'thirdjob'))

How can i achieve this..?
I have been reading about sql.args.1.type and sql.args.1.value unfortunately i can not get
my head around.. it..
Please assist how to achieve this..
As i read this link but it is not clear.. Sample Example<https://community.hortonworks.com/articles/91849/design-nifi-flow-for-using-putsql-processor-to-per.html>

When I use ExtractText as suggest in the Sample Example mentioned above, i get the error:
sql.args.1.value is not accepted in the ExtractText..
Same applies for the ReplaceText as show in the image.

How can i achieve this..? i am not sure how to move forward please assist..

Please assist would be really happy.. and hope i made my self clear

Regards,


On 31 July 2017 at 19:02, Noel Alex Makumuli <alexnoel81@gmail.com<mailto:alexnoel81@gmail.com>>
wrote:
Hello
I have a bunch of XML  files which I transform then I evaluate the XPATH to get the [Please
refer to the NIFI flow]
Each file consist of a single record which needs to inserted to the database.

At the moment  flow processor is as follows::

ListFiles > FetchFile > TransformXml > EvaluateXpath > ReplaceText > PutSql..

In short, after transformation is done, i pick data i need as in image.
Then create my custom insert statement for the query.

Then i load the data into the database.

My question is i have thousand of files which I need to transform and load in the database.

So The PutSql Processor is inserting one file at a time..

I am not sure how to go about and load n files using sql.args.N.args and sql.args.N.value.

Please advice on how to insert a batch of hundreds of files in one insert statement.

Regards
--
NOEL ALEX MAKUMULI



--
NOEL ALEX MAKUMULI
TANZANIA

Mobile: +255 755 100 700 ( Active)
Mobile: +358 44 3077 817 ( Active)
<Screenshot_20170802_123802.png><Screenshot_20170802_123837.png><Screenshot_20170802_124013.png>


Mime
View raw message