Skip to main content
Version: v2

Timing using View & Materialized View

View & Materialized View concept​

In Canner Enterprise, MIS can open the Workspace to do data modeling to generate View or Materialized View. Materialized views are to convert the data into an application-ready data set as a snapshot so that data consumers such as BI users can update BI reports and data analysts using Python or R.

View​

In Canner Enterprise's View, you can also think of it as the View when we operate the Database. We can directly convert the execution SQL of different databases into a SQL View and then use the View to perform operations such as Join with other Tables.

See more Operation of View

Materialized View​

If you have experience using relational databases such as PostgreSQL and Oracle, you may have heard of Materialized View more or less. Simply put, Materialized View will materialize the results of SQL Query. The most common use is to improve query performance. In the Materialized View of Canner Enterprise, you can also think of it as the Materialized View when we operate the Database, that is, when we generally use the database, when you want the "pre-computation" results store in a new Table.

In Canner Enterprise, the Materialized View will occupy a persistent space in the Storage. If we can use the Materialized View to create a snapshot of some time-consuming and complex query results, we can save the time and cost of going back to the data source every time by querying the Materialized View. , For example, we obtain business data through Oracle, and PostgreSQL by joining several tables. If such a query is used frequently but can be more real-time, it can update once daily instead of obtaining data from Oracle and PostgreSQL whenever used. It is better to set a Scheduled Materialized View, execute it once daily, and materialize the data so users can use it regardless of subsequent queries or concurrent needs.

How to use Materialized View:

  1. Pre-computing results, allowing end users to obtain data in their applications quickly
  2. Use Materialized View to pre-calculate commonly used reports, and subsequent users can use them directly without repeated calculations.
  3. You can schedule a fixed time to update the Materialized View to generate subsequent usage reports.
Partitions

The current limit is only 100 Partitions

See more Operation of Materialized View

Canner Enterprise data flow​

When the user executes a complex query under SQL explorer, the SQL query will execute as shown below:

  1. From the source, we only get the data that needs to calculate. In SQL, it is usually just a simple SELECT statement.
  2. When pulling to the computing layer, we will use distributed computing to distribute heavy computing things to multiple nodes for computing, such as Join, max, min, etc.
  3. The result of the operation is displayed on the screen of SQL explorer
  4. Users can regularly schedule Materialized View so that future application units can quickly use the data without having to recalculate every time.
  5. The materialized dataset send to various units, such as Power BI, Tableau, SDKs, etc.