Support Questions
Find answers, ask questions, and share your expertise

NiFi Extract Table MySQL

NiFi Extract Table MySQL

Contributor

Hi,

I would like to extract a big table (MySQL, more than 3 millions rows) and to write it as a file in HDFS.

What would be the best way to do it ?

I tried the following processors :

- ExecuteSQL : error : pb memory

- QueryDatabaseTable : error : pb memory

- GenerateTableFetch : error : failed to invoke @OnScheduled method due to java.lang.RuntimeException

I have 20 Go of memory.

What would be the best way to do it ? Can I set up parameters so that I generate more than 1 DataFlow, then merge in NiFi before loading to HDFS ?

Thank you.

5 REPLIES 5
Highlighted

Re: NiFi Extract Table MySQL

@Raphaël MARY

What does the configuration of the QueryDatabaseTable processor look like?

Highlighted

Re: NiFi Extract Table MySQL

Contributor

QueryDatabaseTable is like this :

14816-2017-04-21-15-46-09.png

GenerateTableFetch is like this :

14817-2017-04-21-15-50-43.png

Highlighted

Re: NiFi Extract Table MySQL

@Raphaël MARY

You have allocated 20GB to the NiFi JVM correct?

Will you post the memory error please?

Highlighted

Re: NiFi Extract Table MySQL

Contributor

Correct.

I am testing 3 ways actually, and I want to know what is the best wayt to do it :

1. For ExecuteSQL : error : pb memory

14818-2017-04-21-16-11-13.png

14819-2017-04-21-16-13-29.png

-----------------------------------------------------

2. For QueryDatabaseTable :

I can extract data but 1 record = 1 flow file. and it is very slow.

Is it possible to generate a flow file every 1000 records for example and then merge theses flow files into a single one?

-----------------------------------------------------

3. For GenerateTableFetch : error : failed to invoke @OnScheduled method due to java.lang.RuntimeException

14820-2017-04-21-16-15-10.png

Highlighted

Re: NiFi Extract Table MySQL

@Raphaël MARY

For method #2, I would set the Max Rows Per Flow File to 1000 and monitor the performance. You might be able to increase above 1000.