Joining cache data with CQs
The standard programming pattern for getting data from a cache is:
type, cache > CQ
Caches are similar to sources and use the same adapters. The type must must correctly describe the data source, with the correct data type for each field or column.
The following is typical TQL for a cache that is loaded from a file on disk:
CREATE TYPE ZipCache_Type( Zip String, City String, State String, LatVal Double, LongVal Double ); CREATE CACHE ZipCache USING FileReader ( directory: 'Samples/PosApp/appData/', wildcard: 'USAddressesPreview.txt', charset: 'UTF-8', blockSize: '64', positionbyeof: 'false' ) PARSE USING DSVPARSER ( columndelimiter: '\t', header: 'true' ) QUERY (keytomap: 'Zip') OF ZipCache_Type; CREATE GenerateWactionContext INSERT INTO PosSourceData SELECT p.MERCHANTID, p.DATETIME, p.AUTHAMOUNT, z.Zip, z.City, z.State, z.LatVal, z.LongVal FROM PosSource_TransformedStream p, ZipCache z WHERE p.ZIP = z.Zip;
GenerateWactionContext
enriches PosSource_TransformedStream
with location information from ZipCache
by matching values in the stream's ZIP
field with values in the cache's Zip
field. (Though the keyword JOIN
does not appear, this is an inner join.) The PosSourceData
WActionStore can then be used to populate maps. To track any un-joinable events, see TrapZipMatchingErrors
in Handling nulls with CQs.
When a cache's source is a database, use the refreshinterval
property to control how often the cache is updated:
CREATE CACHE ZipCache USING DatabaseReader ( ConnectionURL:'jdbc:mysql://10.1.10.149/datacenter', Username:'username', Password:'passwd', Query: "SELECT * FROM ZipData" ) PARSE USING DSVPARSER ( columndelimiter: '\t', header: 'true' ) QUERY (keytomap: 'Zip', , refreshinterval: '60000000') OF ZipCache_Type;
See CREATE CACHE for more details.