It look like PutSQL is not allowing anything else than Insert / Update, or is there a parameter that I should change ?Īny other solution ? (I can't use ExecuteSQL as I don't want to query anything, just truncate before insert) The NiFi doc explain that PutSQL is only for INSERT or UPDATE. So NiFi and the JDBC should be able to run it. The truncate command work when in the Pre-SQL of the ExecuteSQL processor. The database is Clickhouse, using last JDBC. 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.
I use all default parameters from NiFi 1.10 processor. Routing to failure: 圎rrorException: Query must be like 'INSERT INTO table VALUES (?, ?, ?)'. Got: truncate table SBST.geographicalcat All the post I've seen recommand like here using PutSQL for that. I'm trying to execute the SQL command "Truncate 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. Schedule these flows non overlapping times that can trigger first truncating the table then schedule loading data into table. Keep Truncating table process seperately likeįlow:- Generate Flowfile processor //keep truncate table statement -> Putsql processor(execute the truncate table statement)īefore loading data trigger this Generateflowfile flow first(i.e method 2) then trigger your actual flow(i.e in your Question) seperately. In the above method you are executing truncate statement inside your NiFi Flow it self. 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.įlow:- ListSFTP->FetchSFTP->InferAvroSchema->ConvertCSVtoAvro->ConvertAvrotoJSON->Executesql//executes truncate table statment and connect failure relation to next processor) ->ConvertJSONtoSQL->PutSQL It actually truncates the data in the table.
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.īecause executesql processor is used to execute select sql statements and get results but in our case we are not getting results back as Flowfile.īut 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). To Truncate Table use Execute SQL processor before ConvertJSONtoSQL processor. There is no need of using SplitJson processor before ConvertJSONtoSQL processor.
You to remove SplitJson processor before ConvertJSONtoSQL processor because ConvertJSONtoSQL does S plitting 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. Now, what if we want to delete the Students table that we just truncated.There is no predefined properties that we can specify in NiFi processor that can Truncate the table for us before loading into it.īut there are some ways we can do that using NiFi Processor.
#Mysql truncate table code#
Syntax for MySQL DROP DROP TABLE table_name Code language: SQL (Structured Query Language) ( sql ) Example of MySQL DROP It only clears the data within the table. As we observed, the TRUNCATE command does not delete the table by itself.
Let’s move to understanding the MySQL DROP command here. MySQL DROP – Delete MySQL Tables and Databases If you were to put an INSERT statement after this to add a record, it would work. What we can infer from this is that TRUNCATE clears all the records from the tables but does not delete the table. Furthermore, when we use the DESCRIBE command, we can see the structure of the table. As you can see, when we use the SELECT statement, we get an empty set as our output.
0 kommentar(er)
