...
This reader supports the tableMetadata element in the general section of the timeseriesImportrun:
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> |
...
Since 2016.02 there is a column that allows for specifying a user with each data point.
Code Block | ||||
---|---|---|---|---|
| ||||
<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
Columns not available for this import type
flagSourceColumn
sampleIdColumn
propertyColumn
attributeColumnIt is not possible yet to import sample meta data. Only the values will be imported
Configuration for Microsoft SQL Server
...
FEWS 2018 and later branches
Code Block | ||||
---|---|---|---|---|
|
...
<jdbcDriverClass>com.microsoft.sqlserver.jdbc.SQLServerDriver</jdbcDriverClass> |
...
<jdbcConnectionString>jdbc:sqlserver://hostname;DatabaseName=TestDB;integratedSecurity=true;</jdbcConnectionString>
|
FEWS 2017 and earlier branches
Code Block | ||||
---|---|---|---|---|
| ||||
|
...
<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 | ||||
---|---|---|---|---|
| ||||
<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 | ||||
---|---|---|---|---|
| ||||
<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
...