Post
Ask Question

Views In Snowflake

Introduction

 
As we know, View is just a group of SQL statement which holds the data from one or more than one table. It is also called virtual table. So far, we know that View can contain rows and columns only, but View is different in Snowflake. In Snowflake, there are two types of Views.
  1. Non-Materialized view, also referred to as simply View
  2. Materialize View
We can also create a secure view by just adding secure keywork while creating Non-Materialized or Materialized View.
 
In this article, we are working with Non-Materialized View, and you will learn about creating Views to populate json data from one and more than one table. This real time scenario we are mostly using when we use View as API. I have also implemented the stored procedure in Snowflake to achieve the real-time scenario.
 
You can have a look at the article.
 
To work on Views, I have three problem statements here,
  1. Create View which will return table data in the form of rows and columns.
  2. Create View which will return the data in Json format
  3. Create View which will return the data from two tables.
As we know that the snowflake query will be executed under the context and Context will have four major things like Role, warehouse, database, and schema, I have already created warehouse, database, and schema. In our scenario, I will be using the following context,
  • Role-sysadmin
  • Warehouse-compute_wh
  • Database-employeemanagement
  • Schema-EM
Let us set up the required table first.
 
Step 1
 
Set up the context. Execute the following query to set up the context.
  1. use role sysadmin;    
  2. use warehouse compute_wh;    
  3. use database employeeManagement;    
  4. use schema EM;    
Step 2
 
Create a table called employee. Execute the below query.
  1. create or replace table employee(emp_id int, emp_name varchar,emp_address varchar);  
Step 3
 
Insert the record into employee table
  1. insert into employee values(1,'Nitesh','Hyderabad');  
  2. insert into employee values(2,'Sam','Hyderabad');  
  3. insert into employee values(3,'Kam','Pune');  
Step 4
 
Create employee_skill table. Perform the below query
  1. create or replace table employee_skill(  
  2.    skill_id number,  
  3.    emp_id number,  
  4.    skill_name varchar(50),  
  5.    skill_level varchar(50)  
  6. );  
Step 5
 
Insert a few records. Execute the below query.
  1. insert into employee_skill values(1,1,'C#','Advance');  
  2. insert into employee_skill values(2,1,'Python','Basic');  
  3. insert into employee_skill values(3,1,'SQL','Intermediate');  
  4. insert into employee_skill values(1,2,'C#','Advance');  
Please check the inserted data by running the select command
 
Step 6
 
Execute the below query to verify the data
  1. Select * from employee;  
Views In Snowflake
 
Step 7
 
Execute the below query to verify the data
  1. Select * from employee_skill;  
Views In Snowflake
 
Now, we have set up all the requirements. Let’s go ahead and work on the problem statement.
 

Scenario 1

 
Create View which will return table data in the form of rows and columns.
 
Solution
 
This is the normal scenario, where we create a view to populate data in the form of rows and columns.
 
Step 1
 
Create the View by running the below query
  1. create or replace view employee_view  
  2. as select emp_id,emp_name, emp_address from employee;  
Step 2
 
Run the View by executing the below query
  1. Select * from employee;   
Views In Snowflake
 

Scenario 2

 
Create View which will return the data in Json format
 
Solution
 
In this problem state, we use the object_construct() and array_agg() function to populate data in Json format.
 
a. Object_construct() - It is a semi-structure data function. It returns the object is constructed from an argument. The data will be in key-value format. The result will be omitted if the key or value is NULL.
b. Array_agg() - It is an aggregate function for semi-structured data. It returns the input value, pivoted into array.
 
The output of the view will be in Json format and in this example, we will have the list of employees in Json object.
 
Step 1
 
Create json view with object_construct function
  1. CREATE OR replace VIEW employee_json_view   
  2. AS   
  3.   WITH dc   
  4.        AS (SELECT Object_construct('employee_list', Array_agg(Object_construct(   
  5.                                                               'emp_id',   
  6.                                                               emp_id,   
  7.                                                               'emp_name',   
  8.                                                                  emp_name,   
  9.                                                               'emp_address'   
  10.                                                               ,   
  11.                                                                  emp_address)))   
  12.            FROM   employee)   
  13.   SELECT *   
  14.   FROM   dc;   
Step 2
 
Execute the select query to verify the data.
  1. select * from employee_json_view;  
Views In Snowflake
 
Step 3
 
Click on result Row. You will see the Json format data.
 
Views In Snowflake
 

Scenario 3

 
Create View which will return the data from two tables.
 
Solution
 
In this problem statement, I have tried to implement the real-time scenario where we have an employee with their number of skills. The data of employee is stored in the two tables. The first table will have data of employee and another table will have skill data for that employee. Normally, we use join to populate data if you want in the form of rows and columns. But in our case, it is a little bit different. Again we will be using object_construct() and array_agg() function to populate data in Json format.
 
Step 1
 
Create the View as per the below query.
  1. CREATE OR replace VIEW employee_skill_json_view   
  2. AS   
  3.   WITH dc   
  4.        AS (SELECT emp_id,   
  5.                   Array_agg(Object_construct('skill_id', skill_id, 'skill_name',   
  6.                             skill_name,   
  7.                             'skill_level',   
  8.                                       skill_level)) skills   
  9.            FROM   employee_skill   
  10.            GROUP  BY emp_id),   
  11.        es   
  12.        AS (SELECT Object_construct('result_set', Array_agg(Object_construct(   
  13.                                                            'emp_id', e.emp_id,   
  14.           'emp_name', e.emp_name,   
  15.           'emp_address',   
  16.                     e.emp_address,   
  17.           'employee_skill',   
  18.                     dc.skills)))   
  19.            FROM   employee e   
  20.                   inner join dc   
  21.                           ON e.emp_id = dc.emp_id)   
  22.   SELECT *   
  23.   FROM   es;   
Step 2
 
Execute the select query to verify the view data
  1. Select * from employee_skill_json_view;   
Views In Snowflake
 
Step 3
 
Click on the Result Row to see the details
 
Views In Snowflake
 

Conclusion

 
In Snowflake, we can use View for API purposes and populate the data in Json format as per our requirement. We can pull data from one or more than one table.
 
I hope you understand the use of Snowflake View with Json result.
 
Happy Learning!


Similar Articles