Hazelcast SQL GENERATE_STREAM + IMap JOIN

Örnek

Şöyle bir VIEW yaratırız.

CREATE OR REPLACE VIEW orders AS
  SELECT id,

       CASE WHEN orderRand BETWEEN 0 AND 0.2 THEN '11001'
            WHEN orderRand BETWEEN 0.2 AND 0.4 THEN '11002'
            WHEN orderRand BETWEEN 0.4 AND 0.6 THEN '11003'
            WHEN orderRand BETWEEN 0.6 AND 0.8 THEN '11004'
            ELSE '11005'
       END as cust_id,

       CASE WHEN orderRand*1.5 BETWEEN 0 AND 0.25 THEN '501'
            WHEN orderRand*1.5 BETWEEN 0.25 AND 0.5 THEN '502'
            WHEN orderRand*1.5 BETWEEN 0.5 AND 0.75 THEN '503'
            ELSE '504'
       END as item_num,

       CASE WHEN orderRand*.66 BETWEEN 0 AND .25 THEN CAST(1 AS SMALLINT)
            WHEN orderRand*.66 BETWEEN .25 AND .5 THEN CAST(2 AS SMALLINT)
            ELSE CAST(3 AS SMALLINT)
       END as quantity,
       order_ts
FROM
    (SELECT v as id,
           RAND(v*v*v) as orderRand,
           TO_TIMESTAMP_TZ(v*10 + 1645484400000) as order_ts
     FROM TABLE(generate_stream(7)));

Şöyle bir customers IMap yaratırız ve doldururuz

CREATE or REPLACE MAPPING customers (
     __key BIGINT,
     cust_id VARCHAR,
     last_name VARCHAR,
     first_name VARCHAR,
     address1 VARCHAR,
     address2 VARCHAR,
     phone VARCHAR )
TYPE IMap
OPTIONS (
'keyFormat'='bigint',
'valueFormat'='json-flat');

// Populate the customers map with data.

SINK INTO customers VALUES
(1,'11001', 'Smith', 'John', '123 Main St', 'Ames, IA 50012', '515-555-1212'),
(2,'11002', 'Li', 'Guo', '456 Powell St', 'San Francisco, CA 94108', '415-555-1212'),
(3,'11003', 'Ivanov', 'Sergei', '999 Brighton Blvd', 'New York, NY 11235','212-555-1212'),
(4,'11004', 'Mohammed', 'Ibrahim', '42 Elm St', 'Dearborn, MI 48126', '313-555-1212'),
(5,'11005', 'Patel', 'Ram', '5151 Market St', 'Trenton, NJ 08615', '609-555-1212');

Şöyle bir inventory IMap yaratırız ve doldururuz

CREATE or REPLACE MAPPING inventory (
     __key BIGINT,
     item_num VARCHAR,
     unit_price DECIMAL,
     quantity SMALLINT)
TYPE IMap
OPTIONS (
'keyFormat'='bigint',
'valueFormat'='json-flat');
SQL
Populate the inventory database.

SINK INTO inventory VALUES
(1, '501', 1.99, 500),
(2, '502', 3.99, 500),
(3, '503', 5.99, 500),
(4, '504', 7.99, 500);

 Hedef IMap'i yaratırız

CREATE OR REPLACE MAPPING PickOrder (
   __key BIGINT,
   ts TIMESTAMP,
  item_num VARCHAR,
  quantity SMALLINT,
  cust_id VARCHAR,
  last_name VARCHAR,
  first_name VARCHAR,
  address1 VARCHAR,
  address2 VARCHAR,
  phone VARCHAR)
TYPE IMap
OPTIONS (
'keyFormat'='bigint',
'valueFormat'='json-flat');

Bunu dolduracak iş için şöyle yaparız. Gelen order ile ilişkili customer ve inventory bulunur

CREATE JOB PickOrder AS
SINK INTO PickOrder
     SELECT
          ord.id AS __key,
          ord.order_ts AS ts,
          ord.item_num AS item_num,
          ord.quantity AS quantity,
          ord.cust_id AS cust_id,
          cust.last_name AS last_name,
          cust.first_name AS first_name,
          cust.address1 AS address1,
          cust.address2 AS address2,
          cust.phone AS phone
     FROM orders AS ord
     JOIN customers AS cust ON ord.cust_id = cust.cust_id
     JOIN inventory ON ord.item_num = inventory.item_num
     WHERE ord.quantity < inventory.quantity;