I have billions of rows in SQL Server tables and I'm using Nifi to load those rows into MySQL. I tried using putSQL and PutDatabaseRecord both are satisfying the requirement, however they are taking quite long time to load the into MySQL(100k records per minute, for 1 billion it might be 1000+ minutes) as they are doing it record by record. Do we have any bulk load option to load the AVRO/CSV flowfiles into MySQL in Nifi. Could you please suggest an option. Here is my Flow : ListDataBaseTables -> GenerateTableFecth(partition size= 50k records) -> Execute SQL -> ConvertRecord(Avro to CSV) -> PutSQL ListDataBaseTables -> GenerateTableFecth(partition size= 50k records) -> Execute SQL(Avro) -> PutDatabaseRecord. ... View more

I'm trying to load the flow files into MySQL database using bulk load option. Below is the query I'm using as part of the UpdateAttribute processor and passing that query to PutSQL after updating the parameters to do bulk load. LOAD DATA INFILE '${absolute.path}${filename}' INTO TABLE ${dest.database}.${db.table.name} FIELDS TERMINATED BY',' LINES TERMINATED BY'\n' When I ran the flow it's failing saying file not found exception. There were a total of1 FlowFiles that failed,0 that succeeded,and0 that were notexecuteand will be routed to retry;: java.sql.BatchUpdateException: Unable toopenfile'data.csv'for'LOAD DATA INFILE command.Due to underlying IOException:`**BEGIN NESTED EXCEPTION ** java.io.FileNotFoundException MESSAGE: data.csv (No such fileor directory) java.io.FileNotFoundException: data.csv (No such fileor directory). Here MySQL Server and Nifi are on different nodes so I can't use LOAD DATA LOCAL INFILE query. I'm not sure why I'm getting file not found exception even though I mentioned the complete absolute path of the flow file in the SQL Query. When I use query with hard coded file name and providing the absolute path of the file in nifi node, it's working as expected. Working: <code>LOAD DATA LOCAL INFILE '/path/in/nifi/node/to/file/data.csv' INTO TABLE ${dest.database}.${db.table.name} FIELDS TERMINATED BY',' LINES TERMINATED BY'\n'} Question is how to get the absolute path of the flow file and load the same flow file into mysql. Flow: ... View more