Plugins 〉SQLite

Data Source
community

SQLite

  • Overview
  • Installation
  • Change log
  • Related content

Grafana SQLite Datasource

License stability-stable Test Branch Test Release

This is a Grafana backend plugin to allow using an SQLite database as a data source. The SQLite database needs to be accessible to the filesystem of the device where Grafana itself is running.

Plugin Installation

The most up to date (but also most generic) information can always be found here: Grafana Website - Plugin Installation

Recommended: Installing the Official and Released Plugin on an Existing Grafana With the CLI

Grafana comes with a command line tool that can be used to install plugins.

  1. Run this command: grafana-cli plugins install frser-sqlite-datasource
  2. Restart the Grafana server.
  3. Login in with a user that has admin rights. This is needed to create datasources.
  4. To make sure the plugin was installed, check the list of installed datasources. Click the Plugins item in the main menu. Both core datasources and installed datasources will appear.

Latest Version: Installing the newest Plugin Version on an Existing Grafana With the CLI

The grafana-cli can also install plugins from a non-standard URL. This way even plugin versions, that are not (yet) released to the official Grafana repository can be installed.

  1. Run this command:

    # replace the $VERSION part in the URL below with the desired version (e.g. 2.0.2)
    grafana-cli --pluginUrl https://github.com/fr-ser/grafana-sqlite-datasource/releases/download/v$VERSION/frser-sqlite-datasource-$VERSION.zip plugins install frser-sqlite-datasource
    
  2. See the recommended installation above (from the restart step)

Manual: Installing the Plugin Manually on an Existing Grafana

In case the grafana-cli does not work for whatever reason plugins can also be installed manually.

  1. Get the zip file from Latest release on Github

  2. Extract the zip file into the data/plugins subdirectory for Grafana: unzip <the_download_zip_file> -d <plugin_dir>/

    Finding the plugin directory can sometimes be a challenge as this is platform and settings dependent. A common location for this on Linux devices is /var/lib/grafana/plugins/

  3. See the recommended installation above (from the restart step)

Configuring the Datasource in Grafana

The only required configuration is the path to the SQLite database (local path on the Grafana Server)

  1. Add an SQLite datasource.
  2. Set the path to the database (the grafana process needs to find the SQLite database under this path).
  3. Save the datasource and use it.

Support for Time Formatted Columns

SQLite has no native "time" format. It relies on strings and numbers for time and dates. Since especially for time series Grafana expects an actual time type, however, the plugin provides a way to infer a real timestamp. This can be set in the query editor by providing the name of the column, which should be reformatted to a timestamp.

The plugin supports two different inputs that can be converted to a "time" depending on the type of the value in the column, that should be formatted as "time":

  1. A number input: It is assumed to be a unix timestamp / unix epoch and will be converted to an integer before converting it to a timestamp.

  2. A string input: The value is expected to be formatted in accordance with RFC3339, e.g. "2006-01-02T15:04:05Z07:00". Edge cases might occur and the parsing library used is the source of truth here: https://golang.org/pkg/time/#pkg-constants.

Timestamps stored as unix epoch should work out of the box, but the string formatting might require adjusting your current format. The below example shows how to convert a "date" column to a parsable timestamp:

WITH converted AS (
   -- a row looks like this (value, date): 1.45, '2020-12-12'
   SELECT value,  date || 'T00:00:00Z' AS datetime FROM raw_table
)
SELECT datetime, value FROM converted ORDER BY datetime ASC

Macros

