Use a Database Output adapter to send data to an external database.
Column | Datatype | Value |
---|---|---|
Symbol | char(4) | not null |
Price | money | not null |
You must also create a unique index named ind1 on Trades (Ts) and grant all permissions on VwapWindow to public.
<Service Name="dbExample" Type="DB"> <Parameter Name="DriverLibrary">esp_db_jdbc_sybase_lib</Parameter> <Parameter Name="Host">mydbserver</Parameter> <Parameter Name="Port">5000</Parameter> <Parameter Name="User">test4</Parameter> <Parameter Name="Password">password</Parameter> <Parameter Name="Database">interpubs</Parameter> <Parameter Name="ConnectString"></Parameter> <Parameter Name="ConnectionPoolSize">-1</Parameter> </Service>
The table is automatically populated with data from the File CSV Input adapter.
The example creates a schema named TradeSchema, followed by an input window named TradeWindow that references TradeSchema.
The example creates an aggregate output window named VwapWindow, in which the volume weighted average price is returned for TradeWindow data. The return values are grouped by Symbol.
CREATE output WINDOW VwapWindow SCHEMA (Symbol STRING, vwap MONEY(2)) PRIMARY KEY DEDUCED AS SELECT TradeWindow.Symbol AS Symbol, ((SUM(TradeWindow.Price * TradeWindow.Volume)) / (SUM(TradeWindow.Volume))) AS vwap FROM TradeWindow GROUP BY TradeWindow.Symbol;
The example attaches a Database Output adapter to VwapWindow. The project server processes date values in date format, which means date values are truncated.
ATTACH OUTPUT ADAPTER dbOutConn1 TYPE db_out TO VwapWindow PROPERTIES service = 'dbExample' , table = 'VwapWindow' , outputBase = FALSE , truncateTable = TRUE , dateFormat = '%Y-%m-%d %H:%M:%S' ,timestampFormat = '%Y-%m-%d %H:%M:%S' , onlyBase = FALSE , batchLimit =1 ;
The example attaches a File CSV Input adapter to TradeWindow to read data from an external source and populate the database you set up as a prerequisite.
ATTACH INPUT ADAPTER csvInConn1 TYPE dsv_in TO TradeWindow PROPERTIES blockSize=1, dateFormat='%Y/%m/%d %H:%M:%S', delimiter=',', dir='../exampledata', expectStreamNameOpcode=false, fieldCount=0, file='stock-trades.csv', filePattern='*.csv', hasHeader=true, safeOps=false, skipDels=false, timestampFormat= '%Y/%m/%d %H:%M:%S';