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

PUTSQL which cannot be converted to a timestamp

PUTSQL which cannot be converted to a timestamp

Explorer

The value of the sql.args.9.value is '2013-05-26 14:49:045', which cannot be converted to a timestamp

3 REPLIES 3

Re: PUTSQL which cannot be converted to a timestamp

@thuylevn   Your timestamp should be 'yyyy-mm-dd hh:mm:ss'

 

I believe that 3 digits for "ss" is your problem.


Can you adjust the data to be only 2 digits by ignoring the last?  Can you fix the timestamp upstream so the previous suggestion isn't required?

Re: PUTSQL which cannot be converted to a timestamp

Explorer

it is the same issue 

due to The value of the sql.args.9.value is '2013-05-26 14:49:45', which cannot be converted to a timestamp; routing to failure: java.sql.SQLDataException: The value of the sql.args.9.value is '2013-05-26 14:49:45', which cannot be converted to a timestamp 


<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<template encoding-version="1.3">
<description></description>
<groupId>360e8d37-0170-1000-2124-f7af50537a66</groupId>
<name>test_PutDatabseRecord</name>
<snippet>
<connections>
<id>4628c95c-b1dc-3fdd-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>7c677559-6b04-31fb-0000-000000000000</id>
<type>PROCESSOR</type>
<versionedComponentId>7c677559-6b04-31fb-b3f0-f177624fd063</versionedComponentId>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<loadBalanceCompression>DO_NOT_COMPRESS</loadBalanceCompression>
<loadBalancePartitionAttribute></loadBalancePartitionAttribute>
<loadBalanceStatus>LOAD_BALANCE_NOT_CONFIGURED</loadBalanceStatus>
<loadBalanceStrategy>DO_NOT_LOAD_BALANCE</loadBalanceStrategy>
<name></name>
<selectedRelationships>failure</selectedRelationships>
<source>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>29beb3d5-b88e-32df-0000-000000000000</id>
<type>PROCESSOR</type>
</source>
<zIndex>0</zIndex>
</connections>
<connections>
<id>532025c1-8aa9-3e5e-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<versionedComponentId>532025c1-8aa9-3e5e-b287-a646c6c3b0e1</versionedComponentId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>2b305558-f05a-3254-0000-000000000000</id>
<type>PROCESSOR</type>
<versionedComponentId>2b305558-f05a-3254-a069-72ac003eac04</versionedComponentId>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<loadBalanceCompression>DO_NOT_COMPRESS</loadBalanceCompression>
<loadBalancePartitionAttribute></loadBalancePartitionAttribute>
<loadBalanceStatus>LOAD_BALANCE_NOT_CONFIGURED</loadBalanceStatus>
<loadBalanceStrategy>DO_NOT_LOAD_BALANCE</loadBalanceStrategy>
<name></name>
<selectedRelationships>success</selectedRelationships>
<source>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>05136c6f-c4b4-3e66-0000-000000000000</id>
<type>PROCESSOR</type>
<versionedComponentId>05136c6f-c4b4-3e66-b29c-c4da38c83eed</versionedComponentId>
</source>
<zIndex>0</zIndex>
</connections>
<connections>
<id>5874ecf1-cb6b-3019-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<versionedComponentId>5874ecf1-cb6b-3019-bb91-3406bdc548ca</versionedComponentId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>7c677559-6b04-31fb-0000-000000000000</id>
<type>PROCESSOR</type>
<versionedComponentId>7c677559-6b04-31fb-b3f0-f177624fd063</versionedComponentId>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>0</labelIndex>
<loadBalanceCompression>DO_NOT_COMPRESS</loadBalanceCompression>
<loadBalancePartitionAttribute></loadBalancePartitionAttribute>
<loadBalanceStatus>LOAD_BALANCE_NOT_CONFIGURED</loadBalanceStatus>
<loadBalanceStrategy>DO_NOT_LOAD_BALANCE</loadBalanceStrategy>
<name></name>
<selectedRelationships>failure</selectedRelationships>
<source>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>05136c6f-c4b4-3e66-0000-000000000000</id>
<type>PROCESSOR</type>
<versionedComponentId>05136c6f-c4b4-3e66-b29c-c4da38c83eed</versionedComponentId>
</source>
<zIndex>0</zIndex>
</connections>
<connections>
<id>9a089cff-61b8-33f8-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<versionedComponentId>9a089cff-61b8-33f8-a5f9-67b717032071</versionedComponentId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>05136c6f-c4b4-3e66-0000-000000000000</id>
<type>PROCESSOR</type>
<versionedComponentId>05136c6f-c4b4-3e66-b29c-c4da38c83eed</versionedComponentId>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<loadBalanceCompression>DO_NOT_COMPRESS</loadBalanceCompression>
<loadBalancePartitionAttribute></loadBalancePartitionAttribute>
<loadBalanceStatus>LOAD_BALANCE_NOT_CONFIGURED</loadBalanceStatus>
<loadBalanceStrategy>DO_NOT_LOAD_BALANCE</loadBalanceStrategy>
<name></name>
<selectedRelationships>success</selectedRelationships>
<source>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>c9cb38c4-95dd-35a1-0000-000000000000</id>
<type>PROCESSOR</type>
<versionedComponentId>c9cb38c4-95dd-35a1-8f17-4779ac3bb0cf</versionedComponentId>
</source>
<zIndex>0</zIndex>
</connections>
<connections>
<id>b223e632-2be6-3b55-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<versionedComponentId>b223e632-2be6-3b55-8e70-0da7e8a2cf28</versionedComponentId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>29beb3d5-b88e-32df-0000-000000000000</id>
<type>PROCESSOR</type>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<loadBalanceCompression>DO_NOT_COMPRESS</loadBalanceCompression>
<loadBalancePartitionAttribute></loadBalancePartitionAttribute>
<loadBalanceStatus>LOAD_BALANCE_NOT_CONFIGURED</loadBalanceStatus>
<loadBalanceStrategy>DO_NOT_LOAD_BALANCE</loadBalanceStrategy>
<name></name>
<selectedRelationships>success</selectedRelationships>
<source>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>e6e49310-06e6-3642-0000-000000000000</id>
<type>PROCESSOR</type>
<versionedComponentId>e6e49310-06e6-3642-87e1-78f5d613f631</versionedComponentId>
</source>
<zIndex>0</zIndex>
</connections>
<connections>
<id>e9bda431-226f-3f7f-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
<backPressureObjectThreshold>10000</backPressureObjectThreshold>
<destination>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>c9cb38c4-95dd-35a1-0000-000000000000</id>
<type>PROCESSOR</type>
<versionedComponentId>c9cb38c4-95dd-35a1-8f17-4779ac3bb0cf</versionedComponentId>
</destination>
<flowFileExpiration>0 sec</flowFileExpiration>
<labelIndex>1</labelIndex>
<loadBalanceCompression>DO_NOT_COMPRESS</loadBalanceCompression>
<loadBalancePartitionAttribute></loadBalancePartitionAttribute>
<loadBalanceStatus>LOAD_BALANCE_NOT_CONFIGURED</loadBalanceStatus>
<loadBalanceStrategy>DO_NOT_LOAD_BALANCE</loadBalanceStrategy>
<name></name>
<selectedRelationships>splits</selectedRelationships>
<source>
<groupId>7ba66444-6b1d-3473-0000-000000000000</groupId>
<id>29beb3d5-b88e-32df-0000-000000000000</id>
<type>PROCESSOR</type>
</source>
<zIndex>0</zIndex>
</connections>
<controllerServices>
<id>0b91e500-a5a4-3561-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<versionedComponentId>bb40b6e1-30fb-3327-972f-507afbc081ce</versionedComponentId>
<bundle>
<artifact>nifi-registry-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<comments></comments>
<descriptors>
<entry>
<key>avro-reg-validated-field-names</key>
<value>
<name>avro-reg-validated-field-names</name>
</value>
</entry>
<entry>
<key>customer</key>
<value>
<name>customer</name>
</value>
</entry>
</descriptors>
<name>customer-AvroSchemaRegistry</name>
<persistsState>false</persistsState>
<properties>
<entry>
<key>avro-reg-validated-field-names</key>
<value>true</value>
</entry>
<entry>
<key>customer</key>
<value>{ "type":"record",
"name":"customer",
"namespace":"nifi",
"fields":[
{"name":"customer_id","type":["null","int"]},
{"name":"store_id","type":["null","int"]},
{"name":"first_name","type":["null","string"]},
{"name":"last_name","type":["null","string"]},
{"name":"email","type":["null","string"]},
{"name":"address_id","type":["null","int"]},
{"name":"activebool","type":["null","boolean"]},
{"name":"create_date","type":["null","string"]},
{"name":"last_update","type":["null","string"], "logicalType":"timestamp-millis"},
{"name":"active","type":["null","int"]}]}</value>
</entry>
</properties>
<state>ENABLED</state>
<type>org.apache.nifi.schemaregistry.services.AvroSchemaRegistry</type>
</controllerServices>
<controllerServices>
<id>23b93123-18d3-3c4b-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<versionedComponentId>a17a8d5b-3caa-324e-95d4-a2f5a0981073</versionedComponentId>
<bundle>
<artifact>nifi-record-serialization-services-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<comments></comments>
<descriptors>
<entry>
<key>schema-access-strategy</key>
<value>
<name>schema-access-strategy</name>
</value>
</entry>
<entry>
<key>schema-registry</key>
<value>
<identifiesControllerService>org.apache.nifi.schemaregistry.services.SchemaRegistry</identifiesControllerService>
<name>schema-registry</name>
</value>
</entry>
<entry>
<key>schema-name</key>
<value>
<name>schema-name</name>
</value>
</entry>
<entry>
<key>schema-version</key>
<value>
<name>schema-version</name>
</value>
</entry>
<entry>
<key>schema-branch</key>
<value>
<name>schema-branch</name>
</value>
</entry>
<entry>
<key>schema-text</key>
<value>
<name>schema-text</name>
</value>
</entry>
<entry>
<key>csv-reader-csv-parser</key>
<value>
<name>csv-reader-csv-parser</name>
</value>
</entry>
<entry>
<key>Date Format</key>
<value>
<name>Date Format</name>
</value>
</entry>
<entry>
<key>Time Format</key>
<value>
<name>Time Format</name>
</value>
</entry>
<entry>
<key>Timestamp Format</key>
<value>
<name>Timestamp Format</name>
</value>
</entry>
<entry>
<key>CSV Format</key>
<value>
<name>CSV Format</name>
</value>
</entry>
<entry>
<key>Value Separator</key>
<value>
<name>Value Separator</name>
</value>
</entry>
<entry>
<key>Skip Header Line</key>
<value>
<name>Skip Header Line</name>
</value>
</entry>
<entry>
<key>ignore-csv-header</key>
<value>
<name>ignore-csv-header</name>
</value>
</entry>
<entry>
<key>Quote Character</key>
<value>
<name>Quote Character</name>
</value>
</entry>
<entry>
<key>Escape Character</key>
<value>
<name>Escape Character</name>
</value>
</entry>
<entry>
<key>Comment Marker</key>
<value>
<name>Comment Marker</name>
</value>
</entry>
<entry>
<key>Null String</key>
<value>
<name>Null String</name>
</value>
</entry>
<entry>
<key>Trim Fields</key>
<value>
<name>Trim Fields</name>
</value>
</entry>
<entry>
<key>csvutils-character-set</key>
<value>
<name>csvutils-character-set</name>
</value>
</entry>
</descriptors>
<name>customer-CSVReader</name>
<persistsState>false</persistsState>
<properties>
<entry>
<key>schema-access-strategy</key>
<value>schema-name</value>
</entry>
<entry>
<key>schema-registry</key>
<value>0b91e500-a5a4-3561-0000-000000000000</value>
</entry>
<entry>
<key>schema-name</key>
<value>${schema.name}</value>
</entry>
<entry>
<key>schema-version</key>
</entry>
<entry>
<key>schema-branch</key>
</entry>
<entry>
<key>schema-text</key>
<value>${avro.schema}</value>
</entry>
<entry>
<key>csv-reader-csv-parser</key>
<value>commons-csv</value>
</entry>
<entry>
<key>Date Format</key>
</entry>
<entry>
<key>Time Format</key>
</entry>
<entry>
<key>Timestamp Format</key>
</entry>
<entry>
<key>CSV Format</key>
<value>custom</value>
</entry>
<entry>
<key>Value Separator</key>
<value>,</value>
</entry>
<entry>
<key>Skip Header Line</key>
<value>true</value>
</entry>
<entry>
<key>ignore-csv-header</key>
<value>false</value>
</entry>
<entry>
<key>Quote Character</key>
<value>"</value>
</entry>
<entry>
<key>Escape Character</key>
<value>\</value>
</entry>
<entry>
<key>Comment Marker</key>
</entry>
<entry>
<key>Null String</key>
</entry>
<entry>
<key>Trim Fields</key>
<value>true</value>
</entry>
<entry>
<key>csvutils-character-set</key>
<value>UTF-8</value>
</entry>
</properties>
<state>ENABLED</state>
<type>org.apache.nifi.csv.CSVReader</type>
</controllerServices>
<controllerServices>
<id>bb04676f-6936-3eaa-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<versionedComponentId>56dc4974-9651-3216-ab2a-e676e24dd703</versionedComponentId>
<bundle>
<artifact>nifi-dbcp-service-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<comments></comments>
<descriptors>
<entry>
<key>Database Connection URL</key>
<value>
<name>Database Connection URL</name>
</value>
</entry>
<entry>
<key>Database Driver Class Name</key>
<value>
<name>Database Driver Class Name</name>
</value>
</entry>
<entry>
<key>database-driver-locations</key>
<value>
<name>database-driver-locations</name>
</value>
</entry>
<entry>
<key>kerberos-credentials-service</key>
<value>
<identifiesControllerService>org.apache.nifi.kerberos.KerberosCredentialsService</identifiesControllerService>
<name>kerberos-credentials-service</name>
</value>
</entry>
<entry>
<key>Database User</key>
<value>
<name>Database User</name>
</value>
</entry>
<entry>
<key>Password</key>
<value>
<name>Password</name>
</value>
</entry>
<entry>
<key>Max Wait Time</key>
<value>
<name>Max Wait Time</name>
</value>
</entry>
<entry>
<key>Max Total Connections</key>
<value>
<name>Max Total Connections</name>
</value>
</entry>
<entry>
<key>Validation-query</key>
<value>
<name>Validation-query</name>
</value>
</entry>
<entry>
<key>dbcp-min-idle-conns</key>
<value>
<name>dbcp-min-idle-conns</name>
</value>
</entry>
<entry>
<key>dbcp-max-idle-conns</key>
<value>
<name>dbcp-max-idle-conns</name>
</value>
</entry>
<entry>
<key>dbcp-max-conn-lifetime</key>
<value>
<name>dbcp-max-conn-lifetime</name>
</value>
</entry>
<entry>
<key>dbcp-time-between-eviction-runs</key>
<value>
<name>dbcp-time-between-eviction-runs</name>
</value>
</entry>
<entry>
<key>dbcp-min-evictable-idle-time</key>
<value>
<name>dbcp-min-evictable-idle-time</name>
</value>
</entry>
<entry>
<key>dbcp-soft-min-evictable-idle-time</key>
<value>
<name>dbcp-soft-min-evictable-idle-time</name>
</value>
</entry>
</descriptors>
<name>thuyledb-postgres-DBCPConnectionPool</name>
<persistsState>false</persistsState>
<properties>
<entry>
<key>Database Connection URL</key>
<value>jdbc:postgresql://postgresdb.ckdsaqpaaule.us-east-1.rds.amazonaws.com:5432/thuyle_dvdrental</value>
</entry>
<entry>
<key>Database Driver Class Name</key>
<value>org.postgresql.Driver</value>
</entry>
<entry>
<key>database-driver-locations</key>
<value>/home/nifi/jdbc-drivers/postgresql-42.2.6.jar</value>
</entry>
<entry>
<key>kerberos-credentials-service</key>
</entry>
<entry>
<key>Database User</key>
<value>postgresadmin</value>
</entry>
<entry>
<key>Password</key>
</entry>
<entry>
<key>Max Wait Time</key>
<value>500 millis</value>
</entry>
<entry>
<key>Max Total Connections</key>
<value>8</value>
</entry>
<entry>
<key>Validation-query</key>
</entry>
<entry>
<key>dbcp-min-idle-conns</key>
<value>0</value>
</entry>
<entry>
<key>dbcp-max-idle-conns</key>
<value>8</value>
</entry>
<entry>
<key>dbcp-max-conn-lifetime</key>
<value>-1</value>
</entry>
<entry>
<key>dbcp-time-between-eviction-runs</key>
<value>-1</value>
</entry>
<entry>
<key>dbcp-min-evictable-idle-time</key>
<value>30 mins</value>
</entry>
<entry>
<key>dbcp-soft-min-evictable-idle-time</key>
<value>-1</value>
</entry>
</properties>
<state>ENABLED</state>
<type>org.apache.nifi.dbcp.DBCPConnectionPool</type>
</controllerServices>
<processors>
<id>05136c6f-c4b4-3e66-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<position>
<x>8.0</x>
<y>584.0</y>
</position>
<versionedComponentId>05136c6f-c4b4-3e66-b29c-c4da38c83eed</versionedComponentId>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>put-db-record-record-reader</key>
<value>
<identifiesControllerService>org.apache.nifi.serialization.RecordReaderFactory</identifiesControllerService>
<name>put-db-record-record-reader</name>
</value>
</entry>
<entry>
<key>put-db-record-statement-type</key>
<value>
<name>put-db-record-statement-type</name>
</value>
</entry>
<entry>
<key>put-db-record-dcbp-service</key>
<value>
<identifiesControllerService>org.apache.nifi.dbcp.DBCPService</identifiesControllerService>
<name>put-db-record-dcbp-service</name>
</value>
</entry>
<entry>
<key>put-db-record-catalog-name</key>
<value>
<name>put-db-record-catalog-name</name>
</value>
</entry>
<entry>
<key>put-db-record-schema-name</key>
<value>
<name>put-db-record-schema-name</name>
</value>
</entry>
<entry>
<key>put-db-record-table-name</key>
<value>
<name>put-db-record-table-name</name>
</value>
</entry>
<entry>
<key>put-db-record-translate-field-names</key>
<value>
<name>put-db-record-translate-field-names</name>
</value>
</entry>
<entry>
<key>put-db-record-unmatched-field-behavior</key>
<value>
<name>put-db-record-unmatched-field-behavior</name>
</value>
</entry>
<entry>
<key>put-db-record-unmatched-column-behavior</key>
<value>
<name>put-db-record-unmatched-column-behavior</name>
</value>
</entry>
<entry>
<key>put-db-record-update-keys</key>
<value>
<name>put-db-record-update-keys</name>
</value>
</entry>
<entry>
<key>put-db-record-field-containing-sql</key>
<value>
<name>put-db-record-field-containing-sql</name>
</value>
</entry>
<entry>
<key>put-db-record-allow-multiple-statements</key>
<value>
<name>put-db-record-allow-multiple-statements</name>
</value>
</entry>
<entry>
<key>put-db-record-quoted-identifiers</key>
<value>
<name>put-db-record-quoted-identifiers</name>
</value>
</entry>
<entry>
<key>put-db-record-quoted-table-identifiers</key>
<value>
<name>put-db-record-quoted-table-identifiers</name>
</value>
</entry>
<entry>
<key>put-db-record-query-timeout</key>
<value>
<name>put-db-record-query-timeout</name>
</value>
</entry>
<entry>
<key>rollback-on-failure</key>
<value>
<name>rollback-on-failure</name>
</value>
</entry>
<entry>
<key>table-schema-cache-size</key>
<value>
<name>table-schema-cache-size</name>
</value>
</entry>
<entry>
<key>put-db-record-max-batch-size</key>
<value>
<name>put-db-record-max-batch-size</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>put-db-record-record-reader</key>
<value>23b93123-18d3-3c4b-0000-000000000000</value>
</entry>
<entry>
<key>put-db-record-statement-type</key>
<value>INSERT</value>
</entry>
<entry>
<key>put-db-record-dcbp-service</key>
<value>bb04676f-6936-3eaa-0000-000000000000</value>
</entry>
<entry>
<key>put-db-record-catalog-name</key>
</entry>
<entry>
<key>put-db-record-schema-name</key>
</entry>
<entry>
<key>put-db-record-table-name</key>
<value>customer</value>
</entry>
<entry>
<key>put-db-record-translate-field-names</key>
<value>true</value>
</entry>
<entry>
<key>put-db-record-unmatched-field-behavior</key>
<value>Ignore Unmatched Fields</value>
</entry>
<entry>
<key>put-db-record-unmatched-column-behavior</key>
<value>Fail on Unmatched Columns</value>
</entry>
<entry>
<key>put-db-record-update-keys</key>
</entry>
<entry>
<key>put-db-record-field-containing-sql</key>
</entry>
<entry>
<key>put-db-record-allow-multiple-statements</key>
<value>false</value>
</entry>
<entry>
<key>put-db-record-quoted-identifiers</key>
<value>false</value>
</entry>
<entry>
<key>put-db-record-quoted-table-identifiers</key>
<value>false</value>
</entry>
<entry>
<key>put-db-record-query-timeout</key>
<value>0 seconds</value>
</entry>
<entry>
<key>rollback-on-failure</key>
<value>false</value>
</entry>
<entry>
<key>table-schema-cache-size</key>
<value>100</value>
</entry>
<entry>
<key>put-db-record-max-batch-size</key>
<value>0</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>PutDatabaseRecord</name>
<relationships>
<autoTerminate>false</autoTerminate>
<name>failure</name>
</relationships>
<relationships>
<autoTerminate>true</autoTerminate>
<name>retry</name>
</relationships>
<relationships>
<autoTerminate>false</autoTerminate>
<name>success</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.standard.PutDatabaseRecord</type>
</processors>
<processors>
<id>29beb3d5-b88e-32df-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<position>
<x>0.0</x>
<y>192.0</y>
</position>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>Line Split Count</key>
<value>
<name>Line Split Count</name>
</value>
</entry>
<entry>
<key>Maximum Fragment Size</key>
<value>
<name>Maximum Fragment Size</name>
</value>
</entry>
<entry>
<key>Header Line Count</key>
<value>
<name>Header Line Count</name>
</value>
</entry>
<entry>
<key>Header Line Marker Characters</key>
<value>
<name>Header Line Marker Characters</name>
</value>
</entry>
<entry>
<key>Remove Trailing Newlines</key>
<value>
<name>Remove Trailing Newlines</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>Line Split Count</key>
<value>1</value>
</entry>
<entry>
<key>Maximum Fragment Size</key>
</entry>
<entry>
<key>Header Line Count</key>
<value>1</value>
</entry>
<entry>
<key>Header Line Marker Characters</key>
</entry>
<entry>
<key>Remove Trailing Newlines</key>
<value>true</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>SplitText</name>
<relationships>
<autoTerminate>false</autoTerminate>
<name>failure</name>
</relationships>
<relationships>
<autoTerminate>true</autoTerminate>
<name>original</name>
</relationships>
<relationships>
<autoTerminate>false</autoTerminate>
<name>splits</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.standard.SplitText</type>
</processors>
<processors>
<id>2b305558-f05a-3254-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<position>
<x>8.0</x>
<y>792.0</y>
</position>
<versionedComponentId>2b305558-f05a-3254-a069-72ac003eac04</versionedComponentId>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>Log Level</key>
<value>
<name>Log Level</name>
</value>
</entry>
<entry>
<key>Log Payload</key>
<value>
<name>Log Payload</name>
</value>
</entry>
<entry>
<key>Attributes to Log</key>
<value>
<name>Attributes to Log</name>
</value>
</entry>
<entry>
<key>attributes-to-log-regex</key>
<value>
<name>attributes-to-log-regex</name>
</value>
</entry>
<entry>
<key>Attributes to Ignore</key>
<value>
<name>Attributes to Ignore</name>
</value>
</entry>
<entry>
<key>attributes-to-ignore-regex</key>
<value>
<name>attributes-to-ignore-regex</name>
</value>
</entry>
<entry>
<key>Log prefix</key>
<value>
<name>Log prefix</name>
</value>
</entry>
<entry>
<key>character-set</key>
<value>
<name>character-set</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>Log Level</key>
<value>info</value>
</entry>
<entry>
<key>Log Payload</key>
<value>false</value>
</entry>
<entry>
<key>Attributes to Log</key>
</entry>
<entry>
<key>attributes-to-log-regex</key>
<value>.*</value>
</entry>
<entry>
<key>Attributes to Ignore</key>
</entry>
<entry>
<key>attributes-to-ignore-regex</key>
</entry>
<entry>
<key>Log prefix</key>
</entry>
<entry>
<key>character-set</key>
<value>UTF-8</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>Success-Log</name>
<relationships>
<autoTerminate>true</autoTerminate>
<name>success</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.standard.LogAttribute</type>
</processors>
<processors>
<id>7c677559-6b04-31fb-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<position>
<x>608.0</x>
<y>584.0</y>
</position>
<versionedComponentId>7c677559-6b04-31fb-b3f0-f177624fd063</versionedComponentId>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>Log Level</key>
<value>
<name>Log Level</name>
</value>
</entry>
<entry>
<key>Log Payload</key>
<value>
<name>Log Payload</name>
</value>
</entry>
<entry>
<key>Attributes to Log</key>
<value>
<name>Attributes to Log</name>
</value>
</entry>
<entry>
<key>attributes-to-log-regex</key>
<value>
<name>attributes-to-log-regex</name>
</value>
</entry>
<entry>
<key>Attributes to Ignore</key>
<value>
<name>Attributes to Ignore</name>
</value>
</entry>
<entry>
<key>attributes-to-ignore-regex</key>
<value>
<name>attributes-to-ignore-regex</name>
</value>
</entry>
<entry>
<key>Log prefix</key>
<value>
<name>Log prefix</name>
</value>
</entry>
<entry>
<key>character-set</key>
<value>
<name>character-set</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>Log Level</key>
<value>info</value>
</entry>
<entry>
<key>Log Payload</key>
<value>false</value>
</entry>
<entry>
<key>Attributes to Log</key>
</entry>
<entry>
<key>attributes-to-log-regex</key>
<value>.*</value>
</entry>
<entry>
<key>Attributes to Ignore</key>
</entry>
<entry>
<key>attributes-to-ignore-regex</key>
</entry>
<entry>
<key>Log prefix</key>
</entry>
<entry>
<key>character-set</key>
<value>UTF-8</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>Failure-Log</name>
<relationships>
<autoTerminate>true</autoTerminate>
<name>success</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.standard.LogAttribute</type>
</processors>
<processors>
<id>c9cb38c4-95dd-35a1-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<position>
<x>8.0</x>
<y>376.0</y>
</position>
<versionedComponentId>c9cb38c4-95dd-35a1-8f17-4779ac3bb0cf</versionedComponentId>
<bundle>
<artifact>nifi-update-attribute-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>Delete Attributes Expression</key>
<value>
<name>Delete Attributes Expression</name>
</value>
</entry>
<entry>
<key>Store State</key>
<value>
<name>Store State</name>
</value>
</entry>
<entry>
<key>Stateful Variables Initial Value</key>
<value>
<name>Stateful Variables Initial Value</name>
</value>
</entry>
<entry>
<key>canonical-value-lookup-cache-size</key>
<value>
<name>canonical-value-lookup-cache-size</name>
</value>
</entry>
<entry>
<key>schema.name</key>
<value>
<name>schema.name</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>Delete Attributes Expression</key>
</entry>
<entry>
<key>Store State</key>
<value>Do not store state</value>
</entry>
<entry>
<key>Stateful Variables Initial Value</key>
</entry>
<entry>
<key>canonical-value-lookup-cache-size</key>
<value>100</value>
</entry>
<entry>
<key>schema.name</key>
<value>customer</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>UpdateAttribute</name>
<relationships>
<autoTerminate>false</autoTerminate>
<name>success</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.attributes.UpdateAttribute</type>
</processors>
<processors>
<id>d7a85def-1b91-3a09-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<position>
<x>1160.0</x>
<y>856.0</y>
</position>
<versionedComponentId>d7a85def-1b91-3a09-9416-ccd9d7fdbcc6</versionedComponentId>
<bundle>
<artifact>nifi-scripting-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>Script Engine</key>
<value>
<name>Script Engine</name>
</value>
</entry>
<entry>
<key>Script File</key>
<value>
<name>Script File</name>
</value>
</entry>
<entry>
<key>Script Body</key>
<value>
<name>Script Body</name>
</value>
</entry>
<entry>
<key>Module Directory</key>
<value>
<name>Module Directory</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>Script Engine</key>
<value>python</value>
</entry>
<entry>
<key>Script File</key>
</entry>
<entry>
<key>Script Body</key>
<value>import json
import datetime
import java.io
from org.apache.commons.io import IOUtils
from java.nio.charset import StandardCharsets
from org.apache.nifi.processor.io import StreamCallback

