question I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there. in Support Questions

I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Fri, 16 Sep 2022 12:16:50 GMT

As you can see the 3 mb got transferred in screenshot - 1. And in the screenshot -2 I am specifying my Path where I want it stored. I basically want the CSV file written in the untitled text file. Am I doing it right.? I very new to NIFI. Please Help..:

@Wynner

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

Shu_ashu — Sun, 18 Aug 2019 07:22:56 GMT

Hi @Shailesh Nookala, PutFile processor Writes the contents of a FlowFile to the local file system.

1.You can find the file in NiFi running nodes only as the property create missing directories is true, NiFi will creates those directories for you and store the csv file into your directory(i.e /Users/nraghuram/...).

Example:-

1.Lets take you are having 2 NiFi nodes(E01,E02) and you are running GetFile processor on only primary node(consider E02 is primary), so when you run on primary node then the File got stored in the specified directory of your processor on primary node(in our case on E02 because it is primary node).
2.In this example im having E02 is primary node and running PutFile processor to store File in local directory.

3.As Highlighted below the File is on E02 node

4.When i store the file it is inside /d1 directory on E02 node only because i'm running GetFile only on Primary Node.

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Fri, 22 Sep 2017 03:00:46 GMT

Hi @Yash. Thank you so much for taking your time to reply to my Question.

I am sorry I do not understand your answer. I do not know what nodes are.

What I was trying to do is download a CSV file from filezilla and insert it into a table in sql server. I used FetchFTP to get a CSV file from Filezilla. Before inserting I though I would try writing the file into my local machine. That's where i got struck.

Can you teach me how to insert it into a table in sql server.

If you know any good tutorials. Please suggest me.

Thanks

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

Shu_ashu — Sun, 18 Aug 2019 07:22:42 GMT

@Shailesh Nookala
Sure, if you are thinking to insert the data to Sql Server we can do that by using NiFi, You don’t have to download the file at all.

You can do that couple of ways by using ExtractText Processor and extract all the contents of csv file into attributes and prepare insert statement by using ReplaceText Processor and push the data to Sql server.

Another method is preparing json document for input CSV file and extracting all the attributes of flow file and prepare record to insert data to SQL server.
I’m using Json method which is easy to extract attributes of flowfiles.

Example:-

Let’s consider you are having the csv file having just 2 records in it.
1,test,1000

2,test1,2000

As I’m using NiFi 1.1 there is no direct processor which can convert the incoming csv file to json.

we need to follow

InferAvroSchema-->  ConvertCSVToAvro --> ConvertAvroToJSON

the output would be in Json format with the same contents of our input CSV file.
But in new versions of NiFi(I think from 1.3) there are processors to convert CSV to Json directly using ConvertRecord Processor.
InferAvroSchema:-

In this processor I’m giving the incoming csv file definition (or) you can read header definition from incoming flowfile by changing Get CSV Header Definition From Data property to true NiFi can read definition from 1 line of file.
as I know my incoming file having id,name,salary I have given this definition and keep Schema Output Destination to flowfile-attribute we can make use of this schema in ConvertCSVToAvro processor.

ConvertCSVToAvro:-

change the Record Schema property to

${inferred.avro.schema}

Which can infer the schema from flowfile attribute.

ConvertAvroToJSON:-

drag and drop ConvertAvroToJSON processor and leave the properties to default.
Output:-
[{"id": 1, "name": "test", "salary": 1000},{"id": 2, "name": "test1", "salary": 2000}]

SplitJson:-

If you are having more than 1 record in your csv file use splitjson processor because if ConvertAvroToJSON processors find more than one record in the flowfile it will make an array and keep all the records inside the array.
As we need only 1 record in the flowfile before inserting data to Sqlserver.

If your flowfile having array then json path expression property should be

$.*

as it will dynamically splits the array of records to one record for one flowfile.

Input:-

[{"id": 1, "name": "test","salary": 1000},{"id": 2, "name":"test1", "salary": 2000}]

Output:-

flowfile1:-
{"id": 1, "name": "test", "salary":
1000}flowfile2:-
{"id": 2, "name": "test1", "salary":
2000}

