Skip to main content

Cache and Jobs

This section is with regards to caching resources which can be mappings or ontology views, and creating jobs to schedule the cachings.

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}')

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}')

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

  1. = - Equals
  2. != or <> - Not equals
  3. IN(..) - Includes
  4. NOT IN(..) - Not includes
  5. LIKE (can be used with wildcards combinations like % and [] ) - Contains
  6. NOT LIKE (can be used with wildcards combinations like % and [] ) - Not contains
  7. > - Greater than
  8. >= - Greater than or equal
  9. < - Less than
  10. <= - Less than or equal
  11. IS NULL - Null value
  12. 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

  1. = - Equals
  2. != or <> - Not equals
  3. IN(..) - Includes
  4. NOT IN(..) - Not includes
  5. LIKE (can be used with wildcards combinations like % and [] ) - Contains
  6. NOT LIKE (can be used with wildcards combinations like % and [] ) - Not contains
  7. > - Greater than
  8. >= - Greater than or equal
  9. < - Less than
  10. <= - Less than or equal
  11. IS NULL - Null value
  12. IS NOT NULL - Is not null value
REFRESH CACHE VIEW {view_name}
    WHERE {partition_property} {sql_operator} {comparator_value}

Create job to schedule a full cache refresh on a mapping​

Creates a recurring job to do a full cache refresh on a mapping

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

  • {job_name} - The name of the job to be created
  • {refresh_value} - The interval for which the job should be run.
  • {mapping_name} - The name of the cache for the mapping you want to fully refresh
Intervals for refresh_value

Can be one of the following values:

  1. none - Job runs only once
  2. daily - Job will run once a day
  3. monthly - Job will run once a month
  4. yearly - Job will run once a year
  5. cron_expression - A cron expression is a string comprising five or six fields separated by white space that represents a set of times, to schedule when the job should be executed. Visit here for more information
CREATE OR REPLACE JOB {job_name}
    REFRESH '{refresh_value}' 
        AS REFRESH CACHE MAPPING {mapping_name}

Create job to schedule an incremental cache refresh on a mapping​

Creates a recurring job to do a full cache refresh on a mapping

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

  • {job_name} - The name of the job to be created
  • {refresh_value} - The interval for which the job should be run.
  • {mapping_name} - The name of the cache for the 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.
Intervals for refresh_value

Can be one of the following values:

  1. none - Job runs only once
  2. daily - Job will run once a day
  3. monthly - Job will run once a month
  4. yearly - Job will run once a year
  5. cron_expression - A cron expression is a string comprising five or six fields separated by white space that represents a set of times, to schedule when the job should be executed. Visit here for more information
CREATE OR REPLACE JOB {job_name}
    REFRESH {refresh_value}
    AS REFRESH CACHE MAPPING {mapping_name}
        WHERE {partition_property} > CURRENT_DATE - {days_value}

Create job to schedule a full cache refresh on an ontology view​

Creates a recurring job to do a full cache refresh on an ontology view

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

  • {job_name} - The name of the job to be created
  • {refresh_value} - The interval for which the job should be run.
  • {view_name} - The name of the cache for the ontology view you want to fully refresh
Intervals for refresh_value

Can be one of the following values:

  1. none - Job runs only once
  2. daily - Job will run once a day
  3. monthly - Job will run once a month
  4. yearly - Job will run once a year
  5. cron_expression - A cron expression is a string comprising five or six fields separated by white space that represents a set of times, to schedule when the job should be executed. Visit here for more information
CREATE OR REPLACE JOB {job_name}
    REFRESH '{refresh_value}' 
        AS REFRESH CACHE VIEW {view_name}

Create job to schedule an incremental cache refresh on an ontology view​

Creates a recurring job to do a full cache refresh on an ontology view

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

  • {job_name} - The name of the job to be created
  • {refresh_value} - The interval for which the job should be run.
  • {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.
Intervals for refresh_value

Can be one of the following values:

  1. none - Job runs only once
  2. daily - Job will run once a day
  3. monthly - Job will run once a month
  4. yearly - Job will run once a year
  5. cron_expression - A cron expression is a string comprising five or six fields separated by white space that represents a set of times, to schedule when the job should be executed. Visit here for more information
CREATE OR REPLACE JOB {job_name}
    REFRESH {refresh_value}
    AS REFRESH CACHE VIEW {view_name}
        WHERE {partition_property} > CURRENT_DATE - {days_value}

Run a job​

Executes an existing job manually

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

  • {job_name} - The name of the job to run
RUN JOB {job_name}

Remove a job​

Deletes an existing job

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

  • {job_name} - The name of the job to remove
DROP JOB {job_name}

Stop a running job​

Stops the execution of an existing running job

Required information to stop a running job (the curly brackets {} should not be an input, they are used only as a variable substitution):

  • {job_id} - The id of the job to stop
KILL JOB {job_id}