event_to_usage

Overview

The event_to_usage statement generates new CSV file with usage records from START/STOP/UPDATE events in source DSET.

Syntax

event_to_usage fromsource.aliastofile{ options }

See Details section for options description

Details

This statement produces usage records from events in source DSET. Three times of events are supported:

• START event - marks the start of consumption

• STOP event - marks the end of consumption

• UPDATE event - marks the change of consumption attributes, such as quantity

There are several situations when the usage record is created:

• from START to first matching STOP event

• from START to first matching UPDATE event

• from UPDATE to first matching STOP event

• from UPDATE to first matching UPDATE event

• from the beginning of the day to STOP event (if consumption started during previous days)

• from the beginning of the day to UPDATE event (if consumption started during previous days)

• from START event to the end of the day (if no matching STOP/UPDATE events found)

• from UPDATE event to the end of the day (if no matching STOP/UPDATE events found)

• for the whole day (if consumption started during previous days, and there was no STOP or UPDATE event during processing day)

Event B is considered matching to event A if happened after event B and has matching key fields.

Options

Several options control the behaviour of this statement:

Option name	Presence	Description
start_event	Mandatory	SQL condition for START events (see note below)
stop_event	Mandatory	SQL condition for STOP events (see note below)
update_event	Optional	SQL condition for UPDATE events (see note below). If not specified, only START and STOP are used
key_columns	Mandatory	Comma-separated list of columns for matching events. Values in all columns should match in order for events to match
timestamp_column	Mandatory	Column with event timestamp
epoch_date	Optional	Date in YYYYMMDD format when data was initially loaded (see Initial data load)

Conditions for events are valid SQL conditions, which are copied verbatim into query's WHERE, therefore it can be any legal SQL expression, such as "state = 'started' AND (prev_state = 'stopped' OR prev_state IS NULL)". Remember using SQL-standard single quotes for string literals.

Initial data load

If epoch_date option is specified, it is possible to perform initial data load - load running consumptions on specific date. Transcript performs following checks:

• there are no events loaded for the specified DSET

• processing date matches specified epoch_date

If any of these checks fail, Transcript stops with error.

Data integrity checks

It is very important to load events in correct order, therefore Transcript performs following checks:

• the data for the same day for the specified DSET cannot be loaded twice

• there cannot be gaps in processed dates (except for Initial data load)

• there is a matching START event before STOP/UPDATE event (ignored in permissive mode)

If any of these checks fail, Transcript stops with error.

If there is a need to re-process data for specific day, event-related state in the database must be rolled back to the preceding date, and after data for all following days processed in correct order.

Examples

Only START and STOP events, single-column key:

event_to_usage from usage.events to "exported/usage.csv" {
      start_event = "state = 'STARTED' AND (prev_state = 'STOPPED' OR prev_state IS NULL)"
      stop_event = "state = 'STOPPED' AND prev_state = 'STARTED'"
      key_columns = resource_id
      timestamp_column = time
    epoch_date = 20200109
}

START, STOP and UPDATE events, complex key:

event_to_usage from usage.events to "exported/usage.csv" {
      start_event = "state = 'STARTED' AND (prev_state = 'STOPPED' OR prev_state IS NULL)"
      update_event = "state = 'STARTED' AND prev_state = 'STARTED'"
      stop_event = "state = 'STOPPED' AND prev_state = 'STARTED'"
      key_columns = resource_type,resource_id
      timestamp_column = time
}