Data Query

Uqbar uses SQL as the query language, and users can connect to Uqbar through the client or different drivers to execute data queries. To support different data analysis needs of users, Uqbar provides a rich set of query operators.

Aggregation Operators

Uqbar of the current version supports the following common aggregation operators.

time_bucket()

time_bucket is a time range alignment function. It is used for aligning a time based on bucket_width for aggregating time later.

Syntax

time_bucket(bucket_width, time,[offset | origin])

Parameter Description

Constraints

When time_bucket is used, because offset and origin are mutually exclusive, the third parameter of time_bucket may be offset (interval) or origin (timestamp). To distinguish offset and origin easily, using offset or origin needs to specify the third parameter, such as:

select time_bucket('5minute',time,'2001-1-1 00:00:00'::timestamp) as bucket ,count(temperature) from weather group by bucket order by bucket asc;

Or

select time_bucket('1minute',time,'1minute'::interval) as bucket ,count(temperature) from weather group by bucket order by bucket asc;

Examples

Uqbar=#  select * from t1 order by time;
          time          | id | value 
------------------------+----+-------
 2022-06-02 00:01:00+08 |  1 |     9
 2022-06-02 00:04:00+08 |  1 |    11
 2022-06-02 00:06:00+08 |  1 |    10
 2022-06-02 00:07:00+08 |  1 |    11
 2022-06-02 00:15:00+08 |  1 |    12
(5 rows)

Uqbar=# select time_bucket('2 minute',time) as bucket, sum(value)  from t1 group by bucket order by bucket; 
          bucket         | sum 
------------------------------------+-----
 2022-06-02 00:00:00+08   |   9
 2022-06-02 00:04:00+08   |  11
 2022-06-02 00:06:00+08   |  21
 2022-06-02 00:14:00+08   |  12
(4 rows)

Selection Operators

Uqbar of the current version supports the following common selection operators.

first() / last()

cmp_col is used for comparison to return the value of the column specified by the first or last value. The first and last operators in the timeseries_catalog schema is different from those in the public schema.

Syntax

select city_name,timeseries_catalog.first(temp_c,time) from weather_metrics group by city_name;
select city_name,timeseries_catalog.last(temp_c,time) from weather_metrics group by city_name;
timeseries_catalog.first(value, cmp_col)
timeseries_catalog.last(value, cmp_col)

Parameter Description

Constraints

If there are same minimum or maximum values in the cmp_col column, return the first or last value.

If the values in the cmp_col column are constants, the first and last operators equal those in the public schema, and the first or last value will be returned.

Examples

Uqbar=# select city_name,timeseries_catalog.first(temp_c,time) from weather_metrics group by city_name; 
 city_name | first 
---------------+-------
beijing    | 30
shanghai   | 29 
(2 row)

Conversion Operators

Uqbar of the current version supports the following common conversion operators.

Join Query

Uqbar supports join query between time-series table and relational table, time-series table and time-series table.

Index

Time-series tables support index-related functions to help users use index to improve query performance based on actual requirements. The index type supported by time-series tables includes BTree and Gin, partial index is not supported, and BTree supports unique index. The default index in time-series tables is LOCAL index. GLOBAL index is not supported. When data is to be deleted from a time-series table, the index in the corresponding chunk group will also be deleted.

Create an Index

Index creation syntax is the same as that for a partition table. However, GLOBAL index creation is not supported.

CREATE [ UNIQUE ] INDEX [ [schema_name.]index_name ] ON table_name [ USING method ]
    ( {{ column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
    [ LOCAL [ ( { PARTITION index_partition_name | SUBPARTITION index_subpartition_name [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] ]
    [ INCLUDE ( column_name [, ...] )]
    [ WITH ( { storage_parameter = value } [, ...] ) ]
    [ TABLESPACE tablespace_name ];

Delete an Index

DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] index_name [, ...] [ CASCADE | RESTRICT ];

Modify an Index

• Rename a table index.

  ALTER INDEX [ IF EXISTS ] index_name RENAME TO new_name;

• Modify the storage parameters of a table index.

  ALTER INDEX [ IF EXISTS ] index_name SET ( {storage_parameter = value} [, ... ] );

• Set a table index or index partition unavailable

  ALTER INDEX [ IF EXISTS ] index_name [ MODIFY PARTITION index_partition_name ] UNUSABLE;

• Reset a certain storage parameter of one or more index methods of an index as the default. Similar to SET, REINDEX may need to be used for updating an index.

  ALTER INDEX [ IF EXISTS ] index_name RESET ( { storage_parameter } [, …] );

• Recreate an index in a table or index partition.

  ALTER INDEX [ IF EXISTS ] index_name REBUILD [ PARTITION index_partition_name ];

• Rename an index partition.

  ALTER INDEX [ IF EXISTS ] index_name RENAME PARTITION index_partition_name TO new_index_partition_name;

• Modify the tablespace to which an index partition belongs.

  ALTER INDEX [ IF EXISTS ] index_name MOVE PARTITION index_partition_name TABLESPACE new_tablespace;

Rebuild an Index

REINDEX  { INDEX|  [INTERNAL] TABLE} name PARTITION partition_name [ FORCE  ];