Example for compressible data: Create and load a compressed time series
This example shows how to create, load, and query a time series that stores compressed numeric data.
About this task
Time series property | Definition |
---|---|
Calendar interval | Second |
When elements are valid | Always |
Data in the time series | The following data:
|
Time series table | The following columns:
|
Origin | 2014-01-01 00:00:00.00000 |
Regularity | Irregular |
Compression | Three different compression definitions |
Metadata | No metadata |
Where to store the data | In a container that you create |
How to load the data | The InsElem function |
How to access the data | A virtual table |
Procedure
To create, load, and view a compressed time series:
- Create a TimeSeries subtype that is named ts_data_c in
a database by running the following SQL statement:
CREATE ROW TYPE ts_data_c( tstamp datetime year to fraction(5), ts1 int, ts2 int, ts3 int );
You can include only certain numeric data types in your TimeSeries subtype. - Create a time series table that is named tstable_c by
running the following SQL statement:
CREATE TABLE IF NOT EXISTS tstable_c( id int not null primary key, ts timeseries(ts_data_c) ) LOCK MODE ROW;
- Create a container that is named container_c by
running the following SQL statement:
EXECUTE PROCEDURE TSContainerCreate('container_c', 'rootdbs', 'ts_data_c', 512, 512);
You can choose to create the container in a different dbspace than the root dbspace. - Create a calendar by running the following SQL statement:
INSERT INTO CalendarTable(c_name, c_calendar) VALUES('ts_1sec', 'startdate(2014-01-01 00:00:00.00000), pattern({1 on}, second)');
You cannot include off periods in the calendar for a compressed time series. - Create a compressed time series with compression definitions
for the three data columns in the ts_data_c data type by running
the following SQL statement in an explicit transaction:
BEGIN; Started transaction. INSERT INTO tstable_c VALUES(50, TSCreateIrr('ts_1sec', '2014-01-01 00:00:00.00000', 'container_c', 'q(2,1,100),lb(20),ls(20)')); 1 row(s) inserted. COMMIT;
The first INTEGER column has a compression type of quantization with a compression size of 2 bytes, a lower bound of 1 and an upper bound of 100. The second INTEGER column has a compression type of linear boxcar and a maximum deviation of 20. The third INTEGER column has a compression type of linear swing door and a maximum deviation of 20. - Insert five records for the same element into the time
series by running the following SQL statements:
BEGIN; Started transaction. UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:00.00000', 1, 201, 99991)::ts_data_c) WHERE id = 50; 1 row(s) updated. UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:01.00000', 2, 202, 99992)::ts_data_c) WHERE id = 50; 1 row(s) updated. UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:02.00000', 3, 203, 99993)::ts_data_c) WHERE id = 50; 1 row(s) updated. UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:03.00000', 4, 204, 99994)::ts_data_c) WHERE id = 50; 1 row(s) updated. UPDATE tstable_c SET ts = InsElem(ts, row('2014-01-01 00:00:04.00000', 5, 205, 99995)::ts_data_c) WHERE id = 50; 1 row(s) updated. COMMIT; Data committed.
You must insert records in chronological order. - Create a virtual table that is named vt_tstable_c that
is based on the compressed time series by running the following SQL
statement:
EXECUTE PROCEDURE TSCreateVirtualTab('vt_tstable_c', 'tstable_c', 4096, 'ts');
- Query the virtual table to view the compressed data by
running the following SQL statement:
SELECT * FROM vt_tstable_c; id tstamp ts1 ts2 ts3 50 2014-01-01 00:00:00.00000 1 201 99991 50 2014-01-01 00:00:01.00000 2 202 99992 50 2014-01-01 00:00:02.00000 3 203 99993 50 2014-01-01 00:00:03.00000 4 204 99994 50 2014-01-01 00:00:04.00000 5 205 99995 5 row(s) retrieved.