Inserting to Database

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Inserting to Database

Noel Alex Makumuli
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting to Database

Noel Alex Makumuli
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

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 <[hidden email]> 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 (168K) Download Attachment
Screenshot_20170802_123837.png (52K) Download Attachment
Screenshot_20170802_124013.png (70K) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting to Database

Mark Payne
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





On Aug 2, 2017, at 5:46 AM, Noel Alex Makumuli <[hidden email]> 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

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 <[hidden email]> 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>

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Inserting to Database

Noel Alex Makumuli
Hello Mark,
For some reason I have failed to achieve my goal.
I have tried every possible reading and gone through countless places to try to find out how to do this and I have failed.
I am attached the sample 

170806  0:00:14     3 Query     SET autocommit=0
                   3 Query     INSERT INTO employee (job, name) VALUES ('dev', 'Jones')
                   3 Query     INSERT INTO employee (job, name) VALUES ('dev1', 'Eli')
                   3 Query     commit

No what I do, I keep on getting single inserts..
is it something I am missing or what am I doing wrong..
I have attached my flow file in mail on what i have progressed so far..

Please assist further on solving this issue.


Regards,

On 2 August 2017 at 15:39, Mark Payne <[hidden email]> wrote:
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





On Aug 2, 2017, at 5:46 AM, Noel Alex Makumuli <[hidden email]> 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

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 <[hidden email]> 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: <a href="tel:+255%20755%20100%20700" value="+255755100700" target="_blank">+255 755 100 700 ( Active)
Mobile: +358 44 3077 817 ( Active)
<Screenshot_20170802_123802.png><Screenshot_20170802_123837.png><Screenshot_20170802_124013.png>




--
NOEL ALEX MAKUMULI
TANZANIA

Mobile: +255 755 100 700 ( Active)
Mobile: +358 44 3077 817 ( Active)

databaseImport.xml (55K) Download Attachment
Loading...