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

[#1]

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?

[#2 · Accepted solution]

@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

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.

[#3]

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.