View Data Across Both In-Memory and Dynamic Tiering Tables Using a SQL View
- 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
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
SELECT * FROM "TPCH"."ORDERS_CS" UNION ALL SELECT * FROM "TPCH"."ORDERS_DT"
Since we are explicitly managing the data set between
ORDERS_DTto ensure that data is not duplicated between the 2 tables, we can use the
UNION ALLvariation of the
UNIONclause, which is faster because it doesn’t eliminate duplicate records in the combined result set.
- Step 2
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.SQLCopy
CREATE VIEW "TPCH"."ORDERS_ALL_VIEW" AS SELECT * FROM "TPCH"."ORDERS_CS" UNION ALL SELECT * FROM "TPCH"."ORDERS_DT";
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.SQLCopy
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)
Why do we use the UNION ALL clause in our example view?: