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

NiFi Replace text: how to replace "ORDER BY asc" by "ORDER BY newid() asc"

I am ingesting data from MS SQL Server 2016 to hive using Nifi and the worflow is as follows :

GenerateTableFetch->ExtractText->ExecuteSQL->UpdateAttribute->ConvertAvroToORC->PutHDFS->ReplaceText->PutHiveQL

but using MS SQL 2012+ database type gives error in GenerateTableFetch. I got solution from this question on stackoverflow.

My generatetablefetch generates:

SELECT * FROM (SELECT TOP 50000 *, ROW_NUMBER() OVER(ORDER BY  asc) rnum FROM ABCD.dbo.DEFG) A WHERE rnum > 0 AND rnum <= 50000

How to configure ReplaceText processor so as to replace "ORDER BY asc" by "ORDER BY newid() asc" so that I can input that to my existing workflow to the ExecuteSQL processor?

1 ACCEPTED SOLUTION

Super Guru
@Kunal Gaikwad

Use replace text processor with Literal Replace as Replacement strategy, so that you can search for ORDER BY asc in your flowfile content and replace with ORDER BY newid() asc

Replace Text Configs:-
Search Value

ORDER BY asc
Replacement Value
ORDER BY newid() asc
Character Set
UTF-8
Maximum Buffer Size
1 MB
Replacement Strategy
Literal Replace
Evaluation Mode
Entire text

67561-replacetext.png

Input Flowfile content:-

SELECT * FROM (SELECT TOP 50000 *, ROW_NUMBER() OVER(ORDER BY  asc) rnum FROM ABCD.dbo.DEFG) A WHERE rnum > 0 AND rnum <= 50000

OutputFlowfile Content:-

SELECT * FROM (SELECT TOP 50000 *, ROW_NUMBER() OVER(ORDER BY newid() asc) rnum FROM ABCD.dbo.DEFG) A WHERE rnum > 0 AND rnum <= 50000
In output flowfile content we have replace ORDER BY asc with ORDER BY newid() asc

.

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

View solution in original post

2 REPLIES 2

Super Guru
@Kunal Gaikwad

Use replace text processor with Literal Replace as Replacement strategy, so that you can search for ORDER BY asc in your flowfile content and replace with ORDER BY newid() asc

Replace Text Configs:-
Search Value

ORDER BY asc
Replacement Value
ORDER BY newid() asc
Character Set
UTF-8
Maximum Buffer Size
1 MB
Replacement Strategy
Literal Replace
Evaluation Mode
Entire text

67561-replacetext.png

Input Flowfile content:-

SELECT * FROM (SELECT TOP 50000 *, ROW_NUMBER() OVER(ORDER BY  asc) rnum FROM ABCD.dbo.DEFG) A WHERE rnum > 0 AND rnum <= 50000

OutputFlowfile Content:-

SELECT * FROM (SELECT TOP 50000 *, ROW_NUMBER() OVER(ORDER BY newid() asc) rnum FROM ABCD.dbo.DEFG) A WHERE rnum > 0 AND rnum <= 50000
In output flowfile content we have replace ORDER BY asc with ORDER BY newid() asc

.

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

Hello @Shu,

I am wondering how can use the same technique but to replace order by clause with nothing. I would like to not use order by at all.

Thanks.

; ;