Schema trend_directory

Tables

trend_store

Table based trend stores describing the common properties of all its partitions like entity type, data granularity, etc.

Column	Type	Nullable	Description
id	integer	✘
entity_type_id	integer	✔
data_source_id	integer	✔
granularity	interval	✘
partition_size	interval	✘
retention_period	interval	✘

trend_store_part

The parts of a horizontally partitioned table trend store. Each table trend store has at least 1 part.

Column	Type	Nullable	Description
id	integer	✘
name	name	✘
trend_store_id	integer	✘

partition

The parts of a vertically partitioned trend store part.

Column	Type	Nullable	Description
id	integer	✘
trend_store_part_id	integer	✘
name	name	✘
index	integer	✘
from	timestamp with time zone	✘
to	timestamp with time zone	✘

trend_view

View based trend stores describing the properties like entity type, data granularity, etc.

Column	Type	Nullable	Description
id	integer	✘
entity_type_id	integer	✔
data_source_id	integer	✔
granularity	interval	✘

trend_view_part

Column	Type	Nullable	Description
id	integer	✘
name	name	✘
trend_store_id	integer	✘

table_trend

Column	Type	Nullable	Description
id	integer	✘
trend_store_part_id	integer	✘
name	name	✘
data_type	text	✘
extra_data	jsonb	✘
description	text	✘
time_aggregation	text	✘
entity_aggregation	text	✘

generated_table_trend

Column	Type	Nullable	Description
id	integer	✘
trend_store_part_id	integer	✘
name	name	✘
data_type	text	✘
expression	text	✘
extra_data	jsonb	✘
description	text	✘

table_trend_tag_link

Column	Type	Nullable	Description
table_trend_id	integer	✘
tag_id	integer	✘

modified_log

The modified_log table stores records of when what trend_store_part is modified and for what timestamp. This table is typically populated by data loading tools that call the trend_directory.mark_modified function. It is not populated automatically when inserting into the trend_store_part tables. The main purpose is to decouple the logging of data changes from actions triggered by those changes. There are no triggers on this table, and any actions should be triggered by changes on the trend_directory.modified table, which is updated by a separate processed based on the contents of this table.

Column	Type	Nullable	Description
id	bigint	✘	Unique identifier for the log entry
trend_store_part_id	integer	✘	Reference to the trend_store_part
timestamp	timestamp with time zone	✘	Timestamp of the data in the trend_store_part
modified	timestamp with time zone	✘	Timestamp of the moment of modification

modified

Stores information on when trend store parts have changed and for what timestamp. The information in this table is updated when the data has changed and any actions like materialization-state-updating can be triggered (using insert, update or delete triggers) from this table, because it is decoupled from the data loading processes.

Column	Type	Nullable	Description
trend_store_part_id	integer	✘
timestamp	timestamp with time zone	✘
first	timestamp with time zone	✘	Time of the first modification
last	timestamp with time zone	✘	Time of the last modification

materialization

A materialization is a materialization that uses the data from the view registered in the src_view column to populate the target trend store.

Column	Type	Nullable	Description
id	integer	✘	The unique identifier of this materialization
dst_trend_store_part_id	integer	✘	The ID of the destination trend_store_part
processing_delay	interval	✘	The time after the destination timestamp before this materialization can be executed
stability_delay	interval	✘	The time to wait after the most recent modified timestamp before the source data is considered ‘stable’
reprocessing_period	interval	✘	The maximum time after the destination timestamp that the materialization is allowed to be executed
enabled	bool	✘	Indicates if jobs should be created for this materialization (manual execution is always possible)

materialization_metrics

Metrics on individual materializations.

Column	Type	Nullable	Description
materialization_id	integer	✘	The ID of the materialization
execution_count	integer	✘
total_duration	interval	✘

view_materialization

A view_materialization is a materialization that uses the data from the view registered in the src_view column to populate the target trend_store_part.

Column	Type	Nullable	Description
id	integer	✘	The unique identifier of this view materialization
materialization_id	integer	✘
src_view	text	✘

function_materialization

A function_materialization is a materialization that uses the data from the function registered in the src_function column to populate the target trend_store_part.

The function must have the form of:

(timestamp with time zone) -> TABLE(
  entity_id integer,
  timestamp timestamp with time zone,
  ...
)

Column	Type	Nullable	Description
id	integer	✘	The unique identifier of this function materialization
materialization_id	integer	✘
src_function	text	✘

materialization_state

Stores the relation between the state of the sources used for the materialization and the state of the materialized data, so that from this table, it can be decided if a new materialization should be done.

