Author Topic: New Projects VWS replace VWSql, move data to another, Impute database  (Read 2715 times)

0 Members and 1 Guest are viewing this topic.

Offline IngemarS

  • Member
  • *
  • Posts: 48
    • Österåsen, Fjällsjö Weather Station
This is the first of three projects related to VWS in combination with VWSql, using KNIME Analytics Platform.

(Attached is a picture of the workflow, as well as the workflow file to adapt and execute, se more below)

At this stage I have created a KNIME Workflow named "Impute_VWS_VWSql", which finds any missing rows in you database table and "suggest" rows and values to impute back to the database. (Other related projects see IN THE PLANS).

At this stage of my workflow construction, this should be considered as a "Read-Only" test, with only 9 columns.

Suggestions welcome, but I already know there are things need improvement, such as: What about linear regression of WindDir 350 and 10... (Which way should it go?)
There are some things I know can be done via java or python (ver 2?) or other languages - KNIME has nodes for about a handful of scripting languages. That can do fantastic things for imputing very realistic values. Splines for just an example. Help with those things is appreciated... I have spent the last year testing what's the best methods depending on how much data is missing in any sequence between real data...

IN THE PLANS:
A) Replacing need for VWSql with a KNIME workflow - could possibly be done in a few hours (I work only a couple of hours per day though).
B) Creating a workflow that takes your VWSql weather data and recalculates and rearanges columns as neccesary for putting you "old" data into whatever database system your choice of new weather system might be. Columns that don't fit the new system could be in another port of out put and put into another table in the new database...
 Please view a one or a few of the KNIME starter videos, so you know have to execute the nodes, and see how to do some changes, as you must, see below steps...
Search the web for KNIME starter videos - Youtube has about 10 of them of which 4-6 of them are between 2, minues to 6 minutes - well spent time. There are also videos for installing KNIME on Windows - as well as some other operating systems. So, you can in fact, with my workflow, use a linux computer to get data from you VWSql database table, and make all the tests and finally see the imputations.

Here is what is needed to get started testing!

Change settings to suite your needs! The workflow nodes has not been renumbered so the workflow begins at node 22!

Before you Import the workflow to KNIME, you will have to unzip the workflow file and it becomes a packed project file with extension knwf.

There is even a video on importing workflow files (search for "knime import export workflow" 6 minutes well spent...

To change settings click with alternate mouse button. Choose top option "Configurate...", as you should already have seen in the videos...


steps:
1. Node 22  "MySQL Connector" (tested at my place with MariaDB).
   a)Tab "Connection settings", change group Connection/Hostname, /port, and set the name of the database that contains your table with weather data.
    b) ... group "authentication" button "Use username & Password" /Username and Password. (PLEASE use only a database user with no write permissions to data table - at least at this stage.
   c) "Timezone corretion" seet "Use local timezone" - would be sufficient since we´re only reading historical data from db.tbl.
   d) leave all other settings on that on that tab and the two other tabs.

2. Node 23 "Database Table Selector": Only deal with "Settings! tab.
    a) Read throught my comment
   b) Until you know/understand what is being done - please only change in the WHERE-clause  after BETWEEN and its comments.
My test settings are "201504270000 AND 201404272359" (without the quotes). Please choose RecDate rows f. ex. a single date or a few dates - possibly where you KNOW you have missing rows in the table!½

NOW GO TO node 37!
4.   Node 37 "Create Date&Time Range"
   a) Only use "Options" tab. "Output Settings" group should be "Date&time", and "New  column name:" . /"Mode Selection", "Variable" must be selected.
   b) Note value of "Ending point"/"Interval:" my default i "1m" (without the quotes) meaning 1 minute intervals being created. But if you have let VWS save in another interval like 5 minutes, you of cause have to have that interval between VWSql created records too.
   c) IMMEDIATELY BELOW the "Options" tab there should be a WARNING TRIANGLE with exclamation mark and the text ' "start" and "end" are controlled by variables.' If the warning isn't there you have probably manually changed things in "Starting Point or "Ending Point" (besides good changes in "Ending Point"/"Interval", see 4b above. I you lost the leine with the warning, you can get it back by changing "Create Date&Time Range"/"Flow variables": "s* start" select "MinVWSdatetime", and "s* end" to "MaxVWSdatetime", then you should have the warning line in the "Options" tab!

Good luck!
Davis Vantage Pro2 Plus, VWS, WXSIM

Offline BrianLehan

  • Senior Member
  • **
  • Posts: 91
    • Grace C Lehan Memorial Weather Station
Stumbled across this tonight researching moving from VWS version 15 to something and hoping to take 20 years of historical data to the new system or atleast get the old VWS data in a searchable database or spreadsheet.

So if I read this post right the VWS database is SQL structured? And if I follow this I can export the data into MySQL?

Brian
Davis Vantage Pro 2 plus
VWS 15x on Windows 7
Sarasota-Weather Template
Gr3 Radar