This plugins supports macros inspired by the built-in Grafana datasources (e.g. https://grafana.com/docs/grafana/latest/datasources/postgres/#macros).

However, as each macro needs to be re-implemented from scratch, only the following macros are supported. Other macros (that you might expect from other SQL databases) do not work

$__unixEpochGroupSeconds(unixEpochColumnName, intervalInSeconds)

Example: $__unixEpochGroupSeconds("time", 10)

Will be replaced by an expression usable in GROUP BY clause. For example: cast(("time" / 10) as int) * 10

$__unixEpochGroupSeconds(unixEpochColumnName, intervalInSeconds, NULL)

Example: $__unixEpochGroupSeconds(timestamp, 10, NULL)

Same as above but with a fill parameter so missing points in that series will be added for Grafana and NULL will be used as value.

In case multiple time columns are provided the first one is chosen as the column to determine the gap filling. "First" in this context means first in the SELECT statement. This column needs to have no NULL values and must sorted in ascending order.

Alerting

This plugins supports the Grafana alerting feature. Similar to the built in data sources alerting does not support variables as they are normally replaced in the frontend, which is not involved for the alerts. In order to allow time filtering this plugin supports the variables $__from and $__to. For more information about those variables see here: https://grafana.com/docs/grafana/latest/variables/variable-types/global-variables/#__from-and-__to. Formatting of those variables (e.g. ${__from:date:iso}) is not supported for alerts, however.

ARMv7 / Raspberry Pi 2 Mod. B Support

Many programs for ARMv6 (the default 32bit ARM version of this plugin) can run on ARMv7 machines (the version running on Raspberry Pi 2 Mod. B). In case there are complications you can try out the version specifically built for ARMv7, though.

A plugin version specifically built for ARMv7 devices can be found on the Github release page (see manual installation above).

Development and Contributing

Any contribution is welcome. Some information regarding the local setup can be found in the DEVELOPMENT.md file.

For more information, visit the docs on plugin installation.

Changelog

All notable changes to this project will be documented in this file.

The format is based on Keep a Changelog

[2.1.1]

This release adds support for sub second precision for unix time.

Added

  • When using numeric values for a timestamp in SQLite (unix timestamp) the plugin now supports precision below the second (at nanosecond precision)

[2.1.0]

This release adds the JSON extension to the compiled SQLite code.

Added

  • JSON extension for SQLite

[2.0.2]

This release adds testing against Grafana v8.1.0 and fixes an issue with query variables.

Fixed

  • Query variables can now also be used in Grafana v8.X.X

[2.0.1]

This release fixes some long standing issues that prevented the right use of the alerting feature of the plugin even though it was enabled already.

Fixed

  • Using the $__from and $__to variables for alerting
  • Fixing a caching bug for the query (for alerting)

[2.0.0]

All current Raspberry Pi Zero and 1 models have an ARMv6 architecture. All other models (2 Mod. B v1.2, 3 and 4) have an 64Bit ARMv8 architecture. As only the Raspberry Pi 2 Mod. B has an ARMv7 architecture this is not used as default anymore. The Raspberry Pi 2 Mod. B will require a manual installation and all others will be handled via the Grafana CLI.

Changed

  • Using ARMv6 instead of ARMv7 as 32Bit ARM default

[1.2.1]

Added

  • More debug level logging from the plugin

Fixed

  • The type inference of columns in the backend is now ignoring the letter casing

[1.2.0]

Added

  • The response of the plugin includes the final query as metadata and can be checked in the inspector now
  • Macro unixEpochGroupSeconds:
    • replace time columns with an expression to group by
    • Allow filling up missing values with NULL

Fixed

  • return additional time formatted column for time-series formats as normal values (previously they were skipped)

[1.1.0]

Added

  • Experimental support for MacOS (no static linking)

[1.0.3]

Fixed

  • Showing better error messages for certain fail conditions of the plugin health check (e.g. permission error)

[1.0.2]

Fixed

  • Fixed bug preventing using query variables when SQLite is the default datasource (<= Grafana 7.4)

[1.0.1]

Added

  • Enabled the alerting feature for the plugin (no code change)

[1.0.0]

No breaking change was introduced but due to code stability the first major version is released.

Fixed

  • variables like $__interval and $__interval_ms are supported now

[0.2.7]

Changed

  • Changing plugin name to SQLite

  • added category to plugin.json for better grouping on the Grafana homepage

  • updated Readme after first official release of plugin on Grafana homepage

[0.2.6]

Added

  • Documentation about the time conversion in the README and in the query editor help text.

[0.2.5]

Fixed

  • Correct handling of "NUMERIC" columns with mixed data (e.g. float and integer)

[0.2.4]

Added

  • Added option to explicitly convert backend data frame to time series:

    • This requires the initial data frame to be in a Long Format

    • The resulting time series consists of one data frame per metric

[0.2.3]

Changed

  • Releasing arm6 (RaspberryPi Zero) as separate distribution (Github only)

Fixed

  • Renamed the arm7 executable to arm (newer Raspberry Models should run fine now)

[0.2.2]

Changed

  • Different content of zip file published with Github release according to new Grafana v7.3 standards

[0.2.1]

Added

  • Query variables are now supported

[0.2.0]

Added

  • The plugin is now signed

Changed

  • For Signing grafana-toolkit 7.3.3 was necessary. The grafana version to test against was bumped to version 7.3.3

[0.1.3]

Fixed

  • Fixed: Handling of NULL values in queries is now correct

[0.1.2]

First "working" version

Fixed

  • Fixed: Plugin files in the zip file are now executable