Query Write-back (SELECT INTO)

The SELECT ... INTO ... statement copies data from query result set into target time series.

SQL

Syntax

The following is the syntax definition of the select statement:

selectClause 
intoClause? 
fromClause 
whereClause? 
specialClause?

If the intoClause is removed, then the select statement is a simple query statement.

The intoClause is the mark clause for query write-back.

The following is the definition of the intoClause:

intoClause
  : INTO ALIGNED? intoPath (COMMA intoPath)*
  ;

intoPath
  : fullPath
  | nodeNameWithoutStar (DOT nodeNameWithoutStar)*
  ;

There are 2 ways to specify target paths (intoPath).

• Full target series name starting with root.

• Example：

  select s1, s1 
  into root.sg.d1.t1, root.sg.d1.t2 
  from root.sg.d1
  

• Suffix path does not start with root. In this case, the target series name equals to the series prefix path in the from clause + the suffix path.

  • Example:

    select s1, s1 
    into t1, t2 
    from root.sg.d1
    

    which equals to:

    select s1, s1 
    into root.sg.d1.t1, root.sg.d1.t2 
    from root.sg.d1
    

**In intoPath, you can also use ${i} to represent part of the prefix path in the from clause. **

For example, for the path root.sg1.d1.v1, ${1} means sg1, ${2} means d1, and ${3} means v1.

• Example:

  select s1, s1, s1
  into ${1}_t1, ${2}, root.${2}.${1}.t2
  from root.sg.d1
  

  which equals to:

  select s1, s1, s1
  into root.sg.d1.sg_t1, root.sg.d1.d1, root.d1.sg.t2
  from root.sg.d1
  

**You can specify whether the target timeseries are aligned via the keyword ALIGNED. **

When the target aligned timeseries are existed, you need to ensure that the types of the source and target time series match.

When the target aligned timeseries are not existed, the system will automatically create the target aligned time series.

• Example:

  select s1, s2, s3
  into aligned root.sg.d2.t1, root.sg.d2.t2, root.sg.d2.t3
  from root.sg.d1
  

Supported Query Types

**Note that except for the following types of queries, other types of queries (such as LAST queries and raw aggregation queries) are not supported. **

• Raw time series query

  select s1, s1 
  into t1, t2 
  from root.sg.d1
  

• Time series generating function query（including UDF query）

  select s1, sin(s2) 
  into t1, t2 
  from root.sg.d1
  

• Arithmetic query

  select s1, sin(s2), s1 + s3 
  into t1, t2, t3 
  from root.sg.d1
  

• Nested query

  select -s1, sin(cos(tan(s1 + s2 * s3))) + cos(s3), top_k(s1 + s3, 'k'='1') 
  into t1, t2, t3 
  from root.sg.d1
  

• Fill query

  select s1 
  into fill_s1 
  from root.sg.d1 
  where time = 10 
  fill(float [linear, 1ms, 1ms])
  

• Group-by query

  select count(s1) 
  into group_by_s1 
  from root.sg.d1 
  group by ([1, 5), 1ms)
  

• Group-by-fill query

  select last_value(s1) 
  into group_by_fill_s1 
  from root.sg.d1 
  group by ([1, 10),1ms) 
  fill (float[PREVIOUS])
  

Special Cluases Supported in Queries

**Note that except for the following clauses, other query clauses (such as DESC, SOFFSET, etc.) are not supported. **

• Value filter

  select s1, s1 
  into t1, t2 
  from root.sg.d1
  where s1 > 0 and s2 < 0
  

• Time filter

  select s1, s1 
  into t1, t2 
  from root.sg.d1
  where time > 0
  

• LIMIT / OFFSET

  select s1, s1 
  into t1, t2 
  from root.sg.d1
  limit 5 offset 1000
  

Other Restrictions

• The number of source series in the select clause and the number of target series in the into clause must be the same.
• The select * and select ** clause are not supported.
• The target series in the into clause do not need to be created in advance. When the target series in the into clause already exist, you need to ensure that the source series in the select clause and the target series in the into clause have the same data types.
• The target series in the into clause must be different from each other.
• Only one prefix path of a series is allowed in the from clause.
• * and ** are not allowed in the from clause.
• Aligned Timeseries has not been supported in Time series generating function query（including UDF query）/ Arithmetic query / Nested query yet. An error message is expected if you use these types of query with Aligned Timeseries selected in the select clause.

User Permission Management

The user must have the following permissions to execute a query write-back statement:

• All READ_TIMESERIES permissions for the source series in the select clause
• All INSERT_TIMESERIES permissions for the target series in the into clause

For more user permissions related content, please refer to Account Management Statements.

Configurable Properties

• select_into_insert_tablet_plan_row_limit: The maximum number of rows can be processed in one insert-tablet-plan when executing select-into statements. 10000 by default.