You can translate the question and the replies:

Need help on incremental caching

Being new to Denodo , need help to implement the below situation. INVOLVED TABLES -------------------------- Customers table is static. Orders table is incremental. VIEW DEFINITION ----------------- CREATE VIEW customers_j_orders AS SELECT customers.customernumber AS customernumber, customers.customername AS customername, customers.contactlastname AS contactlastname, customers.contactfirstname AS contactfirstname, customers.country AS country, orders.ordernumber AS ordernumber, cast(orders.orderdate AS VARCHAR) AS orderdate, orders.status AS status FROM customers AS customers INNER JOIN orders AS orders ON customers.customernumber = orders.customernumber WHERE orders.status <> 'Shipped'; VIEW INITIALIZATION SCRIPT ---------------------------- Select * from customers_j_orders CONTEXT ( 'cache_preload'='true'); INTENDED INCREMENTAL FLOW Summary : [Select max of the order date from the existing view and bring in all orders with order date greater than that into the incremental cache ] PROPOSED INCREMENTAL QUERY : SELECT customers.customernumber AS customernumber, customers.customername AS customername, customers.contactlastname AS contactlastname, customers.contactfirstname AS contactfirstname, customers.country AS country, orders.ordernumber AS ordernumber, cast(orders.orderdate AS VARCHAR) AS orderdate, orders.status AS status FROM customers AS customers INNER JOIN (Select * from Orders where orders.order_date>(Select max(order_date ) from v_Orders_Join_Customers) ) as orders ON customers.customernumber = orders.customernumber WHERE orders.status <> 'Shipped'; How to get this incremental dataset inserted into the cache table at an execution interval of 1 hour. Unable to get this implemented with Denodo. Please help.
user
27-08-2015 07:33:39 -0400
code

3 Answers

Hi, To load the data into cache incrementally you can add the below context clause to your query: ('cache_preload'='true', 'cache_invalidate'='matching_rows'): You can then use the Denodo Scheduler to schedule a VDPCache job that loads data into cache every 1 hour. Check out the section "Loading the Cache Incrementally" in the Virtual DataPort Administration Guide and the section "CONFIGURING NEW JOBS" in the Scheduler Administration Guide. Hope this helps.
Denodo Team
27-08-2015 12:22:26 -0400
code
Thanks for your response. I do not want to invalidate any records from the cache. Delta records will always be appended to the cache. I tried writing a scheduler job with the extraction as : SELECT customers.customernumber AS customernumber, customers.customername AS customername, customers.contactlastname AS contactlastname, customers.contactfirstname AS contactfirstname, customers.country AS country, orders.ordernumber AS ordernumber, cast(orders.orderdate AS VARCHAR) AS orderdate, orders.status AS status FROM customers AS customers INNER JOIN Orders ON customers.customernumber = orders.customernumber WHERE orders.orderdate>@orderdate But it says that the query is not correct. Does it not support a join condition here ? If this is achievable then I can create another job to find the value of @orderdate Please help.
user
27-08-2015 16:18:35 -0400
Hi, You can first create a view in VDP by issuing the following query, CREATE OR REPLACE VIEW customer_j_order AS (SELECT customers.customernumber AS customernumber, customers.customername AS customername, customers.contactlastname AS contactlastname, customers.contactfirstname AS contactfirstname, customers.country AS country, orders.ordernumber AS ordernumber, Cast(orders.orderdate AS VARCHAR) AS orderdate, orders.status AS status FROM customers AS customers INNER JOIN orders ON customers.customernumber = orders.customernumber) Then select this newly created view and specify the conditions in the 'Query conditions:' as, orders.orderdate > @orderdate After which you can create the 'New Source' for the orderdate variable. Check out the section 'VDPCache Extraction Section' in the Scheduler Administration Guide to know more about this. Hope this helps.
Denodo Team
28-08-2015 08:24:29 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here