Cache

This section is with regards to all actions related to caching resources which can be mappings or ontology views.

Cache mappings

Create a cache for a mapping

Required information for creating a cache for a mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the mapping you want to cache
{cache_schema} - The schema name on which the cached mapping should be stored

CACHE MAPPING {mapping_name} OPTIONS (`schema`='{cache_schema}')

Cache a mapping with partition

Create a cache for a mapping with partition on a property

Required information for creating a cache for a mapping with a partition (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the mapping you want to cache
{virtualization_datasource} - The name of the virtualization datasource which stores and runs the cached mapping
{cache_schema} - The schema name on which the cached mapping should be stored
{partition_property} - The name of the property by which to partition the cache

CACHE MAPPING {mapping_name} USING {virtualization_datasource}
    OPTIONS (`schema`='{cache_schema}',  partition='{partition_property}')

Cache a mapping with partition and split

Create a cache for a mapping with partition on a property and a split to split the partitioned data

Required information for creating a cache for a mapping with a partition and a split (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the mapping you want to cache
{virtualization_datasource} - The name of the virtualization datasource which stores and runs the cached mapping
{cache_schema} - The schema name on which the cached mapping should be stored
{partition_property} - The name of the property by which to partition the cache
{split_value} - The split value depends on the property, if its a timestamp, date or datetime then the value can be daily, monthly, or yearly. The split value can also be partition to split by the {partition_property}.

CACHE MAPPING {mapping_name} USING {virtualization_datasource} 
  OPTIONS (`schema`='{cache_schema}', partition='{partition_property}', split='{split_value}')

Cache a mapping to object storage

Create a cache for a mapping to object storage. Timbr currently supports

S3
Azure Datalake Storage
Google Cloud Storage

Required information for creating a cache for a mapping to object storage (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the mapping you want to cache
{virtualization_datasource} - The name of the virtualization datasource on which to run the cache mapping job
{target_datasource_id} - The name of the object store in which to store the cached mapping
{cache_schema} - The schema name on which the cached mapping should be stored
{partition_property} - The name of the property by which to partition the cache

CACHE MAPPING {mapping_name} USING {virtualization_datasource} 
  INTO {target_datasource_id} 
  OPTIONS (`schema`='{cache_schema}')

using & into

INTO is where to cache the data (the target datasource). USING is used when you cache into the Datalake (S3/HDFS/Azure Blob Storage) and you need to choose which datasource to run the job.

USING is only applicable when the target datasource is a Datalake and you have multiple virtualization clusters installed (one for jobs and one of executing queries).

Cache an ontology view

Create a cache for an ontology view

Required information for creating a cache for an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the view you want to materialize
{cache_schema} - The schema name on which the materialized view should be stored

CACHE VIEW {view_name} OPTIONS (`schema`='{cache_schema}')

Cache an ontology view with partition

Create a cache for an ontology view with partition on a property

Required information for creating a cache for an ontology view with a partition (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the view you want to cache
{virtualization_datasource} - The name of the virtualization datasource which stores and runs the cached view
{cache_schema} - The schema name on which the cached view should be stored
{partition_property} - The name of the property by which to partition the cache

CACHE VIEW {view_name} USING {virtualization_datasource}
    OPTIONS (`schema`='{cache_schema}', partition='{partition_property}')

Cache an ontology view with partition and split

Create a cache for an ontology view with partition on a property and a split to split the partitioned data

Required information for creating a cache for an ontology view with a partition and a split (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the view you want to cache
{virtualization_datasource} - The name of the virtualization datasource which stores and runs the cached view
{cache_schema} - The schema name on which the cached view should be stored
{partition_property} - The name of the property by which to partition the cache
{split_value} - The split value depends on the property, if its a timestamp, date or datetime then the value can be daily, monthly, or yearly. The split value can also be partition to split by the {partition_property}.

CACHE VIEW {view_name} USING {virtualization_datasource} 
  OPTIONS (`schema`='{cache_schema}', partition='{partition_property}', split='{split_value}')

Cache an ontology view to object storage

Create a cache for a view to object storage.

Supported object storage data sources

Timbr currently supports:

S3
Azure Datalake Storage
Google Cloud Storage

Required information for creating a cache for a view to object storage (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the view you want to cache
{virtualization_datasource} - The name of the virtualization datasource on which to run the cache view job
{target_datasource_id} - The name of the object store in which to store the cached view
{cache_schema} - The schema name on which the cached view should be stored
{partition_property} - The name of the property by which to partition the cache

CACHE VIEW {view_name} USING {virtualization_datasource} 
  INTO {target_datasource_id} 
  OPTIONS (`schema`='{cache_schema}')

using & into

INTO is where to cache the data (the target datasource). USING is used when you cache into the Datalake (S3/HDFS/Azure Blob Storage) and you need to choose which datasource to run the job.

USING is only applicable when the target datasource is a Datalake and you have multiple virtualization clusters installed (one for jobs and one of executing queries).

Remove a cached mapping (uncache)

Removes a cache for a mapping.

Required information to remove a cache for a mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the mapping you want to remove the cache from

UNCACHE MAPPING {mapping_name}

Remove a cached ontology view (uncache)

Removes a cache for an ontology view.

Required information to remove a cache for an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the view you want to remove the cache from

UNCACHE VIEW {view_name}

Full refresh a mapping cache

Refresh entirely the cache of a mapping

Required information to fully refresh a cache for a mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the cache mapping you want to fully refresh

REFRESH CACHE MAPPING {mapping_name}

Incrementally refresh a mapping cache

Refresh only a partition of the cache for a mapping based on the partition property

Required information to incrementally refresh a cache for a mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the cache mapping you want to incrementally refresh
{partition_property} - The name of the property by which the cache is partitioned by
{days_value} - An integer representing the amount of days to subtract from the current date in order to apply the cache.

REFRESH CACHE MAPPING {mapping_name}
    WHERE {partition_property} > CURRENT_DATE - {days_value}

Custom partition refresh for a mapping cache

Refresh a particular part of the cache based on the partition property

Required information for custom refresh of a cache of a mapping (the curly brackets {} should not be an input, they are used only as a variable substitution):

{mapping_name} - The name of the cache mapping you want to customly refresh
{partition_property} - The name of the property by which the cache is partitioned by
{sql_operator} - SQL operators used in the WHERE clause of an SQL statement.
{comparator_value} - The value to compare against the partition property

Supported SQL operators

The supported SQL operator are

= - Equals
!= or <> - Not equals
IN(..) - Includes
NOT IN(..) - Not includes
LIKE (can be used with wildcards combinations like % and [] ) - Contains
NOT LIKE (can be used with wildcards combinations like % and [] ) - Not contains
> - Greater than
>= - Greater than or equal
< - Less than
<= - Less than or equal
IS NULL - Null value
IS NOT NULL - Is not null value

REFRESH CACHE MAPPING {mapping_name}
    WHERE {partition_property} {sql_operator} {comparator_value}

Full refresh an ontology view cache

Refresh entirely the cache of an ontology view

Required information to fully refresh a cache for an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the cache for the ontology view you want to fully refresh

REFRESH CACHE VIEW {view_name}

Incrementally refresh an ontology view cache

Refresh only a partition of the cache for an ontology view based on the partition property

Required information to incrementally refresh a cache for an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the cache for the ontology view you want to incrementally refresh
{partition_property} - The name of the property by which the cache is partitioned by
{days_value} - An integer representing the amount of days to subtract from the current date in order to apply the cache.

REFRESH CACHE VIEW {view_name}
    WHERE {partition_property} > CURRENT_DATE - {days_value}

Custom partition refresh for an ontology view cache

Refresh a particular part of the cache based on the partition property

Required information for custom refresh of a cache of an ontology view (the curly brackets {} should not be an input, they are used only as a variable substitution):

{view_name} - The name of the cached ontology view you want to customly refresh
{partition_property} - The name of the property by which the cache is partitioned by
{sql_operator} - SQL operators used in the WHERE clause of an SQL statement.
{comparator_value} - The value to compare against the partition property

Supported SQL Operators

The supported SQL operators are

= - Equals
!= or <> - Not equals
IN(..) - Includes
NOT IN(..) - Not includes
LIKE (can be used with wildcards combinations like % and [] ) - Contains
NOT LIKE (can be used with wildcards combinations like % and [] ) - Not contains
> - Greater than
>= - Greater than or equal
< - Less than
<= - Less than or equal
IS NULL - Null value
IS NOT NULL - Is not null value

REFRESH CACHE VIEW {view_name}
    WHERE {partition_property} {sql_operator} {comparator_value}

Cache

Cache mappings​

Cache a mapping with partition​

Cache a mapping with partition and split​

Cache a mapping to object storage​

Cache an ontology view​

Cache an ontology view with partition​

Cache an ontology view with partition and split​

Cache an ontology view to object storage​

Remove a cached mapping (uncache)​

Remove a cached ontology view (uncache)​

Full refresh a mapping cache​

Incrementally refresh a mapping cache​

Custom partition refresh for a mapping cache​

Full refresh an ontology view cache​

Incrementally refresh an ontology view cache​

Custom partition refresh for an ontology view cache​