Using a view in MySql

I recently was working with Google Data Studio to create a report from data that was stored in a MySQL table. In Google Data Studio you can set up a connector to connect to a MySql database on a server. That was my plan as well, I found that after connecting to the server the MySql was running on, I could in Google Data studio only select a table. My business case was to be able to query data from several tables.

In Google Data Studio I could do a custom query, I decided to go down the path of using a view in MySql.

What is a view in MySQL?

MySQL views are a way of storing queries on the database level and producing virtual tables in the database whose contents are defined by a query.

To the database user, the view appears just like a real table, with a set of named columns and rows of data.

When to use views

Here are some ways to use views

  • You can use a view for a search function and hide irrelevant information.
  • You could create a summary that may or may not involve calculations.
  • When using Google Data Studio for reporting and your source connector is a MySQL table, you can use the view to consolidate your join or union as in Google Data Studio when you set a data source you can only select one table, with a view you can join or union or selecting a set of rows with WHERE clauses.
  • Allow for changes made to the base table via a view that preserves the schema of the original table to accommodate other applications.

What are the advantages of views

Views offer a number of advantages. You can use views to hide table columns from users by granting them access to the view and not to the table itself. This helps enhance database security and integrity.

Security

You can give users permission to access the database only through a small set of views that contain the specific data the user is authorized to see, thus restricting the user’s access to stored data.

Consistency

A view can present a consistent, unchanged image of the structure of the database, even if the underlying source tables are split, restructured, or renamed.

Data Integrity

If data is accessed and entered through a view, the MySQL database can automatically check the data to ensure that it meets the specified integrity constraints.

What are the disadvantages of views

Performance

Views create the appearance of a table, but MySQL must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table query then simple queries on the views may take considerable time.

Create a view

Here is an example on how you can create a view

CREATE VIEW reportview
AS
SELECT  a.*,
b.country,
          b.region,
          c.campaign as campaign_name,
          d.activity
FROM    noon_report a
       INNER JOIN countries b
           ON a.country_id = b.id
       INNER JOIN campaigns c
           ON a.campaign = c.id
       INNER JOIN activities d
           ON a.activityType = d.id

Want to learn more about views, here is a good source https://www.w3schools.com/sql/sql_view.asp


Posted

in

,

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *