question Re: Convert column data type in querydatabasetable in Support Questions

Convert column data type in querydatabasetable

ronlabau — Fri, 16 Sep 2022 13:37:22 GMT

Hi, I'm getting some data from a pg database, and it has 2 "Text" types, which I think are my problem. When I go to insert those to a redshift table, i'm getting a jdbc error, "given type does not match given object". I think i need to use nifi expression language to convert the text column toString, so it will work with varchar, but i'm not sure. any help appreciated.

Thanks,

Ron

Re: Convert column data type in querydatabasetable

Shu_ashu — Fri, 24 Aug 2018 04:26:20 GMT

@Ron Labau

Try to use Cast the fields names to Varchar in your QueryDatabaseTable processor configs in Columns to Return.

If the above method doesn't work then Use ConvertRecord processor to read your Incoming Avro data and in Record SetWriter configure the field names to have string datatype.

So that we are going have OutputFlowfile in Avro format with string datatype.

Refer to this link for configuring/usage of the ConvertRecord processor.

Re: Convert column data type in querydatabasetable

ronlabau — Fri, 24 Aug 2018 22:58:08 GMT

Thanks Shu, that seems to work! is that expression language, or is that basically doing regular sql functions on the db?

Thanks!

Re: Convert column data type in querydatabasetable

Shu_ashu — Sat, 25 Aug 2018 01:57:25 GMT

@Ron Labau

if you are using QueryDatabaseTable processor then it is regular sql function on db.
if you are using convertRecord processor then we are doing in NiFi.