How to load evaluation stats (point stats and grid stats) from Casper/Derecho to RAL machine (mohawk)

[YongxinFred]

I have created the point stats and grid stats on Casper using METplus. I will need to load the stats to the METviewer database on mohawk for making plots. I am wondering how I can accomplish this. I have access to mohawk. Thank you.

[JohnHalleyGotway]

@YongxinFred, I see you'd like to load some MET output data into the METviewer instance running on the NCAR machine named mohawk. Here's the URL for that METviewer instance:
https://dtcenter.ucar.edu/met/metviewer/metviewer1.jsp

I went hunting through some old emails, and think that the step for doing so are as follows:

1. I create a new database for you on mohawk. The name should being with mv_ in order for it to be visible on the METviewer GUI.
   Please tell me what database name you'd like to use. For example, if its mv_yongxin, I'd run the following commands on mohawk:

mysql -u mvadmin -p -e 'create database mv_yongxin'
mysql -u mvadmin -p -e "GRANT INSERT, DELETE, UPDATE , INDEX, DROP ON mv_yongxin.* to 'mvuser'@'%' "
mysql -u mvadmin -p mv_yongxin < /d2/projects/METViewer/METdataio/METdbLoad/sql/mv_mysql.sql

Note that these commands do require an mvadmin password, which I do have.

2. Once the database exists on mohawk, the next question is whether the MET output can be loaded directly from Casper. I do see mariadb installed on Casper in /usr/bin/mariadb and according to the MET existing builds page in 'NCAR Machines > Casper', METdataio is installed there as well:

module use /glade/work/dtcrt/METplus/casper/components/METdataio/installations/modulefiles
module load metdataio/2.1.0

I do recall in the past some scientists in the DTC (like @michelleharrold and @KathrynNewman) transferring MET output from NCAR HPC back to our local project machines before loading into METviewer. But it sure would be nice if we could load directly from Casper instead of having to move the data!

We'll need to lean on advice from @TatianaBurek and @bikegeek for details on using METdataio to load data. And we could ask @jprestop questions about the installs on Casper.

For now, please just tell me what mv_ database name you'd like to use, and I can add that database on mohawk. Then we can coordinate with others on the METplus team to put the pieces together in the right order... and hopefully update the documentation along the way so this process is more clear for the next time!

Thanks for helping us push the ball forward on this.

[TatianaBurek]

I loaded the data to NO GROUP > mv_hydro database on mohawk and it is ready for plotting at this METviewer instance:
dtcenter.ucar.edu/met/metviewer/metviewer1.jsp

[JohnHalleyGotway]

Thanks @TatianaBurek for loading this data. @YongxinFred by way of example, I used METviewer to make this plot of RMSE for the LWDOWN variable for the National Water Model (nwm) MET output:

And here's the corresponding XML (although I had to add a .txt suffix so that GitHub would let me upload it):
plot_20240325_145234.xml.txt

And here's a screen recording to demonstrate how you use the Load XML option in the top-right corner of the GUI to recreate this plot.

METviewer_Load_XML.mov

[YongxinFred]

Hi John, This plot looks great. Thank you so much. This is the kind of plots that I will need to make except that I will need to plot three curves there, one for using the bilinear interpolation method, another one for using the conserve interpolation method, and the third one for using the neareststod interpolation method. I have created the stats from these three interpolation methods but somehow in the METviewer database they did not show up under "Y1 Series Variables". I am wondering if you have any further suggestions. Thanks, Yongxin

…

