Tables and Views are available in Snowflake.

 

Snowflake: Snowflake is built on top of the Amazon Web Services, Microsoft Azure, and Google cloud infrastructure. There’s no hardware or software to select, install, configure, or manage, so it’s ideal for organizations that don’t want to dedicate resources for setup, maintenance, and support of in-house servers.

But what sets Snowflake apart is its architecture and data sharing capabilities. The Snowflake architecture allows storage and compute to scale independently, so customers can use and pay for storage and computation separately. And the sharing functionality makes it easy for organizations to quickly share governed and secure data in real time

There are 4 Types of Tables in Snowflake:

1.Permanent Table

2.Transient Table

3.Temporary Table

4.External Tables

1.Permanent Table:

·       Permanent table its stores the data permanently

·       Its Availability is until explicitly dropped

·       Time Travel Retention Period is 0 to 1 day (Default is 1 day)

·       It’s Fail safe period is 7days

·       Permanent table can be accessible across the any session.

In order to create a Tables, we need to check warehouse, schema and database.

 Table creation:

CREATE OR REPLACE table perm_table(

id number,

dt DATETIME DEFAULT (Current_timestamp)

)

inserting the records:

insert into perm_table(id)

values(11),

(12),

(13),

(14),

(15)

select * from perm_table;

Here is the attachment for creating table and insert the records into permanent table:

Here is attachment for if we execute the code after the session the data saved permanently as shown in below fig...

Transient Table:

Transients table is same as Permanent tables.

·       Its Availability is until explicitly dropped

·       Time Travel Retention Period is 0 to 1 day (Default is 1 day)

·       Its Fail-safe period is 7Days.

·       Transients table can be accessible across the any session.

TIME TRAVEL &FAIL SAFE: These two are mechanism, which is provided by the Snowflake for data retention and also to retrieve the date which are accidently deleted.

 

 

Here is the attachment for TRANSIENT TABLE and insert the values into it as shown below.

Table creation:

CREATE OR REPLACE TRANSIENT TABLE trans(

id number,

dt DATETIME DEFAULT (Current_timestamp)

  )

  

Here is attachment for session if we execute the code after the session the data saved permanently as shown in below fig..

inserting the records:

insert into trans(id)

values(1),

(2),

(3),

(4),

(5)

select * from trans;

3.Temporary Table:

·       Temporary table availability is remainder of session (i.e. Its available for the same session which is it created) we cannot access select or access the temporary table beyond the session.

·       The data is stored only specific session. Once u will come out of the session all the data will be loosed.

·       Its Time Travel Retention period is 0 to 1 day (by default is 1 day)

·       Its Fail-safe period is 0 days.

·       we cannot access select or access the temporary table beyond the session

H Here is the attachment for TEMPORARY TABLE and insert the values into it as shown below.

  Table Creation:

CREATE OR REPLACE Temporary TABLE temp1(

id number,

dt DATETIME DEFAULT (Current_timestamp)

)

inserting the records:

insert into temp1(id)

values(6),

(7),

(8),

(9),

(10)

select * from temp1;

After complete the session the TEMPORARY  table data is not accessible

as shown below showing error message.

4.External tables:

·       External table the Data stored a   file in external stage.

·       External table stored file level metadata like filename, version, identifier, and related properties.

·       External tables are read only which means we cannot perform any DML operations like we can’t update, delete any records.

·       External tables only to use the query the data we can execute a select statement on these tables we can use these table by using JOIN operations (which means v can join between any two table like one is internal table another one is external table we can use these external tables for join conditions)

·       However, Views can be created against external tables.

·       However, we can create a materialize view in these external tables which will help in improving the performance of the external tables.

Here is the attachment for to find the access key and secret key as shown below

Create a stage by using AWS access key and secret key credentials and file location in s3 bucket as shown below.

Stage Creation:

create or replace stage sfuser_ext_stage

url = 's3://snowflakeempty1/'