Column	Type	Nullable	Description
materialization_id	integer	✘	The ID of the materialization type
timestamp	timestamp with time zone	✘	The timestamp of the materialized (materialization result) data
source_fingerprint	jsonb	✔	Aggregate state of all sources
processed_fingerprint	jsonb	✔	Snapshot of the source_fingerprint at the time of the most recent materialization
max_modified	timestamp with time zone	✔	Date of last data received
job_id	bigint	✔	ID of the most recent job for this materialization

function_materialization_state

Stores the relation between the state of the sources used for the materialization and the state of the materialized data, so that from this table, it can be decided if a new materialization should be done.

Column	Type	Nullable	Description
materialization_id	integer	✘	The ID of the materialization type
timestamp	timestamp with time zone	✘	The timestamp of the materialized (materialization result) data
source_fingerprint	jsonb	✔	Aggregate state of all sources
processed_fingerprint	jsonb	✔	Snapshot of the source_fingerprint at the time of the most recent materialization
job_id	bigint	✔	ID of the most recent job for this materialization

materialization_tag_link

Links tags to materializations. Examples of tags to link to a materialization might be: online, offline, aggregation, kpi, etc.

Column	Type	Nullable	Description
materialization_id	integer	✘
tag_id	integer	✘

materialization_trend_store_link

Stores the dependencies between a materialization and its source table trend store parts. Multiple levels of views and functions may exist between a materialization and its source table trend stores. These intermediate views and functions are not registered here, but only the table trend stores containing the actual source data used in the view. The timestamp_mapping_func column stores the function to map a timestamp of the source (trend_store_part) to a timestamp of the target (the view and target trend_store_part).

Column	Type	Nullable	Description
materialization_id	integer	✘	Reference to a materialization.
trend_store_part_id	integer	✘	Reference to a trend_store_part that is a source of the materialization referenced by materialization_id.
timestamp_mapping_func	regprocedure	✘	The function that maps timestamps in the source table to timestamps in the materialized data. For example, for a view for an hour aggregation from 15 minute granularity data will need to map 4 timestamps in the source to 1 timestamp in the resulting data.

modified_log_processing_state

Column	Type	Nullable	Description
name	text	✘
last_processed_id	bigint	✘
updated	timestamp with time zone	✘

Functions

cleanup_for_materialization()

create_metrics_for_materialization()

source_fingerprint_sql(trend_directory.materialization)

Returns the query to generate fingerprints for the specified view materialization.

source_fingerprint(trend_directory.materialization, timestamp with time zone)

Returns the fingerprint of the combined states of all sources required to calculate the data for the target timestamp.

cleanup_for_view_materialization()

define_materialization(dst_trend_store_part_id integer, processing_delay interval, stability_delay interval, reprocessing_period interval)

Define a materialization

undefine_materialization(name name)

Undefine and remove a materialization

define_view_materialization(dst_trend_store_part_id integer, processing_delay interval, stability_delay interval, reprocessing_period interval, src_view regclass)

Define a materialization that uses a view as source

define_function_materialization(dst_trend_store_part_id integer, processing_delay interval, stability_delay interval, reprocessing_period interval, src_function regproc)

Define a materialization that uses a function as source

cleanup_for_function_materialization()

max_modified(trend_directory.materialization, timestamp with time zone)

update_source_fingerprint(trend_directory.materialization, timestamp with time zone)

Update the fingerprint of the sources in the materialization_state table.

map_timestamp(trend_directory.materialization_trend_store_link, timestamp with time zone)

Map timestamp using the mapping function defined in the link

update_materialization_state(integer, timestamp with time zone)

new_modified()

base_table_schema()

staging_table_schema()

view_schema()

granularity_to_text(interval)

base_table_name(trend_directory.trend_store_part)

view_name(trend_directory.trend_view_part)

fingerprint_function_name(trend_directory.materialization)

to_char(trend_directory.trend_store_part)

to_char(trend_directory.trend_view_part)

get_trend_view(data_source_name text, entity_type_name text, granularity interval)

partition_schema()

partition_name(trend_directory.trend_store_part, integer)

index_to_timestamp(partition_size interval, index integer)

timestamp_to_index(interval, timestamp with time zone)

timestamp_to_index(trend_directory.trend_store, timestamp with time zone)

create_partition_sql(trend_directory.trend_store_part, integer)

get_partition_size(trend_directory.trend_store_part)

create_partition(trend_directory.trend_store_part, integer)

create_partition(trend_directory.trend_store_part, timestamp with time zone)

column_spec(trend_directory.table_trend)

column_spec(trend_directory.generated_table_trend)

column_specs(trend_directory.trend_store_part)

