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:
1
event_to_usage from usage.events to "exported/usage.csv" {
2
      start_event = "state = 'STARTED' AND (prev_state = 'STOPPED' OR prev_state IS NULL)"
3
      stop_event = "state = 'STOPPED' AND prev_state = 'STARTED'"
4
      key_columns = resource_id
5
      timestamp_column = time
6
    epoch_date = 20200109
7
}
Copied!
START, STOP and UPDATE events, complex key:
1
event_to_usage from usage.events to "exported/usage.csv" {
2
      start_event = "state = 'STARTED' AND (prev_state = 'STOPPED' OR prev_state IS NULL)"
3
      update_event = "state = 'STARTED' AND prev_state = 'STARTED'"
4
      stop_event = "state = 'STOPPED' AND prev_state = 'STARTED'"
5
      key_columns = resource_type,resource_id
6
      timestamp_column = time
7
}
Copied!

environment

export

Last modified 9mo ago

Export as PDF

Copy link

Contents

Data integrity checks

Examples