Database import

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.

Example configuration for usage of userColumn

<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

Example jdbc configuration

<jdbcDriverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcDriverClass>

<jdbcConnectionString>jdbc:sqlserver://hostname;DatabaseName=TestDB;integratedSecurity=true;</jdbcConnectionString>

FEWS 2017 and earlier branches