Right now I have a sql query setup to pull and write a sqlite .db file for use with microsoft powerbi. I’ve since been told by the database administrators to not pull historical data anymore and move up my query to just the current month. If I run the original query and create a .db of everything from 2019 - nov 2024, how could I append this .db file with the new data, assuming each month in knime I move the date to equal the current month?
I have something like a unique account number, company name and report month (mm/dd/yyyy) in this report. If I run this for 12/1/2024, I need only that months data to append itself to the .db file I’ve already pulled of the last 4 years.
once you have your historical data in a SQLite database (in a table) simply use DB Writer or DB Insert node to add newly pulled monthly data into this table.
Pretty straightforward if I got it right. Only thing to keep in mind if your source/pulled data format changes to properly modify, if needed, your destination table.
without the DB Insert, this does the file creation and overwrites if existing file if ones present. well now that I’m looking at this, this would be the main query pull to create the file, a second version of this would be missing the SQLite connector so it’s not deleting the main DB. So it should be DB insert to db writer
following up there, the image below I have a need to keep the main 5 year .db file seperated from the monthly pull (monthly.db). and want to simply join them in a new file called testmaster.db.
Reason is to be able to adjust and or fix things as I’m working on this project.
I can’t seem to find or figure out a way after the DB Joiner (merging on RPT_MTH and unique customer ID) and output this using DB Writer. What am I missing here as chatgpt and gemini keep telling me that it should work. using knime 5.3
@MichaelB you might want to check which kind of connection does what. If you want to use the results from a (brown) database connection and write that back (again) to a database you can use the: DB Connection Table Writer.
It is important to plan ahead what you want to do in your database and where you want to use KNIME. Both have their strengths. Maybe take a look at this example and follow the logic to see what can be done:
The reason for having two sqlite connectors is to output the two db files, then combine them into a third. If something were to go wrong, I could step backwards and fix any issues. right now, the testmain.db would be the historical pull, the testmonthly.db would be the new data. All this is due to a huge data base (like 20gb), that the admins get mad about pulling from.
I figured this would be simple enough to just merge the historical.db and the monthly.db into a new master.db and build the powerbi queries off of. There’s a lot of data manipulation through powerbi, correcting formatting and other things that I’m not wanting to nor proficient in sql to want to do in the queries themselves.
@MichaelB the DB Joiner would only work with the same database connection since this is only a view technically. I will have to see if I can set up an example.
Would all data be in one SQLite database or different ones?
@MichaelB would you create a copy of the Testmain.db and then just write there and keep a copy. Also: have you tested if SQLite would be able to hold your data and would the database still be useable? If you have very large local databases H2 offers the option to split a DB into several local files but I do not have much experience with performance.
I’m not an expert, but I supose you can only join with db joiner if bowth tables belong to the same db.
It is possible to use a local table joiner like
Another option would be to store the new data inside a temporary table and then insert it into the final table making sure that only dates that have not yet been transferred are used:
based on your description I doubt that you need to do a join operation:
You want to append the newest data (testmonthly) to a existing table (testmain) and write a new table (testmaster). Thats a concatenate.
In your case I would copy the testmain.db file to a backup folder and would then add monthly the new data with “DB insert”
