Types of Views in Databricks|and their Difference-Datacloudy

In this blog, we are going to see about views in Databricks and its types in crisp and clear manner.    Before jumping into views and types of views in Databricks, let us see what is view in sql.

Views in SQL are like virtual tables. A view has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condition.

And the above definition is same for Databricks views as well. Now let us see about the types of views in Databricks.

Types of views in Databricks:

    i) Standard view

    ii) Temporary view

    iii) Global Temporary view

Now lets see about each views in brief manner and i hope this will make you to remember the difference between the view type.

    i) Standard view

• It is same as the views in general database.
  
  
• It is persistent, that means it is available even after the restart of the cluster.
  
  

CREATE VIEW view_name AS

select * from table

             The above example is nothing but we are created the view with the data in the table. In the place of "view_name" you can provide your name of the view and in the place of "table", you can give the name of the table.  You can also give the select sql query that fetch the data for the view 

• You can see the created view using SHOW Tables command

     ii) Temporary view

• As name suggests it is a view and that is temporary.
  
  
• It is available only to a session , that means its scope is within the created session.
  
  

CREATE TEMPORARY VIEW view_name AS

select * from table

 

• You can see the created view using SHOW Tables command. In the result of this comment you will see a column "isTemporary" and the value will be true for the created temp view.

    iii) Global Temporary view

• It is available as long as the cluster is running.
  
  
• And the scope is that it is available for all the session of the cluster. So it is global for all the sessions and similar to standard view but global temporary view is not persistent that means it is temporary.
  
  

CREATE GLOBAL TEMPORARY VIEW global_temp_viewname  AS

select * from table

                 In the place of "global_temp_viewname" you can provide your name of the global temp view and in the place of "table", you can give the name of the table.  You can also give the select sql query that fetch the data for the view. 

• To use this view we cannot directly access that is, select * from global_temp_viewname ,this wont work. So we have to do as shown below
  
  

select * from global_temp.global_temp_viewname

 

• You can see the created view using SHOW TABLES IN global_temp command. This will contain global as well as all temp views.

Hope this blog might have given you a clear idea about Databricks views and its types. If you like this blog please follow this blog by clicking the follow button on the top right side of this screen. You can also follow us in social media by clicking on social media plugin given above the follow button.

Thank you !!!