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:
+-----------------------------------+
| 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:
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.:
`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.
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.
$ 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.
[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.
[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.
$ 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.
$ 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:
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)
Setting | Example Value |
---|---|
Driver | /Applications/dsdriver/lib/libdb2o.dylib |
Timeout (seconds) | 10 |
host | 127.0.0.1 |
port | 50000 |
uid | db2inst1 |
pwd | •••••••••••• |
database | sample |
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.