Page History

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. To allow for special behaviour of the database import, it Besides connecting to a database server with a connection string is also possible to use the import type "DatabaseSingleLocationSingleDayPerQuery".

The different types do:

• database: one query to the database to retrieve all available data within the relativeViewPeriod.
• databaseSingleLocationSingleDayPerQuery: one query per wanted timeseries, per day to the database to retrieve all available data within the relativeViewPeriod.

Sometimes it may be required to install additional drivers to be able to read some Historian databases, like iHistorian from General Electric Janucimport 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:

Image Removed

An example file is attached to this page.

Example to read iHistorian

Example to import from iHistorian database

<?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"/>
  
<general>
  <importType>databaseSingleLocationSingleDayPerQuery</importType>
  <jdbcDriverClass>com.inzoom.jdbcado.Driver</jdbcDriverClass>
  <jdbcConnectionString>jdbc:izmado:Provider=ihOLEDB.iHistorian;Data Source=srvHistorian</jdbcConnectionString>
  <user>*****</user>
  <password>******</password>
  <relativeViewPeriod unit="hour" start="-24" startOverrulable="true" end="0"/>
  <table name="ihRawData">
    <dateTimeColumn name="TimeStamp"/>
    <locationColumn name="TagnameLOC"/>
     <flagColumn <unitColumn name="qualityUNIT"/>
      <valueColumn<parameterColumn name="ValuePARAM" unit/>
      <valueColumn name="SIVALUE"/>
      <userColumn name="USER"/>
</table>

Image Added

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>

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.

<importTypeStandard>database</importTypeStandard>
<jdbcDriverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcDriverClass>
  <idMapId>IdKETEN</idMapId>
  <flagConversionsId>ImportKETENFlagConversions</flagConversionsId>
  <importTimeZone>
    <timeZoneOffset>+01:00</timeZoneOffset>
  </importTimeZone>
  <dataFeedId>iHistorian</dataFeedId>
</general>
<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:

<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.Note that special drivers should be installed at the servers, additionally from FEWS, to enable the access to iHistorian database