@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. ... View more

@Rui Pinheiro The data duplication is because of SelectHiveQL processor won't store the state. So every time when you execute the hive query it will result same data and adding newly added records(if records got added to the table) every time that's the reason why you are getting duplicated data. For this use case we need to store the last value some where in Hive/Hbase/HDFS/DistributeCache, then when you run SelectHiveQL statement you need to pull the state value and keep the state value as attribute, then use the value in your SelectHiveQL statement. Example:- let's take i'm having a table with following columns in it id(int),name(string),ts(timestamp) and i want to run SelectHiveQL process incrementally My hive statement would be like below select * from table-name where ts > '${start_value}' and ts < '${current_time}' We need to prepare start_value and current_time attributes before selecting hive data. Flow:- 1.GenerateFlowfile //add current_time attribute to the flowfile,Right Click on processor goto configure then click on + sign and add current_time value as ${now():format("yyyy-MM-dd HH:mm:ss"} 2.SelectHiveQL/FetchHbaserow/FetchHdfs/FetchDistributecache processors depending on where you have stored your state value 3.ExtractText/EvaluateJsonPath/UpdateAttribute processors //Extract the value got stored and keep as start_value attribute, in this step we are preparing start_value attribute and this start_value will be base value. 4.UpdateAttribute processor //to check the start_value attribute for the first run value will be empty assign some value to it(like 1900-01-01 00:00:00..etc). 4.selectHiveQL processor //select * from table-name where ts>'${start_value}' and ts <${current_time}, as we are having start_value and current_time attributes to the flowfile now we are running hive statements using those attribute values. 5.Fork the data set <br> 5.1. do your processing with the incremental hive dataset 5.2. store the current_time attribute value by using PutHiveStreaming/PutHbaseCell/PutHDFS/PutDistributeCa cheMap Processors //once you store the current_time value then when your process starts again will pul l this state value and this will be your start_value. . 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 more

@Yassine Do alter table on all tables and change the external table to internal table then drop the table. Example:- Write a script which can execute below statement for all the tables that are in warehouse directory hive> ALTER TABLE <table-name> SET TBLPROPERTIES('EXTERNAL'='False'); //changing the tbl properties to to make the table as internal hive> drop table <table-name>; //now the table is internal if you drop the table data will be dropped automatically. ... View more

@Rubén Carmona Run Join on Source side:- You can use Execute Sql processor and run sql join on two tables on clientID, Select all the required columns that you need on the result set then store the results into HDFS and create hive table on top it. (or) Based on the clientid that we got from first table:- 1.Use QueryDatabaseTable/GenerateTableFetch+executeSql processors to pull first table data having clientID,clientName as we are going to get the output data in avro format. 2.Use ConvertAvroToJson processor //now we are going to have json array having all the records in it. 3.SplitJson processor //to split each record into individual flowfileEvaluateJson Path processor //extract the required fields from the json message and keep them as flowfile attributes. 4.ExecuteSql //use the extracted attributes to query the second table select * from tablename where clientid=${clientID}(assuming attributename is clientID in evaluatejsonpath processor)Now we have queried the second table based on clientid,now again convert the output from execute sql processor5.Use ConvertAvroToJson processor //now we are going to have json array having all the records in it.6.SplitJson processor //to split each record into individual flowfile.7.EvaluateJson Path processor //extract the required fields from the json message and keep them as flowfile attributes.8.AttributesTojson processor //to build new json message with all the required attributes.9.inferred avro schema processor //to build avro schema to the json messageConvert JSONtoAvro processor.10.MergeContent processor //use merge strategy as Avro,if you want to merge small flowfiles into one big flowfile11.ConvertAvroToORC processor //convert the merged avro content to orc format.12.PutHDFS processor //store the data into HDFS,create hive table on top of hdfs directory. (or) Run Join using Hive:- 1.ingest two tables into HDFS using NiFi, Then create hive tables on top them. 2.once ingestion is completed then Run join on hive tables using PutHiveQl processor and write results to new table(i.e insert into final table select * from table1 join table2 on table1.clientid=table2.clientid) if we are querying based on the clientid that we got from the first table that process will include lot of processors in NiFi, it's easy to run join on both tables either on source side (or) hadoop side. ... View more