create_base_table_sql(trend_directory.trend_store_part)

create_base_table(trend_directory.trend_store_part)

drop_base_table_sql(trend_directory.trend_store_part)

drop_base_table(trend_directory.trend_store_part)

staging_table_name(trend_directory.trend_store_part)

create_staging_table_sql(trend_directory.trend_store_part)

create_staging_table(trend_directory.trend_store_part)

drop_staging_table_sql(trend_directory.trend_store_part)

drop_staging_table(trend_directory.trend_store_part)

initialize_trend_store_part(trend_directory.trend_store_part)

Create all database objects required for the trend store part to be fully functional and capable of storing data.

deinitialize_trend_store_part(trend_directory.trend_store_part)

Remove all database objects related to the table trend store part

get_default_partition_size(granularity interval)

Return the default partition size in seconds for a particular granularity

define_table_trend(trend_store_part_id integer, trend_directory.trend_descr)

define_generated_table_trend(trend_store_part_id integer, trend_directory.generated_trend_descr)

define_trend_view(data_source_name text, entity_type_name text, granularity interval)

create_view_sql(trend_directory.trend_view_part, sql text)

get_view_trends(view_name name)

show_trends(trend_directory.trend_store_part)

initialize_trend_view_part(trend_directory.trend_view_part, query text)

initialize_trend_view(trend_directory.trend_view, trend_directory.trend_view_part_descr[])

create_trend_view(data_source_name text, entity_type_name text, granularity interval, trend_directory.trend_view_part_descr[])

define_table_trends(trend_directory.trend_store_part, trend_directory.trend_descr[])

define_generated_table_trends(trend_directory.trend_store_part, trend_directory.generated_trend_descr[])

rename_trend_store_part(trend_directory.trend_store_part, name)

get_index_on(name, name)

trend_store(trend_directory.trend_store_part)

get_trend_store(data_source_name text, entity_type_name text, granularity interval)

get_trend_store_id(trend_directory.trend_store)

define_trend_store(data_source_name text, entity_type_name text, granularity interval, partition_size interval)

delete_trend_store(integer)

delete_trend_store(data_source_name text, entity_type_name text, granularity interval)

initialize_trend_store(trend_directory.trend_store)

define_trend_store_part(trend_store_id integer, name name)

define_trend_store_part(trend_store_id integer, name name, trends trend_directory.trend_descr[], generated_trends trend_directory.generated_trend_descr[])

create_trend_store_part(trend_store_id integer, name name)

get_trend_store_parts(trend_store_id integer)

get_trend_store_part(trend_store_id integer, name name)

get_trend_store_part_id(trend_directory.trend_store_part)

get_or_create_trend_store_part(trend_store_id integer, name name)

add_missing_trend_store_parts(trend_directory.trend_store, parts trend_directory.trend_store_part_descr[])

define_trend_store(trend_directory.trend_store, trend_directory.trend_store_part_descr[])

define_trend_store(data_source_name text, entity_type_name text, granularity interval, partition_size interval, trends trend_directory.trend_store_part_descr[])

create_trend_store(data_source_name text, entity_type_name text, granularity interval, partition_size interval, parts trend_directory.trend_store_part_descr[])

staged_timestamps(part trend_directory.trend_store_part)

transfer_staged(trend_store_part trend_directory.trend_store_part, timestamp timestamp with time zone)

transfer_staged(trend_store_part trend_directory.trend_store_part)

alter_trend_name(trend_directory.trend_store_part, trend_name name, new_name name)

table_columns(namespace name, table name)

table_columns(oid)

drop_view_sql(trend_directory.trend_view_part)

drop_view(trend_directory.trend_view_part)

add_column_sql_part(trend_directory.table_trend)

add_trends_to_trend_store_part(trend_directory.trend_store_part, trend_directory.table_trend[])

create_table_trends(trend_directory.trend_store_part, trend_directory.trend_descr[])

add_generated_column_sql_part(trend_directory.generated_table_trend)

add_generated_trends_to_trend_store_part(trend_directory.trend_store_part, trend_directory.generated_table_trend[])

create_generated_table_trends(trend_directory.trend_store_part, trend_directory.generated_trend_descr[])

missing_table_trends(trend_directory.trend_store_part, required trend_directory.trend_descr[])

missing_generated_table_trends(trend_directory.trend_store_part, required trend_directory.generated_trend_descr[])

missing_generated_table_trends(trend_store_part name, required trend_directory.generated_trend_descr[])

assure_table_trends_exist(trend_directory.trend_store_part, trend_directory.trend_descr[], trend_directory.generated_trend_descr[])

