Hi Scott, I am not sure why you would create a new table per file - that does not seem like a good idea to me honestly. How are going to manage these tables then? IMHO if you are on SQL Server 2016, you may consider storing json natively in SQL Server and then use various Json functions. The huge benefit of this approach is that you do not have to model data upfront and you won't lose original data in case you mess things up (and we all do). Normally you would want to retain your original raw data so you can reprocess it.
But if you want to create a new table per file using totally dynamic and changing list of columns, I would probably use a custom Groovy script to figure out your input file structure and then build CREATE TABLE statement snippet for every file and INSERT snippet. Record schema in NiFi assumes a known schema upfront so I do not think it is a good use of this feature for your dynamic use case. Here is a good post that shows how to work with Json http://funnifi.blogspot.com/2016/02/executescript-json-to-json-conversion.html I found Groovy very easy to learn so do not be afraid of it if it is something you have not used before. You can also use other languages to do a similar thing but Groovy is a very compact language and has good performance in NiFi, since it is JVM based language. Boris On Sat, Mar 10, 2018 at 7:18 PM, scott <[email protected]> wrote: > Hello NiFi community, > > I have a problem I've been working on that I was hoping to get some > suggestions for a solution. I'm ingesting a dump of json records from a web > service periodically. The json sometimes comes with some fields missing. My > goal is to insert the data into a SQLServer DB after dynamically creating > the table based on the fields in the json records. The problem is that when > I use the list of fields from a record that is missing some fields, I get > errors when I try to insert. How do I take all the fields from all records > in my dump and then create a unique list of all fields for me to create the > table with? > > > Thanks, > > Scott > >
