Cache

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

Checking disk space for cached data

If you are caching data on disk, you can check available disk space by running the following query.

Make sure to select the cache source as your database for this query to function properly

SELECT formatReadableSize(total_space) as disk_total_space,
  formatReadableSize(free_space) as disk_free_space,
  formatReadableSize(total_space - free_space) as used_space
FROM system.disks

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​