Processing Ensemble Files to SQLite database

Problem

I have a folder full on ensemble forecasts and I need to process them into SQLite database.

Objective

Create a single sqlite database file containing a time-series of ensemble forecasts.

IntelliJ Setup

1. Create new project
2. Configure Project SDK to Jython executable

3. Redirect tmp and temp from IntelliJ Terminals

4. Make Jython Script

from hec.ensemble import CsvEnsembleReader
from java.time import ZonedDateTime, ZoneId
from hec import SqliteDatabase
from org.sqlite import JDBC

def main():

    ensembleFileRoot = r'some\directory\path' 
    dbFilePath = r'output.db' 

    # Initialize or open an existing database
    dbFile = SqliteDatabase(dbFilePath, SqliteDatabase.CREATION_MODE.CREATE_NEW_OR_OPEN_EXISTING_UPDATE)

    # Initialize CSV Reader
    reader = CsvEnsembleReader(ensembleFileRoot)
    
    # First date of issued forecast
    ensembleStartDate = ZonedDateTime.of(1996,12,15,12,0,0,0,ZoneId.of("GMT"))

    # Last date of forecast issued
    ensembleStartDate = ZonedDateTime.of(1997,1,15,12,0,0,0,ZoneId.of("GMT"))

    # Read all forecasts into object
    ets = reader.Read("american", ensembleStartDate, ensembleEndDate)

    # Write the time series to the database
    dbFile.write(ets)

    # Close the database
    dbFile.close()



if __name__ == '__main__':
    main()

CsvEnsembleReader is initialized using a path to a directory NOT an explicit path to an individual file

reader.Read("american", ensembleStartDate, ensembleEndDate) will build the file path to an individual forecast ensemble. The watershed name needs to match the input csv file

from org.sqlite import JDBC is needed for the database driver but is not explicitly used throughout the code.

5. Create run configuration
   • Environment variables: PYTHONUNBUFFERED=1;TMP=C:\Temp;TEMP=C:\Temp
   • Interpreter options
     • -Djava.library.path="C:\Local_Software\HEC-DSSVue 3.2.3\lib"
     • -Dpython.path="C:\workspace\git_clones\folsom-cs-at-modeling\jars\FIRO_TSEnsembles-1.0.1.jar";"C:\workspace\git_clones\folsom-cs-at-modeling\jars\sqlite-jdbc-3.30.1.jar"