On Mon, Mar 25, 2024 at 9:10 AM John Halley Gotway ***@***.***> wrote: Thanks @TatianaBurek <https://github.com/TatianaBurek> for loading this data. @YongxinFred <https://github.com/YongxinFred> by way of example, I used METviewer to make this plot of RMSE for the LWDOWN variable for the National Water Model (nwm) MET output: newplot.png (view on web) <https://github.com/dtcenter/METplus/assets/21087144/b40017eb-4241-4def-8f61-2ae578e5e6a3> And here's the corresponding XML (although I had to add a .txt suffix so that GitHub would let me upload it): plot_20240325_145234.xml.txt <https://github.com/dtcenter/METplus/files/14745642/plot_20240325_145234.xml.txt> And here's a screen recording to demonstrate how you use the Load XML option in the top-right corner of the GUI to recreate this plot. https://github.com/dtcenter/METplus/assets/21087144/33ceade1-82d4-4cbb-910d-742c8014a808 — Reply to this email directly, view it on GitHub <#2523 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKOVCYBKN7NAT4AT42WWZU3Y2A453AVCNFSM6AAAAABFDVCNO6VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DSMBUGAYDC> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[JohnHalleyGotway]

I see what you're seeing. Under Y1 Series Variables, the only option I see listed is BILIN for output using the bilinear interpolation method. That would indicate that the mv_hydro database does NOT contain output from the other two interpolation methods.

I think we need help from @TatianaBurek on this one. Tatiana, can you point us to the load XML you used to load MET output into this database? Perhaps output from the other two interpolation methods were live in some other directories that were not loaded?

[TatianaBurek]

All the data from /d2/projects/HYDRO was loaded to the database

[TatianaBurek]

I used these XML files:
/home/vxwww/hydro_load.xml
/home/vxwww/hydro_load_dates.xml
The directory structures are different so I had to load the data in 2 steps

[YongxinFred]

John, Thanks a lot for looking into this. I have the stats from all three interpolation methods included in these directories. *1)* *bilinear_TUVQP_point_stat* *2) **conserve_TUVQP_point_stat* *3) **neareststod_TUVQP_point_stat* *4) **bilinear_solar_point_stat* 5) *conserve_solar_point_stat* 6) *neareststod_solar_point_stat* *7) **bilinear_precip_grid_stat* *8) **conserve_precip_grid_stat* *9) **neareststod_precip_grid_stat* I think that Tatiana has already loaded all these directories to the database. If it is not possible at this time to add all three curves in one plot due to the time constraint, I can do one curve per plot and then combine the three plots together but the issue is that I did not see "conserve" and "neareststod" in the database. Thanks, Yongxin

…

On Mon, Mar 25, 2024 at 9:49 AM John Halley Gotway ***@***.***> wrote: I see what you're seeing. Under *Y1 Series Variables*, the only option I see listed is *BILIN* for output using the bilinear interpolation method. That would indicate that the *mv_hydro* database does NOT contain output from the other two interpolation methods. I think we need help from @TatianaBurek <https://github.com/TatianaBurek> on this one. Tatiana, can you point us to the load XML you used to load MET output into this database? Perhaps output from the other two interpolation methods were live in some other directories that were not loaded? — Reply to this email directly, view it on GitHub <#2523 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKOVCYFTHDRXDFKZCA6XONLY2BBRVAVCNFSM6AAAAABFDVCNO6VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DSMBUGUZTG> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[JohnHalleyGotway]

Thanks @TatianaBurek. @YongxinFred, FYI, I took a closer look at that data in /d2/projects/HYDRO and see the following:

johnhg@mohawk:/d2/projects/HYDRO$ for file in `ls *_solar_point_stat/point_stat_060000L_20231020_230000V.stat`; do
> echo $file
> cat $file | awk '{print $18, $19}' | sort -u
> done
bilinear_solar_point_stat/point_stat_060000L_20231020_230000V.stat
BILIN 4
INTERP_MTHD INTERP_PNTS
conserve_solar_point_stat/point_stat_060000L_20231020_230000V.stat
BILIN 4
INTERP_MTHD INTERP_PNTS
neareststod_solar_point_stat/point_stat_060000L_20231020_230000V.stat
BILIN 4
INTERP_MTHD INTERP_PNTS

