Databricks - Cache Settings

Less than 1 minute

This guide explains how to configure caching for the Generic SQL Driver to improve performance and reduce database load.

Accessing Cache Settings

To configure caching for your connection:

Open Data Sources from the ☰ menu
Select your Region and Site
Click the Generic SQL tab
Select your existing connection or create a new one
Click the Cache tab in the connection dialog

Cache Types

The Generic SQL Driver supports three types of caching:

Cache Asset Trees

Enable the Cache Asset Trees checkbox to cache asset hierarchies.

Scheduling Options:

Download assets every - Set interval in minutes/hours/days (starts from midnight UTC)
By scheduling (CRON) - Use CRON format for more precise scheduling (marked as "High priority")

If both scheduling options are configured, the CRON schedule takes priority.

Asset Paths:
Enter asset paths in the large text area, separated by commas. This specifies which asset hierarchies to cache.

Cache Values

Enable the Cache values checkbox to cache tag data listed in the "Signals to cache" CSV file.

Time Window Configuration:

Start Pull From (UTC) - Beginning of the daily cache update window
End Pull At (UTC) - End of the daily cache update window

Cache Dataset Tags

Enable the Cache Dataset Tags checkbox to cache virtual tag results listed in the "Signals to cache" CSV file.

CAUTION! Caching dataset tags results can be too expensive for RAM

Signals to Cache CSV File

The Generic SQL driver needs to know which tags should be cached.
Upload a CSV file using the Choose File button under "Signals to cache".

CSV Format Requirements:
The CSV must contain four mandatory columns: datasourceID, dataID, pulling time, and history time.

Complete format:

dataSourceID;FQNID;updateInterval;history;updateDelay;tagName

Column Descriptions:

dataSourceID - The unique ID of the IOTA data source
FQNID - A unique tag ID
updateInterval - How often the driver will update cache for the related tag (pulling time)
history - History time window: defines the historical time range (e.g., 1d, 1w, 1m) for which data is cached
updateDelay - The delay of the first time caching from the driver's start time
tagName - Name of the tag. Can be skipped

You can create a template for the file contents using the script provided below. Then you will need to set the updateInterval, history and updateDelay columns and save the edited template to a .csv file.

How to Collect the Cache Tags List in CSV Format

To collect the list of all tags placed on a view, you can create a ButtonPV and apply the script listed below for the onClick Action.
The script creates a template for tag caching parameters and passes it to the Input Text Area with the variable taCsv.

JavaScript Script for Generating CSV Template

const taCsv = getComponent("taCsv"); // get text area component to write CSV template

taCsv.settings.value = "";

try {
    for (let key of Object.keys(this.$dashboardComponents)) {
        let component = this.$dashboardComponents[key];

        if (component.channels?.length) {
            for (let chan of component.channels) {
                if (chan.fqn?.type == "attribute" || chan.fqn?.type == "tag") {
                    let record = "";
                    if (chan.fqn.drvid && chan.fqn.id) {
                        record = `${chan.fqn.drvid};${chan.fqn.drvid};0s;0d;0m;${chan.name}\n`;
                    } else {
                        continue;
                    }
                    taCsv.settings.value += record;
                } else if (chan.fqn?.type == "element") {
                    if (
                        component.settings?.templateMapping &&
                        component.settings.templateMapping["No Template"]
                    ) {
                        for (let props of component.settings.templateMapping[
                            "No template"
                        ].props) {
                            const attributeName = props.attribute.name;
                            for (let attribute of chan.attributes) {
                                if (attributeName == attribute.name) {
                                    let record = "";
                                    if (
                                        attribute.fqn.id &&
                                        attribute.fqn.drvid
                                    ) {
                                        record = `${attribute.fqn.drvid};${attribute.fqn.drvid};0s;0d;0m;${attribute.name}\n`;
                                    } else {
                                        continue;
                                    }
                                    taCsv.settings.value += record;
                                }
                            }
                        }
                    }
                }
            }
        }
    }
} catch (ex) {
    taCsv.settings.value = ex.message;
}

Example Output:
This script will fill the text area input with the list of tags caching options template like shown below:

12;db_cs_1\asset_tree\FSA\experimental\carding machine\cos;0m;0d;0m;cos
12;db_cs_1\asset_tree\FSA\experimental\carding machine\counter;0m;0d;0m;counter
12;db_cs_1\asset_tree\FSA\experimental\carding machine\sin;0m;0d;0m;sin
...

Need help? Contact support@iotasoft.com or your Customer Success Advisor (CSA).