Example for regular data: Create and load a regular time series

This example shows how to create a TimeSeries data type, create a time series table, create a regular time series by running the TSCreate function, and load data into the time series through the HCL® OneDB® TimeSeries Plug-in for Data Studio.

Before you begin

Prerequisites:

or must be running and the must be installed. Data Studio can be installed on a different computer than the database server.
The stores_demo database must exist. You create the stores_demo database by running the dbaccessdemo command.

About this task

In this example, you create a time series that contains electricity meter readings. Readings are taken every 15 minutes. The table and TimeSeries data type you create are similar to the examples in the ts_data table in the stores_demo database. The following table lists the time series properties used in this example.

Table 1. Time series properties used in this example
Time series property	Definition
Timepoint size	15 minutes
When timepoints are valid	Every 15 minutes with no invalid times
Data in the time series	The following data: Timestamp A decimal value that represents electricity usage
Time series table	The following columns: A meter ID column of type BIGINT A TimeSeries data type column
Origin	All meter IDs have an origin of 2010-11-10 00:00:00.00000
Regularity	Regular
Metadata	No metadata
Amount of storage space	Approximately 1 MB (8640 timepoints for each of the 28 rows)
Where to store the data	In an automatically created container in the same dbspace as the stores_demo database, which is in the root dbspace by default
How to load the data	The
How to access the data	A virtual table

Creating a TimeSeries data type and table

About this task

You create a TimeSeries data type with columns for the timestamp and the electricity usage value. Then you create a table that has primary key column for the meter ID and a TimeSeries column.

Procedure

To create the TimeSeries data type and table:

Create a TimeSeries subtype named my_meter_data in the stores_demo database by running the following SQL statement:
```
CREATE ROW TYPE my_meter_data(
    timestamp    DATETIME YEAR TO FRACTION(5),
    data         DECIMAL(4,3)
);
```
The timestamp column contains the time of the meter reading and the data column contains the reading value.

Create a time series table named my_ts_data by running the following SQL statement:

CREATE TABLE IF NOT EXISTS my_ts_data (
	meter_id	BIGINT NOT NULL PRIMARY KEY,
 raw_reads	 TIMESERIES(my_meter_data)
) LOCK MODE ROW;

Creating regular, empty time series

About this task

You need to define the properties of the time series for each meter ID by loading the meter IDs into the time series table and creating a regular, empty time series for each meter ID. You use the meter IDs from the ts_data table in the stores_demo database to populate the meter_id column of your my_ts_data table.

Procedure

To create regular, empty time series:

Create an unload file named my_meter_id.unl that contains the meter IDs from the loc_esi_id column of the ts_data table by running the following SQL statement:
```
UNLOAD TO "my_meter_id.unl" SELECT loc_esi_id FROM ts_data; 
```
Create a temporary table named my_tmp and load the meter IDs into it by running the following SQL statements:
```
CREATE TEMP TABLE my_tmp (
  id BIGINT NOT NULL PRIMARY KEY);

LOAD FROM "my_meter_id.unl" INSERT INTO my_tmp;
```
You use this table in the next step to create a time series for each meter ID with one SQL statement instead of running a separate SQL statement for each meter ID.
Create a regular, empty time series for each meter ID that uses the pre-defined calendar ts_15min by running the following SQL statement, which uses the time series input function:
```
INSERT INTO my_ts_data
  SELECT id, 
         "origin(2010-11-10 00:00:00.00000),calendar(ts_15min),
          threshold(0),regular,[]"
  FROM my_tmp;
```
Because you did not specify a container name, the time series for each meter ID is stored in a container in the same dbspace in which the table resides. The container is created automatically and is a member of the default container pool.

Creating the data load file

About this task

You create a time series data load file by creating a virtual table based on the ts_data table and then unloading some of the columns.

Procedure

To create the data load file:

Create a virtual table based on the raw_reads time series column of the ts_data table by running the following SQL statement:
```
EXECUTE PROCEDURE TSCreateVirtualTab("my_vt", "ts_data", 0, "raw_reads");
```
You use the virtual table to create a data load file.
Unload the data from the tstamp and value columns from the virtual table into a file named my_meter_data.unl by running the following SQL statement:
```
UNLOAD TO my_meter_data.unl 
  SELECT loc_esi_id, tstamp, value 
       FROM my_vt;
```

Loading the time series data

About this task

You use the to load the data from the my_meter_data.unl file into the my_ts_data table. The has a cheat sheet that you use to guide you through the process of loading the data.

Procedure

To load time series data:

If you are using Data Studio or on a different computer, move the $INFORMIXDIR\my_meter_data.unl file to that computer and start Data Studio or .
From the main menu, choose Help > Cheat Sheets, expand the TimeSeries Data category, choose Load time-series data, and click OK.
From the main menu, choose Help > Cheat Sheets, expand the TimeSeries Data category, choose Loading from a File, and click OK.
Open the TimeSeries perspective.
Create a project area named my_test.
Create the table definition and define the columns of the table. Name the table definition my_table and save the definition in the my_test project directory. Define the following table columns:
- meter_id: choose the Big Integer type and specify that it is the primary key
- raw_reads: choose the TimeSeries type
Define the following subcolumns for the raw_reads column and then save the project:
- timestamp: choose the Timestamp type
- data: choose the Numeric type
Create a record format and define the format of the data file. Name the record format definition my_format and save it in the my_test project directory. Define the following record formats:
- meter_id: choose the Big Integer type and specify the | (pipe) delimiter
- timestamp: choose the Timestamp type and specify the | (pipe) delimiter
- data: choose the Numeric type and specify the | (pipe) delimiter
Create a table map named my_map and map the data formats of the data file to the columns of the table and then save it in the my_test project directory.
Create a connection profile to the database server named my_ifx.
Define and start a load job. Specify the following values:
- File format file: my_format.udrf
- Table definition file: my_table.tbl
- Mapping file: my_map.tblmap
- Data file: my_meter_data.unl
- Connection profile: my_ifx
When you click OK, the load job starts and you see the status.

Accessing time series data through a virtual table

About this task

You create a virtual table to view the time series data in relational data format.

Procedure

To create a virtual table based on the time series table:

Use the TSCreateVirtualTab procedure to create a virtual table named my_vt2 that is based on the my_ts_data table by running the following SQL statement:

EXECUTE PROCEDURE TSCreateVirtualTab("my_vt2", "my_ts_data", 
   "calendar(ts_15min), origin(2010-11-10 00:00:00.00000)");

Results

You can query the virtual table by running standard SQL statements. For example, the following query returns the first value for each of the 28 meter IDs:

SELECT * FROM my_vt2 WHERE timestamp = "2010-11-10 00:00:00.00000";