Skip to Content

View Data Across Both In-Memory and Dynamic Tiering Tables Using a SQL View

test
0 %
View Data Across Both In-Memory and Dynamic Tiering Tables Using a SQL View
Details
// Explore More Tutorials

View Data Across Both In-Memory and Dynamic Tiering Tables Using a SQL View

12/02/2018

Create and use a SQL view to query data from both in-memory and dynamic tiering tables.

You will learn

  • How to view the combined data set for data partitioned between an in-memory and a dynamic tiering table instance.
  • How to create and use a SQL view.
  • How to query against a SQL view with conditions.


Step 1: Query from both in-memory and dynamic tiering tables

There are cases where you only need to access data either from the in-memory table instance (ORDERS_CS) or from the dynamic tiering table instance (ORDERS_DT). However you will also likely have use cases where you need to query the full data set across both table instances, which can be done with a union.
Run the script below in a SQL Console to query data from both in-memory and dynamic tiering tables using a UNION.

SELECT * FROM "TPCH"."ORDERS_CS"
UNION ALL
SELECT * FROM "TPCH"."ORDERS_DT"

Since we are explicitly managing the data set between ORDERS_CS and ORDERS_DT to ensure that data is not duplicated between the 2 tables, we can use the UNION ALL variation of the UNION clause, which is faster because it doesn’t eliminate duplicate records in the combined result set.

Union Query
Log on to answer question
Step 2: Create and query against a SQL view

Alternatively you can create an SQL view to query data from multiple tables and simplify the process. Run the script below to create a SQL view. Verify it ran successfully.

CREATE VIEW "TPCH"."ORDERS_ALL_VIEW" AS
  SELECT * FROM "TPCH"."ORDERS_CS"
  UNION ALL
  SELECT * FROM "TPCH"."ORDERS_DT";
Creating View

After successfully creating a view, you can query against the "TPCH"."ORDERS_ALL_VIEW" whenever you need to access the combined data set. For example, if you want to query order records that are between 6 months and 18 months old, you can execute the query below.

SELECT "TPCH"."ORDERS_ALL_VIEW".* FROM "TPCH"."ORDERS_ALL_VIEW"
    WHERE "TPCH"."ORDERS_ALL_VIEW" ."O_ORDERDATE"
      BETWEEN ADD_YEARS(CURRENT_DATE, -1.5)
          AND ADD_YEARS(CURRENT_DATE, -0.5)
Select View
Why do we use the UNION ALL clause in our example view?:
×

Next Steps

Back to top