Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Please see the Cloudera blog for information on the Cloudera Response to CVE-2021-4428

Handle format dynamically with column name

Explorer

Hi @Matt Burgess

I am beginner to Nifi and facing one issue with PutSQL.

Scenario:

I have two tables say TableA and TableB.In both table I have column 'date' but column sequence is not same.I am trying to apply format for 'date' column for TableA using ${sql.args.4.value:format("yyyy-MM-dd HH:mm:ss.SSS")} but as TableB has 4th column some text value not 'date' so it is failing for TableB.

If I will try to use both ${sql.args.4.value:format("yyyy-MM-dd HH:mm:ss.SSS")} for TableA and ${sql.args.5.value:format("yyyy-MM-dd HH:mm:ss.SSS")} for TableB then it is not working as both tables has different column sequence for 'date'.

Is there any way to handle this format dynamically by using column name?

Thanks

4 REPLIES 4

Explorer

@Matt Burgess

Thanks for your reply.

I got one solution to create a separate rule for each table in UpdateAttribute Processor and specify args but the problem which I am facing on alter table i.e. whenever there is an adding of a new column or deletion of existing column).Can you please suggest me some solutions for ex name like sql.args.<column name>.value or some other suggestions.

Super Guru

If you can get your input into JSON and use ConvertJSONToSQL then it will figure out the positions of your columns and generate the correct sql.args.N.value and sql.args.N.type attributes. It is not currently possible to refer to a column by name in the attributes used for a PreparedStatement in PutSQL, rather you'd have to generate the literal SQL yourself and not use attributes.

Explorer

Thanks @Matt Burgess for your help.

Explorer

@Matt Burgess

One more help ,I am using PutSQL to insert records in my MySQL DB but in case if there is any failure because of some db syntax or any reason ,I am not able to reload the failure records.I am able to see those failure records in "In" but not able to process records when I fix that issue and restart my nifi job.

I tried one approach by nested loop to PutSQL only by enabling retry or failure but it is not working.Can you please help me on this.