Writing a simple application using a text editor

When developing a TQL application, add major components (sources, CQs, WActionStores, targets) one step at a time, verifying that the output is correct before moving on to the next component.

Note

This tutorial assumes that Striim is running locally, as described in Install Striim Platform for evaluation purposes or Running Striim as a process, and that you are Using the console in a terminal or command prompt.

Create a sample data set

When initially coding an application, it is best to use a small sample of the data set for testing and debugging. Once you know all the parts of the application are working, you can move on to test it with real data.

Save the following (which are the first three lines of the sample data in the PosDataPreview.csv file for the PosApp sample application) as .../Striim/Samples/simple.csv:

BUSINESS NAME, MERCHANT ID, PRIMARY ACCOUNT NUMBER, POS DATA CODE, DATETIME, EXP DATE, CURRENCY CODE, AUTH AMOUNT, TERMINAL ID, ZIP, CITY
COMPANY 1,D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu,6705362103919221351,0,20130312173210,0916, USD,2.20,5150279519809946,41363,Quicksand
COMPANY 2,OFp6pKTMg26n1iiFY00M9uSqh9ZfMxMBRf1,4710011837121304048,4,20130312173210,0815, USD,22.78,5985180438915120,16950,Westfield

Note that the last line must end with a return.

Acquire the data using a source

As discussed in TQL programming rules and best practices, you must create a component before you can reference it in another component. This means that the first step in writing a TQL application is to create a source.

Save the following as …/Striim/Samples/simple.tql:

CREATE APPLICATION simple;

CREATE source SimpleSource USING FileReader (
  directory:'Samples',
  wildcard:'simple.csv',
  positionByEOF:false
)
PARSE USING DSVParser (
  header:Yes,
  trimquote:false
) OUTPUT TO RawDataStream;

CREATE TARGET SimpleRawOutput
USING SysOut(name:simpleRaw)
INPUT FROM RawDataStream;;

END APPLICATION simple;

The SysOut adapter writes the output to striim-node.log (see Reading log files). Alternatively, you may run Striim as a process (Running Striim as a process), in which case SysOut will write to the server terminal. This is useful for initial development since you can verify that the data is as expected at each step.

Load, deploy, and start simple.tql using the following commands in the console:

@samples/simple.tql;
deploy application simple;
start simple;

You should see the contents of RawDataStream in striim-node.log. Note that if you are running Striim as a process, SysOut output will be written to the terminal running the server process rather than to striim-node.log:

simpleRaw: WAEvent{
  data: ["COMPANY 1","D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu","6705362103919221351","0","20130312173210","0916","USD","2.20","5150279519809946","41363","Quicksand"]
  metadata: {"FileOffset":0,"RecordEnd":268,"RecordOffset":138,"FileName":"simple.csv","RecordStatus":"VALID_RECORD"}
  userdata: null
  before: null
  dataPresenceBitMap: "AAA="
  beforePresenceBitMap: "AAA="
  typeUUID: null
};
simpleRaw: WAEvent{
  data: ["COMPANY 2","OFp6pKTMg26n1iiFY00M9uSqh9ZfMxMBRf1","4710011837121304048","4","20130312173210","0815"," USD","22.78","5985180438915120","16950","Westfield"]
  metadata: {"FileOffset":0,"RecordEnd":401,"RecordOffset":269,"FileName":"simple.csv","RecordStatus":"VALID_RECORD"}
  userdata: null
  before: null
  dataPresenceBitMap: "AAA="
  beforePresenceBitMap: "AAA="
  typeUUID: null
};

The "COMPANY 1" and "COMPANY 2" lines of simple.csv have been converted to events. WAEvent is the Striim type used for FileReader output. The data field for each event is an array containing the comma-delimited fields from the source file. The other fields (metadata, userdata, before, dataPresenceBitMap, beforePresenceBitMap, and typeUUID) are not used by the simple TQL application.

Filter the data with a CQ SELECT statement

The next step is to filter the data.

In simple.tql, replace these lines:

CREATE TARGET SimpleRawOutput
USING SysOut(name:simpleRaw)
INPUT FROM RawDataStream;

with the following:

