Re: [Nifi 1.5] PutDatabaseRecord and pgsql integer fields
Nice Pierre, that's consistent with which I'm experiencing Currently, this is what i'm using to process csv with header names different from db table : Access strategy = Schema text property Schema text = ... Treat First Line as Header = True Ignore CSV Header Column Names = False In the schema, names should match csv headers, and it'll take columns order to put in pgsql. As imported files structure come from external sources, we should adapt our pgsql structure (with buffer tables eventually) and this is an acceptable way to achieve for us. All the best François Lacombe 2018-04-09 11:30 GMT+02:00 Pierre Villard : > If I recall correctly, if you set the property "Ignore CSV Header Column > Names" to true, then it'll take the columns in the same order as defined in > the schema. > Same on the DB side (PutDatabaseRecord), with the property "Translate > Field Names". If false, the field names must match the column names > exactly, or the column will not be updated. If true, I think it'll assume > that fields in the input are in the same order as in the database. > > Someone more familiar with PutDatabaseRecord can chime in to correct me :) > > Pierre > > 2018-04-09 11:10 GMT+02:00 françois lacombe > : > >> Hi Pierre, >> >> Thank you for your input >> Schema certainly is a key point here. I used a CSVReader with Use Strings >> Fields from Header as access strategy. >> Moving access strategy to a text-defined schema solve the issue >> >> On side question: in the schema, what are fields name relative to, csv >> header or pgsql table? >> >> All the best >> >> François Lacombe >> >> 2018-04-09 10:47 GMT+02:00 Pierre Villard : >> >>> Hi François, >>> >>> How did you define your schema in the PutDatabaseRecord processor? >>> If you're using a CSV Reader and infer the schema using the header, then >>> all the fields will be considered as strings. >>> You probably want to define the schema by yourself so that you can >>> define the type of each column of your CSV file. >>> >>> Pierre >>> >>> >>> 2018-04-09 10:44 GMT+02:00 françois lacombe < >>> francois.laco...@dcbrain.com>: >>> Hi all, This is my first mail on this feed, as I've discovering NiFi a few days ago. I'm working for a company involved in software development for utilities networks operators One of my first use case to test NiFi is to load a csv file in an existing pgsql table, which sounds like a pretty common thing. I'm currently facing issues with integer fields. My csv file contains numbers and I use GetFile processor to load and PutDatabaseRecord seems to quote values with single quotes. PgSQL doesn't like it at all and raise errors (column is of type numeric but expression is of type character varying How can I prevent PutDatabaseRecord process to quote my numeric values? Thank you in advance for answers, best regards François Lacombe >>> >> >
Re: [Nifi 1.5] PutDatabaseRecord and pgsql integer fields
If I recall correctly, if you set the property "Ignore CSV Header Column Names" to true, then it'll take the columns in the same order as defined in the schema. Same on the DB side (PutDatabaseRecord), with the property "Translate Field Names". If false, the field names must match the column names exactly, or the column will not be updated. If true, I think it'll assume that fields in the input are in the same order as in the database. Someone more familiar with PutDatabaseRecord can chime in to correct me :) Pierre 2018-04-09 11:10 GMT+02:00 françois lacombe : > Hi Pierre, > > Thank you for your input > Schema certainly is a key point here. I used a CSVReader with Use Strings > Fields from Header as access strategy. > Moving access strategy to a text-defined schema solve the issue > > On side question: in the schema, what are fields name relative to, csv > header or pgsql table? > > All the best > > François Lacombe > > 2018-04-09 10:47 GMT+02:00 Pierre Villard : > >> Hi François, >> >> How did you define your schema in the PutDatabaseRecord processor? >> If you're using a CSV Reader and infer the schema using the header, then >> all the fields will be considered as strings. >> You probably want to define the schema by yourself so that you can define >> the type of each column of your CSV file. >> >> Pierre >> >> >> 2018-04-09 10:44 GMT+02:00 françois lacombe > >: >> >>> Hi all, >>> >>> This is my first mail on this feed, as I've discovering NiFi a few days >>> ago. >>> I'm working for a company involved in software development for utilities >>> networks operators >>> >>> One of my first use case to test NiFi is to load a csv file in an >>> existing pgsql table, which sounds like a pretty common thing. >>> I'm currently facing issues with integer fields. My csv file contains >>> numbers and I use GetFile processor to load and PutDatabaseRecord seems to >>> quote values with single quotes. >>> >>> PgSQL doesn't like it at all and raise errors (column is of type numeric >>> but expression is of type character varying >>> >>> How can I prevent PutDatabaseRecord process to quote my numeric values? >>> >>> >>> Thank you in advance for answers, best regards >>> >>> François Lacombe >>> >>> >> >
Re: [Nifi 1.5] PutDatabaseRecord and pgsql integer fields
Hi Pierre, Thank you for your input Schema certainly is a key point here. I used a CSVReader with Use Strings Fields from Header as access strategy. Moving access strategy to a text-defined schema solve the issue On side question: in the schema, what are fields name relative to, csv header or pgsql table? All the best François Lacombe 2018-04-09 10:47 GMT+02:00 Pierre Villard : > Hi François, > > How did you define your schema in the PutDatabaseRecord processor? > If you're using a CSV Reader and infer the schema using the header, then > all the fields will be considered as strings. > You probably want to define the schema by yourself so that you can define > the type of each column of your CSV file. > > Pierre > > > 2018-04-09 10:44 GMT+02:00 françois lacombe > : > >> Hi all, >> >> This is my first mail on this feed, as I've discovering NiFi a few days >> ago. >> I'm working for a company involved in software development for utilities >> networks operators >> >> One of my first use case to test NiFi is to load a csv file in an >> existing pgsql table, which sounds like a pretty common thing. >> I'm currently facing issues with integer fields. My csv file contains >> numbers and I use GetFile processor to load and PutDatabaseRecord seems to >> quote values with single quotes. >> >> PgSQL doesn't like it at all and raise errors (column is of type numeric >> but expression is of type character varying >> >> How can I prevent PutDatabaseRecord process to quote my numeric values? >> >> >> Thank you in advance for answers, best regards >> >> François Lacombe >> >> >
Re: [Nifi 1.5] PutDatabaseRecord and pgsql integer fields
Hi François, How did you define your schema in the PutDatabaseRecord processor? If you're using a CSV Reader and infer the schema using the header, then all the fields will be considered as strings. You probably want to define the schema by yourself so that you can define the type of each column of your CSV file. Pierre 2018-04-09 10:44 GMT+02:00 françois lacombe : > Hi all, > > This is my first mail on this feed, as I've discovering NiFi a few days > ago. > I'm working for a company involved in software development for utilities > networks operators > > One of my first use case to test NiFi is to load a csv file in an existing > pgsql table, which sounds like a pretty common thing. > I'm currently facing issues with integer fields. My csv file contains > numbers and I use GetFile processor to load and PutDatabaseRecord seems to > quote values with single quotes. > > PgSQL doesn't like it at all and raise errors (column is of type numeric > but expression is of type character varying > > How can I prevent PutDatabaseRecord process to quote my numeric values? > > > Thank you in advance for answers, best regards > > François Lacombe > >
[Nifi 1.5] PutDatabaseRecord and pgsql integer fields
Hi all, This is my first mail on this feed, as I've discovering NiFi a few days ago. I'm working for a company involved in software development for utilities networks operators One of my first use case to test NiFi is to load a csv file in an existing pgsql table, which sounds like a pretty common thing. I'm currently facing issues with integer fields. My csv file contains numbers and I use GetFile processor to load and PutDatabaseRecord seems to quote values with single quotes. PgSQL doesn't like it at all and raise errors (column is of type numeric but expression is of type character varying How can I prevent PutDatabaseRecord process to quote my numeric values? Thank you in advance for answers, best regards François Lacombe
