Support Questions
Find answers, ask questions, and share your expertise

ConvertJSONtoSQL Issue: How to get unmatched Columns?

Solved Go to solution
Highlighted

ConvertJSONtoSQL Issue: How to get unmatched Columns?

New Contributor

I am using ConvertJSONtoSQL to convert simple JSON to a SQL statement for inserting rows in DB2. I am using ConvertJSONtoSQL and PutSQL to write to DB2, but it is omitting the unmatched columns.

 

How can I add unmatched columns to the SQL? Is there a way to add the matching names in the JSON prior to running ConvertJSONtoSQL?

 

I tried using AttributestoJSON but it didn't work. Can someone please help me?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Re: ConvertJSONtoSQL Issue: How to get unmatched Columns?

Expert Contributor

@ang_coder   Tried it myself and have to correct my idea a little bit. Have a look at my example.
Here are my tables. The target table has two columns more than the source.
TablesTables
I defined following ControllerServices:
ControllerServicesControllerServices
And created this flow:
FlowFlow
Here the information of the single processors:
Processor informationProcessor information

In my example it works.
Sure there will be a more elegant solution but I don't know how.

View solution in original post

Highlighted

Re: ConvertJSONtoSQL Issue: How to get unmatched Columns?

Expert Contributor

@ang_coder 

The AvroSchemaRegistry is to set in the ControllerServices.
If it should be available in your whole NiFi just go to the canvas and click anywhere right -> choose "Configure". It is the same place where you define your database connections.


Configure -> ControllerServicesConfigure -> ControllerServices

If you already have JSON-format just ignore the first two steps of my example flow where data were retrieved and converted to JSON.


Start with your JSON at UpdateAttribute which doesn't care about the kind of flowfile content.
The ConvertRecord in my example uses an JSON-reader and JSON-writer where the writer points to the AvroSchemaRegistry with the schema.name set in UpdateAttribute. Therefore the JsonRecordSetWriter_IJ_TestMoreColumnsAtTarget (just to take a short name) has the specification "Schema Access Strategy" with option "Use Schema.Name Property". Have a look at the configuration details in my post before.

Hope this helps.

View solution in original post

5 REPLIES 5
Highlighted

Re: ConvertJSONtoSQL Issue: How to get unmatched Columns?

Expert Contributor

@ang_coder 

I'm not sure if i get you right and haven't done such a thing myselfe until now.

But if you use PutDatabaseRecord instead of PutSQL you have to specify a RecordReader. This will be configured as ControllerService and you can add a schema in AvroSchemaRegistry (also in ControllerServices). In this schema provide the full table definition with default values.

 

f.e. {
"name" : "zeichen",
"type" : ["string","null"]
},...

 

I suppose that all columns which are not included by your flowfile should be taken from the schema considering the default values.

Highlighted

Re: ConvertJSONtoSQL Issue: How to get unmatched Columns?

Expert Contributor

@ang_coder   Tried it myself and have to correct my idea a little bit. Have a look at my example.
Here are my tables. The target table has two columns more than the source.
TablesTables
I defined following ControllerServices:
ControllerServicesControllerServices
And created this flow:
FlowFlow
Here the information of the single processors:
Processor informationProcessor information

In my example it works.
Sure there will be a more elegant solution but I don't know how.

View solution in original post

Highlighted

Re: ConvertJSONtoSQL Issue: How to get unmatched Columns?

New Contributor

Can you please tell me where did you set the AvroSchemaRegistry? Since you are retrieving the data from SQL DB, you are getting it as Avro format. My incoming data is json format. So how can I use my flow file directly in Update Attribute and use in ConvertRecord? Does it make sense?

Highlighted

Re: ConvertJSONtoSQL Issue: How to get unmatched Columns?

Expert Contributor

@ang_coder 

The AvroSchemaRegistry is to set in the ControllerServices.
If it should be available in your whole NiFi just go to the canvas and click anywhere right -> choose "Configure". It is the same place where you define your database connections.


Configure -> ControllerServicesConfigure -> ControllerServices

If you already have JSON-format just ignore the first two steps of my example flow where data were retrieved and converted to JSON.


Start with your JSON at UpdateAttribute which doesn't care about the kind of flowfile content.
The ConvertRecord in my example uses an JSON-reader and JSON-writer where the writer points to the AvroSchemaRegistry with the schema.name set in UpdateAttribute. Therefore the JsonRecordSetWriter_IJ_TestMoreColumnsAtTarget (just to take a short name) has the specification "Schema Access Strategy" with option "Use Schema.Name Property". Have a look at the configuration details in my post before.

Hope this helps.

View solution in original post

Highlighted

Re: ConvertJSONtoSQL Issue: How to get unmatched Columns?

New Contributor

thank you very much! This works.