Create a data view to simplify complex nested SQL statements - Realtime Compute for Apache Flink

When your business logic requires multi-layer nested statements in a DML statement, issues become difficult to locate and the statement grows hard to read. A data view lets you wrap a nested statement as a named, reusable unit so your DML statements stay concise and easier to debug.

A data view is a logical table that describes computing logic. It does not store physical data.

Views vs. tables

Views and tables can both be referenced in SQL queries, but they behave differently:

Dimension	View	Table
Write support	Read-only. Cannot be used as a sink in `INSERT INTO` statements.	Supports both reads and writes.
Physical storage	None. Computed on-the-fly each time it is referenced.	Persists data.
Storage overhead	Stores only the query expression, not the data it returns.	Stores the full dataset.

Syntax

CREATE TEMPORARY VIEW viewName
  [( columnName [, columnName]* )]
  AS queryStatement;

Parameter	Description
`viewName`	The name of the view.
`columnName`	The name of a field in the view. Optional.
`queryStatement`	The nested statement that defines the view.

TEMPORARY: The view exists only for the duration of the session and overrides any persistent view with the same name.

Example

The following example wraps a source table query in a data view, then reads from the view in a DML statement instead of querying the source table directly.

-- Create a source table using the DataGen connector.
CREATE TEMPORARY TABLE datagen_source (
  name    VARCHAR,
  score   BIGINT
) WITH (
  'connector'      = 'datagen',
  'number-of-rows' = '10'
);

-- Create a result table using the BlackHole connector.
CREATE TEMPORARY TABLE rds_output (
  name  VARCHAR,
  score BIGINT
) WITH (
  'connector' = 'blackhole'
);

-- Define a data view over the source table.
-- Encapsulate the selection logic here to keep the DML statement simple.
CREATE TEMPORARY VIEW tmp_view AS
SELECT *
FROM datagen_source;

-- Read from the view instead of the source table directly.
INSERT INTO rds_output
SELECT name, score
FROM tmp_view;

What's next

DROP VIEW
CREATE TEMPORARY TABLE
INSERT INTO