CREATE_STREAM-YMatrix 国产数据库厂商

CREATE STREAM

Define a stream.

Summary

CREATE STREAM [IF NOT EXISTS] stream_name [ (column_name [, ...] ) ]
AS(
    SELECT [ { * | expression [ [ AS ] output_name ] } [, ...] ]
    FROM STREAMING { ALL | INSERT ｜UPDATE | DELETE } table_name
    [ WHERE condition ]
    [ join_type ]
    [ GROUP BY grouping_element [, ...] ]
    [ WITH [ NO ] DATA ]
)
[ USING MARS3 ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ DISTRIBUTED BY ( [column_name [, ...] ] ) ]

describe

CREATE STREAM declares the definition of a stream and defines internal management functions within the database.

Parameters

stream_name [ (column_name [, ...] ) ]
- stream_name: flow table. If as the end of the stream, the calculation result of the stream is stored.
- (column_name [, ...] ): Field map, can be omitted. When omitting, the field information listed in the SELECT section of the AS statement block is used by default.
AS
- AS statement block defines the computational logic of the stream in the form of SQL.
SELECT [ { * | expression [ [ AS ] output_name ] } [, ...] ]
- Specify the columns to be queried, which can be the default fields of the upstream table, or specify a new field (aggregated calculation results, etc.).
FROM STREAMING { ALL | INSERT ｜UPDATE | DELETE } table_name
- FROM STREAMING declares that data is incrementally subscribed from the source table in a stream, and a set of processes will be started inside the corresponding database to monitor the data changes on the table.
- ALL | INSERT ｜UPDATE | DELETE Select the subscription method of the stream from the source table, that is, to monitor a certain type of data change in the source table. ALL is used to monitor the insertion, update and delete changes of source table data; INSERT is used to monitor the insertion changes of source table; UPDATE is used to monitor the update operation of source table; DELETE is used to monitor the delete operation of source table.
- table_name source table (upstream table) of the stream.
join_type
- Currently, only [ INNER ] JOIN operations are supported, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, etc. [OUTER] JOIN operations will be updated in subsequent versions.
WITH [NO] DATA
- Indicates whether the historical data of the source table needs to be processed when creating a stream. [NO] means not to deal with it.
USING MARS3
- Created as a HEAP table by default, which can be specified as a MARS3 table using this statement.
WITH ( storage_parameter [= value] [, ... ] )
- You can refer to the CREATE_TABLE section for use.
DISTRIBUTED BY ( [column_name [, ...] ] )
- YMatrix database distribution policy for declaring tables.

Example

Create a basic stream.

CREATE STREAM s1(id, c1, ts, arrive_s1) 
AS ( 
    SELECT *, clock_timestamp() 
    FROM STREAMING t1
    WITH NO DATA 
) 
DISTRIBUTED BY (id);

Create a stream that uses the MARS3 engine and specifies data compression types and levels.

CREATE STREAM s4(id, c1, ts, arrive_s4)
AS (
 SELECT *, clock_timestamp()
 FROM STREAMING t4
 WITH NO DATA
)
USING mars3
WITH (compresstype='zstd', compresslevel=1)
DISTRIBUTED BY (id, arrive_s4);

For more information, please refer to Flow calculation function example