So even though you've named the output directories as "bilinear", "conserve", and "neareststod", the INTERP_MTHD output column indicates that bilinear interpolation was actually used for all 3.

But I did diff these files and see that the forecast values really do differ... which would indicate a real difference somewhere. So I'm not really sure what's going on. Perhaps I'm confusing exactly what you mean by interpolation method? I'm assuming this means how the point interpolation options are set when running MET. Is that what you mean as well? Or something different?

Ultimately, the fact that the full paths differ doesn't really matter once the data is loaded into the database. We need to make sure that the actual data can be differentiated from line-to-line. One option is making use of the DESC column. By default, that's set to NA, but the user can set it at runtime to set it to some other constant string. That would enable you to differ between these 3 runs.

[YongxinFred]

John, Many thanks for all these. The way I created the data and the stats were these: 1) First I regridded the RAP/HARR variables (2-m T, 2-m Q, 10-m U, 10-m V, surface pressure, surface longwave downward radiation, surface shortwave downward radiation, and precipitation) to the National Water Model grid using three different interpolation/regridding methods, bilinear, conserve and neareststod. This is why there are "bilinear", "conserve", and "neareststod" in the directory names; 2) Then I computed the point stats for T, Q, U, V, P and solar variables, and grid stats for precip with METplus using the config files that Amanda used and modified for me, for the regridded data using the three different interpolation/regrid methods. What I will need to do is to compare each of the variables among the three different interpolation methods. Please let me know if you have any questions or suggestions. I will try with the "DESC" column and I will let you know if I have any questions. Thanks, Yongxin

…

On Mon, Mar 25, 2024 at 10:12 AM John Halley Gotway < ***@***.***> wrote: Thanks @TatianaBurek <https://github.com/TatianaBurek>. @YongxinFred <https://github.com/YongxinFred>, FYI, I took a closer look at that data in /d2/projects/HYDRO and see the following: ***@***.***:/d2/projects/HYDRO$ for file in `ls *_solar_point_stat/point_stat_060000L_20231020_230000V.stat`; do > echo $file > cat $file | awk '{print $18, $19}' | sort -u > done bilinear_solar_point_stat/point_stat_060000L_20231020_230000V.stat BILIN 4 INTERP_MTHD INTERP_PNTS conserve_solar_point_stat/point_stat_060000L_20231020_230000V.stat BILIN 4 INTERP_MTHD INTERP_PNTS neareststod_solar_point_stat/point_stat_060000L_20231020_230000V.stat BILIN 4 INTERP_MTHD INTERP_PNTS So even though you've named the output directories as "bilinear", "conserve", and "neareststod", the INTERP_MTHD output column indicates that bilinear interpolation was actually used for all 3. But I did diff these files and see that the forecast values really do differ... which would indicate a real difference somewhere. So I'm not really sure what's going on. Perhaps I'm confusing exactly what you mean by interpolation method? I'm assuming this means how the point interpolation options are set when running MET. Is that what you mean as well? Or something different? Ultimately, the fact that the full paths differ doesn't really matter once the data is loaded into the database. We need to make sure that the actual data can be differentiated from line-to-line. One option is making use of the DESC column. By default, that's set to NA, but the user can set it at runtime to set it to some other constant string. That would enable you to differ between these 3 runs. — Reply to this email directly, view it on GitHub <#2523 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKOVCYDZDGQKSNHKKNIF74TY2BEGTAVCNFSM6AAAAABFDVCNO6VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DSMBUG44TQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[YongxinFred]

Hi John, I checked "DESC" under "Y1 Series Variables" but I only saw "NA". I am not sure how I can add "bilinear" or "conserve" or "neareststod" there. I am wondering if we can have a Google meeting to go over this. Thank you John. Yongxin

[JohnHalleyGotway]

@YongxinFred the DESC setting is defined when METplus itself is run... so like when you're running on Casper. The NA you're seeing is the default value for the description. But re-running all the METplus commands just to set the DESC column sure seems like overkill.