add_trends(trend_directory.trend_store, parts trend_directory.trend_store_part_descr[])

remove_table_trend(trend trend_directory.table_trend)

trend_store_part_name_for_trend(trend trend_directory.table_trend)

get_trends_for_trend_store(trend_store_id integer)

get_trends_for_trend_store(trend_directory.trend_store)

get_trend_if_defined(trend trend_directory.table_trend, trends trend_directory.trend_descr[])

Return the trend, but only if it is a trend defined by trends

remove_trend_if_extraneous(trend trend_directory.table_trend, trends trend_directory.trend_descr[])

Remove the trend if it is not one that is described by trends

get_trends_for_trend_store_part(trend_store_part_id integer)

get_trends_for_trend_store_part(trend_directory.trend_store_part)

remove_extra_trends(trend_directory.trend_store_part, trend_directory.trend_descr[])

remove_extra_trends(trend_directory.trend_store, parts trend_directory.trend_store_part_descr[])

change_table_trend_data_unsafe(trend_directory.table_trend, data_type text, entity_aggregation text, time_aggregation text)

data_type_order(data_type text)

greatest_data_type(data_type_a text, data_type_b text)

change_table_trend_data_safe(trend_directory.table_trend, data_type text, entity_aggregation text, time_aggregation text)

change_trend_data_unsafe(trend trend_directory.table_trend, trends trend_directory.trend_descr[], partname text)

change_trend_data_safe(trend trend_directory.table_trend, trends trend_directory.trend_descr[], partname text)

change_all_trend_data(trend_directory.trend_store, parts trend_directory.trend_store_part_descr[])

change_trend_data_upward(trend_directory.trend_store, parts trend_directory.trend_store_part_descr[])

change_trendstore_strong(trend_directory.trend_store, parts trend_directory.trend_store_part_descr[])

change_trendstore_weak(trend_directory.trend_store, parts trend_directory.trend_store_part_descr[])

get_most_recent_timestamp(dest_granularity interval, ts timestamp with time zone)

is_integer(varchar)

get_most_recent_timestamp(dest_granularity varchar, ts timestamp with time zone)

get_timestamp_for(granularity interval, ts timestamp with time zone)

get_timestamp_for(granularity varchar, ts timestamp with time zone)

get_table_trend(trend_directory.trend_store_part, name)

trend_store_part_has_trend_with_name(part trend_directory.trend_store_part, trend_name name)

column_exists(table_name name, column_name name)

get_max_modified(trend_directory.trend_store, timestamp with time zone)

update_modified(trend_store_part_id integer, timestamp timestamp with time zone, modified timestamp with time zone)

process_modified_log(start_id bigint)

process_modified_log()

mark_modified(trend_store_part_id integer, timestamp timestamp with time zone, modified timestamp with time zone)

Stores a record in the trend_directory.modified_log table.

mark_modified(trend_store_id integer, timestamp timestamp with time zone)

Stores a record in the trend_directory.modified_log table.

clear_trend_store_part_sql(trend_store_part_id integer)

Return the query to remove all records of the specified timestamp from the trend_store_part.

clear_trend_store_part(trend_store_part_id integer, timestamp timestamp with time zone)

Removes all records of the specified timestamp from the trend_store_part and returns the removed record count.

transfer(materialization trend_directory.view_materialization, timestamp timestamp with time zone)

Transfer all records of the specified timestamp from the view to the target trend store of the materialization.

transfer(materialization trend_directory.function_materialization, timestamp timestamp with time zone)

transfer(materialization trend_directory.materialization, timestamp timestamp with time zone)

materialize(materialization trend_directory.materialization, timestamp timestamp with time zone)

Materialize the data produced by the referenced view of the materialization by clearing the timestamp in the target trend_store_part and inserting the data resulting from the view into it.

show_trends(trend_store_part_id integer)

clear(trend_directory.trend_store_part, timestamp with time zone)

to_char(trend_directory.materialization)

add_new_state()

update_modified_state()

delete_obsolete_state()

update_state()

dst_trend_store_part(trend_directory.materialization)

dst_trend_store_part(trend_directory.view_materialization)

dst_trend_store_part(trend_directory.function_materialization)

columns_part(trend_directory.materialization)

Return the comma separated, quoted list of column names to be used in queries

columns_part(trend_directory.view_materialization)

Return the comma separated, quoted list of column names to be used in queries

columns_part(trend_directory.function_materialization)

Return the comma separated, quoted list of column names to be used in queries

cleanup_on_data_source_delete()

changes_on_trend_update()

update_modified_column()

cleanup_trend_store_part_on_delete()

drop_view_on_delete()