Snowflake Time Travel Working Session - Lab Queries in Description
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- You can get all snowflake Videos, PPTs, Queries, Interview questions and Practice files in my Udemy course for very less price.. I will be updating this content and will be uploading all new videos in this course.
My Snowflake Udemy Course:
www.udemy.com/...
I can be reachable on jana.snowflake2@gmail.com.
------------------------------------------------------------
// where can you see the retention period?
SHOW TABLES in SCHEMA myown_db.public;
SHOW SCHEMAS in DATABASE myown_db;
SHOW DATABASES;
// how to set this at the time of table creation
CREATE OR REPLACE TABLE myown_db.public.timetravel_ex(id number, name string);
SHOW TABLES like 'timetravel_ex%';
CREATE OR REPLACE TABLE myown_db.public.timetravel_ex(id number, name string)
DATA_RETENTION_TIME_IN_DAYS = 10;
SHOW TABLES like 'timetravel_ex%';
// setting at schema level
CREATE SCHEMA myown_db.abcxyz DATA_RETENTION_TIME_IN_DAYS = 10;
SHOW SCHEMAS like 'abcxyz';
CREATE OR REPLACE TABLE myown_db.abcxyz.timetravel_ex2(id number, name string);
SHOW TABLES like 'timetravel_ex2%';
CREATE OR REPLACE TABLE myown_db.abcxyz.timetravel_ex2(id number, name string) DATA_RETENTION_TIME_IN_DAYS = 20;
SHOW TABLES like 'timetravel_ex2%';
// dont forget to change your schema back to public on right top corner
// how to alter retention period later?
ALTER TABLE myown_db.public.timetravel_ex
SET DATA_RETENTION_TIME_IN_DAYS = 15;
SHOW TABLES like 'timetravel_ex%';
// Querying history data
// Updating some data first
// Case1: update some data in customer table
SELECT * FROM myown_db.public.customer;
SELECT * FROM myown_db.public.customer WHERE CUSTOMERID=1682100334099;
UPDATE myown_db.public.customer SET CUSTNAME='ABCXYZ' WHERE CUSTOMERID=1682100334099;
SELECT * FROM myown_db.public.customer WHERE CUSTOMERID=1682100334099;
=============
// Case2: delete some data from emp_data table
SELECT * FROM myown_db.public.emp_data;
SELECT * FROM myown_db.public.emp_data where id=1;
DELETE FROM myown_db.public.emp_data where id=1;
SELECT CURRENT_TIMESTAMP; -- 2022-07-08 19:47:48.916
SELECT * FROM myown_db.public.emp_data where id=1;
==============
// Case3: update some data in customer table
SELECT * FROM myown_db.public.orders;
SELECT * FROM myown_db.public.orders WHERE ORDER_ID='B-25601';
UPDATE myown_db.public.orders SET AMOUNT=0 WHERE ORDER_ID='B-25601'; -- 01a57b69-0004-25d4-0015-ab8700024536
SELECT * FROM myown_db.public.orders WHERE ORDER_ID='B-25601';
==============
// Case1: retrieve history data by using AT OFFSET
SELECT * FROM myown_db.public.customer WHERE CUSTOMERID=1682100334099;
SELECT * FROM myown_db.public.customer AT (offset = -60*5)
WHERE CUSTOMERID=1682100334099;
// Case2: retrieve history data by using AT TIMESTAMP
SELECT * FROM myown_db.public.emp_data where id=1;
SELECT * FROM myown_db.public.emp_data AT(timestamp = '2022-07-08 19:47:48.916'::timestamp)
WHERE id=1;
// Case3: retrieve history data by using BEFORE STATEMENT
SELECT * FROM myown_db.public.orders WHERE ORDER_ID='B-25601';
SELECT * FROM myown_db.public.orders
before(statement = '01a57b69-0004-25d4-0015-ab8700024536')
WHERE ORDER_ID='B-25601';
CREATE TABLE myown_db.public.orders_tt
AS
SELECT * FROM myown_db.public.orders
before(statement = '01a57b69-0004-25d4-0015-ab8700024536');
SELECT * FROM myown_db.public.orders WHERE ORDER_ID='B-25601';
SELECT * FROM myown_db.public.orders_tt WHERE ORDER_ID='B-25601';
=================
// Restoring Tables
SHOW TABLEs like 'customer%';
DROP TABLE myown_db.public.customer;
SHOW TABLEs like 'customer%';
UNDROP TABLE myown_db.public.customer;
SHOW TABLEs like 'customer%';
// Restoring Schemas
SHOW SCHEMAS in DATABASE myown_db;
DROP SCHEMA STAGE_TBLS;
SHOW SCHEMAS in DATABASE myown_db;
UNDROP SCHEMA STAGE_TBLS;
SHOW SCHEMAS in DATABASE myown_db;
====================
// Time Travel Cost
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS;
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE TABLE_NAME = 'CUSTOMER_LARGE';
SELECT ID,
TABLE_NAME,
TABLE_SCHEMA,
TABLE_CATALOG,
ACTIVE_BYTES / (1024*1024*1024) AS STORAGE_USED_GB,
TIME_TRAVEL_BYTES / (1024*1024*1024) AS TIME_TRAVEL_STORAGE_USED_GB
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE TABLE_NAME = 'CUSTOMER_LARGE'
ORDER BY STORAGE_USED_GB DESC,TIME_TRAVEL_STORAGE_USED_GB DESC;
Super Anna... Explanation with practicals is really awesome
I am confusing about what is the time travel ,data retention and fail safe ,iam searching lot of platforms no one can clear about this.
This video most helpful for me ,my doubt is clear ,lot of thanks please upload more videos ❤️
Thank you sir
Can u make video on snowflake stream and task . What is the purpose of steam and task. how it will perform they internal operations.
sure
superb !!
Superrrr
If we do truncate also we can do timetravel , where exactly it will get data
Yes
Sir I have one question:how time tavel its work internally
It maintains multiple copies of changes occuring to that table, so if you use larger retention period, the cost will be more
what are the basics and advanced things in snowflake for learning ....
Call me or send me email, we can discuss
@@mrjana520 can I have your contact ?
Hi sir need your number to contact
I understood that undrop is similar to rollback in traditional databases. Is there any command similar to commit in snowflake?
Undrop is not at all similar to rollback, and there is auto commit in Snowflake
@@mrjana520 Thanks for sharing the valuable info bro
Can I have some interview questions with answers please provide
Please send me anna
Uploaded 50+ snowflake interview questions in my channel, you can watch them..
Hi Sir, I had an interview with Wipro andi very recently they asked me two questions. One is how can we remove the files in s3 bucket coz if they are available we need to pay credits rt? also second question how we will know once the files are available in s3 buckets? plz help with answers
For you first question, watch Copy Options video from my playlist, we have to use PURGE option to delete the files once processing is completed.
For 2nd question watch Snowpipe video, we need to create notifications at aws side.
SIR OKA CHINNA DOBUT DATABASE KI RETENTION TIME 5 DAYS ICHI ANDULO UNNA TABLES KI RETENTION TIME 10 DAYS ISTHE DATABASE DROP CHESI 5 DATINA TARWATHA ANDULO UNNA TABLES NI RETRIVE CHEYACHA TABLES KI 10 DAYS UNTADI KADA RETENTION TIME.LEDA DABASE KI 5 DAYS YE KABTTI RETENTION TIME ANDULO UNNA TABLES KUDA 5 DAYS LO NE RETRIVE CHEYLNA?
Currently, when a database is dropped, the data retention period for child schemas or tables, if explicitly set to be different from the retention of the database, is not considered. The child schemas or tables are retained for the same period of time as the database.
Similarly, when a schema is dropped, the data retention period for child tables, if explicitly set to be different from the retention of the schema, is not considered. The child tables are retained for the same period of time as the schema.
To consider the data retention period for these child objects (schemas or tables), drop them explicitly before you drop the database or schema.
@@mrjana520 thank you very much sir meeru chala help chestunnaru .. chala baga ardham ayyelaga cheptunnaru .. evaru intha clear ga cheppadam nenu ekkada chudaledu chala thanks 👍
Hi Sir,
How to cancel the query execution.!
Cancel button is there right
Thanks sir
HI Sir, w.r.t timestamp option is it mandate to take the timestamp from system or approx time also will do i think. Also what exactly the values or numbers appearing in timestamp post date and time.pls let me know the significance of this.
Thank you in advance.
That last part of timestamp is micro seconds and it is not mandatory to mention micro seconds