Instead, I'd recommend that we patch your output by updating the DESC column to set it as bilinear, conserve, downscale, or neareststod, based on the output directory in which it resides. Let me take a look patching those output files and I'll get back to you.

[JohnHalleyGotway]

OK, I'm running the Stat-Analysis tool right now to update the contents of the DESC column based on the directory name. It'll take a while to crank through all the files. Once it's done, I'll copy over the output to a HYDRO_patch_DESC directory.

FYI, here's the commands I'm running.

for DIR in `ls -1d /var/autofs/mnt/mohawk_d2/projects/HYDRO/*_stat`; do

  export DESC=`basename $DIR | cut -d'_' -f1`

  for FILE in `find $DIR -name "*.stat"`; do

    PATCH_FILE=`echo $FILE | sed 's%/var/autofs/mnt/mohawk_d2/projects/HYDRO%/d1/projects/HYDRO_patch_DESC%g'`
    echo "mkdir -p `dirname $PATCH_FILE`" >> /d1/projects/patch_cmds

    echo "/d1/projects/MET/MET_regression/develop/NB20240325/MET-develop/bin/stat_analysis -lookin $FILE -job filter -set_hdr DESC $DESC -dump_row $PATCH_FILE" >> /d1/projects/patch_cmds

  done
done

[JohnHalleyGotway]

@YongxinFred and @TatianaBurek, I finished patching this data by updating the DESC column.

On mohawk, the updated data now lives in /d2/projects/HYDRO_patch_DESC. I have updated the XML load specs in /home/vxwww/load_hydro_grid_stat.xml and /home/vxwww/load_hydro_point_stat.xml to load Grid-Stat output from 3 directories organized by initialization date and 7 directories without initialization date sub-directories, respectively.

I'll need to drop the existing mv_hydro database before reloading it.

Question: Should I also drop the existing mv_hydro_bilinear, mv_hydro_conserve, and mv_hydro_neareststod databases to avoid confusion?

[JohnHalleyGotway]

OK, I just dropped and am reloading the mv_hydro database on mohawk:

# Drop db
mysql -u mvadmin -p -e 'drop database mv_hydro'

# Re-create db
mysql -u mvadmin -p -e 'create database mv_hydro'
mysql -u mvadmin -p -e "GRANT INSERT, DELETE, UPDATE , INDEX, DROP ON mv_hydro.* to 'mvuser'@'%' "
mysql -u mvadmin -p mv_hydro <  /d2/projects/METViewer/METdataio/METdbLoad/sql/mv_mysql.sql

# Load data
/d2/projects/METViewer/src/apps/METviewer/bin/mv_load.sh load_hydro_grid_stat.xml >& ~/load_hydro_grid_stat.log&
/d2/projects/METViewer/src/apps/METviewer/bin/mv_load.sh load_hydro_point_stat.xml >& ~/load_hydro_point_stat.log&

I'll follow up with a plot to demonstrate once it's all complete.

[YongxinFred]

John, Thanks a lot for patching the stats output by updating the DESC column. If that works, that will be a big help for me and for other users whose stats outputs are organized in the similar way that I did. Yongxin

[JohnHalleyGotway]

@YongxinFred, here's an example of plotting the mean (average) RMSE statistic for the LWDOWN variable using the 3 different interpolation methods indicated in the DESC column.

And here's the corresponding XML file (with a .txt suffix for it to be attached here):
plot_20240327_140945.xml.txt

@TatianaBurek, all of the Grid-Stat output loaded into the mv_hydro database without an issue. But when loading the Point-Stat output on mohawk, I saw the following error message:

egrep -i error /home/johnhg/load_hydro_point_stat.log | sort -u
ERROR:root:*** <class 'NameError'> in read_data upper ***

And that appears 377 time in the load log file.

Is this an error message to be concerned about?

[YongxinFred]

John, That plot looks great! That is exactly what I would like to have. Thank you so much! Yongxin