DocumentationPluginsSqlyze data source for Grafana

Grafana Sqlyze Data source

This plugin is not yet supported in Grafana Cloud.

The Grafana Sqlyze Data Source plugin is a versatile plugin that allows you to connect to hundreds of data sources using a single query language: SQL. Whether your data resides in traditional SQL databases, NoSQL databases, or other non-SQL data sources, this plugin enables you to query them all with SQL.

Overview

The Sqlyze plugin leverages the ODBC (Open Database Connectivity) interface to connect to various databases. ODBC is a standard API for accessing database management systems (DBMS). The following diagram illustrates how the plugin interacts with the different components in the data connection stack:

Copy

+-----------------------------------+
|           Sqlyze plugin           |
| +-----------------+               |
| |   Plugin        |               |
| +-----------------+               |
|         |                         |
|         | ODBC API Calls          |
|         v                         |
| +-------------------------+       |
| | unixODBC Driver Manager |       |
| +-------------------------+       |
|         |                         |
|         | Directs ODBC Calls      |
|         v                         |
+-----------------------------------+
          |
          v
+----------------+
|  ODBC Driver   |
| (e.g., MySQL,  | (ODBC Drivers that are needed to be installed for your specific DBMS / DB)
| PostgreSQL)    |
+----------------+
          |
          | Translates ODBC Calls to DB-Specific Commands
          v
+-----------------+
|      DBMS       | (Database Management System)
+-----------------+
          |
          | Manages Data Access
          v
+-----------------+
|    Database     |
+-----------------+

Configuration

Driver Configuration

The plugin requires two essential fields to be configured: Driver and Timeout.

• Driver: This is the absolute path to the ODBC driver on your system. The ODBC driver is specific to the database management system (DBMS) you are connecting to and must be installed separately.
• Timeout: This field sets the timeout for database connections in seconds.

Connection String Settings

ODBC uses a connection string under the hood to connect to the DBMS. This connection string is built from a concatenation of the driver path, timeout, and any other key value combinations provided in the Driver Settings sections of the settings UI. Here is an example connection string for an IBM DB2 database:

plaintext Copy

Driver=/home/ibm/db2/V11.5/lib64/libdb2o.so;Timeout=0;Hostname=db2.server.com;Port=50000;Uid=testUserUID;Pwd=Testing123;DB=testdb

The connection string format may vary depending on the database you’re connecting to. For specific connection strings, you can refer to resources like ConnectionStrings.com.

The Driver field can also contain a reference to the driver in configuration in the odbc.ini file, instead of referring to the driver directly. e.g.:

plaintext Copy

`Driver={MyDB2Database};Timeout=0;Hostname=db2.server.com;Port=50000;Uid=testUserUID;Pwd=Testing123;DB=testdb

Testing and Troubleshooting ODBC Connections

Proper setup of ODBC connections is critical for ensuring that your database communication works as expected. This section covers the key configuration files required for ODBC, as well as tools and commands to help you test and troubleshoot your setup.

The CLI tools mentioned below are likely not installed on your system and you will most likely need to install unixodbc and / or unixodbc-dev, ( if you are using ubuntu ) using the command below.

sh Copy

apt-get install unixodbc unixodbc-dev

ODBC Configuration Files

odbcinst -j

The odbcinst -j command is used to display the locations of important ODBC configuration files. This can help verify that your system is set up correctly.

sh Copy

$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /home/username/.odbc.ini

Example odbc.ini File

The odbc.ini file is used to define DSNs (Data Source Names), which represent individual database connections. Each DSN specifies the connection parameters for a particular database.

ini Copy

[ODBC Data Sources]
MyDB2Database=IBM DB2 ODBC Driver

[MyDB2Database]
Description=IBM DB2 Database
Driver=/home/ibm/db2/V11.5/lib64/libdb2o.so
Database=testdb
Hostname=db2.server.com
Port=50000
Uid=testUserUID
Pwd=Testing123

[ODBC]
Trace=Yes
TraceFile=/tmp/sql.log

Example odbcinst.ini File

The odbcinst.ini file is used to define the ODBC drivers installed on your system. Each section represents a different driver.

ini Copy

[Db2]
Description = Db2 Driver
Driver = <instance_path>/lib/libdb2o.so
fileusage=1
dontdlclose=1

Using odbc_cli to Verify Configuration Files

odbc_cli is a utility for checking your ODBC configuration files, such as ensuring the correct driver paths and DSN configurations.

Copy

$ odbc_cli verify
Configuration file(s) verified:
- /etc/odbcinst.ini
- /etc/odbc.ini

isql to verify connections

The isql tool is an interactive SQL shell that can be used to test the connection to your database via ODBC. It’s particularly useful for diagnosing issues with your ODBC configuration before using the Grafana plugin. It is installed with the unixodbc/unixodbc-dev packages.

sh Copy

$ isql MyDB2Database
+---------------------------------------+
| Connected!                            |
|                                       |
| SQL>                                  |
+---------------------------------------+

Time series

To format your SQL queries for time series data in Grafana, use the alias as time in your SQL queries. This allows Grafana to recognize the timestamp and render time series data correctly.

Example query:

sql Copy

select date_start as time, foo, avg(bar) as bar
from foo_bar

Platform Support

This plugin relies on ODBC for connectivity to various databases. It includes the unixODBC library, which facilitates the ODBC communication but does not include the utilities (such as command-line tools). Since ODBC is widely supported across operating systems like Windows, Linux, and macOS, the plugin is cross-platform. However, some additional dependencies might need to be installed on non-Linux systems.

Using ODBC with Linux ARM or macOS

Installing unixODBC

To use ODBC on Linux ARM or macOS, you must have unixODBC installed. This library is essential for ODBC operations on these platforms. Installation instructions can be found in the IBM documentation for installing unixODBC Driver Manager.

Short list of known drivers and settings

DB2

• Can be downloaded from IBM Fix Central where you can choose your DB2 Version and Platform: https://www.ibm.com/support/fixcentral/swg/selectFixes?parent=ibm%7EInformation%20Management&product=ibm/Information+Management/IBM+Data+Server+Client+Packages&release=11.5.*&platform=Mac+OSX&function=all

• Driver Settings - set the path to where the driver is installed and add required settings (MacOS example below)

Impala

• Driver: https://www.cloudera.com/downloads/connectors/impala/odbc/2-6-14.html
• Settings: https://docs.cloudera.com/documentation/other/connectors/impala-odbc/latest/Cloudera-ODBC-Driver-for-Impala-Install-Guide.pdf

Databricks

• Driver: https://databricks.com/spark/odbc-drivers-download
• Settings: https://docs.databricks.com/integrations/bi/jdbc-odbc-bi.html#odbc-driver

Third party drivers

The settings for other third-party drivers vary, but the Sqlyze plugin is designed to accept any key-value pair required by the ODBC driver. Always refer to the documentation provided by the driver vendor for the most accurate configuration settings.