Quantcast

How to use ConvertRecord Processor

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to use ConvertRecord Processor

suman.das@cuddle.ai
Hi All,
I am using nifi version 1.2.0.

We have CSV files given by Vendor with random number of Columns in the file.
We want to save the contents of the File in Postgres Database.

I have created a Flow with the following processors.

GetFile-->ConvertRecord-->ConvertJSONToSql--->PutSql

In ConvertRecord i have used CSVReader to read the record and JSONRecordSetWriter.

In JSONRecordSetWriter Controller Service is INVALID  telling SCHEMA Registry is invalid . How to fix it.

I just want to take the CSV file column headers as name.

Please Help
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to use ConvertRecord Processor

Koji Kawamura-2
Hello,

If you'd like to load data from CSV file to database table, 1.2.0 also
has PutDatabaseRecord processor.
Then you can configure PutDatabaseRecord to use CSVReader. Since you
have your schema within incoming file as CSV header, no need to setup
Schema Registry.

However, "random number of Columns" can be challenging I think.

I've shared an example flow template here.
https://gist.github.com/ijokarumawak/ddf8a67162a97d022e226c706d9f3386

Hope this helps,
Koji

On Wed, May 17, 2017 at 6:18 PM, [hidden email]
<[hidden email]> wrote:

> Hi All,
> I am using nifi version 1.2.0.
>
> We have CSV files given by Vendor with random number of Columns in the file.
> We want to save the contents of the File in Postgres Database.
>
> I have created a Flow with the following processors.
>
> GetFile-->ConvertRecord-->ConvertJSONToSql--->PutSql
>
> In ConvertRecord i have used CSVReader to read the record and
> JSONRecordSetWriter.
>
> In JSONRecordSetWriter Controller Service is INVALID  telling SCHEMA
> Registry is invalid . How to fix it.
>
> I just want to take the CSV file column headers as name.
>
> Please Help
>
>
>
>
> --
> View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/How-to-use-ConvertRecord-Processor-tp15873.html
> Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to use ConvertRecord Processor

suman.das@cuddle.ai
Hi,
Thanks for your help . It worked.

I used the following processors
GetFile-->PutDatabaseRecord-->PutFile

Only doubt i have is how to specify particular dataType for a column.

For Eg: I have file like below and want to insert into the table with City as Varchar and Count as Integer in Postgres.

City,Count
Mumbai,10
Mumbai,10
Pune,10
Pune,10
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to use ConvertRecord Processor

Matt Burgess-2
The CSVReader, if using "Use String Fields From Header" as the Schema
Access Strategy, will treat all fields as Strings since there is no
good way of knowing what value(s) the records/rows will contain for
each column. In that case you'd need to know the schema(s) of the
possible CSV files coming in, add them to an AvroSchemaRegistry
instance, and use a different Schema Access Strategy.

If there is a way to tell from the filename or something in the
content which schema corresponds to it, you could use RouteOnAttribute
and/or RouteOnContent to send the different CSV files down different
paths, where you could set the "avro.schema" attribute to the schema
explicitly, or if they are all in the AvroSchemaRegistry you could set
the "schema.name" attribute to the corresponding name in the
AvroSchemaRegistry. Then all the paths could join back up to a single
ConvertRecord, or each branch could have its own. In either case, the
attribute you set would correspond to the Schema Access Strategy you
select in your CSVReader.

Regards,
Matt


On Thu, May 18, 2017 at 2:10 AM, [hidden email]
<[hidden email]> wrote:

> Hi,
> Thanks for your help . It worked.
>
> I used the following processors
> GetFile-->PutDatabaseRecord-->PutFile
>
> Only doubt i have is how to specify particular dataType for a column.
>
> For Eg: I have file like below and want to insert into the table with City
> as Varchar and Count as Integer in Postgres.
>
> City,Count
> Mumbai,10
> Mumbai,10
> Pune,10
> Pune,10
>
>
>
> --
> View this message in context: http://apache-nifi-developer-list.39713.n7.nabble.com/How-to-use-ConvertRecord-Processor-tp15873p15901.html
> Sent from the Apache NiFi Developer List mailing list archive at Nabble.com.
Loading...