Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Nifi - Truncate and Load to mysql db table

Highlighted

Nifi - Truncate and Load to mysql db table

New Contributor

I am reading CSV files from an SFTP site and loading it to mysql db.

I have the below workflow which seems to be working fine. I just need some help in figuring out how to truncate the table before i start with the data load.

Nifi Flow:

ListSFTP -> FetchSFTP -> InferAvroSchema -> ConvertCSVtoAvro -> ConvertAvrotoJSON -> SplitJSON -> ConvertJSONtoSQL -> PutSQL

This flow seems to work fine, but every time i run this, i need the table to be truncated first and then start the load.

Can someone please help me with some info on how i can achieve this. Or is there a better flow than what i have written, please advise.

Thanks,
Aadil

5 REPLIES 5
Highlighted

Re: Nifi - Truncate and Load to mysql db table

Super Guru
@Aadil M

There is no predefined properties that we can specify in NiFi processor that can Truncate the table for us before loading into it.

But there are some ways we can do that using NiFi Processor.

Going through your Flow i suggest

You to remove SplitJson processor before ConvertJSONtoSQL processor because ConvertJSONtoSQL does Splitting of records i.e lets take you are having a json array having 1000 records in it, ConvertJsontoSQL processor prepares sql statements for 1000 records and gives 1000 Flowfiles individually. There is no need of using SplitJson processor before ConvertJSONtoSQL processor.

To Truncate Table use Execute SQL processor before ConvertJSONtoSQL processor.

Method1:-

Execute SQL Processor Configs:-

Change the below property to

SQL select query

truncate table <table-name>

so when you got json file fron convertavrotojson processor, executesql processor gets that file to trigger the truncate query and we need to connect Failure of executesql processor to ConvertJSONtoSQL processor.

Why failure relation we need to connect?

Because executesql processor is used to execute select sql statements and get results but in our case we are not getting results back as Flowfile.

But we are just executing a truncate statment that's not executesql processor meant to do, but it executes the truncate statement and gives an error(the statement is not resulting any thing). It actually truncates the data in the table.

So when we connects failure relation to another processor it will transfer our json file to convertjsontosql processor then your loading data will be done everytime after truncating the table.

47452-executesql.png

Flow:-

ListSFTP->FetchSFTP->InferAvroSchema->ConvertCSVtoAvro->ConvertAvrotoJSON->Executesql//executes truncate table statment and connect failure relation to next processor) ->ConvertJSONtoSQL->PutSQL

In the above method you are executing truncate statement inside your NiFi Flow it self.

(or)

Method2:-

Keep Truncating table process seperately like

Flow:-

Generate Flowfile processor //keep truncate table statement --> Putsql processor(execute the truncate table statement)

Before loading data trigger this Generateflowfile flow first(i.e method 2) then trigger your actual flow(i.e in your Question) seperately. Schedule these flows non overlapping times that can trigger first truncating the table then schedule loading data into table.

.

If the Answer helped to resolve your issue, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

Highlighted

Re: Nifi - Truncate and Load to mysql db table

New Contributor

Hi,

I'm trying to execute the SQL command "Truncate Table". All the post I've seen recommand like here using PutSQL for that.

 

I've the error message :

ERROR
PutSQL[id=a154204d-016e-1000-d9c9-d0d79519e70e] Failed to update database for
[StandardFlowFileRecord[uuid=6c92c3bc-a219-4017-9641-dc4d21ab061a,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1574662524581-1, container=default, section=1], offset=3900, length=35],offset=0,name=6c92c3bc-a219-4017-9641-dc4d21ab061a,size=35]] 
due to Query must be like 'INSERT INTO [db.]table [(c1, c2, c3)] VALUES (?, ?, ?)'. 
Got: truncate table SBST.geographicalcat; 
routing to failure: java.sql.SQLSyntaxErrorException: Query must be like 'INSERT INTO [db.]table [(c1, c2, c3)] VALUES (?, ?, ?)'. 
Got: truncate table SBST.geographicalcat

I use all default parameters from NiFi 1.10 processor.

I've try with the truncate query as constant in the SQL Statement parameter or this one empty and the SQL comming from the incoming flow file.

The database is Clickhouse, using last JDBC.

The truncate command work when in the Pre-SQL of the ExecuteSQL processor.

So NiFi and the JDBC should be able to run it.

 

The NiFi doc explain that PutSQL is only for INSERT or UPDATE.

It look like PutSQL is not allowing anything else than Insert / Update, or is there a parameter that I should change ?

Any other solution ? (I can't use ExecuteSQL as I don't want to query anything, just truncate before insert)

 

thanks

Highlighted

Re: Nifi - Truncate and Load to mysql db table

New Contributor

Hi, for the moment I've done it with two branches and a Notify/Wait.

 

