During this post we will discuss about the latest feature i.e., STREAMS on VIEWS. Consider the scenario you have a regular View in your database which is based on multiple tables. Due to security compliance these tables are not exposed to the Custom Role in system. Indeed, the Custom role has access to VIEW only. As part of the daily load, we are getting DELTA data in our tables which includes deletion and insertion of new records. These changes are readily available to the VIEW based on these underlying tables. Currently there is no way to track if the record has been deleted or inserted into the respective tables. The VIEW displays only the latest information with no audit of any changes. As per the business requirement we need to keep track of any DML operations performed on underlying tables and which reflects on to the VIEW. So in short need to audit the VIEWS and synch the changes to final table for history purpose.
So the requirement is:
- Auditing should be available on the VIEW and keep track the DML operations performed on VIEW or indirectly to the underlying tables.
- There is a target table with similar structure of VIEW in addition of three attributes:.
STARTDATE, ENDDATE, ACTIVE_FLG to track the changes.
Say we have PROD_TBL and ACCESSORY_DETAILS table in our database.
We have created the below VIEW on these tables.
CREATE OR REPLACE VIEW PROD_ACCESSORY_VIEW AS
FROM PROD_TBL A ,ACCESSORY_DETAILS B
WHERE A.CUST_NUM = B.CUST_ID;
Now Create a Stream on the VIEW to track the changes.
create or replace stream VIEW_STREAM on view PROD_ACCESSORY_VIEW;
Create the Target table similar to VIEW with 3 additional columns. This will keep the history of all the records which are modified or deleted in the VIEW.
CREATE OR REPLACE TABLE PROD_ACCESSORY_TABLE (
CUST_NUM STRING,CUST_BAL decimal(10, 2),
INV_NO varchar(50),INV_AMT decimal(10, 2),
CRID STRING,SSN STRING,
ACCESSORY STRING,STATUS STRING,
ACC_AMT decimal(10, 2),startdate date,
enddate date,active_flg char(1)
Now populate the data to both tables and verify the records in VIEW.
Importantly observe the STREAM an d it will be having the same 75 records.
Now finally merge these changes to the target table using below SQL:
merge into PROD_ACCESSORY_TABLE T1
using(select * from VIEW_STREAM) T2
on T1.CUST_NUM = T2.CUST_NUM
when matched and (T2.METADATA$ACTION='DELETE') THEN
update set ENDDATE = TO_TIMESTAMP_NTZ(CURRENT_TIMESTAMP) , active_flg = 'N'
when not matched and (T2.METADATA$ACTION='INSERT') THEN insert (CUST_NUM,CUST_BAL,INV_NO,INV_AMT,CRID,SSN,ACCESSORY,STATUS,ACC_AMT,STARTDATE,ENDDATE,active_flg)
Assume on next day ,Customer and Accessory details got loaded into the tables and will see how STREAM would behave for this DELTA record.
Lets Insert one record in underlying table and observe the STREAM. If the Joining conditions satisfies for the VIEW then STREAM would capture the new record details.
INSERT INTO PROD_TBL VALUES ('C-1000','Active',3000,'I-1000',200,'ABCD','9876','7677777777','Cfirstname.lastname@example.org');
INSERT INTO ACCESSORY_DETAILS VALUES ('C-1000','Ink','In-use',200,'Y');
SELECT * FROM VIEW_STREAM;
Note: Say you have inserted the data in underlying tables that does not satisfy the VIEW join condition then STREAM does not capture this record.
Suppose if we insert below record :
INSERT INTO PROD_TBL VALUES ('C-1001','Active',3000,'I-1000',200,'ABCD','9876','7677777777','Cemail@example.com')
Then STREAM would not capture this details.
Run the merge statement:
Once the changes are consume ,STREAM gets empty.
SELECT * FROM VIEW_STREAM; output 0 records
Verify the data inside the target table and View and hence new record gets ingested.
Now Try to delete the data from any of the table say PROD_TBL:
Delete from PROD_TBL where cust_num = 'C-101';
This has populated the stream in below way:
Run the Merge statement and it will update the Enddate and status in target table.
To get more details about the Streams on Views follow the below documentation link: