Note |
---|
This is a placeholder for more extensive documentation |
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 r a database. The import reads the database directlythis 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:
Image Removed
Code Block |
---|
|
<?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.
Code Block |
---|
language | xml |
---|
title | 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>
|
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
Code Block |
---|
language | xml |
---|
title | Example jdbc configuration |
---|
|
<jdbcDriverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcDriverClass>
<jdbcConnectionString>jdbc:sqlserver://hostname;DatabaseName=TestDB;integratedSecurity=true;</jdbcConnectionString>
|
FEWS 2017 and earlier branches
Code Block |
---|
language | xml |
---|
title | 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.
Code Block |
---|
language | xml |
---|
title | 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:
Code Block |
---|
language | xml |
---|
title | 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 extensionsEn example file is attached to this page.