From the GetFile, a first Success branch go to the Execute SQL that have a Pre-Query that do the Truncate, and a Query that do a simple select query (here a select count() of the truncated table that should return 0 and could be used as check), then that go to a Notify to inform that the truncate is done.

 

From the GetFile, a Second Success branch go to the Wait (for the truncate to be done), than move to the PutDatabaseRecord (with a UpdateAttibute before to set the schema.name).

 

 

Also here

https://stackoverflow.com/questions/59026724/nifi-1-10-putsql-error-query-must-be-like-insert/590692...

 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<template encoding-version="1.3">
    <description>Truncate Load for dimensions (slow batch frequency) with archive.
No error management implemented</description>
    <groupId>a0377c1a-016e-1000-167a-e6f1c899a66f</groupId>
    <name>Clickhouse Truncate Load</name>
    <snippet>
        <connections>
            <id>0e31c732-4e03-37c9-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
            <backPressureObjectThreshold>10000</backPressureObjectThreshold>
            <destination>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>6200e074-aa50-36e9-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>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>b8fa507a-797c-3714-0000-000000000000</id>
                <type>PROCESSOR</type>
            </source>
            <zIndex>0</zIndex>
        </connections>
        <connections>
            <id>19a865e3-39c1-3fee-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
            <backPressureObjectThreshold>10000</backPressureObjectThreshold>
            <destination>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>f4b41b47-34d9-3bb4-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>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>6200e074-aa50-36e9-0000-000000000000</id>
                <type>PROCESSOR</type>
            </source>
            <zIndex>0</zIndex>
        </connections>
        <connections>
            <id>2c1086e0-bbca-393a-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
            <backPressureObjectThreshold>10000</backPressureObjectThreshold>
            <destination>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>d741151d-7167-3026-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>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>56a6347a-9259-3a63-0000-000000000000</id>
                <type>PROCESSOR</type>
            </source>
            <zIndex>0</zIndex>
        </connections>
        <connections>
            <id>329ecc10-4aff-35e8-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
            <backPressureObjectThreshold>10000</backPressureObjectThreshold>
            <destination>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>53f7bf4b-d5d4-32c6-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>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>d741151d-7167-3026-0000-000000000000</id>
                <type>PROCESSOR</type>
            </source>
            <zIndex>0</zIndex>
        </connections>
        <connections>
            <id>872806d3-be25-34ec-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
            <backPressureObjectThreshold>10000</backPressureObjectThreshold>
            <destination>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>d24ba22b-88ad-34ae-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>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>62e39b15-77da-3fa5-0000-000000000000</id>
                <type>PROCESSOR</type>
            </source>
            <zIndex>0</zIndex>
        </connections>
        <connections>
            <id>8d81818b-b883-35f3-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
            <backPressureObjectThreshold>10000</backPressureObjectThreshold>
            <destination>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>56a6347a-9259-3a63-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>
            <prioritizers>org.apache.nifi.prioritizer.FirstInFirstOutPrioritizer</prioritizers>
            <selectedRelationships>success</selectedRelationships>
            <source>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>f1513fa5-c26a-3157-0000-000000000000</id>
                <type>PROCESSOR</type>
            </source>
            <zIndex>0</zIndex>
        </connections>
        <connections>
            <id>ead6ac6b-a621-3f65-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
            <backPressureObjectThreshold>10000</backPressureObjectThreshold>
            <destination>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>62e39b15-77da-3fa5-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>
            <prioritizers>org.apache.nifi.prioritizer.FirstInFirstOutPrioritizer</prioritizers>
            <selectedRelationships>success</selectedRelationships>
            <source>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>f1513fa5-c26a-3157-0000-000000000000</id>
                <type>PROCESSOR</type>
            </source>
            <zIndex>0</zIndex>
        </connections>
        <connections>
            <id>fccc8d95-eaed-3a31-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <backPressureDataSizeThreshold>1 GB</backPressureDataSizeThreshold>
            <backPressureObjectThreshold>10000</backPressureObjectThreshold>
            <destination>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>b8fa507a-797c-3714-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>
            <prioritizers>org.apache.nifi.prioritizer.FirstInFirstOutPrioritizer</prioritizers>
            <selectedRelationships>success</selectedRelationships>
            <source>
                <groupId>bbf1ef49-f963-37ab-0000-000000000000</groupId>
                <id>f1513fa5-c26a-3157-0000-000000000000</id>
                <type>PROCESSOR</type>
            </source>
            <zIndex>0</zIndex>
        </connections>
        <controllerServices>
            <id>517808ef-0266-3e67-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <bundle>
                <artifact>nifi-dbcp-service-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</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>DBCP_SBST</name>
            <persistsState>false</persistsState>
            <properties>
                <entry>
                    <key>Database Connection URL</key>
                    <value>jdbc:clickhouse://192.168.1.185:8123/SBST</value>
                </entry>
                <entry>
                    <key>Database Driver Class Name</key>
                    <value>ru.yandex.clickhouse.ClickHouseDriver</value>
                </entry>
                <entry>
                    <key>database-driver-locations</key>
                    <value>/home/thales/downloads/clickhouse-jdbc/clickhouse-jdbc-release_0.2/target/clickhouse-jdbc-0.2-jar-with-dependencies.jar</value>
                </entry>
                <entry>
                    <key>kerberos-credentials-service</key>
                </entry>
                <entry>
                    <key>Database User</key>
                </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>
        <controllerServices>
            <id>8d6517b8-c698-3871-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <bundle>
                <artifact>nifi-record-serialization-services-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</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>CSVReaderTild</name>
            <persistsState>false</persistsState>
            <properties>
                <entry>
                    <key>schema-access-strategy</key>
                    <value>schema-name</value>
                </entry>
                <entry>
                    <key>schema-registry</key>
                    <value>f6754d68-6957-3155-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>false</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>ccdef25c-4ad3-37ac-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <bundle>
                <artifact>nifi-distributed-cache-services-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</version>
            </bundle>
            <comments></comments>
            <descriptors>
                <entry>
                    <key>Server Hostname</key>
                    <value>
                        <name>Server Hostname</name>
                    </value>
                </entry>
                <entry>
                    <key>Server Port</key>
                    <value>
                        <name>Server Port</name>
                    </value>
                </entry>
                <entry>
                    <key>SSL Context Service</key>
                    <value>
                        <identifiesControllerService>org.apache.nifi.ssl.SSLContextService</identifiesControllerService>
                        <name>SSL Context Service</name>
                    </value>
                </entry>
                <entry>
                    <key>Communications Timeout</key>
                    <value>
                        <name>Communications Timeout</name>
                    </value>
                </entry>
            </descriptors>
            <name>DistributedMapCacheClientService</name>
            <persistsState>false</persistsState>
            <properties>
                <entry>
                    <key>Server Hostname</key>
                    <value>192.168.1.190</value>
                </entry>
                <entry>
                    <key>Server Port</key>
                    <value>4557</value>
                </entry>
                <entry>
                    <key>SSL Context Service</key>
                </entry>
                <entry>
                    <key>Communications Timeout</key>
                    <value>30 secs</value>
                </entry>
            </properties>
            <state>ENABLED</state>
            <type>org.apache.nifi.distributed.cache.client.DistributedMapCacheClientService</type>
        </controllerServices>
        <controllerServices>
            <id>f6754d68-6957-3155-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <bundle>
                <artifact>nifi-registry-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</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>functionalcat</key>
                    <value>
                        <name>functionalcat</name>
                    </value>
                </entry>
                <entry>
                    <key>geographicalcat</key>
                    <value>
                        <name>geographicalcat</name>
                    </value>
                </entry>
                <entry>
                    <key>hisevent</key>
                    <value>
                        <name>hisevent</name>
                    </value>
                </entry>
                <entry>
                    <key>hiseventorigin</key>
                    <value>
                        <name>hiseventorigin</name>
                    </value>
                </entry>
            </descriptors>
            <name>AvroSchemaRegistry</name>
            <persistsState>false</persistsState>
            <properties>
                <entry>
                    <key>avro-reg-validated-field-names</key>
                    <value>true</value>
                </entry>
                <entry>
                    <key>functionalcat</key>
                    <value>{
"type": "record",
"name": "functionalcat",
"fields": [
   { "name": "FUNCTIONALCAT", "type": "int" },
   { "name": "FUNCTIONALDESC", "type": "string" }
  ]
}</value>
                </entry>
                <entry>
                    <key>geographicalcat</key>
                    <value>{
"type": "record",
"name": "geographicalcat",
"fields": [
   { "name": "GEOGRAPHICALCAT", "type": "int" },
   { "name": "GEOGRAPHICALCODE", "type": "string" },
   { "name": "GEOGRAPHICALDESC", "type": "string" }
  ]
}</value>
                </entry>
                <entry>
                    <key>hisevent</key>
                    <value>{
"type": "record",
"name": "hisevent",
"fields": [
  { "name": "ALARMID", "type": ["null", "long"] },
  { "name": "ENVIRONMENT", "type": ["null", "string"] },
  { "name": "VALUE", "type": "long" },
  { "name": "ACKREQUIRED", "type": "long" },
  { "name": "SEVERITY", "type": "long" },
  { "name": "EQUIPMENTCLASS", "type": ["null", "long"] },
  { "name": "FUNCTIONALCAT", "type": "long" },
  { "name": "GEOGRAPHICALCAT", "type": "long" },
  { "name": "DATEANDTIME", "type": "string" },
  { "name": "EQUIPMENTNAME", "type": "string" },
  { "name": "ASSETNAME", "type": "string" },
  { "name": "MESSAGE", "type": ["null", "string"] },
  { "name": "STATUS", "type": ["null", "string"] },
  { "name": "GROUP1", "type": ["null", "string"] },
  { "name": "GROUP2", "type": ["null", "string"] },
  { "name": "FORMAT", "type": ["null", "string"] },
  { "name": "DSSEVENTTYPE", "type": ["null", "string"] },
  { "name": "OPER", "type": ["null", "string"] }
	]
}</value>
                </entry>
                <entry>
                    <key>hiseventorigin</key>
                    <value>{
"type": "record",
"name": "hisevent",
"fields": [
  { "name": "ALARMID", "type": ["null", "long"] },
  { "name": "ENVIRONMENT", "type": ["null", "string"] },
  { "name": "VALUE", "type": "long" },
  { "name": "ACKREQUIRED", "type": "long" },
  { "name": "SEVERITY", "type": "long" },
  { "name": "EQUIPMENTCLASS", "type": ["null", "long"] },
  { "name": "FUNCTIONALCAT", "type": "long" },
  { "name": "GEOGRAPHICALCAT", "type": "long" },
  { "name": "DATEANDTIME", "type": "string" },
  { "name": "EQUIPMENTNAME", "type": "string" },
  { "name": "ASSETNAME", "type": "string" },
  { "name": "MESSAGE", "type": ["null", "string"] },
  { "name": "STATUS", "type": ["null", "string"] },
  { "name": "GROUP1", "type": ["null", "string"] },
  { "name": "GROUP2", "type": ["null", "string"] },
  { "name": "FORMAT", "type": ["null", "string"] },
  { "name": "DSSEVENTTYPE", "type": ["null", "string"] },
  { "name": "OPER", "type": ["null", "string"] },
  { "name": "EMPTY", "type": ["null", "string"] }
	]
}</value>
                </entry>
            </properties>
            <state>ENABLED</state>
            <type>org.apache.nifi.schemaregistry.services.AvroSchemaRegistry</type>
        </controllerServices>
        <labels>
            <id>7bbcc1a5-298e-31f1-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>1040.0</x>
                <y>616.0</y>
            </position>
            <height>184.0</height>
            <label>Main Folder Level : Avro Registry, add Schema that describe the CSV

