A Beginner’s Guide to Views in BigQuery

If you're new to BigQuery, data warehousing or databases in general, you might have come across the term "View" and wondered what it really means. In this post, let's try to understand what a view is, why it’s useful, and how it behaves inside BigQuery.

1. What is a View in BigQuery?

A View in BigQuery is like a virtual table, it doesn’t store any data by itself but shows data by running a predefined SQL query on one or more existing tables.

 

2. Key Characteristics of Views:

·      No Physical Data: Views do not store any actual data. Every time you query a view, BigQuery executes the SQL query behind it and fetches the result from the base table(s).

 

·      Read-Only: You can only query from views. You can't insert, update, or delete data directly in a view.

 

·      Frozen Schema: When a view is created, its structure is locked based on the original query. If the base table schema changes later, the view won’t automatically adapt. You’ll need to update the view’s query to reflect those changes.

 

·      Dependent on Base Tables: If the original table(s) a view relies on are deleted, the view becomes non-functional.

 

3. Why Use Views?

·      Controlled Access: If you don’t want to give users full access to the actual tables (for security or compliance reasons), you can grant access to views instead. This helps to enforce data privacy.

 

·      Customized Views for Different Roles: You can create different views for different user groups. For example, HR teams might use a view that includes all employee details, while a support team might use a simplified version with only basic information.

 

·      Simplify Complex Queries: Views help to shorten long and complicated SQL queries. You can create a view from a complex join or filters and reuse it like a shortcut.

 

 

4. Views (Hands on)

Follow below step-by-step procedure to work with views.

 

Step 1: Create a Table application_logs

You can run this SQL in the BigQuery Editor:

CREATE TABLE my_project.my_dataset.application_logs (
  log_id INT64,
  application_name STRING,
  log_level STRING,
  message STRING,
  log_timestamp TIMESTAMP
);

Step 2: Insert Sample Data.

INSERT INTO my_project.my_dataset.application_logs (log_id, application_name, log_level, message, log_timestamp)
VALUES
  (1, 'UserService', 'INFO', 'User created successfully', CURRENT_TIMESTAMP()),
  (2, 'AuthService', 'ERROR', 'Invalid login attempt', CURRENT_TIMESTAMP()),
  (3, 'PaymentService', 'WARN', 'Payment took too long', CURRENT_TIMESTAMP());

Step 3: Print the table content.

Let's print the content of application_logs table by executing below statement.

SELECT * FROM my_project.my_dataset.application_logs;

 

 

Step 4: Create a View — application_logs_view

Let’s say you only want to show logs that are not of level INFO.

CREATE VIEW my_project.my_dataset.application_logs_view AS
SELECT
  log_id,
  application_name,
  log_level,
  message,
  log_timestamp
FROM
  my_project.my_dataset.application_logs
WHERE
  log_level != 'INFO';

Query the content of view.

SELECT * FROM my_project.my_dataset.application_logs_view;

We can add Metadata like Label, Description, Expiration Date to the View You’ll need to use the BigQuery Console or bq command-line tool for this.

 

Following snippet add description to the view.

 

CREATE OR REPLACE VIEW my_project.my_dataset.application_logs_view
OPTIONS (
  description = "View to show non-INFO level logs from application_logs table"
)
AS
SELECT
  log_id,
  application_name,
  log_level,
  message,
  log_timestamp
FROM
  my_project.my_dataset.application_logs
WHERE
  log_level != 'INFO';

Upon successful creation of the view, you can see the view description at view details tab.

 

Previous                                                    Next                                                    Home