Overview
TimeSeries reader for a database. The general identifier for this reader is "database". This import allows a FSS or stand alone system to import data from a database. The import reads this database directly. Besides connecting to a database server with a connection string is also possible to import msaccess *.mdb (only on 32 bit Windows systems) and firebird *.fdb database files.
Configuration
This reader supports the tableMetadata element in the general section of the timeseriesImportrun:
<?xml version="1.0" encoding="UTF-8"?> <timeSeriesImportRun xmlns="http://www.wldelft.nl/fews" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.wldelft.nl/fews http://fews.wldelft.nl/schemas/version1.0/timeSeriesImportRun.xsd"> <import> <!--QPEsums Actuals and Forecast--> <general> <importTypeStandard>database</importTypeStandard> <!-- example for mysql, with non embedded db drivers in FEWS bin --> <jdbcDriverClass>com.mysql.jdbc.Driver</jdbcDriverClass> <jdbcBinDir>$REGION_HOME$\Modules\jdbc</jdbcBinDir> <jdbcConnectionString>jdbc:mysql://myhost/cwb_ac</jdbcConnectionString> <user>?</user> <encryptedPassword>?</encryptedPassword> <!--example for oracle, with embedded drivers in FEWS bin--> <jdbcDriverClass>oracle.jdbc.OracleDriver</jdbcDriverClass> <jdbcConnectionString>jdbc:oracle:thin:@<server>:<port>:<db_instance></jdbcConnectionString> <user>?</user> <encryptedPassword>?</encryptedPassword> <relativeViewPeriod startOverrulable="true" endOverrulable="true" start="-1" end="1" unit="day"/> <table name="qpe_sums_obs"> <dateTimeColumn name="recdate"/> <valueColumn name="rad_gz" unit="mm/hr" locationId="Qpesums" parameterId="P.radar.actual" parser="Mosaic"/> </table> <table name="qpe_sums_foc"> <forecastDateTimeColumn name="createdate"/> <dateTimeColumn name="raddate"/> <valueColumn name="rad_gz" unit="mm/hr" locationId="Qpesums" parameterId="P.radar.forecast" parser="Mosaic"/> </table> <unitConversionsId>ImportUnitConversions</unitConversionsId> <importTimeZone> <timeZoneOffset>+00:00</timeZoneOffset> </importTimeZone> <dataFeedId>QPE_Sums</dataFeedId> </general> <timeSeriesSet> <moduleInstanceId>Import_Qpesums</moduleInstanceId> <valueType>grid</valueType> <parameterId>P.radar.actual</parameterId> <locationId>Qpesums</locationId> <timeSeriesType>external historical</timeSeriesType> <timeStep unit="minute" multiplier="10"/> <readWriteMode>add originals</readWriteMode> <expiryTime unit="day" multiplier="14"/> </timeSeriesSet> <timeSeriesSet> <moduleInstanceId>Import_Qpesums</moduleInstanceId> <valueType>grid</valueType> <parameterId>P.radar.forecast</parameterId> <locationId>Qpesums</locationId> <timeSeriesType>external forecasting</timeSeriesType> <timeStep unit="nonequidistant"/> <readWriteMode>add originals</readWriteMode> <expiryTime unit="day" multiplier="2"/> </timeSeriesSet> </import> </timeSeriesImportRun>
userColumn
Since 2016.02 there is a column that allows for specifying a user with each data point.
<table> <dateTimeColumn name="DATE" pattern="dd-MM-yy HH:mm"/> <locationColumn name="LOC"/> <unitColumn name="UNIT"/> <parameterColumn name="PARAM"/> <valueColumn name="VALUE"/> <userColumn name="USER"/> </table>
Samples
It is not possible yet to import sample meta data. Only the values will be imported
Configuration for Microsoft SQL Server
In the above example a jdbc connection example for Oracle was given, to connect to a Microsoft SQL server use the following:
FEWS 2018 and later branches
<jdbcDriverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcDriverClass> <jdbcConnectionString>jdbc:sqlserver://hostname;DatabaseName=TestDB;integratedSecurity=true;</jdbcConnectionString>
FEWS 2017 and earlier branches
<jdbcDriverClass>net.sourceforge.jtds.jdbc.Driver</jdbcDriverClass> <jdbcConnectionString>jdbc:jtds:sqlserver://hostname;DatabaseName=TestDB;integratedSecurity=true;</jdbcConnectionString>
Configuration for PostgreSQL
To import time series from a PostgreSQL database the connection string is the following:
<jdbcDriverClass>org.postgresql.Driver</jdbcDriverClass> <jdbcConnectionString>jdbc:postgresql://localhost:5432/Database1?currentSchema=workspace1</jdbcConnectionString>
In this example the database is connected to a PostgreSQL database installed on a local machine. The database is "Database1" and the schema "workspace1".
Make sure to use the latest PostgreSQL driver from the PostgreSQL site (https://jdbc.postgresql.org/download.html). Minimum driver version must be 42.2.1, there is an error in 42.2.0.
Configuration for importing firebird database files
To import one or more Firebird database files, you can simply configure the folder where the *.fdb files are located with the <folder> element, the configured folder should only contain firebird files as the import will loop over all files in the folder regardles of the file extensions.