class ModJSON(StreamCallback):
def __init__(self):
pass
def process(self, inputStream, outputStream):
text = IOUtils.toString(inputStream, StandardCharsets.UTF_8)
obj = json.loads(text)
rows = len(obj["columns"])
newcolumns = {}
for row in range(0, rows):
if (obj["columns"][row]["column_type"]==93):
obj["columns"][row]["value"] = datetime.datetime.strptime(obj["columns"][row]["value"], "%a %b %d %H:%M:%S %Z %Y").strftime("%Y-%m-%d %H:%M:%S.%f")

newcolumns[obj["columns"][row]["name"]] = obj["columns"][row]["value"]

outputStream.write(bytearray(json.dumps(newcolumns, indent=4).encode('utf-8')))

flowFile = session.get()
if (flowFile != None):
flowFile = session.write(flowFile, ModJSON())
session.transfer(flowFile, REL_SUCCESS)
session.commit()</value>
</entry>
<entry>
<key>Module Directory</key>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>0 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>Convert datetime -- ExecuteScript</name>
<relationships>
<autoTerminate>false</autoTerminate>
<name>failure</name>
</relationships>
<relationships>
<autoTerminate>false</autoTerminate>
<name>success</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.script.ExecuteScript</type>
</processors>
<processors>
<id>e6e49310-06e6-3642-0000-000000000000</id>
<parentGroupId>7ba66444-6b1d-3473-0000-000000000000</parentGroupId>
<position>
<x>0.0</x>
<y>0.0</y>
</position>
<versionedComponentId>e6e49310-06e6-3642-87e1-78f5d613f631</versionedComponentId>
<bundle>
<artifact>nifi-standard-nar</artifact>
<group>org.apache.nifi</group>
<version>1.11.1</version>
</bundle>
<config>
<bulletinLevel>WARN</bulletinLevel>
<comments></comments>
<concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
<descriptors>
<entry>
<key>File Size</key>
<value>
<name>File Size</name>
</value>
</entry>
<entry>
<key>Batch Size</key>
<value>
<name>Batch Size</name>
</value>
</entry>
<entry>
<key>Data Format</key>
<value>
<name>Data Format</name>
</value>
</entry>
<entry>
<key>Unique FlowFiles</key>
<value>
<name>Unique FlowFiles</name>
</value>
</entry>
<entry>
<key>generate-ff-custom-text</key>
<value>
<name>generate-ff-custom-text</name>
</value>
</entry>
<entry>
<key>character-set</key>
<value>
<name>character-set</name>
</value>
</entry>
</descriptors>
<executionNode>ALL</executionNode>
<lossTolerant>false</lossTolerant>
<penaltyDuration>30 sec</penaltyDuration>
<properties>
<entry>
<key>File Size</key>
<value>0B</value>
</entry>
<entry>
<key>Batch Size</key>
<value>1</value>
</entry>
<entry>
<key>Data Format</key>
<value>Text</value>
</entry>
<entry>
<key>Unique FlowFiles</key>
<value>false</value>
</entry>
<entry>
<key>generate-ff-custom-text</key>
<value>"customer_id","store_id","first_name","last_name","email","address_id","activebool","create_date","last_update","active"
1,1,Mary,Smith,mary.smith@sakilacustomer.org,5,true,2006-02-14,2013-05-26 14:49:45,1
2,1,Patricia,Johnson,patricia.johnson@sakilacustomer.org,6,true,2006-02-14,2013-05-26 14:49:45,1
3,1,Linda,Williams,linda.williams@sakilacustomer.org,7,true,2006-02-14,2013-05-26 14:49:45,1
4,2,Barbara,Jones,barbara.jones@sakilacustomer.org,8,true,2006-02-14,2013-05-26 14:49:45,1
5,1,Elizabeth,Brown,elizabeth.brown@sakilacustomer.org,9,true,2006-02-14,2013-05-26 14:49:45,1
6,2,Jennifer,Davis,jennifer.davis@sakilacustomer.org,10,true,2006-02-14,2013-05-26 14:49:45,1
7,1,Maria,Miller,maria.miller@sakilacustomer.org,11,true,2006-02-14,2013-05-26 14:49:45,1
8,2,Susan,Wilson,susan.wilson@sakilacustomer.org,12,true,2006-02-14,2013-05-26 14:49:45,1
9,2,Margaret,Moore,margaret.moore@sakilacustomer.org,13,true,2006-02-14,2013-05-26 14:49:45,1
10,1,Dorothy,Taylor,dorothy.taylor@sakilacustomer.org,14,true,2006-02-14,2013-05-26 14:49:45,1
11,2,Lisa,Anderson,lisa.anderson@sakilacustomer.org,15,true,2006-02-14,2013-05-26 14:49:45,1
12,1,Nancy,Thomas,nancy.thomas@sakilacustomer.org,16,true,2006-02-14,2013-05-26 14:49:45,1
13,2,Karen,Jackson,karen.jackson@sakilacustomer.org,17,true,2006-02-14,2013-05-26 14:49:45,1
14,2,Betty,White,betty.white@sakilacustomer.org,18,true,2006-02-14,2013-05-26 14:49:45,1
15,1,Helen,Harris,helen.harris@sakilacustomer.org,19,true,2006-02-14,2013-05-26 14:49:45,1
16,2,Sandra,Martin,sandra.martin@sakilacustomer.org,20,true,2006-02-14,2013-05-26 14:49:45,0
17,1,Donna,Thompson,donna.thompson@sakilacustomer.org,21,true,2006-02-14,2013-05-26 14:49:45,1
18,2,Carol,Garcia,carol.garcia@sakilacustomer.org,22,true,2006-02-14,2013-05-26 14:49:45,1
19,1,Ruth,Martinez,ruth.martinez@sakilacustomer.org,23,true,2006-02-14,2013-05-26 14:49:45,1
20,2,Sharon,Robinson,sharon.robinson@sakilacustomer.org,24,true,2006-02-14,2013-05-26 14:49:45,1
21,1,Michelle,Clark,michelle.clark@sakilacustomer.org,25,true,2006-02-14,2013-05-26 14:49:45,1
22,1,Laura,Rodriguez,laura.rodriguez@sakilacustomer.org,26,true,2006-02-14,2013-05-26 14:49:45,1</value>
</entry>
<entry>
<key>character-set</key>
<value>UTF-8</value>
</entry>
</properties>
<runDurationMillis>0</runDurationMillis>
<schedulingPeriod>100000 sec</schedulingPeriod>
<schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
<yieldDuration>1 sec</yieldDuration>
</config>
<executionNodeRestricted>false</executionNodeRestricted>
<name>customer -- GenerateFlowFile</name>
<relationships>
<autoTerminate>false</autoTerminate>
<name>success</name>
</relationships>
<state>STOPPED</state>
<style/>
<type>org.apache.nifi.processors.standard.GenerateFlowFile</type>
</processors>
</snippet>
<timestamp>02/12/2020 17:26:41 UTC</timestamp>
</template>

Re: PUTSQL which cannot be converted to a timestamp

Explorer

Any suggestions?