nifi-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Burgess <mattyb...@apache.org>
Subject Re: csv to sql
Date Thu, 09 Nov 2017 21:47:56 GMT
Austin,

Yes that's exactly what PutDatabaseRecord is for, it is kind of like
ConvertJSONToSQL + PutSQL, but it uses the record reader of your
choice, so it doesn't have to be JSON. You can set up a CSVReader and
a DBCPConnection pool pointing at your PostgreSQL DB, set the verb
(INSERT, e.g.) and it should put your lines directly into the database
using the given schema. Note that the input schema needs to match the
database (with a little leeway depending on your settings for
Ignore/Fail missing columns/fields, Normalize Field Names. etc.).

If you do find you need to convert to SQL, you can still do this with
ConvertRecord, using the same CSVReader but then a
FreeFormTextRecordSetWriter, where you can create your own SQL
statement using the incoming fields.  Then you'd set an attribute
"statement.type" to "sql", set the Statement Type in PutDatabaseRecord
to "Use statement.type attribute", then use a CSVReader whose schema
is a single field "sql" of type "string", ignores the header and/or
uses the explicit schema, and has a delimiter which cannot show up in
the input. That effectively treats your whole SQL line as a record
named "sql". Then you can set the Field Containing SQL property in
PutDatabaseRecord to "sql", and it will iterate over the lines in your
flow file, executing each as its own SQL statement.

That last way may sound a bit hacky, it may behoove us to implement a
SQLRecordSetWriter to take the place of ConvertJSONToSQL (but without
requiring JSON input of course, any record reader will do). That
second option above just emulates such a writer.

Hope this helps (and didn't confuse the issue!), let us know if you
have any issues or questions while setting this up.

Regards,
Matt


On Thu, Nov 9, 2017 at 4:04 PM, Austin Duncan <aduncan@pyaanalytics.com> wrote:
> all,
> is there a way to insert the data of a csv into postgres without having to
> convert it into json and then into an sql statement with the
> convertjsontosql processor.
>
> --
> Austin Duncan
> Researcher
>
> PYA Analytics
> 2220 Sutherland Avenue
> Knoxville, TN 37919
> 423-260-4172

Mime
View raw message