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
Example jdbc configuration
<jdbcDriverClass>net.sourceforge.jtds.jdbc.Driver</jdbcDriverClass>

<jdbcConnectionString>jdbc:jtds:sqlserver://hostname;DatabaseName=TestDB;integratedSecurity=true;</jdbcConnectionString>
SQL Server with linked server

It is also possible to use the database import from a Microsoft SQLServer database with a linked server.  In the example below the linked server is "168.168.0.0\TESTLINK" with a database name "dbName", schema name "dbo" and table name "TableName". To import data from this table, the connectionstring need to be connected to the original database, the table configuration requires the linked database information. If the linked server contains points '. ', line in an IP address, make sure to use [] around the linked server name.

Example jdbc configuration
<importTypeStandard>database</importTypeStandard>
<jdbcDriverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcDriverClass>
<jdbcConnectionString>jdbc:sqlserver://0.0.0.0:1433;databaseName=originaldatabase;Integrated Security=SSPI;</jdbcConnectionString>
.....
<table name="[168.168.0.0\TESTLINK].dbName.dbo.TableName">
	<locationColumn name="st_code"/>
	<dateTimeColumn name="site_time"/>
	<valueColumn name="waterlv" parameterId="H.obs"/>
	<valueColumn name="flow" parameterId="Q.obs"/>
</table>

  


Configuration for PostgreSQL

To import time series from a PostgreSQL database the connection string is the following:

Example jdbc configuration
<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.


  • No labels

Disclaimer