CREATE TYPE FilteredDataType(
  merchantId String KEY,
  dateTime DateTime,
  amount double,
  zip String
);
CREATE STREAM FilteredDataStream OF FilteredDataType;

CREATE CQ Raw2FilteredCQ
INSERT INTO FilteredDataStream
SELECT data[1],
  TO_DATEF(data[4],'yyyyMMddHHmmss'),
  TO_DOUBLE(data[7]),
  data[9]
FROM RawDataStream;

CREATE TARGET SimpleFilteredOutput
USING SysOut(name:simpleFiltered)
INPUT FROM FilteredDataStream;

This selects four of the fields from the source data, converts the data types as necessary, and discards the remaining data. Save the file, then enter the following commands in the console to stop, undeploy, and drop the old version, then load, deploy, and run the new version (for more information, see Loading and reloading TQL applications during development):

stop simple;
undeploy simple;
drop application simple cascade;
@samples/simple.tql;
deploy application simple;
start simple;

You should see these results in the console:

simpleFiltered: FilteredDataType_1_0{
  merchantId: "D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu"
  dateTime: 1363134730000
  amount: 2.2
  zip: "41363"
};
simpleFiltered: FilteredDataType_1_0{
  merchantId: "OFp6pKTMg26n1iiFY00M9uSqh9ZfMxMBRf1"
  dateTime: 1363134730000
  amount: 22.78
  zip: "16950"
};

Join data from a cache using a CQ

Caches provide data from non-real-time sources. In this case, the cache is a subset of the USAddresses.txt file used by the PosApp sample application.

Save the following as …/Striim/Samples/simplezipdata.txt, being sure to end the last line with a return:

zip	city	state	latVal	longVal
16950	Westfield	PA	41.9193	-77.523
41363	Quicksand	KY	37.5331	-83.3652

In simple.tql, replace these lines:

CREATE TARGET SimpleFilteredOutput
USING SysOut(name:simpleFiltered)
INPUT FROM FilteredDataStream;

with the following:

CREATE TYPE ZipCacheType(
  zip String KEY,
  city String,
  state String,
  latVal double,
  longVal double
);

CREATE CACHE ZipCache using FileReader (
  directory: 'Samples',
  wildcard: 'simplezipdata.txt')
PARSE USING DSVParser (
  header: Yes,
  columndelimiter: '\t',
  trimquote:false
) QUERY (keytomap:'zip') OF ZipCacheType;

CREATE TYPE JoinedDataType(
  merchantId String KEY,
  zip String,
  city String,
  state String,
  latVal double,
  longVal double
);
CREATE STREAM JoinedDataStream OF JoinedDataType;

CREATE CQ JoinDataCQ
INSERT INTO JoinedDataStream
SELECT  f.merchantId,
        f.zip,
        z.city,
        z.state,
        z.latVal,
        z.longVal
FROM FilteredDataStream f, ZipCache z
WHERE f.zip = z.zip;

CREATE TARGET simpleJoinedData
USING SysOut(name:simpleJoined)
INPUT FROM JoinedDataStream;

Reload and restart simple.tql:

stop simple;
undeploy simple;
drop application simple cascade;
@samples/simple.tql;
deploy application simple;
start simple;

You should see these results in the console:

simpleJoined: JoinedDataType_1_0{
  merchantId: "D6RJPwyuLXoLqQRQcOcouJ26KGxJSf6hgbu"
  zip: "41363"
  city: "Quicksand"
  state: "KY"
  latVal: 37.5331
  longVal: -83.3652
};
simpleJoined: JoinedDataType_1_0{
  merchantId: "OFp6pKTMg26n1iiFY00M9uSqh9ZfMxMBRf1"
  zip: "16950"
  city: "Westfield"
  state: "PA"
  latVal: 41.9193
  longVal: -77.523
};

Populate a Dashboard map from a WActionStore

In simple.tql, replace these lines:

CREATE TARGET JoinedDataTarget
USING SysOut(name:JoinedData)
INPUT FROM JoinedDataStream;

with the following:

CREATE JUMPING WINDOW FilteredDataWindow
OVER FilteredDataStream KEEP 1 ROWS;

