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
Please log in to access this content.
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
Please log in to access this content.

Next Steps

Updated 03/22/2017

Time to Complete

5 Min.

Beginner

Next Steps

Next
Back to top