Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Rising Star

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

avatar
Master Guru
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
2 REPLIES 2

avatar
Master Guru
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login

avatar

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.