CREATE WACTIONSTORE MapData 
CONTEXT OF JoinedDataType
EVENT TYPES (JoinedDataType)
PERSIST NONE USING();

CREATE CQ PopulateMapDataCQ
INSERT INTO MapData
SELECT  f.merchantId,
        f.zip,
        z.city,
        z.state,
        z.latVal,
        z.longVal
FROM FilteredDataWindow f, ZipCache z
WHERE f.zip = z.zip; 

CREATE TARGET JoinedDataTarget
USING SysOut(name:JoinedData)
INPUT FROM JoinedDataStream;

Reload and restart simple.tql:

stop simple;
undeploy simple;
drop application simple cascade;
@samples/simple.tql;
deploy application simple;
start simple;

Create a dashboard (for more information, see Dashboard Guide):

From the top menu, select Dashboards > View All Dashboards > Add Dashboard, enter SimpleDash as the name, select the namespace in which you created SimpleApp, and click Create Dashboard. The dashboard's main page appears.
Drag the Vector Map icon into the workspace and drop it in the top left corner.
At the top of the chart, click Edit Query the (< > icon). The query editor opens.
In the Name field, enter SimpleDashQuery. Edit the SELECT statement to read select * from MapData; and click Save Query.
Click Configure (the pencil icon), or double-click anywhere in the chart, specify the properties as follows (for more information, see Visualization types and properties), leave the other properties at their defaults, and click Save visualization.
Click Done editing (the check icon at the top right above the workspace).

You should see something like this:

Completed simple.tql

When you complete all of the previous exercises, your final simple.tql should look like this:

CREATE APPLICATION simple;

CREATE source SimpleSource USING FileReader (
  directory:'Samples',
  wildcard:'simple.csv',
  positionByEOF:false
)
PARSE USING DSVParser (
  header:Yes,
  trimquote:false
) OUTPUT TO RawDataStream;

CREATE TYPE FilteredDataType(
  merchantId String KEY,
  dateTime DateTime,
  amount double,
  zip String
);
CREATE STREAM FilteredDataStream OF FilteredDataType;

CREATE CQ Raw2FilteredCQ
INSERT INTO FilteredDataStream
SELECT data[1],
  TO_DATEF(data[4],'yyyyMMddHHmmss'),
  TO_DOUBLE(data[7]),
  data[9]
FROM RawDataStream;

CREATE TYPE ZipCacheType(
  zip String KEY,
  city String,
  state String,
  latVal double,
  longVal double
);

CREATE CACHE ZipCache using FileReader (
  directory: 'Samples',
  wildcard: 'simplezipdata.txt')
PARSE USING DSVParser (
  header: Yes,
  columndelimiter: '\t',
  trimquote:false
) QUERY (keytomap:'zip') OF ZipCacheType;

CREATE TYPE JoinedDataType(
  merchantId String KEY,
  zip String,
  city String,
  state String,
  latVal double,
  longVal double
);
CREATE STREAM JoinedDataStream OF JoinedDataType;

CREATE CQ JoinDataCQ
INSERT INTO JoinedDataStream
SELECT  f.merchantId,
        f.zip,
        z.city,
        z.state,
        z.latVal,
        z.longVal
FROM FilteredDataStream f, ZipCache z
WHERE f.zip = z.zip;

CREATE JUMPING WINDOW FilteredDataWindow
OVER FilteredDataStream KEEP 1 ROWS;

CREATE WACTIONSTORE MapData 
CONTEXT OF JoinedDataType
EVENT TYPES (JoinedDataType)
PERSIST NONE USING();

CREATE CQ PopulateMapDataCQ
INSERT INTO MapData
SELECT  f.merchantId,
        f.zip,
        z.city,
        z.state,
        z.latVal,
        z.longVal
FROM FilteredDataWindow f, ZipCache z
WHERE f.zip = z.zip; 

CREATE TARGET JoinedDataTarget
USING SysOut(name:JoinedData)
INPUT FROM JoinedDataStream;

END APPLICATION simple;

Writing a simple application using a text editor

Note

Create a sample data set

Acquire the data using a source

Filter the data with a CQ SELECT statement

Join data from a cache using a CQ

Populate a Dashboard map from a WActionStore

Completed simple.tql

Search results