Skip to main content

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.