credentials=(aws_key_id='AKIAYMFP4KTSRP4UD7UE' aws_secret_key='elnkoDR6QRb+pXnSnzJrbyyf/yhYgU1hlnAgJ4rE');

Create External table with csv data as shown below :

Table creation:

create or replace external table ext_tbl_userdata1

with location = @sfuser_ext_stage

file_format = (type = parquet);

Here is the output screenshot:

Select statement:

select * from ext_tbl_userdata1;

View: view is a simplistic query or simplistic thing which we can create on top of the table.

View allows the result of a query to be accessed as if it were a table. Views server a variety of purposes, including combining, segregating and protecting data.

 

Views are 3types:

1.Non-Materialized view (Views)

2.Secured View

3.Materialized View

1.Non-Materialized view(views):

·       A non-materialized view’s results are created by executing the query at the time that the view is referenced in a query

·       The results are not stored for future use.

·       Performance is slower than with materialized views.

·       when we create a view to execute the query its take very less time to execute (i.e. within a seconds).

Here is the attachment for creating a view as shown below.

View Creation:

CREATE VIEW test_view_creation.public.date_wise_orders

AS

SELECT L_COMMITDATE AS ORDER_DATE,

SUM(L_QUANTITY) AS TOT_QTY,

SUM(L_EXTENDEDPRICE) AS TOT_PRICE

FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM

GROUP BY L_COMMITDATE;

Here is the output attachment for the created view.

Select statement:

SELECT * FROM test_view_creation.public.date_wise_orders;

2.Secure View:

·       Secure view is nothing but more or less data privacy (specific people to access the data).

·       Both non-materialized and materialized views can be defined as Secure.

·       Secure view has advantage over standard views, including improved data privacy and data sharing, however some performance impact to take into consideration.

Here is the attachment for creating secure view as shown below:

Secure view Creation:

CREATE Secure VIEW test_view_creation.public.date_wise_orders_fast

AS

SELECT L_COMMITDATE AS ORDER_DATE,

SUM(L_QUANTITY) AS TOT_QTY,

SUM(L_EXTENDEDPRICE) AS TOT_PRICE

FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM

GROUP BY L_COMMITDATE;

Here is the attachment for output screenshot for secured view.

Select statement:

SELECT * FROM test_view_creation.public.date_wise_orders_fast1;

3.Materialized view:

·       Although a materialized view is named as though it were a type of view in many ways it behaves more like a table.

·       materialized views created in the database as an object, it has its own size, it has the data maintained for the materialize views.

·       A materialized view’s results are stored almost as though the results were a table.

·       This allows faster access, but requires storage space and active maintenance both of which incur additional costs.

·       when we create a materialized  view to execute the query its take time to execute between  2-3 min depends on data size.

Here is the attachment for created materialized view as shown below.

Materialized view creation:

CREATE materalized VIEW test_view_creation.public.date_wise_orders_fast

AS

SELECT L_COMMITDATE AS ORDER_DATE,

SUM(L_QUANTITY) AS TOT_QTY,

SUM(L_EXTENDEDPRICE) AS TOT_PRICE

FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1000.LINEITEM

GROUP BY L_COMMITDATE;

Here is the output attachment for materialized view

select staement:

SELECT * FROM test_view_creation.public.date_wise_orders_fast1;
 

CREATING VIEW IN EXTERNAL TABLE AS SHOWN BELOW

Creating external table

External table creation:

create or replace external table ext_card_data

with location = @sfuser_ext_stage

file_format = (type = csv)

pattern = '.*[.]csv';

Here creating materialized view on external table as shown below:

Creation a  materialized view  on external table:

CREATE or replace  materialized VIEW date_wise_orders_fast2 as

select  value:c1::string as id,

value:c2::string as lastname

from ext_card_data;

 Here is the output for creating materialized view on external tables as shown below:

select statement:

select * from date_wise_orders_fast2;