Writing a loader program

You can write a program to load time series data by using time series SQL routines.

Before you begin

You must have the following prerequisites before you load data:
  • An existing table with a TimeSeries column and a primary key. The name of the table and the name of the TimeSeries column must not contain uppercase letters.
  • A container that is associated with the TimeSeries column.
  • Data that consists of primary key values and time-based data. The data can have the form of a buffer, a file, JSON documents, a ROW data type that is compatible with the TimeSeries data type, or an SQL query to extract the data from the source database.
  • The data must be compatible with HCL OneDB™ data types.

About this task

A loader program creates a loader session. A loader session loads data into a specific TimeSeries column. You must use separate loader sessions for every TimeSeries column. Opening a loader session takes time. Leave a session open instead of repeatedly opening and closing the session.

Within a loader session, you can open multiple database sessions so that you can load data in parallel. You can view active loader sessions by running the TSL_ActiveHandles function.

Procedure

To write a loader program that uses one database session:
  1. Initialize a global context and open a database session by running the TSL_Init function.
  2. Specify the new time series definition by running the TSL_SetNewTS function.
    Alternatively, you can prepare your table by adding rows with primary key values and time series definitions. The time series definition must include a threshold value of 0, which means that all elements are stored in containers
  3. Copy data from a file or input stream into the database server by running the TSL_Put, TSL_PutJSON, TSL_PutRow, or TSL_PutSQL function.
    You run this function many times while you load data.
  4. Save data to disk by running the TSL_FlushAll or TSL_Commit function.
    You can view information about the last flush operation by running the TSL_FlushInfo function.
  5. If necessary, change the logging mode by running the TSL_SetLogMode function.
  6. Optional: Monitor the progress of loaded and saved data by repeatedly running the TSL_GetFmtMessage function or the TSL_GetLogMessage function.
  7. Close the database session by running the TSL_SessionClose function.
  8. Remove the global context and shut down the loader by running the TSL_Shutdown procedure.

Results

To write a loader program that uses multiple database sessions:

  1. Initialize a global context and open a database session by running the TSL_Init function.
  2. Open additional database sessions by running the TSL_Attach function.
  3. Determine how to distribute the data among database sessions by running the TSL_GetKeyContainer function to find into which container each primary key value belongs.

    Loading is faster if each database session loads data into a different container.

  4. Within the context of each database session, run the TSL_Put, TSL_PutJSON, TSL_PutRow, or TLS_PutSQL function and the TSL_FlushAll or TSL_Commit function to load and save data.
  5. Within the context of each database session, monitor the progress of loaded and saved data by running the TSL_GetLogMessage function.
  6. If necessary, change the logging mode of all database sessions by running the TSL_SetLogMode function.
  7. Within the context of each database session, close the database session by running the TSL_SessionClose function.
  8. Remove the global context and shut down the loader by running the TSL_Shutdown procedure.

Examples

The following loader session uses one database session to load data into the ts_data table in the stores_demo database:

EXECUTE PROCEDURE ifx_allow_newline ('t');

EXECUTE FUNCTION TSL_Init ('ts_data','raw_reads',
                          	3,4, NULL, '%Y-%m-%d %H:%M:%S',
                         	'/tmp/rejects.log',NULL);

EXECUTE FUNCTION TSL_Put ('ts_data|raw_reads',
'4727354321000111|KWH|P|2010-11-10 00:00:00.00000|0.092|
4727354321000111|KWH|P|2010-11-10 00:15:00.00000|0.084|
4727354321000111|KWH|P|2010-11-10 00:30:00.00000|0.09|
4727354321000111|KWH|P|2010-11-10 00:45:00.00000|0.085|
4727354321000111|KWH|P|2010-11-10 01:00:00.00000|0.088|
4727354321000111|KWH|P|2010-11-10 01:15:00.00000|0.088|
4727354321000111|KWH|P|2010-11-10 01:30:00.00000|0.085|
4727354321000111|KWH|P|2010-11-10 01:45:00.00000|0.091|
4727354321046021|KWH|P|2010-11-10 00:00:00.00000|0.041|
4727354321046021|KWH|P|2010-11-10 00:15:00.00000|0.041|
4727354321046021|KWH|P|2010-11-10 00:30:00.00000|0.04|
4727354321046021|KWH|P|2010-11-10 00:45:00.00000|0.041|
4727354321046021|KWH|P|2010-11-10 01:00:00.00000|0.041|
4727354321046021|KWH|P|2010-11-10 01:15:00.00000|0.041|
4727354321046021|KWH|P|2010-11-10 01:30:00.00000|0.055|
4727354321046021|KWH|P|2010-11-10 01:45:00.00000|0.073|
4727354321046021|KWH|P|2010-11-10 02:00:00.00000|0.071|
4727354321046021|KWH|P|2010-11-10 02:15:00.00000|0.068|
4727354321046021|KWH|P|2010-11-10 02:30:00.00000|0.07|
');

EXECUTE FUNCTION TSL_Put ('ts_data|raw_reads',
'4727354321090954|KWH|P|2010-11-10 00:00:00.00000|0.026|
4727354321090954|KWH|P|2010-11-10 00:15:00.00000|0.035|
4727354321090954|KWH|P|2010-11-10 00:30:00.00000|0.062|
4727354321090954|KWH|P|2010-11-10 00:45:00.00000|0.092|
4727354321090954|KWH|P|2010-11-10 01:00:00.00000|0.016|
4727354321090954|KWH|P|2010-11-10 01:15:00.00000|0.043|
4727354321090954|KWH|P|2010-11-10 01:30:00.00000|0.038|
4727354321090954|KWH|P|2010-11-10 01:45:00.00000|0.037|
4727354321090954|KWH|P|2010-11-10 02:00:00.00000|0.034|
4727354321090954|KWH|P|2010-11-10 02:15:00.00000|0.023|
4727354321090954|KWH|P|2010-11-10 02:30:00.00000|0.03|
4727354321090954|KWH|P|2010-11-10 02:45:00.00000|0.05|
4727354321090954|KWH|P|2010-11-10 03:00:00.00000|0.048|
4727354321090954|KWH|P|2010-11-10 03:15:00.00000|0.047|
');

begin;
EXECUTE FUNCTION TSL_FlushAll ('ts_data|raw_reads');
commit;

EXECUTE FUNCTION TSL_SessionClose ('ts_data|raw_reads');

EXECTUE PROCEDURE TSL_Shutdown ('ts_data|raw_reads');