Timestamp incompatibility (NiFi's PutSQL)

​I resolved this by using an ExecuteStreamCommand processor which calls a python script which converts a JSON line in to it's respective SQL insert statement. The table of interest in this case is reddit_post.

Code of the python script (I'm aware that there is no need for the INSERT argument, but this is there because I plan on adding an UPDATE option later on):

import jsonimport argparseimport sys# For command line argumentsparser = argparse.ArgumentParser(description='Converts JSON to respective SQL statement')parser.add_argument('statement_type', type=str, nargs=1)parser.add_argument('table_name', type=str, nargs=1)# Reading the command line argumentsstatement_type = parser.parse_args().statement_type[0]table_name = parser.parse_args().table_name[0]# Initialize SQL statement statement = ''for line in sys.stdin:  # Load JSON line  json_line = json.loads(line)  # Add table name and SQL syntax  if statement_type == 'INSERT':    statement += 'INSERT INTO {} '.format(table_name)  # Add table parameters and SQL syntax  statement += '({}) '.format(', '.join(json_line.keys()))  # Add table values and SQL syntax  # Note that strings are formatted with single quotes, other data types are converted to strings (for the join method)  statement += "VALUES ({});".format(', '.join("'{0}'".format(value.replace("'", "''")) if type(value) == str else str(value) for value in json_line.values()))  # Send statement to stdout  print(statement)​

Configuration of ExecuteStreamCommand (Note that Argument Delimeter is set to a single space):

Flow snippet:

I hope this can help someone that came across a similar issue. If you have any advice on how to improve the script, flow, or anything else please don't hesitate to let me know!

You may be able to use UpdateAttribute before PutSQL, along with the toDate() and format() Expression Language functions, to convert your timestamp value into something that the database will accept.

Alternatively, you may be able to skip the SplitText, ConvertJSONToSQL, and PutSQL steps by using PutDatabaseRecord, you might be able to configure a RecordReader that will accept your timestamp format and convert accordingly. If that works, it's a much better solution as it will handle the whole flow file at once (instead of individual lines)

Just adding the 3 digits of milliseconds solved the problem for me with the ExecuteSQL processor - "yyyy-mm-dd hh:mm:ss.sss"