Skip to Content

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

Previous

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

By Balkarann

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

You will learn

  • Viewing the combined data set for data partitioned between an in-memory and a Dynamic Tiering table instance.
  • Creating and using a SQL view.
  • Querying against a SQL view with conditions.

Details


Step 1: Query From Both In-Memory and Dynamic Tiering Tables

You may have many 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"

Note: 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
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.

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

Verify the script executed correctly.

Create View Success

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)
Query View

Next Steps

Updated 09/13/2017

Time to Complete

5 Min.

Beginner

Next Steps

Next
Back to top