Variable RootFolder : File Location root

GetFile : input Directory based on RootFolder and File Name
ExecuteSQL : Truncate table and count queries
NotiFy and Wait : Name of the Identifier
Update Attribute : Schema Name
PutDatabaseRecord : Create a Record Reader controller CSVReader at this process group level,
     just changing the CSV format, and using the registry of the root process group,
     change the table name, the 2 Unmatched parmaters to Ignore</label>
            <style>
                <entry>
                    <key>font-size</key>
                    <value>14px</value>
                </entry>
            </style>
            <width>688.0</width>
        </labels>
        <processors>
            <id>53f7bf4b-d5d4-32c6-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>624.0</x>
                <y>680.0</y>
            </position>
            <bundle>
                <artifact>nifi-standard-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</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>8d6517b8-c698-3871-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>517808ef-0266-3e67-0000-000000000000</value>
                    </entry>
                    <entry>
                        <key>put-db-record-catalog-name</key>
                    </entry>
                    <entry>
                        <key>put-db-record-schema-name</key>
                        <value>SBST</value>
                    </entry>
                    <entry>
                        <key>put-db-record-table-name</key>
                        <value>geographicalcat</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>Ignore 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>geographicalcat_PutDatabaseRecord</name>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>failure</name>
            </relationships>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>retry</name>
            </relationships>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>success</name>
            </relationships>
            <state>STOPPED</state>
            <style/>
            <type>org.apache.nifi.processors.standard.PutDatabaseRecord</type>
        </processors>
        <processors>
            <id>56a6347a-9259-3a63-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>624.0</x>
                <y>248.0</y>
            </position>
            <bundle>
                <artifact>nifi-standard-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</version>
            </bundle>
            <config>
                <bulletinLevel>WARN</bulletinLevel>
                <comments></comments>
                <concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
                <descriptors>
                    <entry>
                        <key>release-signal-id</key>
                        <value>
                            <name>release-signal-id</name>
                        </value>
                    </entry>
                    <entry>
                        <key>target-signal-count</key>
                        <value>
                            <name>target-signal-count</name>
                        </value>
                    </entry>
                    <entry>
                        <key>signal-counter-name</key>
                        <value>
                            <name>signal-counter-name</name>
                        </value>
                    </entry>
                    <entry>
                        <key>wait-buffer-count</key>
                        <value>
                            <name>wait-buffer-count</name>
                        </value>
                    </entry>
                    <entry>
                        <key>releasable-flowfile-count</key>
                        <value>
                            <name>releasable-flowfile-count</name>
                        </value>
                    </entry>
                    <entry>
                        <key>expiration-duration</key>
                        <value>
                            <name>expiration-duration</name>
                        </value>
                    </entry>
                    <entry>
                        <key>distributed-cache-service</key>
                        <value>
                            <identifiesControllerService>org.apache.nifi.distributed.cache.client.AtomicDistributedMapCacheClient</identifiesControllerService>
                            <name>distributed-cache-service</name>
                        </value>
                    </entry>
                    <entry>
                        <key>attribute-copy-mode</key>
                        <value>
                            <name>attribute-copy-mode</name>
                        </value>
                    </entry>
                    <entry>
                        <key>wait-mode</key>
                        <value>
                            <name>wait-mode</name>
                        </value>
                    </entry>
                    <entry>
                        <key>wait-penalty-duration</key>
                        <value>
                            <name>wait-penalty-duration</name>
                        </value>
                    </entry>
                </descriptors>
                <executionNode>ALL</executionNode>
                <lossTolerant>false</lossTolerant>
                <penaltyDuration>30 sec</penaltyDuration>
                <properties>
                    <entry>
                        <key>release-signal-id</key>
                        <value>NotifyGeographicalcatTruncated</value>
                    </entry>
                    <entry>
                        <key>target-signal-count</key>
                        <value>1</value>
                    </entry>
                    <entry>
                        <key>signal-counter-name</key>
                    </entry>
                    <entry>
                        <key>wait-buffer-count</key>
                        <value>1</value>
                    </entry>
                    <entry>
                        <key>releasable-flowfile-count</key>
                        <value>1</value>
                    </entry>
                    <entry>
                        <key>expiration-duration</key>
                        <value>10 min</value>
                    </entry>
                    <entry>
                        <key>distributed-cache-service</key>
                        <value>ccdef25c-4ad3-37ac-0000-000000000000</value>
                    </entry>
                    <entry>
                        <key>attribute-copy-mode</key>
                        <value>keeporiginal</value>
                    </entry>
                    <entry>
                        <key>wait-mode</key>
                        <value>keep</value>
                    </entry>
                    <entry>
                        <key>wait-penalty-duration</key>
                    </entry>
                </properties>
                <runDurationMillis>0</runDurationMillis>
                <schedulingPeriod>0 sec</schedulingPeriod>
                <schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
                <yieldDuration>1 sec</yieldDuration>
            </config>
            <executionNodeRestricted>false</executionNodeRestricted>
            <name>geographicalcat_WaitTruncate</name>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>expired</name>
            </relationships>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>failure</name>
            </relationships>
            <relationships>
                <autoTerminate>false</autoTerminate>
                <name>success</name>
            </relationships>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>wait</name>
            </relationships>
            <state>STOPPED</state>
            <style/>
            <type>org.apache.nifi.processors.standard.Wait</type>
        </processors>
        <processors>
            <id>6200e074-aa50-36e9-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>0.0</x>
                <y>208.0</y>
            </position>
            <bundle>
                <artifact>nifi-standard-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</version>
            </bundle>
            <config>
                <bulletinLevel>WARN</bulletinLevel>
                <comments></comments>
                <concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
                <descriptors>
                    <entry>
                        <key>Mode</key>
                        <value>
                            <name>Mode</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Compression Format</key>
                        <value>
                            <name>Compression Format</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Compression Level</key>
                        <value>
                            <name>Compression Level</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Update Filename</key>
                        <value>
                            <name>Update Filename</name>
                        </value>
                    </entry>
                </descriptors>
                <executionNode>ALL</executionNode>
                <lossTolerant>false</lossTolerant>
                <penaltyDuration>30 sec</penaltyDuration>
                <properties>
                    <entry>
                        <key>Mode</key>
                        <value>compress</value>
                    </entry>
                    <entry>
                        <key>Compression Format</key>
                        <value>gzip</value>
                    </entry>
                    <entry>
                        <key>Compression Level</key>
                        <value>9</value>
                    </entry>
                    <entry>
                        <key>Update Filename</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>CompressContent</name>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>failure</name>
            </relationships>
            <relationships>
                <autoTerminate>false</autoTerminate>
                <name>success</name>
            </relationships>
            <state>STOPPED</state>
            <style/>
            <type>org.apache.nifi.processors.standard.CompressContent</type>
        </processors>
        <processors>
            <id>62e39b15-77da-3fa5-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>1264.0</x>
                <y>40.0</y>
            </position>
            <bundle>
                <artifact>nifi-standard-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</version>
            </bundle>
            <config>
                <bulletinLevel>WARN</bulletinLevel>
                <comments></comments>
                <concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
                <descriptors>
                    <entry>
                        <key>Database Connection Pooling Service</key>
                        <value>
                            <identifiesControllerService>org.apache.nifi.dbcp.DBCPService</identifiesControllerService>
                            <name>Database Connection Pooling Service</name>
                        </value>
                    </entry>
                    <entry>
                        <key>sql-pre-query</key>
                        <value>
                            <name>sql-pre-query</name>
                        </value>
                    </entry>
                    <entry>
                        <key>SQL select query</key>
                        <value>
                            <name>SQL select query</name>
                        </value>
                    </entry>
                    <entry>
                        <key>sql-post-query</key>
                        <value>
                            <name>sql-post-query</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Max Wait Time</key>
                        <value>
                            <name>Max Wait Time</name>
                        </value>
                    </entry>
                    <entry>
                        <key>dbf-normalize</key>
                        <value>
                            <name>dbf-normalize</name>
                        </value>
                    </entry>
                    <entry>
                        <key>dbf-user-logical-types</key>
                        <value>
                            <name>dbf-user-logical-types</name>
                        </value>
                    </entry>
                    <entry>
                        <key>compression-format</key>
                        <value>
                            <name>compression-format</name>
                        </value>
                    </entry>
                    <entry>
                        <key>dbf-default-precision</key>
                        <value>
                            <name>dbf-default-precision</name>
                        </value>
                    </entry>
                    <entry>
                        <key>dbf-default-scale</key>
                        <value>
                            <name>dbf-default-scale</name>
                        </value>
                    </entry>
                    <entry>
                        <key>esql-max-rows</key>
                        <value>
                            <name>esql-max-rows</name>
                        </value>
                    </entry>
                    <entry>
                        <key>esql-output-batch-size</key>
                        <value>
                            <name>esql-output-batch-size</name>
                        </value>
                    </entry>
                </descriptors>
                <executionNode>ALL</executionNode>
                <lossTolerant>false</lossTolerant>
                <penaltyDuration>30 sec</penaltyDuration>
                <properties>
                    <entry>
                        <key>Database Connection Pooling Service</key>
                        <value>517808ef-0266-3e67-0000-000000000000</value>
                    </entry>
                    <entry>
                        <key>sql-pre-query</key>
                        <value>truncate table SBST.geographicalcat</value>
                    </entry>
                    <entry>
                        <key>SQL select query</key>
                        <value>select count(*) as cnt from SBST.geographicalcat</value>
                    </entry>
                    <entry>
                        <key>sql-post-query</key>
                    </entry>
                    <entry>
                        <key>Max Wait Time</key>
                        <value>0 seconds</value>
                    </entry>
                    <entry>
                        <key>dbf-normalize</key>
                        <value>false</value>
                    </entry>
                    <entry>
                        <key>dbf-user-logical-types</key>
                        <value>false</value>
                    </entry>
                    <entry>
                        <key>compression-format</key>
                        <value>NONE</value>
                    </entry>
                    <entry>
                        <key>dbf-default-precision</key>
                        <value>10</value>
                    </entry>
                    <entry>
                        <key>dbf-default-scale</key>
                        <value>0</value>
                    </entry>
                    <entry>
                        <key>esql-max-rows</key>
                        <value>0</value>
                    </entry>
                    <entry>
                        <key>esql-output-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>geographicalcat_ExecuteSQLTruncate</name>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>failure</name>
            </relationships>
            <relationships>
                <autoTerminate>false</autoTerminate>
                <name>success</name>
            </relationships>
            <state>STOPPED</state>
            <style/>
            <type>org.apache.nifi.processors.standard.ExecuteSQL</type>
        </processors>
        <processors>
            <id>b8fa507a-797c-3714-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>8.0</x>
                <y>0.0</y>
            </position>
            <bundle>
                <artifact>nifi-update-attribute-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</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>filename</key>
                        <value>
                            <name>filename</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>filename</key>
                        <value>${filename:prepend("_"):prepend(${now():format("yyyy-MM-dd-HH-mm-ss")})}</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>d24ba22b-88ad-34ae-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>1264.0</x>
                <y>264.0</y>
            </position>
            <bundle>
                <artifact>nifi-standard-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</version>
            </bundle>
            <config>
                <bulletinLevel>WARN</bulletinLevel>
                <comments></comments>
                <concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
                <descriptors>
                    <entry>
                        <key>release-signal-id</key>
                        <value>
                            <name>release-signal-id</name>
                        </value>
                    </entry>
                    <entry>
                        <key>signal-counter-name</key>
                        <value>
                            <name>signal-counter-name</name>
                        </value>
                    </entry>
                    <entry>
                        <key>signal-counter-delta</key>
                        <value>
                            <name>signal-counter-delta</name>
                        </value>
                    </entry>
                    <entry>
                        <key>signal-buffer-count</key>
                        <value>
                            <name>signal-buffer-count</name>
                        </value>
                    </entry>
                    <entry>
                        <key>distributed-cache-service</key>
                        <value>
                            <identifiesControllerService>org.apache.nifi.distributed.cache.client.AtomicDistributedMapCacheClient</identifiesControllerService>
                            <name>distributed-cache-service</name>
                        </value>
                    </entry>
                    <entry>
                        <key>attribute-cache-regex</key>
                        <value>
                            <name>attribute-cache-regex</name>
                        </value>
                    </entry>
                </descriptors>
                <executionNode>ALL</executionNode>
                <lossTolerant>false</lossTolerant>
                <penaltyDuration>30 sec</penaltyDuration>
                <properties>
                    <entry>
                        <key>release-signal-id</key>
                        <value>NotifyGeographicalcatTruncated</value>
                    </entry>
                    <entry>
                        <key>signal-counter-name</key>
                        <value>default</value>
                    </entry>
                    <entry>
                        <key>signal-counter-delta</key>
                        <value>1</value>
                    </entry>
                    <entry>
                        <key>signal-buffer-count</key>
                        <value>1</value>
                    </entry>
                    <entry>
                        <key>distributed-cache-service</key>
                        <value>ccdef25c-4ad3-37ac-0000-000000000000</value>
                    </entry>
                    <entry>
                        <key>attribute-cache-regex</key>
                    </entry>
                </properties>
                <runDurationMillis>0</runDurationMillis>
                <schedulingPeriod>0 sec</schedulingPeriod>
                <schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
                <yieldDuration>1 sec</yieldDuration>
            </config>
            <executionNodeRestricted>false</executionNodeRestricted>
            <name>geographicalcat_NotifyTruncated</name>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>failure</name>
            </relationships>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>success</name>
            </relationships>
            <state>STOPPED</state>
            <style/>
            <type>org.apache.nifi.processors.standard.Notify</type>
        </processors>
        <processors>
            <id>d741151d-7167-3026-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>624.0</x>
                <y>464.0</y>
            </position>
            <bundle>
                <artifact>nifi-update-attribute-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</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>geographicalcat</value>
                    </entry>
                </properties>
                <runDurationMillis>0</runDurationMillis>
                <schedulingPeriod>0 sec</schedulingPeriod>
                <schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
                <yieldDuration>1 sec</yieldDuration>
            </config>
            <executionNodeRestricted>false</executionNodeRestricted>
            <name>geographicalcat_UpdateAttributeSchemaName</name>
            <relationships>
                <autoTerminate>false</autoTerminate>
                <name>success</name>
            </relationships>
            <state>STOPPED</state>
            <style/>
            <type>org.apache.nifi.processors.attributes.UpdateAttribute</type>
        </processors>
        <processors>
            <id>f1513fa5-c26a-3157-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>624.0</x>
                <y>32.0</y>
            </position>
            <bundle>
                <artifact>nifi-standard-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</version>
            </bundle>
            <config>
                <bulletinLevel>WARN</bulletinLevel>
                <comments></comments>
                <concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
                <descriptors>
                    <entry>
                        <key>Input Directory</key>
                        <value>
                            <name>Input Directory</name>
                        </value>
                    </entry>
                    <entry>
                        <key>File Filter</key>
                        <value>
                            <name>File Filter</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Path Filter</key>
                        <value>
                            <name>Path Filter</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Batch Size</key>
                        <value>
                            <name>Batch Size</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Keep Source File</key>
                        <value>
                            <name>Keep Source File</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Recurse Subdirectories</key>
                        <value>
                            <name>Recurse Subdirectories</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Polling Interval</key>
                        <value>
                            <name>Polling Interval</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Ignore Hidden Files</key>
                        <value>
                            <name>Ignore Hidden Files</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Minimum File Age</key>
                        <value>
                            <name>Minimum File Age</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Maximum File Age</key>
                        <value>
                            <name>Maximum File Age</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Minimum File Size</key>
                        <value>
                            <name>Minimum File Size</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Maximum File Size</key>
                        <value>
                            <name>Maximum File Size</name>
                        </value>
                    </entry>
                </descriptors>
                <executionNode>ALL</executionNode>
                <lossTolerant>false</lossTolerant>
                <penaltyDuration>30 sec</penaltyDuration>
                <properties>
                    <entry>
                        <key>Input Directory</key>
                        <value>${FileSourceFolder}</value>
                    </entry>
                    <entry>
                        <key>File Filter</key>
                        <value>GeographicalCat.dat</value>
                    </entry>
                    <entry>
                        <key>Path Filter</key>
                    </entry>
                    <entry>
                        <key>Batch Size</key>
                        <value>10</value>
                    </entry>
                    <entry>
                        <key>Keep Source File</key>
                        <value>false</value>
                    </entry>
                    <entry>
                        <key>Recurse Subdirectories</key>
                        <value>false</value>
                    </entry>
                    <entry>
                        <key>Polling Interval</key>
                        <value>0 sec</value>
                    </entry>
                    <entry>
                        <key>Ignore Hidden Files</key>
                        <value>true</value>
                    </entry>
                    <entry>
                        <key>Minimum File Age</key>
                        <value>60 sec</value>
                    </entry>
                    <entry>
                        <key>Maximum File Age</key>
                    </entry>
                    <entry>
                        <key>Minimum File Size</key>
                        <value>0 B</value>
                    </entry>
                    <entry>
                        <key>Maximum File Size</key>
                    </entry>
                </properties>
                <runDurationMillis>0</runDurationMillis>
                <schedulingPeriod>10 sec</schedulingPeriod>
                <schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
                <yieldDuration>1 sec</yieldDuration>
            </config>
            <executionNodeRestricted>false</executionNodeRestricted>
            <name>geographicalcat_GetFile</name>
            <relationships>
                <autoTerminate>false</autoTerminate>
                <name>success</name>
            </relationships>
            <state>STOPPED</state>
            <style/>
            <type>org.apache.nifi.processors.standard.GetFile</type>
        </processors>
        <processors>
            <id>f4b41b47-34d9-3bb4-0000-000000000000</id>
            <parentGroupId>bbf1ef49-f963-37ab-0000-000000000000</parentGroupId>
            <position>
                <x>0.0</x>
                <y>456.0</y>
            </position>
            <bundle>
                <artifact>nifi-standard-nar</artifact>
                <group>org.apache.nifi</group>
                <version>1.10.0</version>
            </bundle>
            <config>
                <bulletinLevel>WARN</bulletinLevel>
                <comments></comments>
                <concurrentlySchedulableTaskCount>1</concurrentlySchedulableTaskCount>
                <descriptors>
                    <entry>
                        <key>Directory</key>
                        <value>
                            <name>Directory</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Conflict Resolution Strategy</key>
                        <value>
                            <name>Conflict Resolution Strategy</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Create Missing Directories</key>
                        <value>
                            <name>Create Missing Directories</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Maximum File Count</key>
                        <value>
                            <name>Maximum File Count</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Last Modified Time</key>
                        <value>
                            <name>Last Modified Time</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Permissions</key>
                        <value>
                            <name>Permissions</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Owner</key>
                        <value>
                            <name>Owner</name>
                        </value>
                    </entry>
                    <entry>
                        <key>Group</key>
                        <value>
                            <name>Group</name>
                        </value>
                    </entry>
                </descriptors>
                <executionNode>ALL</executionNode>
                <lossTolerant>false</lossTolerant>
                <penaltyDuration>30 sec</penaltyDuration>
                <properties>
                    <entry>
                        <key>Directory</key>
                        <value>${FileArchiveFolder}/ref</value>
                    </entry>
                    <entry>
                        <key>Conflict Resolution Strategy</key>
                        <value>fail</value>
                    </entry>
                    <entry>
                        <key>Create Missing Directories</key>
                        <value>true</value>
                    </entry>
                    <entry>
                        <key>Maximum File Count</key>
                    </entry>
                    <entry>
                        <key>Last Modified Time</key>
                    </entry>
                    <entry>
                        <key>Permissions</key>
                    </entry>
                    <entry>
                        <key>Owner</key>
                    </entry>
                    <entry>
                        <key>Group</key>
                    </entry>
                </properties>
                <runDurationMillis>0</runDurationMillis>
                <schedulingPeriod>0 sec</schedulingPeriod>
                <schedulingStrategy>TIMER_DRIVEN</schedulingStrategy>
                <yieldDuration>1 sec</yieldDuration>
            </config>
            <executionNodeRestricted>false</executionNodeRestricted>
            <name>PutFile</name>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>failure</name>
            </relationships>
            <relationships>
                <autoTerminate>true</autoTerminate>
                <name>success</name>
            </relationships>
            <state>STOPPED</state>
            <style/>
            <type>org.apache.nifi.processors.standard.PutFile</type>
        </processors>
    </snippet>
    <timestamp>11/29/2019 08:00:19 HKT</timestamp>
</template>

Re: Nifi - Truncate and Load to mysql db table

New Contributor

Hi Adil,

Can you provide complete details with configuration for the data flow you implemented?

It will be great help.

Thanks,

Sandip

Highlighted

Re: Nifi - Truncate and Load to mysql db table

New Contributor

Please send me the screen shots of the flow. I am working with the same use case  writing csv content to DB. help me I am unable define the properties in the processors.

 

Don't have an account?
Coming from Hortonworks? Activate your account here