# event\_to\_usage

## Overview

The `event_to_usage` statement generates new CSV file with usage records from START/STOP/UPDATE events in source [DSET](https://olddocs.exivity.io/3.5.4/advanced/digging-deeper/dataset-lifecycle).

## Syntax

**`event_to_usage from`***`source.alias`***`to`***`file`*`{` *`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](#initial-data-load))                 |

{% hint style="info" %}
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.
{% endhint %}

### 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](https://olddocs.exivity.io/3.5.4/data-pipelines/transform/option#execution-mode) 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
}
```