As Splijson splits the array into individual records.

EvaluateJsonPath configs:-

in this processor extracting all the contents of flowfile to attributes by changing the list of properties

Destination as flowfile-attribute

extracting contents of json file by adding new properties by clicking + symbol on right corner.

id as $.id

name as $.name

salary as $.salary

ReplaceText Configs:-

We are preparing insert statement in this processor and change

Replacement Strategy property as AlwaysReplace

Use insert statement and give destination table name and use the extracted attributes to replace the contents of values dynamically.

insert into sqlserver_tablename (id,name,salary) values (${id},${name},${salary})

output:-

flowfile1 content:-
insert into sqlserver_tablename (id,name,salary) values(1,test,1000)
flowfile2 content:-
insert into sqlserver_tablename (id,name,salary) values(2,test1,2000)

PutSQL Configs:-
use putsql processor and make a connection pool to sql server enable and use the connection pool.
so Putsql processor will executes all the insert statements that we are having contents of the flowfile and all the data will get inserted to sql server.

FlowScreenshot:-

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Fri, 22 Sep 2017 19:28:36 GMT

Thanks you so much man @Yash. I'll try this out.:)

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Sun, 18 Aug 2019 07:22:16 GMT

@Yash

Hi man. I was following your answer step by step. I am getting error. Can you please help me fix it.

I am getting error "Convert CSV to AVRO" and "Convert "AVRO to JSON "processors.(Ignore the 3 processors in the 1st row)

Screenshot - 1 shows the errors.

At Convert CSV to AVRO it says cannot find Schema.

My Configuration of "CSV to AVRO" processor.

At "AVRO to JSON "processor.

"AVRO to JSON " configuration

I was trying to get a text file which has CSV's from filezilla. That part worked. and then the second step infer Avro schema also worked.

Is it because my CSV's are in text file, these errors occur.?

The only difference between your input and mine is. You hard coded the headers($is,$ name..) and I set take header from the first line of the file. Is it because of that.?

Please have a look man. Thanks

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

Shu_ashu — Sun, 24 Sep 2017 09:36:29 GMT

@Shailesh Nookala
i don't think header is causing these issues as i tried the same way as you and its working as expected.
But as i have observed in your flow you have connected all the available relations on each processor to the next processor, that's causing issues for you and i have seen PutFile processor instead of PutSQL in your flow.
Are you storing the prepared records to file? i thought you are pushing the prepared records to SQL server, is my assumption is wrong?. Connect only the following Relationships to the Next Processors:-

GetFile(Sucputsql.xmlcess)-->InferAvroSchema(Success)-->ConvertCSVToAvro(Success)-->ConvertAvroToJSON(Success)-->SplitJson(Split)-->EvaluateJsonPath(Matched)-->ReplaceText(Success)-->PutSQL(Autoterminate Success).

I have attached my .xml file use this make changes to that as per you needs.

putsql.xml
input :-

name,adresse,zip
1,hi,8000

InferAvroSchema configs:-

infer-avro.png

Convertcsvtoavro configs:-
csvtoavro.png
Output:-

{
  "name" : 1,
  "adresse" : "hi",
  "zip" : 8000
}

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Sun, 24 Sep 2017 23:16:44 GMT

@Yash

Hi.. Your understanding is right..I am trying to insert it into the table only. I just wanted to see what the output is at the end of the flow..That's why I added PUTFILE. I'll change it. Again thank you so much for replying. I'll make the changes and try again.

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Sun, 18 Aug 2019 07:21:50 GMT

@Yash

Hi..I changed the relationship in all my processors. I get this error in all of them. Please take a look.

I am getting the relationship error in all my processors.

This is what I did in the bridge that connects "InferAvroSchema" and "convert CSV to AVRO".(screenshot - 2)

This is my "inferavro" configuration

When I select success in "Automatically terminate relationships" it gets selected and after I connect it to the next processor it gets un-selected automatically.

Please take a look.

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

Shu_ashu — Sun, 18 Aug 2019 07:21:30 GMT

@Shailesh Nookala
as you have given Inferavroschema processor Success relation to convertAvrotoJson processor then you need to

