Databricks Views

Introduction

Databricks is a powerful data analytics platform built on Apache Spark, offering robust capabilities for big data processing, machine learning, and data engineering. Among its many features, Views play a crucial role in simplifying data access and transformation. This article explores Databricks Views, their types, creation, management.

What Are Databricks Views?

A View in Databricks is a virtual table that represents the result of a SQL query. It does not store data physically but allows users to access and manipulate data efficiently. Views provide a way to encapsulate complex queries and improve reusability.

For our examples, we will use a smartphones table containing information about various smartphone brands and models. Below is the sample data:

CREATE TABLE smartphones (
    id INT,
    brand STRING,
    model STRING,
    year INT
);

INSERT INTO smartphones VALUES
(1, 'Apple', 'iPhone 12', 2020),
(2, 'Apple', 'iPhone 13', 2021),
(3, 'Samsung', 'Galaxy S21', 2021),
(4, 'OnePlus', 'OnePlus 9', 2021),
(5, 'Apple', 'iPhone 14', 2022);

Querying the table:

SELECT * FROM smartphones;

Result:

+----+-----------+-----------+------+
| id | brand     | model     | year |
+----+-----------+-----------+------+
| 1  | Apple     | iPhone 12 | 2020 |
| 2  | Apple     | iPhone 13 | 2021 |
| 3  | Samsung   | Galaxy S21| 2021 |
| 4  | OnePlus   | OnePlus 9 | 2021 |
| 5  | Apple     | iPhone 14 | 2022 |
+----+-----------+-----------+------+

Types of Views in Databricks

Databricks supports three types of views:

1. Stored Views
2. Temporary Views
3. Global Temporary Views

1. Stored Views

• Persisted objects that remain in the database until explicitly dropped.
• Useful for frequently accessed queries.
• Created using the CREATE VIEW statement.

CREATE VIEW view_apple_phones 
AS SELECT * FROM smartphones
WHERE brand='Apple';

SELECT * FROM view_apple_phones;

+----+-------+-----------+------+
| id | brand | model     | year |
+----+-------+-----------+------+
| 1  | Apple | iPhone 12 | 2020 |
| 2  | Apple | iPhone 13 | 2021 |
| 5  | Apple | iPhone 14 | 2022 |
+----+-------+-----------+------+

2. Temporary Views

• Exist only within the current session.
• Are not accessible to other users or sessions.
• Automatically deleted when the session ends.

CREATE TEMP VIEW view_apple_phone_brands
AS SELECT DISTINCT brand FROM smartphones;

SELECT * FROM view_apple_phone_brands;

+---------+
| brand   |
+---------+
| Apple   |
| Samsung |
| OnePlus |
+---------+

3. Global Temporary Views

• Persist across sessions but are limited to the cluster scope.
• Can be accessed by all users within the cluster.
• Require the global_temp database prefix.

CREATE GLOBAL TEMP VIEW global_temp_view_latest_phones
AS SELECT * FROM smartphones
WHERE year > 2020;

SELECT * FROM global_temp.global_temp_view_latest_phones;

+----+-----------+-----------+------+
| id | brand     | model     | year |
+----+-----------+-----------+------+
| 2  | Apple     | iPhone 13 | 2021 |
| 3  | Samsung   | Galaxy S21| 2021 |
| 4  | OnePlus   | OnePlus 9 | 2021 |
| 5  | Apple     | iPhone 14 | 2022 |
+----+-----------+-----------+------+

Managing Views in Databricks

1. Listing Views

To check available views in a database:

SHOW TABLES;

For global views:

SHOW TABLES IN global_temp;

2. Dropping a View

To remove a view:

DROP VIEW view_apple_phones;

For global views:

DROP VIEW global_temp.global_temp_view_latest_phones;

Best Practices for Using Views

1. Use Temporary Views for Session-Specific Queries: If the data is only required within a session, avoid creating global views.
2. Optimize Performance: Since views are virtual tables, querying them frequently with complex joins may impact performance. Consider materialized views or tables for heavy workloads.
3. Ensure Proper Permissions: Global views should only be created when necessary to avoid unnecessary data exposure.
4. Use Aliases for Better Readability: When working with multiple views, using aliases enhances query readability and maintainability.

Conclusion

Databricks Views are a valuable feature that simplifies querying and enhances data accessibility without physically storing data. By understanding their types, creation, and management, users can leverage views to improve efficiency and collaboration within Databricks workspaces.