Except success relation
Auto terminate the following relations failure,original,unsupported content i.e click on the check boxes for these 3 relations.
Make sure you haven't selected success relation.
Click on Apply button

why we haven't Autoterminated Success Relation?

Reason is we have connected success relation to convertavrotojson processor.

Once you are done with this do the same steps for all the other processors auto terminate all the relations Except the relation that you have connected to the next processor.

For the final processor(PutSQL) Auto Terminate success and reconnect failure and retry to the same processor.

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Sun, 18 Aug 2019 07:21:22 GMT

@Shu

I've been hard-coding the replacement value property in REPLACE TEXT processor ( eg - "insert into table_name values (..)" ).

Is there a way to do it dynamically.? Can I use reg-ex.? I tried a couple if things. It did not work. Can you please tell me what Search Value is.?

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

Shu_ashu — Sun, 18 Aug 2019 07:21:13 GMT

Hi @Shailesh Nookala, Keep Search Value config as is (?s)(^.*$)

ReplaceText Configs:-

We are preparing insert statement in this processor and change

Change ReplacementStrategy property to

AlwaysReplace

Use insert statement and give destination table name and use the extracted attributes to replace the contents of values dynamically.

change Replacement Value property to

insert into sqlserver_tablename (id,name,salary) values (${id},${name},${salary})

above statement will work dynamically with the attributes of the ff if we are having

id attribute value as 1 and

name as abc

salary as 1000

then this insert statement will be

insert into sqlserver_tablename (id,name,salary) values (1,abc,1000)

the same way this replace text processor prepares insert statements according to the attributes associated with the ff.

ReplaceText Configs:-

let me know if you still facing issues and please share more screenshots with configs and flow...!!!

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Sun, 18 Aug 2019 07:21:04 GMT

@Shu

Can you please help me fix this error. It says Incorrect syntax near','. Not able to figure out where I am making this error.

This is my CSV file.

My insert statement is

insert into [test_shailesh].[dbo].[nifiTest](policyID,statecode,county) values (${policyID},${statecode},${county})

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Sun, 18 Aug 2019 07:20:56 GMT

@Shu

This is the output of my EVALUATEJSONPATH Processor -

Guess this is fine.

This is the output of my REPLACETEXT processor

Don't really much about this.

This is the output of my PUTSQL processor

If you see the values section is blank with just two commas. Can you please tell me what is going wrong.

My insert statement in REPLACETEXT processor

insert into [test_shailesh].[dbo].[nifiTest](policyID,statecode,county) values (${policyID},${statecode},${county})

my overall flow -

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Mon, 09 Oct 2017 00:11:13 GMT

@Shu

I am not able to attach my CSV file. My CSV file has these two records.

policyID,statecode,county

498960,498960,498960

498960,498960,792148

name of my CSV file - FL_insurance_sample.csv

I created a table in my sql server with these three columns names all being varchar data type.

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

Shu_ashu — Mon, 09 Oct 2017 00:41:14 GMT

@Shailesh Nookala, i tested out with your csv file its working fine, can you attach all of your processors configs screenshots please.

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Sun, 18 Aug 2019 07:19:49 GMT

@Shu

Sure man. Here are the screenshots of all my processors -

1) GetFTP processor -

(contd)

2) InferAROSchema -

3) ConvertCSVtoAVRO

4) AVROToJSON -

(I'll post the rest it in the next answer since I allowed to post only 5 attachments)

Re: I used GetFTP processor to get a CSV file from filezilla and transferred it to a PUTFILE. it worked but when I open the directory where I wanted the file to save in laptop. It's not there.

shailuk — Mon, 09 Oct 2017 20:43:57 GMT

@Shu

It worked man! Thank you so very much:)

LoL. One last thing, What if the my table has more number of columns lets say 15-20 columns. Do we still have to hard code all the column names in the processors.?

eg -

In the insert statement (in replaceText processor) where we do

insert into table_name value (${column1},${column2}................)

Is there a way that nifi can do this dynamically? I mean if there are 20-25 columns in my table then it would be a pain to mention all the column names in the insert statement. Can I use regex or something.?